Cassandra data modelling for group by

user_275891 0
Viewed 99

I have a data, where columns looks like:

group, item, date, some_metric_column, another_metric_column

A bit about the columns:

Instead of metric_column substitute clicks/sales/temperature/etc...

item belongs to a group. So when item=18 and group=6 - then never would be situation when item=18 and group=10 (different group for same item). Group may have up to 100K items inside (daily).

date - it's a date when those metrics occurred.

So we have data like:

groupA, itemA1, 2021-05-10, temp=32
groupA, itemA1, 2021-05-11, temp=36
groupA, itemA2, 2021-05-11, temp=37
groupB, itemB1, 2021-05-10, temp=18
....

The result I'm trying to query from that data is:

give me all items from specified group between some dates and AVG(temp). (or SUM or whatever)

So for dates between May 10 and 11, and group=groupA would have something like:

item,  avg(temp)
itemA1, 34
itemA2, 37

Question:

How should I organise table primary key, what would be partition key, what clustering, and how better to query it?

Ideally I want to filter out results and return only subset of it that matches some requirements, like when AVG temp is greater than X. (having clause?)

Thanks in advance.

EDIT1:

The one and single SQL query, that I want to run over this table looks like:

SELECT item, avg(temp) temp, avg(humidity) humidity 
FROM table_name
WHERE group = 8
  and date >= '2021-04-01' 
  and date <= '2021-04-30'
group by item 
having temp > 50

But CQL, is slightly different with more restrictions on keys composition.

Imagine, the group is like some organisation_id, so we want to see all weird sensors from that organisation (it would not be all 100K cause of having clause, but expect to get up to 1K as output).

As I said above, org can handle up to 100K sensors (usually it's about 10-20K but may be 100K or slightly more in some rare cases). And we have to save all those 100K on daily basis.

And of course - that table contains many organisations.

EDIT2:

About cardinality.

There are some more interim relations between group and item. Exact data struct is: account -> profile -> group -> subgroup -> item. My initial example from above columns looks like:

account, item, date, some_metric_column, another_metric_column

but we can easily use remaining columns to more granularly identify the item (partition it). Then columns in table would looks like:

account, profile, group, subgroup, item, date, some_metric_column, another_metric_column

But anyway the question is:

how to get the all items from account=X for some specific date range and aggregate some metrics for every item. The range is most recent 60 or 90 days (it's not infinity or too wide range like years of data).

And last one point the data is should be updatable, so I can remove data for account=X for day=Y and insert updated data back again. (so solution to use data type list for metric column is not reasonable)

data-modeling cassandra
1 Answers

How should I organize table primary key, what would be partition key, what clustering, and how better to query it?

Without understanding the cardinalities of each column and in the interest of addressing the specific question, I'd build the PK/CK definition like this:

PRIMARY KEY (group,item,date))
WITH CLUSTERING ORDER BY (item asc, date desc);

After loading the data above, you could run a SELECT like this:

> SELECT item, avg(temp) FROM temps_by_item
  WHERE group='groupA'
  GROUP BY group,item;

 item   | system.avg(temp)
--------+------------------
 itemA1 |               34
 itemA2 |               37

(2 rows)

Again though, the other main thing to think about, is the cardinalities of group and item, just to make sure that the partitions by group don't get too big.

Edit

Ugh, I just saw this.

Group may have up to 100K items inside (daily).

So maybe there's a way to split that up? Although, if we're only talking about a couple of columns on each, it might be ok as long as the partition sizes stay between 1MB and 10MB.

user_10545580
Related