# 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: