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!