DDL Syntax and Schema Design in Lindorm Wide Tables - Lindorm - Alibaba Cloud - Lindorm

Lindorm CQL provides DDL statements for managing keyspaces, tables, secondary indexes, and search indexes. This topic describes the syntax, parameters, and examples of each statement, together with the naming rules, table options, and behavior differences that apply to Lindorm CQL.

Statements in this topic

Lindorm CQL groups DDL statements by the object that they act on:

Keyspace statements — CREATE KEYSPACE, ALTER KEYSPACE, USE, and DROP KEYSPACE.
Table statements — CREATE TABLE, ALTER TABLE, DROP TABLE, and TRUNCATE.
Index statements — CREATE INDEX, DROP INDEX, CREATE SEARCH INDEX, DROP SEARCH INDEX, REBUILD SEARCH INDEX, and ALTER SEARCH INDEX.

Data model and index types

Lindorm CQL stores data in tables. A schema defines the layout of the data in a table, and tables are grouped in keyspaces. A keyspace defines options that apply to all tables it contains, and the most important option is the replication policy. In Lindorm, a keyspace functions as a namespace.

Lindorm CQL also exposes two index types through CQL: Lindorm high-performance secondary indexes and Lindorm search indexes, which are built on the full-text search engine. For the differences between the two index types, see Index types.

Prerequisites

Permissions — Only the root user has permission to run DROP KEYSPACE, DROP TABLE, and TRUNCATE.
Search index services — Enable the full-text search and LTS services on Lindorm before you use search indexes through Lindorm CQL. See Overview.
Table attributes for indexes — Extend the properties of the table before you create an index on it. For details, see CREATE INDEX and CREATE SEARCH INDEX.

Syntax notation

The syntax blocks in this topic use the following notation:

::= — Defines a production. The element on the left is defined by the expression on the right.
Uppercase words — Keywords that you enter as shown, such as CREATE TABLE.
Lowercase words — Placeholders that you replace with your own values, such as table_name.
[ ] — Encloses an optional element.
( ) — Groups elements.
| — Separates mutually exclusive alternatives.
* — Indicates that the preceding element can be repeated zero or more times.
'x' — A literal character, such as a parenthesis or a comma, that you enter as shown.
re('...') — A regular expression that the value must match.

Naming rules

The following rules apply to keyspace, table, and index names in Lindorm CQL:

Names consist of letters, digits, and underscores (_). A name cannot be empty.
Keyspace and table names cannot exceed 48 characters. This limit prevents file names that may include keyspace or table names from exceeding file system limits.
By default, keyspace and table names are case-insensitive. For example, myTable and mytable refer to the same object. Enclose a name in double quotation marks (") to make it case-sensitive. For example, "myTable" and "mytable" refer to different objects.
A table is part of a keyspace, so you can qualify a table name with its keyspace name. For example, if the keyspaces ks and gc each contain a table named orders, the qualified names are ks.orders and gc.orders.
The following productions define keyspace, table, and index names:

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

Differences from standard Cassandra CQL

If you reuse Cassandra clients or DDL scripts, review the following differences in Lindorm CQL before you design your data model:

Keyspace options — Because of underlying design constraints, Lindorm CQL does not support custom replication policies or replica counts. The replication and durable_writes options are accepted for compatibility with standard CQL clients, but they do not take effect. Keyspaces always use the default two replicas and the default durable_writes value of true.
Data distribution — In standard CQL, the partition_key alone determines the physical node that a row belongs to. In Lindorm CQL, a single partition_key cannot determine that node. For details, see Primary keys and partitions.
Column definitions — Lindorm CQL does not support static columns (STATIC), and it does not support deleting columns.
Table options — The table_options in Lindorm CQL differ from those in Cassandra, and Lindorm CQL adds Lindorm-specific extensions. For details, see Table options.

Primary keys and partitions

In a Lindorm CQL table, a row is uniquely identified by its PRIMARY KEY. All tables must define exactly one PRIMARY KEY, which consists of one or more columns defined in the table. In syntax, the primary key is the PRIMARY KEY keyword followed by a parenthesized list of column names. If the primary key has only one column, the column definition can be replaced with the PRIMARY KEY keyword. The order of columns in the primary key matters and affects data distribution and storage order.

A Lindorm CQL primary key consists of two parts:

partition_key: The first part of the primary key. It can be a single column, or multiple columns with additional parentheses to form a composite partition key. A table always has at least one partition key.
clustering_columns: The columns after the first part of the primary key. Their order defines the clustering order. Lindorm CQL allows clustering_columns to be omitted.
The following examples show how a PRIMARY KEY definition maps to these two parts:

PRIMARY KEY(a): a is the partition key. No clustering columns.
PRIMARY KEY(a, b, c): a is the partition key. b and c are clustering columns.
PRIMARY KEY((a, b), c): a and b form the partition key (composite partition key). c is the clustering column.
In Lindorm CQL, the partition_key and clustering_columns together form the PRIMARY KEY with equal status. A standalone partition_key or clustering_columns does not have independent meaning.

Lindorm CQL also defines the concept of a partition. A partition is a set of rows that share the same partition key value. If the partition key consists of multiple columns, rows belong to the same partition only if all partition key column values are identical. The following statement creates a table with the composite partition key (a, b):

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

Query the table:

SELECT * FROM personinfo;

The following output is returned:

   a | b | c | d
  ---+---+---+---
   0 | 0 | 0 | 0    // row 1: partition key (a, b) = (0, 0)
   0 | 0 | 1 | 1    // row 2: partition key (a, b) = (0, 0)
   0 | 1 | 2 | 2    // row 3: partition key (a, b) = (0, 1)
   0 | 1 | 3 | 3    // row 4: partition key (a, b) = (0, 1)
   1 | 1 | 4 | 4    // row 5: partition key (a, b) = (1, 1)

In Lindorm CQL, the partition_key and clustering_columns together determine which node a row belongs to, rather than the partition_key alone as in standard CQL. Row 1 and row 2 in the preceding output are guaranteed to be on the same node in standard CQL, but this guarantee does not apply in Lindorm CQL.

Index types

Lindorm CQL supports two index types, each with its own query capabilities and prerequisites:

Secondary Index — Lindorm CQL supports creating secondary indexes on tables, which allow you to query tables using these indexes.
For information about high-performance secondary indexes, see .
Search index — A search index is served by the search engine and supports the following features:
- Multi-dimensional queries. Given a random combination of multiple columns, quickly return query results.
- Sorting. Provides the Order By capability to return results sorted by any specified column.
- Fuzzy matching.
Choose the index type based on the query pattern that you need to support:

Query pattern	Index type	Statement
Query a table by a single column	Secondary index	CREATE INDEX
Query a table by multiple columns	Secondary index created with a Lindorm secondary index class	CREATE INDEX
Query by any combination of multiple columns, sort results by any column, or run fuzzy matching	Search index	CREATE SEARCH INDEX

A secondary index is sufficient for queries on the columns of a single table. Use a search index when you need arbitrary column combinations, sorting, or fuzzy matching, because a search index depends on the full-text search and LTS services. See Prerequisites.

Search index workflow

To make a search index serve queries, complete the following operations in order:

Extend the properties of the source data table with the extensions property. You must set the MUTABILITY attribute before you use secondary indexes or search indexes. In a multi-zone Lindorm deployment, you must also specify the consistency level. In a single-zone deployment, these settings are not required. For details, see Extensions.
Run CREATE SEARCH INDEX to create the index on the columns that you want to query. After creation, the index status is INACTIVE.
Run REBUILD SEARCH INDEX to activate the index. This operation also builds the search index for existing data and can be time-consuming.
Each time you add or remove index columns with ALTER SEARCH INDEX SCHEMA, run REBUILD SEARCH INDEX again.

Keyspace statements

CREATE KEYSPACE

Creates a keyspace.

Syntax

CREATE KEYSPACE [ IF NOT EXISTS ] keyspace_name WITH options

Parameters

Parameter	Example	Description
`keyspace_name`	testks	The name of the keyspace.
`options`	replication	Valid values: `replication` and `durable_writes`. `replication`: map type. Accepted for compatibility with standard CQL clients. The underlying storage uses two replicas by default. `durable_writes`: boolean type. Specifies whether data is written durably. Default value: true.

Important

Lindorm CQL accepts the replication and durable_writes options but does not apply them. For details, see Differences from standard Cassandra CQL.

Example

CREATE KEYSPACE testks WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};   // The replication clause is accepted for compatibility and does not change the actual number of replicas.

ALTER KEYSPACE

Modifies keyspace options.

Syntax

ALTER KEYSPACE keyspace_name WITH options

Parameters

The options in an ALTER KEYSPACE statement are the same as the options in a CREATE KEYSPACE statement. See CREATE KEYSPACE.

Note

Keyspace-level options always use their default values in Lindorm CQL, so no modification is required.

Example

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

USE

Sets the active keyspace. Many objects in Lindorm CQL are bound to a keyspace, such as tables, user-defined types, and functions.

Syntax

USE keyspace_name

Replace keyspace_name with the name of the keyspace, for example, testks.

Example

USE testks;

DROP KEYSPACE

Deletes a keyspace.

Warning

Dropping a keyspace takes effect immediately and cannot be recovered. All tables, user-defined types, and functions in the keyspace, together with all data in those tables, are deleted. Only the root user has permission to drop keyspaces.

Syntax

DROP KEYSPACE [ IF EXISTS ] keyspace_name

Replace keyspace_name with the name of the keyspace, for example, testks.

Note

If the keyspace does not exist, the statement returns an error. If you use IF EXISTS, no error is returned, but the operation has no effect.

Example

DROP KEYSPACE testks;

Table statements

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 [ 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 a set of rows. Creating a table defines which columns compose a row of data, which columns form the primary key, and the optional table options. Unless you use the IF NOT EXISTS clause, creating a table that already exists returns an error.

Each row in a Lindorm CQL table has a set of predefined columns that are defined at table creation time, or added later with an ALTER TABLE statement.

The column_definition consists of the column name and type, which restrict the values that the column accepts. Add the PRIMARY KEY modifier to indicate that the column is part of the table's primary key. For how the primary key controls data distribution and storage order, see Primary keys and partitions.

For the options that you can set in the WITH clause, see Table options.

Example

CREATE TABLE personinfo (name text PRIMARY KEY, age int);   // Create a table with name as the primary key by default.
CREATE TABLE personinfo_ttl (name text PRIMARY KEY, age int) WITH default_time_to_live = 1000;  // Set the table TTL to 1000 seconds.
CREATE TABLE personinfo_compressed (name text PRIMARY KEY, age int) WITH compression = {'class': 'LZ4Compressor'};   // Set the compression algorithm to LZ4.
CREATE TABLE personinfo_cold (name text PRIMARY KEY, age int) WITH extensions = {'COLD_BOUNDARY':'10'};  // Set the Lindorm hot-cold separation timeline to 10 seconds.

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

The ALTER TABLE statement supports the following operations:

Add new columns to a table with the ADD instruction. Because the primary key of a table cannot be changed, newly added columns are never part of the primary key. Compact tables have certain restrictions on column addition.
Lindorm CQL does not support deleting columns.
Change table options with the WITH instruction. The modifiable table options are the same as those at table creation, but CLUSTERING ORDER cannot be changed. Lindorm CQL supports modifying the default_time_to_live, compression, and extensions options. For the definitions of these options, see Table options.

Example

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

DROP TABLE

Deletes a table.

Warning

Dropping a table takes effect immediately and cannot be recovered. All data in the table is deleted. Only the root user has permission to drop tables.

Syntax

DROP TABLE [ IF EXISTS ] table_name

Replace table_name with the name of the table, for example, personinfo.

Note

If the table does not exist, the statement returns an error. If you use IF EXISTS, no error is returned, but the operation has no effect.

Example

DROP TABLE personinfo;

TRUNCATE

Removes all data from a table.

Warning

Using TRUNCATE permanently deletes all data in a table but does not delete the table schema. Only the root user has permission to truncate tables.

Syntax

TRUNCATE [ TABLE ] table_name

Replace table_name with the name of the table, for example, personinfo.

Example

TRUNCATE TABLE personinfo;

Table options

Set table options in the WITH clause of a CREATE TABLE statement, and change the supported options with an ALTER TABLE statement.

Supported table options

Option	Type	Default value	Description
`default_time_to_live`	int	0	The default time-to-live (TTL) for the table, in seconds.
`compression`	map	SNAPPY	The compression algorithm for sstable files. Supported algorithms: LZ4, ZSTD, and SNAPPY. See Compression.
`extensions`	map	-	Lindorm-specific extension settings, including cold storage, hot-cold separation, and table consistency level. See Extensions.

Compression

Lindorm CQL supports the following configurable compression algorithms. The compression coefficients for each algorithm use default parameters.

LZ4 (LZ4Compressor)
ZSTD (ZstdCompressor)
SNAPPY (SnappyCompressor)
Specify the algorithm class in the compression map when you create the table:

CREATE TABLE personinfo (
   id int,
   name text,
   address text,
   PRIMARY KEY (id, name)
) WITH compression = {'class': 'LZ4Compressor'};   // Replace LZ4Compressor with ZstdCompressor or SnappyCompressor to use another algorithm.

Note

You can also modify the compression property through the ALTER TABLE statement.

Extensions

Configure Lindorm-specific table properties through the extensions property. Lindorm CQL supports the following extension properties.

STORAGE_POLICY

For cold storage, the keyword is STORAGE_POLICY. The value COLD indicates cold storage, and DEFAULT indicates hot storage (default).

CREATE TABLE personinfo (name text PRIMARY KEY, age int) WITH extensions = {'STORAGE_POLICY' : 'COLD'};  // Create a table with cold storage.
ALTER TABLE personinfo WITH extensions = {'STORAGE_POLICY' : 'DEFAULT'};  // Switch the table to hot storage.

COLD_BOUNDARY

For hot-cold separation, the keyword is COLD_BOUNDARY. When you use hot-cold separation, you do not need to set the table or column family property to COLD. If you have already set the column family property to COLD, remove the cold storage property first. For more information, see Introduction to Capacity-Oriented Cloud Storage.

CREATE TABLE personinfo (name text PRIMARY KEY, age int) WITH extensions = {'COLD_BOUNDARY':'86400'};  // Set the hot-cold separation timeline to 1 day (86400 seconds). Data older than the timeline is written to cold storage.
ALTER TABLE personinfo WITH extensions = {'COLD_BOUNDARY':''};    // Disable hot-cold separation.
ALTER TABLE personinfo WITH extensions = {'COLD_BOUNDARY':'1000'};   // Change the timeline to 1000 seconds.

CONSISTENCY_TYPE

For multi-zone Lindorm deployments, CONSISTENCY_TYPE sets the table consistency level. Valid values: eventual, timestamp, basic, and strong.

CREATE TABLE personinfo (name text PRIMARY KEY, age int) WITH extensions = {'CONSISTENCY_TYPE':'strong'};  // Create a table with strong consistency.
ALTER TABLE personinfo WITH extensions = {'CONSISTENCY_TYPE':'eventual'};  // Change the consistency level to eventual.

MUTABILITY

You must set MUTABILITY before you use secondary indexes or search indexes on the table. See Index types.

Value	Required CONSISTENCY_TYPE	Default at table creation
`IMMUTABLE`	-	No
`IMMUTABLE_ROWS`	`strong`	No
`MUTABLE_LATEST`	`strong`	No
`MUTABLE_ALL`	`strong`	Yes

CREATE TABLE personinfo (name text PRIMARY KEY, age int) WITH extensions = {'MUTABILITY':'IMMUTABLE'};  // Create a table with mutability set to IMMUTABLE.
ALTER TABLE personinfo WITH extensions = {'MUTABILITY':'MUTABLE_LATEST'};   // Change the table mutability to MUTABLE_LATEST.

Unsupported table options

Lindorm CQL does not support the following table options. The listed default values are the defaults in standard Cassandra CQL, because these options have no effect in Lindorm CQL.

Option	Type	Default value in standard Cassandra CQL	Description
`comment`	string	-	The description of the table.
`speculative_retry`	string	99PERCENTILE	-
`cdc`	boolean	false	Creates a change data capture (CDC) log on the table.
`gc_grace_seconds`	int	86400	The time before garbage collection of tombstones (deletion markers).
`bloom_filter_fp_chance`	float	0.00075	The target false positive probability for the stable Bloom filter. The size of the Bloom filter determines the provided probability. Lowering this value affects the size of the Bloom filter in memory and on disk.
`compaction`	map	STCS strategy	-
`caching`	map	-	-
`memtable_flush_period_in_ms`	int	0	-
`read_repair`	string	BLOCKING	-

Index statements

CREATE INDEX

Creates a secondary index.

Before you create a secondary index, set the MUTABILITY attribute on the source data table. For details, see Extensions. To compare a secondary index with a search index, see Index types.

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

The CREATE INDEX statement creates a secondary index on a column of a specified table. You can specify an index name before the ON keyword. If the column already contains data, the index is built asynchronously. After the index is created, new data written to the column is automatically indexed.

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

The CREATE INDEX statement only supports indexing a single column. To index multiple columns in a table, use the USING clause with the Lindorm secondary index class in a CREATE CUSTOM INDEX statement.

Example

CREATE INDEX myindex ON personinfo (c2);
CREATE INDEX ON personinfo (c2);
CREATE CUSTOM INDEX myindex ON personinfo (c1, c2)
    USING '<index-implementation-class>';

DROP INDEX

Deletes a secondary index.

Syntax

DROP INDEX [ IF EXISTS ] index_name

Description

Use the DROP INDEX statement to delete an existing secondary index. The variable in the statement is the index name index_name. You can optionally qualify the index name with a keyspace.

Note

If the index does not exist, the statement returns an error. If you use IF EXISTS, no error is returned, but the operation has no effect.

Example

DROP INDEX myindex;

CREATE SEARCH INDEX

Creates a search index.

Tables targeted by a CREATE SEARCH INDEX statement must have their properties extended through extensions. In a multi-zone Lindorm deployment, you must specify the consistency level and the MUTABILITY attribute. In a single-zone deployment, these settings are not required. For the full operation sequence, see Search index workflow.

Syntax

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

Description

The CREATE SEARCH INDEX statement supports building search indexes on certain columns of the source data table.

WITH COLUMNS(column): Specifies one or more columns to build a search index on. Separate each column with a comma.
WITH COLUMNS(*): Uses the (*) symbol to build a search index on all columns.

Important

After a search index is created, the index status is INACTIVE. The index does not serve queries until you activate it.

Example

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

Next steps

Run REBUILD SEARCH INDEX to activate the index and build it for existing data.

DROP SEARCH INDEX

Deletes a search index.

Syntax

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

Example

DROP SEARCH INDEX ON testks.personinfo;

REBUILD SEARCH INDEX

Activates a search index and builds the index for existing data. This operation can be time-consuming.

Syntax

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

Description

After a search index is created, the index status is INACTIVE. You must manually run the REBUILD operation to activate the index. The REBUILD operation also builds a search index for existing data, so the entire process can be time-consuming. Use the ASYNC parameter to specify that the build is an asynchronous operation.

Example

REBUILD SEARCH INDEX ON personinfo;
REBUILD SEARCH INDEX ASYNC ON personinfo;

ALTER SEARCH INDEX

Adds or removes columns in a search index.

Syntax

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

Note

Columns that you add with ADD FIELD or remove with DROP FIELD must exist in the source data table.

Example

ALTER SEARCH INDEX SCHEMA ON personinfo ADD FIELD c3;
ALTER SEARCH INDEX SCHEMA ON personinfo DROP FIELD c2;

Next steps

After you modify a search index with ALTER SEARCH INDEX, you must use REBUILD to rebuild the index status. See REBUILD SEARCH INDEX.