CREATE TABLE

CREATE TABLE syntax

The invocation of CREATE TABLE creates a table with the specified data schema and primary key columns (PRIMARY KEY). It also allows defining secondary indexes on the created table.

CREATE [TEMP | TEMPORARY] 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, ... )

YDB supports two types of tables:

The table type is specified by the STORE parameter in the WITH clause, where ROW indicates a row-oriented table and COLUMN indicates a column-oriented table:

CREATE <table_name> (
  columns
  ...
)
WITH (
  STORE = COLUMN -- Default value ROW
)

By default, if the STORE parameter is not specified, a row-oriented table is created.

Examples of table creation

CREATE TABLE <table_name> (
  a Uint64,
  b Uint64,
  c Float,
  PRIMARY KEY (a, b)
);

For both key and non-key columns, only primitive data types are allowed.

Without additional modifiers, a column acquires an optional type and allows NULL values. To designate a non-optional type, use the NOT NULL constraint.

Specifying a PRIMARY KEY with a non-empty list of columns is mandatory. These columns become part of the key in the order they are listed.

Example of creating a row-oriented table using partitioning options:

CREATE TABLE <table_name> (
  a Uint64,
  b Uint64,
  c Float,
  PRIMARY KEY (a, b)
)
WITH (
  AUTO_PARTITIONING_BY_SIZE = ENABLED,
  AUTO_PARTITIONING_PARTITION_SIZE_MB = 512
);

Such code will create a row-oriented table with automatic partitioning by partition size (AUTO_PARTITIONING_BY_SIZE) enabled, and with the preferred size of each partition (AUTO_PARTITIONING_PARTITION_SIZE_MB) set to 512 megabytes. The full list of row-oriented table partitioning options can be found in the Partitioning row-oriented tables section.

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

Example of creating a column-oriented table with an option to specify the minimum physical number of partitions for storing data:

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,
  AUTO_PARTITIONING_MIN_PARTITIONS_COUNT = 10
);

This code will create a columnar table with 10 partitions. The full list of column-oriented table partitioning options can be found in the Partitioning of a column-oriented table section.

When creating row-oriented tables, it is possible to specify:

For column-oriented tables, only additional parameters can be specified during creation.

Previous
CREATE OBJECT TYPE SECRET
Next
INDEX