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:
- Row-oriented tables.
- Column-oriented 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.