Data Manipulation in Cassandra

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:

E_id E_name E_city
908 Rahul Rana Noida
909 Null Meerut

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.