0

Cassandra supports upsert for UPDATE. That is, if the key provided in the update statement doesn't exist in the table; it is implied as insert. Is there a way to know from the return code of the update query if update resulted in update or insert. We would prefer to detect this without an additional read from Cassandra.

We have scenario where we have a very high throughput application where 99% of the events results in insert but a very small chunk results in update. Once updated we would want perform some additional checks and trigger some.

Raedwald
  • 46,613
  • 43
  • 151
  • 237
Sunil
  • 139
  • 1
  • 2
  • 8

2 Answers2

3

You can add IF EXITS in the end of update statement.

So the update statement would be :

UPDATE person SET name = 'xxxxx' WHERE id = '16843158' IF exists;

for a table person and name, id being the column names.

This would return the True if the row exits and False if not. Using this you can create a check and then "perform some additional checks and trigger some". Also this would not insert anything if row does not exit.

Please check if the true/false is returned as column of Row or as a boolean, because I have checked this in cqlsh only.

Talking for Java. In cqlsh it is displayed as column output.

r005t3r
  • 228
  • 1
  • 8
  • I think if Cassandra had bit opposite of this would help us. As I said in our case 99% of incoming records results in insert. And 1% update. We want to let both do what it does; update or insert. But I just want to know after upsert execution what had actually happened; update or insert. – Sunil Mar 06 '17 at 07:27
  • Ok! I understand what exactly you want. I doubt that, what you want exist on level of Cassandra itself. You probably have to have this functionality on your app level. By using the above query, you can find out if update succeeded or there is need to insert it (see through the words). There does not seems to be any way after normal upsert execution to decide "what had actually happened; update or insert". – r005t3r Mar 06 '17 at 07:44
1

Short answer: All Cassandra writes are upserts, they do not and cannot check the state of the database before writing.

Longer answer: knowing whether a value is set requires a read from Cassandra, meaning that if you want to know whether something exists when you upsert means you end up having to do the anti-pattern of reading before writing.

Basically all data models which require this kind of stateful information will need to take performance penalties. Your best bet is to figure out another way to structure your code. If you really need to know the difference you may try Paxos supported if not exists style requests but know they come at a cost as well.

RussS
  • 16,476
  • 1
  • 34
  • 62