Tables

The three table modes, APPEND, UPSERT and VERSIONED, and how keys, shards, partitions, order and storage profile shape a table.

A table is a typed schema plus a mode. The mode decides which writes the table accepts and which state it keeps. Every other clause of CREATE TABLE shapes where rows live and how they are stored; none of them changes the mode.

The three modes

Mode	Key	Accepted writes	State kept
`APPEND`	none: the engine assigns each row its identity	`INSERT` only	every row
`UPSERT` (the default)	exactly one `PRIMARY KEY`	`INSERT`, `UPSERT`, `REPLACE`, `UPDATE`, `DELETE`	the latest row per key
`VERSIONED`	exactly one `PRIMARY KEY`	the same as `UPSERT`	the current row per key and every accepted version

MODE is optional; a table without it is an UPSERT table. A keyed mode has one PRIMARY KEY, and an APPEND table carries no primary key.

APPEND: a log of events

An append table keeps every row it accepts. Use it for events, readings and other facts that are never corrected in place.

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 append table takes inserts; corrections are derived downstream, in a keyed table maintained by a transformation.

UPSERT: the latest row per key

An upsert table holds one current row per primary key. A later write to the same key changes that row; the previous value is not retained.

CREATE TABLE token_metadata (
  chain_id UINT16 NOT NULL,
  token STRING NOT NULL,
  symbol STRING NULL ENCODING LOW_CARDINALITY,
  decimals UINT8 NULL,
  PRIMARY KEY (chain_id, token)
)
MODE UPSERT
SHARD BY (token) SHARDS 4;

VERSIONED: current state plus history

A versioned table also keeps one current row per key, and it retains every accepted version beside it. VALID BY and REVISION BY decide which version is current when writes arrive out of order. See Versioned tables.

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);

Columns and nulls

A column is NOT NULL unless NULL is written after its type. A DEFAULT takes one literal. A write that omits a column uses its default, or NULL when the column is nullable. Primary-key columns and the columns named by VALID BY and REVISION BY are non-null. The types are listed in Data types.

Write kinds

Statement	Meaning
`INSERT`	creates a row; on a keyed table it reports an existing key
`UPSERT`	changes the named columns of an existing row, or creates the row
`REPLACE`	writes a complete row for the key
`UPDATE`	changes the named columns of existing rows
`DELETE`	removes rows by key or by condition

Statement grammar is on INSERT and UPDATE and DELETE.

A keyed table can split its columns into attribute groups: each group is written and ordered on its own, so several writers can each own part of a row.

Keys, shards, partitions and order

Four clauses are independent of each other:

Clause	Decides	Default
`PRIMARY KEY (…)`	row identity on a keyed table	none (required on UPSERT and VERSIONED)
`SHARD BY (…) SHARDS n`	which shard owns a row, and how many shards the table starts with	the primary key, and the node's automatic shard count
`PARTITION BY …`	how rows of a shard are bucketed for pruning: `NONE`, a UTC time bucket `HOUR(col)`, `DAY(col)` or `MONTH(col)`, or `HASH(col, buckets)`	`NONE`
`ORDER BY (…)`	the physical order of rows inside stored parts	the primary key

Each shard is a tablet. Tablets are replicated across nodes and split and merge as the table grows. See ALTER TABLE for schema changes.

Storage profile

STORAGE PROFILE ROW | COLUMNAR | HYBRID | DEFAULT selects the physical layout. ROW stores keyed rows for point access; COLUMNAR and HYBRID write column blocks that favour scans. Append tables always store their parts as column blocks. Per-column ENCODING, COMPRESSION and CODEC choose the encoding of each column inside those blocks. See CREATE TABLE and Storage.

Inspecting a table

SHOW CREATE TABLE returns the stored definition as a statement that can be run again:

SHOW CREATE TABLE balances;

The other inspection statements are on Introspection.

On this page