CREATE TABLE

YDB supports two types of tables:

• Row-oriented
• Column-oriented.

When you create a table, the table type is specified by the STORE parameter, with ROW creating a row-oriented table and COLUMN creating a column-oriented table. If the STORE parameter is omitted, a row-oriented table is created by default.

Row-oriented tables

The CREATE TABLE call creates a table with the specified data schema and key columns (PRIMARY KEY). It lets you define secondary indexes on the created table.

CREATE TABLE table_name (
    column1 type1,
    column2 type2 NOT NULL,
    ...
    columnN typeN,
    INDEX index1_name GLOBAL ON ( column ),
    INDEX index2_name GLOBAL ON ( column1, column2, ... ),
    PRIMARY KEY ( column, ... ),
    FAMILY column_family ( family_options, ... )
)
WITH ( key = value, ... )

Columns

For the key and non-key columns, you can only use primitive data types.

Without additional modifiers, a column gets an optional type and allows NULL values to be written. To create a non-optional type, use NOT NULL.
It is mandatory to specify the PRIMARY KEY with a non-empty list of columns. Those columns become part of the key in the listed order.

Example

CREATE TABLE my_table (
    a Uint64 NOT NULL,
    b Bool,
    c Float NOT NULL,
    PRIMARY KEY (b, a)
)

Secondary indexes

The INDEX construct is used to define a secondary index in a table:

CREATE TABLE table_name (
    ...
    INDEX <index_name> GLOBAL [SYNC|ASYNC] ON ( <index_columns> ) COVER ( <cover_columns> ),
    ...
)

Where:

• Index_name is the unique name of the index to be used to access data.
• SYNC/ASYNC indicates synchronous/asynchronous data writes to the index. If not specified, synchronous.
• Index_columns is a list of comma-separated names of columns in the created table to be used for a search in the index.
• Cover_columns is a list of comma-separated names of columns in the created table, which will be stored in the index in addition to the search columns, making it possible to fetch additional data without accessing the table for it.

Example

CREATE TABLE my_table (
    a Uint64,
    b Bool,
    c Utf8,
    d Date,
    INDEX idx_d GLOBAL ON (d),
    INDEX idx_ba GLOBAL ASYNC ON (b, a) COVER (c),
    PRIMARY KEY (a)
)

Additional parameters

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.

For a list of valid parameter names and values, see the YDB table description.

For example, this code will create a table with enabled automatic partitioning by partition size and the preferred size of each partition is 512 MB:

Listing 4

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

Column groups

Columns of the same table can be grouped to set the following parameters:

• DATA: A storage device type for the data in this column group. Acceptable values: ssd, rot.
• COMPRESSION: A data compression codec. Acceptable values: off, lz4.

By default, all columns are in the same group named default. If necessary, the parameters of this group can also be redefined.

In the example below, for the created table, the family_large group of columns is added and set for the series_info column, and the parameters for the default group, which is set by default for all other columns, are also redefined.

CREATE TABLE series_with_families (
    series_id Uint64,
    title Utf8,
    series_info Utf8 FAMILY family_large,
    release_date Uint64,
    PRIMARY KEY (series_id),
    FAMILY default (
        DATA = "ssd",
        COMPRESSION = "off"
    ),
    FAMILY family_large (
        DATA = "rot",
        COMPRESSION = "lz4"
    )
);

Сolumn-oriented tables

The CREATE TABLE statement creates a column-oriented table with the specified data schema and key columns (PRIMARY KEY).

CREATE TABLE table_name (
    column1 type1,
    column2 type2 NOT NULL,
    column2 type2,
    ...
    columnN typeN,
    PRIMARY KEY ( column, ... ),
    ...
)
PARTITION BY HASH(column1, column2, ...)
WITH (
    STORE = COLUMN,
    key = value,
    ...
)

Columns

Data types supported by column-oriented tables and constraints imposed on data types in primary keys or data columns are described in the supported data types section for column-oriented tables.

Make sure to add the PRIMARY KEY and PARTITION BY clauses with a non-empty list of columns.

If you omit modifiers, a column is assigned an optional type and can accept NULL values. To create a non-optional type, use NOT NULL.

Example

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

Additional parameters

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.

Supported parameters in column-oriented tables:

• AUTO_PARTITIONING_MIN_PARTITIONS_COUNT sets the minimum physical number of partitions used to store data (see Partitioning of a column-oriented table).

For example, the following code creates a column-oriented table with ten partitions:

CREATE TABLE my_table (
    id Uint64,
    title Utf8,
    PRIMARY KEY (id)
)
PARTITION BY HASH(id)
WITH (
    AUTO_PARTITIONING_MIN_PARTITIONS_COUNT = 10
);