Creating a table

How to create CQL tables.

In CQL, data is stored in tables containing rows of columns, similar to SQL definitions.
Note: The concept of rows and columns in the internal implementation of databases is not the same. For more information, see A Thrift to CQL3 upgrade guide or CQL3 for Cassandra experts.
Tables can be created, dropped, and altered at runtime without blocking updates and queries. To create a table, you must define a primary key and other data columns. Add the optional WITH clause and keyword arguments to configure table properties (caching, compaction, and so forth). See table_options.

Create schema using cqlsh

Create table schema using cqlsh. DataStax Enterprise does not support dynamic schema generation because collisions can occur if multiple clients attempt to generate tables simultaneously. To recover from collisions, follow the instructions in schema collision fix.

Primary Key

A Primary_key identifies the location and order of stored data. The primary key is defined when the table is created and cannot be altered. If you must change the primary key, create a new table schema and write the existing data to the new table. See ALTER TABLE for details on altering a table after creation.

The DataStax Enterprise database is a partition row store. The first element of the primary key, the partition key, specifies which node will hold a particular table row. At the minimum, the primary key must consist of a partition key. You can define a compound partition key to split a data set so that related data is stored on separate partitions. A compound primary key includes clustering columns that order the data on a partition.

The definition of a table's primary key is critical. Carefully model how data in a table will be inserted and retrieved before choosing which columns to define in the primary key. When selecting the table's primary key, consider:
  • the size of the partitions.
  • the order of the data within partitions.
  • the distribution of the partitions among the nodes of the cluster.

Table name conventions

The name of a table can be a string of alphanumeric characters and underscores, but it must begin with a letter. Consider the keyspace when naming a table:
  • To specify the keyspace that contains the table, put the keyspace name followed by a period before the table name: keyspace_name.table_name. This allows you to create a new table in a keyspace that is different from the one set for the current session (by the USE command, for example).
  • To create a table in the current keyspace, just specify the new table name.

Column characteristics

CQL supports several column types. When creating a table, assign a data type to each column. The table definition defines (non-collection) columns in a comma-delimited list of name and type pairs. The following example illustrates three data types: UUID, text, and timestamp:
CREATE TABLE cycling.cyclist_alt_stats ( id UUID PRIMARY KEY, lastname text, birthday timestamp, nationality text, weight text, height text );
CQL supports the following collection column types: map, set, and list. A collection column is defined using the collection type, followed by another type, such as int or text, in angle brackets. The collection column definition is included in the column list as described above. The following example illustrates each collection type, but is not designed for an actual query:
CREATE TABLE cycling.whimsey ( id UUID PRIMARY KEY, lastname text, cyclist_teams set<text>, events list<text>, teams map<int,text> );
Collection types cannot be nested. Collections can include frozen data types. For examples and usage, see Freezing collection types.
A column of type tuple holds a fixed-length set of typed positional fields. Use a tuple as an alternative to a user-defined type. A tuple can accommodate many fields (32768), although it is not a recommended best practice to use that many. A typical tuple holds 2 to 5 fields. Specify a tuple in a table definition, using angle brackets; within these, use a comma-delimited list to define each component type. Tuples can be nested. The following example illustrates a tuple type composed of a text field and a nested tuple of two float fields:
CREATE TABLE cycling.route (race_id int, race_name text, point_id int, lat_long tuple<text, tuple<float,float>>, PRIMARY KEY (race_id, point_id));
For more information, see Tuple type.

Create a user-defined type (UDTs) as a data type of several fields, using CREATE TYPE. It is best to create a UDT for use with multiple table definitions. The user-defined column type (UDT) requires the frozen keyword. The scope of a user-defined type is the keyspace in which you define it. Use dot notation to access a type from a keyspace outside its scope: keyspace name followed by a period followed the name of the type, for example: test.myType where test is the keyspace name and myType is the type name. The database accesses the type in the specified keyspace, but does not change the current keyspace; otherwise, if you do not specify a keyspace, the database accesses the type within the current keyspace. For examples and usage information, see Using a user-defined type.

A counter is a special column used to store a number that is changed in increments. A counter can only be used in a dedicated table that includes a column of counter data type. For more examples and usage information, see Using a counter.