Home > Documentation > CREATE TABLE Statement

CREATE TABLE Statement

Syntax

CREATE [MEMORY | DISK] TABLE table_identifier (
  column_identifier column_type PRIMARY KEY [USING {HASH | BTREE}]
  [, column_identifier column_type[, ...]]
);

Or

CREATE [MEMORY | DISK] TABLE table_identifier (
  column_identifier column_type[, ...],
  PRIMARY KEY(column_identifier[, ...]) [USING {HASH | BTREE}]
);

Substitutable Fields

table_identifier:

A unique identifier (name) for the table.

column_identifier:

A unique identifier (name) for a column in the table. Since each row in the table corresponds to a stored tuple, a column corresponds to a tuple field.

column_type:

    int
  | double
  | bool
  | timestamp
  | string(length)

The data type contained in a specified column. StreamSQL Tables support the following data types: int, string, double, bool, and timestamp. With a string type, the maximum length must be specified.

Discussion

Tables are defined in a CREATE TABLE statement.

Each Table can be declared to reside in-memory or on-disk. In the case of memory-resident Tables, any data stored in the table at runtime is not saved when the server shuts down. In the case of disk-based Tables, the data stored in the tables may persist between server sessions, provided you enabled this behavior through configuration.

Configuration file entries specify where a disk-based Table is stored and how transactional semantics should be applied to the table.

Each table must have a primary index (key). Secondary indices are optional and are defined in CREATE INDEX statements.

How keys are indexed for table read operations may also be specified.

• Unordered, no ranges (hash): Keys are unsorted, and they are evenly distributed (hashed) across the index. A hash index is used for accessing keys based on equality.
• Ordered, with ranges (btree): Keys are sorted. A btree index is used when output ordering and range queries are desired. Note that the sort depends on the order of the fields in the index keys.

There are two statements that may be used to create a table. The first syntax is used when the table's primary key (or index) is composed of only one field. In this case, identify the primary key field as part of the field's declaration.

The second syntax may be used when the table has a composite primary key. In this case, define the primary key in a separate clause. This syntax may also be used if the primary key is based on a single table column.

Optionally, whether the table resides in memory (the default) or on disk, and whether a BTREE (the default) or HASH is used for ordering key/index values, may be specified.

Secondary indices are defined in separate CREATE INDEX statements.

StreamSQL tables are analogous to relational database tables and applications will use stream queries to manipulate the content of these tables. Stream queries issued against a table alter the contents of the table and may return a result set of tuples that documents the changes made to the table. List, in the stream query's optional RETURNING clause, the tuple fields and table columns included in the result set.

Stream queries that do not include a RETURNING clause modify the content of StreamSQL tables but additional query operations will be needed to confirm the changes.

A SELECT statement against a table is equivalent to a table read operation. A table read operation also returns a result set of tuples but does not alter the content of the table.

The tuples returned by a table read operation or stream query against a table must be "captured" into a stream by either defining the stream query as part of a stream definition or by explicitly applying the query's result set to an existing stream.