Cassandra insert/update if not exists or field=value

user_779340 0
Viewed 424

I would like to do in a single operation an insert if a record doesn't exist, or update only if a field of the row has a certain value.

Imagine the table:

CREATE TABLE (id VARCHAR PRIMARY KEY, field_a VARCHAR, field_b VARCHAR);

Is it possible to have something like:

UPDATE my_table SET field_a='test' WHERE id='an-id' IF NOT EXISTS OR IF field_b='AVALUE';

Doing INSERT/UPDATE on a single statement that insert the record if field_b has value AVALUE or create a new row if a row doesn't already exists, but in case a row is in the table having field_b with a different value failing the update.

cql conditional-statements upsert cassandra
1 Answers
UPDATE my_table SET field_a='test' WHERE id='an-id'
IF NOT EXISTS OR IF field_b='AVALUE';

There are a few of nuances here. First it's important to remember that when doing a compare-and-set (CAS) operation in CQL, the syntax and capabilities between INSERT and UPDATE are not the same.

Case-in-point, the IF NOT EXISTS conditional is valid for INSERT, but not for UPDATE. On the flip-side, IF EXISTS is valid for UPDATE, but not for INSERT.

Secondly, OR is not a valid operator in CQL WHERE or in CAS operation conditionals.

Third, using UPDATE with IF EXISTS short-circuits any subsequent conditionals. So UPDATE can either use IF EXISTS or IF (condition) [ AND (another condition) ], but not both.

Considering these points, it would seem one approach here would be to split the statement into two:

INSERT INTO my_table (id,field_a) VALUES ('a1','test') IF NOT EXISTS;

And:

UPDATE my_table SET field_a='test' WHERE id='an-id' IF field_b='AVALUE';

These are both valid CQL. However, that doesn't really help this situation. An alternative would be to build this logic on the application side. Technically, read-before-write approaches are considered anti-patterns in Cassandra, in-built CAS operations not withstanding due to their use of lightweight transactions.

Perhaps something like SELECT field_a,field_b FROM my_table WHERE id='an-id'; is enough to answer whether it exists as well as what the value of field_b is, thus triggering an additional write? There's a potential for a race condition here, so I'd closely examine the business requirements to see if something like this could work.

user_10545580
Related