Data Manipulation in Cassandra

Last Updated : 01 Nov, 2019

In this article, we will describe the following DML commands in Cassandra which help us to insert, update, delete, query data, etc. In CQL there are following data manipulation command. Let’s discuss one by one.

1. Insert 
2. Update 
3. Delete
4. Batch

Let’s take an example:

Table Name: Employee_info

CREATE TABLE Employee_info
 (
  E_id int,
  E_name text,
  E_city text,
  PRIMARY KEY(E_id)
 );

DML commands:

Figure – Data Manipulation Commands

1. INSERT:
In column family, there are many rows which contain column which can be specified in advance by the user. But there is a situation when a user wants to add more column after creating a table then we can have used the data manipulation commands.’ INSERT’ keyword is used to insert data.

There are 2 format to insert data.

Format-1:

Syntax:

INSERT INTO table_name (field _name1, field_name2, field_name3…)
            VALUES ('field_value1', 'field_value2', 'field_value3'…)
            USING  update_parameter[optional] ;

For instance:

INSERT INTO Employee_info(E_id, E_name, E_city) 
            VALUES (107, ‘Ashish’, ‘Delhi’);
INSERT INTO Employee_info(E_id, E_name, E_city) 
       VALUES (110, ‘Rana’, ‘Noida’);

To Query data or read data used the following ‘SELECT’ query.

Select * 
from Employee_info;

Output:

E_id	E_name	E_city
107	Ashish	Delhi
110	Rana	Noida

Format-2: By using JSON we can also insert data.

Syntax:

INSERT INTO table_name JSON '{"field_name1":"field_value1", 
                                   "field_name2":"field_value2", 
                                   "field_name3":"field_value3", ...}'

For instance:

INSERT INTO Employee_info JSON ’(“E_id” :”107”, 
                                      “E_name” : “Ashish”, 
                                       “E_city” : “Delhi”)’ ;

Output: using JSON to Insert data into table.

E_id	E_name	E_city
107	Ashish	Delhi

2. UPDATE:
To update the existing data with new data then we can use the ‘UPDATE’ data manipulation command in Cassandra.

Syntax:

UPDATE table_name USING  update _parameter
       SET field_name1 = field_value1,
           field_name2 = field_value2,
           field_name3> = field_value3,
                    .....
WHERE field_name = field_value;

For instance:

UPDATE student USING TTL 500
SET E_name= ‘Ashish Rana’,
E_city= ‘Mumbai’,
WHERE E_id=107;

Output:

E_id	E_name	E_city
107	Ashish Rana	Mumbai
110	Rana	Noida

Note:
UPDATE and INSERT statement support the following Parameter.

1. TIMESTAMP
2. TTL (Time To Live)

DELETE and BATCH statement support the following Parameter.

1. TIMESTAMP

3. DELETE:
When user want to delete some existing data based on some condition then we can perform ‘DELETE’ data manipulation command in Cassandra.

Syntax:

DELETE table_name  
    USING  update _parameter
     ... 
    WHERE condition;

For instance:

Example-1:

DELETE FROM Employee_info 
            USING TIMESTAMP 1450002143
 WHERE E_id = 107;

Output:

E_id	E_name	E_city
110	Rana	Noida

Example-2:

DELETE E_city 
FROM Employee_info
WHERE E_id IN (107, 110);

Output:

E_id	E_name
107	Ashish Rana
110	Rana

4. BATCH:
It is used to execute multiple INSERT, UPDATE, and DELETE statements by grouping through a BATCH statement.

Syntax:

batch_statement ::=  BEGIN [ UNLOGGED | COUNTER ] BATCH
                         [ USING update_parameter ( AND update_parameter )* ]
                           modification_statement ( ';' modification_statement )*
                            APPLY BATCH
modification_statement ::=  insert_statement | update_statement | delete_statement

Source.

For instance:

BEGIN BATCH
   INSERT INTO users (E_id, E_name, E_city) 
          VALUES (908, 'Rahul Rana', 'Meerut');
   INSERT INTO users (E_id, E_name, E_city) 
          VALUES (909, 'Gaurav Rana', 'Meerut');   
   UPDATE Employee_info SET E_city = 'Noida' WHERE E_id = '908';
   DELETE E_name 
          FROM Employee_info 
          WHERE E_id = 909;
APPLY BATCH;

Output: