ApsaraDB for HBase:Use lindorm-cqlsh to connect to and use the wide table engine

Download and install lindorm-cqlsh

You can download the latest version of lindorm-cqlsh from the download link and decompress the downloaded file to install lindorm-cqlsh.

Run lindorm-cqlsh

You can query the endpoint, username, and password in the Lindrom console. The cluster information is required when you perform Cassandra Query Language (CQL) queries. By default, the port number is 9042. You can run the following command to connect to a Lindorm cluster:

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

In the command, $host indicates the endpoint of the Lindorm cluster to which you want to connect and $port indicates the port number. By default, the port number is 9042. If you leave $port empty, port 9042 is used. If you frequently connect to a cluster, we recommend that you save the cluster endpoint and port number in the $CQLSH_HOST and $CQLSH_PORT environment variables. If you want to obtain more parameters that are supported by lindorm-cqlsh, run the bin/cqlsh -help command.

Common commands in lindorm-cqlsh

You can run the following commands in lindorm-cqlsh to connect to and manage a Lindorm cluster:

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

Use lindorm-cqlsh to create a keyspace

Lindorm is compatible with keyspaces in Cassandra. A keyspace is similar to a database on an ApsaraDB RDS instance. A keyspace can contain one or more tables or column families. If you run lindorm-cqlsh without specifying a keyspace, the command prompt cqlsh> appears. Then, you can execute the CREATE KEYSPACE statement to create a keyspace. For example, you can execute the following statement:

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

When you execute the CREATE KEYSPACE statement in Lindorm, the replication strategy and replication factor in Lindorm are similar to those in Cassandra. By default, the replication_factor parameter in Lindorm and Cassandra is set to 2. After you create a keyspace, you can execute the DESCRIBE KEYSPACE statement to query keyspace information.

cqlsh> DESCRIBE KEYSPACE  test_keyspace;
cqlsh> 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>

Use lindorm-cqlsh to create a table

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)) ;

Query the schema of the table:

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 execute to create the table and the schema of the table, including the custom settings and default settings. 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 does not support the following table attributes:

crc_check_chance, gc_grace_seconds, read_repair_chance, speculative_retry, dclocal_read_repair_chance, and crc_check_chance

The table attributes that are not supported by Lindorm will be available soon. You can configure the following table attributes:

compression: supports LZ4, SNAPPY, and ZSTD.
default_time_to_live: sets the time-to-live (TTL) attribute for a table.

Use lindorm-cqlsh to read and write data

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

cqlsh:test_keyspace> INSERT INTO test_user (first_name, last_name) VALUES ('test', 'LINDORM');
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 are used to insert three data records into the test_user table. In the last data record, you use a key to specify the first_name field and leave the last_name field empty. You can execute the SELECT COUNT statement to check whether the data is inserted. However, we recommend that you do not execute the SELECT COUNT statement to query large amounts of data.

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

  count
-------     
    3

(1 rows)

The return result shows 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 |    LINDORM
         Wu |      null
      Zhang |       San

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

first_name | last_name
------------+-----------
       test |    LINDORM

(1 rows)

Delete a column or a row

You can execute the DELETE statement to delete one or more columns. If you want to delete the last_name column and verify the result, 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

You can also execute the DELETE statement to delete a row.

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>

Clear or delete a table

If you want to clear a table, execute the TRUNCATE statement or DROP TABLE statement. Only superusers have the permissions to execute the TRUNCATE, DROP KEYSAPCE, or DROP TABLE statement. Example:

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

Use a search index in Lindorm

You can execute a CQL statement to create a search index for a table in Lindorm. Before you create a search index, make sure that the attributes of the table meet the requirements. For more information about the table attributes, see the DOC file in the lindorm-cqlsh installation package or the CREATE TABLE section in the CQL official documentation. You can also contact online support.

The following statements are used to create a table that has the CONSISTENCY_TYPE and MUTABILITY attributes, create a search index for the table, insert data into the table, and perform a fuzzy search. For more information, see Full-text indexes in Enterprise features.

cqlsh:test_keyspace>CREATE TABLE test_keyspace.tb ( cn1 text PRIMARY KEY , cn2 text , cn3 text )  WITH extensions = {'CONSISTENCY_TYPE':'strong', 'MUTABILITY':'MUTABLE_LATEST'};
cqlsh:test_keyspace>CREATE SEARCH INDEX tbidx ON test_keyspace.tb WITH COLUMNS (cn2, cn3);
cqlsh:test_keyspace>REBUILD SEARCH INDEX ON test_keyspace.tb ;
cqlsh:test_keyspace>INSERT INTO test_keyspace.tb (cn1, cn2, cn3) VALUES ( 'v11', 'v12', 'v13');
cqlsh:test_keyspace>select * from test_keyspace.tb where cn2 like '%v1';

Use a secondary index in Lindorm

You can execute a CQL statement to create a secondary index for a table in Lindorm. Before you create a secondary index, make sure that the attributes of the table meet the requirements. For more information, see Use native secondary indexes to make queries efficient.

Compared with Cassandra, Lindorm enables more features for executing CQL statements. For more information, see Use native secondary indexes to make queries efficient in Enterprise features.

cqlsh:test_keyspace>CREATE TABLE test_keyspace.tb ( cn1 text PRIMARY KEY , cn2 text , cn3 text )  WITH extensions = {'CONSISTENCY_TYPE':'strong', 'MUTABILITY':'MUTABLE_LATEST'};
cqlsh:test_keyspace>CREATE INDEX tbidx ON test_keyspace.tb (cn2);
cqlsh:test_keyspace>INSERT INTO test_keyspace.tb (cn1, cn2, cn3) VALUES ( 'v11', 'v12', 'v13');
cqlsh:test_keyspace>select * from test_keyspace.tb where cn2 = 'v12';