I am evaluating distributed query engines for analytical queries (both interactive as well as batch) on large scale data (~100GB). One of the requirements is low latency (<= 1s) for count-distinct queries, where approximate results (with up to 5% error) are acceptable.
Presto seems to support this with its approx_distinct(). As far as my understanding goes, it uses HyperLogLog for that. However, unless the data is persisted in rolled-up form, along with the HyperLogLog values, it would have to be computed on the fly. I do not think my queries would finish within a second for large datasets.
Does it support rollup with HyperLogLog computation at ingestion time (similar to Druid)? Given that unlike Druid, Presto queries the data from external stores (Hive/Cassandra/RDBMS etc.), I am not sure that ingestion time rollups are supported, unless Presto's native store supports them. Can someone please confirm?
2 Answers
There isn't such a thing as "Presto's native store". Presto is query execution engine with connector architecture allowing plugging in multiple storage layers.
If you want an approximate count-distinct for a whole data set, you can compute table stats (When using Presto with Hive, this currently needs to be done in Hive).
If you want an approximate count-distinct for a dynamic selection of data, you still need to read the data. Then you won't get to second latency with such big data set. However, you can combine approx_distinct (or use plain count(distinct ..)) with TABLESAMPLE to limit the size of data read.
You can try with Verdict, which can significantly reduce query processing cost by applying statistics and approximate query processing, yielding 99.9% accuracy. It runs on all SQL-based engines including Apache Hive, Apache Impala, Apache Spark, Amazon Redshift, etc..
You can download source code from here. After downloading and some simple setup, you can issue query as you normally do and get results in a much shorter time.
