Useful CQLSH Commands for Everyday Use
Cassandra is an open-source NoSQL distributed database used for processing large volumes of data with high availability and scalability.
The CQL shell (cqlsh) allows users to communicate with Cassandra. Using this shell, you can execute Cassandra Query Language (CQL).
Usage: cqlsh [options] [host [port]]
Type $CASSANDRA_HOME/bin/cqlsh -help for detailed syntax and options for cqlsh:
cassandra@ip-10-10-0-70:~ $ cqlsh Connected to Anil_Cluster at 127.0.0.1:9042. [cqlsh 5.0.1 | Cassandra 3.11.11 | CQL spec 3.4.4 | Native protocol v4] Use HELP for help. dba@cqlsh>
If you specify a hostname or IP address after cqlsh command, the session gets connected to a specific Cassandra node. By default, the CQL shell launches a session with the local host on 127.0.0.1. when no port is specified, the connection uses a default port: 9042. this is configured in Cassandra.yaml as
native_transport_port: 9042
Below are a few CQLSH commands which are helpful and I use them often at work.
CQLSH output
We can redirect CQLSH output to a file using the commands below:
cassandra@ip-10-10-0-70:~ $ echo "select * from tes.ratings_by_email;" | cqlsh > query_output.txt cassandra@ip-10-10-0-70:~ $ cat query_output.txt email | title | date_rated | rating | user_location | year -----------------+--------------+------------+--------+---------------+------ sarma@gmail.com | Avatar | 2010-05-10 | 9 | USA | 2009 anil@gmail.com | Avatar | 2012-06-10 | 8 | Mexico | 2009 payal@gmail.com | Avatar | 2010-04-21 | 9 | India | 2009
cassandra@ip-10-10-0-70:~ $ cqlsh -e "select * from tes.ratings_by_email;" > query_output.txt cassandra@ip-10-10-0-70:~ $ cat query_output.txt email | title | date_rated | rating | user_location | year -----------------+--------------+------------+--------+---------------+------ sarma@gmail.com | Avatar | 2010-05-10 | 9 | USA | 2009 anil@gmail.com | Avatar | 2012-06-10 | 8 | Mexico | 2009 payal@gmail.com | Avatar | 2010-04-21 | 9 | India | 2009 (3 rows)
Capture
The CAPTURE command is also used to capture the output of cqlsh, but this command is useful if you are interactively working on the cqlsh and if you want to capture different outputs to different files. The CAPTURE command appends the output to the output file. The output will not be shown at the console while it’s captured:
dba@cqlsh> capture 'query_output.txt' Now capturing query output to 'query_output.txt'. dba@cqlsh> select * from tes.ratings_by_email; dba@cqlsh> select * from tes.ratings_by_email; dba@cqlsh> capture off dba@cqlsh> exit cassandra@ip-10-10-0-70:~ $ cat query_output.txt email | title | date_rated | rating | user_location | year -----------------+--------------+------------+--------+---------------+------ sarma@gmail.com | Avatar | 2010-05-10 | 9 | USA | 2009 anil@gmail.com | Avatar | 2012-06-10 | 8 | Mexico | 2009 payal@gmail.com | Avatar | 2010-04-21 | 9 | India | 2009 (3 rows) email | title | date_rated | rating | user_location | year -----------------+--------------+------------+--------+---------------+------ sarma@gmail.com | Avatar | 2010-05-10 | 9 | USA | 2009 anil@gmail.com | Avatar | 2012-06-10 | 8 | Mexico | 2009 payal@gmail.com | Avatar | 2010-04-21 | 9 | India | 2009 (3 rows)
Paging
When paging is enabled, a prompt to fetch the next page will display after just one page of data has been fetched at a time. By default, paging is enabled with page size 100. Use PAGING OFF to disable this prompting:
dba@cqlsh:tes> paging Query paging is currently enabled. Use PAGING OFF to disable Page size: 100 dba@cqlsh:tes>
Expand
EXPAND on/off enables or disables vertical printing of rows. When numerous columns are fetched or a single column’s contents are large, enabling EXPAND is helpful:
dba@cqlsh> select * from tes.ratings_by_email ; email | title | date_rated | rating | user_location | year -----------------+--------------+------------+--------+---------------+------ sarma@gmail.com | Avatar | 2010-05-10 | 9 | USA | 2009 anil@gmail.com | Avatar | 2012-06-10 | 8 | Mexico | 2009 payal@gmail.com | Avatar | 2010-04-21 | 9 | India | 2009 (3 rows) dba@cqlsh> expand on; Now Expanded output is enabled dba@cqlsh> select * from tes.ratings_by_email ; @ Row 1 ---------------+----------------- email | sarma@gmail.com title | Avatar date_rated | 2010-05-10 rating | 9 user_location | USA year | 2009 @ Row 2 ---------------+----------------- email | anil@gmail.com title | Avatar date_rated | 2012-06-10 rating | 8 user_location | Mexico year | 2009 @ Row 3 ---------------+----------------- email | payal@gmail.com title | Avatar date_rated | 2010-04-21 rating | 9 user_location | India year | 2009 (3 rows)
Source
Processes each line of a file’s content as a CQL statement after reading its contents:
cassandra@ip-10-10-0-70:~ $ cat select.cql select * from tes.ratings_by_email; cassandra@ip-10-10-0-70:~ $ cqlsh Connected to Anil_Cluster at 34.198.232.39:9042. [cqlsh 5.0.1 | Cassandra 3.11.11 | CQL spec 3.4.4 | Native protocol v4] Use HELP for help. dba@cqlsh> source 'select.cql'; email | title | date_rated | rating | user_location | year -----------------+--------------+------------+--------+---------------+------ sarma@gmail.com | Avatar | 2010-05-10 | 9 | USA | 2009 anil@gmail.com | Lucy | 2016-06-10 | 9 | Mexico | 2014 payal@gmail.com | Interstellar | 2020-04-21 | 8 | India | 2014
Tracing
Enables or disables query tracing. Once a query is finished running when tracing is enabled, a trace of the events taking place during the query will be produced. Queries with tracing on create a tracing session and results are stored in two tables which are system_traces.events, and system_traces.sessions.
system_traces.sessions: This table holds the high-level details of query operation such as session_id, client, command, coordinator, coordinator_port, duration, and parameters.
system_traces.events: This table holds more detailed information about the query operation such as session_id, event_id, activity, source, source_elapsed, source_port, and thread:
dba@cqlsh:tes> tracing on;
Now Tracing is enabled
dba@cqlsh:tes> select * from tes.ratings_by_email ;
email | title | date_rated | rating | user_location | year
-----------------+--------------+------------+--------+---------------+------
sarma@gmail.com | Avatar | 2010-05-10 | 9 | USA | 2009
anil@gmail.com | lucy | 2016-06-10 | 9 | Mexico | 2014
payal@gmail.com | Interstellar | 2020-04-21 | 8 | INDIA | 2014
(3 rows)
Tracing session: 5c87f060-f61d-11ec-9046-9f0647711032
activity | timestamp | source | source_elapsed | client
----------------------------------------------------------------------------------------------------------------------------+----------------------------+---------------+----------------+---------------
Execute CQL3 query | 2022-06-27 13:30:50.856000 | hostname | 0 | hostname
Parsing select * from tes.ratings_by_email ; [Native-Transport-Requests-1] | 2022-06-27 13:30:50.864000 | hostname | 8374 | hostname
Preparing statement [Native-Transport-Requests-1] | 2022-06-27 13:30:50.864000 | hostname | 8684 | hostname
Executing single-partition query on roles [ReadStage-2] | 2022-06-27 13:30:50.875000 | hostname | 19324 | hostname
Acquiring sstable references [ReadStage-2] | 2022-06-27 13:30:50.875000 | hostname | 19468 | hostname
Key cache hit for sstable 16 [ReadStage-2] | 2022-06-27 13:30:50.875000 | hostname | 19650 | hostname
Skipped 0/1 non-slice-intersecting sstables, included 0 due to tombstones [ReadStage-2] | 2022-06-27 13:30:50.875000 | hostname | 19846 | hostname
Merged data from memtables and 1 sstables [ReadStage-2] | 2022-06-27 13:30:50.875000 | hostname | 20107 | hostname
Read 1 live rows and 0 tombstone cells [ReadStage-2] | 2022-06-27 13:30:50.875000 | hostname | 20230 | hostname
Executing single-partition query on roles [ReadStage-3] | 2022-06-27 13:30:50.913000 | hostname | 57867 | hostname
Acquiring sstable references [ReadStage-3] | 2022-06-27 13:30:50.913000 | hostname | 58075 | hostname
Key cache hit for sstable 16 [ReadStage-3] | 2022-06-27 13:30:50.913000 | hostname | 58262 | hostname
Skipped 0/1 non-slice-intersecting sstables, included 0 due to tombstones [ReadStage-3] | 2022-06-27 13:30:50.914000 | hostname | 58450 | hostname
Merged data from memtables and 1 sstables [ReadStage-3] | 2022-06-27 13:30:50.914000 | hostname | 58738 | hostname
Read 1 live rows and 0 tombstone cells [ReadStage-3] | 2022-06-27 13:30:50.914000 | hostname | 58870 | hostname
Computing ranges to query [Native-Transport-Requests-1] | 2022-06-27 13:30:50.916000 | hostname | 61003 | hostname
Submitting range requests on 9 ranges with a concurrency of 2 (75.6 rows per range expected) [Native-Transport-Requests-1] | 2022-06-27 13:30:50.917000 | hostname | 61354 | hostname
Submitted 1 concurrent range requests [Native-Transport-Requests-1] | 2022-06-27 13:30:50.917000 | hostname | 62001 | hostname
Executing seq scan across 3 sstables for (min(-9223372036854775808), min(-9223372036854775808)] [ReadStage-3] | 2022-06-27 13:30:50.917000 | hostname | 62218 | hostname
Read 3 live rows and 6 tombstone cells [ReadStage-3] | 2022-06-27 13:30:50.931000 | hostname | 75641 | hostname
Request complete | 2022-06-27 13:30:50.935665 | hostname | 79665 | hostname
dba@cqlsh:tes> select activity from system_traces.events where session_id=5c87f060-f61d-11ec-9046-9f0647711032;
activity
-------------------------------------------------------------------------------------------------
Parsing select * from tes.ratings_by_email ;
Preparing statement
Executing single-partition query on roles
Acquiring sstable references
Key cache hit for sstable 16
Skipped 0/1 non-slice-intersecting sstables, included 0 due to tombstones
Merged data from memtables and 1 sstables
Read 1 live rows and 0 tombstone cells
Executing single-partition query on roles
Acquiring sstable references
Key cache hit for sstable 16
Skipped 0/1 non-slice-intersecting sstables, included 0 due to tombstones
Merged data from memtables and 1 sstables
Read 1 live rows and 0 tombstone cells
Computing ranges to query
Submitting range requests on 9 ranges with a concurrency of 2 (75.6 rows per range expected)
Submitted 1 concurrent range requests
Executing seq scan across 3 sstables for (min(-9223372036854775808), min(-9223372036854775808)]
Read 3 live rows and 6 tombstone cells
(19 rows)
Cqlsh session
When you run the CQLSH command, it reads a file “cqlshrc” from ~/.cassandra. You can configure options in ~/.cassandra/cqlshrc file. You can get a cqlshrc sample file from software you can find in $CASSANDRA_HOME/conf/cqlshrc.sample. The options I use are below.:
cassandra@ip-10-10-0-70:.cassandra $ cat cqlshrc [authentication] username = cassandra password = cassandra [connection] hostname=xx.xx.xx.xx port=9042
In the same directory, there are more useful files. The file “cqlsh_history” records all commands typed in cqlsh. The file “nodetool.history” records nodetool history. It’s important to provide the right ownership and security to these files:
cassandra@ip-10-10-0-70:.cassandra $ ls -ltr total 528 -rw-r--r-- 1 cassandra cassgrp 94 Feb 16 10:14 cqlshrc -rwx------ 1 cassandra cassgrp 504297 Jun 22 06:48 cqlsh_history -rwx------ 1 cassandra cassgrp 27192 Jun 22 11:09 nodetool.history cassandra@ip-10-10-0-70:.cassandra $
There are a few more cqlsh commands which are helpful. Listed below:
dba@cqlsh> show version [cqlsh 5.0.1 | Cassandra 3.11.11 | CQL spec 3.4.4 | Native protocol v4] dba@cqlsh> show host Connected to Anil_Cluster at xx.xx.xx.xx:9042. dba@cqlsh> describe cluster Cluster: Anil_Cluster Partitioner: Murmur3Partitioner dba@cqlsh> consistency Current consistency level is ONE. dba@cqlsh> consistency QUORUM Consistency level set to QUORUM. dba@cqlsh> consistency Current consistency level is QUORUM.
dba@cqlsh:tes> show session 5c87f060-f61d-11ec-9046-9f0647711032
Tracing session: 5c87f060-f61d-11ec-9046-9f0647711032
activity | timestamp | source | source_elapsed | client
----------------------------------------------------------------------------------------------------------------------------+----------------------------+---------------+----------------+---------------
Execute CQL3 query | 2022-06-27 13:30:50.856000 | hostname | 0 | hostname
Parsing select * from tes.ratings_by_email ; [Native-Transport-Requests-1] | 2022-06-27 13:30:50.864000 | hostname | 8374 | hostname
Preparing statement [Native-Transport-Requests-1] | 2022-06-27 13:30:50.864000 | hostname | 8684 | hostname
Executing single-partition query on roles [ReadStage-2] | 2022-06-27 13:30:50.875000 | hostname | 19324 | hostname
Acquiring sstable references [ReadStage-2] | 2022-06-27 13:30:50.875000 | hostname | 19468 | hostname
Key cache hit for sstable 16 [ReadStage-2] | 2022-06-27 13:30:50.875000 | hostname | 19650 | hostname
Skipped 0/1 non-slice-intersecting sstables, included 0 due to tombstones [ReadStage-2] | 2022-06-27 13:30:50.875000 | hostname | 19846 | hostname
Merged data from memtables and 1 sstables [ReadStage-2] | 2022-06-27 13:30:50.875000 | hostname | 20107 | hostname
Read 1 live rows and 0 tombstone cells [ReadStage-2] | 2022-06-27 13:30:50.875000 | hostname | 20230 | hostname
Executing single-partition query on roles [ReadStage-3] | 2022-06-27 13:30:50.913000 | hostname | 57867 | hostname
Acquiring sstable references [ReadStage-3] | 2022-06-27 13:30:50.913000 | hostname | 58075 | hostname
Key cache hit for sstable 16 [ReadStage-3] | 2022-06-27 13:30:50.913000 | hostname | 58262 | hostname
Skipped 0/1 non-slice-intersecting sstables, included 0 due to tombstones [ReadStage-3] | 2022-06-27 13:30:50.914000 | hostname | 58450 | hostname
Merged data from memtables and 1 sstables [ReadStage-3] | 2022-06-27 13:30:50.914000 | hostname | 58738 | hostname
Read 1 live rows and 0 tombstone cells [ReadStage-3] | 2022-06-27 13:30:50.914000 | hostname | 58870 | hostname
Computing ranges to query [Native-Transport-Requests-1] | 2022-06-27 13:30:50.916000 | hostname | 61003 | hostname
Submitting range requests on 9 ranges with a concurrency of 2 (75.6 rows per range expected) [Native-Transport-Requests-1] | 2022-06-27 13:30:50.917000 | hostname | 61354 | hostname
Submitted 1 concurrent range requests [Native-Transport-Requests-1] | 2022-06-27 13:30:50.917000 | hostname | 62001 | hostname
Executing seq scan across 3 sstables for (min(-9223372036854775808), min(-9223372036854775808)] [ReadStage-3] | 2022-06-27 13:30:50.917000 | hostname | 62218 | hostname
Read 3 live rows and 6 tombstone cells [ReadStage-3] | 2022-06-27 13:30:50.931000 | hostname | 75641 | hostname
Request complete | 2022-06-27 13:30:50.935665 | hostname | 79665 | hostname
The commands above are the ones that I use the most. If you use other helpful commands, share them in the comments below!
