Export cassandra query result to a csv file

问题:

I'm new in cassandra, and I have to export the result of a specific query to a csv file.

I found the COPY command, but (from what I understand) it allows you only to copy an already existing table to a csv file, and what I want is to copy directly the stdout of my query to the csv file. is there any way to do it with COPY command or with another way ?

My command is style (select column1, column2 from table where condition = xy) and I'm using cqlsh.

回答1:

If you don't mind your data using a pipe ('|') as a delimiter, you can try using the -e flag on cqlsh. The -e flag allows you to send a query to Cassandra from the command prompt, where you could redirect or even perform a grep/awk/whatever on your output.

$ bin/cqlsh -e'SELECT video_id,title FROM stackoverflow.videos' > output.txt
$ cat output.txt

 video_id                             | title
--------------------------------------+---------------------------
 2977b806-df76-4dd7-a57e-11d361e72ce1 |                 Star Wars
 ab696e1f-78c0-45e6-893f-430e88db7f46 | The Witches of Whitewater
 15e6bc0d-6195-4d8b-ad25-771966c780c8 |              Pulp Fiction

(3 rows)

Older versions of cqlsh don't have the -e flag. For older versions of cqlsh, you can put your command into a file, and use the -f flag.

$ echo "SELECT video_id,title FROM stackoverflow.videos;" > select.cql
$ bin/cqlsh -f select.cql > output.txt

From here, doing a cat on output.txt should yield the same rows as above.

回答2:

1. Use CAPTURE command to export the query result to a file.

cqlsh> CAPTURE
cqlsh> CAPTURE '/home/Desktop/user.csv';
cqlsh> select *from user;
Now capturing query output to '/home/Desktop/user.csv'.

Now, view the output of the query in /home/Desktop/user.csv

2. Use DevCenter and execute a query. Right click on the output and select "Copy All as CSV" to paste the output in CSV.

回答3:

I just wrote a tool to export CQL query to CSV and JSON format. Give it a try :)

https://github.com/tenmax/cqlkit

回答4:

I believe DevCenter also allows you to copy to CSV. http://www.datastax.com/what-we-offer/products-services/devcenter

回答5:

In windows, double quotes should be used to enclose the CQL.

cqlsh -e"SELECT video_id,title FROM stackoverflow.videos" > output.txt

回答6:

If I am understanding correctly you want to redirect your output to stdout?

Put your cql command in a file. My files is called select.cql and contents are:

select id from wiki.solr limit 100;

Then issue the following and you get it to stdout:

cqlsh < select.cql

I hope this helps. From there on you can pipe it and add commas, remove headers etc.

回答7:

Cannot comment... To deal with "MORE" issue when there are more than 100 rows, simply add "paging off" before the SQL.

Something like

$ bin/cqlsh -e'PAGING OFF;SELECT video_id,title FROM stackoverflow.videos' > output.txt

This will cause a little messy at the beginning of the output file but can easily be removed afterwards.