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!
No Comments Yet
Let us know what you think