CREATE TABLE

Creates a new table.

Creates a new table in the selected keyspace. Use IF NOT EXISTS to suppress the error message if the table already exists; no table is created. A static column can store the same data in multiple clustered rows of a partition, and then retrieve that data with a single SELECT statement.

Tables support a single counter column.

Synopsis

CREATE TABLE [ IF NOT EXISTS ] [keyspace_name.]table_name
  ( column_definition [ , ... ] | PRIMARY KEY (column_list) )
Table 1. Legend
Syntax conventions Description
UPPERCASE Literal keyword.
Lowercase Not literal.
Italics Variable value. Replace with a user-defined value.
[] Optional. Square brackets ( [] ) surround optional command arguments. Do not type the square brackets.
( ) Group. Parentheses ( ( ) ) identify a group to choose from. Do not type the parentheses.
| Or. A vertical bar ( | ) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
... Repeatable. An ellipsis ( ... ) indicates that you can repeat the syntax element as often as required.
'Literal string' Single quotation ( ' ) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
{ key : value } Map collection. Braces ( { } ) enclose map collections or key value pairs. A colon separates the key and the value.
<datatype1,datatype2> Set, list, map, or tuple. Angle brackets ( < > ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
cql_statement; End CQL statement. A semicolon ( ; ) terminates all CQL statements.
[--] Separate the command line options from the command arguments with two hyphens ( -- ). This syntax is useful when arguments might be mistaken for command line options.
' <schema> ... </schema> ' Search CQL only: Single quotation marks ( ' ) surround an entire XML schema declaration.
@xml_entity='xml_entity_type' Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.

column_definition

Sets the column name, defines the data type, and optionally sets a column to static or primary key.

Enclosed in parentheses after the table name, use a comma-separated list to define multiple columns. All tables must have at least one primary key column. Each column is defined using the following syntax: column_name cql_type_definition [STATIC | PRIMARY KEY] [, ...]
Restriction:
  • When primary key is at the end of a column definition, that column is the only primary key for the table.
  • A table must have at least one PRIMARY KEY.
  • A static column cannot be a primary key.
  • Primary keys can include frozen collections.
column_name
Use a unique name for each column in a table. To preserve case or use special characters, enclose the name in double-quotes.
cql_type_definition
Defines the type of data allowed in the column. See CQL data type or a user-defined type.
STATIC
Optional, the column has a single value.
PRIMARY KEY

When the PRIMARY KEY is one column, append PRIMARY KEY to the end of the column definition. This is only schema information required to create a table. When there is one primary key, it is the partition key; the data is divided and stored by the unique values in this column:column_name cql_type_definition PRIMARY KEY.

Alternatively, you can declare the primary key consisting of only one column in the same way as you declare a compound primary key.

PRIMARY KEY (column_list)

Uniquely identifies rows, determines storage partitions, and orders data (clustering columns) within a partition.

Uniquely identifies rows, determines storage partitions, and orders data (clustering columns) within a partition.
Restriction: Primary keys cannot have the following data types: counter, non-frozen collection, or static.
Important: A NULL value cannot be inserted into a PRIMARY KEY column. This restriction applies to both partition keys and clustering columns.
column_list
Defines a partition and clustering columns, which affects how the data in stored.
  • Compound primary key: the first column is the partition key, and the additional columns are clustering keys. Syntax: PRIMARY KEY (partition_column_name, clustering_column_name [, ...])
  • Composite partition key: Multiple columns in the partition key. Enclose the partition key columns in parentheses. Syntax: PRIMARY KEY ((partition_column_name[, ...]),clustering_column_name [, ...])

Examples

CREATE TABLE CQL examples.

cassandra.yaml

The location of the cassandra.yaml file depends on the type of installation:
Package installations /etc/dse/cassandra/cassandra.yaml
Tarball installations installation_location/resources/cassandra/conf/cassandra.yaml

Creating a table with UUID as the primary key

Create the cyclist_name table with UUID as the primary key:
CREATE TABLE IF NOT EXISTS cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);

Creating a composite partition key

Create a table that is optimized for query by cyclist rank by year:
CREATE TABLE IF NOT EXISTS cycling.rank_by_year_and_name (
  race_year int,
  race_name text,
  cyclist_name text,
  rank int,
  PRIMARY KEY ((race_year, race_name), rank)
);

Creating a table with a frozen UDT

Create the race_winners table that has a frozen user-defined type (UDT):
CREATE TABLE IF NOT EXISTS cycling.race_winners (
  cyclist_name FROZEN<fullname>, 
  race_name text,
  race_position int,
  PRIMARY KEY (race_name, race_position)
);

See Creating a user-defined type for information on creating UDTs. UDTs can be created unfrozen if only non-collection fields are used in the user-defined type creation. If the table is created with an unfrozen UDT, then individual field values can be updated and deleted.

Creating a table with a CDC log

Create a change data capture log for the cyclist_id table:
CREATE TABLE IF NOT EXISTS cycling.cyclist_id (
  lastname text,
  firstname text,
  age int,
  id UUID,
  PRIMARY KEY ((lastname, firstname), age)
);
CDC logging must be enabled in cassandra.yaml.
CAUTION: Before enabling CDC logging, have a plan for moving and consuming the log information. After the disk space limit is reached, writes to CDC-enabled tables are rejected until more space is freed. See Change-data-capture (CDC) space settings for information about available CDC settings.