Data Definition¶

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 it 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 Excalibur
WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1' : 1, 'DC2' : 3}
AND durable_writes = true;

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, ScyllaDB supports the following 'class':

CREATE KEYSPACE excalibur
    WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor' : 3}

DESCRIBE KEYSPACE excalibur
    CREATE KEYSPACE excalibur WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1': '3', 'DC2': '3'} AND durable_writes = true;

CREATE KEYSPACE excalibur
    WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor' : 3, 'DC2': 2}

DESCRIBE KEYSPACE excalibur
    CREATE KEYSPACE excalibur WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1': '3', 'DC2': '2'} AND durable_writes = true;

CREATE KEYSPACE excalibur
    WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor' : 3, 'DC2': 0} ;

DESCRIBE KEYSPACE excalibur
    CREATE KEYSPACE excalibur WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1': '3'} AND durable_writes = true;

CREATE KEYSPACE excalibur
WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'replication_factor': 3,
} AND tablets = {
    'initial': 2048
};

USE¶

The USE statement allows you to change the current keyspace (for the connection on which it is executed). Some 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 specified keyspace and uses the name as an argument for all future actions until this name is changed.

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' : 'NetworkTopologyStrategy', 'dc1' : 3, 'dc2' : 0};

The supported options are the same as 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 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>]'}'
                : | caching  '=' ' {'caching_options'}'
                : | `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';

 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'};

CREATE TABLE caching (
                 k int PRIMARY KEY,
                 v1 int,
                 v2 int,
             ) WITH caching = {'enabled': 'true'};

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 id (id int PRIMARY KEY) WITH compression = {};

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' };

CREATE TABLE caching (
                 k int PRIMARY KEY,
                 v1 int,
                 v2 int,
             ) WITH caching = {'enabled': 'true'};

CREATE TABLE ks.cf (key blob PRIMARY KEY,  val blob) WITH tombstone_gc = {'mode':'repair'};

ALTER TABLE ks.cf WITH tombstone_gc = {'mode':'repair'} ;

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` [ USING TIMESTAMP `timestamp` ]
                       : | DROP '(' `column_name` ( ',' `column_name` )* ')' [ USING TIMESTAMP `timestamp` ]
                       : | 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';


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.

• Change or add per-partition rate limits. See Limiting the rate of requests per partition.

It is also possible to drop a column with specified timestamp ALTER TABLE ... DROP ... USING TIMESTAMP .... The purpose of this statement is to be able to safely restore schema (see Backup and Restore Procedures) in the case a column was dropped and re-added later. The timestamp should be obtained by describing schema with internals DESC SCHEMA WITH INTERNALS (or other descriptions like DESC TABLE ks.cf WITH INTERNALS)

For example: Let’s say you have a table with some data. Then you drop one of the column and re-add it later. In the future, when you wish to restore the schema, you have to also drop the column with specified timestamp (the same timestamp as the original drop) and re-add it again. Otherwise, you can resurrect your data (if you skip ALTER ... DROP/ADD ... entirely) or you can lose data inserted after column re-addition (if you drop the column without the timestamp).

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 removal of the table, including all data it contains and any associated secondary indexes.

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`
                  : [ USING TIMEOUT `timeout` ]
timeout: `duration`

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.

The USING TIMEOUT clause allows specifying a timeout for a specific request.

For example:

TRUNCATE TABLE users USING TIMEOUT 5m;

• Apache Cassandra Query Language (CQL) Reference

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.