Operations with tables

CREATE TABLE — Create a new table
ALTER TABLE — Change table attributes
DROP TABLE — Delete a table
TRUNCATE TABLE — Remove all rows from a table
SHOW TABLES — Output a list of all tables
SHOW COLUMNS — Output a list of all columns in all tables
DESCRIBE TABLE — Display information about the table

Create a new table

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] [<table_schema>.]<table_name>
    (<column_name> <column_type>
        [NOT NULL] [DEFAULT <default_expr>]
    ...
    )
    [WITH (<table_param>, ... )]

Creates a new table with the specified name and specified columns.

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] [<table_schema>.]<table_name> AS
    <select_expr>
    [WITH (<table_param>, ... )]

Creates a new table with the specified name based on the result of a SELECT query.

Parameters

<table_name> — name of the table to be created

<table_schema> — schema of the table to be created

<column_name> — column name of the table to be created

<column_type> — data type of the column of the table to be created or the IDENTITY keyword (for autoincrement).

NOT NULL — this column does not accept the value NULL

DEFAULT <default_expr> — default constant or constant expression

<select_expr> — a SELECT expression whose result will be written to the table being created

<table_param> — parameters of the table to be created
table_param ::= [<name> = <value>]
Possible values:
- snapshot_ttl = <duration> — depth of snapshot (table version) storage.
  Default: 7 days, but no more than 1000 snapshots.
  For example: '1 week', '2 days', '4 days 3 hours 5 minutes 30 seconds'.
- order_by = <column_name> — column to sort data at the storage level.
  Read more: Managing table partitioning.
- order_by = [<column1_name>, <column2_name>, ...] — columns to sort data at the storage level.

If the OR REPLACE modifier is specified, the final action is equivalent to deleting the existing table and creating a new one with the same name.

The optional IF NOT EXISTS modifier restricts the query to only those cases in which the specified object does not already exist.

The modifiers are mutually exclusive. Specifying them both will result in an error.

If there is no schema prefix (<table_schema>), the table is created in the default schema. The default schema can be set for the current session. If the default schema is not explicitly set, the public schema is used as the default.

Managing table partitioning

When creating a table, you can configure its partitioning settings. Partitioning allows you to define a storage structure to optimise the handling of large volumes of data.

Partitioning is managed using two independent parameters in the WITH block:

partition_by — determines the subsets of data across which records are distributed.
order_by — determines the sort order of the data during storage.

These parameters can be applied either individually or in combination. They are specified when the table is created and cannot be changed subsequently (changing the storage structure requires recreating the table and rewriting the data).

Tables are stored in Iceberg format. Data granularity is achieved by splitting the table’s data into separate parquet files. Both parameters affect data granularity.

Partitioning parameters are used for two main purposes:

Improving the performance of data modification operations (DELETE / UPDATE) — through selectivity and by excluding unnecessary files from processing during filtering.
Improving the performance of join and group operations (JOIN / GROUP BY) — by processing data in smaller blocks.

The `order_by` parameter

The order_by parameter allows you to specify a column (or several columns) for sorting data at the storage level.

If this parameter is not specified, the data in the table is stored in random order.

Specifying the sort order (e.g. order_by = id DESC) is not supported and will result in a syntax error.

It is recommended to use the order_by parameter when creating large tables. This helps to speed up data modification operations (DELETE / UPDATE).

See example

Let’s create a table with a specified sort parameter and sort by the column2 column:

CREATE TABLE my_table (column1 INT, column2 DATE)
    WITH (order_by = column2);

Let’s delete some data using a filter based on the sort column:

DELETE FROM my_table
    WHERE column2  > '2026-01-01';

In this case, the deletion process will be as efficient as possible for large tables.

The `partition_by` parameter

The partition_by parameter specifies which subsets of data will be stored separately.

Values can be a single column, a transformation, or a combination of several expressions:

Expression type Description

Expression type	Description
`partition_by = column_name`	Partitioning by column value
`partition_by = bucket(N, column_name)`	Bucketing transformation (dividing data into `N` fixed groups by hash)
`partition_by = truncate(N, column_name)`	String transformation (partitioning by the first `N` characters)
`partition_by = year(column_name)`	Date transformation (`month()`, `day()`, `hour()` are also supported)
`partition_by = [bucket(4, column1_name), column2_name]`	Combination of multiple expressions

partition_by = column_name

Partitioning by column value

partition_by = bucket(N, column_name)

Bucketing transformation
(dividing data into N fixed groups by hash)

partition_by = truncate(N, column_name)

String transformation
(partitioning by the first N characters)

partition_by = year(column_name)

Date transformation
(month(), day(), hour() are also supported)

partition_by = [bucket(4, column1_name), column2_name]

Combination of multiple expressions

For a join to be executed efficiently, both tables must be bucketed by the join key with the same number of buckets.

If the number of buckets differs, or if only one of the tables is bucketised, the evaluator will be unable to utilise bucketisation and will perform the join using standard methods.

Specify partition_by for columns used in GROUP BY or JOIN operations, rather than for columns used in standard filtering (WHERE).

Partitioning by a column that is not used in filters, groupings or joins merely slows down the write operation and offers no benefit.

See examples

Let’s create a log table partitioned by month. This will allow records from different months to be physically stored in separate subsets of files:

CREATE TABLE logs (
    log_id UUID,
    event_time TIMESTAMP,
    message VARCHAR
)
    WITH (partition_by = month(event_time));

Let’s create a table with bucketing by the user_id field to distribute the data across 20 groups based on a hash:

CREATE TABLE events (user_id BIGINT, ts TIMESTAMP, payload VARCHAR)
    WITH (partition_by = bucket(20, user_id));

Thanks to bucketing, subsequent aggregation of data with high key cardinality will be performed in stages (by bucket), which will prevent memory overflow on the compute nodes:

SELECT user_id, count(*) FROM events GROUP BY user_id;

Let’s create two tables with the same bucketing key and bucket size:

CREATE TABLE orders (user_id BIGINT, total DECIMAL)
    WITH (partition_by = bucket(20, user_id));

CREATE TABLE sessions (user_id BIGINT, started TIMESTAMP)
    WITH (partition_by = bucket(20, user_id));

In this case, joining them by key will take place in batches and require less RAM if the data does not fit entirely into memory:

SELECT o.user_id,
       sum(total),
       max(started) - min(started)
FROM orders o
JOIN sessions s ON o.user_id = s.user_id
GROUP BY o.user_id;

Autoincrement

When creating a table, you can specify a column that will be automatically filled with new values when rows are added to the table. This is convenient in cases when you need an automatic row identifier when adding data.

To do this, you need to specify the IDENTITY keyword as the column type. Then when new rows are added to the table, integers starting from 0 will be automatically inserted into this column.

Example

Create a table with two columns and for the first column we specify IDENTITY as the type:

CREATE TABLE my_table (column1 IDENTITY, column2 VARCHAR);

+--------+
| status |
+--------+
| CREATE |
+--------+

Let’s insert two identical values into column column2, for column column1 we will not specify values to insert:

INSERT INTO my_table (column2) VALUES
    ('test_value'),
    ('test_value');

+-------+
| count |
+-------+
| 2     |
+-------+

Let’s output all rows of the resulting table:

SELECT * FROM my_table;

+---------+------------+
| column1 | column2    |
+---------+------------+
| 0       | test_value |
+---------+------------+
| 1       | test_value |
+---------+------------+

We can see that integers starting with 0 have been automatically inserted into column column1.

Inside the autoincrement mechanism, the function nextval_tngri is used.

Change table attributes

Rename table

ALTER TABLE [<table_schema>.]<old_table_name>
    RENAME TO <new_table_name>;

Renames an existing table to the specified name <new_table_name>. All attributes and permissions are retained.

If the name <new_table_name> is taken by an existing table, the rename will not occur.

The new name <new_table_name> is specified without the schema prefix. The renamed table will remain under the same schema. You cannot specify a new schema when renaming.

If you need to rename a table by changing its schema, it is recommended to create a new table in the desired schema with a full copy of the data, and then delete the old one:

CREATE TABLE new_schema.table_name AS SELECT * FROM old_schema.table_name;
DROP TABLE old_schema.table_name;

Adding a column

ALTER TABLE [<table_schema>.]<table_name>
    ADD COLUMN <column_name> <column_type>;

Adds a column with the specified name and the specified data type to the table. The value NULL is written to all rows of the added column.

Deleting a column

ALTER TABLE [<table_schema>.]<table_name>
    DROP COLUMN <column_name>;

Deletes the column with the specified name from the table.

Delete a table

DROP TABLE [IF EXISTS] [<table_schema>.]<table_name>;

Deletes the table with the specified name.

The optional IF EXISTS modifier restricts the query to only those cases in which the specified object exists.

Remove all rows from a table

TRUNCATE TABLE [IF EXISTS] [<table_schema>.]<table_name>;

Deletes all rows from the table, but does not delete the table itself (columns, column data types, and table privileges stay intact).

The optional IF EXISTS modifier restricts the query to only those cases in which the specified object exists.

Output a list of all tables

SHOW TABLES;

Outputs a list of all tables available to the user.

Output format:

+-------------+------------+
| schema_name | table_name |
+-------------+------------+
| ...         | ...        |
+-------------+------------+

Output a list of all columns in all tables

SHOW COLUMNS;

Outputs a list of all columns in all tables accessible to the user.

Output format:

+-------------+------------+-------------+
| schema_name | table_name | column_name |
+-------------+------------+-------------+
| ...         | ...        | ...         |
+-------------+------------+-------------+

Display information about the table

DESC[RIBE] TABLE <table_name>;

Displays information about the table.

Output format:

+-------------+-------------+------+---------+-----------+-------+
| column_name | column_type | null | default | partition | order |
+-------------+-------------+------+---------+-----------+-------+
| ...         | ...         | ...  | ...     | ...       | ...   |
+-------------+-------------+------+---------+-----------+-------+

column_name — column name
column_type — data type of the column
null — whether NULL values are allowed in the column
default — default value of the column
partition — partition expression for the given column or NULL
order — yes if the column is in the table’s sort order, otherwise — no

Operations with tables

Create a new table

Parameters

Managing table partitioning

The order_by parameter

The partition_by parameter

Autoincrement

Change table attributes

Rename table

Adding a column

Deleting a column

Delete a table

Remove all rows from a table

Output a list of all tables

Output a list of all columns in all tables

Display information about the table

The `order_by` parameter

The `partition_by` parameter