Understand and use the DML subset of CQL

What is the syntax of the INSERT statement?

INSERT INTO table_name (column1, column2 ...) VALUES (value1, value2 ...)

What is the syntax of the UPDATE statement?

UPDATE <keyspace>.<table> SET column_name1 = value, column_name2 = value WHERE primary_key_column = value;

  • Row must be identified by values in primary key columns

What is an "upsert"?

  • Both UPDATE and INSERT are write operations
  • No reading before writing

What are lightweight transactions or ‘compare and set’?

Introduces a new clause IF NOT EXISTS for inserts

  • Insert operation executes if a row with the same primary key does not exist
  • Uses a consensus algorithm called Paxos to ensure inserts are done serially
  • Multiple messages are passed between coordinator and replicas with a large performance penalty
  • [applied] column returns true if row does not exist and insert executes
  • [applied] column is false if row exists and the existing row will be returned

Update uses IF to verify the value for column(s) before execution

  • [applied] column returns true if condition(s) matches and update written
  • [applied] column is false if condition(s) do not match and the current row will be returned

What is the purpose of the BATCH statement?

BATCH statement combines multiple INSERT, UPDATE, and DELETE statements into a single logical operation

  • Saves on client-server and coordinator-replica communication
  • Atomic operation
    • If any statement in the batch succeeds, all will
  • No batch isolation
    • Other “transactions” can read and write data being affected by a partially executed batch

Example:

BEGIN BATCH
  DELETE FROM albums_by_performer WHERE performer = 'The Beatles' AND year = 1966 AND title = 'Revolver'; 
  INSERT INTO albums_by_performer (performer, year, title, genre) VALUES ('The Beatles', 1966, 'Revolver', 'Rock');
APPLY BATCH;

Lightweight transactions in batch

  • Batch will execute only if conditions for all lightweight transactions are met
  • All operations in batch will execute serially with the increased performance overhead

Example:

BATCH
  UPDATE user SET lock = true IF lock = false; WHERE performer = 'The Beatles' AND year = 1966 AND title = 'Revolver'; 
  INSERT INTO albums_by_performer (performer, year, title, genre) VALUES ('The Beatles', 1966, 'Revolver', 'Rock');
  UPDATE user SET lock = false; 
APPLY BATCH;