5. CREATE TABLE#

5.1. Synopsis#

CREATE TABLE [ IF NOT EXISTS ] <table_name> (
  <column_name> <data_type> [ ( <column_size> ) ]
  [, ... ]
)
[ PARTITION BY <partition_strategy> ]
[ SHARD_SIZE [ = ] { <duration> | INFINITE } ]
[ WITH TAGS = '[' [ '<tag>' [, ... ] ] ']' ]
[ TTL [ = ] <duration> ]

<partition_strategy> ::= <column_name>
                       | HASH ( <column_name> ) PARTITIONS <partition_count>
                       | RANGE ( <column_name>, <range_width> )
                       | TIMESTAMP ( <column_name> )

<data_type> ::= BINARY | BLOB
              | FLOAT | DOUBLE
              | BIGINT | INT64
              | STRING
              | TIMESTAMP
              | SYMBOL [ ( <symbol_table_name> ) ]

Specify the options in the order that the synopsis shows.

5.2. Description#

CREATE TABLE creates a table with the specified column schema. To infer a schema from a query, use CREATE TABLE AS SELECT.

If the table exists, CREATE TABLE returns an error. If you specify IF NOT EXISTS, the statement succeeds and does not change the existing table.

QuasarDB supports time-series tables and general tables. The schema determines the table type:

  • Add one $timestamp TIMESTAMP column to create a time-series table. This column is the time index. It can occur at any position in the column list.

  • Do not add $timestamp to create a general table. A general table does not have a time index. This option is available in QuasarDB 3.14.3 and later.

The $timestamp column must have the TIMESTAMP type and must be unique. A table must also have at least one regular column. You cannot create a table that contains only $timestamp.

5.3. General tables#

A general table stores data that does not need a time index. You can use INSERT, SELECT, UPDATE, DELETE, ALTER TABLE, and DROP TABLE with general tables.

The following operations need a time index and do not support general tables:

  • IN RANGE

  • ASOF JOIN

  • COPY

  • IMPORT

  • TRIM TABLE

To use these operations, add $timestamp TIMESTAMP when you create the table. General tables always use an infinite shard size. If you specify SHARD_SIZE for a general table, QuasarDB does not use that value.

5.4. Column partitioning#

Column partitioning is planned for QuasarDB 4.0.0. These clauses describe the current development implementation, which reports version 3.15; they do not establish support in released 3.15 builds.

Use PARTITION BY after the column list and before SHARD_SIZE, WITH TAGS and TTL. The selected column must exist. Distinct and HASH keys must be ordinary INT64 or STRING columns; RANGE requires an ordinary INT64 column; TIMESTAMP requires a TIMESTAMP column. SYMBOL, DOUBLE and BLOB keys are unsupported. An omitted clause retains timestamp sharding on $timestamp. The explicit equivalent is PARTITION BY TIMESTAMP($timestamp); bare PARTITION BY $timestamp is invalid.

A column-partitioned table must include $timestamp TIMESTAMP and an ordinary key column. Only one key is supported. General tables, aggregated table layouts, TTL and composite keys are unsupported. The policy is fixed at creation; ALTER TABLE cannot change its strategy, range width or hash count.

See Column partitioning for strategy selection and Supported operations and limitations for the complete support matrix.

5.5. Parameters#

table_name

The name of the table to create. Use double quotation marks around an identifier that contains special characters.

column_name

The name of a column in the new table. Column names must be unique. Use double quotation marks around an identifier that contains special characters.

data_type

The column data type. BINARY is an alias for BLOB. FLOAT is an alias for DOUBLE. BIGINT is an alias for INT64.

column_size

An unsigned integer in parentheses after a data type. QuasarDB accepts this compatibility parameter but does not use it. It does not set a storage size or a value-length limit.

symbol_table_name

The symbol table for a SYMBOL column. If you omit the name, QuasarDB creates a symbol table and assigns an internal name.

PARTITION BY column_name

Creates one physical partition for each distinct INT64 or STRING key.

PARTITION BY HASH(column_name) PARTITIONS partition_count

Routes INT64 or STRING keys into at most partition_count slots. The count must be between 1 and 65,536 inclusive. Collisions share a physical partition without changing row values. Slots are populated on demand.

PARTITION BY RANGE(column_name, range_width)

Routes INT64 keys into intervals of positive INT64 range_width. Interval k is [k * range_width, (k + 1) * range_width); negative values use floor division. For width 1,000, key -1 belongs to interval -1.

PARTITION BY TIMESTAMP(column_name)

Routes rows using time intervals of the selected TIMESTAMP column. SHARD_SIZE controls their duration. If this is an ordinary column, $timestamp remains the independent row-time index.

SHARD_SIZE

The time span of one shard in a time-series table. Specify a duration or INFINITE. The default value is one day. The = character is optional. With TIMESTAMP partitioning, this is the duration of the selected key’s partition, from 1 to 9,223,372,036,854 milliseconds inclusive. INFINITE selects one partition for all valid key timestamps. Distinct, HASH and RANGE partitioning reject SHARD_SIZE.

WITH TAGS

A comma-separated list of tags to attach to the new table. Put each tag in single quotation marks. The = character is required.

TTL

The time-to-live value for the table data. Specify a duration. The = character is optional. For more information, see Time to live. TTL is unsupported when the partition key is an ordinary column.

5.6. Examples#

Create a general table:

CREATE TABLE example (id INT64, description STRING)

Create a time-series table. The time index does not have to be the first column:

CREATE TABLE example (
  value DOUBLE,
  $timestamp TIMESTAMP,
  status SYMBOL(status_symbols)
)

Create a time-series table with a one-hour shard size and a seven-day TTL:

CREATE TABLE example (
  $timestamp TIMESTAMP,
  value DOUBLE
) SHARD_SIZE = 1hour TTL = 7day

Create a table only if it does not exist:

CREATE TABLE IF NOT EXISTS example (id INT64)

Create a table and attach three tags:

CREATE TABLE example (id INT64) WITH TAGS = ['tag1', 'tag2', 'tag3']

Create distinct STRING partitions:

CREATE TABLE customer_events (
  $timestamp TIMESTAMP, customer_id STRING, value DOUBLE
) PARTITION BY customer_id;

Bound the number of partitions with HASH:

CREATE TABLE sensor_hash (
  $timestamp TIMESTAMP, sensor_id INT64, value DOUBLE
) PARTITION BY HASH(sensor_id) PARTITIONS 16;

Create integer-range partitions:

CREATE TABLE sensor_ranges (
  $timestamp TIMESTAMP, sensor_id INT64, value DOUBLE
) PARTITION BY RANGE(sensor_id, 1000);

Partition by event time while keeping an independent row timestamp:

CREATE TABLE timed_events (
  $timestamp TIMESTAMP, event_time TIMESTAMP, value DOUBLE
) PARTITION BY TIMESTAMP(event_time) SHARD_SIZE = 1hour;

5.7. Time to live#

TTL removes expired table data during storage compaction. It does not remove the data at an exact time. Expired data can remain until a compaction processes it. For information about compaction, see Storage Optimization Techniques.

Use TTL when delayed removal is acceptable. Use DELETE FROM when you must remove a specified time range before the next compaction.

Set TTL when you create a table:

CREATE TABLE example (
  $timestamp TIMESTAMP,
  value INT64
) TTL = 7day

TTL cleanup normally occurs when new writes cause storage compaction. If the table does not receive new data, expired data can remain. To start a full cluster compaction, run cluster_compact full in the QuasarDB shell. A full compaction can put a high load on the cluster and can take a long time.