CQL for Cassandra 2.0

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_definition is:

  column_name cql_type STATIC PRIMARY KEY
| ( PRIMARY KEY ( partition_key ) )
Restrictions:
  • There should always be exactly one primary key definition.
  • cql_type of the primary key must be a custom type or a CQL data type.
  • cql_type of a collection uses this syntax:
    LIST<cql_type>
    | SET<cql_type>
    | MAP<cql_type, cql_type>

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 ), ...) )

Synopsis legend

  • Uppercase means literal
  • Lowercase means not literal
  • Italics mean optional
  • The pipe (|) symbol means OR or AND/OR
  • Ellipsis (...) means repeatable
  • Orange ( and ) means not literal, indicates scope

A semicolon that terminates CQL statements is not included in the synopsis.

Description

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

Attempting to create an already existing table will return an error unless the IF NOT EXISTS option is used. If the option is used, the statement will be 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.

In Cassandra 2.0.6 and later, 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.

Defining a column

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

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>
);

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 CQL table properties. Enclose a string property in single quotation marks. For example:

CREATE TABLE MonkeyTypes (
  block_id uuid,
  species text,
  alias text,
  population varint,
  PRIMARY KEY (block_id)
)
WITH comment='Important biological records'
AND read_repair_chance = 1.0;
CREATE TABLE DogTypes (
  block_id uuid,
  species text,
  alias text,
  population varint,
  PRIMARY KEY (block_id)
) WITH compression =
    { 'sstable_compression' : 'DeflateCompressor', 'chunk_length_kb' : 64 }
  AND compaction =
    { 'class' : 'SizeTieredCompactionStrategy', 'min_threshold' : 6 };

You can specify using compact storage or clustering order using the WITH clause.

Using compact storage

The compact storage directive is used for backward compatibility of old applications with CQL. Use the directive to store data in the legacy (Thrift) storage engine format. To take advantage of CQL capabilities, do not use this directive in new applications.

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.

Show/hide