Can you change the partition key of a particular row in a Cassandra table?

user_19872592 0
Viewed 31

I am unsure if you can change the partition key value of a particular row in a table. For example, if you had a table such that the PRIMARY KEY(name, title) and have some entry where name = "John", title = "New". Would we be able to run:

UPDATE table
SET name="Ron"
WHERE name="John" AND title="New";

I understand the general concept of the partition key such that it uniquely identifies rows in a partition while also identifying what nodes hold said partitions. So this leads to me to believe this would not run.

datastax-enterprise cql cassandra
2 Answers

So this leads to me to believe this would not run.

You are correct. With Cassandra it is correct to say that UPDATE and INSERT typically do the same thing (under the hood). However in this case, that is not true. Running that UPDATE statement yields this message:

> UPDATE table SET name='Ron' WHERE name='John' AND title='New';
InvalidRequest: Error from server: code=2200 [Invalid query] message="PRIMARY KEY part name found in SET part"

This is because the UPDATE has additional checks around the WHERE clause. The best way to handle a situation like this, is to rewrite the row with the new key and then DELETE the old row.

And it's probably best to wrap those two statements in a BATCH, to ensure that entry doesn't end up in a weird state due to one of them failing.

BEGIN BATCH
    INSERT INTO table (name,title) VALUES ('Ron','New');
    DELETE FROM table WHERE name='John' AND title='New';
APPLY BATCH;
user_10545580

It is not possible to change the partition key of a row in Cassandra.

The partition key uniquely identifies a partition in a table. Think of the partition key as the record ID. It isn't possible to change the partition key of a partition because it is more like the metadata which identifies where the actual data is stored in the cluster.

A simple analogy of this is the address to your house. Your address (partition key) is a unique identifier for locating your house (the partition or data). If you changed your address then it is going to identify a completely different house, not your house. Changing the address to your house makes no sense unless you have moved houses.

If you were to attempt to run the example query you posted, you would get an error which indicates that a primary key column was included in the SET clause which isn't valid. Cheers!

user_42695350
Related