How to sum up cassandra counter grouping by only one column in the primary key set?

user_5090590 0
Viewed 282

I am trying to keep track of the amount of events of each type that occured in one-hour buckets of time, and then sum the counts per category in arbitrary time ranges. So, I create a table like this:

CREATE TABLE IF NOT EXISTS sensor_activity_stats(
 sensor_id text,
 datetime_hour_bucket timestamp,
 activity_type text,
 activity_count counter,
 PRIMARY KEY ((sensor_id), datetime_hour_bucket, activity_type)
)
WITH CLUSTERING ORDER BY(datetime_hour_bucket DESC, activity_type ASC);

I would like to be able to achieve this kind of query:

SELECT datetime_hour_bucket, activity_type, SUM(activity_count) as count 
 FROM sensor_activity_stats
  WHERE sensor_id=:sensorId 
   AND datetime_hour_bucket >= :fromDate AND datetime_hour_bucket < :untilDate
  GROUP BY activity_type

Cassandra complains about because grouping must be done in the order of the primary key columns. And, if I change the order I won't be able to query by a range over any activity_type.

Some notes:

  1. I am grouping by hours because some users could ask me to show the data in different timezones and I want to be able to perform a decent conversion.
  2. The activity_type has low cardinality, however I can not be sure I'll always be able to predict it's possible values.

Right now my solution was to query the whole data in the range and perform the aggregation myself in code. Have you have faced similar situation and what was your solution? Would you suggest a different way of querying or arranging the data?

cql cassandra
1 Answers

I hope you've found the solution of your problem, however I have a way to you try.

First, you can chage the create table to change the order of fields:

EDITED

CREATE TABLE IF NOT EXISTS sensor_activity_stats(
 sensor_id text,
 datetime_hour_bucket timestamp,
 activity_type text,
 activity_count counter,
 PRIMARY KEY (activity_type, sensor_id, datetime_hour_bucket, activity_count)
)
WITH CLUSTERING ORDER BY(activity_type ASC, datetime_hour_bucket DESC);

Then, the query you can add the field "datetime_hour_bucket" in the Group By clause:

SELECT datetime_hour_bucket, activity_type, SUM(activity_count) as count 
 FROM sensor_activity_stats
  WHERE sensor_id=:sensorId 
   AND datetime_hour_bucket >= :fromDate AND datetime_hour_bucket < :untilDate
  GROUP BY activity_type, datetime_hour_bucket;
user_152007050
Related