Cassandra CQLSH and CQL

Posted on Edited on In

Here I write down some very common CQL/CQLSH commands for quick revisit.

The statements are case-insensitive, for cql shell commands, no comma at end, for cql, there is.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
# launch CQL shell to enter interactive mode
cqlsh <host IP>

# better format for multiple columns query
expand on

# list keyspace names
describe keyspaces
# describe shows the all table schemas in that keyspace
describe keyspace <keyspace name>

# list table names
describe tables
# describe shows the table schema, with partition key and clustering key
describe table <table name>

# entry keyspace
use <keyspace name>;

# empty whole table data
truncate <table name>;

# capture query result into a file
# check current capture location
capture
# off the expand format when using capture
expand off
# off the paging in query result for capture
# otherwise you need to hit enter to continue the query
paging off
# set capture file path
capture '~/20230603_xxx'
# turn off capture
capture off

# number of row
select count(*) from <table>;

# Allow filtering clause
# Please use single quote for text value
# The column1 is not the table partition key(because of using Allow filtering),
# then this query will do a full cluster scan, not efficient
select * from <table> where column1='column1 value' allow filtering;

# Limit output
select * from <table> limit 3;

# Order by clause, to use it, the partition key must be defined in the WHERE
# clause and the ORDER BY clause uses the clustering key for ordering.
select pkey, rkey FROM <table> WHERE pkey='xxx' order by rkey desc limit 1;

# decode blob(binary large object) from "value" column
select blobastext(value) from <table> WHERE rkey='xxx' allow filtering;

# there is also textasblob() for insert
insert into <table> (id, data) values (1,textasblob('This is a string'));

# update general fields, you need to specify partition and clustering keys to
# identify the row, the "in" operator is helpful for multiple deletion
update <table> set field1='value1',field2='value2' where <partitionKey>='xxx' and cluseringKey in (1111,2222,3333);

# Deletion only needs partition key
# If the partition key is timestamp type, it will show as
# "2023-08-21 18:28:31.489000+0000" in the table, in deletion, please remomve
# the 000 right before the +0000, otherwise it won't work:
delete from <table> where updated_at='2023-08-21 18:28:31.489+0000';

You can also run commands without the interactive cql shell:

1
cqlsh <host IP> -e "describe tables"