DDL

Statements covered

• CREATE KEYSPACE

• ALTER KEYSPACE

• USE

• DROP KEYSPACE

• CREATE TABLE

• ALTER TABLE

• DROP TABLE

• TRUNCATE

• CREATE INDEX

• DROP INDEX

• CREATE SEARCH INDEX

• DROP SEARCH INDEX

• REBUILD SEARCH INDEX

• ALTER SEARCH INDEX

Key concepts

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

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 keyspace options.

Syntax

ALTER KEYSPACE keyspace_name WITH options

Example

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

USE

Sets the active keyspace for the session. Objects such as tables, user-defined types (UDTs), and user-defined functions (UDFs) are scoped to a keyspace.

Syntax

USE keyspace_name

Example

USE testks;

DROP KEYSPACE

Deletes a keyspace and all its contents.

Syntax

DROP KEYSPACE [ IF EXISTS ] keyspace_name

Usage notes

• Use IF EXISTS to suppress the error when the keyspace does not exist.

• Only the root user can drop keyspaces.

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

Use IF NOT EXISTS to skip the statement silently when the table already exists.

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

SELECT * FROM personinfo;
   a | b | c | d
  ---+---+---+---
   0 | 0 | 0 | 0    -- row 1: same partition as row 2 (a=0, b=0)
   0 | 0 | 1 | 1    -- row 2
   0 | 1 | 2 | 2    -- row 3: different partition (a=0, b=1)
   0 | 1 | 3 | 3    -- row 4
   1 | 1 | 4 | 4    -- row 5: different partition (a=1, b=1)

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

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

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

-- Create a table with cold storage
CREATE TABLE personinfo (name text PRIMARY KEY, age int)
  WITH extensions = {'STORAGE_POLICY': 'COLD'};

-- Switch from cold to hot storage
ALTER TABLE personinfo WITH extensions = {'STORAGE_POLICY': 'DEFAULT'};

-- Enable cold/hot data separation with an 86400-second (1 day) boundary
CREATE TABLE personinfo (name text PRIMARY KEY, age int)
  WITH extensions = {'COLD_BOUNDARY': '86400'};

-- Update the boundary to 1000 seconds
ALTER TABLE personinfo WITH extensions = {'COLD_BOUNDARY': '1000'};

-- Disable cold/hot data separation
ALTER TABLE personinfo WITH extensions = {'COLD_BOUNDARY': ''};

-- Set strong consistency
CREATE TABLE personinfo (name text PRIMARY KEY, age int)
  WITH extensions = {'CONSISTENCY_TYPE': 'strong'};

-- Change to eventual consistency
ALTER TABLE personinfo WITH extensions = {'CONSISTENCY_TYPE': 'eventual'};

-- Set IMMUTABLE mutability
CREATE TABLE personinfo (name text PRIMARY KEY, age int)
  WITH extensions = {'MUTABILITY': 'IMMUTABLE'};

-- Change to MUTABLE_LATEST
ALTER TABLE personinfo WITH extensions = {'MUTABILITY': 'MUTABLE_LATEST'};

-- Basic table with a single-column primary key
CREATE TABLE tb (name text PRIMARY KEY, age int);

-- Table with a 1000-second default TTL
CREATE TABLE ttltb (name text PRIMARY KEY, age int)
  WITH default_time_to_live = 1000;

-- Table with LZ4 compression
CREATE TABLE cptb (name text PRIMARY KEY, age int)
  WITH compression = {'sstable_compression': 'LZ4Compressor'};

-- Table with a 10-second cold/hot data separation boundary
CREATE TABLE hct (name text PRIMARY KEY, age int)
  WITH extensions = {'COLD_BOUNDARY': '10'};

ALTER TABLE

Modifies a table's columns or options.

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

Usage notes

• ADD — adds one or more columns. The primary key cannot be changed after table creation, so new columns cannot be part of the primary key.

• DROP — not supported in Lindorm CQL. Column drop operations are ignored.

• WITH — modifies table options. All options set at creation time can be changed except CLUSTERING ORDER. Modifiable options: default_time_to_live, compression, and extensions.

Example

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

DROP TABLE

Deletes a table and all its data.

Syntax

DROP TABLE [ IF EXISTS ] table_name

Usage notes

• Use IF EXISTS to suppress the error when the table does not exist.

• Only the root user can drop tables.

Example

DROP TABLE personinfo;

TRUNCATE

Deletes all rows from a table while keeping the table schema.

Syntax

TRUNCATE [ TABLE ] table_name

Usage notes

• Only the root user can truncate tables.

Example

TRUNCATE TABLE personinfo;

CREATE INDEX

Creates a secondary index on a table column.

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

Existing data in the indexed column is indexed asynchronously after the index is created. New data is indexed automatically.

Use IF NOT EXISTS to suppress the error when the index already exists.

Single-column index

CREATE INDEX myindex ON personinfo (c2);
CREATE INDEX ON personinfo (c2);  -- index name is optional

Multi-column index

To index multiple columns, use CREATE CUSTOM INDEX with the Lindorm secondary index class:

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

DROP INDEX

Deletes a secondary index.

Syntax

DROP INDEX [ IF EXISTS ] index_name

Usage notes

• index_name includes the keyspace qualifier.

• Use IF EXISTS to suppress the error when the index does not exist.

Example

DROP INDEX myindex;

CREATE SEARCH INDEX

Creates a full-text index on one or more columns of a table.

Syntax

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

Usage notes

• WITH COLUMNS (col1, col2, ...) — indexes the specified columns.

• WITH COLUMNS (*) — indexes all columns.

• In multi-zone deployments, set CONSISTENCY_TYPE and MUTABILITY on the table using the extensions option before creating a Search Index.

• After creation, the index status is INACTIVE. Run REBUILD SEARCH INDEX to activate it.

Example

CREATE SEARCH INDEX schidx ON personinfo 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.personinfo;

REBUILD SEARCH INDEX

Activates a full-text index and builds the index for existing data.

Syntax

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

After CREATE SEARCH INDEX, the index status is INACTIVE. Run REBUILD SEARCH INDEX to set it to active and index any existing data.

Indexing existing data may take a significant amount of time. Use the ASYNC option to run the rebuild in the background.

Examples

-- Synchronous rebuild
REBUILD SEARCH INDEX ON personinfo;

-- Asynchronous rebuild
REBUILD SEARCH INDEX ASYNC ON personinfo;

ALTER SEARCH INDEX

Adds or removes indexed columns from a full-text index.

Syntax

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

The column specified in ADD FIELD or DROP FIELD must exist in the table.

Examples

-- Add a column to the index
ALTER SEARCH INDEX SCHEMA ON personinfo ADD (c3);

-- Remove a column from the index
ALTER SEARCH INDEX SCHEMA ON personinfo DROP (c2);