Export cassandra query result to a csv file

Question

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.

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.

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

https://github.com/tenmax/cqlkit

In 2020th you can use DSBulk to export or import data to/from CSV (by default), or JSON. It could be as simple as:

dsbulk unload -k keyspace -t table -u user -p password -url filename

DSBulk is heavily optimized for fast data export, without putting too much load onto the coordinator node that happens when you just run select * from table.

You can control what columns to export, and even provide your own query, etc. See following blog posts for examples:

• https://www.datastax.com/blog/2019/03/datastax-bulk-loader-introduction-and-loading
• https://www.datastax.com/blog/2019/04/datastax-bulk-loader-more-loading
• https://www.datastax.com/blog/2019/04/datastax-bulk-loader-common-settings
• https://www.datastax.com/blog/2019/06/datastax-bulk-loader-unloading
• https://www.datastax.com/blog/2019/07/datastax-bulk-loader-counting
• https://www.datastax.com/blog/2019/12/datastax-bulk-loader-examples-loading-other-locations

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

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

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

You can use the COPY command to create the CSV file. e.g. copy table with selected columns. Columns are optional, if you select them, every column will be picked.

COPY TABLE (COL1, COL2) TO 'filename.csv' HEADER=TRUE/FALSE

For more reference https://docs.datastax.com/en/cql/3.3/cql/cql_reference/cqlshCopy.html