cqlkit is a CLI tool to export Cassandra query to CSV and JSON format. Cassandra is not good at Ad-hoc query, cqlkit allows you to export query result to semi-structured(JSON) or structured data(CSV). There are many tools out there for you to query or process these kinds of format.
Here is a simple some examples.
Export JSON for the system columns in cassandra cluster.
cql2json -q "select peer, data_center, host_id, preferred_ip, rack, release_version from system.peers"Export CSV for the system columns in cassandra cluster.
cql2csv -q "select peer, data_center, host_id, preferred_ip, rack, release_version from system.peers"- Java8
- Download from release page.
- Unzip the package.
- Add
$CQLKIT_PATH/binto the PATH environment variable
Install cqlkit via Homebrew.
brew update
brew install cqlkitUpgrade cqlkit
brew update
brew upgrade cqlkitRun cqlkit via Docker.
docker run --rm -it tenmax/cqlkitusage: cql2csv [-c contactpoint] [-r cassandraPort] [-q query] [FILE]
File The file to use as CQL query. If both FILE and QUERY are
omitted, query will be read from STDIN.
-c <arg> The contact point. if use multi
contact points, use ',' to separate
multi points
--connect-timeout <arg> Connection timeout in seconds;
default: 5
--consistency <LEVEL> The consistency level. The level
should be 'any', 'one', 'two',
'three', 'quorum', 'all',
'local_quorum', 'each_quorum',
'serial' or 'local_serial'.
--cqlshrc <arg> Use an alternative cqlshrc file
location, path.
--date-format <arg> Use a custom date format. Default is
"yyyy-MM-dd'T'HH:mm:ss.SSSZ"
--fetchSize <SIZE> The fetch size. Default is 5000
-h,--help Show the help and exit
-H,--no-header-row Do not output column names.
-k <arg> The keyspace to use.
-l,--linenumbers Insert a column of line numbers at
the front of the output. Useful when
piping to grep or as a simple primary
key.
-p <arg> The password to authenticate.
-r <arg> The port to connect to Cassandra, defaults to 9042.
-P,--parallel <arg> The level of parallelism to run the
task. Default is sequential.
-q,--query <CQL> The CQL query to execute. If
specified, it overrides FILE and
STDIN.
--query-partition-keys <TABLE> Query the partition key(s) for a
column family.
--query-ranges <CQL> The CQL query would be splitted by
the token ranges. WHERE clause is not
allowed in the CQL query
--request-timeout <arg> Request timeout in seconds; default:
12
-u <arg> The user to authenticate.
-v,--version Print the version
usage: cql2json [-c contactpoint] [-r cassandraPort] [-q query] [FILE]
File The file to use as CQL query. If both FILE and QUERY are
omitted, query will be read from STDIN.
-c <arg> The contact point. if use multi
contact points, use ',' to separate
multi points
--connect-timeout <arg> Connection timeout in seconds;
default: 5
--consistency <LEVEL> The consistency level. The level
should be 'any', 'one', 'two',
'three', 'quorum', 'all',
'local_quorum', 'each_quorum',
'serial' or 'local_serial'.
--cqlshrc <arg> Use an alternative cqlshrc file
location, path.
--date-format <arg> Use a custom date format. Default is
"yyyy-MM-dd'T'HH:mm:ss.SSSZ"
--fetchSize <SIZE> The fetch size. Default is 5000
-h,--help Show the help and exit
-j,--json-columns <arg> The columns that contains json
string. The content would be used as
json object instead of plain text.
Columns are separated by comma.
-k <arg> The keyspace to use.
-l,--linenumbers Insert a column of line numbers at
the front of the output. Useful when
piping to grep or as a simple primary
key.
-p <arg> The password to authenticate.
-r <arg> The port to connect to Cassandra, defaults to 9042.
-P,--parallel <arg> The level of parallelism to run the
task. Default is sequential.
-q,--query <CQL> The CQL query to execute. If
specified, it overrides FILE and
STDIN.
--query-partition-keys <TABLE> Query the partition key(s) for a
column family.
--query-ranges <CQL> The CQL query would be splitted by
the token ranges. WHERE clause is not
allowed in the CQL query
--request-timeout <arg> Request timeout in seconds; default:
12
-u <arg> The user to authenticate.
-v,--version Print the version
To connect to cassandra cluster, although we can use -c and -k to specify the contact server and keyspace respectively, to preapre a cqlshrc is recommended to simply your query. cqlshrc is used by cqlsh. cqlkit leverages this file to connect to your cluster. Here is the setup steps.
-
Create the cqlshrc file at
~/.cassandra/cqlshrc -
Here is the example format.
[authentication] keyspace = system [connection] hostname = 192.168.59.103 port = 9042 ; vim: set ft=dosini :
$ cql2csv -q "select text_col from ks.tbl" > example.csv
$ ./cqlsh localhost
cqlsh> COPY ks.tbl FROM 'example.csv' WITH ESCAPE='"' AND HEADER=TRUE
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent