1

I am in need of the best approach for the following use case,

I have 'Device' table (Only one Partition Id : 'Device') and I have another table 'DeviceStatistics' (Partition Id : 'deviceId' so that this table will have as many partition as number of devices) which means for every device there will be a statistics collected for every minute.

CREATE TABLE device(
   "partitionId" text,"name" text,"deviceId" text, ..., primary key ("partitionId","name","deviceId"));

where partitionId - it is a constant ('device')

CREATE TABLE deviceStatistics (    
"deviceId" text,     
"timestamp" timestamp, ...,
primary key ("deviceId","timestamp")) with clustering order by ("timestamp" DESC);

where 'deviceId' - it is the partition key and under each partition will have the list of timestamp entries

Till this it is fine, Because I just need the following queries,

1) select * from device where partitionId = 'device'
   - which list all the devices available. 
2) select * from deviceStatistics where deviceId = 'deviceId_1'
   - which list all the device statistics for a deviceId
3) select * from deviceStatistics where deviceId = 'deviceId_1' LIMIT 1
   - which gets the most recent statistics for a deviceId

Now I need the solution for the following use case,

I need to collect the cluster level statistics which means I need to collect all the device statistics for the timestamp,

(i.e) If the deviceStatistics for 4 devices are available for the timestamp then I need to collect all the four statistics for a timestamp and add in device group level.

which means my DeviceGroupstatistics is the aggregation of all the device statistics for the timestamp.

Now the problem is, Since I have 'deviceId' as the partitionId for the deviceStatistics table, I need to perform this query (select * from deviceStatistics where deviceId = 'deviceId' LIMIT 1) for all the deviceIds. So Lets say I have 1000 devices, then I need to trigger this query for all the 1000 devices for every minute.

Is there a better design for this?

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Harry
  • 3,072
  • 6
  • 43
  • 100

2 Answers2

1

I would recommend to have separate table where timestamp will be the partition key, and device ID is clustering key. Granularity of timestamp may depend on your application - for example, drop seconds & round to minutes, or something like.

You can implement storing the data from your application (preferred), or you can use materialized views (but they are experimental, and not always recommended to use).

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
  • thanks for your suggestion but the problem is 'I designed the table based on my queries' If I keep the timestamp as partition key then there will be too much of partitions and also this query will be impacted : select * from deviceStatistics where deviceId = 'deviceId_1' AND timestamp from somevalue to timestamp to somevalue – Harry Feb 18 '18 at 13:27
  • Currently I am looking for the query to frame devicegroupstatistics, It is getting complex for me when the number of devices is getting increased. I need to collect the statistics for every deviceId for the last minute and It increases the query but the current table design satisfies this query : select * from deviceStatistics where deviceId = 'deviceId_1' AND timestamp from somevalue to timestamp to somevalue – Harry Feb 18 '18 at 13:29
  • 1
    I believe the solution above is to build a third table with timestamp as partition key and deviceId as clustering column. Write to both the tables deviceStatistics_by_timestamp and deviceStatistics_by_deviceId. Read corresponding table depending on what the partitioning key is. – dilsingi Feb 18 '18 at 18:03
  • 1
    @dilsingi - yes, you're correct - that's my suggestion - either by writing it explicitly, or by creation of materialized view – Alex Ott Feb 18 '18 at 19:22
  • @AlexOtt Thanks for the solution. It's really sane. :) – Harry Feb 19 '18 at 04:34
  • @AlexOtt Could you check this : https://stackoverflow.com/questions/48920971/datamodel-use-case-for-logging-in-cassandra – Harry Feb 22 '18 at 10:00
1

Alex Ott's proposal is good practice: duplicate your data in another table with a timestamped bucket (day, hour, minute, second, depending on input velocity) as partition key, and deviceid as first clustering column (depending on your query).

something like 

PRIMARY KEY (bucket, device_id, timestamp ... etc)

Choosing the right bucket size is important : according to several posts, a partition in cassandra should not go too much above 100MB or so.

If you collect your statistics every minute, a day bucket for 1000 devices and 100 bytes record of data would result in a 1440 (24x60) x nbr of devices (1000) x size of record (100) partition size => 144,000,000 bytes per partition whichs sounds ok, but you have to do the estimation and measures with your data, this is a rough calculation.

If you have to query for sevaral days, you will have to add an IN clause to your query, with a limited number of terms (10 is considered lot), or do several queries, but they will be fast ... :)

bests,

Alain

ARA
  • 1,296
  • 10
  • 18