Aggregation queries in Cassandra CQL
We are currently evaluating Cassandra as the data store for an analytical application. The plan was to dump raw data in Cassandra and then run mainly aggregation queries over it. Looking at CQL, it does not seem to support some traditional SQL operators like:
- Typical aggregation functions like average, sum, count-Distinct etc.
- Groupby-having operators
I did not find anything that can help achieve the above in the documentation. Also checked if there were any hooks for providing such functions as extensions. Say like in database map-reduce in Mongodb, or user-defined-functions in Relational DBs.
People do talk about the paid Datastax Enterprise Edition, and that too achieves this not via plain Cassandra, but through separate components like Hadoop-Hive-Pig-Hadoop etc. Or there are suggestions about doing needed pre-aggregations before dumping data to the DB since Cassandra writes are fast.
It looked like too much of overheads, at least for basic stuff we need. Am I missing something fundamental here?
Would highly appreciate help on this.
Answer
In one particular application we're using Cassandra for the write speed and then have the app compact the data down to a more compressed, slightly aggregated summary form. Then we run an hourly job to copy the the summary form to Postgres table. This approach doesn't score highly for elegance, but it's simple and it means that we can run ad-hoc analytic queries without having to complicate the primary data ingress path or having to build bespoke aggregation into the CQL app.