Let us assume there is a table called emp with columns (emp_id, emp_name, emp_city, emp_phone, emp_sal) and you have to insert the following data into the emp table.

emp_id	emp_name	emp_city	emp_phone	emp_sal
1	ram	Hyderabad	9848022338	50000
2	robin	Hyderabad	9848022339	40000
3	rahman	Chennai	9848022330	45000

Use the commands given below to fill the table with required data.

cqlsh:tecklearn> INSERT INTO emp (emp_id, emp_name, emp_city,

emp_phone, emp_sal) VALUES(1,’ram’, ‘Hyderabad’, 9848022338, 50000);

cqlsh:tecklearn> INSERT INTO emp (emp_id, emp_name, emp_city,

emp_phone, emp_sal) VALUES(2,’robin’, ‘Hyderabad’, 9848022339, 40000);

cqlsh:tecklearn> INSERT INTO emp (emp_id, emp_name, emp_city,

emp_phone, emp_sal) VALUES(3,’rahman’, ‘Chennai’, 9848022330, 45000);

Verification

After inserting data, use SELECT statement to verify whether the data has been inserted or not. If you verify the emp table using SELECT statement, it will give you the following output.

cqlsh:tecklearn> SELECT * FROM emp;

emp_id | emp_city | emp_name | emp_phone | emp_sal

——–+———–+———-+————+———

1 | Hyderabad | ram | 9848022338 | 50000

2 | Hyderabad | robin | 9848022339 | 40000

3 | Chennai | rahman | 9848022330 | 45000

(3 rows)

Here you can observe the table has populated with the data we inserted.

Creating Data using Java API

You can create data in a table using the execute() method of Session class. Follow the steps given below to create data in a table using java API.

Step1: Create a Cluster Object

Create an instance of Cluster.builder class of com.datastax.driver.core package as shown below.

//Creating Cluster.Builder object

Cluster.Builder builder1 = Cluster.builder();

Add a contact point (IP address of the node) using the addContactPoint() method of Cluster.Builder object. This method returns Cluster.Builder.

//Adding contact point to the Cluster.Builder object

Cluster.Builder builder2 = build.addContactPoint(“127.0.0.1”);

Using the new builder object, create a cluster object. To do so, you have a method called build() in the Cluster.Builder class. The following code shows how to create a cluster object.

//Building a cluster

Cluster cluster = builder.build();

You can build a cluster object using a single line of code as shown below.

Cluster cluster = Cluster.builder().addContactPoint(“127.0.0.1”).build();

Step 2: Create a Session Object

Create an instance of Session object using the connect() method of Cluster class as shown below.

Session session = cluster.connect( );

This method creates a new session and initializes it. If you already have a keyspace, then you can set it to the existing one by passing the KeySpace name in string format to this method as shown below.

Session session = cluster.connect(“ Your keyspace name ” );

Here we are using the KeySpace called tp. Therefore, create the session object as shown below.

Session session = cluster.connect(“ tp” );

Step 3: Execute Query

You can execute CQL queries using the execute() method of Session class. Pass the query either in string format or as a Statement class object to the execute() method. Whatever you pass to this method in string format will be executed on the cqlsh.

In the following example, we are inserting data in a table called emp. You have to store the query in a string variable and pass it to the execute() method as shown below.

String query1 = “INSERT INTO emp (emp_id, emp_name, emp_city, emp_phone, emp_sal)

VALUES(1,’ram’, ‘Hyderabad’, 9848022338, 50000);” ;

String query2 = “INSERT INTO emp (emp_id, emp_name, emp_city, emp_phone, emp_sal)

VALUES(2,’robin’, ‘Hyderabad’, 9848022339, 40000);” ;

String query3 = “INSERT INTO emp (emp_id, emp_name, emp_city, emp_phone, emp_sal)

VALUES(3,’rahman’, ‘Chennai’, 9848022330, 45000);” ;

session.execute(query1);

session.execute(query2);

session.execute(query3);

Given below is the complete program to insert data into a table in Cassandra using Java API.

import com.datastax.driver.core.Cluster;

import com.datastax.driver.core.Session;

public class Create_Data {

public static void main(String args[]){

//queries

String query1 = “INSERT INTO emp (emp_id, emp_name, emp_city, emp_phone, emp_sal)”

+ ” VALUES(1,’ram’, ‘Hyderabad’, 9848022338, 50000);” ;

String query2 = “INSERT INTO emp (emp_id, emp_name, emp_city,

emp_phone, emp_sal)”

+ ” VALUES(2,’robin’, ‘Hyderabad’, 9848022339, 40000);” ;

String query3 = “INSERT INTO emp (emp_id, emp_name, emp_city, emp_phone, emp_sal)”

+ ” VALUES(3,’rahman’, ‘Chennai’, 9848022330, 45000);” ;

//Creating Cluster object

Cluster cluster = Cluster.builder().addContactPoint(“127.0.0.1”).build();

//Creating Session object

Session session = cluster.connect(“tp”);

//Executing the query

session.execute(query1);

session.execute(query2);

session.execute(query3);

System.out.println(“Data created”);

}

Save the above program with the class name followed by .java, browse to the location where it is saved. Compile and execute the program as shown below.

$javac Create_Data.java

$java Create_Data

Under normal conditions, it should produce the following output −

Data created

Cassandra – Update Data

Updating Data in a Table

UPDATE is the command used to update data in a table. The following keywords are used while updating data in a table −

Where − This clause is used to select the row to be updated.
Set − Set the value using this keyword.
Must − Includes all the columns composing the primary key.

While updating rows, if a given row is unavailable, then UPDATE creates a fresh row. Given below is the syntax of UPDATE command −

UPDATE <tablename>

SET <column name> = <new value>

<column name> = <value>….

WHERE <condition>

Example

Assume there is a table named emp. This table stores the details of employees of a certain company, and it has the following details −

emp_id	emp_name	emp_city	emp_phone	emp_sal
1	ram	Hyderabad	9848022338	50000
2	robin	Hyderabad	9848022339	40000
3	rahman	Chennai	9848022330	45000

Let us now update emp_city of robin to Delhi, and his salary to 50000. Given below is the query to perform the required updates.

cqlsh:tecklearn> UPDATE emp SET emp_city=’Delhi’,emp_sal=50000

WHERE emp_id=2;

Verification

Use SELECT statement to verify whether the data has been updated or not. If you verify the emp table using SELECT statement, it will produce the following output.

cqlsh:tecklearn> select * from emp;

emp_id | emp_city | emp_name | emp_phone | emp_sal

——–+———–+———-+————+———

1 | Hyderabad | ram | 9848022338 | 50000

2 | Delhi | robin | 9848022339 | 50000

3 | Chennai | rahman | 9848022330 | 45000

(3 rows)

Here you can observe the table data has got updated.

Updating Data using Java API

You can update data in a table using the execute() method of Session class. Follow the steps given below to update data in a table using Java API.

Step1: Create a Cluster Object

Create an instance of Cluster.builder class of com.datastax.driver.core package as shown below.

//Creating Cluster.Builder object

Cluster.Builder builder1 = Cluster.builder();

Add a contact point (IP address of the node) using the addContactPoint() method of Cluster.Builder object. This method returns Cluster.Builder.

//Adding contact point to the Cluster.Builder object

Cluster.Builder builder2 = build.addContactPoint(“127.0.0.1”);

Using the new builder object, create a cluster object. To do so, you have a method called build() in the Cluster.Builder class. Use the following code to create the cluster object.

//Building a cluster

Cluster cluster = builder.build();

You can build the cluster object using a single line of code as shown below.

Cluster cluster = Cluster.builder().addContactPoint(“127.0.0.1”).build();

Step 2: Create a Session Object

Create an instance of Session object using the connect() method of Cluster class as shown below.

Session session = cluster.connect( );

Session session = cluster.connect(“ Your keyspace name”);

Here we are using the KeySpace named tp. Therefore, create the session object as shown below.

Session session = cluster.connect(“tp”);

Step 3: Execute Query

You can execute CQL queries using the execute() method of Session class. Pass the query either in string format or as a Statement class object to the execute() method. Whatever you pass to this method in string format will be executed on the cqlsh.

In the following example, we are updating the emp table. You have to store the query in a string variable and pass it to the execute() method as shown below:

String query = “ UPDATE emp SET emp_city=’Delhi’,emp_sal=50000

WHERE emp_id = 2;” ;

Given below is the complete program to update data in a table using Java API.

import com.datastax.driver.core.Cluster;

import com.datastax.driver.core.Session;

public class Update_Data {

public static void main(String args[]){

//query

String query = ” UPDATE emp SET emp_city=’Delhi’,emp_sal=50000″

//Creating Cluster object

Cluster cluster = Cluster.builder().addContactPoint(“127.0.0.1”).build();

//Creating Session object

Session session = cluster.connect(“tp”);

//Executing the query

session.execute(query);

System.out.println(“Data updated”);

}

Save the above program with the class name followed by .java, browse to the location where it is saved. Compile and execute the program as shown below.

$javac Update_Data.java

$java Update_Data

Under normal conditions, it should produce the following output −

Data updated

Cassandra – Read Data

Reading Data using Select Clause

SELECT clause is used to read data from a table in Cassandra. Using this clause, you can read a whole table, a single column, or a particular cell. Given below is the syntax of SELECT clause.

SELECT FROM <tablename>

Example

Assume there is a table in the keyspace named emp with the following details −

emp_id	emp_name	emp_city	emp_phone	emp_sal
1	ram	Hyderabad	9848022338	50000
2	robin	null	9848022339	50000
3	rahman	Chennai	9848022330	50000
4	rajeev	Pune	9848022331	30000

The following example shows how to read a whole table using SELECT clause. Here we are reading a table called emp.

cqlsh:tecklearn> select * from emp;

emp_id | emp_city | emp_name | emp_phone | emp_sal

——–+———–+———-+————+———

1 | Hyderabad | ram | 9848022338 | 50000

2 | null | robin | 9848022339 | 50000

3 | Chennai | rahman | 9848022330 | 50000

4 | Pune | rajeev | 9848022331 | 30000

(4 rows)

Reading Required Columns

The following example shows how to read a particular column in a table.

cqlsh:tecklearn> SELECT emp_name, emp_sal from emp;

emp_name | emp_sal

———-+———

ram | 50000

robin | 50000

rajeev | 30000

rahman | 50000

(4 rows)

Where Clause

Using WHERE clause, you can put a constraint on the required columns. Its syntax is as follows −

SELECT FROM <table name> WHERE <condition>;

Note − A WHERE clause can be used only on the columns that are a part of primary key or have a secondary index on them.

In the following example, we are reading the details of an employee whose salary is 50000. First of all, set secondary index to the column emp_sal.

cqlsh:tecklearn> CREATE INDEX ON emp(emp_sal);

cqlsh:tecklearn> SELECT * FROM emp WHERE emp_sal=50000;

emp_id | emp_city | emp_name | emp_phone | emp_sal

——–+———–+———-+————+———

1 | Hyderabad | ram | 9848022338 | 50000

2 | null | robin | 9848022339 | 50000

3 | Chennai | rahman | 9848022330 | 50000

Reading Data using Java API

You can read data from a table using the execute() method of Session class. Follow the steps given below to execute multiple statements using batch statement with the help of Java API.

Step1: Create a Cluster Object

Create an instance of Cluster.builder class of com.datastax.driver.core package as shown below.

//Creating Cluster.Builder object

Cluster.Builder builder1 = Cluster.builder();

Add a contact point (IP address of the node) using the addContactPoint() method of Cluster.Builder object. This method returns Cluster.Builder.

//Adding contact point to the Cluster.Builder object

Cluster.Builder builder2 = build.addContactPoint( “127.0.0.1” );

Using the new builder object, create a cluster object. To do so, you have a method called build() in the Cluster.Builder class. Use the following code to create the cluster object.

//Building a cluster

Cluster cluster = builder.build();

You can build the cluster object using a single line of code as shown below.

Cluster cluster = Cluster.builder().addContactPoint(“127.0.0.1”).build();

Step 2: Create a Session Object

Create an instance of Session object using the connect() method of Cluster class as shown below.

Session session = cluster.connect( );

Session session = cluster.connect(“Your keyspace name”);

Here we are using the KeySpace called tp. Therefore, create the session object as shown below.

Session session = cluster.connect(“tp”);

Step 3: Execute Query

You can execute CQL queries using execute() method of Session class. Pass the query either in string format or as a Statement class object to the execute() method. Whatever you pass to this method in string format will be executed on the cqlsh.

In this example, we are retrieving the data from emp table. Store the query in a string and pass it to the execute() method of session class as shown below.

String query = ”SELECT 8 FROM emp”;

session.execute(query);

Execute the query using the execute() method of Session class.

Step 4: Get the ResultSet Object

The select queries will return the result in the form of a ResultSet object, therefore store the result in the object of RESULTSET class as shown below.

ResultSet result = session.execute( );

Given below is the complete program to read data from a table.

import com.datastax.driver.core.Cluster;

import com.datastax.driver.core.ResultSet;

import com.datastax.driver.core.Session;

public class Read_Data {

public static void main(String args[])throws Exception{

//queries

String query = “SELECT * FROM emp”;

//Creating Cluster object

Cluster cluster = Cluster.builder().addContactPoint(“127.0.0.1”).build();

//Creating Session object

Session session = cluster.connect(“tecklearn”);

//Getting the ResultSet

ResultSet result = session.execute(query);

System.out.println(result.all());

}

Save the above program with the class name followed by .java, browse to the location where it is saved. Compile and execute the program as shown below.

$javac Read_Data.java

$java Read_Data

Under normal conditions, it should produce the following output −

[Row[1, Hyderabad, ram, 9848022338, 50000], Row[2, Delhi, robin,

9848022339, 50000], Row[4, Pune, rajeev, 9848022331, 30000], Row[3,

Chennai, rahman, 9848022330, 50000]]

Cassandra – Delete Data

Deleting Datafrom a Table

You can delete data from a table using the command DELETE. Its syntax is as follows −

DELETE FROM <identifier> WHERE <condition>;

Example

Let us assume there is a table in Cassandra called emp having the following data −

emp_id	emp_name	emp_city	emp_phone	emp_sal
1	ram	Hyderabad	9848022338	50000
2	robin	Hyderabad	9848022339	40000
3	rahman	Chennai	9848022330	45000

The following statement deletes the emp_sal column of last row −

cqlsh:tecklearn> DELETE emp_sal FROM emp WHERE emp_id=3;

Verification

Use SELECT statement to verify whether the data has been deleted or not. If you verify the emp table using SELECT, it will produce the following output −

cqlsh:tecklearn> select * from emp;

emp_id | emp_city | emp_name | emp_phone | emp_sal

——–+———–+———-+————+———

1 | Hyderabad | ram | 9848022338 | 50000

2 | Delhi | robin | 9848022339 | 50000

3 | Chennai | rahman | 9848022330 | null

(3 rows)

Since we have deleted the salary of Rahman, you will observe a null value in place of salary.

Deleting an Entire Row

The following command deletes an entire row from a table.

cqlsh:tecklearn> DELETE FROM emp WHERE emp_id=3;

Verification

Use SELECT statement to verify whether the data has been deleted or not. If you verify the emp table using SELECT, it will produce the following output −

cqlsh:tecklearn> select * from emp;

emp_id | emp_city | emp_name | emp_phone | emp_sal

——–+———–+———-+————+———

1 | Hyderabad | ram | 9848022338 | 50000

2 | Delhi | robin | 9848022339 | 50000

(2 rows)

Since we have deleted the last row, there are only two rows left in the table.

Deleting Data using Java API

You can delete data in a table using the execute() method of Session class. Follow the steps given below to delete data from a table using java API.

Step1: Create a Cluster Object

Create an instance of Cluster.builder class of com.datastax.driver.core package as shown below.

//Creating Cluster.Builder object

Cluster.Builder builder1 = Cluster.builder();

Add a contact point (IP address of the node) using the addContactPoint() method of Cluster.Builder object. This method returns Cluster.Builder.

//Adding contact point to the Cluster.Builder object

Cluster.Builder builder2 = build.addContactPoint( “127.0.0.1” );

Using the new builder object, create a cluster object. To do so, you have a method called build() in the Cluster.Builder class. Use the following code to create a cluster object.

//Building a cluster

Cluster cluster = builder.build();

You can build the cluster object using a single line of code as shown below.

Cluster cluster = Cluster.builder().addContactPoint(“127.0.0.1”).build();

Step 2: Create a Session Object

Create an instance of Session object using the connect() method of Cluster class as shown below.

Session session = cluster.connect();

Session session = cluster.connect(“ Your keyspace name ”);

Here we are using the KeySpace called tp. Therefore, create the session object as shown below.

Session session = cluster.connect(“tp”);

Step 3: Execute Query

In the following example, we are deleting data from a table named emp. You have to store the query in a string variable and pass it to the execute() method as shown below.

String query1 = ”DELETE FROM emp WHERE emp_id=3; ”;

session.execute(query);

Given below is the complete program to delete data from a table in Cassandra using Java API.

import com.datastax.driver.core.Cluster;

import com.datastax.driver.core.Session;

public class Delete_Data {

public static void main(String args[]){

//query

String query = “DELETE FROM emp WHERE emp_id=3;”;

//Creating Cluster object

Cluster cluster = Cluster.builder().addContactPoint(“127.0.0.1”).build();

//Creating Session object

Session session = cluster.connect(“tp”);

//Executing the query

session.execute(query);

System.out.println(“Data deleted”);

}

Save the above program with the class name followed by .java, browse to the location where it is saved. Compile and execute the program as shown below.

$javac Delete_Data.java

$java Delete_Data

Under normal conditions, it should produce the following output −

Data deleted

So, this brings us to the end of blog. This Tecklearn ‘How to perform CRUD Operations in Table in Apache Cassandra’ helps you with commonly asked questions if you are looking out for a job in Cassandra and No-SQL Database Domain.

If you wish to learn HBase and build a career in Cassandra or No-SQL Database domain, then check out our interactive, Apache Cassandra Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/apache-cassandra-training/

Apache Cassandra Training

About the Course

Take your career to the next level as a certified Apache Cassandra developer by acquiring all the skills through our hands-on training sessions. Tecklearn’s Apache Cassandra Certification Training is designed by professionals as per the industry requirements and demands. This Cassandra Certification Training helps you to master the concepts of Apache Cassandra including Cassandra Architecture, its features, Cassandra Data Model, and its Administration. Our Cassandra certification training course lets you master the high availability NoSQL distributed database.

Why Should you take Apache Cassandra Training?

The average salary of a Software Engineer with Apache Cassandra skill is $120,500 per year. – Payscale.com
Cassandra is in use at Constant Contact, CERN, Comcast, eBay, GitHub, GoDaddy, Hulu, Instagram, Intuit, Netflix, Reddit, The Weather Channel, and over 1500 more companies that have large, active data sets.
Apache Cassandra is one of the most widely used NoSQL database. It offers features such as Fault Tolerance, Scalability, Flexible Data Storage and its efficient writes, which makes it the perfect database for various purposes.

What you will Learn in this Course?

Introduction to Big Data, and Cassandra

What is Big Data
Limitations of RDBMS
NoSQL and it’s Characteristics
CAP Theorem
Basic concepts of Cassandra
Features of Cassandra

Cassandra Data model, Installation and setup

Installation of Cassandra
Key concepts and deployment of non-relational database, column-oriented database, Data Model – column, column family

Cassandra Architecture

Explain the Architecture of Cassandra
Different Layers of Cassandra Architecture
Partitioning and Snitches
Explain Vnodes and How Read and Write Path works
Understand Compaction, Anti-Entropy and Tombstone
Describe Repairs in Cassandra

Deep Dive into Cassandra Database

Describe Different Data Types Used in Cassandra
Explain Collection Types
Describe What are CRUD Operations
Implement Insert, Select, Update and D elete of various elements
Implement Various Functions Used in Cassandra
Describe Importance of Roles and Indexing

Backup & Restore and Performance Tuning

Learn backup and restore functionality and its importance
Create a snapshot using Nodetool utility
Restore a snapshot
Understand how to choose the right balance of the following resources: memory, CPU, disks, number of nodes, and network.
Understand all the logs created by Cassandra
Explain the purpose of different log files
Configure the log files
Learn about Performance Tuning
Integration with Spark and Kafka

Advance Modelling

Rules of Cassandra data modelling
Modelling data around queries
Creating table for data queries

Deploying the IDE for Cassandra applications

Learning key drivers
Deploying the IDE for Cassandra applications and cluster connection
Data query implementation

Cassandra Administration

Understanding Node Tool Utility
Cluster management using Command Line Interface
Management and Monitoring using DataStax Ops Center

Cassandra API and Summarization

Cassandra client connectivity
Connection pool internals
Cassandra API
Features and concepts of Hector client
Thrift, JAVA code and Summarization

Got a question for us? Please mention it in the comments section and we will get back to you.

456