Data Definition¶

This reference covers CQL specification version 3.3.1¶

CQL stores data in tables, whose schema defines the layout of said data in the table, and those tables are grouped in keyspaces. A keyspace defines a number of options that applies to all the tables it contains, most prominently of which is the replication strategy used by the keyspace. It is generally encouraged to use one keyspace by application, and thus many clusters may define only one keyspace.

This section describes the statements used to create, modify, and remove keyspaces and tables.

CREATE KEYSPACE

USE

ALTER KEYSPACE

DROP KEYSPACE

CREATE TABLE

ALTER TABLE

DROP TABLE

TRUNCATE

Common Definitions¶

Keyspace and table names are defined by the following grammar:

keyspace_name: `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` '"'

Both keyspace and table names consist of only alphanumeric characters, cannot be empty and are limited in size to 48 characters (that limit exists mostly to avoid filenames, which may include the keyspace and table name, to go over the limits of certain file systems). By default, keyspace and table names are case insensitive (myTable is equivalent to mytable) but case sensitivity can be forced by using double-quotes ("myTable" is different from mytable).

Further, a table is always part of a keyspace and a table name can be provided fully-qualified by the keyspace it is part of. If is is not fully-qualified, the table is assumed to be in the current keyspace (see USE statement).

Further, valid column names are simply defined as:

column_name: `identifier`

We also define the notion of statement options for use in the following section:

options: `option` ( AND `option` )*
option: `identifier` '=' ( `identifier` | `constant` | `map_literal` )

In all cases for creating keyspaces and tables, if you are using Reserved Keywords, enclose them in single or double quotes.

CREATE KEYSPACE¶

A keyspace is created using a CREATE KEYSPACE statement:

create_keyspace_statement: CREATE KEYSPACE [ IF NOT EXISTS ] `keyspace_name` WITH `options`

For example:

CREATE KEYSPACE Excelsior
WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 3};

CREATE KEYSPACE Excalibur
WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1' : 1, 'DC2' : 3}
AND durable_writes = false;

The supported options are:

The replication property is mandatory and must at least contains the 'class' sub-option which defines the replication strategy class to use. The rest of the sub-options depend on what replication strategy is used. By default, Scylla support the following 'class':

• 'SimpleStrategy': defines a replication factor for the whole cluster. The only sub-options supported is 'replication_factor' to define that replication factor and is mandatory.
• 'NetworkTopologyStrategy': A replication strategy that lets you set the replication factor independently for each data-center. The rest of the sub-options are key-value pairs where a key is a data-center name and its value is the associated replication factor.

Attempting to create a keyspace that already exists will return an error unless the IF NOT EXISTS option is used. If it is used, the statement will be a no-op if the keyspace already exists.

CREATE KEYSPACE Excalibur
WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor': 3}
AND durable_writes = false;

ALTER KEYSPACE test WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor': 3}

ALTER KEYSPACE test WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor': 3, 'new-dc' : 5}

USE¶

The USE statement allows you to change the current keyspace (for the connection on which it is executed). A number of objects in CQL are bound to a keyspace (tables, user-defined types, functions, …) and the current keyspace is the default keyspace used when those objects are referred without a fully-qualified name (that is, without being prefixed a keyspace name). A USE statement simply takes the keyspace to use as current as argument:

use_statement: USE `keyspace_name`

ALTER KEYSPACE¶

An ALTER KEYSPACE statement lets you modify the options of a keyspace:

alter_keyspace_statement: ALTER KEYSPACE `keyspace_name` WITH `options`

For instance:

ALTER KEYSPACE Excelsior
WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 4};

ALTER KEYSPACE Excelsior
WITH replication = { 'class' : 'NetworkTopologyStrategy', 'dc1' : 3, 'dc2' : 0};

The supported options are the same than for creating a keyspace.

DROP KEYSPACE¶

Dropping a keyspace can be done using the DROP KEYSPACE statement:

drop_keyspace_statement: DROP KEYSPACE [ IF EXISTS ] `keyspace_name`

For instance:

DROP KEYSPACE Excelsior;

Dropping a keyspace results in the immediate, irreversible removal of that keyspace, including all the tables, UTD and functions in it, and all the data contained in those tables.

If the keyspace does not exist, the statement will return an error, unless IF EXISTS is used in which case the operation is a no-op.

CREATE TABLE¶

Creating a new table uses the CREATE TABLE statement:

create_table_statement: 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: COMPACT STORAGE [ AND `table_options` ]
                : | CLUSTERING ORDER BY '(' `clustering_order` ')' [ AND `table_options` ]
                : | scylla_encryption_options: '=' '{'[`cipher_algorithm` : <hash>]','[`secret_key_strength` : <len>]','[`key_provider`: <provider>]'}'
                : | `options`

clustering_order: `column_name` (ASC | DESC) ( ',' `column_name` (ASC | DESC) )*

For instance:

CREATE TABLE monkeySpecies (
    species text PRIMARY KEY,
    common_name text,
    population varint,
    average_size int
) WITH comment='Important biological records'
   AND read_repair_chance = 1.0;

CREATE TABLE timeline (
    userid uuid,
    posted_month int,
    posted_time uuid,
    body text,
    posted_by text,
    PRIMARY KEY (userid, posted_month, posted_time)
) WITH compaction = { 'class' : 'LeveledCompactionStrategy' };

CREATE TABLE loads (
    machine inet,
    cpu int,
    mtime timeuuid,
    load float,
    PRIMARY KEY ((machine, cpu), mtime)
) WITH CLUSTERING ORDER BY (mtime DESC);

CREATE TABLE users_picture (
    userid uuid,
    pictureid uuid,
    body text,
    posted_by text,
    PRIMARY KEY (userid, pictureid, posted_by)
) WITH compression = {'sstable_compression': 'LZ4Compressor'};


CREATE TABLE data_atrest (
    pk text PRIMARY KEY,
    c0 int
) WITH scylla_encryption_options = {
   'cipher_algorithm' : 'AES/ECB/PKCS5Padding',
   'secret_key_strength' : 128,
   'key_provider': 'LocalFileSystemKeyProviderFactory',
   'secret_key_file': '/etc/scylla/data_encryption_keys/secret_key'};

A CQL table has a name and is composed of a set of rows. Creating a table amounts to defining which columns the rows will be composed, which of those columns compose the primary key, as well as optional options for the table.

Attempting to create an already existing table will return an error unless the IF NOT EXISTS directive is used. If it is used, the statement will be a no-op if the table already exists.

CREATE TABLE t (
    pk int,
    t int,
    v text,
    s text static,
    PRIMARY KEY (pk, t)
);

INSERT INTO t (pk, t, v, s) VALUES (0, 0, 'val0', 'static0');
INSERT INTO t (pk, t, v, s) VALUES (0, 1, 'val1', 'static1');

SELECT * FROM t;
   pk | t | v      | s
  ----+---+--------+-----------
   0  | 0 | 'val0' | 'static1'
   0  | 1 | 'val1' | 'static1'

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

SELECT * FROM t;
   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

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

SELECT * FROM t;
   a | b | c
  ---+---+---
   0 | 0 | 4     // row 1
   0 | 1 | 9     // row 2
   0 | 2 | 2     // row 3
   0 | 3 | 3     // row 4

CREATE TABLE id (id int PRIMARY KEY) WITH compression = {'sstable_compression': 'LZ4Compressor'};

CREATE TABLE customer_data (
    cust_id uuid,
    cust_first-name text,
    cust_last-name text,
    cust_phone text,
    cust_get-sms text,
    PRIMARY KEY (customer_id)
) WITH cdc = { 'enabled' : 'true', 'preimage' : 'true' };

ALTER TABLE¶

Altering an existing table uses the ALTER TABLE statement:

alter_table_statement: ALTER TABLE `table_name` `alter_table_instruction`
alter_table_instruction: ADD `column_name` `cql_type` ( ',' `column_name` `cql_type` )*
                       : | DROP `column_name` ( `column_name` )*
                       : | ALTER `column_name` TYPE `cql_type`
                       : | WITH `options`
                       : | scylla_encryption_options: '=' '{'[`cipher_algorithm` : <hash>]','[`secret_key_strength` : <len>]','[`key_provider`: <provider>]'}'

For instance:

ALTER TABLE addamsFamily ADD gravesite varchar;

ALTER TABLE addamsFamily
       WITH comment = 'A most excellent and useful table'
       AND read_repair_chance = 0.2;


ALTER TABLE data_atrest (
    pk text PRIMARY KEY,
    c0 int
) WITH scylla_encryption_options = {
   'cipher_algorithm' : 'AES/ECB/PKCS5Padding',
   'secret_key_strength' : 128,
   'key_provider': 'LocalFileSystemKeyProviderFactory',
   'secret_key_file': '/etc/scylla/data_encryption_keys/secret_key'};

ALTER TABLE customer_data
   WITH cdc = { 'enabled' : 'true', 'preimage' : 'true' };

The ALTER TABLE statement can:

• Add new column(s) to the table (through the ADD instruction). Note that the primary key of a table cannot be changed and thus newly added column will, by extension, never be part of the primary key. Also note that compact tables have restrictions regarding column addition. Note that this is constant (in the amount of data the cluster contains) time operation.
• Remove column(s) from the table. This drops both the column and all its content, but note that while the column becomes immediately unavailable, its content is only removed lazily during compaction. Please also note the warnings below. Due to lazy removal, the altering itself is a constant (in the amount of data removed or contained in the cluster) time operation.
• Change data type of the column to a compatible type.
• Change some of the table options (through the WITH instruction). The supported options are the same that when creating a table (outside of COMPACT STORAGE and CLUSTERING ORDER that cannot be changed after creation). Note that setting any compaction sub-options has the effect of erasing all previous compaction options, so you need to re-specify all the sub-options if you want to keep them. The same note applies to the set of compression sub-options.
• Change or add any of the Encryption options above.
• Change or add any of the CDC options above.

DROP TABLE¶

Dropping a table uses the DROP TABLE statement:

drop_table_statement ::=  DROP TABLE [ IF EXISTS ] table_name

Dropping a table results in the immediate, irreversible removal of the table, including all data it contains.

If the table does not exist, the statement will return an error, unless IF EXISTS is used in which case the operation is a no-op.

TRUNCATE¶

A table can be truncated using the TRUNCATE statement:

truncate_statement ::=  TRUNCATE [ TABLE ] table_name

Note that TRUNCATE TABLE foo is allowed for consistency with other DDL statements but tables are the only object that can be truncated currently and so the TABLE keyword can be omitted.

Truncating a table permanently removes all existing data from the table, but without removing the table itself.

Apache Cassandra Query Language

Copyright

© 2016, The Apache Software Foundation.

Apache®, Apache Cassandra®, Cassandra®, the Apache feather logo and the Apache Cassandra® Eye logo are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries. No endorsement by The Apache Software Foundation is implied by the use of these marks.