Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Difference between UPDATE and INSERT in Cassandra?

What is the difference between UPDATE and INSERT when executing CQL against Cassandra?

It looks like there used to be no difference, but now the documentation says that INSERT does not support counters while UPDATE does.

Is there a "preferred" method to use? Or are there cases where one should be used over the other?

Thanks so much!

like image 328
Chris Dutrow Avatar asked May 13 '13 22:05

Chris Dutrow


People also ask

Is insert and update same in Cassandra?

Insert, Update, and Upsert Because Cassandra uses an append model, there is no fundamental difference between the insert and update operations. If you insert a row that has the same primary key as an existing row, the row is replaced. If you update a row and the primary key does not exist, Cassandra creates it.

How does update work in Cassandra?

Cassandra treats each new row as an upsert: if the new row has the same primary key as that of an existing row, Cassandra processes it as an update to the existing row. During a write, Cassandra adds each new row to the database without checking on whether a duplicate record exists.

What is Upsert in Cassandra?

Cassandra does upsert. Upsert means that Cassandra will insert a row if a primary key does not exist already otherwise if primary key already exists, it will update that row.

Can we update in Cassandra?

UPDATE writes one or more column values to a row in a Cassandra table. Like INSERT, UPDATE is an upsert operation: if the specified row does not exist, the command creates it. All UPDATEs within the same partition key are applied atomically and in isolation.


1 Answers

There is a subtle difference. Inserted records via INSERT remain if you set all non-key fields to null. Records inserted via UPDATE go away if you set all non-key fields to null.

Try this:

CREATE TABLE T (   pk int,   f1 int,   PRIMARY KEY (pk) );  INSERT INTO T (pk, f1) VALUES (1, 1); UPDATE T SET f1=2 where pk=2; SELECT * FROM T; 

Returns:

 pk | f1 ----+----   1 |  1   2 |  2 

Now, update each row setting f1 to null.

UPDATE T SET f1 = null WHERE pk = 1; UPDATE T SET f1 = null WHERE pk = 2; SELECT * FROM T; 

Note that row 1 remains, while row 2 is removed.

 pk | f1 ----+------   1 | null 

If you look at these using Cassandra-cli, you will see a different in how the rows are added.

I'd sure like to know whether this is by design or a bug and see this behavior documented.

like image 118
billbaird Avatar answered Sep 20 '22 08:09

billbaird