Lindorm:DDL

Overview

Lindorm CQL can be used to store data in tables. The schema of a table defines the layout of data that is stored in the table. 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. Lindorm CQL uses the default settings to determine the replication strategy and the number of replicas. 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. This allows you to use indexes based on your business requirements.

Syntaxes for defining 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.

• 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. You can execute the following statement to specify the name of a secondary index.

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

• Search indexes

  You can use search indexes in the following scenarios:

  • Queries that are performed based on multiple dimensions. You can specify conditions based on multiple columns to query data in an efficient manner.

  • Sorting results. Lindorm supports the ORDER BY clause. You can use the ORDER BY clause to sort results based on values in specified columns.

  • Fuzzy match.

  Before you use search indexes in Lindorm CQL, you must enable the search index feature and Lindorm Tunnel Service (LTS). For more information, see Overview.

CREATE KEYSPACE

Creates a keyspace.

Syntax

CREATE KEYSPACE [ IF NOT EXISTS ] keyspace_name WITH options

Parameters

Example

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

ALTER KEYSPACE

Modifies the options of a keyspace.

Syntax

ALTER KEYSPACE keyspace_name WITH options

Parameters

The parameters that are included in the values of the options parameter in the ALTER KEYSPACE statement are the same as the parameters that are included in the values of the options parameter in the CREATE KEYSPACE statements. For more information, see Parameters.

Example

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.

Syntax

USE keyspace_name

Parameters

Example

USE testks;

DROP KEYSPACE

Deletes a keyspace.

Syntax

DROP KEYSPACE [ IF EXISTS ] keyspace_name

Parameters

Example

DROP KEYSPACE testks;

CREATE TABLE

Creates a table.

Syntax

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

Description

When you use Lindorm CQL to create a table, you need to specify a name for the table, the columns in the table, the columns of which the primary key consists, and the table options. You can use the IF NOT EXISTS keyword to prevent the system from returning an error when the table name that you specify already exists. This way, if the table already exists, the create operation is not performed.

Each row in a table consists of data in the columns of the table. When you create a table, you define columns in the value of the column_definition parameter. You can execute the ALTER statement to add columns after a table is created.

In a table, a data row is uniquely identified by a value of the primary key. You must specify a unique primary key for a table. Each table has only one primary key. A primary key consists of one or more columns in the table. In the CREATE TABLE statement, the primary key is specified by using the PRIMARY KEY keyword 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 keyword to the column definition. The order of the columns in the primary key definition affects the distribution and storage order of data rows in the table.

In Lindorm CQL, primary keys are important. A primary key consists of a partition key and clustering columns. Lindorm SQL cannot identify the node on which a row of data is stored based on only the partition key or 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

The partition key and clustering columns of the primary key of a table have the same priority. The partition key is used in combination with the clustering columns to identify the node on which a row of data is stored. Lindorm SQL cannot identify the node on which a row of data is stored based on only the partition key. In the preceding example, Row 1 and Row 2 reside on the same node. If you use a Lindorm CQL, Row 1 and Row 2 may reside on different nodes. This is the main difference between Lindorm CQL and traditional CQLs.

Examples

CREATE TABLE tb (name text PRIMARY KEY , age int);   // Sets the primary key column to the name column. 
CREATE TABLE ttltb (name text PARIMARY KEY, age int) WITH default_time_to_live = 1000;  // Sets the Time-to-Live (TTL) of data in the table to 1,000 seconds. 
CREATE TABLE cptb (name text PRIMARY KEY, age int) WITH compression = {'sstable_compression': 'LZ4Compressor'};  // Sets the compression algorithm to LZ4. The default compression algorithm is SNAPPY. 
CREATE TABLE hct ( name text PRIMARY KEY , age int  ) WITH extensions = {'COLD_BOUNDARY':'10'};  // Sets the hot data and cold data boundary to 10 seconds. 

Features that are unsupported by Lindorm CQL

Lindorm CQL cannot identify the physical node on which a row of data is stored based on only the partition key.

Supported table options

Compression

Extensions

Table options that you cannot configure

ALTER TABLE

Modifies a table.

Syntax

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

Description

Examples

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

DROP TABLE

Deletes a table.

Syntax

DROP TABLE [ IF EXISTS ] table_name

Parameters

Example

DROP TABLE persioninfo;

TRUNCATE

Clears the data of a table.

Syntax

TRUNCATE [ TABLE ] table_name

Parameters

Example

TRUNCATE TABLE persioninfo;

CREATE INDEX

Creates a secondary index.

Syntax

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

Description

You can execute the CREATE INDEX statement to create a secondary index based on a column in a table. You can specify the name of the index on the left side of the ON keyword. The data that was written to the column before you create the index is asynchronously indexed. After the index is created, the system automatically indexes new data that is written to the column.

If the index name that you specify already exists, the system returns an error. You can use the IF NOT EXISTS option to prevent the system from returning an error message when the specified index name already exists. This way, if the index name already exists, the create operation is not performed.

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

Examples

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.

Syntax

DROP INDEX [ IF EXISTS ] index_name

Example

DROP INDEX myindex;

CREAT SEARCH INDEX

Creates a full-text index.

Syntax

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

Example

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

DROP SEARCH INDEX

Deletes a full-text index.

Syntax

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

Example

DROP SEARCH INDEX ON testks.persioninfo;

REBUILD SEARCH INDEX

Sets the status of a full-text index to active.

Syntax

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

Examples

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

ALTER SEARCH INDEX

Modifies a full-text index. This statement can be used to change the status of a full-text index, add an index key column, or delete an index key column.

Syntax

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

Examples

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