Inserting data in cassandra without puting in single quotes using cql java driver

Question

I am having trouble puting those single quotes for ASCII/Timestamp columns and not puting for other types like Int, Decimal, Boolean etc.

The data comes from another db/table, which is a sql.

I have all the column data as string. I don't want to format each column data to check null values and then decide to put quote or not.

Is it possible to pass in insert data value without giving single quotes, using prepared statement or whatever.

I never said, prepared statement is unacceptable, but will i be able to use prepared statement for seting insert values dynamically without need of adding in quotes. — Abhishek Anand, Jul 25 '14 at 06:36

score 3 · Answer 1 · edited May 23 '17 at 11:56

If you don't want to write a loader that uses prepared statements (via the CQL driver...which is a good idea), I can think of one other way. To import without using single quotes, you should be able to accomplish this with the COPY FROM CQL3 command (setting the QUOTE parameter to an empty string). If you can dump your RDBMS data to a csv file, you should be able to insert those values into Cassandra like this:

COPY myColumnFamily (colname1,colname2,colname3)
FROM '/home/myUser/rdbmsdata.csv' WITH QUOTE='';

Check out the documentation on the COPY command for more information. Examples can be found here.

EDIT:

I also read the above question and assumed that you did not want a prepared statement-based answer. Since that's obviously not the case, I thought I'd also provide one here (using DataStax's Java CQL driver). Note that this answer is based on my column family and column names from my example above, and assumes that col1 is the (only) primary key.

PreparedStatement statement = session.prepare(
    "UPDATE myKeyspace.myColumnFamily " +
    "SET col2=?, col3=? " +
    "WHERE col1=?");

BoundStatement boundStatement = statement.bind(
    strCol2, strCol3, strCol1);
session.execute(boundStatement);

This solution does not require you to encapsulate your string data in single quotes, and has a few added benefits over your String.ReplaceAll:

Allows you to insert values containing single quotes.
Escapes your values, protecting you from CQL-Injection (the lesser-known relative of SQL-Injection).
In CQL, both UPDATE and INSERT add a record if it does not exist and update it if it does (effectively known as an "UPSERT"). Using an UPDATE over an INSERT supports counter columns (if your schema ends up using them).
Prepared statements are faster, because they allow Cassandra to only have to parse the query once, and then re-run that same query with different values.

For more information, check out DataStax's documentation on using prepared statements with the Java Driver.

Its valid way Bryce, I just have to do it from within java program. :) — Abhishek Anand, Jul 25 '14 at 06:37
Agreed, but when i tried inserting data into prepared statement, it does validate the inputs, like in example you gave, strCol2, StrCol3 must match the type for col2 and col3. So again I would have to parse each string data i have into their Cassandra equivalent Java types. Agreed though, prepared statements are better. — Abhishek Anand, Jul 26 '14 at 12:26

score -1 · Accepted Answer · answered Jul 25 '14 at 09:12

-1

Finally did it using String.format clubbed with replace

String.format("INSERT INTO xyz_zx(A,B,C,D) VALUES('%s','%s',%s,%s);",(Object[])Strings).replaceAll("'null'","null");

answered Jul 25 '14 at 09:12

Abhishek Anand

1,940
14
27

Inserting data in cassandra without puting in single quotes using cql java driver

2 Answers2