Cassandra: Data Modeling for event based time series

user_2504172 0
Viewed 33

I have a data modeling question. In my application I'm reading data from a few different sensors and storing it in Cassandra. The sensors generate new values at very different rates: Some every other second, some every other month.

Furthermore, the assumption is that a value stays valid until the next one is encountered. Example: Sensor 1 sent a value of 500 at 10s after EPOCH and a value of 1000 at 20s after EPOCH. The valid value for 15s after EPOCH would need to be 500.

Since some rates are going to be high and I don't want unbounded partitions, I want to apply bucketing. I'm thinking about modeling my data like this:

CREATE TABLE sensor_data (
  sensor_id          text,
  some_timing_bucket date,
  measured_at        time,
  value              double
  PRIMARY KEY ((sensor_id, some_timing_bucket),  measured_at)
) WITH CLUSTERING ORDER BY (measured_at DESC);

The usual queries the application would need to serve are "give me the data of the last 5/15 minutes/1 day", so I would choose the some_timing_bucket accordingly. Maybe even have multiple tables with different bucket sizes.

What I cannot wrap my head around is this: Consider I choose one day as bucketing interval. Now I want to retrieve the current value of a sensor that hasn't updated in ten days. There will be no partition for today, so on my application layer I would need to send nine queries that yield nothing until I have gone far enough back in time to encounter the value that is currently valid. That doesn't sound very efficient and I'd appreciate any input on how to model this.

Side note: This would not be an issue if all data for the same sensor was in the same partition: Just ask for all the points with a timestamp less than the beginning of the ranged query and limit the results to one. But that's not feasible because of the unbounded partition.

cassandra
2 Answers

There is a much simpler way to model your data by using one-day buckets. Something like:

CREATE TABLE sensor_data_by_day (
    sensor_id text,
    year int,
    month int,
    day int,
    measured_at timestamp,
    value double,
    PRIMARY KEY ((sensor_id, year, month, day), measured_at)
) WITH CLUSTERING ORDER BY (measured_at DESC)

If a sensor measures a data point every second, then there are 86,400 maximum possible values for a single day (60 secs x 60 mins * 24 hrs). 86K rows per partition is still manageable.

If today is 17 August 2022 and you wanted to retrieve the data for the previous day, the query would be:

SELECT value FROM sensor_data_by_day
    WHERE sensor_id = ?
    AND year = 2022
    AND month = 8
    AND day = 16

Assuming it is currently 08:30:00 GMT on the 17th of August (1660725000000 ms since epoch), to retrieve the data for the last 15 minutes (900 secs ago or 1660724100000 ms):

SELECT value FROM
    WHERE sensor_id = ?
    AND year = 2022
    AND month = 8
    AND day = 17
    AND measured_at > 1660724100000

I think you'll find that it is easier to work with timestamps because it provides a bit more flexibility when it comes to doing range queries. Cheers!

user_42695350

you can do this with a simpler table like this:

 CREATE TABLE sensor_data (
    sensor_id text,
    day_number_from_1970 int,
    measured_at timestamp,
    value double,
    PRIMARY KEY ((sensor_id, day_number_from_1970), measured_at)
) WITH CLUSTERING ORDER BY (measured_at DESC)

and you can query data like that:

SELECT value 
FROM sensor_data
    WHERE sensor_id = some_sensor_id
    AND day_number_from_1970 = day_number
    AND measured_at > start_time 
    AND measured_at < end_time

with a single int column, you should less data on disk and get results well

user_36773190
Related