Ufdjrw

I need to be able to return all users that performed an action during a specified interval. The table definition in Cassandra is just below:

create table t ( timestamp from, timestamp to, user text, PRIMARY KEY((from,to), user))

I'm trying to implement the following query in Cassandra:

select * from t WHERE from > :startInterval and to < :toInterval

However, this query will obviously not work because it represents a range query on the partition key, forcing Cassandra to search all nodes in the cluster, defeating its purpose as an efficient database.

Is there an efficient to model this query in Cassandra?

My solution would be to split both timestamps into their corresponding years and months and use those as the partition key. The table would look like this:

 create table t_updated ( yearFrom int, monthFrom int,yearTo int,monthTo int, timestamp from, timestamp to, user text, PRIMARY KEY((yearFrom,monthFrom,yearTo,monthTo), user) )

If i wanted the users that performed the action between Jan 2017 and July 2017 the query would look like the following:

select user from t_updated where yearFrom IN (2017) and monthFrom IN (1,2,3,4,5,6,7) and yearTo IN (2017) and monthTo IN (1,2,3,4,5,6,7)

Would there be a better way to model this query in Cassandra? How would you approach this issue?

The answer depends on the expected number of entries. Thumb rule, is that a partition should not exceed 100mb. So if you expect a moderate number of entries, it would be enough to go with year as partition key.

We use Week-First-Date as a partition key in a iot scenario, where values get written at most once a minute.

First, the partition key has to operate on equals operator. It is better to use PRIMARY KEY (BUCKET, TIME_STAMP) here where bucket can be combination of year, month (or include days, hrs etc depending on how big your data set is).

It is better to execute multiple queries and combine the result in client side.