Data Manipulation in Cassandra

Data manipulation is the handling of data in a database server. The data is handled and manipulated by using different types of commands which are present in a query. It helps us perform different types of operations on databases. There are four data manipulation commands.

Insert

It is used to insert data into the table. It can be used for adding more data to a pre−existing table. The motive is to insert the data into the table by using an INSERT statement.

Syntax

INSERT INTO table_name(parameters) VALUES (values_for_parameter);

Here,

• table_name is the name of the table on which the operation is to be performed.

• Parameters are columns to be inserted like name, id, salary, etc.

Example

This example consists of two cases to insert data into the table using the INSERT command.

Case 1

Normal insertion of data

Algorithm

• Step1: Create a table

• Step 2: Insert values into the table using INSERT

• Step3: Select the table to show output and to see the values that get inserted

CREATE TABLE student(id int,name char(50),roll_no. (50),branch char);#table created
INSERT INTO student(id,name,roll_no,branch) VALUES (1,aman,20,cs);#row 1 inserted
INSERT INTO student(id,name,roll_no,branch) VALUES (2,naman,21,civil);#row 2 inserted
INSERT INTO student

Output

id	name	roll_no. 	 branch
1	aman	 20	         cs
2	naman	 21	         civil
3	raman	 22	         bao

Case 2

Insert data using JSON

Algorithm

• Step1: Create a table

• Step2: Use JSON to insert values into the table

• Step3: Select the table to show the output

Example

CREATE TABLE student(id int,name char(50),roll_no. (50),branch char);#table created
INSERT INTO student JSON ("id":"1","name":"aman","roll_no":"20","branch":"cs");#json is used 

Output

id	name	 roll_no. 	branch
1	aman	 20	        cs

Update

It is used to update the data in the existing table. The data is already present inside a table and if we need to change or update any of the data, then the UPDATE statement is used.

Syntax

UPDATE table_name USING update_parameter 
SET values_to_update WHERE condition;

Here,

• table_name is the name of the table on which the operation will be performed

• update_parameter refers to the timestamp

• values_to_update refers to the values which are to be updated

• conditions are there to select the row on which the value will get updated

Example

This example covers the update command which is used to update the values in a given table

Algorithm

• Step1: Use update to update and provide table name

• Step2: Use ttl(time to live)

• Step3: Set values to get updated

• Step4: Provide condition of where to perform updation

• Step5: Select the table to show the output

Input

id	name	 roll_no. 	branch
1	aman	 20	        cs
2	naman	 21	        civil
3	raman	 22	        bao

Example

UPDATE student #update operation is to be performed
USING TTL 400# time to live is used
SET name='monu',roll_no=25#set updated values
WHERE id=1;#condition regarding where to update
SELECT * FROM student;#Select table to show output

Output

id	name	  roll_no. 	branch
1	monu	  25	        cs
2	naman	  21	        civil
3	raman	  22	        bao

Delete

It is used to delete any data from the table. Works only when the data is already present in the data.

Syntax

DELETE FROM table_name USING update_parameter
WHERE condition;

Algorithm

• Step1: Use delete

• Step2: Provide table name

• Step3: Use timestamp

• Step4: Provide condition of where to perform the deletion

• Step5: Select the table to show the output

Input

id	name	  roll_no. 	branch
1	monu	  25	        cs
2	naman	  21	        civil
3	raman	  22	        bao

Example

DELETE FROM student#table on which data deletion will occur
USING TIMESTAMP 234334 #timestamp is used
WHERE id=1;#condition condition regarding where to delete
SELECT * FROM student;#Select table to show output

Output

id	name	  roll_no. 	branch
2	naman	  21	    civil
3	raman	  22	     bao

Batch

It is used to execute multiple INSERT, UPDATE, and DELETE in a single statement.

Syntax

BEGIN [ UNLOGGED | COUNTER ] BATCH 
modification_statement 
APPLY BATCH;

Here,

• unlogged is used to skip batchlog i. e. failed batch might leave a patch.

• counter is for batched counter updates.

• modification statements cover insert, update, and delete statements

Example

This example consists of a batch followed by insert, update, and delete statements.

Algorithm

• Step1: Create a table

• Step2: begin batch

• Step3: Perform operations

• Step4: Apply batch to operations

• Step5: Select the table to show the output

CREATE TABLE student(id int,name char(50),roll_no. (50),branch char);#table created
BEGIN BATCH#batch started
INSERT INTO student(id,name,roll_no,branch) VALUES (1,aman,20,cs);#value inserted
INSERT INTO student(id,name,roll_no,branch) VALUES (2,naman,21,civil);#value inserted
UPDATE student SET name='monu',roll_no=25 WHERE id=1;#value updated
DELETE name FROM student WHERE id=1;#value deleted
APPLY BATCH;#batch applied to above operations
SELECT * FROM student;#Select table to show output

Output

id	name	 roll_no. 	branch
1	null	 25	        cs
2	naman	 21	        civil

Conclusion

Data manipulation consists of four commands. The first one is INSERT which helps in data insertion by having two cases, one is the normal insertion, and the other uses JSON to insert the data. The second is an update which is used to update the data in the database. The third is delete which is used to delete data. Fourth is batch which combines insert, update, and delete to make it a single statement.