Delete data from Cassandra with part of the partition key

Viewed 5689

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:

  1. 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.
  2. Leave the data in the table. - The only problem with doing this, is that we will have useless data.
  3. 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)?

2 Answers
Related