Операции с таблицами

  • CREATE TABLE — Создание новой таблицы

  • ALTER TABLE — Изменение свойств таблицы

  • DROP TABLE — Удаление таблицы

  • TRUNCATE TABLE — Удаление всех строк из таблицы

  • SHOW TABLES — Вывод списка всех таблиц

  • SHOW COLUMNS — Вывод списка всех колонок

  • DESCRIBE TABLE — Вывод информации о таблице

Перейти к операциям DML:

  • SELECT — Извлечение данных из таблицы

  • INSERT — Добавление строк в таблицу

  • DELETE — Удаление строк из таблицы

  • UPDATE — Обновление строк в таблице

Создание новой таблицы

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

Создает новую таблицу с указанным именем и указанными колонками.

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

Создает новую таблицу с указанным именем на основе результата запроса SELECT.

Параметры

  • <table_name> — имя создаваемой таблицы

  • <table_schema> — схема создаваемой таблицы

  • <column_name> — имя колонки создаваемой таблицы

  • NOT NULL — данная колонка не принимает значения NULL

  • DEFAULT <default_expr> — константа или константное выражение по умолчанию

  • <select_expr> — выражение SELECT, результат выполнения которого будет записан в создаваемую таблицу

  • <table_param> — параметры создаваемой таблицы

    table_param ::= [<name> = <value>]

    Возможные значения:

    • snapshot_ttl = <duration> — глубина хранения снапшотов (версий таблицы).
      По умолчанию: 7 дней, но не более 1000 снапшотов.
      Например: '1 week', '2 days', '4 days 3 hours 5 minutes 30 seconds'

    • order_by = <column_name> — колонка для сортировки данных на уровне хранения.
      Подробнее: Управление партиционированием таблицы.

    • order_by = [<column1_name>, <column2_name>, ...] — список колонок для сортировки данных на уровне хранения.

    • partition_by = <partition_param> — параметр распределения данных.
      Подробнее: Управление партиционированием таблицы.

    • partition_by = [<partition_param1>, <partition_param2>, ...] — список параметров распределения данных.

Если указан модификатор OR REPLACE, то конечное действие эквивалентно удалению существующей таблицы и созданию новой с тем же именем.

Опциональный модификатор IF NOT EXISTS ограничивает запрос только теми случаями, в которых указанный объект еще не существует.

Модификаторы являются взаимоисключающими. Если указать их оба, это приведет к ошибке.

При отсутствии префикса схемы (<table_schema>) таблица создается в схеме по умолчанию. Схему по умолчанию можно задать для текущей сессии. Если схема по умолчанию явно не задана, то схемой по умолчанию является схема public.

Управление партиционированием таблицы

При создании таблицы можно настроить параметры ее партиционирования. Партиционирование позволяет определить структуру хранения для оптимизации работы с большими объемами данных.

Управление партиционированием осуществляется с помощью двух независимых параметров в блоке WITH:

  • partition_by — определяет, на какие подмножества данных распределяются записи.

  • order_by — определяет сортировку данных при хранении.

Эти параметры можно применять как по отдельности, так и совместно. Они задаются при создании таблицы и не могут быть изменены позже (для изменения структуры хранения требуется пересоздание таблицы и перезапись данных).

Таблицы хранятся в формате Iceberg. Гранулярность данных поддерживается за счет разделения данных таблицы на отдельные файлы parquet. Оба параметра влияют на гранулярность данных.

Параметры партиционирования используются для двух основных целей:

  1. Повышение производительности операций изменения данных (DELETE / UPDATE) — за счет селективности и исключения лишних файлов из обработки при фильтрации.

  2. Повышение производительности операций соединения и группировки (JOIN / GROUP BY) — за счет обработки данных меньшими блоками.

Параметр order_by

В параметре order_by можно задать колонку (или несколько колонок) для сортировки данных на уровне хранения.

Если параметр не указан, то данные в таблице хранятся с произвольной сортировкой.

Указание направления сортировки (например, order_by = id DESC) не поддерживается и вызовет синтаксическую ошибку.
Рекомендуется использовать параметр order_by при создании таблиц большого размера. Это позволяет ускорить операции изменения данных (DELETE / UPDATE).
Посмотреть пример

Создадим таблицу с заданным параметром для сортировки и укажем сортировку по колонке column2:

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

Удалим часть данных с фильтром по колонке сортировки:

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

В таком случае процесс удаления будет происходить максимально эффективно при больших размерах таблицы.

Параметр partition_by

В параметре partition_by указывается, какие подмножества данных будут храниться раздельно.

В качестве значений можно указать одну колонку, трансформацию или комбинацию нескольких выражений:

Тип выражения Описание

partition_by = column_name

Разделение по значению колонки

partition_by = bucket(N, column_name)

Трансформация бакетирования
(разделение данных на N фиксированных групп по хешу)

partition_by = truncate(N, column_name)

Трансформация строк
(разделение по первым N символам)

partition_by = year(column_name)

Трансформация дат
(также поддерживаются month(), day(), hour())

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

Комбинация нескольких выражений

Для эффективного выполнения соединения (JOIN) обе таблицы должны быть бакетированы по ключу соединения с одинаковым количеством бакетов.

Если количество бакетов отличается или бакетирована только одна из таблиц, вычислитель не сможет использовать бакетирование и будет выполнять соединение стандартными методами.

Задавайте partition_by для колонок, по которым выполняются GROUP BY или JOIN, а не для колонок, по которым идет обычная фильтрация (WHERE).
Партиционирование по колонке, которая не используется в фильтрах, группировках или соединениях, лишь замедляет запись и не дает никакой пользы.
Посмотреть примеры

Создадим таблицу логов, партиционированную по месяцам. Это позволит физически хранить записи разных месяцев в отдельных подмножествах файлов:

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

Создадим таблицу с бакетированием по полю user_id, чтобы распределить данные на 20 групп по хешу:

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

Благодаря бакетированию последующая агрегация данных с высокой кардинальностью ключа будет выполняться поэтапно (по бакетам), что предотвратит переполнение памяти у вычислителей:

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

Создадим две таблицы с одинаковым ключом бакетирования и размером бакетов:

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

В таком случае их соединение по ключу будет происходить частями и требовать меньше оперативной памяти в случае, если данные не помещаются в памяти целиком:

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;

Автоинкремент

При создании таблицы можно указать колонку, которая будет автоматически наполняться новыми значениями при добавлении в таблицу строк. Это удобно в случаях, когда необходим автоматический идентификатор строк при добавлении данных.

Чтобы это сделать, нужно указать в качестве типа колонки ключевое слово IDENTITY. Тогда при добавлении в таблицу новых строк в эту колонку будут автоматически вставляться целые числа начиная с 0.

  • Пример

    Создадим таблицу с двумя колонками и для первой колонки в качестве типа укажем IDENTITY:

    CREATE TABLE my_table (column1 IDENTITY, column2 VARCHAR);
    +--------+
| status |
+--------+
| CREATE |
+--------+

    Вставим два одинаковых значения в колонку column2, для колонки column1 не будем указывать значения для вставки:

    INSERT INTO my_table (column2) VALUES
    ('test_value'),
    ('test_value');
    +-------+
| count |
+-------+
| 2     |
+-------+

    Выведем все строки получившейся таблицы:

    SELECT * FROM my_table;
    +---------+------------+
| column1 | column2    |
+---------+------------+
| 0       | test_value |
+---------+------------+
| 1       | test_value |
+---------+------------+

    Мы видим, что в колонку column1 были автоматически вставлены целые числа начиная с 0.

Внутри механизма автоинкремента используется функция nextval_tngri.

Изменение свойств таблицы

Переименование таблицы

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

Переименовывает существующую таблицу в указанное имя <new_table_name>. Все атрибуты и права при этом сохраняются.

Если имя для переименования <new_table_name> занято существующей таблицей, то переименование не произойдет.

Имя для переименования <new_table_name> указывается без префикса схемы. Переименованная таблица останется в той же схеме. Задать новую схему при переименовании нельзя.

Если необходимо переименовать таблицу c изменением ее схемы, то рекомендуется создать новую таблицу в нужной схеме с полным копированием данных, а затем удалить старую:

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

Добавление колонки

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

Добавляет в таблицу колонку с указанным именем и указанным типом данных. Во все строки добавленной колонки записывается значение NULL.

Удаление колонки

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

Удаляет из таблицы колонку с указанным именем.

Удаление таблицы

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

Удаляет таблицу с указанным именем.

Опциональный модификатор IF EXISTS ограничивает запрос только теми случаями, в которых указанный объект существует.

Удаление всех строк из таблицы

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

Удаляет все строки из таблицы, но не удаляет саму таблицу (сохраняются колонки, типы данных колонок, привилегии на таблицу).

Опциональный модификатор IF EXISTS ограничивает запрос только теми случаями, в которых указанный объект существует.

Вывод списка всех таблиц

SHOW TABLES;

Выводит список всех существующих таблиц, доступных пользователю.

Формат вывода:

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

Вывод списка всех колонок

SHOW COLUMNS;

Выводит список всех колонок всех таблиц, доступных пользователю.

Формат вывода:

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

Вывод информации о таблице

DESC[RIBE] TABLE <table_name>;

Выводит информацию о таблице.

Формат вывода:

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

  • column_name — имя колонки

  • column_type — тип данных колонки

  • null — допустимы ли в колонке значения NULL

  • default — значение колонки по умолчанию

  • partition — выражение партиционирования для данной колонки или NULL

  • order — yes, если колонка входит в порядок сортировки таблицы, в противном случае — no