Cassandra 是一个开源 NoSQL 分布式数据库，用于处理具有高可用性和可扩展性的大量数据。
CQL shell (cqlsh) 允许用户与 Cassandra 进行通信。使用此 shell，您可以执行 Cassandra 查询语言 (CQL)。
用法：cqlsh [选项] [主机[端口]]
键入 $CASSANDRA_HOME/bin/cqlsh -help 以获取 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>
如果在 cqlsh 命令后指定主机名或 IP 地址，会话将连接到特定的 Cassandra 节点。默认情况下，CQL shell 在 127.0.0.1 上启动与本地主机的会话。当没有指定端口时，连接使用默认端口：9042。这在 Cassandra.yaml 中配置为
native_transport_port: 9042
下面是一些有用的 CQLSH 命令，我在工作中经常使用它们。
CQLSH 输出
我们可以使用以下命令将 CQLSH 输出重定向到文件：
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 命令也用于捕获 cqlsh 的输出，但如果您以交互方式处理 cqlsh 并且想要将不同的输出捕获到不同的文件，则此命令很有用。CAPTURE 命令将输出附加到输出文件。捕获时，输出不会显示在控制台上：
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)
寻呼
启用分页后，一次仅获取一页数据后，将显示获取下一页的提示。默认情况下，页面大小为 100 时启用分页。使用 PAGING OFF 禁用此提示：
dba@cqlsh:tes> paging
Query paging is currently enabled. Use PAGING OFF to disable
Page size: 100
dba@cqlsh:tes>
扩张
EXPAND on/off 启用或禁用行的垂直打印。当获取大量列或单个列的内容很大时，启用 EXPAND 很有帮助：
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)
资源
读取文件内容后，将文件内容的每一行作为 CQL 语句处理：
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
追踪
启用或禁用查询跟踪。在启用跟踪时完成查询运行后，将生成查询期间发生的事件的跟踪。在创建跟踪会话时进行跟踪的查询和结果存储在两个表中，即 system_traces.events 和 system_traces.sessions。
system_traces.sessions：此表保存查询操作的高级详细信息，例如 session_id、client、command、coordinator、coordinator_port、duration 和参数。
system_traces.events：此表包含有关查询操作的更详细信息，例如 session_id、event_id、activity、source、source_elapsed、source_port 和 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 命令时，它会从 ~/.cassandra 中读取一个文件“cqlshrc”。您可以在 ~/.cassandra/cqlshrc 文件中配置选项。您可以从 $CASSANDRA_HOME/conf/cqlshrc.sample 中找到的软件获取 cqlshrc 示例文件。我使用的选项如下：
cassandra@ip-10-10-0-70:.cassandra $ cat cqlshrc
[authentication]
username = cassandra
password = cassandra
[connection]
hostname=xx.xx.xx.xx
port=9042
在同一目录中，还有更多有用的文件。文件“cqlsh_history”记录了在 cqlsh 中键入的所有命令。文件“nodetool.history”记录了节点工具的历史。为这些文件提供正确的所有权和安全性很重要：
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 $
还有一些有用的 cqlsh 命令。下面列出：
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. <br><br>
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
上面的命令是我用得最多的。如果您使用其他有用的命令，请在下面的评论中分享！
原文标题：USEFUL CQLSH COMMANDS FOR EVERYDAY USE
原文作者：Anil Mittana
原文地址：https://blog.pythian.com/useful-cqlsh-commands-for-everyday-use/
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
