如何使用cqlsh访问Cassandra
Cassandra包中提供了cqlsh工具,cqlsh指的是CQL(Cassandra查询语言) shell。是一种命令行shell,作为客户端,用于执行Cassandra查询语言。
1.下载和安装
https://geminidb-community.obs.cn-north-1.myhuaweicloud.com/Gemini_Cassandra_tools_cqlsh.zip
2. 启动cqlsh
1)解压Cassandra包后,cqlsh脚本在bin目录下。启动命令如下:
cqlsh {host} {port} -u {username} -p {password}
2)如果您需要经常连接到特定节点,您可以使用配置文件启动, 将参数统一保存在 {cqlshrc_file} 中,执行如下命令即可启动cqlsh
cqlsh --cqlshrc {cqlshrc_file}
cqlshrc_file中配置如下,更多配置参考附件: cqlshrc.txt
[authentication] username = {username} password = {password} [connection] hostname = {host} port = {port}
3)想要获取更多的cqlsh的命令和参数,执行
cqlsh --help
3. cqlsh常用命令
1)使用HELP命令可以查看所有支持的命令
rwuser@cqlsh> HELP Documented shell commands: =========================== CAPTURE CLS COPY DESCRIBE EXPAND LOGIN SERIAL SOURCE UNICODE CLEAR CONSISTENCY DESC EXIT HELP PAGING SHOW TRACING CQL help topics: ================ AGGREGATES CREATE_KEYSPACE DROP_TRIGGER TEXT ALTER_KEYSPACE CREATE_MATERIALIZED_VIEW DROP_TYPE TIME ALTER_MATERIALIZED_VIEW CREATE_ROLE DROP_USER TIMESTAMP ALTER_TABLE CREATE_TABLE FUNCTIONS TRUNCATE ALTER_TYPE CREATE_TRIGGER GRANT TYPES ALTER_USER CREATE_TYPE INSERT UPDATE APPLY CREATE_USER INSERT_JSON USE ASCII DATE INT UUID BATCH DELETE JSON BEGIN DROP_AGGREGATE KEYWORDS BLOB DROP_COLUMNFAMILY LIST_PERMISSIONS BOOLEAN DROP_FUNCTION LIST_ROLES COUNTER DROP_INDEX LIST_USERS CREATE_AGGREGATE DROP_KEYSPACE PERMISSIONS CREATE_COLUMNFAMILY DROP_MATERIALIZED_VIEW REVOKE CREATE_FUNCTION DROP_ROLE SELECT CREATE_INDEX DROP_TABLE SELECT_JSON
2)如果需要查看特定命令的帮助。可以使用HELP {COMMAND} 去查看特定命令用法
比如查看DESC命令的使用方法 HELP DESC,使用DESC命令可以输出集群的相关信息。比如 DESC CLUSTER 可以显示集群的名称及采用的Partitioner;DESC KEYSPACES 可以显示集群中的所有keyspace,包括系统库和自己创建的keyspace。
rwuser@cqlsh> DESC CLUSTER; Cluster: Test Cluster Partitioner: Murmur3Partitioner rwuser@cqlsh> desc KEYSPACES; system_traces system_schema system_auth system system_distributed
3)DDL 和 DML语句演示
创建keyspace语法如下:
CREATE KEYSPACE [IF NOT EXISTS] keyspace_name WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : N } | 'class' : 'NetworkTopologyStrategy', 'dc1_name' : N [, ...]、 } [AND DURABLE_WRITES = true|false] ;
创建名为 example的keyspace; replcation选项用于指定副本位置策略为简单策略和所需副本的数量为3。
CREATE KEYSPACE IF NOT EXISTS example WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '3'};
使用 DESC example 命令来验证创建。 使用use example 命令切换到创建的keyspace
rwuser@cqlsh> DESCRIBE example; CREATE KEYSPACE example WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '3'} AND durable_writes = true; rwuser@cqlsh> use example; rwuser@cqlsh:example>
创建表语法如下:
CREATE TABLE [IF NOT EXISTS] [keyspace_name.]table_name ( column_definition [, ...] PRIMARY KEY (column_name [, column_name ...]) [WITH table_options | CLUSTERING ORDER BY (clustering_column_name order]) | ID = 'table_hash_tag' | COMPACT STORAGE]
创建一个测试表test_table,定义三个列,分别是user_id,age,user_name。user_id是int类型,表示用户的ID号;age也是int类型,代表用户的年龄;user_name 是text类型,表示用户的名称。 主键为 user_id。
CREATE TABLE test_table(user_id int, age int, user_name text, PRIMARY KEY(user_id));
同样使用DESC test_table 命令验证创建的表
rwuser@cqlsh:example> desc test_table ; CREATE TABLE example.test_table ( user_id int PRIMARY KEY, age int, user_name text ) WITH bloom_filter_fp_chance = 0.01 AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'} AND comment = '' AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'} AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'} AND crc_check_chance = 1.0 AND dclocal_read_repair_chance = 0.1 AND default_time_to_live = 0 AND gc_grace_seconds = 864000 AND max_index_interval = 2048 AND memtable_flush_period_in_ms = 0 AND min_index_interval = 128 AND read_repair_chance = 0.0 AND speculative_retry = '99PERCENTILE';
插入数据语法如下:
INSERT INTO [keyspace_name.] table_name (column_list) VALUES (column_values) [IF NOT EXISTS] [USING TTL seconds | TIMESTAMP epoch_in_microseconds]
查询数据语法如下:
SELECT * | select_expression | DISTINCT partition FROM [keyspace_name.] table_name [WHERE partition_value [AND clustering_filters [AND static_filters]]] [ORDER BY PK_column_name ASC|DESC] [LIMIT N] [ALLOW FILTERING]
插入4条记录后进行查询。
INSERT INTO test_table (user_id, age, user_name) VALUES (1, 17, 'ZhangXiao'); INSERT INTO test_table (user_id, age, user_name) VALUES (2, 16, 'DongXin'); INSERT INTO test_table (user_id, age, user_name) VALUES (3, 17, 'HuFang'); INSERT INTO test_table (user_id, age, user_name) VALUES (4, 15, 'WangGuoHua'); rwuser@cqlsh:example> select * from test_table; user_id | age | user_name ---------+-----+------------ 1 | 17 | ZhangXiao 2 | 16 | DongXin 4 | 15 | WangGuoHua 3 | 17 | HuFang (4 rows)
更新表结构语法如下:
ALTER TABLE [keyspace_name.] table_name [ALTER column_name TYPE cql_type] [ADD (column_definition_list)] [DROP column_list | COMPACT STORAGE ] [RENAME column_name TO column_name] [WITH table_properties];
发现创建的表结构中缺少 性别gender字段,我们现在要修改一下表结构,新增一个text类型的字段。同样可以使用DESC test_table 命令验证新增的gender列.
rwuser@cqlsh:example> ALTER TABLE test_table ADD gender text; rwuser@cqlsh:example> desc test_table ; CREATE TABLE example.test_table ( user_id int PRIMARY KEY, age int, gender text, user_name text ) WITH bloom_filter_fp_chance = 0.01 AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'} AND comment = '' AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'} AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'} AND crc_check_chance = 1.0 AND dclocal_read_repair_chance = 0.1 AND default_time_to_live = 0 AND gc_grace_seconds = 864000 AND max_index_interval = 2048 AND memtable_flush_period_in_ms = 0 AND min_index_interval = 128 AND read_repair_chance = 0.0 AND speculative_retry = '99PERCENTILE';
更新数据语法如下:
UPDATE [keyspace_name.] table_name [USING TTL time_value | USING TIMESTAMP timestamp_value] SET assignment [, assignment] . . . WHERE row_specification [IF EXISTS | IF condition [AND condition] . . .] ;
更新每一条记录,增加gender字段的值。
UPDATE example.test_table SET gender = 'female' WHERE user_id = 1; UPDATE example.test_table SET gender = 'male' WHERE user_id = 2; UPDATE example.test_table SET gender = 'male' WHERE user_id = 3; UPDATE example.test_table SET gender = 'female' WHERE user_id = 4;
删除数据语法如下:
DELETE [column_name (term)][, ...] FROM [keyspace_name.] table_name [USING TIMESTAMP timestamp_value] WHERE PK_column_conditions [IF EXISTS | IF static_column_conditions]
需要删除user_id 为4的用户的age列
rwuser@cqlsh:example> DELETE age FROM example.test_table WHERE user_id=4; rwuser@cqlsh:example> select * from example.test_table WHERE user_id = 4; user_id | age | gender | user_name ---------+------+--------+------------ 4 | null | female | WangGuoHua (1 rows)
删除user_id 为3的 整条记录
rwuser@cqlsh:example> DELETE FROM example.test_table WHERE user_id=3; rwuser@cqlsh:example> select * from example.test_table WHERE user_id = 3; user_id | age | gender | user_name ---------+-----+--------+----------- (0 rows)
清空表数据语法如下:
TRUNCATE [TABLE] [keyspace_name.table_name]
清空表中的所有记录
rwuser@cqlsh:example> truncate example.test_table ; rwuser@cqlsh:example> select * from example.test_table; user_id | age | gender | user_name ---------+-----+--------+----------- (0 rows)
删除表语法如下:
DROP TABLE [IF EXISTS] keyspace_name.table_name
删除创建的 test_table表
rwuser@cqlsh:example> DROP TABLE example.test_table ; rwuser@cqlsh:example> desc example.test_table ; 'test_table' not found in keyspace 'example'
删除keyspace语法如下:
DROP KEYSPACE [IF EXISTS] keyspace_name
删除创建的keyspace example
rwuser@cqlsh:example> drop KEYSPACE example; rwuser@cqlsh:example> desc example; Keyspace 'example' not found.
- 点赞
- 收藏
- 关注作者
评论(0)