CREATE TABLE statement [YCQL]

Synopsis

Use the CREATE TABLE statement to create a new table in a keyspace. It defines the table name, column names and types, primary key, and table properties.

Syntax

Diagram

create_table

table_schema

table_properties

Grammar

ebnf

create_table ::= CREATE TABLE [ IF NOT EXISTS ] table_name
                     '(' table_element [ ',' table_element ...] ')'
                     [WITH table_properties];

table_element ::= table_column | table_constraints

table_column ::= column_name column_type [ column_constraint ...]

column_constraint ::= PRIMARY KEY | STATIC

table_constraints ::= PRIMARY KEY '(' partition_key_column_list clustering_key_column_list ')'

partition_key_column_list ::= '(' column_name [ ',' column_name ...] ')' | column_name

clustering_key_column_list ::= [ ',' column_name ...]

table_properties = [table_options]
                    [[AND] CLUSTERING ORDER BY '(' column_ordering_property [ ',' column_ordering_property ...] ')']
                    [[AND] COMPACT STORAGE]

table_options = property_name '=' property_literal [AND property_name '=' property_literal ...]

column_ordering_property ::= column_name [ ASC | DESC ]

Where

table_name, column_name, and property_name are identifiers (table_name may be qualified with a keyspace name).
property_literal is a literal of either boolean, text, or map data type.

Semantics

An error is raised if table_name already exists in the associated keyspace unless the IF NOT EXISTS option is used.

PRIMARY KEY

Primary key must be defined in either column_constraint or table_constraint but not in both of them.
Each row in a table is uniquely identified by its primary key.
Primary key columns are either partitioning columns or clustering columns (described below).
If primary key is set as a column constraint, then that column is the partition column and there are no clustering columns.
If primary key is set as a table constraint then:
- The partition columns are given by the first entry in the primary key list: the nested column list (if given), otherwise the first column.
- The clustering columns are the rest of the columns in the primary key list (if any).
Types MAP, SET, LIST, JSONB, USER_DEFINED_TYPE cannot be used in the primary key.

PARTITION KEY

Partition key is required and defines a split of rows into partitions.
Rows that share the same partition key form a partition and will be colocated on the same replica node.

CLUSTERING KEY

Clustering key is optional and defines an ordering for rows within a partition.
Default ordering is ascending (ASC) but can be set for each clustering column as ascending or descending using the CLUSTERING ORDER BY table property.

STATIC COLUMNS

Columns declared as STATIC will share the same value for all rows within a partition (that is, rows having the same partition key).
Columns in the primary key cannot be static.
A table without clustering columns cannot have static columns (without clustering columns the primary key and the partition key are identical so static columns would be the same as regular columns).

table_properties

The CLUSTERING ORDER BY property can be used to set the ordering for each clustering column individually (default is ASC).
The default_time_to_live property sets the default expiration time (TTL) in seconds for a table. The expiration time can be overridden by setting TTL for individual rows. The default value is 0 and means rows do not expire.
The transactions property specifies if distributed transactions are enabled in the table. To enable distributed transactions, use transactions = { 'enabled' : true }.
Use the AND operator to use multiple table properties.
The other YCQL table properties are allowed in the syntax but are currently ignored internally (have no effect).
The TABLETS = <num> property specifies the number of tablets to be used for the specified YCQL table. Setting this property overrides the value from the --yb_num_shards_per_tserver option. For an example, see Create a table specifying the number of tablets.
COMPACT STORAGE is only for syntax compatibility with Cassandra. It doesn't affect the underlying storage.

Examples

Use column constraint to define primary key

'user_id' is the partitioning column and there are no clustering columns.

sql

ycqlsh:example> CREATE TABLE users(user_id INT PRIMARY KEY, full_name TEXT);

Use table constraint to define primary key

'supplier_id' and 'device_id' are the partitioning columns and 'model_year' is the clustering column.

sql

ycqlsh:example> CREATE TABLE devices(supplier_id INT,
                                    device_id INT,
                                    model_year INT,
                                    device_name TEXT,
                                    PRIMARY KEY((supplier_id, device_id), model_year));

Use column constraint to define a static column

You can do this as follows:

sql

ycqlsh:example> CREATE TABLE items(supplier_id INT,
                                  item_id INT,
                                  supplier_name TEXT STATIC,
                                  item_name TEXT,
                                  PRIMARY KEY((supplier_id), item_id));

sql

ycqlsh:example> INSERT INTO items(supplier_id, item_id, supplier_name, item_name)
               VALUES (1, 1, 'Unknown', 'Wrought Anvil');

sql

ycqlsh:example> INSERT INTO items(supplier_id, item_id, supplier_name, item_name)
               VALUES (1, 2, 'Acme Corporation', 'Giant Rubber Band');

sql

ycqlsh:example> SELECT * FROM items;

output

 supplier_id | item_id | supplier_name    | item_name
-------------+---------+------------------+-------------------
           1 |       1 | Acme Corporation |     Wrought Anvil
           1 |       2 | Acme Corporation | Giant Rubber Band

Use table property to define the order (ascending or descending) for clustering columns

Timestamp column 'ts' will be stored in descending order (latest values first).

sql

ycqlsh:example> CREATE TABLE user_actions(user_id INT,
                                         ts TIMESTAMP,
                                         action TEXT,
                                         PRIMARY KEY((user_id), ts))
                                         WITH CLUSTERING ORDER BY (ts DESC);

sql

ycqlsh:example> INSERT INTO user_actions(user_id, ts, action) VALUES (1, '2000-12-2 12:30:15', 'log in');

sql

ycqlsh:example> INSERT INTO user_actions(user_id, ts, action) VALUES (1, '2000-12-2 12:30:25', 'change password');

sql

ycqlsh:example> INSERT INTO user_actions(user_id, ts, action) VALUES (1, '2000-12-2 12:30:35', 'log out');

sql

ycqlsh:example> SELECT * FROM user_actions;

output

 user_id | ts                              | action
---------+---------------------------------+-----------------
       1 | 2000-12-02 19:30:35.000000+0000 |         log out
       1 | 2000-12-02 19:30:25.000000+0000 | change password
       1 | 2000-12-02 19:30:15.000000+0000 |          log in

Use table property to define the default expiration time for rows

You can do this as follows:

sql

ycqlsh:example> CREATE TABLE sensor_data(sensor_id INT,
                                        ts TIMESTAMP,
                                        value DOUBLE,
                                        PRIMARY KEY((sensor_id), ts))
                                        WITH default_time_to_live = 5;

First insert at time T (row expires at T + 5).

sql

ycqlsh:example> INSERT INTO sensor_data(sensor_id, ts, value) VALUES (1, '2017-10-1 11:22:31', 3.1);

Second insert 3 seconds later (row expires at T + 8).

sql

ycqlsh:example> INSERT INTO sensor_data(sensor_id, ts, value) VALUES (2, '2017-10-1 11:22:34', 3.4);

First select 3 seconds later (at time T + 6).

sql

ycqlsh:example> SELECT * FROM sensor_data;

output

 sensor_id | ts                              | value
-----------+---------------------------------+-------
         2 | 2017-10-01 18:22:34.000000+0000 |   3.4

Second select 3 seconds later (at time T + 9).

sql

ycqlsh:example> SELECT * FROM sensor_data;

output

 sensor_id | ts | value
-----------+----+-------

Create a table specifying the number of tablets

You can use the CREATE TABLE statement with the WITH tablets = <num> clause to specify the number of tablets for a table. This is useful to scale the table up or down based on requirements. For example, for smaller static tables, it may be wasteful to have a large number of shards (tablets). In that case, you can use this to reduce the number of tablets created for the table. Similarly, for a very large table, you can use this statement to presplit the table into a large number of shards to get improved performance.

Note that YugabyteDB, by default, presplits a table in yb_num_shards_per_tserver * num_of_tserver shards. This clause can be used to override that setting on per-table basis.

sql

ycqlsh:example> CREATE TABLE tracking (id int PRIMARY KEY) WITH tablets = 10;

If you create an index for these tables, you can also specify the number of tablets for the index.

You can also use AND to add other table properties, like in this example.

sql

ycqlsh:example> CREATE TABLE tracking (id int PRIMARY KEY) WITH tablets = 10 AND transactions = { 'enabled' : true };

Synopsis

Syntax

Diagram

create_table

table_schema

table_properties

Grammar

Semantics

PRIMARY KEY

PARTITION KEY

CLUSTERING KEY

STATIC COLUMNS

table_properties

Examples

Use column constraint to define primary key

Use table constraint to define primary key

Use column constraint to define a static column

Use table property to define the order (ascending or descending) for clustering columns

Use table property to define the default expiration time for rows

Create a table specifying the number of tablets

See also