Useful CQLSH Commands for Everyday Use

Posted in: Technical Track

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:

[email protected]:~ $ 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.
[email protected]>

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:

[email protected]:~ $ echo "select * from tes.ratings_by_email;" | cqlsh > query_output.txt
[email protected]:~ $ cat query_output.txt

 email           | title        | date_rated | rating | user_location | year
-----------------+--------------+------------+--------+---------------+------
 [email protected] |       Avatar | 2010-05-10 |      9 |           USA | 2009
  [email protected] |       Avatar | 2012-06-10 |      8 |        Mexico | 2009
 [email protected] |       Avatar | 2010-04-21 |      9 |         India | 2009


[email protected]:~ $ cqlsh -e "select * from tes.ratings_by_email;" > query_output.txt
[email protected]:~ $ cat query_output.txt

 email           | title        | date_rated | rating | user_location | year
-----------------+--------------+------------+--------+---------------+------
 [email protected] |       Avatar | 2010-05-10 |      9 |           USA | 2009
  [email protected] |       Avatar | 2012-06-10 |      8 |        Mexico | 2009
 [email protected] |       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:

[email protected]> capture 'query_output.txt'
Now capturing query output to 'query_output.txt'.
[email protected]> select * from tes.ratings_by_email;
[email protected]> select * from tes.ratings_by_email;
[email protected]> capture off
[email protected]> exit
[email protected]:~ $ cat query_output.txt

 email           | title        | date_rated | rating | user_location | year
-----------------+--------------+------------+--------+---------------+------
 [email protected] |       Avatar | 2010-05-10 |      9 |           USA | 2009
  [email protected] |       Avatar | 2012-06-10 |      8 |        Mexico | 2009
 [email protected] |       Avatar | 2010-04-21 |      9 |         India | 2009

(3 rows)

 email           | title        | date_rated | rating | user_location | year
-----------------+--------------+------------+--------+---------------+------
 [email protected] |       Avatar | 2010-05-10 |      9 |           USA | 2009
  [email protected] |       Avatar | 2012-06-10 |      8 |        Mexico | 2009
 [email protected] |       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:

[email protected]:tes> paging
Query paging is currently enabled. Use PAGING OFF to disable
Page size: 100
[email protected]: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:

[email protected]> select * from tes.ratings_by_email ;

 email           | title        | date_rated | rating | user_location | year
-----------------+--------------+------------+--------+---------------+------
 [email protected] |       Avatar | 2010-05-10 |      9 |           USA | 2009
  [email protected] |       Avatar | 2012-06-10 |      8 |        Mexico | 2009
 [email protected] |       Avatar | 2010-04-21 |      9 |         India | 2009


(3 rows)
[email protected]> expand on;
Now Expanded output is enabled
[email protected]> select * from tes.ratings_by_email ;

@ Row 1
---------------+-----------------
 email         | [email protected]
 title         | Avatar
 date_rated    | 2010-05-10
 rating        | 9
 user_location | USA
 year          | 2009


@ Row 2
---------------+-----------------
 email         | [email protected]
 title         | Avatar
 date_rated    | 2012-06-10
 rating        | 8
 user_location | Mexico
 year          | 2009

@ Row 3
---------------+-----------------
 email         | [email protected]
 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:

[email protected]:~ $ cat select.cql
 select * from tes.ratings_by_email;
[email protected]:~ $ 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.
[email protected]> source 'select.cql';

 email           | title        | date_rated | rating | user_location | year
-----------------+--------------+------------+--------+---------------+------
 [email protected] |       Avatar | 2010-05-10 |      9 |           USA | 2009
  [email protected] |         Lucy | 2016-06-10 |      9 |        Mexico | 2014
 [email protected] | 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:

[email protected]:tes> tracing on;
Now Tracing is enabled
[email protected]:tes> select * from tes.ratings_by_email ;

 email           | title        | date_rated | rating | user_location | year
-----------------+--------------+------------+--------+---------------+------
 [email protected] |       Avatar | 2010-05-10 |      9 |           USA | 2009
  [email protected] |         lucy | 2016-06-10 |      9 |        Mexico | 2014
 [email protected] | 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


[email protected]: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.:

[email protected]:.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:

[email protected]:.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
[email protected]:.cassandra $

There are a few more cqlsh commands which are helpful. Listed below:

[email protected]> show version
[cqlsh 5.0.1 | Cassandra 3.11.11 | CQL spec 3.4.4 | Native protocol v4]
[email protected]> show host
Connected to Anil_Cluster at xx.xx.xx.xx:9042.
[email protected]> describe cluster

Cluster: Anil_Cluster
Partitioner: Murmur3Partitioner

[email protected]> consistency
Current consistency level is ONE.
[email protected]> consistency QUORUM
Consistency level set to QUORUM.
[email protected]> consistency
Current consistency level is QUORUM. 

 

[email protected]: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!

 

email

Author

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Anil Mittana is a Datastax Certified Cassandra Architect with extensive knowledge of distributed architecture technologies. Anil thrives on new challenges and appreciates the opportunities to learn new things that come with working at Pythian. Customers and coworkers have grown to appreciate and trust him for his ability to comprehend difficult challenges and perform well under pressure. Anil spent the last 7 years working on technologies like Cassandra, PostgreSQL, Oracle.

1 Comment. Leave new

Thanks for Sharing your knowledge to us. it is very useful in daily activities

Reply

Leave a Reply

Your email address will not be published.