Write multiple DML statements.
BEGIN ( UNLOGGED | COUNTER ) BATCH USING TIMESTAMP timestamp dml_statement; dml_statement; ... APPLY BATCH;
- Uppercase means literal
- Lowercase means not literal
- Italics mean optional
- The pipe (|) symbol means OR or AND/OR
- Ellipsis (...) means repeatable
- Orange ( and ) means not literal, indicates scope
A semicolon that terminates CQL statements is not included in the synopsis.
A BATCH statement combines multiple data modification language (DML) statements (INSERT, UPDATE, DELETE) into a single logical operation, and sets a client-supplied timestamp for all columns written by the statements in the batch. Batching multiple statements saves network exchanges between the client/server and server coordinator/replicas.
Batches are atomic by default. In the context of a Cassandra batch operation, atomic means that if any of the batch succeeds, all of it will. To achieve atomicity, Cassandra first writes the serialized batch to the batchlog system table that consumes the serialized batch as blob data. When the rows in the batch have been successfully written and persisted (or hinted) the batchlog data is removed. There is a performance penalty for atomicity. If you do not want to incur this penalty, prevent Cassandra from writing to the batchlog system by using the UNLOGGED option: BEGIN UNLOGGED BATCH
Although an atomic batch guarantees that if any part of the batch succeeds, all of it will, no other transactional enforcement is done at the batch level. For example, there is no batch isolation. Other clients are able to read the first updated rows from the batch, while other rows are in progress. However, transactional row updates within a single partition are isolated: a partial partition update cannot be read.
Using a timestamp
BATCH supports setting a client-supplied timestamp, an integer, in the USING clause. The timestamp applies to all statements in the batch. If not specified, the current time of the insertion (in microseconds) is used.
Individual DML statements inside a BATCH cannot specify a timestamp. If you do not specify a batch-level timestamp, you can specify a timestamp in the individual DML statements.
Batching conditional updates
In Cassandra 2.0.6 and later, you can batch conditional updates introduced as lightweight transactions in Cassandra 2.0. Only updates made to the same partition can be included in the batch because the underlying Paxos implementation works at the granularity of the partition. You can group updates that have conditions with those that do not, but when a single statement in a batch uses a condition, the entire batch is committed using a single Paxos proposal, as if all of the conditions were contained in the batch apply. This example shows batching of conditional updates:
The statements for inserting values into purchase records use the IF conditional clause.
BEGIN BATCH INSERT INTO purchases (user, balance) VALUES ('user1', -8) IF NOT EXISTS; INSERT INTO purchases (user, expense_id, amount, description, paid) VALUES ('user1', 1, 8, 'burrito', false); APPLY BATCH; BEGIN BATCH UPDATE purchases SET balance = -208 WHERE user='user1' IF balance = -8; INSERT INTO purchases (user, expense_id, amount, description, paid) VALUES ('user1', 2, 200, 'hotel room', false); APPLY BATCH;
A continuation of this example shows how to use a static column with conditional updates in a batch.
Batching counter updates
Use BEGIN COUNTER BATCH in a batch statement for batched counter updates. Unlike other writes in Cassandra, counter updates are not idempotent.
BEGIN BATCH INSERT INTO users (userID, password, name) VALUES ('user2', 'ch@ngem3b', 'second user') UPDATE users SET password = 'ps22dhds' WHERE userID = 'user2' INSERT INTO users (userID, password) VALUES ('user3', 'ch@ngem3c') DELETE name FROM users WHERE userID = 'user2' INSERT INTO users (userID, password, name) VALUES ('user4', 'ch@ngem3c', 'Andrew') APPLY BATCH;