Read JSON File in Cassandra

INSERT/Load Json data File in Cassandra (3.0+) table

Requirement:

1. Create Cassandra table where we can load json data into it. Here some of the column name is separated by space(like 'a b').
2. Load JSON file into the table.

Challenge: 

Cassandra support only 'CSV file' load in Table(As per my understanding and searched till now) by using 'COPY' command but not 'JSON' file.

Resolution:

As per the Cassandra document page, Cassandra supports CSV record level insert and file level insert both operation but for JSON it only support Record level insert by following command:

cqlsh> INSERT INTO keyspace1.table1 JSON '{
  "id" : "12", 
  "DB" : "Cassandra", 
  "ops" : "Insert",
  "Project" : "Hadoop" }';


So If we want to insert whole file in the table then we need to loop for each object in JSON file and need to call insert query every time.

So the loop can be implemented by:
 

• Linux script
• JavaScript library jq
• Java Program
• PHP script
• Any other script/language

I did with java since I stuck to call INSERT query in loop with jq.

I created table like:


cqlsh> use keyspace1;

cqlsh:keyspace1>CREATE TABLE table1 (duration text,title text PRIMARY KEY,description text,"end date" text,"start date" text,venue text);




Please find the java code to implement the same. It require Cassandra library which is available inside Cassandra folder and json-simple-1.1.1.jar:

Now create java runnable jar and run on the server like

java -jar csd.jar 127.0.0.1 keyspace1 table1 "/root/asd.json"



Let me know if I missed anything or more information required on this.