CODEWITHSUNDEEP
cassandradata-modelingcql
maulik13
maulik13

Reputation: 3760

How to model data in Cassandra for last 100 events for a customer

We have multiple customers with each customer running multiple sensors. Each sensor logs data frequently (event 20s). How do I create a data model in Cassandra to answer this query?

We have thought of the data model like this for other queries: 

Create Table Data{
  CustomerId,
  SensorId,
  Date,
  DataTime
  SensorData1,
  SensorData2,
  Primary key ((CustomerId, SensorId, Date), DataTime) 
}

Upvotes: 1

Views: 129

Answers (1)

Aaron
Aaron

Reputation: 57798

To satisfy a query of the last 100 events for a customer, you'll need to make two adjustments to the model above:

  1. Adjust your PRIMARY KEY definition to partition only on your CustomerId and your date bucket (Date). Then, you'll want to cluster on DataTime. To ensure uniqueness of sensor, you'll probably also want to add SensorId on the end.

  2. Add a CLUSTERING ORDER with a sort direction of DESC on datatime. This will cluster your data on-disk by datatime, sorted with the most-recent times first.

Basically, I created your table like this:

CREATE TABLE sensordata2 (
    customerid uuid,
    datebucket text,
    datatime timeuuid,
    sensorid text,
    sensordata1 text,
    sensordata2 text,
    PRIMARY KEY ((customerid, datebucket), datatime, sensorid)
) WITH CLUSTERING ORDER BY (datatime DESC, sensorid ASC);

After inserting some test rows, I can now query the last 10 sensor readings WHERE customerid 3221b1d7-13b4-40d4-b41c-8d885c63494f like this:

aploetz@cqlsh:stackoverflow2> SELECT customerid, datebucket, sensorid, dateof(datatime), datatime, sensordata1, sensordata2
FROM sensordata2 WHERE customerid=3221b1d7-13b4-40d4-b41c-8d885c63494f 
AND datebucket='20150515' LIMIT 10;

 customerid                           | datebucket | sensorid | dateof(datatime)         | datatime                             | sensordata1 | sensordata2
--------------------------------------+------------+----------+--------------------------+--------------------------------------+-------------+-------------
 3221b1d7-13b4-40d4-b41c-8d885c63494f |   20150515 |       A1 | 2015-05-15 10:34:34-0500 | e3a15c20-fb17-11e4-93da-21b264d4c94d |          47 |          24
 3221b1d7-13b4-40d4-b41c-8d885c63494f |   20150515 |       A1 | 2015-05-15 10:34:34-0500 | e39ffc90-fb17-11e4-93da-21b264d4c94d |          46 |          23
 3221b1d7-13b4-40d4-b41c-8d885c63494f |   20150515 |       A1 | 2015-05-15 10:34:34-0500 | e39e4ee0-fb17-11e4-93da-21b264d4c94d |          45 |          22
 3221b1d7-13b4-40d4-b41c-8d885c63494f |   20150515 |       B1 | 2015-05-15 10:34:22-0500 | dc64a340-fb17-11e4-93da-21b264d4c94d |          47 |          24
 3221b1d7-13b4-40d4-b41c-8d885c63494f |   20150515 |       B1 | 2015-05-15 10:34:22-0500 | dc60aba0-fb17-11e4-93da-21b264d4c94d |          46 |          23
 3221b1d7-13b4-40d4-b41c-8d885c63494f |   20150515 |       B1 | 2015-05-15 10:34:22-0500 | dc5d0220-fb17-11e4-93da-21b264d4c94d |          45 |          22
 3221b1d7-13b4-40d4-b41c-8d885c63494f |   20150515 |       A1 | 2015-05-15 10:32:16-0500 | 90e27fa0-fb17-11e4-93da-21b264d4c94d |          47 |          24
 3221b1d7-13b4-40d4-b41c-8d885c63494f |   20150515 |       A1 | 2015-05-15 10:32:16-0500 | 90e0aae0-fb17-11e4-93da-21b264d4c94d |          46 |          23
 3221b1d7-13b4-40d4-b41c-8d885c63494f |   20150515 |       A1 | 2015-05-15 10:32:16-0500 | 90de8800-fb17-11e4-93da-21b264d4c94d |          45 |          22
 3221b1d7-13b4-40d4-b41c-8d885c63494f |   20150515 |       A1 | 2015-05-15 10:25:24-0500 | 9b5d1ae0-fb16-11e4-93da-21b264d4c94d |          47 |          24

(10 rows)

Upvotes: 1

Related Questions