Use cqlsh to manage an ApsaraDB for Cassandra instance - ApsaraDB for Cassandra - Deprecated

This topic describes how to manage an ApsaraDB for Cassandra instance by using cqlsh. cqlsh is a command-line shell used to interact with Cassandra based on Cassandra Query Language (CQL). You can install Cassandra on an on-premises machine or on an Elastic Compute Service (ECS) instance.

Download and install Cassandra

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

Use cqlsh to connect to an ApsaraDB for Cassandra instance

Log on to the ApsaraDB for Cassandra console, find your ApsaraDB for Cassandra instance, and then obtain the endpoint and port number 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 number to the $CQLSH_HOST and $CQLSH_PORT environment variables. You can run the bin/cqlsh -help command to view more parameters that are supported by cqlsh.

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 settings 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

Query environment variables

You can execute the DESCRIBE statement to query the values of environment variables that are used in 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, and ByteOrderedPartitioner. In Cassandra of versions earlier than 1.2, the default partitioner is RandomPartitioner. In Cassandra 1.2 and later, the default partitioner is Murmur3Partitioner.

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

cqlsh> DESCRIBE KEYSPACES;

The system returns all keyspaces that are provided by the ApsaraDB for Cassandra instance, such as system_traces, system_schema, system_auth, and system_distributed, and the keyspaces that you have created.

To query the versions of cqlsh, Cassandra, and the 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 in an ApsaraDB for Cassandra instance are similar to databases in an ApsaraDB RDS instance. A 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. Then, 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, a keyspace named test_keyspace is created, and the replication mode is set to SimpleStrategy. In addition, the ApsaraDB for Cassandra instance used for testing consists of only one node. Therefore, the replication factor is set to 1. However, if the ApsaraDB for Cassandra instance is used in a production environment, we recommend that you set the replication factor to 3.

After you create a keyspace, you can execute the DESCRIBE KEYSPACE statement to query the keyspace. 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 statements:

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

In this example, a table named test_user is created in the test_keyspace keyspace. The table contains two fields of the TEXT data type: 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 CQL statements that 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>

You can execute the DESCRIBE TABLE statement to query all the statements that you executed to create the table and the schema of the table, including the custom settings and default settings.

Read and write data

You can execute the INSERT INTO statement to insert data into a table. Example:

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 ('Li');

The preceding statements are used to insert three data records into the test_user table. In the last data record, only the value of the first_name field is specified and the last_name field is left empty.

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 returned results show that the data records are inserted. You can also execute the following statement to query the data records:

cqlsh:test_keyspace> SELECT * FROM test_user;
 first_name | last_name
------------+-----------
       test |    Hadoop
       Wang |      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 value is Wang. 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 a row

You can execute the DELETE statement to delete one or more columns. For example, you can execute the following statements to delete the last_name column:

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 execute 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 value of the first_name field is test is deleted.

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

cqlsh:test_keyspace> INSERT INTO test_user (first_name, last_name) VALUES ('Wang', 'Shi');
cqlsh:test_keyspace> SELECT * FROM test_user;
 first_name | last_name
------------+-----------
       Wang |       Shi
      Zhang |       San
(2 rows)

A value of Shi is returned for the last_name field associated with the first_name field whose value is Wang.

If you execute 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
------------+-----------
       Wang |       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
------------+-----------
       Wang |      Shi
      Zhang |       San
         Li |        Si
(3 rows)
cqlsh:test_keyspace>

A data record whose value of the first_name field 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. Example:

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