Data modelling to faciliate pruning/bulk update/delete in scylladb/cassandra

user_973508 0
Viewed 102

Lets say I have a table like below with a composite partition key.

CREATE TABLE heartrate (
    pet_chip_id  uuid,
    date text,
    time timestamp,
    heart_rate int,
    PRIMARY KEY ((pet_chip_id, date), time)
);

Lets say there is a batch job to prune all the data older than X. I can't do below query since its missing other partition key in the query.

DELETE FROM heartrate WHERE date < '2020-01-01';

How do you model your data such a way that this can be achieved in Scylla? I understand that internally scylla creates a partition based on partition keys but in this case its impossible to query all the list of pet_chip_id and do N queries to delete.

Just wanted to know how people do this outside RDBMS world.

scylla nosql cassandra
1 Answers

The recommended way to delete old data automatically in Scylla is using the Time-to-live (TTL) feature:

When you write a row, you add "USING TTL 864000" is you want that data to be deleted automatically in 10 days. You can also specify a default TTL for a given table, so that every piece of data written to the table will get expired after (say) 10 days.

Scylla's TTL feature is separate from the data itself, so it doesn't matter which columns you used as partition keys or clustering keys - in particular the "date" column no longer needs to be a clustering key (or exist at all, for that matter) - unless you also need it for something else.

user_88912240
Related