Insert Select Update Delete data in Cassandra using cqlsh

Connect with

Apache Cassandra
This post is demonstrating , how to insert/create, select, update and delete data in Cassandra using cqlsh? How to perform CRUD (Create, Read, Update and Delete ) Operation in Cassandra using cqlsh is straight forward and siple.

I’,m 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 Cassandra and how to create table in Casasndra? , please visit my earlier post what is Keyspace and Table in Cassandra

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

Insert Data into Table

To write a value, use the INSERT command:

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

Count Added Rows in Table

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)

Get Added Data

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)

Delete Column from Table

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)

Update Row(s)

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)

Delete Row(s) from Table

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)

Truncate table Data

If we really want to clean up after 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;

Drop table Data

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

cqlsh:user_keyspace> DROP TABLE user;

Reference

Apache Cassandra official Site

Your suggestions or Comments are welcome to improve this post.


Connect with

Leave a Reply

Your email address will not be published. Required fields are marked *