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!
1 Comment. Leave new
Thanks for Sharing your knowledge to us. it is very useful in daily activities