CREATE TABLE

The full CREATE TABLE grammar - columns, nullability, defaults, codecs, reducers, keys, groups, indexes, mode, ordering, shards, partitions and storage profile.

CREATE TABLE declares a table's columns, its mode and its physical layout in one statement. For what the modes mean, read Tables first.

Shape of the statement

The statement is CREATE TABLE <name> ( <elements> ) <table clauses>. The elements inside the parentheses are comma-separated, in any order:

Element	Form
column	`<column> <type> [column options]`
primary key	`PRIMARY KEY (<column>, …)` — at most once
attribute group	`ATTRIBUTE GROUP <group> (<column>, …) [VALID BY (…)] [REVISION BY (…)]`
inline index	`INDEX <index> (<column>, …) USING <structure>`, where the structure is `MINMAX`, `BLOOM` or `POSTINGS`; optionally `SCOPE CURRENT`, `SCOPE HISTORY` or `SCOPE BOTH`, and `GRANULE <rows>`

The table clauses follow the closing parenthesis, in any order:

Clause	Form	Default
mode	`MODE APPEND`, `MODE UPSERT` or `MODE VERSIONED`	`UPSERT`
ordering	`VALID BY (<column>, …)`, `REVISION BY (<column>, …)` — each at most once	commit order
shards	`SHARD BY (<column>, …)`, optionally followed by `SHARDS <n>` or `SHARDS AUTO`	shard by the primary key; without `SHARDS`, the node's automatic shard count
partitions	`PARTITION BY NONE`, `HOUR(<column>)`, `DAY(<column>)`, `MONTH(<column>)` or `HASH(<column>, <buckets>)`	`NONE`
physical order	`ORDER BY (<column>, …)`	the primary key
storage profile	`STORAGE PROFILE ROW`, `COLUMNAR`, `HYBRID` or `DEFAULT`	`DEFAULT`, the node's profile

A trailing ; is optional. Names are unquoted ASCII identifiers and are stored in lower case; see Syntax.

Column options

After the type, a column takes each of these options at most once, in any order:

Option	Meaning
`NOT NULL` or `NULL`	nullability; a column is `NOT NULL` unless `NULL` is written
`DEFAULT <literal>`	the value used when a write omits the column; a literal only (number, string, `TRUE`, `FALSE` or `NULL`)
`ENCODING PLAIN` or `ENCODING LOW_CARDINALITY`	physical encoding; `LOW_CARDINALITY` is dictionary encoding
`COMPRESSION NONE`, `LZ4` or `ZSTD`	a one-stage compression shorthand
`CODEC(<stage>[(<int>, …)], …)`	an explicit codec chain, instead of `COMPRESSION`
`REDUCER REPLACE`, `SUM`, `MIN`, `MAX` or `RETAIN_FIRST`	how successive writes to the same key combine in this column

Codec stage names: NONE, Delta, DoubleDelta, GCD, T64, Gorilla, FPC, LZ4, LZ4HC, ZSTD A chain has at most four stages and each stage at most four integer parameters. COMPRESSION and CODEC are alternatives: a column takes one of them.

Examples

A VERSIONED table of balances, with two attribute groups, each ordered by its own coordinate:

CREATE TABLE balances (
  chain_id UINT16 NOT NULL,
  address STRING NOT NULL,
  token STRING NOT NULL,
  amount UINT256 NULL,
  updated_at DATETIME64(3, 'UTC') NULL,
  balance_index UINT128 NULL,
  balance_version UINT64 NULL,
  transfer_count UINT64 NULL,
  last_transfer_at DATETIME64(3, 'UTC') NULL,
  activity_index UINT128 NULL,
  PRIMARY KEY (chain_id, address, token),
  ATTRIBUTE GROUP holding (amount, updated_at, balance_index, balance_version)
    VALID BY (balance_index)
    REVISION BY (balance_version),
  ATTRIBUTE GROUP activity (transfer_count, last_transfer_at, activity_index)
    VALID BY (activity_index)
)
MODE VERSIONED
SHARD BY (address) SHARDS AUTO
ORDER BY (chain_id, address, token);

An APPEND table of transfers, partitioned by month of a timestamp, with an explicit physical order, a MINMAX index over history and a column-oriented layout:

CREATE TABLE transfers (
  chain_id UINT16 NOT NULL,
  token STRING NOT NULL,
  sender STRING NOT NULL,
  recipient STRING NOT NULL,
  amount UINT256 NOT NULL,
  transferred_at DATETIME64(3, 'UTC') NOT NULL,
  _index UINT128 NOT NULL,
  INDEX transfers_ts (transferred_at) USING MINMAX SCOPE HISTORY GRANULE 8192
)
MODE APPEND
SHARD BY (token) SHARDS AUTO
PARTITION BY MONTH(transferred_at)
ORDER BY (chain_id, token, _index)
STORAGE PROFILE COLUMNAR;

An UPSERT table of token prices with codecs, a dictionary-encoded column and a bloom index over current state:

CREATE TABLE token_metadata (
  chain_id UINT16 NOT NULL,
  token STRING NOT NULL,
  symbol STRING NULL ENCODING LOW_CARDINALITY,
  decimals UINT8 NULL REDUCER MAX,
  contract STRING NULL CODEC(ZSTD(3)),
  PRIMARY KEY (chain_id, token),
  INDEX token_contract (contract) USING BLOOM SCOPE CURRENT
)
MODE UPSERT
SHARD BY (token) SHARDS 4
STORAGE PROFILE ROW;

Attribute groups are covered on Attribute groups; ordering on Versioned tables.

Reducers

A reducer decides what a write does to a column of an existing key. The default is REPLACE: the new value overwrites the old one.

CREATE TABLE token_totals (
  chain_id UINT16 NOT NULL,
  token STRING NOT NULL,
  last_amount UINT256 NOT NULL REDUCER REPLACE,
  total_amount UINT256 NOT NULL REDUCER SUM,
  smallest UINT256 NOT NULL REDUCER MIN,
  largest UINT256 NOT NULL REDUCER MAX,
  first_amount UINT256 NOT NULL REDUCER RETAIN_FIRST,
  PRIMARY KEY (chain_id, token)
)
MODE VERSIONED;

A row written with 10 in every column, then with 7, 7, 7, 20 and 99, reads 7 (REPLACE), 17 (SUM), 7 (MIN), 20 (MAX) and 10 (RETAIN_FIRST). The raw written values are retained beside the reduced row on a VERSIONED table. REDUCER NATIVE(…) names a custom reducer.

Rules checked at creation

UPSERT and VERSIONED tables have exactly one PRIMARY KEY; an APPEND table carries no primary key, and its rows get an engine-generated identity.
Column names are unique within the table.
SHARDS <n> takes a positive count. SHARDS AUTO, or no SHARDS at all, takes the node's configured automatic shard count. Tablets split and merge as the data grows.
On a table with attribute groups, DEFAULT values belong to grouped columns.
Columns named by VALID BY, REVISION BY and PRIMARY KEY are non-null; VALID BY and REVISION BY columns are integers or timestamps.
A table has at most 1,024 columns, 32 primary-key or shard columns, 256 attribute groups and 256 inline indexes. Names are at most 128 bytes.
The statement is at most 64 KiB.
Table behaviour comes from MODE, the keys and STORAGE PROFILE; named policies (… POLICY <name>) attach placement, retention and compaction rules.

SHOW CREATE TABLE and DROP TABLE

SHOW CREATE TABLE <table> returns the stored definition as a statement. That statement can be run again to recreate the table:

SHOW CREATE TABLE balances;

DROP TABLE [IF EXISTS] <table> removes the table and its rows. Definitions that read or write a table are dropped before the table. See DROP.

ALTER TABLE for changes after creation.
Data types for the column types.
Indexes for index structures and scopes.
INSERT for writing rows.