Use Lindorm-cqlsh to connect to LindormTable - Lindorm - Alibaba Cloud Documentation Center

Lindorm-cqlsh is a shell that is developed by the Lindorm team based on Cassandra cqlsh. Lindorm-cqlsh supports the following operating systems: Windows and Unix. To use Lindorm-cqlsh to connect to and use LindormTable, install Lindorm-cqlsh on an on-premises machine or an Elastic Compute Service (ECS) instance.

Prerequisites

The IP address of your client is added to the whitelist of your Lindorm instance. For more information, see Configure whitelists.
The LindormTable endpoint that is displayed after CQL Connection on the Wide Table Engine tab of the Lindorm console is obtained. For more information, see View endpoints.
A Lindorm-cqlsh file is downloaded and extracted. You can download the file from the following link: Lindorm-cqlsh.
If Lindorm-cqlsh is deployed on an ECS instance, make sure that the Lindorm instance and the ECS instance meet the following requirements to ensure network connectivity: For more information about how to view the ECS instance information, see View instance information.
- The Lindorm instance and the ECS instance are deployed in the same region. We recommend that you deploy the two instances in the same zone to reduce network latency.
- The network type of the Lindorm instance is the same as that of the ECS instance.

Common commands

After you use Lindorm-cqlsh to connect to LindormTable, you can run the following commands:

Documented shell commands:===========================
CAPTURE  COPY  DESCRIBE  LOGIN   DESC  EXIT  HELP PAGING  SHOW
CQL help topics:================
CREATE_KEYSPACE        TEXT                ALTER_KEYSPACE           TIME       CREATE_ROLE
DROP_USER              TIMESTAMP           ALTER_TABLE              CREATE_TABLE
GRANT                  ALTER_USER          INSERT                   UPDATE   
CREATE_USER            INSERT_JSON         USE                      ASCII
DATE                   INT                 UUID                     BATCH
DELETE                 JSON                BEGIN                    KEYWORDS        
BLOB                   DROP_COLUMNFAMILY   LIST_PERMISSIONSBOOLEAN  LIST_ROLES      
COUNTER                DROP_INDEX          LIST_USERS               DROP_KEYSPACE   
PERMISSIONS            CREATE_COLUMNFAMILY REVOKE                   DROP_ROLE                 
SELECT                 CREATE_INDEX        DROP_TABLE                SELECT_JSON

Procedure

Run the following command to connect Lindorm-cqlsh to LindormTable:
```
bin/cqlsh <host> <port> -u <username> -p <password>
```
Note
- We recommend that you set the Python version of the path in which bin/cqlsh is located to 2.7.x.
- <host>: The LindormTable endpoint that is displayed after CQL Connection on the Wide Table Engine tab of the Lindorm console. Example: ld-bp17j28j2y7pm****-proxy-lindorm.lindorm.rds.aliyuncs.com.
- <port>: the port number that corresponds to the endpoint. Default value: 9042.
- <username>: the username that you want to use to connect to LindormTable. Default value: root.
- <password>: the password that corresponds to the username. If you forget the password, you can change the password in the cluster management system. For more information, see Change the password of a user.
- Lindorm allows you to run various commands after you use Lindorm-cqlsh to connect to Lindorm. To check the commands, run bin/cqlsh -help.
Use Lindorm-cqlsh to create a keyspace. The concept of a keyspace in Lindorm is similar to the concept of a database in a relational database system. A keyspace can contain one or more tables or column families.
```
CREATE KEYSPACE test_keyspace WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};
```
After the keyspace is created, run the following command to query the keyspace:
```
DESCRIBE KEYSPACE test_keyspace;
```
Run the USE command to switch to the keyspace.
```
USE test_keyspace;
```
The following information is returned:
```
cqlsh:test_keyspace>
```
After you use Lindorm-cqlsh to connect to LindormTable, run the supported commands based on your business requirements. The following examples show how to run common commands:
- Use Lindorm-cqlsh to create a table named test_user. The table contains two columns of the TEXT data type: first_name and last_name. The first_name field is the primary key of the table.
```
CREATE TABLE test_user(first_name text, last_name text, PRIMARY KEY (first_name));
```
  Note
  If you do not run the USE test_keyspace; command before you create a table, you can run the following command to create a table:
```
CREATE TABLE test_keyspace.test_user(first_name text, last_name text, PRIMARY KEY (first_name));
```
- Query the information about the test_user table.
```
DESCRIBE TABLE test_user;
```
  The following information is returned:
```
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';
```
  Note
  You can execute the DESCRIBE TABLE statement to query the statement that you execute to create the table. The returned information shows the custom settings and default settings in a formatted manner. Lindorm is not fully compatible with the table attributes that are supported by Cassandra. If a table attribute is not supported by Lindorm, the system still returns the attribute. The returned value is the default attribute value in Cassandra.
  - Lindorm is not compatible with the following table attributes: crc_check_chance, gc_grace_seconds, read_repair_chance, speculative_retry, dclocal_read_repair_chance, and crc_check_chance.
  - Lindorm is compatible with the following table attributes: compression and default_time_to_live.
- Insert data into the test_user table.
```
INSERT INTO test_user (first_name, last_name) VALUES ('test', 'LINDORM');
INSERT INTO test_user (first_name, last_name) VALUES ('Zhang', 'San');
INSERT INTO test_user (first_name) VALUES ('Wu');
```
- Check whether the data is inserted as expected. We recommend that you do not run the following command in scenarios in which a large volume of data is inserted:
```
SELECT COUNT(*) FROM test_user; 
```
  The following information is returned:
```
  count
-------     
    3

(1 rows)
```
  You can run the following command to query the details of the inserted data:
```
SELECT * FROM test_user;
```
  The following information is returned:
```
first_name | last_name
------------+-----------
       test |    LINDORM
         Wu |      null
      Zhang |       San

(3 rows)
```
- Delete a value in the last_name column from a specified data row and query the data row.
```
DELETE last_name FROM test_user WHERE first_name='test';
SELECT * FROM test_user WHERE first_name='test';
```
  The following information is returned:
```
first_name | last_name
------------+-----------
       test |    null
```
- Delete the data of a specified data row and query the data row.
```
DELETE FROM test_user WHERE first_name='test';
SELECT * FROM test_user WHERE first_name='test';
```
  The following information is returned:
```
first_name | last_name
------------+-----------

(0 rows)
```
- Remove all data from a table or delete a table. Only a superuser can execute the TRUNCATE, DROP TABLE, and DROP KEYSPACE statements.
```
TRUNCATE test_user;
DROP TABLE test_user;
```
- Use a search index in Lindorm.
  Run Lindorm Cassandra Query Language (CQL) commands to create a search index for a wide table in LindormTable. Before you create a search index for a wide table, make sure that the attributes of the table meet the corresponding requirements. For more information about the requirements, submit a ticket to contact technical support.
```
CREATE TABLE test_keyspace.tb (cn1 text PRIMARY KEY, cn2 text, cn3 text) WITH extensions = {'CONSISTENCY_TYPE':'strong', 'MUTABILITY':'MUTABLE_LATEST'};
CREATE SEARCH INDEX tbidx ON test_keyspace.tb WITH COLUMNS (cn2, cn3);
REBUILD SEARCH INDEX ON test_keyspace.tb ;
INSERT INTO test_keyspace.tb (cn1, cn2, cn3) VALUES ('v11', 'v12', 'v13');
SELECT * FROM test_keyspace.tb WHERE cn2 like '%v1';
```
  Note
  The preceding sample code creates an index table named tbidx for the tb table, writes data to the tb table, and performs a fuzzy search.
- Use a secondary index in Lindorm.
  Run Lindorm CQL commands to create a secondary index for a wide table in LindormTable. Before you create a secondary index for a wide table, make sure that the attributes of the table meet the corresponding requirements. For information about the requirements, see Secondary index.
```
CREATE TABLE test_keyspace.tb (cn1 text PRIMARY KEY, cn2 text, cn3 text) WITH extensions = {'CONSISTENCY_TYPE':'strong', 'MUTABILITY':'MUTABLE_LATEST'};
CREATE INDEX tbidx ON test_keyspace.tb (cn2);
INSERT INTO test_keyspace.tb (cn1, cn2, cn3) VALUES ('v11', 'v12', 'v13');
SELECT * FROM test_keyspace.tb WHERE cn2 = 'v12';
```