CREATE TABLE

Define a new table.

Synopsis

CREATE TABLE IF NOT EXISTS keyspace_name.table_name 
( column_definition, column_definition, ...)
WITH property AND property ...

column_name cql_type STATIC PRIMARY KEY
| column_name <tuple<tuple_type> tuple<tuple_type>... > PRIMARY KEY
| column_name frozen<user-defined_type> PRIMARY KEY
| column_name frozen<collection_name><collection_type>... PRIMARY KEY
|  PRIMARY KEY ( partition_key ) 

Cassandra 2.1.0 to 2.1.2 column_definition is:

column_name cql_type STATIC PRIMARY KEY
| column_name frozen<tuple<tuple_type> tuple<tuple_type>... > PRIMARY KEY
| column_name frozen<user-defined_type> PRIMARY KEY
| column_name frozen<collection> PRIMARY KEY
|  PRIMARY KEY ( partition_key ) 

PRIMARY KEY is:

column_name
| ( column_name1, column_name2, column_name3 ...)
| ((column_name4, column_name5), column_name6, column_name7 ...)

column_name1 is the partition key.

column_name2, column_name3 ... are clustering columns.

column_name4, column_name5 are partitioning keys.

column_name6, column_name7 ... are clustering columns.

property is a CQL table property, enclosed in single quotation marks in the case of strings, or one of these directives:

COMPACT STORAGE
|  CLUSTERING ORDER BY (clustering_column  ASC) | DESC , ...) 

Description

CREATE TABLE creates a new table under the current keyspace. You can also use the alias CREATE COLUMNFAMILY.

Cassandra 2.1.1 and later supports the IF NOT EXISTS syntax for creating a trigger. Attempting to create an existing table returns an error unless the IF NOT EXISTS option is used. If the option is used, the statement if a no-op if the table already exists.

Valid table names are strings of alphanumeric characters and underscores, which begin with a letter. You can use dot notation to specify a keyspace for the table: keyspace name followed by a period followed the name of the table, Cassandra creates the table in the specified keyspace, but does not change the current keyspace; otherwise, if you do not use a keyspace name, Cassandra creates the table within the current keyspace.

You can use a static column to store the same data in multiple clustered rows of a partition, and then retrieve that data with a single SELECT statement.

You can add a counter column, which has been improved in Cassandra 2.1, to a table.

Defining a column

You assign a type to columns during table creation. Column types, other than collection-type columns, are specified as a parenthesized, comma-separated list of column name and type pairs.

This example shows how to create a table that includes collection-type columns: map, set, and list.

CREATE TABLE users (
  userid text PRIMARY KEY,
  first_name text,
  last_name text,
  emails set<text>,
  top_scores list<int>,
  todo map<timestamp, text>
);

Defining columns of the user-defined, tuple types, and collections

To support future capabilities, a column definition of a user-defined or tuple type requires the frozen keyword. Cassandra serializes a frozen value having multiple components into a single value. For examples and usage information, see "Using a user-defined type", "Tuple type", and Collection type.

frozen <tuple <int, tuple<text, double>>>

Ordering results

You can order query results to make use of the on-disk sorting of columns. You can order results in ascending or descending order. The ascending order will be more efficient than descending. If you need results in descending order, you can specify a clustering order to store columns on disk in the reverse order of the default. Descending queries will then be faster than ascending ones.

The following example shows a table definition that changes the clustering order to descending by insertion time.

CREATE TABLE timeseries (
  event_type text,
  insertion_time timestamp,
  event blob,
  PRIMARY KEY (event_type, insertion_time)
)
WITH CLUSTERING ORDER BY (insertion_time DESC);

Using compact storage

When you create a table using compound primary keys, for every piece of data stored, the column name needs to be stored along with it. Instead of each non-primary key column being stored such that each column corresponds to one column on disk, an entire row is stored in a single column on disk. If you need to conserve disk space, use the WITH COMPACT STORAGE directive that stores data in the legacy (Thrift) storage engine format.

CREATE TABLE sblocks (
  block_id uuid,
  subblock_id uuid,
  data blob,
  PRIMARY KEY (block_id, subblock_id)
)
WITH COMPACT STORAGE;

Using the compact storage directive prevents you from defining more than one column that is not part of a compound primary key. A compact table using a primary key that is not compound can have multiple columns that are not part of the primary key.

A compact table that uses a compound primary key must define at least one clustering column. Columns cannot be added nor removed after creation of a compact table. Unless you specify WITH COMPACT STORAGE, CQL creates a table with non-compact storage.

Setting a table property

Using the optional WITH clause and keyword arguments, you can configure caching, compaction, and a number of other operations that Cassandra performs on new table. Use the WITH clause to specify the properties of tables listed in Setting a table property. Enclose a string property in single quotation marks.

Creating a table WITH ID

If a table is accidentally dropped with DROP TABLE, this option can be used to recreate the table and run a commitlog replay to retrieve the data.

CREATE TABLE users (
        userid text PRIMARY KEY,
        emails set<text>
        ) WITH ID='5a1c395e-b41f-11e5-9f22-ba0be0483c18';