cqlsh is a command line shell used to interact with Cassandra based on Cassandra Query Language (CQL).

You can download the latest version of Cassandra from the official Apache Cassandra website and decompress the downloaded software package to install Cassandra.

$ wget http://mirror.bit.edu.cn/apache/cassandra/3.11.4/apache-cassandra-3.11.4-bin.tar.gz
$ tar -zxf apache-cassandra-3.11.4-bin.tar.gz 
$ cd apache-cassandra-3.11.4 
		

Start cqlsh

Log on to the ApsaraDB for Cassandra console, find your ApsaraDB for Cassandra instance, and obtain the endpoint and port of the node to which you want to connect. Then, run the following command to connect to the node:

bin/cqlsh $host $port -u $username -p $password
			

If you need to connect to a node frequently, we recommend that you save its endpoint and port to the $CQLSH_HOST and $CQLSH_PORT environment variables. For information about the parameters supported by cqlsh, run the bin/cqlsh -help command.

Common CQL statements

You can run the HELP or ? command in cqlsh to view all CQL statements that can be used to manage ApsaraDB for Cassandra instances.

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
			

If you want to know how to execute a specific CQL statement, run the HELP command on that statement. Some CQL statements do not allow you to specify parameters. If you execute such a CQL statement, the system returns the setting queried by that statement. Such CQL statements include CONSISTENCY, EXPAND, and PAGING. Examples:

cqlsh> CONSISTENCY
Current consistency level is ONE.
cqlsh> EXPAND
Expanded output is currently disabled. Use EXPAND ON to enable.
cqlsh> PAGING
Query paging is currently enabled. Use PAGING OFF to disable
Page size: 100
			

View environment variables

You can execute the DESCRIBE statement to view the values of environment variables used on an ApsaraDB for Cassandra instance. Example:

cqlsh> DESCRIBE CLUSTER;

Cluster: Test Cluster
Partitioner: Murmur3Partitioner
			

The DESCRIBE CLUSTER statement queries the name and partitioner of the ApsaraDB for Cassandra instance. You can choose one of the following four partitioners: RandomPartitioner, Murmur3Partitioner, OrderPreservingPartitioner, or ByteOrderedPartitioner. In versions earlier than Cassandra 1.2, the default partitioner is RandomPartitioner. From Cassandra 1.2 onwards, the default partitioner is Murmur3Partitioner.

To query the keyspaces available on an ApsaraDB for Cassandra instance, execute the following statement:

cqlsh> DESCRIBE KEYSPACES;
system_traces system_schema system_auth system system_distributed

The system returns all keyspaces provided with the ApsaraDB for Cassandra instance and the keyspaces you have created.

To query the versions of cqlsh, Cassandra, and protocol, execute the following statement:

cqlsh> SHOW VERSION;
[cqlsh 5.0.1 | Cassandra 3.11.4 | CQL spec 3.4.4 | Native protocol v4]
			

Create a keyspace

Keyspaces on an ApsaraDB for Cassandra instance are similar to databases on an ApsaraDB for RDS instance. One keyspace contains one or more tables or column families. If you start cqlsh and do not specify a keyspace, the cqlsh> command prompt is displayed, after which you can execute the CREATE KEYSPACE statement to create a keyspace. Example:

cqlsh> CREATE KEYSPACE test_keyspace WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};
cqlsh>
			

In this example, you create a keyspace named test_keyspace and set the replication mode to SimpleStrategy. In addition, the ApsaraDB for Cassandra instance used for test consists of only one node. Therefore, set the replication factor to 1. However, if the ApsaraDB for Cassandra instance is in a production environment, we recommend that you set the replication factor to 3.

After you create the keyspace, you can execute the DESCRIBE KEYSPACE statement to query it. Example:

cqlsh> DESCRIBE KEYSPACE  test_keyspace;

CREATE KEYSPACE test_keyspace WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}  AND durable_writes = true;
			

You can also execute the USE statement to switch to the keyspace. Example:

cqlsh> USE test_keyspace;
cqlsh:test_keyspace>
			

Create a table

To create a table, execute the following statement:

cqlsh> use test_keyspace;
cqlsh:test_keyspace> CREATE TABLE test_user (first_name text , last_name text, PRIMARY KEY (first_name)) ;
			

In this example, you create a table named test_user in the test_keyspace keyspace. The table contains two fields in the TEXT format: first_name and last_name. The first_name field is the primary key of the table. You can also execute the following statement to create the test_user table in the test_keyspace keyspace:

cqlsh> CREATE TABLE test_keyspace.test_user(first_name text , last_name text, PRIMARY KEY (first_name)) ;
			

To query the SQL statements you executed to create the test_user table in the test_keyspace keyspace, execute the following statement:

cqlsh:test_keyspace> DESCRIBE TABLE test_user;

CREATE TABLE test_keyspace.test_user (
    first_name text PRIMARY KEY,
    last_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';

cqlsh:test_keyspace>
			

The system returns all CQL statements you executed to create the test_user table in the test_keyspace, along with the user-defined and default settings.

Read and write data

You can execute the INSERT INTO statement to insert data. Examples:

cqlsh:test_keyspace> INSERT INTO test_user (first_name, last_name) VALUES ('test', 'Hadoop');
cqlsh:test_keyspace> INSERT INTO test_user (first_name, last_name) VALUES ('Zhang', 'San');
cqlsh:test_keyspace> INSERT INTO test_user (first_name) VALUES ('Wu');
			

The preceding statements insert three data records into the test_user table. In the last data record, the first_name field is specified with a key and the last_name field is not specified.

You can execute the SELECT COUNT statement to check whether the data records are inserted. Example:

cqlsh:test_keyspace> SELECT COUNT(*) FROM test_user;

 count
-------
     3

(1 rows)

Warnings :
Aggregation query used without partition key
			

The return result shows that the data records are inserted. You can also execute the following statement to query the data records you insert:

cqlsh:test_keyspace> SELECT * FROM test_user;

 first_name | last_name
------------+-----------
       test |    Hadoop
         Wu |      null
      Zhang |       San

(3 rows)
cqlsh:test_keyspace> SELECT * FROM test_user WHERE first_name='test';

 first_name | last_name
------------+-----------
       test |    Hadoop

(1 rows)
			

A null value is returned for the last_name field associated with the first_name field whose key is Wu. In Cassandra, a null value indicates that the column specified by the field does not have data, and a column without data does not occupy space in the storage system. However, in common relational databases, a column occupies storage space even if it does not have data.

Delete a column or row

You can use the DELETE statement to delete one or more columns. To delete the last_name column, execute the following statements:

cqlsh:test_keyspace> DELETE last_name FROM test_user WHERE first_name='test';
cqlsh:test_keyspace> SELECT * FROM test_user WHERE first_name='test';

 first_name | last_name
------------+-----------
       test |      null

(1 rows)
			

The column specified by the last_name field is deleted.

You can also use the DELETE statement to delete a row. Example:

cqlsh:test_keyspace> DELETE FROM test_user WHERE first_name='test';
cqlsh:test_keyspace> SELECT * FROM test_user WHERE first_name='test';

 first_name | last_name
------------+-----------

(0 rows)
cqlsh:test_keyspace>
			

The data record whose key is test is deleted.

The INSERT and UPDATE statements used together are equal to the UPSERT statement. If the key associated with the new data record you want to insert already exists, the system does not update the existing data record with the same key, but inserts the new data record and deletes the existing one.

cqlsh:test_keyspace> INSERT INTO test_user (first_name, last_name) VALUES ('Wu', 'Shi');
cqlsh:test_keyspace> SELECT * FROM test_user;

 first_name | last_name
------------+-----------
         Wu |       Shi
      Zhang |       San

(2 rows)
			

A value other than null is returned for the last_name field whose key is Wu.

If you use the UPDATE statement to update a data record that does not exist, the system inserts the data record. Example:

cqlsh:test_keyspace> SELECT * FROM test_user;

 first_name | last_name
------------+-----------
         Wu |       Shi
      Zhang |       San

(2 rows)
cqlsh:test_keyspace> UPDATE test_user SET last_name = 'Si' WHERE first_name = 'Li';
cqlsh:test_keyspace> SELECT * FROM test_user;

 first_name | last_name
------------+-----------
         Wu |       Shi
      Zhang |       San
         Li |        Si

(3 rows)
cqlsh:test_keyspace>
			

A data record whose key is Li is inserted into the table, but this data record does not exist before the update.

Clear or delete a table

You can execute the TRUNCATE or DROP TABLE statement to clear or delete a table. Examples:

cqlsh:test_keyspace> TRUNCATE test_user;
cqlsh:test_keyspace> DROP TABLE test_user;