Cassandra Query Language(CQL): Read, Insert, Update, Delete data

This tutorial completely explains the Cassandra Query Language CQL  and step by step process how to,

1. Define Scheme in cassandra nosql db
2. Read records from Cassandra
3. Insert new records in Cassandra.
4. Update an existing record in Cassandra.
5. Delete record from Cassandra nosql database.

By default, Cassandra Query Language (CQL) shell comes along with Cassandra distribution. Through which user can communicate with Cassandra nosql database. By using CQL shell, user can execute all CQL Commands.

Start Cassandra

Before going to start the cql shell make sure that Cassandra is already installed and in running condition in your machine.

If Cassandra service is not running in your machine then follow the below mentioned steps to start the Cassandra service.

You can download Cassandra from here.

Open the cmd in administrative mode. And go to cassandra installation directory. Then write Cassandra and press enter. 

When you execute the following command, It will take few seconds to start.

Start CQL (Cassandra Query Language) Shell:

If you have successfully executed the above commands then you are ready to start your Cassandra CQL shell.

Go to the specified path and write cqlsh and hit enter to start the Casandra Query language shell.

Cassandra Read Data

Data Retrieval in Cassandra is a sensitive issue. The columns are filtered only by creating index on non-primary key columns.

Syntax for Cassandra Select Query

Select ColumnNames from KeyspaceName.TableName Where ColumnName1=Column1Value AND
ColumnName2=Column2Value AND
.
.
.


Execution of select query from Cassandra nosql

• Here is the screenshot that shows all data from Student table (in University Keyspace) without data filtration.

Only one record is present in student table so show only one row.

Cassandra Insert Data

Data Insertion in Cassandra is same as you insert some data in traditional relational databases.  ‘Insert into’ command is used for this purpose. it writes data in Cassandra table in row form. Cassandra only stores those columns that are specified by the user in query. Primary key column is necessary all others can be left null.

Syntax for Cassandra Insert query

Insert into KeyspaceName.TableName(ColumnName1, ColumnName2, ColumnName3 . . . .)
Values (Column1Value, Column2Value, Column3Value . . . .)


Execution of Insert Query

Here is the screenshot of Insert into command that Insert one record in table student within keyspace (Database in RDMS) University. After Inserting I have applied the select command. This shows the recently inserted record.

After successful completion of Insert Command, One student record with StudentId 1, StudentName Obama and Degree MSCS is inserted in table Student.

Upsert Data Concept in Cassandra

Cassandra does upsert with your Insert into Command. Upsert data means that it insert a row if primary key does not exist already in table otherwise if primary key already exists, Cassandra will update that row instead of inserting new row.

Cassandra Update Data Query

Cassandra ‘Update’ Command is used to update the already existing records in Cassandra tables. If there is no output generated by cassandra update command it means that the record are successfully updated. Else in other case it throws some kind of exceptions. Specify the new Column values in ‘Set’ clause and records are filtered with ‘Where’ clause whom you want to update.

Syntax for Cassandra Update Query

Update KeyspaceName.TableName
Set ColumnName1=new Column1Value,
ColumnName2=new Column2Value,
ColumnName3=new Column3Value,
.
.
.
Where ColumnName=ColumnValue


Execution of Cassandra Update Query

In the below Example I have updated the name of student of same record which we have already inserted in DB in our Insert Command part. And set the name of student from Obama to Michel Obama. Here is the snapshot that shows the database state before updating data.

Here is the screenshot of the executed command ‘Update’ that update the record in the Student table. In first select query it shows the state of table before update and in 2^nd select statement it shows the state of table after the update query. After successful execution of update command student name will be changed from ‘Obama’ to ‘Michel Obama’ with StudentId 1.

Cassandra Delete Data Query

Cassandra ‘Delete from’ command is used to delete an entire record from the table. When you execute the delete query, data is not deleted immediately; infect Instead of immediate deletion data is marked with a tombstone flag and removed on the time of compaction.

Syntax Cassandra Delete Query

Delete from KeyspaceName.TableName
Where ColumnName1=ColumnValue


The above syntax will delete one or more rows depend upon data filtration in where clause.

Syntax for Cassandra Delete Column

The below command is used to delete some specific columns from the table.

Delete ColumnNames from KeyspaceName.TableName
Where ColumnName1=ColumnValue

Execution of Cassandra Delete Query

Here is the snapshot that shows the current database state before deleting data. In this state there are two records in table. Then I have applied the delete query it removes record where the StudentId =2.

This is the screenshot of the delete command that remove one row from the table Student. After successful execution, one rows will be deleted from the table where StudentId value is 2.

See Also:

Download and Install Cassandra on Windows

Step by step process to Cassandra Create Table Alter Drop And Truncate

Learn About Cassandra Data Model

Cassandra Architecture

Summary:

This Article explains the step by step procedure that how to connect the Cassandra Query language (CQL) Shell and how to execute the different commands on the shell. Like how to execute the select statement? How to execute Insert into command? How to execute the Update Command? How to execute the Delete command in Cassandra? Cassandra Query Language (CQL) is very easy to use Scripting Language as well as easy to configure like in relational databases.