Data Definition¶

Common definitions¶

The names of the keyspaces and tables 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 name should be comprised 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, the valid names for columns is 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 )

CREATE KEYSPACE¶

A keyspace is created using a CREATE KEYSPACE statement:

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

For instance:

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 depends on what replication strategy is used. By default, Cassandra support the following 'class':

• 'SimpleStrategy': A simple strategy that 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 allows to 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.

USE¶

The USE statement allows 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 allows to 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};

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 exists, 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 ]
                            | 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);

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, c, d)
);

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

ALTER TABLE¶

Altering an existing table uses the ALTER TABLE statement:

alter_table_statement   ::=  ALTER TABLE table_name alter_table_instruction
alter_table_instruction ::=  ALTER column_name TYPE cql_type
                             | ADD column_name cql_type ( ',' column_name cql_type )*
                             | DROP column_name ( column_name )*
                             | WITH options

For instance:

ALTER TABLE addamsFamily ALTER lastKnownLocation TYPE uuid;

ALTER TABLE addamsFamily ADD gravesite varchar;

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

The ALTER TABLE statement can:

• Change the type of one of the column in the table (through the ALTER instruction). Note that the type of a column cannot be changed arbitrarily. The change of type should be such that any value of the previous type should be a valid value of the new type. Further, for clustering columns and columns on which a secondary index is defined, the new type must sort values in the same way the previous type does. See the type compatibility table below for detail on which type changes are accepted.
• 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 see 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 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.

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.