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) )
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.
column_name
cql_type_definition [STATIC | PRIMARY KEY] [, ...]
- 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.
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 [, ...])
- Compound primary key: the first column is the partition key, and
the additional columns are clustering keys. Syntax:
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
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 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
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
cyclist_id
table:CREATE TABLE IF NOT EXISTS cycling.cyclist_id ( lastname text, firstname text, age int, id UUID, PRIMARY KEY ((lastname, firstname), age) );