Skip to content
Related Articles

Related Articles

Improve Article
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_idE_nameE_city
    107AshishDelhi
    110RanaNoida



  • 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_idE_nameE_city
    107AshishDelhi

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_idE_nameE_city
107Ashish RanaMumbai
110RanaNoida

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_idE_nameE_city
    110RanaNoida

  • Example-2:
    DELETE E_city 
    FROM Employee_info
    WHERE E_id IN (107, 110); 

    Output:

    E_idE_name
    107Ashish Rana
    110Rana

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_idE_nameE_city
908Rahul RanaNoida
909NullMeerut

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
Recommended Articles
Page :