CODEWITHSUNDEEP
cassandracqlcql3
Dhamodaran
Dhamodaran

Reputation: 333

Cassandra selective copy

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.

Upvotes: 13

Views: 13955

Answers (4)

Gangadhar Kairi
Gangadhar Kairi

Reputation: 478

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

Upvotes: 0

user123
user123

Reputation: 175

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

Upvotes: -1

Amine CHERIFI
Amine CHERIFI

Reputation: 1175

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.

Upvotes: 7

BrianC
BrianC

Reputation: 10721

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.

Upvotes: 4

Related Questions