I'm using Cassandra 2.0.9 for store quite big amounts of data, let's say 100Gb, in one column family. I would like to export this data to CSV in fast way. I tried:
I use Amazon Ec2 instance with fast storage, 15 Gb of RAM and 4 cores
Is there any better option for export gigabytes of data from Cassandra to CSV?
csv'; cqlsh> select *from user; Now capturing query output to '/home/Desktop/user. csv'. Use DevCenter and execute a query. Right click on the output and select "Copy All as CSV" to paste the output in CSV.
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 store the output in a file, then import with 'cassandra-cli -f filename'. If using cqlsh, you can use the 'describe schema' command. You can restrict to a keyspace with 'describe keyspace keyspace'. You can save this to a file then import with 'cqlsh -f filename'.
Update for 2020th: DataStax provides a special tool called DSBulk for loading and unloading of data from Cassandra (starting with Cassandra 2.1), and DSE (starting with DSE 4.7/4.8). In simplest case, the command line looks as following:
dsbulk unload -k keyspace -t table -url path_to_unload
DSBulk is heavily optimized for loading/unloading operations, and has a lot of options, including import/export from/to compressed files, providing the custom queries, etc.
There is a series of blog posts about DSBulk, that could provide more information & examples: 1, 2, 3, 4, 5, 6
Because using COPY will be quite challenging when you are trying to export a table with millions of rows from Cassandra, So what I have done is to create simple tool to get the data chunk by chunk (paginated) from cassandra table and export it to CSV.
Look at my example solution using java library from datastax.
Inspired by @user1859675 's answer, Here is how we can export data from Cassandra using Spark
val cassandraHostNode = "10.xxx.xxx.x5,10.xxx.xxx.x6,10.xxx.xxx.x7";
val spark = org.apache.spark.sql.SparkSession
.builder
.config("spark.cassandra.connection.host", cassandraHostNode)
.appName("Awesome Spark App")
.master("local[*]")
.getOrCreate()
val dataSet = spark.read.format("org.apache.spark.sql.cassandra")
.options(Map("table" -> "xxxxxxx", "keyspace" -> "xxxxxxx"))
.load()
val targetfilepath = "/opt/report_values/"
dataSet.write.format("csv").save(targetfilepath) // Spark 2.x
You will need "spark-cassandra-connector
" in your classpath for this to work.
The version I am using is below
<groupId>com.datastax.spark</groupId>
<artifactId>spark-cassandra-connector_2.11</artifactId>
<version>2.3.2</version>
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