Export and Import data in Cassandra

Prerequisite – Cassandra
In this article, we are going to discuss how we can export and import data through cqlsh query.
Let’s discuss one by one.

First, we are going to create table namely as Data in which id, firstname, lastname are the fields for sample exercise.
Let’s have a look.

Table name: Data

CREATE TABLE Data ( 
  id UUID PRIMARY KEY, 
  firstname text,
  lastname text 
); 

Now, we are going to insert some data to export and import data for sample exercise. let’s have a look.

INSERT INTO Data (id, firstname, lastname ) 
VALUES (3b6441dd-3f90-4c93-8f61-abcfa3a510e1, 'Ashish', 'Rana');

INSERT INTO Data (id, firstname, lastname) 
VALUES (3b6442dd-bc0d-4157-a80f-abcfa3a510e2, 'Amit', 'Gupta');

INSERT INTO Data (id, firstname, lastname) 
VALUES (3b6443dd-d358-4d99-b900-abcfa3a510e3, 'Ashish', 'Gupta');

INSERT INTO Data (id, firstname, lastname) 
VALUES (3b6444dd-4860-49d6-9a4b-abcfa3a510e4, 'Dhruv', 'Gupta');

INSERT INTO Data (id, firstname, lastname)   
VALUES (3b6445dd-e68e-48d9-a5f8-abcfa3a510e5, 'Harsh', 'Vardhan');

INSERT INTO Data (id, firstname, lastname) 
VALUES (3b6446dd-eb95-4bb4-8685-abcfa3a510e6, 'Shivang', 'Rana'); 

Now, we are going to Export Data used the following cqlsh query given below. let’s have a look.

cqlsh>COPY Data(id, firstname, lastname) 
TO 'AshishRana\Desktop\Data.csv' WITH HEADER = TRUE; 

The CSV file is created:

Using 7 child processes 

Starting copy of Data with columns [id, firstname, lastname].

Processed: 6 rows; Rate: 20 rows/s; Avg. rate: 30 rows/s
6 rows exported to 1 files in 0.213 seconds. 

Now, we are going to delete data from table ‘Data’ to import again from CSV file which is already has been created.

truncate Data; 

Now, here we are going to import data again. To import Data used the following cqlsh query given below.

COPY Data (id, firstname, lastname)
FROM 'AshishRana\Desktop\Data.csv' 
WITH HEADER = TRUE; 

The rows are imported:

Using 7 child processes 

Starting copy of Data with columns [id, firstname, lastname].

Processed: 6 rows; Rate: 10 rows/s; Avg. rate: 14 rows/s
6 rows imported from 1 files in 0.423 seconds (0 skipped). 

To verify the results whether it is successfully imported or not. let’s have a look.

SELECT * 
FROM Data; 

Output:

To copy a specific rows of a table used the following cqlsh query given below.
First, export data from table and then truncate after these two steps follow these steps given below.

COPY Data FROM STDIN; 

After executing above cqlsh query the line prompt changes to [copy] let’s have a look.

Using 7 child processes 

Starting copy of cluster1.Data with columns [id, firstname, lastname].

[Use . on a line by itself to end input]
[copy] 

Now, insert the row value of table which you want to import.

[copy] 3b6441dd-3f90-4c93-8f61-abcfa3a510e1, 'Ashish', 'Rana'
[copy] .    // keep it in mind at the end insert the period 

After successfully executed above given cqlsh query will give you the following results given below. let’s have a look.

Processed: 1 rows; Rate: 0 rows/s; Avg. rate: 0 rows/s
1 rows imported from 1 files in 36.991 seconds (0 skipped). 

Now, let verify the results.

SELECT * 
FROM Data; 

Output: