Конференция Arenadata
Новое время — новый Greenplum
Мы приглашаем вас принять участие в конференции, посвященной будущему Open-Source Greenplum 19 сентября в 18:00:00 UTC +3. Встреча будет проходить в гибридном формате — и офлайн, и онлайн. Онлайн-трансляция будет доступна для всех желающих.
Внезапное закрытие Greenplum его владельцем — компанией Broadcom - стало неприятным сюрпризом для всех, кто использует или планирует начать использовать решения на базе этой технологии. Многие ожидают выхода стабильной версии Greenplum 7 и надеются на её дальнейшее активное развитие.
Arenadata не могла допустить, чтобы разрабатываемый годами Open-Source проект Greenplum прекратил своё существование, поэтому 19 сентября мы представим наш ответ на данное решение Broadcom, а участники сообщества получат исчерпывающие разъяснения на все вопросы о дальнейшей судьбе этой технологии.

На конференции вас ждёт обсуждение следующих тем:

  • План возрождения Greenplum;
  • Дорожная карта;
  • Экспертное обсуждение и консультации.
Осталось до события

Основной синтаксис

Елена Дворядкина

Основные операции с таблицами выполняются с помощью SQL-запросов, каждый из которых имеет свой синтаксис и особенности использования. В этой статье приведен базовый синтаксис и примеры запросов для создания, изменения и удаления таблиц, а также для просмотра информации о таблицах, чтения и записи данных.

Создание таблиц MergeTree

Обзор синтаксиса

Чтобы создать таблицу на сервере ADQM, необходимо использовать запрос CREATE TABLE с указанием движка таблицы с помощью выражения ENGINE. Полную информацию о запросе CREATE TABLE можно получить в документации ClickHouse.

Ниже приведен базовый синтаксис запроса для создания таблицы MergeTree — основного типа таблиц для работы с большими данными в ADQM.

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
(   <column_name1> [<data_type1>] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS <expr1>] [CODEC(<compression_codec1>)] [TTL <expr1>],
    <column_name2> [<data_type2>] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS <expr2>] [CODEC(<compression_codec2>)] [TTL <expr2>],
    ...
    CONSTRAINT <constraint_name_1> CHECK <boolean_expr_1>,
    CONSTRAINT <constraint_name_2> CHECK <boolean_expr_2>,
    ...
    INDEX <index_name1> <expr1> TYPE <index_type1> GRANULARITY <granularity_value1>,
    INDEX <index_name2> <expr2> TYPE <index_type2> GRANULARITY <granularity_value2>)
ENGINE = MergeTree()
ORDER BY <expr>
[PARTITION BY <expr>]
[PRIMARY KEY <expr>]
[SAMPLE BY <expr>]
[TTL <expr>
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
    [WHERE <conditions>]
    [GROUP BY <key_expr> [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
[SETTINGS name=value, ...];
Параметры запроса

 
Один из основных вариантов синтаксиса запроса CREATE TABLE — описание столбцов, ограничений и индексов в качестве параметров запроса.

Описания столбцов

Столбцы таблицы MergeTree описываются следующим образом:

<column_name> [<data_type>] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS <expr>] [CODEC(<compression_codec>)] [TTL <expr>]

Описание столбца содержит:

Ограничения (constraints)

Ограничения на значения в столбцах таблицы описываются следующим образом:

CONSTRAINT <constraint_name> CHECK <boolean_expr>

Описание ограничения содержит:

  • имя ограничения;

  • логическое выражение, которому должны удовлетворять данные для успешного выполнения запросов INSERT.

Индексы пропуска данных

Для таблиц семейства MergeTree можно задавать индексы пропуска данных, которые агрегируют данные в блоки по заданным выражениям и потом при выполнении запроса SELECT позволяют пропускать блоки, в которых гарантированно нет запрашиваемых данных. Таким образом можно уменьшить объём данных, читаемых с диска.

Индексы пропуска данных описываются в следующем формате:

INDEX <index_name> <expr> TYPE <index_type> GRANULARITY <granularity_value>

При описании индекса задается:

  • имя индекса;

  • выражение, по которому будут агрегироваться данные;

  • тип индекса. Наиболее часто используемые индексы:

    • minmax — хранит минимум и максимум выражения;

    • set(max_rows) — хранит уникальные значения выражения в количестве, не большем чем max_rows;

    • bloom_filter([false_positive]) — фильтр Блума для указанных столбцов. Необязательный параметр false_positive — это вероятность получения ложноположительного срабатывания. Возможные значения: (0, 1). Значение по умолчанию: 0.025.

  • гранулярность — количество гранул данных в пропускаемом блоке.

Добавлять и удалять столбцы, ограничения и индексы можно и после создания таблицы с помощью запросов ALTER TABLE (см. раздел Модификация таблицы ниже).

Основные выражения

ON CLUSTER

По умолчанию таблица создается только на том сервере, на котором выполняется запрос. Чтобы создать таблицу на всех серверах кластера, нужно использовать выражение ON CLUSTER с указанием имени кластера

ENGINE

Движок таблицы с параметрами. Названия движков таблиц являются регистрозависимыми. Их можно посмотреть в системной таблице system.table_engines

ORDER BY

Ключ сортировки в виде одного или нескольких столбцов. ADQM использует ключ сортировки в качестве первичного ключа, если первичный ключ не задан с помощью выражения PRIMARY KEY

PARTITION BY

Ключ партиционирования. Ключом партиционирования может быть столбец таблицы или произвольное выражение по столбцу. Например, чтобы задать партиционирование по месяцам, можно использовать выражение toYYYYMM(date_column), где date_column — столбец с датой типа Date. Ключом партиционирования также может быть кортеж из столбцов или выражений

PRIMARY KEY

Первичный ключ. По умолчанию первичный ключ совпадает с ключом сортировки ORDER BY, поэтому в большинстве случаев выражение PRIMARY KEY отдельно использовать не нужно. Если задается первичный ключ, отличный от ключа сортировки, то кортеж выражений первичного ключа должен быть префиксом кортежа выражений ключа сортировки

SAMPLE BY

Выражение для семплирования

TTL

Список правил TTL (time-to-live), определяющих сроки хранения строк в таблице, а также как данные должны быть обработаны по истечении указанного времени (например, устаревшие данные могут быть автоматически удалены, перемещены на другие диски/тома, сжаты другим кодеком или агрегированы/сгруппированы — подробнее в статье Использование TTL)

SETTINGS

Дополнительные параметры, регулирующие поведение таблицы MergeTree. Список настроек таблиц семейства MergeTree и их значений можно получить с помощью системной таблицы system.merge_tree_settings

В запросе CREATE TABLE (а также в других запросах, приведенных в этой статье ниже) <table_name> — имя таблицы, <db_name> — имя базы данных, в которой находится таблица. Если база данных не указана, запрос применяется к таблице в текущей базе данных сессии. Чтобы установить текущую базу данных, используйте следующий запрос:

USE <db_name>;

Примеры

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

 

Следующий запрос создает таблицу MergeTree с тремя столбцами, которые будут хранить числовые идентификаторы, строковые значения и даты. В качестве ключа сортировки задается кортеж из столбца с идентификаторами и столбца с датами (первичный ключ PRIMARY KEY будет совпадать с ключом сортировки), а также устанавливается партиционирование по дням.

CREATE TABLE table_mergeTree
(
    id Int32,
    value String,
    date Date
)
ENGINE = MergeTree
ORDER BY (id, date)
PARTITION BY toYYYYMMDD(date);

Движок MergeTree не имеет параметров, его можно указывать как ENGINE = MergeTree() или ENGINE = MergeTree.

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

 
Таблица ReplacingMergeTree выполняет удаление дублирующихся записей с одинаковым значением ключа сортировки (ORDER BY, не PRIMARY KEY) во время слияния кусков данных.

Движок ReplacingMergeTree имеет один необязательный параметр ver — столбец (типа UInt*, Date или DateTime) с версией записи данных.

Из всех строк с одинаковым значением ключа сортировки ReplacingMergeTree оставляет только одну:

  • Последнюю по времени создания, если параметр ver не задан.

    CREATE TABLE table_replacing_1
(
    id Int32,
    value String,
    date Date,
    ver Int32
)
ENGINE = ReplacingMergeTree()
ORDER BY id;

INSERT INTO table_replacing_1 VALUES(1, 'value1', '2022-01-01', 1), (1, 'value2', '2022-01-01', 2), (1, 'value3', '2022-01-01', -1);

SELECT * FROM table_replacing_1 FINAL;

    В результате остается последняя добавленная строка:

    --id---value----------date---ver--
│  1 │ value3 │ 2022-01-01 │  -1 │
----------------------------------

  • С максимальной версией, если параметр ver задан.

    CREATE TABLE table_replacing_2
(
    id Int32,
    value String,
    date Date,
    ver Int32
)
ENGINE = ReplacingMergeTree(ver)
ORDER BY id;

INSERT INTO table_replacing_2 VALUES(1, 'value1', '2022-01-01', 1), (1, 'value2', '2022-01-01', 2), (1, 'value3', '2022-01-01', -1);

SELECT * FROM table_replacing_2 FINAL;

    В результате остается строка с большим значением в столбце ver:

    --id---value----------date---ver--
│  1 │ value2 │ 2022-01-01 │   2 │
----------------------------------

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

Так как дедупликация производится во время слияния данных, которое происходит в фоновом режиме, некоторая часть данных может остаться необработанной в момент, когда выполняется запрос SELECT. Чтобы вызвать внеочередное слияние и получить таблицу или партицию без дубликатов, можно использовать модификатор FINAL после имени таблицы в запросе SELECT или предварительно выполнить запрос OPTIMIZE для таблицы или партиции.

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

 
Движок SummingMergeTree отслеживает строки с одинаковыми ключами сортировки и заменяет их на одну, суммируя значения в числовых столбцах. Значения в столбцах других типов выбираются произвольно из какой-либо строки.

Движок SummingMergeTree имеет один необязательный параметр columns — имена столбцов, в которых будут суммироваться данные (столбцы должны иметь числовой тип и не должны входить в ключ сортировки).

  • Если параметр columns не задан, то ADQM суммирует значения во всех столбцах с числовым типом данных, не входящих в ключ сортировки.

    CREATE TABLE table_sum_1
(
    id UInt32,
    value1 Int32,
    value2 Int32,
    value3 Int32
)
ENGINE = SummingMergeTree()
ORDER BY id;

INSERT INTO table_sum_1 VALUES(1, 1, 1, 1), (1, 2, 2, 2), (1, 3, 3, 3);

SELECT * FROM table_sum_1;

    В результате значения во всех числовых столбцах просуммировались:

    --id---value1---value2---value3--
│  1 │      6 │      6 │      6 │
---------------------------------

  • Если параметр columns задан, ADQM суммирует значения в заданных столбцах.

    CREATE TABLE table_sum_2
(
    id UInt32,
    value1 Int32,
    value2 Int32,
    value3 Int32
)
ENGINE = SummingMergeTree(value1, value2)
ORDER BY id;

INSERT INTO table_sum_2 VALUES(1, 1, 1, 1), (1, 2, 2, 2), (1, 3, 3, 3);

SELECT * FROM table_sum_2;

    В результате значения в столбцах value1 и value2 просуммировались, а значение столбца value3 взялось из произвольной строки:

    --id---value1---value2---value3--
│  1 │      6 │      6 │      1 │
---------------------------------
Создание таблицы AggregatingMergeTree

 
В таблицах AggregatingMergeTree все строки с одинаковым ключом сортировки заменяются на одну (в пределах одного куска данных), которая хранит объединение состояний агрегатных функций. Такие таблицы могут использоваться для инкрементальной агрегации данных, в том числе для агрегирующих материализованных представлений.

Движок AggregatingMergeTree не имеет параметров. Ниже приведен пример создания агрегирующего материализованного представления.

  1. Создайте таблицу table_sales типа MergeTree для хранения данных о продажах:

    CREATE TABLE table_sales
(
    user_id Int64,
    store_id Int32,
    product_id Int32,
    date Date,
    sum Decimal(20, 2)
)
ENGINE = MergeTree
PARTITION BY date
ORDER BY (store_id, product_id, user_id);

  2. Используя запрос CREATE MATERIALIZED VIEW, создайте материализованное представление типа AggregatingMergeTree, которое "смотрит" на таблицу table_sales и хранит промежуточные состояния (state) для агрегатных функций:

    CREATE MATERIALIZED VIEW table_aggregating
(
    store_id Int32,
    product_id Int32,
    date Date,
    s AggregateFunction(sum, Decimal(20, 2)),
    c AggregateFunction(count, Decimal(20, 2))
)
ENGINE = AggregatingMergeTree
PARTITION BY date
ORDER BY (store_id, date) AS
SELECT
    store_id,
    product_id,
    date,
    sumState(sum) AS s,
    countState(sum) AS c
FROM table_sales
GROUP BY
    store_id,
    date,
    product_id;

    При вставке данных в таблицу table_sales в материализованном представлении table_aggregating будет происходит автоматическое обновление агрегатных состояний для всех измененных ключей, указанных в GROUP BY.

  3. Чтобы получить агрегированные данные, выполните запрос SELECT …​ GROUP BY …​ из представления table_aggregating:

    SELECT
    store_id,
    product_id,
    date,
    sumMerge(s),
    countMerge(c)
FROM table_aggregating
GROUP BY
    store_id,
    date,
    product_id
LIMIT 1;
Создание таблицы CollapsingMergeTree

 
Движок таблиц CollapsingMergeTree позволяет быстро записывать постоянно изменяющиеся состояния объектов и удаляет старые состояния объектов в фоновом режиме, что значительно сокращает объем хранения.

CollapsingMergeTree асинхронно удаляет (сворачивает) пары строк, которые содержат одинаковые значения во всех столбцах в ключе сортировки (ORDER BY), но разные значения в специальном столбце sign. Этот столбец имеет тип данных Int8 и может содержать значения 1 и -1, по которым определяется тип строки:

  • 1 — обозначает, что строка является строкой состояния;

  • -1 — обозначает, что строка является строкой отмены состояния.

Столбец sign указывается в качестве параметра движка CollapsingMergeTree.

Подробно алгоритм сворачивания строк описан в статье CollapsingMergeTree документации ClickHouse.

Ниже приведен пример преобразования данных таблицей CollapsingMergeTree.

  1. Создайте таблицу типа CollapsingMergeTree и укажите столбец, по значениям которого будет определяться тип строки:

    CREATE TABLE table_collapsing
(
    id UInt32,
    pageViews UInt8,
    duration UInt8,
    sign Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY id;

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

    INSERT INTO table_collapsing VALUES (1, 7, 100, 1);
INSERT INTO table_collapsing VALUES (1, 7, 100, -1), (1, 8, 150, 1);

  3. Используйте запрос SELECT, чтобы проверить, что в таблице удалились строки, соответствующие устаревшему состоянию объекта, и осталось только последнее состояние объекта. Так как ADQM объединяет вставленные куски данных в неизвестный момент времени, то для получения полностью "свернутых" данных из таблицы CollapsingMergeTree, требуется агрегирование. Если необходимо выбирать данные без агрегации (например, проверить наличие строк, последние значения которых удовлетворяют некоторым условиям), можно использовать модификатор FINAL для выражения FROM.

    SELECT * FROM table_collapsing FINAL;

    Результат выполнения запроса:

    --id---pageViews---duration---sign--
│  1 │         8 │      150 │    1 │
------------------------------------
Создание таблицы VersionedCollapsingMergeTree

 
Движок VersionedCollapsingMergeTree имеет то же назначение, что и CollapsingMergeTree, но использует другой алгоритм удаления (сворачивания) строк, который позволяет вставлять данные в любом порядке в несколько потоков (CollapsingMergeTree требует строго последовательную вставку данных).

Параметры движка VersionedCollapsingMergeTree:

  • sign — имя столбца (типа Int8), значения которого обозначают тип строки (1 — строка состояния объекта; -1 — строка отмены состояния, которая копирует все поля отменяемого состояния за исключением sign);

  • version — имя столбца (типа UInt), который содержит версию состояния объекта (идентифицирует каждое состояние объекта отдельным номером).

Ниже приведен пример преобразования данных таблицей VersionedCollapsingMergeTree.

  1. Создайте таблицу типа VersionedCollapsingMergeTree и определите столбцы, по которым будет определяться тип строки и версия состояния объекта:

    CREATE TABLE table_versioned_collapsing
(
    id UInt32,
    pageViews UInt8,
    duration UInt8,
    sign Int8,
    version UInt8
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY id;

  2. Используйте два запроса INSERT для создания двух отдельных кусков данных. Во втором запросе первая запись отменяет предыдущее состояние объекта, а вторая содержит текущее состояние.

    INSERT INTO table_versioned_collapsing VALUES (1, 7, 100, 1, 1);
INSERT INTO table_versioned_collapsing VALUES (1, 7, 100, -1, 1), (1, 8, 150, 1, 2);

  3. Используйте запрос SELECT, чтобы проверить, что таблица удалила строки, соответствующие устаревшему состоянию объекта, и оставила только последнее состояние объекта. Так как ADQM объединяет вставленные куски данных в неизвестный момент времени, то для получения полностью "свернутых" данных из таблицы VersionedCollapsingMergeTree, требуется агрегирование. Если необходимо выбирать данные без агрегации (например, проверить наличие строк, последние значения которых удовлетворяют некоторым условиям), можно использовать модификатор FINAL для выражения FROM.

    SELECT * FROM table_versioned_collapsing FINAL;

    Результат выполнения запроса:

    --id---pageViews---duration---sign---version--
│  1 │         8 │      150 │    1 │       2 │
----------------------------------------------
Создание реплицируемых таблиц

 
Для создания реплицируемых таблиц используются движки семейства MergeTree с префиксом Replicated.

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

  • путь к таблице в ZooKeeper;

  • имя реплики в ZooKeeper (идентифицирует разные реплики одной и той же таблицы).

Также каждый движок с префиксом Replicated имеет параметры движка, которому соответствует данная реплицируемая версия. Например, движок ReplicatedSummingMergeTree, как и движок SummingMergeTree, имеет необязательный параметр columns — список столбцов, в которых будут суммироваться данные.

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

Просмотр списка таблиц

Получить имена всех таблиц, существующих в базе данных, можно следующими способами:

  • Использовать запрос SHOW TABLES.

    SHOW TABLES FROM <database_name>;

  • Сделать выборку из системной таблицы system.tables.

    SELECT name FROM system.tables WHERE database='<database_name>';

Получение информации о таблице

Структура таблицы

Описание столбцов таблицы можно получить с помощью запроса DESCRIBE TABLE:

DESCRIBE TABLE [<db_name>.]<table_name>;

Этот запрос возвращает список столбцов таблицы с их именами и типами данных, а также следующую информацию по каждому столбцу:

  • тип выражения для значения по умолчанию (DEFAULT, MATERIALIZED, EPHEMERAL или ALIAS);

  • выражение для значения по умолчанию;

  • комментарий;

  • кодек, который применяется к столбцу;

  • выражение TTL.

Пример

 
Запрос:

CREATE TABLE test_table
(
    id Int32,
    description String DEFAULT 'unknown' COMMENT 'This column contains descriptions' CODEC(LZ4),
    date Date COMMENT 'This column contains event dates'
)
ENGINE = MergeTree
ORDER BY id;

DESCRIBE TABLE test_table;

Результат:

--name----------type-----default_type---default_expression---comment-----------------------------codec_expression---ttl_expression--
│ id          │ Int32  │              │                    │                                   │                  │                │
│ description │ String │ DEFAULT      │ 'unknown'          │ This column contains descriptions │ LZ4              │                │
│ date        │ Date   │              │                    │ This column contains event dates  │                  │                │
------------------------------------------------------------------------------------------------------------------------------------

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

Метаданные таблицы можно получить из системной таблицы system.tables. Синтаксис запроса:

SELECT * FROM system.tables WHERE name = '<table_name>' [FORMAT <format_name>];
Пример

 
Запрос:

SELECT * FROM system.tables WHERE name = 'test_table' FORMAT Vertical;

Результат:

Row 1:
──────
database:                      default
name:                          test_table
uuid:                          7239d59f-9144-4600-b47f-c9dd02330242
engine:                        MergeTree
is_temporary:                  0
data_paths:                    ['/var/lib/clickhouse/store/723/7239d59f-9144-4600-b47f-c9dd02330242/']
metadata_path:                 /var/lib/clickhouse/store/463/4632cdc5-46bc-4d4e-ad36-27b8d101e2e1/test_table.sql
metadata_modification_time:    2022-12-21 07:04:25
dependencies_database:         []
dependencies_table:            []
create_table_query:            CREATE TABLE default.test_table (`id` Int32, `v` String, `d` Date, `v2` String CODEC(ZSTD(10))) ENGINE = MergeTree PARTITION BY d ORDER BY id SETTINGS index_granularity = 8192 COMMENT 'This is a test table'
engine_full:                   MergeTree PARTITION BY d ORDER BY id SETTINGS index_granularity = 8192
as_select:
partition_key:                 d
sorting_key:                   id
primary_key:                   id
sampling_key:
storage_policy:                default
total_rows:                    1000000
total_bytes:                   56688997
lifetime_rows:                 ᴺᵁᴸᴸ
lifetime_bytes:                ᴺᵁᴸᴸ
comment:                       This is a test table
has_own_data:                  1
loading_dependencies_database: []
loading_dependencies_table:    []
loading_dependent_database:    []
loading_dependent_table:       []

Добавление данных в таблицу

Для добавления данных в таблицу, используется запрос INSERT INTO, в котором после выражения VALUES через запятую перечисляются значения столбцов (или кортежи данных, если добавляется более одной строки):

INSERT INTO [<db_name>.]<table_name> [(<column_name1>, <column_name2>, <column_name3>)] VALUES (v11, v12, v13), (v21, v22, v23), ...;
Пример

  1. Создайте таблицу с тремя столбцами:

    CREATE TABLE test_table
(
    a Int32,
    b String,
    c Int32
)
ENGINE = MergeTree
ORDER BY a;

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

    INSERT INTO test_table VALUES (1, 'a', 1), (2, 'b', 2);

    Результат:

    --a---b---c--
│ 1 │ a │ 1 │
│ 2 │ b │ 2 │
-------------

  3. Вставьте две строки, указав значения только для столбцов a и b:

    INSERT INTO test_table (a, b) VALUES (3, 'c'), (4, 'd');

    В результате данные из запроса INSERT INTO записываются в столбцы a и b, а столбец c заполняется нулями, так как для него не задано значение по умолчанию:

    --a---b---c--
│ 3 │ c │ 0 │
│ 4 │ d │ 0 │
-------------

Запрос INSERT INTO позволяет также копировать данные из одной таблицы в другую. Для этого используется комбинация выражений INSERT INTO и SELECT:

INSERT INTO [<db_name>.]<table_name> [(<column_name1>, <column_name2>, <column_name3>)] SELECT ... ;
Пример

 
Приведенный ниже пример копирует в таблицу new_table первые три строки из таблицы с той же структурой source_table, которая содержит следующие данные:

--a---b----c--
│ 1 │ a │  1 │
│ 1 │ a │  1 │
│ 2 │ b │  2 │
│ 3 │ c │  0 │
│ 6 │ e │ 12 │
│ 9 │ a │  9 │
--------------

  1. Создайте таблицу new_table с тремя столбцами:

    CREATE TABLE new_table
(
    d Int32,
    f String,
    g Int32
)
ENGINE = MergeTree
ORDER BY d;

  2. Вставьте в новую таблицу три строки из таблицы source_table с помощью следующего запроса:

    INSERT INTO new_table SELECT * FROM source_table LIMIT 3;

    Результат выполнения запроса:

    --d---f---g--
│ 1 │ a │ 1 │
│ 1 │ a │ 1 │
│ 2 │ b │ 2 │
-------------

Чтение данных из таблицы

Для чтения данных из таблицы используется запрос SELECT. В запросах этого типа можно использовать различные модификаторы и выражения для дополнительной обработки считываемых данных. Например, с помощью специальных выражений данные, которые будет возвращать запрос, можно отфильтровать/отсортировать/сгруппировать, ограничить количество строк в выборке, задать формат вывода данных. Подробнее все выражения, доступные для использования в запросах SELECT, описаны в разделе SELECT Query документации ClickHouse.

Чтение всех данных

 
Чтобы считать полностью все данные из таблицы, нужно использовать символ * после выражения SELECT:

SELECT * FROM [<db_name>.]<table_name>;

Например, следующий запрос считывает данные из всех столбцов таблицы test_table:

SELECT * FROM test_table;

Результат:

--a---b----c--
│ 1 │ a │  1 │
│ 1 │ a │  1 │
│ 2 │ b │  2 │
│ 3 │ c │  0 │
│ 6 │ e │ 12 │
│ 9 │ a │  9 │
--------------
Чтение данных из определенных столбцов

 
Чтобы считать данные из определенных столбцов, нужно перечислить имена этих столбцов после выражения SELECT:

SELECT <column_name1>[, ...] FROM [<db_name>.]<table_name>;

Например, следующий запрос считывает данные из столбцов b и c таблицы test_table:

SELECT b, c FROM test_table;

Результат:

--b----c--
│ a │  1 │
│ a │  1 │
│ b │  2 │
│ c │  0 │
│ e │ 12 │
│ a │  9 │
----------
Выборка определенного количества строк

 
Чтобы выбрать из таблицы определенное количество строк, нужно использовать выражение LIMIT в запросе SELECT:

SELECT * FROM [<db_name>.]<table_name> LIMIT [n, ]m;

В результате выполнения этого запроса из таблицы выбираются первые m строк после пропуска первых n строк.

Например, следующий запрос считывает первые три строки из таблицы test_table:

SELECT * FROM test_table LIMIT 3;

Результат:

--a---b----c--
│ 1 │ a │  1 │
│ 1 │ a │  1 │
│ 2 │ b │  2 │
--------------
Чтение данных по заданному условию

 
Задать условие для фильтрации считываемых данных можно с помощью выражения WHERE:

SELECT <column_name1>[, ...] FROM [<db_name>.]<table_name> WHERE <filter_expr>;

Например, следующий запрос выводит значения столбца c, если они больше 1, но меньше 10:

SELECT c FROM test_table WHERE (c > 1) AND (c < 10);

Результат:

---c--
│  2 │
│  9 │
------
Сортировка результатов

 
Отсортировать результаты запроса SELECT можно с помощью выражения ORDER BY:

SELECT <column_name1>[, ...] FROM [<db_name>.]<table_name> ORDER BY <expr_list>;

Например, следующий запрос сортирует данные по значениям в столбце c в порядке возрастания:

SELECT * FROM test_table ORDER BY c;

Результат:

--a---b----c--
│ 3 │ c │  0 │
│ 1 │ a │  1 │
│ 1 │ a │  1 │
│ 2 │ b │  2 │
│ 9 │ a │  9 │
│ 6 │ e │ 12 │
--------------

Модификация таблицы

Для таблиц семейства MergeTree, а также таблиц типа Merge и Distributed, поддерживается запрос ALTER TABLE, который позволяет изменять структуру таблицы, ее настройки или данные. Например, ниже приведен синтаксис запроса для изменения столбцов таблицы:

ALTER TABLE [<db_name>].<table_name> [ON CLUSTER <cluster_name>] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...;
Манипуляции со столбцами

 
Следующие выражения в запросах ALTER TABLE позволяют выполнять различные действия над столбцами таблицы.

Выражение Описание Синтаксис

ADD COLUMN

Добавляет столбец в таблицу.

Выражения AFTER | FIRST используются, чтобы указать куда вставлять новый столбец — после указанного столбца after_column_name или в начало таблицы. Если ни одно из этих выражений не используется, столбец добавляется в конец таблицы

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    ADD COLUMN [IF NOT EXISTS] <new_column_name> [<data_type>] [<default_expr>] [CODEC(<codec_name>)] [TTL <expr>] [AFTER <after_column_name> | FIRST];

DROP COLUMN

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

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    DROP COLUMN [IF EXISTS] <column_name>;

RENAME COLUMN

Переименовывает существующий столбец.

Столбцы, которые являются частью первичного ключа (PRIMARY KEY) или ключа сортировки (ORDER BY), не могут быть переименованы

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    RENAME COLUMN [IF EXISTS] <old_name> to <new_name>;

CLEAR COLUMN

Сбрасывает все значения в столбце для заданной партиции

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    CLEAR COLUMN [IF EXISTS] <column_name> IN PARTITION <partition_name>;

COMMENT COLUMN

Добавляет комментарий к столбцу.

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

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    COMMENT COLUMN [IF EXISTS] <column_name> 'Text comment';

MODIFY COLUMN

Изменяет настройки столбца (тип данных, выражение для значения по умолчанию, кодек сжатия данных, TTL, позицию столбца в таблице)

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    MODIFY COLUMN [IF EXISTS] <column_name> [<data_type>] [<default_expr>] [CODEC(<codec_name>)] [TTL <expr>] [AFTER <after_column_name> | FIRST];
Манипуляции с ключевыми выражениями таблиц

 
Для таблиц семейства MergeTree (в том числе реплицируемых таблиц) поддерживается запрос на изменение ключа сортировки таблицы:

ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>] MODIFY ORDER BY <new_expression>;
Манипуляции с индексами

 
С помощью следующих запросов можно добавить или удалить индекс пропуска данных:

ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>] ADD INDEX <index_name> <expression> TYPE <index_type> GRANULARITY  <granularity_value> [FIRST|AFTER name];
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>] DROP INDEX <index_name>;

Эти операции поддерживаются только таблицами семейства MergeTree.

Манипуляции с ограничениями (constraints)

 
Добавить или удалить ограничение можно с помощью запросов:

ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>] ADD CONSTRAINT <constraint_name> CHECK <expression>;
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>] DROP CONSTRAINT <constraint_name>;
Манипуляции с партициями и кусками данных

 
Для работы с партициями и кусками данных можно использовать следующие выражения в запросах ALTER TABLE.

Выражение Описание Синтаксис

DETACH PARTITION|PART

Переносит партицию/кусок в директорию detached (сервер не будет "видеть" партицию/кусок, пока не будет выполнен запрос ATTACH PARTITION|PART).

Запрос реплицируется — данные перемещаются в директорию detached на всех репликах

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    DETACH PARTITION|PART <partition_expr>;

DROP PARTITION|PART

Удаляет партицию/кусок.

Запрос реплицируется — данные удаляются на всех репликах

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    DROP PARTITION|PART <partition_expr>;

DROP DETACHED PARTITION|PART

Удаляет партицию/кусок из директории detached

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    DROP DETACHED PARTITION|PART <partition_expr>;

ATTACH PARTITION|PART

Добавляет партицию/кусок в таблицу из директории detached.

Запрос реплицируется

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    ATTACH PARTITION|PART <partition_expr>;

ATTACH PARTITION FROM

Копирует партицию из одной таблицы в другую.

Таблицы должны иметь одинаковые:

  • структуры;

  • ключи партиционирования, ключи сортировки и первичные ключи;

  • политики хранения данных (диск, на котором хранится партиция, должен быть доступен для обеих таблиц).

 
ALTER TABLE <dest_table_name> [ON CLUSTER <cluster_name>]
    ATTACH PARTITION <partition_expr> FROM <source_table_name>;

REPLACE PARTITION FROM

Копирует партицию из одной таблицы в другую с заменой существующей партиции в таблице назначения (данные из таблицы-источника не удаляются).

Таблицы должны иметь одинаковые:

  • структуры;

  • ключи партиционирования, ключи сортировки и первичные ключи;

  • политики хранения данных (диск, на котором хранится партиция, должен быть доступен для обеих таблиц).

 
ALTER TABLE <dest_table_name> [ON CLUSTER <cluster_name>]
    REPLACE PARTITION <partition_expr> FROM <source_table_name>;

MOVE PARTITION TO TABLE

Перемещает партицию из одной таблицы в другую с удалением данных из таблицы-источника.

Таблицы должны иметь одинаковые:

  • структуры;

  • ключи партиционирования, ключи сортировки и первичные ключи;

  • политики хранения данных (диск, на котором хранится партиция, должен быть доступен для обеих таблиц);

  • семейства движков (реплицируемые или нереплицируемые).

 
ALTER TABLE <source_table_name> [ON CLUSTER <cluster_name>]
    MOVE PARTITION <partition_expr> TO TABLE <dest_table_name>;

CLEAR COLUMN IN PARTITION

Удаляет все значения в столбце для заданной партиции.

Если для столбца задано значение по умолчанию, то будет выставлено это значение

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    CLEAR COLUMN <column_name> IN PARTITION <partition_expr>;

CLEAR INDEX IN PARTITION

Очищает построенные вторичные индексы для заданной партиции

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    CLEAR INDEX <index_name> IN PARTITION <partition_expr>;

FREEZE PARTITION

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

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

Запрос не реплицируется — резервная копия создается только на локальном сервере.

Чтобы сделать резервную копию метаданных таблицы, необходимо скопировать файл /var/lib/clickhouse/metadata/database/table.sql

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    FREEZE [PARTITION <partition_expr>] [WITH NAME 'backup_name'];

UNFREEZE PARTITION

Удаляет с диска резервную копию партиции.

Если секция PARTITION не используется, запрос удаляет резервную копию всех партиций сразу

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    UNFREEZE [PARTITION <part_expr>] WITH NAME 'backup_name';

FETCH PARTITION|PART

Скачивает партицию/кусок с другого сервера.

Запрос работает только для реплицируемых таблиц

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    FETCH PARTITION|PART <partition_expr> FROM 'path-in-zookeeper';

MOVE PARTITION|PART

Перемещает партицию/кусок на другой диск или том.

Запрос не реплицируется, так как на разных репликах могут быть различные конфигурации политик хранения

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    MOVE PARTITION|PART <partition_expr> TO DISK|VOLUME 'disk_name';

UPDATE IN PARTITION

Обновляет соответствующие указанному условию данные в партиции

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    UPDATE <column_name1> = <expr1> [, ...] [IN PARTITION <partition_expr>] WHERE <filter_expr>;

DELETE IN PARTITION

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

 
ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>]
    DELETE [IN PARTITION <partition_expr>] WHERE <filter_expr>;

Как задать партицию в запросе

Чтобы задать партицию в запросе ALTER TABLE …​ PARTITION, можно использовать:

  • Имя партиции в одинарных кавычках (кавычки можно не использовать, если выражение, по которому задано партиционирование, типа Int*). Например: ALTER TABLE test_table DROP PARTITION '2022-12-26';.

  • Кортеж констант или выражений, совпадающий по типам с кортежем ключа партиционирования таблицы. Если ключ партиционирования таблицы состоит из одного элемента, выражение нужно обернуть в функцию tuple(…​). Например, ALTER TABLE test_table DROP PARTITION tuple(toYYYYMMDD(toDate('2022-12-26')));.

  • Строковый идентификатор партиции в одинарных кавычках, указанный для выражения PARTITION ID. Например, ALTER TABLE test_table DROP PARTITION ID '20221226';.

Посмотреть имя и идентификатор партиции можно в столбцах partition и partition_id системной таблицы system.parts:

SELECT partition, partition_id FROM system.parts WHERE table = '<table_name>' and active = 1;

Чтобы задать имя куска партиции в запросах ATTACH PART и DROP DETACHED PART, используется значение из столбца name системной таблицы system.detached_parts (в одинарных кавычках).

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

Отключение и подключение таблицы

Таблицу можно отключить с помощью запроса DETACH TABLE:

DETACH TABLE [IF EXISTS] [<db_name.]<table_name> [ON CLUSTER <cluster_name>] [PERMANENTLY];

При выполнении этого запроса таблица становится "невидимой" для сервера, но ее данные и метаданные не удаляются. Если отключение не было перманентным (ключевое слово PERMANENTLY не использовалось в запросе), при следующем запуске сервер прочитает метаданные и снова будет "видеть" таблицу. Если таблица была отключена перманентно, то сервер не подключит ее обратно автоматически.

Независимо от того, каким способом таблица была отключена, ее можно подключить обратно с помощью запроса ATTACH:

ATTACH TABLE [IF NOT EXISTS] [<db_name.]<table_name> [ON CLUSTER <cluster_name>];

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

Удалить таблицу можно с помощью запроса DROP TABLE:

DROP TABLE [IF EXISTS] [<db_name.]<table_name> [ON CLUSTER <cluster_name>];
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней