CREATE TABLE¶

Synopsis¶

CREATE TABLE <column family name>
  (<column_name> <type>,
  [<column_name2> <type>, ...]
  PRIMARY KEY (<column_name> <type>
    [, <column_name2> <type>,...])
  [WITH <option name> = <value>
  [AND <option name> = <value> [...]]]);

Description¶

CREATE TABLE creates new column family namespaces under the current keyspace. You can also use the alias CREATE COLUMNFAMILY. Valid column family names are strings of alphanumeric characters and underscores, which begin with a letter.

The only schema information that must be defined for a column family is the primary key (or row key) and its associated data type. Other column metadata, such as the size of the associated row and key caches, can be defined.

CREATE TABLE users (
  user_name varchar PRIMARY KEY,
  password varchar,
  gender varchar,
  session_token varchar,
  state varchar,
  birth_year bigint
);

CREATE TABLE emp (
  empID int,
  deptID int,
  first_name varchar,
  last_name varchar,
  PRIMARY KEY (empID, deptID)
);

Specifying the Key Type

When creating a new column family, specify PRIMARY KEY. It probably does not make sense to use counter for a key. The key type must be compatible with the partitioner in use. For example, OrderPreservingPartitioner and CollatingOrderPreservingPartitioner (deprecated partitioners) require UTF-8 keys.

Using Composite Primary Keys

When you use composite keys in CQL, Cassandra supports wide Cassandra rows using composite column names. In CQL 3, a primary key can have any number (1 or more) of component columns, but there must be at least one column in the column family that is not part of the primary key. The new wide row technique consumes more storage because for every piece of data stored, the column name is stored along with it.

cqlsh> CREATE TABLE History.tweets (
         tweet_id uuid PRIMARY KEY,
         author varchar,
         body varchar);

cqlsh> CREATE TABLE timeline (
         user_id varchar,
         tweet_id uuid,
         author varchar,
         body varchar,
         PRIMARY KEY (user_id, tweet_id));

Using Compact Storage

When you create a table using composite primary keys, rows can become very wide because 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 essentially the same as it was stored under CQL 2.

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 adding new columns that are not part of the PRIMARY KEY. Each logical row corresponds to exactly one physical column, as shown in the tweets timeline example.

At this time, updates to data in a column family created with compact storage is not allowed.

Unless you specify WITH COMPACT STORAGE, CQL creates a column family with non-compact storage.

Specifying Column Types

You assign columns a type during column family creation. Column types are specified as a parenthesized, comma-separated list of column term and type pairs. See CQL Data Types for the available types.

Column Family Options (not required)

Using the WITH clause and optional keyword arguments, you can control the configuration of a new column family. See CQL 3 Column Family Storage Parameters for the column family options you can define.

Examples¶

cqlsh> use zoo;

cqlsh:zoo> 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;

cqlsh:zoo> CREATE TABLE DogTypes (
             block_id uuid,
             species text,
             alias text,
             population varint,
             PRIMARY KEY (block_id)
           )
           WITH compression_parameters:sstable_compression = 'SnappyCompressor'
           AND compression_parameters:chunk_length_kb = 128;

CQL Commands