Additional parameters (WITH)

You can also specify a number of YDB-specific parameters for the table. When you create a table, those parameters are listed in the WITH clause:

CREATE TABLE table_name (...)
WITH (
    key1 = value1,
    key2 = value2,
    ...
)

Here, key is the name of the parameter and value is its value.

The list of allowable parameter names and their values is provided on the table description page YDB.

For example, such a query will create a string table with automatic partitioning enabled based on partition size and a preferred size of each partition being 512 megabytes:

CREATE TABLE my_table (
    id Uint64,
    title Utf8,
    PRIMARY KEY (id)
)
WITH (
    AUTO_PARTITIONING_BY_SIZE = ENABLED,
    AUTO_PARTITIONING_PARTITION_SIZE_MB = 512
);

A colum-oriented table is created by specifying the parameter STORE = COLUMN in the WITH clause:

 CREATE TABLE table_name (
    a Uint64 NOT NULL,
    b Timestamp NOT NULL,
    c Float,
    PRIMARY KEY (a, b)
  )
  PARTITION BY HASH(b)
  WITH (
    STORE = COLUMN
  );

The properties and capabilities of columnar tables are described in the article Table, and the specifics of their creation through YQL are described on the page CREATE TABLE.

Time to Live (TTL)

The TTL (Time to Live) — the lifespan of a row — can be specified in the WITH clause for row-based and columnar tables. TTL automatically deletes rows or evicts them to external storage when the specified number of seconds has passed since the time recorded in the TTL column. TTL can be specified when creating row-based and columnar tables or added later using the ALTER TABLE command only for row-based tables.

The short form of the TTL value for specifying the time to delete rows:

Interval("<literal>") ON column [AS <unit>]

The general form of the TTL value:

Interval("<literal1>") action1, ..., Interval("<literalN>") actionN ON column [AS <unit>]

• action — the action performed when the TTL expression triggers. Allowed values:
  • DELETE — delete the row;
  • TO EXTERNAL DATA SOURCE <path> — evict the row to external storage specified by the external data source at the path <path>.
• <unit> — the unit of measurement, specified only for columns with a numeric type:
  • SECONDS;
  • MILLISECONDS;
  • MICROSECONDS;
  • NANOSECONDS.

Example of creating a row-oriented and column-oriented tables with TTL:

CREATE TABLE my_table (
    id Uint64,
    title Utf8,
    expire_at Timestamp,
    PRIMARY KEY (id)
)
WITH (
    TTL = Interval("PT0S") ON expire_at
);

CREATE TABLE table_name (
    a Uint64 NOT NULL,
    b Timestamp NOT NULL,
    c Float,
    PRIMARY KEY (a, b)
)
PARTITION BY HASH(b)
WITH (
    STORE = COLUMN,
    TTL = Interval("PT0S") ON b
);

Example of creating a column-oriented table with eviction to external storage:

CREATE TABLE table_name (
    a Uint64 NOT NULL,
    b Timestamp NOT NULL,
    c Float,
    PRIMARY KEY (a, b)
)
PARTITION BY HASH(b)
WITH (
    STORE = COLUMN,
    TTL =
        Interval("PT1D") TO EXTERNAL DATA SOURCE `/Root/s3`,
        Interval("P2D") DELETE
    ON b
);