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.

Answer 1

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.