• Glossary
  • Support
  • Downloads
  • DataStax Home
Get Live Help
Expand All
Collapse All

CQL for DSE

  • Overview
    • Cassandra structure
    • About CQL
    • CQL quick reference
    • Known issues
  • Getting started
    • Vector Search Quickstart
    • CQL Quickstart
    • SAI Quickstart
    • DSE Search Indexing Quick Start
  • Planning
    • Data modeling
      • Data modeling concepts
      • Data modeling analysis
  • Connecting
    • cqlsh (startup options)
      • Accessing data using CQL
        • Starting the standalone cqlsh tool
        • Starting cqlsh
    • Developing
      • Introduction to CQL
      • Managing keyspaces
        • Keyspace concepts
        • Create a keyspace
        • Check a keyspace
        • Alter a keyspace
        • Drop a keyspace
      • Managing tables
        • Table concepts
        • Data types
        • Create a table
        • Check a table
        • Alter a table
        • Truncate a table
        • Drop a table
      • Managing columns
        • Managing columns
        • Create a table column
        • Check column existence
        • Alter a table column
        • Drop a column
        • User-defined type (UDT) overview
          • Create a user-defined type (UDT)
          • Check for user-defined type (UDT) existence
          • Alter a user-defined type (UDT)
          • Drop a user-defined type (UDT)
        • User-defined functions (UDFs) and user-defined aggregates (UDAs) overview
          • Create a user-defined function (UDF)
          • Check a user-defined function (UDF)
          • Drop a user-defined function (UDF)
          • Create a user-defined aggregate function (UDA)
          • Checking a user-defined aggregate (UDA)
          • Drop a user-defined aggregate (UDA)
      • Managing Materialized views
        • Materialized views concepts
        • Known limitations of materialized views
        • Create a materialized view (MV)
        • Alter a materialized view (MV)
        • Drop a materialized view
        • Frequently asked questions about materialized views
      • Indexing Tables
        • Indexing concepts
        • Storage-Attached Indexing (SAI) Overview
          • SAI concepts
          • SAI Quickstart
          • SAI FAQ
          • Create SAI index
          • Alter SAI index
          • Drop SAI index
          • Querying with SAI
          • Configure SAI indexes
          • Monitor SAI indexes
          • SAI write path and read path
        • Secondary indexes (2i) overview
          • Secondary index (2i) concepts
          • When to use an index
          • Building and maintaining secondary indexes (2i)
          • Create a secondary index (2i)
            • Create a 2i on a collection column
          • Check secondary index (2i) existence
          • Alter a secondary index (2i)
          • Drop a secondary index (2i)
        • Search indexes overview
          • Search index commands
          • Adjusting timeout for index management
          • Creating a search index
          • Configuring search index joins
          • Joining cores
          • Reloading the search index
          • Removing a search index
          • Managing search index fields
            • Syntax for changing schema settings
            • Search index schema field types
            • Adding a new field type
            • Adding a column to the index
            • Indexing tuples and UDTs fields
              • Tuple configuration example
              • UDT configuration example
              • Nesting tuples and UDTs
              • Tuples and UDTs as CQL map values
            • Indexing map columns
            • Indexing a column for different analysis
            • Dropping columns from the index
          • Updating the index after data expires (TTL)
          • Inserting, updating, and deleting data
          • Search indexing examples
        • Search index examples
          • Search index examples
            • Creating a demo keyspace for tutorials
            • Indexing and querying polygons
      • Inserting and updating data
        • Insert simple data
        • Set column
        • List fields
        • Map fields
        • Tuple
        • User-defined type (UDT) column
        • Insert static data
        • UUID and timeuuid column
        • Date column
        • Time column
        • Timestamp column
        • Duration column
        • Update counter
        • Lightweight transactions (LWTs)
        • Inserting JSON formatted values
        • Batching inserts and updates
          • Batching inserts, updates, and deletes
          • Good use of BATCH statement
          • Misuse of BATCH statement
        • Importing data from a CSV file
        • Setting the time-to-live (TTL) for value
          • Inserting, updating, and deleting data
      • Deleting data
        • Deleting values from a column or entire row
        • Expiring data with time-to-live
        • Expiring data with TTL example
      • Querying and retrieving data
        • Using a SELECT statement
        • Restricting queries using WHERE clauses
          • Partition keys
          • Clustering columns
          • Retrieval using the IN keyword
          • Sorting and limiting results
        • Returning data from collection columns
        • Aggregating results
        • Using user-defined functions (UDF)
        • Applying user-defined aggregate (UDA) functions
          • Testing performance impact using tracing
        • Formatting query results as JSON
        • Retrieving the write timestamp
        • Using tracing
          • Tracing performance
        • DSE Search index querying
        • Search index filtering best practices
          • Search index syntax
          • search-index:si-query-syntax.adoc#siIndexedColumns
          • search-index:si-query-syntax.adoc#basicSolrSyntax
          • search-index:si-query-syntax.adoc#queriesJSON
          • search-index:si-query-syntax.adoc#siEscapeCharacters
        • Native CQL search queries
        • Identifying the partition key
        • Filtering on terms
          • Filtering on words, phrases, or substrings
          • Fuzzy search
        • UDT query examples
        • Querying a CQL collection set
        • Using date ranges in solr_query
        • Geospatial queries for Point and LineString
        • Spatial queries with polygons require JTS
        • Using dynamic fields
        • Limiting results and paging
        • Restricted query routing
    • Securing overview
      • Security quickstart
      • Using cqlsh with authentication
      • System tables for authentication and authorization
  • Cycling Examples
    • Setting up the cycling keyspace
    • User-defined types (UDTs)
      • basic_info
      • basic_info_withTTL
      • fullname
      • race
    • User-defined functions (UDFs) and user-defined aggregates (UDAs)
      • flog
      • left
      • average_state
      • average_final
      • Cycling user-defined aggregate team_average
    • Tables
      • birthday_list
      • calendar
      • comments
      • country_flag
      • cyclist_alt_stats
      • cyclist_base
      • cyclist_career_teams
      • cyclist_category
      • cyclist_emails
      • cyclist_expenses
      • cyclist_id
      • cyclist_name
      • cyclist_points
      • cyclist_races
      • cyclist_sponsors
      • cyclist_stats
      • cyclist_teams
      • events
      • popular_count
      • race_sponsors
      • race_starts
      • race_times
      • race_winners
      • rank_by_year_and_name
      • team_average
      • upcoming_calendar
    • Materialized views (MVs)
      • cyclist_by_age-mv
    • Search indexes
      • Search index examples
      • comments search index
    • SASI indexes
      • Index on cyclist_name
    • Access control
      • Cycling internal
    • Cycling queries
      • Aggregating cycling data
    • Reference
      • CQL quick reference
      • Understanding the CQL command syntax
      • Data types
        • Creating a blob column
        • Date, time, and timestamp format
        • Solr field type reference for DSE Search
      • Operators
      • CQL native functions
      • CQL native aggregates
      • System and virtual tables
        • System tables
          • Keyspace tables
          • Querying system tables
          • Functions, aggregates, and user types
        • Virtual tables
          • Virtual keyspaces and tables
          • Virtual tables for SAI indexes and SSTables
      • cqlsh (startup options)
        • Configuring a cqlshrc file
        • Starting cqlsh on a search node
      • CQL shell (cqlsh) reference
        • CAPTURE
        • CLEAR
        • CONSISTENCY
        • COPY TO
        • COPY FROM
        • DESCRIBE AGGREGATE
        • DESCRIBE CLUSTER
        • DESCRIBE SCHEMA
        • DESCRIBE FUNCTION
        • DESCRIBE INDEX
        • DESCRIBE KEYSPACE
        • DESCRIBE MATERIALIZED VIEW
        • DESCRIBE SEARCH INDEX
        • DESCRIBE TABLE
        • DESCRIBE TYPE
        • EXECUTE AS
        • EXPAND
        • EXIT
        • LOGIN
        • PAGING
        • SERIAL CONSISTENCY
        • SHOW
        • SOURCE
        • TIMING
        • TRACING
      • CQL commands
        • ALTER KEYSPACE
        • ALTER MATERIALIZED VIEW
        • ALTER ROLE
        • ALTER SEARCH INDEX CONFIG
        • ALTER SEARCH INDEX SCHEMA
        • ALTER TABLE
        • ALTER TYPE
        • ALTER USER (Deprecated)
        • BATCH
        • COMMIT SEARCH INDEX
        • CREATE AGGREGATE
        • CREATE CUSTOM INDEX
        • CREATE FUNCTION
        • CREATE INDEX
        • CREATE KEYSPACE
        • CREATE MATERIALIZED VIEW
        • CREATE ROLE
        • CREATE SEARCH INDEX
        • CREATE TABLE
        • CREATE TYPE
        • CREATE USER (Deprecated)
        • DELETE
        • DROP AGGREGATE
        • DROP FUNCTION
        • DROP INDEX
        • DROP KEYSPACE
        • DROP MATERIALIZED VIEW
        • DROP ROLE
        • DROP SEARCH INDEX
        • DROP TABLE
        • DROP TYPE
        • DROP USER (Deprecated)
        • GRANT
        • INSERT
        • LIST PERMISSIONS
        • LIST ROLES
        • LIST USERS (Deprecated)
        • REBUILD SEARCH INDEX
        • RELOAD SEARCH INDEX
        • RESTRICT
        • RESTRICT ROWS
        • REVOKE
        • SELECT
        • TRUNCATE
        • UNRESTRICT
        • UNRESTRICT ROWS
        • UPDATE
        • USE
  • CQL for DSE
  • Developing
  • Inserting and updating data
  • Batching inserts and updates
  • Good use of BATCH statement
Edit this Page

Good use of BATCH statement

Batch operations can be beneficial, as shown in the following examples. The examples use the table cyclist_expenses:

CREATE TABLE IF NOT EXISTS cycling.cyclist_expenses ( 
  cyclist_name text, 
  balance float STATIC, 
  expense_id int, 
  amount float, 
  description text, 
  paid boolean, 
  PRIMARY KEY (cyclist_name, expense_id) 
);

Note that balance is STATIC.

If there are two different tables in the same keyspace and the two tables have the same partition key, this scenario is considered a single partition batch. There will be a single mutation for each table. This happens because the two tables could have different columns, even though the keyspace and partition are the same. Batches allow a caller to bundle multiple operations into a single batch request. All the operations are performed by the same coordinator. The best use of a batch request is for a single partition in multiple tables in the same keyspace. Also, batches provide a guarantee that mutations will be applied in a particular order.

Single partition batch

  • The first INSERT in the BATCH statement sets the balance to zero. The next two statements insert an expense_id and change the balance value. All the INSERT and UPDATE statements in this batch write to the same partition, keeping the latency of the write operation low.

    BEGIN BATCH
    
      INSERT INTO cycling.cyclist_expenses (
        cyclist_name, balance
      ) VALUES (
        'Vera ADRIAN', 0
      ) IF NOT EXISTS;
    
      INSERT INTO cycling.cyclist_expenses (
        cyclist_name, expense_id, amount, description, paid
      ) VALUES (
        'Vera ADRIAN', 1, 7.95, 'Breakfast', false
      );
    
    APPLY BATCH;

    This batching example includes conditional updates combined with using static columns. Recall that single partition batches are not logged.

    It would be reasonable to expect that an UPDATE to the balance could be included in this BATCH statement:

    +

    UPDATE cycling.cyclist_expenses
    SET balance = -7.95
    WHERE cyclist_name = 'Vera ADRIAN'
    IF balance = 0;

    + However, it is important to understand that all the statements processed in a BATCH statement timestamp the records with the same value. The operations may not perform in the order listed in the BATCH statement. The UPDATE might be processed BEFORE the first INSERT that sets the balance value to zero, allowing the conditional to be met.

    An acknowledgement of a batch statement is returned if the batch operation is successful.

     [applied]
    -----------
          True

    The resulting table will only have one record so far.

     cyclist_name | expense_id | balance | amount | description | paid
    --------------+------------+---------+--------+-------------+-------
      Vera ADRIAN |          1 |       0 |   7.95 |   Breakfast | False
    
    (1 rows)
  • The balance can be adjusted separately with an UPDATE statement. Now the balance will reflect that breakfast was unpaid.

    UPDATE cycling.cyclist_expenses
    SET balance = -7.95
    WHERE cyclist_name = 'Vera ADRIAN'
    IF balance = 0;
     cyclist_name | expense_id | balance | amount | description | paid
    --------------+------------+---------+--------+-------------+-------
      Vera ADRIAN |          1 |   -7.95 |   7.95 |   Breakfast | False
    
    (1 rows)
  • The table cyclist_expenses stores records about each purchase by a cyclist and includes the running balance of all the cyclist’s purchases. Because the balance is static, all purchase records for a cyclist have the same running balance. This BATCH statement inserts expenses for two more meals changes the balance to reflect that breakfast and dinner were unpaid.

    BEGIN BATCH
    
      INSERT INTO cycling.cyclist_expenses (
        cyclist_name, expense_id, amount, description, paid
      ) VALUES (
        'Vera ADRIAN', 2, 13.44, 'Lunch', true
      );
    
      INSERT INTO cycling.cyclist_expenses (
        cyclist_name, expense_id, amount, description, paid
      ) VALUES (
        'Vera ADRIAN', 3, 25.00, 'Dinner', false
      );
    
      UPDATE cycling.cyclist_expenses
      SET balance = -32.95
      WHERE cyclist_name = 'Vera ADRIAN'
      IF balance = -7.95;
    
    APPLY BATCH;
     cyclist_name | expense_id | balance | amount | description | paid
    --------------+------------+---------+--------+-------------+-------
      Vera ADRIAN |          1 |  -32.95 |   7.95 |   Breakfast | False
      Vera ADRIAN |          2 |  -32.95 |  13.44 |       Lunch |  True
      Vera ADRIAN |          3 |  -32.95 |     25 |      Dinner | False
    
    (3 rows)
  • Finally, the cyclist pays off all outstanding bills and the balance of the account goes to zero.

    BEGIN BATCH
    
      UPDATE cycling.cyclist_expenses
      SET balance = 0
      WHERE cyclist_name = 'Vera ADRIAN'
      IF balance = -32.95;
    
      UPDATE cycling.cyclist_expenses
      SET paid = true
      WHERE cyclist_name = 'Vera ADRIAN'
      AND expense_id = 1 IF paid = false;
    
      UPDATE cycling.cyclist_expenses
      SET paid = true
      WHERE cyclist_name = 'Vera ADRIAN'
      AND expense_id = 3
      IF paid = false;
    
    APPLY BATCH;
     cyclist_name | expense_id | balance | amount | description | paid
    --------------+------------+---------+--------+-------------+------
      Vera ADRIAN |          1 |       0 |   7.95 |   Breakfast | True
      Vera ADRIAN |          2 |       0 |  13.44 |       Lunch | True
      Vera ADRIAN |          3 |       0 |     25 |      Dinner | True
    
    (3 rows)

    Because the column is static, you can provide only the partition key when updating the data. To update a non-static column, you would also have to provide a clustering key. Using batched conditional updates, you can maintain a running balance. If the balance were stored in a separate table, maintaining a running balance would not be possible because a batch having conditional updates cannot span multiple partitions.

Multiple partition logged batch

  • Another example is using BATCH to perform a multiple partition insert that involves writing the same data to two related tables that must be synchronized. The following example modifies multiple partitions, which in general is to be avoided, but the batch only contains two statements:

    BEGIN BATCH
    
      INSERT INTO cycling.cyclist_expenses (
        cyclist_name, expense_id, amount, description, paid
      ) VALUES (
        'John SMITH', 3, 15.00, 'Lunch', false
      );
    
      INSERT INTO cycling.cyclist_name (
        id, lastname, firstname
      ) VALUES (
        6ab09bec-e68e-48d9-a5f8-97e6fb4c9b12, 'SMITH', 'John'
      );
    
    APPLY BATCH;

    Another common use for this type of batch operation is updating usernames and passwords.

Batching inserts, updates, and deletes Misuse of BATCH statement

General Inquiries: +1 (650) 389-6000 info@datastax.com

© DataStax | Privacy policy | Terms of use

DataStax, Titan, and TitanDB are registered trademarks of DataStax, Inc. and its subsidiaries in the United States and/or other countries.

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries.

Kubernetes is the registered trademark of the Linux Foundation.

landing_page landingpage