DDL

Overview

Lindorm Cassandra Query Language (CQL) stores data in tables. The schema of a table defines the layout of data that is stored in the table. The tables are grouped into keyspaces. Multiple options are available for keyspaces. If you specify an option for a keyspace, the option is applied to all tables in the keyspace. The replication strategy is the most frequently used option. The replication strategy and the number of replicas use the default settings. These settings in Lindorm CQL cannot be modified. Lindorm CQL allows you to use CQL to manage high-performance secondary indexes and full-text indexes of ApsaraDB for Lindorm (Lindorm). This helps you use indexes with ease.

Syntax of keyspaces and tables

keyspace_name ::=  name
name          ::=  unquoted_name | quoted_name
unquoted_name ::=  re('[a-zA-Z_0-9]{1, 48}')
quoted_name   ::=  '"' unquoted_name '"'

table_name    ::=  [ keyspace_name '.' ] name
name          ::=  unquoted_name | quoted_name
unquoted_name ::=  re('[a-zA-Z_0-9]{1, 48}')
quoted_name   ::=  '"' unquoted_name '"'

Indexes

Lindorm CQL supports secondary indexes and full-text indexes that are created by using the engine-based Search Index.

• Secondary indexes
  You can use Lindorm CQL to create secondary indexes on a table. Then, you can use the secondary indexes to query data from the table. The following statement is used to specify the name of a secondary index.

  index_name ::=  re('[a-zA-Z_0-9]+')

• Search Index
  Search Index can be used to perform the following operations:

  • Multidimensional queries: Lindorm aggregates data across columns and returns the query result in an efficient way.
  • Sequencing: Lindorm supports the ORDER BY keyword. You can use this keyword to sort results based on the specified columns.
  • Fuzzy match.

  Before you use Search Index in Lindorm CQL, you must enable the Search Index feature and Lindorm Tunnel Service (LTS). For more information, see Full-text indexes.

CREATE KEYSPACE

Creates a keyspace.

CREATE KEYSPACE [ IF NOT EXISTS ] keyspace_name WITH options

CREATE KEYSPACE testks WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1}

ALTER KEYSPACE

Modifies the options of a keyspace.

ALTER KEYSPACE keyspace_name WITH options

The options parameter used in ALTER KEYSPACE statements is the same as the options parameter used in CREATE KEYSPACE statements. For more information, see the Parameters section for CREATE KEYSPACE.

ALTER KEYSPACE testks WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1}

USE

Uses a keyspace. In Lindorm CQL, objects such as tables, user-defined types (UDTs), and user-defined functions (UDFs) are bound to a keyspace.

USE keyspace_name

USE testks;

DROP KEYSPACE

Deletes a keyspace.

DROP KEYSPACE [ IF EXISTS ] keyspace_name

DROP KEYSPACE testks;

CREATE TABLE

Creates a table.

CREATE TABLE [ IF NOT EXISTS ] table_name
                  '('
                       column_definition
                       ( ',' column_definition )*
                       [ ',' PRIMARY KEY '(' primary_key ')' ]
                 ')' [ WITH table_options ]
column_definition      ::=  column_name cql_type [ STATIC ] [ PRIMARY KEY]
primary_key            ::=  partition_key [ ',' clustering_columns ]
partition_key          ::=  column_name
                            | '(' column_name ( ',' column_name )* ')'
clustering_columns     ::=  column_name ( ',' column_name )*
table_options          ::=  CLUSTERING ORDER BY '(' clustering_order ')' [ AND options ]
                            | options
clustering_order       ::=  column_name (ASC | DESC) ( ',' column_name (ASC | DESC) )*

Each Lindorm CQL table has a name and consists of a set of rows. When you create a table, you define the columns of which each row consists, the columns of which the primary key consists, and the table options. You can use the IF NOT EXISTS option to prevent the system from returning an error when the table that you want to create already exists. This way, if the table already exists, the create operation is not performed.

Each row in a Lindorm CQL table has a set of predefined columns. The predefined columns are specified when you create the table. You can execute ALTER statements to add predefined columns.

In a table, a row is uniquely identified by the primary key. You must specify a unique primary key for each table. Each table has only one primary key. A primary key consists of one or more defined columns in the table. In syntax, the primary key is specified by using the PRIMARY KEY phrase that is followed by a string of column names within parentheses. If the primary key contains only one column, you can add the PRIMARY KEY phrase to the column in the table definition. The order of the columns in the primary key definition is important. This order affects the distribution and storage order of the data.

In Lindorm CQL, primary keys are important. A primary key consists of a partition key and clustering columns. In actual scenarios, a partition key must be used in combination with clustering columns.

CREATE TABLE persioninfo (
    a int,
    b int,
    c int,
    d int,
    PRIMARY KEY ((a, b), c, d)
);

SELECT * FROM persioninfo;
   a | b | c | d
  ---+---+---+---
   0 | 0 | 0 | 0    // row 1
   0 | 0 | 1 | 1    // row 2
   0 | 1 | 2 | 2    // row 3
   0 | 1 | 3 | 3    // row 4
   1 | 1 | 4 | 4    // row 5

In Lindorm CQL, a primary key consists of a partition key and clustering columns. A partition key is as important as clustering columns. A partition key is used in combination with clustering columns to identify the node to which a row of data belongs. A partition key alone cannot identify the node to which a row of data belongs. In the preceding example, row 1 and row 2 reside in the same node in Lindorm CQL. However, row 1 and row 2 may reside in different nodes in the traditional CQL. This is the main difference between Lindorm CQL and the traditional CQL.

CREATE TABLE tb (name text PRIMARY KEY , age int);   // By default, the statement is used to create a table whose primary key is the name column. 
CREATE TABLE ttltb (name text PARIMARY KEY, age int) WITH default_time_to_live = 1000;  // Sets the Time-to-Live (TTL) to 1000s. 
CREATE TABLE cptb (name text PRIMARY KEY, age int) WITH compression = {'sstable_compression': 'LZ4Compressor'};   // Sets the compression algorithm to LZ4Compressor. By default, SNAPPY is used. 
CREATE TABLE hct ( name text PRIMARY KEY , age int  ) WITH extensions = {'COLD_BOUNDARY':'10'};  // Sets the timeline of cold and hot data separation for Lindorm to 10s. 

PARTITION KEY: determines the related node to which distributed data belongs. Lindorm CQL cannot identify the physical node to which a row of data belongs based only on the partition key.

Compression

Extensions

ALTER TABLE

Modifies a table.

ALTER TABLE table_name alter_table_instruction
alter_table_instruction ::=  ADD column_name cql_type ( ',' column_name cql_type )*
                             | DROP column_name ( column_name )*
                             | WITH options

CREATE TABLE persioninfo (name text PRIMARY KEY, age int);
ALTER TABLE persioninfo ADD address text;

DROP TABLE

Deletes a table.

DROP TABLE [ IF EXISTS ] table_name

DROP TABLE persioninfo;

TRUNCATE

Clears the data of a table.

TRUNCATE [ TABLE ] table_name

TRUNCATE TABLE persioninfo;

CREATE INDEX

Creates a secondary index.

CREATE [ CUSTOM ] INDEX [ IF NOT EXISTS ] [ index_name ]
                                ON table_name '(' index_identifier ')'
                                [ USING string [ WITH OPTIONS = map_literal ] ]
index_identifier       ::=  column_name
                           | '(' column_name ')'

You can execute the CREATE INDEX statement to create a secondary index for a column in a table. You can specify an index name before the ON keyword. If data already exists for a column, the column is asynchronously indexed. After the index is created, new data for the column is automatically indexed.

If the index that you want to create already exists, an error message is returned. To prevent the system from returning an error message, you can use the IF NOT EXISTS option. In this case, your query has no result.

The CREATE INDEX statement can only be used to create indexes for a single column. If you want to create indexes for multiple columns in a table, specify USING 'com.alibaba.lindorm.cserver.schema.index.LindormSecondaryIndex' in the CREATE CUSTOM INDEX statement.

CREATE INDEX myindex ON persioninfo (c2);
CREATE INDEX ON persioninfo (c2);
CREATE CUSTOM INDEX myindex ON persioninfo (c1,c2) USING 'com.alibaba.lindorm.cserver.schema.index.LindormSecondaryIndex';

DROP INDEX

Deletes a secondary index.

DROP INDEX [ IF EXISTS ] index_name

DROP INDEX myindex;

CREAT SEARCH INDEX

Creates a full-text index.

CREATE SEARCH INDEX [ IF NOT EXISTS ] index_name ON [keyspace_name.]table_name
| [ WITH [ COLUMNS (column1,...,columnn) ]
| [ WITH [ COLUMNS (*) ]

CREATE SEARCH INDEX schidx ON persioninfo WITH COLUMNS (c2, c3); 

DROP SEARCH INDEX

Deletes a full-text index.

DROP SEARCH INDEX [IF EXISTS] ON [keyspace_name.]table_name;

DROP SEARCH INDEX ON testks.persioninfo;

REBUILD SEARCH INDEX

Sets the indexing status to active.

REBUILD SEARCH INDEX [ASYNC] [IF EXISTS] ON [keyspace_name.]table_name;

REBUILD SEARCH INDEX ON persioninfo;
REBUILD SEARCH INDEX ASYNC ON persioninfo;

ALTER SEARCH INDEX

Modifies the status of a full-text index. This statement can also be used to manually add or delete an indexed column.

ALTER SEARCH INDEX SCHEMA [IF EXISTS] ON [keyspace_name.]table_name
  ( ADD FIELD column_name
  | DROP FIELD column_name) ;

ALTER SEARCH INDEX SCHEMA ON persioninfo ADD  (c3);
ALTER SEARCH INDEX SCHEMA ON persioninfo DROP  (c2);