Cassandra selective copy

Question

I want to copy selected rows from a columnfamily to a .csv file. The copy command is available just to dump a column or entire table to a file without where clause. Is there a way to use where clause in copy command?

Another way I thought of was,

Do "Insert into table2 () values ( select * from table1 where <where_clause>);" and then dump the table2 to .csv , which is also not possible.

Any help would be much appreciated.

Genni

Answer 1

There are no way to make a where clause in copy, but you can use this method :

echo "select c1,c2.... FROM keySpace.Table where ;" | bin/cqlsh > output.csv

It allows you to save your result in the output.csv file.

Stoneblind

It only outputs 100 rows. And as a text table, not in csv format. – Delitescence

Answer 2

No, there is no built-in support for a "where" clause when exporting to a CSV file.

One alternative would be to write your own script using one of the drivers. In the script you would do the "select", then read the results and write out to a CSV file.

Cumin

Answer 3

Other ways to run the SQL with filter and redirect the response to csv

1) Inside the cqlsh, use the CAPTURE command and redirect the output to a file. You need to set the tracing on before executing the command

Example: CAPTURE 'output.txt' -- output of the sql executed after this command gets captured into output.txt file

2) In case if you would like to redirect the SQL output to a file from outside of cqlsh

./cqlsh -e'select * from keyspaceName.tableName' > fileName.txt -- hostname

Dougald

Answer 4

In addition to Amine CHERIFI's answer:

| sed -e 's/^\s+//; s_\s*\|\s*_,_g; /^-{3,}|^$|^\(.+\)$/d'

1. Removes spaces
2. Replaces | with ,
3. Removes header separator, empty and summary lines

Gunas

| sed -e 's/^\s*//' -e '/^---/d' -e 's/\s*|\s*/;/g' worked for me (I use ; as separator which is commonly used in Germany) – Racemic

It's | sed -e 's/^\s*//' -e 's/\s*|\s*/;/g' -e '/^---/d' -e '/^\s*$/d' -e '/^(.*)$/d', sorry. – Racemic