Alternative to BigQuery for medium-sized data

Question

This is a follow-up to the question Why doesn't BigQuery perform as well on small data sets.

Let's suppose I have a data-set that is ~1M rows. In the current database that we're using (mysql) aggregation queries would run quite slow, perhaps taking ~10s or so on complex aggregations. On BigQuery, the initialization time required might make this query take ~3 seconds, better than in mysql, but the wrong tool for the job, if we need to return queries in 1s or under.

My question then is, what would be a good alternative to using BigQuery on doing aggregated queries on moderate-sized data-sets, such as 1-10M rows? An example query might be:

SELECT studio, territory, count(*)
FROM mytable
GROUP BY studio, territory
ORDER BY count(*) DESC

Possible solutions I've thought of are ElasticSearch (https://github.com/NLPchina/elasticsearch-sql) and Redshift (postgres is too slow). What would be a good option here that can be queried via SQL?

Note: I'm not looking for why or how BQ should be used, I'm looking for an alternative for data sets under 10M rows where the query can be returned in under ~1s.

Answer 1

2020 update: Check out BigQuery BI Engine, the built-in accelerator of queries for dashboards:

• https://cloud.google.com/bi-engine/docs/overview

---

If you need answers in less than a second, you need to think about indexing.

Typical story:

1. MySQL (or any other database proposed here) is fast, until...
2. One day some of your aggregation queries start running slow. Minutes, hours, days, etc.
3. Typical solution for step 2 is indexing and pre-aggregating. If you want answers in less than a second for certain type of questions, you'll need to invest time and optimization cycles to answer just that type of questions.
4. BigQuery's beauty is that you can skip step 3. Bring those minutes/hours/days to seconds, with minimal investment - any query, at any time.

BigQuery is awesome because it gives you 4. But you are asking for 3, MySQL is fine for that, Elasticsearch is fine too, any indexed database will bring you results in less than a second - as long as you invest time on optimizing your system for certain type of question. Then to get answers for any arbitrary question without investing any optimization time, use BigQuery.

BigQuery: Will answer arbitrary questions in seconds, no preparation needed.

MySQL and alternatives: Will answer certain type of questions in less than a second, but it will take development time to get there.

Answer 2

Here are a few alternatives to consider for data of this size:

1. Single Redshift small SSD node
   • No setup. Easily returns answers on this much data in under 1s. 
2. Greenplum on a small T2 instance
   • Postgres-like. Similar perf to Redshift. Not paying for storage you won't need. Start with their single node "sandbox" AMI.
3. MariaDB Columnstore
   • MySQL-like. Used to be called InfiniDB. Very good performance. Supported by MariaDB (the company).
4. Apache Drill
   • Drill has a very similar philosophy to BiqQuery but can be used to anywhere (it's just a jar). Queries will be fast on this size data.

If low admin / quick start is critical go with Redshift. If money / flexibility is critical start with Drill. If you prefer MySQL start with MariaDB Columnstore.