Insert Select Update Delete data in Cassandra using cqlsh

CRUD operations using cqlsh in Apache Cassandra. How to perform CRUD (Create, Read, Update and Delete ) Operation in Cassandra using cqlsh.

1. Overview of CRUD operations using CQLSH

I am going to demonstrate the following CRUD(Create/insert, Read/Select, Update, Delete) operations step by step in cqlsh in Apache Cassandra, the activities are:

1. insert data in table.
2. get inserted data from table.
3. update row(s) in table.
4. delete column(s) from row in a table
5. delete row(s) from table.

Assuming that we have already created the keyspace and table in cassandra via cqlsh utility tool. If you don’t know, how to create keyspace in Apache Cassandra, and how to create table in Casasndra? , please visit my these post what is Keyspace and Table in Cassandra.

create TABLE user ( user_id UUID, first_name varchar , last_name varchar, PRIMARY KEY (first_name));

2. Insert Data into Table in Cassandra

This is normal add data using sqlsh, to write a value, using INSERT command:

cqlsh:user_keyspace> INSERT INTO user (first_name, last_name )  VALUES ('ranjeet', 'jha');

3. Get count for Added Rows in Table in Cassandra

Here, we have created a new row with two columns for the key ‘ranjeet’, to store a set of related values. The column names are first_name and last_name. We can use the SELECT COUNT command to make sure that the row was written:

cqlsh:user_keyspace> SELECT  * FROM  user;

 first_name | last_name | user_id
------------+-----------+---------
    ranjeet |       jha |    null

(1 rows)

4. Get Added Data in Cassandra

Now that we know the data is there, let’s read it, using the SELECT command:

cqlsh:user_keyspace> SELECT * FROM user where first_name='ranjeet';

 first_name | last_name | user_id
------------+-----------+---------
    ranjeet |       jha |    null

(1 rows)

5. Delete Column from Table in Cassandra

You can delete a column using the DELETE command. Here we will delete the last_name column for the ‘ranjeet’ row key:

cqlsh:user_keyspace> DELETE last_name FROM user where first_name='ranjeet';

To make sure that it’s removed, we can query again:

                 
cqlsh:user_keyspace> SELECT * FROM user where first_name='ranjeet';

 first_name | last_name | user_id
------------+-----------+---------
    ranjeet |      null |    null

(1 rows)

6. Update Row(s) in Cassandra

UPDATE user SET last_name='kumar' where first_name='ranjeet';

Output:

cqlsh:user_keyspace> UPDATE user SET last_name='kumar' where first_name='ranjeet';
cqlsh:user_keyspace> SELECT * FROM user WHERE first_name='ranjeet';

 first_name | last_name | user_id
------------+-----------+---------
    ranjeet |     kumar |    null

(1 rows)

7. Delete Row(s) from Table in Cassandra

It’s the same command, but we don’t specify a column name:

cqlsh:user_keyspace> DELETE FROM user WHERE first_name='ranjeet';

To make sure that it’s removed, we can query again:

cqlsh:user_keyspace> SELECT * FROM user WHERE first_name='ranjeet';

(0 rows)

8. Truncate table Data in Cassandra

If we really want to clean up after the exercise, you can remove all data from the table using the TRUNCATE command, or delete the table schema with data using the DROP TABLE command.

Delete entire table data with TRUNCATE.

cqlsh:user_keyspace> TRUNCATE user;

9. Drop table Data in Cassandra

Drop table data with schema , no more table structure would be available after executing this.

cqlsh:user_keyspace> DROP TABLE user;

10. Reference

Apache Cassandra official Site
I hope you enjoyed this post of CRUD operations using CQLSH in Apache Cassandra, and you can visit Apache Cassandra tutorial for more blog post.
Your suggestions or comments are welcome to improve this post.