Inserting data using a CSV file in Cassandra

In this article, we will discuss how you can insert data into the table using a CSV file. And we will also cover the implementation with the help of examples. Let’s discuss it one by one.

Pre-requisite – Introduction to Cassandra

Introduction :
If you want to store data in bulk then inserting data from a CSV file is one of the nice ways. If you have data in a file so, you can directly insert your data into the database by using the COPY command in Cassandra. It will be very useful when you have a very large database, and you want to store data quickly and your data is in a CSV file then you can directly insert your data. 

Syntax –
You can see the COPY command syntax for your reference as follows.

COPY table_name [( column_list )]
FROM 'file_name path'[, 'file2_name path', ...] | STDIN
[WITH option = 'value' [AND ...]]

Now, let’s create the sample data for implementing the approach. 

Step-1 :
Creating keyspace – data
Here, you can use the following cqlsh command to create the keyspace as follows.

CREATE KEYSPACE data
 WITH REPLICATION = {  
  'class' : 'NetworkTopologyStrategy',  
  'datacenter1' : 1  
  } ;

Step-2 :
Creating the Student_personal_data table –
Here, you can use the following cqlsh command to create the Student_personal_data table as follows.

CREATE TABLE data.Student_personal_data (  
 S_id UUID PRIMARY KEY,  
S_firstname text,  
S_lastname text,    
);

Step-3 :
Creating the CSV file –
Consider the following given table as a CSV file namely as personal_data.csv. But, in actual you can insert data in CSV file and save it in your computer drive. 

S_id(UUID)	S_firstname	S_lastname
e1ae4cf0-d358-4d55-b511-85902fda9cc1	Ashish	christopher
e2ae4cf0-d358-4d55-b511-85902fda9cc2	Joshua	D
e3ae4cf0-d358-4d55-b511-85902fda9cc3	Ken	N
e4ae4cf0-d358-4d55-b511-85902fda9cc4	Christine	christopher
e5ae4cf0-d358-4d55-b511-85902fda9cc5	Allie	K
e6ae4cf0-d358-4d55-b511-85902fda9cc6	Lina	M

Step-4 :
Inserting data from the CSV file –
In this, you will see how you can insert data into the database from existed CSV file you have, and you can use the following cqlsh command as follows.  

COPY data.Student_personal_data (S_id, S_firstname, S_lastname) 
FROM 'personal_data.csv' 
WITH HEADER = TRUE;

Step-5 :
Verifying the result –
Once you will execute the above command, then you will get the following result as follows.

Using 7 child processes

Starting copy of data.Student_personal_data with columns [S_id, S_firstname, S_lastname].
Processed: 6 rows; Rate:      10 rows/s; Avg. rate:      14 rows/s
6 rows imported from 1 files in 0.422 seconds (0 skipped).

You can use the following command to see the output as follows.

select * from data.Student_personal_data;

Output :

S_id	S_firstname	S_lastname
e5ae4cf0-d358-4d55-b511-85902fda9cc5	Allies	K
e6ae4cf0-d358-4d55-b511-85902fda9cc6	Lina	M
e2ae4cf0-d358-4d55-b511-85902fda9cc2	Joshua	D
e1ae4cf0-d358-4d55-b511-85902fda9cc1	Ashish	christopher
e3ae4cf0-d358-4d55-b511-85902fda9cc3	Ken	N
e4ae4cf0-d358-4d55-b511-85902fda9cc4	Christine	christopher