Follow my blog with Bloglovin

Wednesday, January 15, 2014

Count (*) : Cassandra Data Modeling

How to model Cassandra for count queries?

Consider same "playlist" database from previous post.

We need to do following queries in tracks table:
  • Count number of tracks. SQL : SELECT COUNT(*) FROM tracks;
  • Count number of tracks for a particular genre. SQL : SELECT COUNT(*) FROM tracks WHERE genre=?;
Step #1:
Create table to store counts:
CREATE TABLE track_count {
   count counter,
   table_name text,
   genre text,
   PRIMARY KEY(table_name,genre)
}

This table can be used to store all table counts, hence table_name column added. In case, if there we need to have count for only one table this column can be removed. And, if grouping is not required or distinct groups are large (so to implement count all need to read all record in memory), remove this column. So, for one table count all query there will be only one cell in this table.

Generalized table to store counts for all tables in database:
CREATE TABLE track_count {
   count counter,
   table_name text,
   <grouping columns>,
   PRIMARY KEY(table_name,<grouping columns>...)
}
 

Step #2:
Update count on adding track:
BATCH BEGIN
INSERT INTO tracks (...) VALUES (...);
UPDATE tracks SET count=count+1 WHERE table_name='tracks' and genre=?;
APPLY BATCH;

On every insert of track the count will be updated and grouped by genre. As, genre will of small number its okay to get all records in memory and sum counts to get number of records.

Step #3:
CQL queries modeling:

[SQL] : SELECT COUNT(*) FROM tracks;
[CQL] : SELECT count FROM track_count WHERE table_name='tracks';
Sum count in application code.

[SQL] : SELECT COUNT(*) FROM tracks WHERE genre=?;
[CQL] : SELECT count FROM track_count WHERE table_name='tracks' genre=?;

[SQL] : SELECT COUNT(*) FROM tracks GROUP BY genre;
[CQL] : SELECT count FROM track_count WHERE table_name='tracks';
 
In SQL we query same table for count and in CQL we create new tables(s) and query other table(s).

Popular Posts