Cassandra Time Series Data Modeling

Question

I have a table defined as follows

create table events (offset int,key varchar, user uuid,name varchar, 
emitted int, day int, month int, year int,PRIMARY KEY((offset), year, month, 
day) ) 
WITH CLUSTERING  ORDER BY (year DESC, month DESC, day DESC);

where user is a uuid of the user who submitted the event, offset is unique, but user is not because a user has many events. I'd like to select events given user and date range, how can I accomplish this

Ashraful Islam Ashraful Islam · Accepted Answer · 2017-05-22T15:54:02

You need to a create materialized view or another table

If you are using cassandra >= 3.x, you can create materialized view

CREATE MATERIALIZED VIEW events_by_user AS
    SELECT user, year, month, day, offset
    FROM events
    WHERE user IS NOT NULL AND year IS NOT NULL AND month IS NOT NULL AND day IS NOT NULL AND offset IS NOT NULL
    PRIMARY KEY (user, year, month, day, offset)
    WITH CLUSTERING ORDER BY (year DESC, month DESC, day DESC, offset ASC);

Or If you use lower version then create another table :

CREATE TABLE events_by_user (
   user uuid, 
   offset int, 
   day int, 
   month int, 
   year int, 
   primary key(user,  year, month, day, offset)
);

Now you can query by user with date range i.e :

SELECT * FROM events_by_user WHERE user = 6d5c6400-3f04-11e7-b92e-371a840aa4bb AND (year, month, day) >= (2017,05,20) AND (year, month, day) <= (2017,05,25);

Cassandra Time Series Data Modeling

1 Answers