DDL - Lindorm - Alibaba Cloud Documentation Center

This section explains DDL syntax and provides examples.

Overview

Lindorm CQL stores data in tables. A table schema defines the layout of data stored in the table. Tables are grouped into keyspaces. Keyspaces support multiple options that apply to all tables in the keyspace. The most common option is the replication policy. Lindorm CQL uses default values for the replication policy and number of replicas. You cannot modify these settings. Lindorm CQL lets you use CQL to manage high-performance secondary indexes and full-text search indexes to meet your indexing needs.

Syntax for defining keyspaces and tables

Lindorm CQL keyspace syntax:

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

Lindorm CQL table syntax:

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

Note

A keyspace or table name can contain only letters, digits, and special characters. It cannot be empty.
A keyspace or table name can be up to 48 characters long. This limit prevents file names—which may include keyspace or table names—from exceeding file system limits.
Keyspace and table names are case-insensitive by default. For example, myTable and mytable refer to the same object. However, if you enclose a name in double quotation marks ("), it becomes case-sensitive. For example, "myTable" and "mytable" refer to different objects.
Tables belong to keyspaces. You can qualify a table name with its keyspace name. For example, both the ks and gc keyspaces contain a table named table. You can refer to them as ks.table and gc.table.

Indexes

Lindorm CQL supports both secondary indexes and search indexes built on search engines.

Secondary indexes
You can create secondary indexes on a table and then use those indexes to query the table. Define a secondary index name using this syntax:
```
index_name ::=  re('[a-zA-Z_0-9]+')
```
Search indexes
Search indexes support the following features:
- Multi-dimensional queries. Query results return quickly based on any combination of columns.
- Sorting. Use the ORDER BY clause to sort results by any specified column.
- Fuzzy match.
To use search indexes with Lindorm CQL, first enable full-text search 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

Note

In Lindorm, keyspaces are similar to namespaces. Due to underlying design constraints, Lindorm CQL does not support custom replication policies or replica counts. Default values apply.

Parameters

Parameter name

Example

Description

keyspace_name

testks

The keyspace name.

options

replication

Valid values: replication and durable_writes.

replication: A map. Specifies the default number of data replicas. By default, Lindorm uses two replicas.
durable_writes: A Boolean. Specifies whether writes are durable. Default value: True.

Example

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

Note

The Lindorm CQL keyspace compatibility layer does not support configuring replication or durable_writes. Default values apply: two replicas and durable_writes set to True. These settings will be configurable in future releases.

ALTER KEYSPACE

Modifies keyspace options.

Syntax

ALTER KEYSPACE keyspace_name WITH options

Note

The options in the ALTER KEYSPACE statement and the options in the CREATE KEYSPACE statement are the same. However, the keyspace-level options are all default, so you do not need to modify them.

Parameters

The options in the ALTER KEYSPACE statement and the options in the CREATE KEYSPACE statement are the same. For more information, see Parameters.

Example

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

USE

Selects a keyspace. In Lindorm CQL, many objects—including tables, user-defined types (UDTs), and functions—are bound to a keyspace.

Syntax

USE keyspace_name

Parameters

Parameter Name	Example	Description
keyspace_name	testks	The keyspace name.

Example

USE testks;

DROP KEYSPACE

Deletes a keyspace.

Syntax

DROP KEYSPACE [ IF EXISTS ] keyspace_name

Note

Dropping a keyspace deletes it immediately and permanently. All tables, UDTs, functions, and data in the keyspace are also deleted.
If the keyspace does not exist, the system returns an error. Use IF EXISTS to avoid an error. If the keyspace does not exist, the operation has no effect.
Only the root user can drop keyspaces.

Parameters

Parameter Name	Example	Description
keyspace_name	testks	The keyspace name.

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

A Lindorm CQL table has a name and consists of rows. Creating a table defines which columns make up each row, which columns form the primary key, and optional table settings. Without IF NOT EXISTS, creating a table that already exists returns an error.

Each row in a Lindorm CQL table has a set of predefined columns. You define these columns when you create the table or later using ALTER statements.

A column_definition specifies a column name and type. This determines which values the column accepts. A column definition can also include these modifiers:

STATIC: Declares the column as static. Not supported.
PRIMARY KEY: Indicates the column is the sole component of the table's primary key.

In a table, a data row is uniquely identified by its PRIMARY KEY, so every table must define exactly one PRIMARY KEY. PRIMARY KEY definitions consist of one or more columns defined in the table. Syntactically, a primary key is defined using the PRIMARY KEY keyword followed by a list of column names enclosed in parentheses. However, if the primary key contains only one column, you can specify the PRIMARY KEY keyword in the column definition instead. The order of columns in the primary key definition is important because it affects how data is distributed and stored.

A primary key has two parts:

partition key: The first part of the primary key. It can be a single column or multiple columns enclosed in parentheses. A table always has at least one partition key.
clustering columns: These are the columns that follow the first part of the primary key definition. The order of these columns defines the clustering order. In Lindorm CQL, clustering columns are optional.

Examples of PRIMARY KEY definitions:

PRIMARY KEY(a): Column a is the partition key. No clustering columns.
PRIMARY KEY(a, b, c): Column a is the partition key. Columns b and c are clustering columns.
PRIMARY KEY((a, b), c): Columns a and b form a composite partition key. Column c is a clustering column.

In Lindorm CQL, the PRIMARY KEY plays a crucial role. A partition key and clustering columns make up the PRIMARY KEY. The partition key or clustering columns cannot be used independently.

Lindorm CQL defines partitions. A partition is a group of rows that share the same partition key value. If the partition key has multiple columns, rows belong to the same partition only if all partition key column values match. Consider this table definition and data:

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, the partition key and clustering columns together form the PRIMARY KEY, and they have equal status. In Lindorm CQL, the combination of the partition key and cluster column determines the node for a row of data, unlike in traditional CQL where the node is determined solely by the partition key. For example, in the preceding example, row1 and row2 are guaranteed to be on the same node in traditional CQL, but this is not guaranteed in Lindorm CQL. This is a major difference between Lindorm CQL and traditional CQL.

Examples

CREATE TABLE tb (name text PRIMARY KEY , age int);   // Creates a table with name as the primary key.
CREATE TABLE ttltb (name text PRIMARY KEY, age int) WITH default_time_to_live = 1000;  // Sets the table TTL to 1000 seconds.
CREATE TABLE cptb (name text PRIMARY KEY, age int) WITH compression = {'sstable_compression': 'LZ4Compressor'};   // Uses LZ4 compression. Default is SNAPPY.
CREATE TABLE hct ( name text PRIMARY KEY , age int  ) WITH extensions = {'COLD_BOUNDARY':'10'};  // Sets the hot-cold boundary to 10 seconds.

Unsupported table features

Lindorm CQL cannot determine the physical node for a row using only the partition key.

Supported table options

Lindorm CQL's table_options and Cassandra's table_options have some differences. Lindorm CQL currently supports the following option settings.

Option	Type	Description
default_time_to_live	int	The default time-to-live (TTL) for data in the table, in seconds. Default value: 0.
compression	map	The compression algorithm for SSTables. Supported algorithms: LZ4, ZSTD, and SNAPPY. For details, see Compression.
extensions	map	Extension settings. Supports cold storage, hot-cold separation, and consistency level. For details, see Extensions.

Compression

Lindorm CQL supports these configurable compression algorithms. Each uses default coefficients.

LZ4 (LZ4Compressor). Create an LZ4-compressed table:

CREATE TABLE persioninfo (
   id int,
   name text,
   address text,
   PRIMARY KEY (id, name)
) with compression = {'class': 'LZ4Compressor'};

ZSTD (ZstdCompressor). Create a ZSTD-compressed table:

CREATE TABLE persioninfo (
   id int,
   name text,
   address text,
   PRIMARY KEY (id, name)
) with compression = {'class': 'ZstdCompressor'};

SNAPPY (SnappyCompressor). Create a SNAPPY-compressed table:

CREATE TABLE persioninfo (
   id int,
   name text,
   address text,
   PRIMARY KEY (id, name)
) with compression = {'class': 'SnappyCompressor'};

Note

You can modify the compression setting using ALTER TABLE.

Extensions

Use the extensions option to configure Lindorm-specific properties:

Cold storage. Use the STORAGE_POLICY keyword. Set to COLD for cold storage or DEFAULT for hot storage.

CREATE TABLE persioninfo (name text PRIMARY KEY, age int) WITH extensions = {'STORAGE_POLICY' : 'COLD'};  // Creates a cold-storage table.
ALTER TABLE persioninfo WITH extensions = {'STORAGE_POLICY' : 'DEFAULT'};  // Changes to hot storage.

Hot-cold separation. Use the COLD_BOUNDARY keyword. Do not set STORAGE_POLICY to COLD when using hot-cold separation. If you did, remove the STORAGE_POLICY setting. For details, see Capacity storage.

CREATE TABLE persioninfo (name text PRIMARY KEY, age int) with extensions = {'COLD_BOUNDARY':'86400'};  // Sets the hot-cold boundary to 1 day (86400 seconds). Data older than this is written to cold storage.
ALTER TABLE persioninfo with extensions = {'COLD_BOUNDARY':''};    // Disables hot-cold separation.
ALTER TABLE persioninfo with extensions = {'COLD_BOUNDARY':'1000'};   // Sets the boundary to 1000 seconds.

CONSISTENCY_TYPE: Sets the consistency level for tables deployed across multiple zones. Valid values: eventual, timestamp, basic, and strong.

CREATE TABLE persioninfo (name text PRIMARY KEY, age int) with extensions = {'CONSISTENCY_TYPE':'strong'};  // Sets consistency level to strong.
ALTER TABLE persioninfo with extensions = {'CONSISTENCY_TYPE':'eventual'};  // Changes consistency level to eventual.

Mutability: Required before using secondary indexes or search indexes. Valid values: IMMUTABLE, IMMUTABLE_ROWS (requires CONSISTENCY_TYPE=strong), MUTABLE_LATEST (requires CONSISTENCY_TYPE=strong), and MUTABLE_ALL (default, requires CONSISTENCY_TYPE=strong).
```
CREATE TABLE persioninfo (name text PRIMARY KEY, age int) with extensions = {'MUTABILITY':'IMMUTABLE'};  // Sets mutability to IMMUTABLE.
ALTER TABLE persioninfo with extensions = {'MUTABILITY':'MUTABLE_LATEST'};   // Changes mutability to MUTABLE_LATEST.
```

Unsupported table options

Option	Type	Description
comment	string	A description of the table. Default value: f.
speculative_retry	simple simple	Default value: 99PERCENTILE.
cdc	boolean	Enables change data capture (CDC) logs for the table. Default value: false.
gc_grace_seconds	int	Time to wait before garbage collecting tombstones (delete markers). Default value: 86400.
bloom_filter_fp_chance	float	Target false-positive probability for stable Bloom filters. Bloom filter size determines this probability. Lowering this value affects memory and disk usage. Default value: 0.00075.
compaction	map	Default value: STCS.
caching	map	N/A
memtable_flush_period_in_ms	map	Default value: 0.
read_repair	int	Default value: BLOCKING.

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

Description

Use ALTER TABLE to:

Add new columns using ADD. Because you cannot change a table's primary key, new columns never become part of it. Note that compact tables have limits on adding columns.
Deleting columns is not currently supported in Lindorm CQL.
Modify table options using WITH. You can change the same options as during table creation—except CLUSTERING ORDER. Supported options: default_time_to_live, compression, and extensions.

Example

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

Note

Dropping a table deletes it and all its data immediately and permanently.
If the table does not exist, the system returns an error. Use IF EXISTS to avoid an error. If the table does not exist, the operation has no effect.
Only the root user can drop tables.

Parameters

Parameter name	Example	Description
table_name	persioninfo	The table name.

Example

DROP TABLE persioninfo;

TRUNCATE

Clears all data from a table.

Syntax

TRUNCATE [ TABLE ] table_name

Note

TRUNCATE permanently deletes all table data but leaves the table structure intact.
Only the root user can truncate tables.

Parameters

Parameter Name	Example value	Description
table_name	persioninfo	The table name.

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

Use CREATE INDEX to automatically create a secondary index on a column in a table. Specify the index name before the ON keyword. If the column already contains data, indexing happens asynchronously. After the index is created, the system automatically indexes new data written to the column.

If you try to create an index that already exists, the system returns an error. Using IF NOT EXISTS avoids the error. If the index exists, the operation has no effect.

CREATE INDEX supports indexing only one column. To index multiple columns, use CREATE CUSTOM INDEX with USING 'com.alibaba.lindorm.cserver.schema.index.LindormSecondaryIndex'.

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

Note

Use DROP INDEX to delete an existing secondary index. The index_name parameter identifies the index. You can optionally specify the keyspace.
If the index does not exist, the system returns an error. Use IF EXISTS to avoid an error. If the index does not exist, the operation has no effect.

Example

DROP INDEX myindex;

CREATE SEARCH INDEX

Creates a full-text search index.

Syntax

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

Note

CREATE SEARCH INDEX creates full-text search indexes on specified columns of a source table.
WITH COLUMNS(column): Specifies one or more columns for the search index. Separate columns with commas (,).
WITH COLUMNS(*): Uses the * wildcard to index all columns.
When creating a search index, extend the source table's properties using extensions. In multi-zone deployments, set CONSISTENCY_TYPE and MUTABILITY. In single-zone deployments, these settings are optional.

Example

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

DROP SEARCH INDEX

Deletes a full-text search index.

Syntax

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

Example

DROP SEARCH INDEX ON testks.persioninfo;

REBUILD SEARCH INDEX

Set the index status to valid.

Syntax

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

Note

After you create a search index, its status is INACTIVE. Run REBUILD to activate it. REBUILD also indexes historical data. This process takes time. Use the ASYNC parameter to run it asynchronously.

Examples

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

ALTER SEARCH INDEX

Modifies a search index. You can change its status, add index columns, or remove index columns.

Syntax

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

Note

After using ALTER SEARCH INDEX to modify a search index, run REBUILD to reactivate it.
Columns added with ADD SEARCH INDEX or removed with DROP SEARCH INDEX must exist in the source table.

Examples

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