cassandra常用命令

一、capture用于捕获命令的输出并将其添加到文件

hadoop@datax ~]$ touch 1.txt
[hadoop@datax ~]$ cqlsh
Connected to Test Cluster at 127.0.0.1:9042
[cqlsh 6.0.0 | Cassandra 4.0.0 | CQL spec 3.4.5 | Native protocol v5]
Use HELP for help.
cqlsh> use store
... ;
cqlsh:store> CAPTURE '/home/hadoop/1.txt'
Now capturing query output to '/home/hadoop/1.txt'.
cqlsh:store> select * from shopping_cart ;
cqlsh:store> CAPTURE off

cqlsh:store> exit
[hadoop@datax ~]$ cat 1.txt

userid | item_count | last_update_timestamp
--------+------------+---------------------------------
1234 | 5 | 2022-12-20 08:20:18.813000+0000
9876 | 2 | 2022-12-20 08:20:17.765000+0000

(2 rows)

 

二、CONSISTENCY显示当前的一致性级别，或设置新的一致性级别

 

cqlsh> CONSISTENCY
Current consistency level is ONE.

cqlsh> CONSISTENCY
Current consistency level is TWO.

三、Copy将数据从cassandra复制到文件并从中复制

cqlsh:store> select * from shopping_cart ;

userid | item_count | last_update_timestamp
--------+------------+---------------------------------
1234 | 5 | 2022-12-20 08:20:18.813000+0000
9876 | 2 | 2022-12-20 08:20:17.765000+0000

(2 rows)
cqlsh:store> copy shopping_cart(userid, item_count , last_update_timestamp) to '/home/hadoop/2.txt'
... ;   #这里表后面加不加字段不影响输出结果
Using 1 child processes

Starting copy of store.shopping_cart with columns [userid, item_count, last_update_timestamp].
Processed: 2 rows; Rate: 17 rows/s; Avg. rate: 17 rows/s
2 rows exported to 1 files in 0.122 seconds.

cqlsh:store> exit
[hadoop@datax ~]$ cat 2.txt
1234,5,2022-12-20 08:20:18.813+0000
9876,2,2022-12-20 08:20:17.765+0000

 

四、describe描述casscadra及其对像的当前集群

 

[hadoop@datax ~]$ cqlsh
Connected to Test Cluster at 127.0.0.1:9042
[cqlsh 6.0.0 | Cassandra 4.0.0 | CQL spec 3.4.5 | Native protocol v5]
Use HELP for help.
cqlsh> DESCRIBE cluster

 

Cluster: Test Cluster
Partitioner: Murmur3Partitioner
Snitch: DynamicEndpointSnitch

 

cqlsh> DESCRIBE KEYSPACEs;

store system_auth system_schema system_views
system system_distributed system_traces system_virtual_schema

cqlsh> DESCRIBE tables;

Keyspace store
--------------
shopping_cart

Keyspace system
---------------
available_ranges paxos size_estimates
available_ranges_v2 peer_events sstable_activity
batches peer_events_v2 table_estimates
built_views peers transferred_ranges
compaction_history peers_v2 transferred_ranges_v2
"IndexInfo" prepared_statements view_builds_in_progress
local repairs

Keyspace system_auth
--------------------
network_permissions role_members roles
resource_role_permissons_index role_permissions

Keyspace system_distributed
---------------------------
parent_repair_history repair_history view_build_status

Keyspace system_schema
----------------------
aggregates dropped_columns indexes tables types
columns functions keyspaces triggers views

Keyspace system_traces
----------------------
events sessions

Keyspace system_views
---------------------
caches internode_inbound rows_per_read
clients internode_outbound settings
coordinator_read_latency local_read_latency sstable_tasks
coordinator_scan_latency local_scan_latency system_properties
coordinator_write_latency local_write_latency thread_pools
disk_usage max_partition_size tombstones_per_read

Keyspace system_virtual_schema
------------------------------
columns keyspaces tables

 

cqlsh> use store;
cqlsh:store> DESCRIBE TABLE shopping_cart;

CREATE TABLE store.shopping_cart (
userid text PRIMARY KEY,
item_count int,
last_update_timestamp timestamp
) WITH additional_write_policy = '99p'
AND bloom_filter_fp_chance = 0.01
AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
AND cdc = false
AND comment = ''
AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
AND compression = {'chunk_length_in_kb': '16', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
AND crc_check_chance = 1.0
AND default_time_to_live = 0
AND extensions = {}
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 = 'BLOCKING'
AND speculative_retry = '99p';

 

cqlsh:store> DESCRIBE COLUMNFAMILIES ;

shopping_cart

五、expand扩展输出

 

cqlsh> EXPAND on ;
Now Expanded output is enabled
cqlsh> use store
... ;
cqlsh:store> select * from shopping_cart ;

 

@ Row 1
-----------------------+---------------------------------
userid | 1234
item_count | 5
last_update_timestamp | 2022-12-20 08:20:18.813000+0000

 

@ Row 2
-----------------------+---------------------------------
userid | 9876
item_count | 2
last_update_timestamp | 2022-12-20 08:20:17.765000+0000

 

(2 rows)

 

六、source可以在文件中执行命令

 

[hadoop@datax ~]$ cat t2.txt
use store;
select * from shopping_cart;
[hadoop@datax ~]$ cqlsh
Connected to Test Cluster at 127.0.0.1:9042
[cqlsh 6.0.0 | Cassandra 4.0.0 | CQL spec 3.4.5 | Native protocol v5]
Use HELP for help.
cqlsh> source '/home/hadoop/'
.bash_history .bash_logout .bash_profile .bashrc .cache/ .cassandra/ .pki/ .ssh/ 1.txt 2.txt t2.txt
cqlsh> source '/home/hadoop/t2.txt'

 

userid | item_count | last_update_timestamp
--------+------------+---------------------------------
1234 | 5 | 2022-12-20 08:20:18.813000+0000
9876 | 2 | 2022-12-20 08:20:17.765000+0000

 

(2 rows)

 

七、修改复制因子

cqlsh:store> select * from system_schema.keyspaces ;

keyspace_name | durable_writes | replication
--------------------+----------------+-------------------------------------------------------------------------------------
system_auth | True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '1'}
store | True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '1'}
system_schema | True | {'class': 'org.apache.cassandra.locator.LocalStrategy'}
system_distributed | True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '3'}
system | True | {'class': 'org.apache.cassandra.locator.LocalStrategy'}
system_traces | True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '2'}

(6 rows)
cqlsh:store> alter KEYSPACE store WITH replication = {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '3'}
... ;

Warnings :
When increasing replication factor you need to run a full (-full) repair to distribute the data.

Your replication factor 3 for keyspace store is higher than the number of nodes 1

cqlsh:store> select * from system_schema.keyspaces ;

keyspace_name | durable_writes | replication
--------------------+----------------+-------------------------------------------------------------------------------------
system_auth | True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '1'}
store | True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '3'}
system_schema | True | {'class': 'org.apache.cassandra.locator.LocalStrategy'}
system_distributed | True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '3'}
system | True | {'class': 'org.apache.cassandra.locator.LocalStrategy'}
system_traces | True | {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '2'}

(6 rows)

 

八、批量处理

现有表及内容

cqlsh:store> select * from shopping_cart ;

userid | item_count | last_update_timestamp

cqlsh:store> SELECT * FROM shopping_cart ;

userid | item_count | last_update_timestamp
--------+------------+---------------------------------
9886 | 88 | 2022-12-20 09:19:33.682000+0000
1234 | 5 | 2022-12-20 08:20:18.813000+0000
9876 | 2 | 2022-12-20 08:20:17.765000+0000

 

批处理内容：

 会保证事务的原子性，要么都成功执行，要么回退。

cqlsh:store> BEGIN BATCH

INSERT INTO store.shopping_cart (userid, item_count, last_update_timestamp) VALUES ('8886', 88, toTimeStamp(now()));

UPDATE shopping_cart set item_count=6 where userid='1234';

delete item_count from shopping_cart where userid='9876';

APPLY BATCH;

验证：