Get latest unique result with Cassandra

user_4419613 0
Viewed 55

I have a service that handles users state on different services. The traffic can be very high on multiple DCs so I thought Cassandra will fit to store this data.
I only need to keep the latest update per service and user.
I thought about creating this table:

CREATE TABLE db.state (
   service uuid,
   user uuid,
   updated_at timestamp,
   data varchar,

   PRIMARY KEY (service, user, updated_at)
) WITH CLUSTERING ORDER BY (updated_at DESC);

The question is how can I query for the latest 100 unique users state.
With this query:

SELECT service, user, data, updated_at FROM db.state WHERE service = :service LIMIT 100 .

If a certain user had many updates, I'm not going to get the latest 100 users but less. I don't want to merge the unique users in the client because in order to get 100 users I sometimes need to get 10000 rows.

I thought about 2 solutions which both have problems:

  1. create the main table with PRIMARY KEY (service, user) and create materialized view with PRIMARY KEY (service, user, updated_at). But this will hurt performance.
  2. create the table with PRIMARY KEY (service, user) and read with full consistency before the write to check that older update is not written. But this gives up availability and an anti-pattern for Cassandra.

Is there a way to do it without a read-before-write / materialized view?

edit

The writes doesn't necessarily comes in order, so the timestamp is provided externally.
I don't need to keep history, just the last update (by the external timestamp).

cassandra
1 Answers

For your options:

  1. create the main table with PRIMARY KEY (service, user) and create materialized view with PRIMARY KEY (service, user, updated_at). But this will hurt performance.

Materialized views do not really hurt performance much, and write path is very fast so I wouldnt worry about that but there are a lot of issues with MVs currently and marked experimental for a reason - I would not recommend them or you will face lots of consistency issues in current versions.

  1. create the table with PRIMARY KEY (service, user) and read with full consistency before write to check that older update is not written. But this gives up availability and an anti-pattern for Cassandra.

Maybe I am missing some requirement you havent explained but you dont need to do a read before write. This seems to me like by far the best solution to me. Just when you have an update push the change to the (service, user) table, then when you read from the table you get the latest update per user. Theres always IF EXISTS or IF clauses on your insert/update using paxos as well.

If you need the history (not just the latest) and you dont want a second table you can use group by:

CREATE TABLE state (  // simplified a little
   service int,
   user int,
   updated_at timeuuid,
   data text,
   PRIMARY KEY (service, user, updated_at)
) WITH CLUSTERING ORDER BY (user ASC, updated_at DESC);

INSERT INTO state (service, user, updated_at, data) VALUES ( 1, 1, now(), '1');
INSERT INTO state (service, user, updated_at, data) VALUES ( 1, 1, now(), '2');
INSERT INTO state (service, user, updated_at, data) VALUES ( 1, 1, now(), '3');
INSERT INTO state (service, user, updated_at, data) VALUES ( 1, 2, now(), '1');
INSERT INTO state (service, user, updated_at, data) VALUES ( 1, 2, now(), '2');
INSERT INTO state (service, user, updated_at, data) VALUES ( 2, 1, now(), '1');
INSERT INTO state (service, user, updated_at, data) VALUES ( 1, 3, now(), '2');
INSERT INTO state (service, user, updated_at, data) VALUES ( 1, 3, now(), '3');
INSERT INTO state (service, user, updated_at, data) VALUES ( 1, 3, now(), '1');
INSERT INTO state (service, user, updated_at, data) VALUES ( 1, 3, now(), '2');

SELECT * FROM state WHERE service = 1 GROUP BY service, user;

 service | user | updated_at                           | data
---------+------+--------------------------------------+------
       1 |    1 | 7c2bd900-981e-11e9-a27a-7b01c564a3f0 |    3
       1 |    2 | 7c2d1180-981e-11e9-a27a-7b01c564a3f0 |    2
       1 |    3 | 7c88c610-981e-11e9-a27a-7b01c564a3f0 |    2

Its not amazingly efficient or anything but it will work providing you never let a single service partition get too large. I would actually strongly recommend adding a date component/bucket to it like:

CREATE TABLE state (
   bucket text
   service int,
   user int,
   updated_at timeuuid,
   data text,
   PRIMARY KEY ((bucket, service), user, updated_at)
) WITH CLUSTERING ORDER BY (user ASC, updated_at DESC);

where bucket is a YYYY-MM-DD string (or YYYY-WEEKOFYEAR or something). Then just around boundary times you query both current and last bucket. Otherwise the partitions will grow until they cause issues.

user_2663370
Related