how to delete a row using non primary key in cassandra

Question

This is my table with id as the primary key:

I want to delete the row based on msisdn column.

How can I do that?

Please suggest

Answer 1

The answer is: By writing a tool that first does a select using allow filtering to determine the primary key, then do the delete using the determined primary key.

The actual answer is: You don't, because what I just proposed would be terribly slow, put your cluster under a lot of stress, and generally reveal a misconception of how Cassandra data modeling works.

Cassandra data modeling is fundamentally different than SQL data modeling. In SQL, you have your primary keys, you normalize, and then you are pretty confident that you will be able to query and write most things with good performance. In Cassandra you model for your queries, and by queries, I mean anything that contains a where.

Assuming that you designed your table like this for a reason, what you could do is add a materialized view that adds msisdn to the primary key, turns it into the partitioning key, and turns id into a clustering column. That way, you could at least query efficiently without doing an allow filtering. You will still need two roundtrips, one for reading, one for deleting, but that cost may be acceptable. If it is not, you need to fundamentally redesign your table, keeping in mind that you will need to query by msisdn.