MAX(), DISTINCT and group by in Cassandra

nosql cassandra cql cql3 nosql-aggregation
eldho picture eldho · Jun 27, 2013 · Viewed 57k times · Source

I am trying to remodel a SQL database Cassandra such that, I can find the Cassandra equivalent for the SQL queries. I use CQL 3 and Cassandra v1.2. I modeled the db design in cassandra so that it supports the order by clauses and denormalized tables to support the join operation. However I am at sea when it comes to DISTINCT, SUM() and GROUPBY equvalents

SELECT a1,MAX(b1) FROM demo1 group by a1.
SELECT DISTINCT (a2) FROM demo2 where b2='sea'
SELECT sum(a3), sum(b3) from demo3 where c3='water' and d3='ocean'

This is like a showstopper to my work for past couple of days. Is there a way in Cassandra, that I can model the db schema to support queries of these kind? I cant think of any way in Cassandra . How are such queries be implemented using Cassandra?

I read that a hive layer over Cassandra can possibly make these queries work. I am just wondering if that is the only way that such queries can be supported in Cassandra..? Pls advise on any other possible methods..

Answer

Theo picture Theo · Jun 27, 2013

With Cassandra you solve these kinds of problems by doing more work when you insert your data -- which sounds like it would be slow, but Cassandra is designed for fast writes, and you're probably going to read the data many more times than you write it so it makes sense when you consider the whole system.

I can't tell you exactly how to create your tables to model your problem because it will depend a lot on the details. You need to figure a schema that lets you get the data without performing any on-the-fly aggregations. Think about how you would create views for the queries in an RDBMS, and then try to think how you would insert data directly into those views, not into the underlying tables. That's kind of how you model things in Cassandra.

Related questions

Cassandra IN clause on index

Assume a simple table with one insert (or without this insert, doesn't really matter). CREATE TABLE test ( x int, y int, z int, PRIMARY KEY (x, y, z) ); create index z_index on test (z); insert into test(x, y, …

Read More
how UPDATE rows in cassandra using only Partition Key?

My table looks like this create table Notes( user_id varchar, real_time timestamp, insertion_time timeuuid, read boolean PRIMARY KEY (user_id,real_time,insertion_time) ); create index read_index on Notes (read); I want update all the rows …

Read More
How to get tombstone count for a cql query?

I am trying to evaluate number of tombstones getting created in one of tables in our application. For that I am trying to use nodetool cfstats. Here is how I am doing it: create table demo.test(a int, b …

Read More