CREATE TABLE

To create a table definition, use a CREATE TABLE statement. Its form is:

CREATE TABLE [IF NOT EXISTS] table-name [COMMENT "comment string"] ( 
    field-definition, field-definition-2 ..., 
    PRIMARY KEY (field-name, field-name-2...),
) [USING TTL ttl] 

where:

  • IF NOT EXISTS is optional, and it causes table creation to be silently skipped if a table of the given name already exists in the store, and the table's definition exactly matches the provided definition. No error is returned as a result of the statement execution.

    If this statement phrase is not specified, then an attempt to duplicate a table name in the store results in a failed table creation.

  • table-name is the name of the table. This field is required. If you are creating a sub-table, then use dot notation. For example, a table might be named Users. You might then define a sub-table named Users.MailingAddress.

  • COMMENT is optional. You can use this to provide a brief description of the table. The comment will not be interpreted but it is included in the table's metadata.

  • field-definition is a comma-separated list of fields. There are one or more field definitions for every table. Field definitions are described next in this section.

  • PRIMARY KEY identifies at least one field in the table as the primary key. A primary key definition is required for every table. For information on primary keys, see Primary Keys.

    Note:

    If the primary key field is an INTEGER data type, you can apply a serialized size constraint to it. See Integer Serialized Constraints for details.

    To define a shard key (optional), use the SHARD keyword in the primary key statement. For information on shard keys, see Shard Keys.

    For example:

    PRIMARY KEY (SHARD(id), lastName)
  • USING TTL is optional. It defines a default time-to-live value for the table's rows. See USING TTL for information on this statement.

Field Definitions

When defining a table, field definitions take the form:

field-name type [constraints] [COMMENT "comment-string"] 

where:

  • field-name is the name of the field. For example: id or familiarName. Every field must have a name.

  • type describes the field's data type. This can be a simple type such as INTEGER or STRING, or it can be a complex type such a RECORD. The list of allowable types is described in the next section.

  • constraints describes any limits placed on the data contained in the field. That is, allowable ranges or default values. This information is optional. See Field Constraints for more information.

  • COMMENT is optional. You can use this to provide a brief description of the field. The comment will not be interpreted but it is included in the table's metadata.

Supported Data Types

The following data types are supported for table fields:

  • ARRAY

    An array of data. All elements of the array must be of the same data type, and this type must be declared when you define the array field. For example, to define an array of strings:

    myArray ARRAY(STRING)
  • BINARY

    Binary data.

  • BINARY(length)

    Fixed-length binary field of size length (in bytes).

  • BOOLEAN

    A boolean data type.

  • DOUBLE

    A double.

  • ENUM

    An enumerated list. The field definition must provide the list of allowable enumerated values. For example:

    fruitName ENUM(apple,pear,orange)
  • FLOAT

    A float.

  • INTEGER

    An integer.

  • JSON

    A JSON-formatted string.

  • LONG

    A long.

  • MAP

    A data map. All map keys are strings, but when defining these fields you must define the data type of the data portion of the map. For example, if your keys map to integer values, then you define the field like this:

    myMap MAP(INTEGER)
  • Number

    A numeric type capable of handling any type of number of any value or precision.

  • RECORD

    An embedded record. This field definition must define all the fields contained in the embedded record. All of the same syntax rules apply as are used for defining an ordinary table field. For example, a simple embedded record might be defined as:

    myEmbeddedRecord RECORD(firstField STRING, secondField INTEGER)

    Data constraints, default values, and so forth can also be used with the embedded record's field definitions.

  • STRING

    A string.

  • TIMESTAMP(<precision>)

    Represents a point in time as a date and, optionally, a time value.

    Timestamp values have a precision (0 - 9) which represents the fractional seconds to be held by the timestamp. A value of 0 means that no fractional seconds are stored, 3 means that the timestamp stores milliseconds, and 9 means a precision of nanoseconds. When declaring a timestamp field, the precision is required.

Field Constraints

Field constraints are used to define information about the field, such as whether the field can be NULL, or what a row's default value should be. Not all data types support constraints, and individual data types do not support all possible constraints.

Integer Serialized Constraints

You can put a serialized size constraint on an INTEGER data type, provided the INTEGER is used for a primary key field. Doing this can reduce the size the keys in your store.

To do this, use (n) after the primary key field name, where n is the number of bytes allowed for the integer. The meaningful range for n is 1 - 4. For example:

create table myId (id integer, primary key(id(3)))

The number of bytes allowed defines how large the integer can be. The range is from negative to positive.

Number of Bytes Allowed Integer Values
1 -63 to 63
2 -8191 to 8191
3 -1048575 to 1048575
4 -134217727 to 134217727
5 Any integer value

COMMENT

All data types can accept a COMMENT as part of their constraint. COMMENT strings are not parsed, but do become part of the table's metadata. For example:

myRec RECORD(a STRING, b INTEGER) COMMENT "Comment string"

DEFAULT

All fields can accept a DEFAULT constraint, except for ARRAY, BINARY, MAP, and RECORD. The value specified by DEFAULT is used in the event that the field data is not specified when the table is written to the store.

For example:

id INTEGER DEFAULT -1,
description STRING DEFAULT "NONE",
size ENUM(small,medium,large) DEFAULT medium,
inStock BOOLEAN DEFAULT FALSE

NOT NULL

NOT NULL indicates that the field cannot be NULL. This constraint requires that you also specify a DEFAULT value. Order is unimportant for these constraints. For example:

id INTEGER NOT NULL DEFAULT -1,
description STRING DEFAULT "NONE" NOT NULL

USING TTL

USING TTL is an optional statement that defines a default time-to-live value for a table's rows. See Using Time to Live for information on TTL.

If specified, this statement must provide a ttl value, which is an integer greater than or equal to 0, followed by a space, followed by time unit designation which is either hours or days. For example:

USING TTL 5 days

If 0 is specified, then either days or hours can be used. A value of 0 causes table rows to have no expiration time. Note that 0 is the default if a default TTL has never been applied to a table schema. However, if you previously applied a default TTL to a table schema, and then want to turn it off, use 0 days or 0 hours.

USING TTL 0 days

Be aware that if you altering an existing table, you can not both add/drop a field and alter the default TTL value for the field using the same ALTER TABLE statement. These two operations must be performed using separate statements.

Table Creation Examples

The following are provided to illustrate the concepts described above.

CREATE TABLE users 
COMMENT "This comment applies to the table itself" (
  id INTEGER,
  firstName STRING,
  lastName STRING,
  age INTEGER,
  PRIMARY KEY (id),
) 
CREATE TABLE temporary
COMMENT "These rows expire after 3 days" (
  sku STRING,
  id STRING,
  price FLOAT,
  count INTEGER,
  PRIMARY KEY (sku),
) USING TTL 3 days
CREATE TABLE usersNoId (
  firstName STRING,
  lastName STRING COMMENT "This comment applies to this field only",
  age INTEGER,
  ssn STRING NOT NULL DEFAULT "xxx-yy-zzzz",
  PRIMARY KEY (SHARD(lastName), firstName)
) 
CREATE TABLE users.address (
  streetNumber INTEGER,
  streetName STRING,  // this comment is ignored by the DDL parser
  city STRING,
  /* this comment is ignored */
  zip INTEGER,
  addrType ENUM (home, work, other),
  PRIMARY KEY (addrType)
) 
CREATE TABLE complex 
COMMENT "this comment goes into the table metadata" (
  id INTEGER, 
  PRIMARY KEY (id), # this comment is just syntax
  nestedMap MAP(RECORD( m MAP(FLOAT), a ARRAY(RECORD(age INTEGER)))),
  address RECORD (street INTEGER, streetName STRING, city STRING, \
                  zip INTEGER COMMENT "zip comment"),
  friends MAP (STRING),
  floatArray ARRAY (FLOAT),
  aFixedBinary BINARY(5),
  days ENUM(mon, tue, wed, thur, fri, sat, sun) NOT NULL DEFAULT tue
) 
CREATE TABLE myJSON (
    recordID INTEGER,
    jsonData JSON,
    PRIMARY KEY (recordID)
)