Let's say that I have the following table in Cassandra:
customer_bought_product (
store_id uuid,
product_id text,
order_time timestamp,
email text,
first_name text,
last_name text,
PRIMARY KEY ((store_id, product_id), order_time, email)
The partition keys are store_id and order_id and it is used in order to store time series data.
The data does not have a TTL, as it should be accessible at all times.
In some cases we may require to delete all of the data for a given store_id.
What is the best practice to do it?
So far I have thought of the following solutions:
- Write a program, that will select all of the data from the table and delete the records with the given
store_id. - The downside is that this will take more and more time as we insert more data in the table. - Leave the data in the table. - The only problem with doing this, is that we will have useless data.
- Store the table name with the available partition keys in a different table, that can be queried by
store_id, get the keys from it and create a delete statement for each or those keys. - I do not like this concept, because I have to maintain the records.
Has anyone encountered this problem? What is the best practice to clear unused records from Cassandra (excluding TTL)?