Menu
To copy a specific rows of a table used the following cqlsh query given below. First, export data from table and then truncate after these two steps follow these steps given below. COPY Data FROM STDIN; After executing above cqlsh query the line prompt changes to [copy] let's have a look.
Roughly you need to perform the following steps: take a "snapshot" of the keyspace using: nodetool snapshot <keyspace-name> . This is run on the server, where you want to take generates a "snapshot". It will do that, storing a "snapshot" for each table of the keyspace.
You can use COPY command to export from one table and import into other table. From your example - I created 2 tables. user_country and user_car with respective primary keys. CREATE KEYSPACE user WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy', 'datacenter1' : 2 } ; CREATE TABLE user.
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.
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
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:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With