CODEWITHSUNDEEP
cassandradata-modeling
Eddie Martinez
Eddie Martinez

Reputation: 13910

how to handle search by unique id in Cassandra

I have a table with a composite primary key. name,description, ID

PRIMARY KEY (id, name, description)

whenever searching Cassandra I need to provide the three keys, but now I have a use case where I want to delete, update, and get just based on ID.

So I created a materialized view against this table, and reordered the keys to have ID first so I can search just based on ID.

But how do I delete or update record with just an ID ?

Upvotes: 1

Views: 346

Answers (1)

xmas79
xmas79

Reputation: 5180

It's not clear if you are using a partition key with 3 columns, or if you are using a composite primary key.

If you are using a partition key with 3 columns:

CREATE TABLE tbl (
    id uuid,
    name text,
    description text,
    ...
    PRIMARY KEY ((id, name, description))
);

notice the double parenthesis you need all 3 components to identify your data. So when you query your data by ID from the materialized view you need to retrieve also both name and description fields, and then issue one delete per tuple <id, name, description>.

Instead, if you use a composite primary key with ID being the only PARTITION KEY:

CREATE TABLE tbl (
    id uuid,
    name text,
    description text,
    ...
    PRIMARY KEY (id, name, description)
);

notice the single parenthesis, then you can simply issue one delete because you already know the partition and don't need anything else.

Check this SO post for a clear explanation on primary key types.

Another thing you should be aware of is that the materialized view will populate a table under the hood for you, and the same rules/ideas about data modeling should also apply for materialized views.

Upvotes: 2

Related Questions