Структура базы данных

Основные движки таблиц в ClickHouse

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

ENGINE	SELECT	INSERT	DELETE	UPDATE	persistent	indexes
*MergeTree	+	+	+	+	+	+
*Log	+	+	+	-	+	-
EmbeddedRocksDB	+	+	+	-	+	-
URL	+	-/+	-	-	external	-
Buffer	+	+	-	-	+	-
Memory	+	+	+	+	-	-
Set	(only IN)	-	-	-	+	-
Join	+	-	-	-	+	-
PostgreSQL	+	+	-	-	external	-
Kafka	+	-	-	-	external	-

Примечания:

+ - поддерживается
- - не поддерживается
- / + - частичная поддержка или зависит от конфигурации
external - данные хранятся во внешней системе
(only IN) - поддерживается только операция IN
persistent - "постоянное хранение" (указывает, сохраняются ли данные на диск или во внешней системе)
indexes - "индексы" (указывает, поддерживает ли движок индексацию данных)

Семейство движков *MergeTree

Движки семейства *MergeTree - это основа ClickHouse и наиболее часто используемый тип таблиц в этой СУБД. Они предназначены для хранения больших объёмов данных на диске в сжатом виде и поддерживают полный набор операций DML: SELECT, INSERT, DELETE, UPDATE. Благодаря этому можно не только читать и добавлять данные, но и выполнять их удаление и обновление.

Ключевая особенность MergeTree - поддержка индексов, в том числе уникальных для ClickHouse индексов пропуска данных (разреженных индексов). Эти индексы позволяют эффективно фильтровать данные и ускоряют выполнение аналитических запросов на больших таблицах. Таблицы MergeTree могут быть оптимизированы с помощью партиционирования и сортировки по ключу, что дополнительно увеличивает производительность при работе с большими объёмами информации.

Разновидности движков семейства MergeTree

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

Базовый MergeTree

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

Расширенные движки на базе MergeTree

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

AggregatingMergeTree и SummingMergeTree - используются для хранения агрегированных данных, например, суммарных значений за разные периоды. Они позволяют автоматически агрегировать данные при слиянии частей таблицы, что упрощает и ускоряет аналитические вычисления.
ReplacingMergeTree - предназначен для автоматического удаления дубликатов записей, оставляя только последнюю версию данных по ключу. Это полезно, когда данные могут обновляться путём вставки новых версий.
CollapsingMergeTree (с параметром sign) - реализует механизм «свёртывания» строк с противоположными значениями sign, что позволяет эффективно удалять отменённые записи и поддерживать консистентность данных.
VersionedCollapsingMergeTree - расширяет возможности CollapsingMergeTree, добавляя поддержку версионирования записей для более точного контроля обновлений и удаления.

Рассмотрим их подробнее.

Движок SummingMergeTree

Движок SummingMergeTree предназначен для хранения данных с автоматической агрегацией по сумме значений в определённых числовых столбцах. Основной принцип работы этого движка заключается в том, что при слиянии частей таблицы все строки, имеющие одинаковый ключ сортировки (первичный ключ), объединяются в одну запись. При этом для указанных в параметрах движка числовых столбцов происходит суммирование значений.

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

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

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

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

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

sql

CREATE TABLE summing_mt
(
    id UInt32,
    val UInt32,
    example UInt32,  -- столбец, не входящий в ключ сортировки и параметры движка
)
ENGINE = SummingMergeTree(val) -- сумма будет считаться по полю val, так как оно указано в качестве параметра движка
ORDER BY (id); -- записи по этому ключу будут группироваться

INSERT INTO summing_mt SELECT 1, (number + 1) * 10, (number + 1) * 100 from numbers(3);
INSERT INTO summing_mt SELECT 1, 10, 100;

Посмотрите на выборку:

sql

SELECT * FROM summing_mt;

Здесь вы видите два куска, для которых слияние ещё не запустилось.

Теперь обратите внимание на вторую выборку:

sql

SELECT * FROM summing_mt FINAL;

Модификатор FINAL выдаст данные, как будто слияние уже произошло.

Альтернативным способом получить актуальные данные является запуск команды OPTIMIZE по целевой таблице или по указанной партиции.

sql

OPTIMIZE TABLE summing_mt FINAL;
SELECT * FROM summing_mt;

В результате вы увидите, что после OPTIMIZE данные просуммировались.

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

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

Движок AggregatingMergeTree

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

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

Рассмотрим пример:

sql

CREATE TABLE agg_mt
(
    id UInt32,
    val SimpleAggregateFunction(max, UInt32) -- агрегатная функция max
)
ENGINE = AggregatingMergeTree
ORDER BY (id);

INSERT INTO agg_mt SELECT 1, (number + 1) * 10 from numbers(3); -- 10, 20, 30

SELECT * FROM agg_mt

INSERT INTO agg_mt SELECT 1, 50;    -- вставка данных в новый кусок

SELECT * FROM agg_mt

Последний SELECT нам выдал 2 строки, потому что данные вставились в разные куски, а агрегация срабатывает для данных в одному куске. Нужно, чтобы прошло слияние и данные оказались в одном куске. С инструкцией FINAL можно увидеть результат, будто слияние уже прошло, вот так:

sql

SELECT * FROM agg_mt FINAL

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

sql

SELECT id, max(val) as val
FROM agg_mt
GROUP BY id;

Внимание

FINAL работает примерно в полтора-два раза медленнее, чем явное указание группировки.

sql

OPTIMIZE TABLE agg_mt; -- выполнение слияния с помощью OPTIMIZE

SELECT * FROM agg_mt

В результате - одна строка, как в запросе с GROUP BY.

sql

INSERT INTO agg_mt SELECT 2, 150;  -- вставка новой записи с другим id

OPTIMIZE TABLE agg_mt;  -- выполнение слияния

SELECT * FROM agg_mt

Здесь на выходе получается две строки, так как ORDER BY (id), а у нас id разные.

Замечание

Следующие три движка используются в ситуациях, когда исходный MergeTree не подходит из-за низкой производительности, а именно — когда нужно совершать много операций обновления.

Движок ReplacingMergeTree

Движок ReplacingMergeTree предназначен для удаления дублирующихся записей с одинаковым значением ключа сортировки. В процессе фонового слияния данных (merge) все дубликаты по ключу заменяются на одну запись - последнюю по времени вставки.

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

Кроме того, ReplacingMergeTree поддерживает необязательный параметр ver - столбец с номером версии. При указании этого параметра движок сохраняет запись с максимальным значением версии для каждого ключа сортировки, что позволяет более точно контролировать, какая версия данных останется после дедупликации.

Ключевые особенности ReplacingMergeTree:

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

Чтобы посмотреть на практике, как работает ReplacingMergeTree, создайте таблицу с помощью кода:

sql

CREATE TABLE replacing_mt
(
    id UInt32,
    val UInt32
)
ENGINE = ReplacingMergeTree
ORDER BY (id);

Вставим искусственные данные. Для этого воспользуемся функцией numbers

sql

SELECT 1, (number + 1) * 10 FROM numbers(3);

Запрос выполняет выборку из виртуальной таблицы, которую создаёт функция numbers(3). Эта функция генерирует последовательность из трёх чисел: 0, 1 и 2. В результате для каждой строки формируется два столбца: первый всегда равен 1, а второй вычисляется как (number + 1) * 10. То есть для каждой строки к текущему значению number прибавляется единица, а затем результат умножается на 10.

Заполним этими данными таблицу:

sql

INSERT INTO replacing_mt SELECT 1, (number + 1) * 10 from numbers(3);
SELECT * FROM replacing_mt;

Обратите внимание, вы вставляли три записи, а вставилась в итоге одна. Это произошло потому, что вставка INSERT шла в одном батче и попала в один кусок, то есть replacing отработал сразу. У всех записей id=1, поэтому осталась только последняя.

Теперь посмотрите на код ниже:

sql

INSERT INTO replacing_mt SELECT 1, 3;
SELECT * FROM replacing_mt
SELECT * FROM replacing_mt FINAL

Новая вставка произошла в новый кусок. С использованием FINAL мы видим результирующий вариант. Асинхронно в фоне запускается слияние, или вы можете форсированно запустить его с помощью OPTIMIZE.

Движок ReplacingMergeTree может быть параметризован полем, указывающим на номер строки ENGINE = ReplacingMergeTree(version_fld). Кроме целочисленных типов поля используются для Date или DateTime. Когда запускается слияние, остаётся строка с максимальным номером version.

Рассмотрим пример кода с версионированием:

sql

CREATE TABLE replacing_mt_v
(
    id UInt32,
    val UInt32,
    version Int32 -- в рамках id останется одна строка с максимальным значением поля version
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (id);

Обратите внимание на поле version — в рамках id останется одна строка с максимальным значением поля version.

Движок CollapsingMergeTree

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

Принцип работы следующий:

Если у двух строк совпадают все значения по ключу сортировки, а в поле Sign у одной строки стоит 1 (означает "активная запись"), а у другой - -1 (означает "отменённая запись" или "удаление"), то такая пара строк будет автоматически "свёрнута" (удалена) в процессе фонового слияния данных.
CollapsingMergeTree позволяет реализовать логику отмены или удаления записей без явного удаления строк, что удобно для аналитических сценариев.

Рассмотрим пример:

sql

CREATE TABLE UAct
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID

В этом примере приложение фиксирует количество просмотров. Используйте код для эмуляции действий приложения:

sql

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1)

Теперь представьте, что пользователь посмотрел страницу ещё раз, и вам нужно зафиксировать новую цифру, то есть произвести UPDATE. Приложение должно отправить в таком случае две записи для вставки:

sql

INSERT INTO UAct
VALUES (4324182021466249494, 5, 146, -1),(4324182021466249494, 6, 185, 1)

Строка с Sign = -1 свернёт старую версию данных. А строка с Sign = 1 добавит новую. Теперь проверьте выборку с FINAL и без него:

sql

SELECT * FROM UAct;
SELECT * FROM UAct FINAL;

Обратите внимание, что после слияния остаётся одна версия записи.

VersionedCollapsingMergeTree

VersionedCollapsingMergeTree реализует логику CollapsingMergeTree, но использует другой алгоритм удаления строк и адаптирован для многопоточной вставки. Это достигается при помощи дополнительного поля version. Движок VersionedCollapsingMergeTree удаляет дублирующиеся записи с одинаковым значением ключа сортировки, за исключением поля sign с учётом версии строки. Этот процесс имеет альтернативное название — сворачивание записей. Когда выполняется процесс слияния, ClickHouse удаляет каждую пару строк, которые имеют один и тот же первичный ключ и версию, но разный Sign. Чтобы получить полностью свёрнутые данные из таблицы, необходимо использовать агрегирование или модификатор FINAL.

sql

CREATE TABLE vcollapsing_mt
(
    id UInt32,
    val String,
    sign Int8,
    version UInt8 -- удаляет каждую пару строк, которые имеют один и тот же первичный ключ, версию и разный sign
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY (id);

После этого добавьте в таблицу две записи с одинаковым id, равным единице. Строки будут различаться значением поля sign: у первой строки 1, у второй строки -1. Это означает удаление предыдущей строки в рамках id, у которой совпадает значение поля version.

sql

INSERT INTO vcollapsing_mt SELECT 1, 'state_1', 1, 1;
INSERT INTO vcollapsing_mt SELECT 1, 'cancel state', -1, 1;

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

sql

SELECT * FROM vcollapsing_mt;
SELECT * FROM vcollapsing_mt FINAL

На примере видно, что если выполнить выборку из таблицы vcollapsing_mt FINAL, то выборка не вернёт записи, потому что тут подразумевается удаление вставленной записи.

Разберём значения поля `sign:

Если sign = 1, это означает, что строка является состоянием объекта. Назовём её строкой состояния.
Если sign = -1, это означает отмену состояния объекта с теми же атрибутами. Назовём её строкой отмены состояния.

Работа с состояниями

Управление логикой выставления нужных значений sign и version возлагается на клиента, которым обычно выступает backend-приложение. Backend должен помнить состояние объекта, чтобы иметь возможность отменить его. Строка отмены состояния должна содержать идентичные поля первичного ключа и версии и противоположное значение sign.

Существует типичный алгоритм работы с движком VersionedCollapsingMergeTree для обновления данных, он выглядит так:

вставка новой строки, sign = 1, ver = 1;
обновление вставленной строки:
- удаление старой строки с помощью вставки с тем же ключом, sign = -1, ver = 1;
- вставка новой строки с тем же ключом, sign = 1, ver = 2.

В этом примере новая вставка будет выглядеть так:

sql

INSERT INTO vcollapsing_mt SELECT 1, 'state_2', 1, 2;
SELECT * FROM vcollapsing_mt FINAL;

Семейство движков *Log

Движки семейства *Log предназначены для сценариев, где требуется быстро записывать небольшие объёмы данных (до миллиона строк) в большое количество таблиц, а затем массово читать эти записи. Они идеально подходят для временных таблиц, тестовых данных или хранения промежуточных результатов.

Однако, в отличие от MergeTree, таблицы Log не поддерживают операции UPDATE и DELETE - изменить или удалить уже записанные данные невозможно. Также движки Log не поддерживают индексы, поэтому поиск по таким таблицам менее эффективен при больших объёмах данных. Основное преимущество - простота и высокая скорость вставки и чтения для небольших таблиц.

Основные свойства *Log-таблиц

При записи данных в *Log-таблицы новые данные добавляются в конец файла. Каждый столбец хранится в отдельном файле. Такие таблицы могут хранить данные в распределённых файловых системах, таких как HDFS или S3. Для обеспечения конкурентного доступа к данным используются блокировки.

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

Ограничения семейства *Log

Семейство *Log имеет следующие ограничения:

Отсутствие индексов: запросы SELECT неэффективны для выборки диапазонов данных. Движки *Log подходят для выборки всех записей сразу.
Невозможность модификации данных: операции UPDATE и DELETE не поддерживаются.
Отсутствие атомарности записи: в случае прерывания операции записи (например, из-за аварийного завершения работы сервера) таблица может содержать повреждённые данные.

Разновидности Log-движков

Семейство Log включает три движка:

TinyLog
Log
StripeLog

ENGINE	Parallel read	Колонночное хранение	Низкое использование дескрипторов
StripeLog	+	-	+
Log	+	+	-
TinyLog	-	+	-

Синтаксис создания *Log-таблиц предельно простой и состоит из перечисления полей и указания типа движка StripeLog, Log или TinyLog:

sql

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    ...
) ENGINE = [ StripeLog | Log | TinyLog ]

Движок TinyLog

Свойства движка TinyLog:

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

Движок TinyLog можно использовать в простых сценариях с низкой нагрузкой.

Движок Log

Свойства движка Log:

в отличие от TinyLog, вместе с файлами столбцов хранит небольшой файл засечек, это обеспечивает многопоточное чтение;
поддерживает параллельное чтение — при чтении данных ClickHouse использует множество потоков, каждый поток обрабатывает отдельный блок данных;
сохраняет каждый столбец таблицы в отдельном файле.

Движок Log подходит для временных данных, таблиц write-once и тестовых и демонстрационных целей.

Движок StripeLog

Свойства движка StripeLog:

хранит все данные в одном файле и использует меньше дескрипторов в операционной системе;
поддерживает параллельное чтение.

Движок StripeLog используется в тех же случаях, что и Log. Но его стоит выбирать, когда объём занимаемого места важнее, чем скорость чтения

Замечание

Если нужно хранить логи объёмом более миллиона строк или персистентно, использовать Log неэффективно. Для такой задачи лучше подойдёт MergeTree.

Движок EmbeddedRocksDB

ClickHouse включает встроенный движок EmbeddedRocksDB, который предназначен для хранения и обработки данных в формате key-value. Этот движок основан на RocksDB - высокопроизводительной базе данных, являющейся форком Google LevelDB. RocksDB поддерживает такие возможности, как транзакционность, снапшоты (снимки состояния базы), фильтры Блума для ускорения поиска, а также TTL (время жизни) для ключей, что позволяет автоматически удалять устаревшие данные.

На данный момент движок EmbeddedRocksDB в ClickHouse поддерживает операции чтения (SELECT) и вставки данных (INSERT). Однако операции удаления (DELETE) и обновления (UPDATE) пока не реализованы.

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

Движок RocksDB широко применяется во многих популярных СУБД, включая Cassandra®, MongoDB® и MySQL®, поэтому если вы работали с этими системами, интерфейс для взаимодействия с EmbeddedRocksDB может показаться вам знакомым.

Особенности движка EmbeddedRocksDB

Собственный формат хранения: В отличие от колоночных таблиц ClickHouse, таблицы на движке EmbeddedRocksDB хранят данные не по столбцам, а в собственном формате RocksDB, в основе которого лежит структура данных LSM-дерево.
Ограничения первичного ключа: Таблицы EmbeddedRocksDB поддерживают только один столбец в первичном ключе. При этом указание первичного ключа является обязательным.
Оптимизация запросов: Запросы с фильтрацией на равенство по ключу или с использованием оператора IN (вхождение в множество) специально оптимизированы для эффективного поиска по нескольким ключам в RocksDB.
Поддерживаемые операции: Движок поддерживает операции SELECT и INSERT, но не поддерживает UPDATE и DELETE.
Хранение данных: Обеспечивает постоянное хранение данных на диске, что гарантирует их сохранность между перезапусками сервера.

Синтаксис создания таблицы:

sql

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    pk_name [type1],
    column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    ...
) ENGINE = EmbeddedRocksDB
PRIMARY KEY(pk_name)

Движок URL

Движок URL позволяет работать с данными, расположенными на удалённом сервере, не сохраняя их локально в ClickHouse. Запросы к таблице с этим движком транслируются в HTTP-запросы: операции SELECT преобразуются в GET-запросы, а INSERT - в POST-запросы.

При этом модификация данных (DELETE, UPDATE) не поддерживается, так как движок URL не хранит данные локально и лишь выступает в роли прокси к удалённому источнику.

Особенности движка URL

Поддерживает многопоточные операции чтения и записи.
Не хранит данные локально, обращаясь напрямую к удалённому источнику.
Не поддерживает изменение данных через команды ALTER.
Не поддерживает семплирование данных.
Не поддерживает индексы и репликацию.

Синтаксис создания таблицы:

sql

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|ALIAS expr1],
    ...
) ENGINE = URL('http://127.0.0.1:12345/', [CSV | TabSeparated | JSON | ... ])

Движок File

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

Преимущества использования движка File:

Выгрузка данных в файл.
Преобразование данных из одного формата в другой.

Свойства движка File:

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

Синтаксис создания таблицы:

sql

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|ALIAS expr1],
    ...
) ENGINE = File([CSV | TabSeparated | JSON | ... ])

Движок Buffer

Движок Buffer предназначен для временного накопления данных в оперативной памяти перед их записью в основную таблицу-приёмник на диске. Он накапливает вставляемые данные в ОЗУ и при достижении определённых пороговых значений автоматически сбрасывает их в приёмник. При этом, если сервер базы данных перезапускается, данные из буфера не теряются - они сохраняются на диске и будут записаны в основную таблицу при следующем запуске. Такой механизм позволяет повысить производительность массовых вставок, сглаживая нагрузку на диск.

Для движка Buffer доступны операции SELECT и INSERT. Операции удаления и обновления не поддерживаются.

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

Для движков семейства MergeTree оптимальна вставка данных батчами - то есть создание части данных при каждой вставке. С помощью Buffer можно аккумулировать несколько одиночных операций INSERT в один батч.

Для повышения производительности вставки рекомендуется использовать батчи размером от 10 строк и более.

Синтаксис создания таблицы:

sql

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|ALIAS expr1],
    ...
) ENGINE = Buffer(database, target_table,
                  num_layers, min_time, max_time, min_rows,
                  max_rows, min_bytes, max_bytes
);

database — имя базы данных.
table — таблица, в которую сбрасываются данные.
num_layers — уровень параллелизма. Физически таблица будет представлена в виде num_layers независимых буферов. Рекомендуемое значение — 16.

Данные сбрасываются из буфера и записываются в таблицу назначения, если выполнены все min-условия или хотя бы одно max-условие.

min_time, max_time — условие времени в секундах от момента первой записи в буфер.
min_rows, max_rows — условие на количество строк в буфере.
min_bytes, max_bytes — условие на количество байт в буфере.

Асинхронная вставка в движке Buffer

Начиная с версии 21.11, для подключений по HTTP в ClickHouse появилась поддержка асинхронной вставки. Это позволяет достигать скорости порядка 15 000 отдельных вставок в секунду.

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

Этот режим настраивается параметрами конфигурации:

async_insert = 1 - включает асинхронный режим вставки (по умолчанию 0).
async_insert_threads - определяет количество потоков для фоновой обработки и вставки данных (по умолчанию 16).
wait_for_async_insert = 0 - клиент не ждёт завершения сброса данных в таблицу, запрос завершается практически мгновенно. Если установить wait_for_async_insert = 1, клиент будет ожидать, пока данные не будут записаны в таблицу.
wait_for_async_insert_timeout - задаёт время ожидания (в секундах) для обработки асинхронной вставки. Значение 0 отключает ожидание.

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

Движок Memory

Движок Memory предназначен для работы с данными, которые не требуют постоянного хранения и могут быть утрачены без критических последствий, но при этом нуждаются в быстром доступе для чтения. Он хранит все данные исключительно в оперативной памяти, что обеспечивает очень высокую скорость чтения и записи. Он поддерживает операции SELECT, INSERT, UPDATE и DELETE, позволяя модифицировать и удалять данные.

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

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

Свойства движка Memory

Эффективен для работы с датасетами объёмом до 100 миллионов строк.
Поддерживает параллельное чтение данных.
Операции чтения и записи не блокируют друг друга.
Данные хранятся без сжатия.
Не поддерживает использование индексов.

Синтаксис создания таблицы:

sql

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|ALIAS expr1],
    ...
) ENGINE = Memory [SETTINGS ...]

Раздел SETTINGS может содержать следующие необязательные параметры:

min_bytes_to_keep — минимальный размер в байтах.
max_bytes_to_keep — максимальный размер в байтах.
min_rows_to_keep — минимальный размер в строках.
max_rows_to_keep — максимальный размер в строках.

Значения по умолчанию — 0. При указании минимального размера также требуется указать максимальный.

Движок Set

Движок Set используется для хранения набора значений, которые применяются в правой части оператора IN в SQL-запросах. Прямое выполнение SELECT из такой таблицы невозможно - она служит исключительно для подстановки значений в условия фильтрации. Движок реализует структуру данных «множество», которая всегда находится в оперативной памяти. Он предназначен для хранения набора неизменяемых данных, к которым требуется быстрый постоянный доступ.

В таблицу Set можно записывать данные с помощью команды INSERT. Персистентность данных (сохранение на диск) задаётся специальной опцией при создании таблицы.

Обычные запросы SELECT к таблице Set недоступны. В такие таблицы можно загружать данные для последующего использования в условиях отбора с оператором IN.

Свойства Set-таблиц

Таблица не поддерживает прямой SELECT.
Возможны дубликаты записей.
Все данные хранятся в оперативной памяти.
Существует опция persistent для хранения данных на диске.

Персистентность (сохранение данных на диск) настраивается через одноимённую опцию при создании таблицы.

Синтаксис создания таблицы:

sql

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|ALIAS expr1],
    ...
) ENGINE = Set
SETTINGS persistent = [1 | 0];

Работа с движком Set

Создайте таблицы MergeTree и Set и сделайте вставки:

sql

CREATE TABLE set_tbl (val UInt32) ENGINE = Set;

CREATE TABLE mtree_tbl
(
    id UInt32,
    val UInt32
)
ENGINE = MergeTree
ORDER BY (id);

INSERT INTO set_tbl SELECT number from numbers(30000);
INSERT INTO mtree_tbl SELECT number, (number + 1) * 10 from numbers(300000000);

Используйте таблицу Set в выражении IN и посмотрите на результат:

sql

SELECT count(*) FROM mtree_tbl WHERE val in set_tbl;

Движок Join

Движок Join предназначен для предварительной подготовки данных, которые будут использоваться в операциях соединения (JOIN) в запросах ClickHouse. В таблицах с этим движком данные полностью загружаются и всегда доступны для быстрого соединения и фильтрации. Движок поддерживает операции чтения (SELECT), вставки (INSERT) и удаления (DELETE), но не позволяет обновлять (UPDATE) уже существующие записи. Это делает его удобным для хранения справочных наборов данных, которые часто используются в JOIN-операциях.

Этот движок особенно полезен, когда для аналитических задач требуется объединить основную таблицу с небольшими справочниками - например, для подстановки ФИО сотрудников или названия департамента.

Свойства движка Join

Таблица используется в правой части секции JOIN или в функции joinGet.
Все данные всегда находятся в оперативной памяти.
Существует опция persistent для хранения данных на диске.
Поддерживаются DELETE-мутации.
Не поддерживается использование в GLOBAL JOIN.
Семплирование не поддерживается.
Индексы и репликация не поддерживаются.

Синтаксис создания таблицы:

sql

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    fld [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    ...
) ENGINE = Join(
    [ANY | ALL],        -- строгость соединения
    [INNER | LEFT | ...],    -- тип соединения
    fld[, fld2, ...]     -- ключевые столбцы секции USING
)
SETTINGS
   persistent = [1 | 0],
   join_use_nulls = [1 | 0],
   max_rows_in_join = 0,
   max_bytes_in_join = 0,
   join_overflow_mode = [THROW | BREAK],
   join_any_take_last_row = [1 | 0];

Параметры и настройка движка Join

Движок Join в ClickHouse настраивается с помощью трёх основных параметров:

Строгость соединения (ANY, ALL):

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

Тип соединения (INNER, LEFT и др.):

Определяет, какой тип JOIN будет поддерживаться этой таблицей. Если попытаться выполнить, например, LEFT JOIN к таблице, созданной с типом INNER, возникнет исключение.

Ключевые столбцы:

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

Эти параметры определяют, как таблица Join будет применяться в SQL-запросах и какие типы соединений поддерживаются.

Вспомогательные параметры Join-таблицы

persistent - включает хранение данных на диске.
join_use_nulls = [1 | 0] - определяет, будут ли пустые ячейки заполняться NULL или дефолтными значениями при соединении.
max_rows_in_join - ограничение на количество строк в хеш-таблице для соединения (по умолчанию 0 - не ограничено).
max_bytes_in_join - ограничение на объём памяти (в байтах) для хеш-таблицы (по умолчанию 0 - не ограничено).
join_overflow_mode - действие при переполнении лимита строк или памяти:
- THROW (по умолчанию) - выбрасывает исключение и завершает запрос.
- BREAK - завершает операцию соединения без ошибки.
join_any_take_last_row - определяет, какую строку присоединять при совпадении ключа:
- 0 - первую найденную в правой таблице,
- 1 - последнюю найденную в

Пример создание таблицы с типом Join

Создайте таблицу с Join для типов соединения INNER со строгостью ANY и проверьте, как отрабатывают корректные и некорректные выборки.

sql

DROP TABLE IF EXISTS mtree_tbl;

CREATE TABLE mtree_tbl
(
    id UInt32,
    val UInt32
)
ENGINE = MergeTree
ORDER BY (id);

INSERT INTO mtree_tbl SELECT number, (number + 1) * 10 FROM numbers(10);

CREATE TABLE join_tbl
ENGINE = Join(ANY, INNER , id) as SELECT id, val * 10 as val
FROM mtree_tbl where id >= 5;

Проверьте данные в таблице join_tbl и сделайте соединение корректным:

sql

SELECT * FROM join_tbl ORDER BY id

SELECT *
FROM mtree_tbl AS mt
ANY INNER JOIN join_tbl jt USING(id);

Дополнительно попробуйте другой тип соединения:

sql

SELECT *
FROM mtree_tbl AS mt
ANY LEFT JOIN join_tbl jt USING(id);

Движок S3

Движок S3 обеспечивает интеграцию с веб-службой Simple Storage Service (S3), предоставляющей возможность хранения и получения любых объёмов данных.

Использование S3 позволяет существенно сэкономить на хранении данных по сравнению с другими движками.

Свойства движка S3

Поддерживает параллельное чтение и запись данных.
Не поддерживает операции ALTER и мутации (UPDATE, DELETE).
Не поддерживает семплирование.
Не поддерживает индексы и репликацию.

Синтаксис создания таблицы:

sql

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    fld [type1] [DEFAULT|ALIAS expr1],
    ...
) ENGINE = S3(path, [aws_access_key_id, aws_secret_access_key,] format, [compression]);

Обязательные параметры:

path — URL-адрес бакета с указанием пути к файлу. Для обращения к нескольким файлам предусмотрены подстановочные знаки: *, ?, {abc,def}.
format — формат файла, например CSV, JSON, TSV.

Необязательные параметры:

aws_access_key_id, aws_secret_access_key — данные пользователя учётной записи AWS могут быть заданы в файле конфигурации.
compression — тип сжатия. Возможные значения: none, gzip/gz, brotli/br, xz/LZMA, zstd/zst.

Таблицы на движке S3 имеют виртуальные колонки: _path и _file.

Движок PostgreSQL

Движок PostgreSQL предоставляет возможность работать с внешними таблицами, находящимися в базе данных PostgreSQL, напрямую из ClickHouse. С помощью этого движка можно выполнять операции чтения (SELECT) и вставки (INSERT) данных, используя ClickHouse как интерфейс доступа к внешним данным. Данные физически хранятся во внешней системе, а не в ClickHouse. Это позволяет интегрировать ClickHouse с PostgreSQL и использовать возможности обеих систем в рамках одной аналитической задачи.

Движок Kafka

Движок Kafka предназначен для интеграции с потоковыми данными из Apache Kafka. Он не хранит данные самостоятельно, а используется для подписки на потоки данных (consumer) и публикации данных (producer) в Kafka-топики. Такой подход позволяет ClickHouse эффективно обрабатывать и анализировать потоковые данные в реальном времени, используя возможности Kafka для обмена сообщениями между системами.

Типы данных

Мы познакомились со специальными движками таблиц в ClickHouse и разобрали, для каких задач подходит каждый из них. Теперь мы переходим к работе с данными: рассмотрим типы данных ClickHouse, создадим таблицы с разными типами полей, определим, когда какой тип использовать, и напишем запросы для выборки и преобразования данных. Также вы узнаете о композитных типах и научитесь работать с JSON.

Простые типы данных

ClickHouse поддерживает широкий набор типов данных, которые можно использовать при создании таблиц. Чтобы посмотреть список всех доступных типов, используйте системное представление system.data_type_families. В нём содержатся имена типов, их алиасы и информация о чувствительности к регистру.

sql

SELECT name, case_insensitive, alias_to
FROM system.data_type_families
order by name

Базовые типы данных и сценарии их применения в ClickHouse

В ClickHouse поддерживается широкий спектр базовых типов данных, которые можно использовать в таблицах для различных задач.

Целые числа

Int8, Int16 ... Int256 - это знаковые целые числа фиксированной длины (от 8 до 256 бит).

UInt8, UInt16 ... UInt256 - это беззнаковые целые числа фиксированной длины (от 8 до 256 бит).

Почему стоит использовать целые числа

Если вы работаете с числами, рекомендуется выбирать целые типы: Int<X> или UInt<X>, где X - разрядность (8, 16, 32, 64, 128, 256). Разрядность определяет, сколько байт (1 байт = 8 бит) требуется для хранения числа.

Целые числа требуют минимальных вычислительных ресурсов и занимают меньше места по сравнению со строками. Для хранения данных с фиксированной точностью, таких как денежные суммы или время в миллисекундах, также лучше использовать целые числа. Например, вместо хранения суммы 1,42 рубля можно хранить 142 копейки.

Числа с плавающей запятой

Float32, Float64 - числа с плавающей точкой (аналогично float и double в C).
Эти типы отлично подходят для статистических и математических расчётов, однако не рекомендуются для хранения денежных значений из-за возможных ошибок округления при вычислениях.

Decimal32(S) ... Decimal256(S) - дробные числа с фиксированной точностью, где S - количество знаков после запятой.
Эти типы идеально подходят для хранения денежных сумм и других значений, где важна точность вычислений.

String (BINARY LARGE OBJECT, BLOB) - строка произвольной длины, способная хранить любые байты, включая нулевые.
Подходит для хранения текстовых и бинарных данных, например, названий категорий или моделей.

FixedString(N) - строка фиксированной длины в N байт.
Рекомендуется использовать для хранения данных с постоянной длиной, таких как IP-адреса или коды валют (например, USD, RUB).

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

Рассмотрим некоторые функции для работы с преобразованием типов и строками в ClickHouse. Примеры помогут понять, как эти функции использовать на практике.

Преобразование типов

sql

SELECT toInt64('012') as v;
-- Результат: 12

SELECT toFloat32('012.10000') as v;
-- Результат: 12.1

SELECT toDecimal32(2, 4) SETTINGS output_format_decimal_trailing_zeros = 1;
-- Результат: 2.0000

SELECT toString(123);
-- Результат: '123'

toInt64() - преобразует строку в 64-битное целое число со знаком.
toFloat32() - преобразует строку подходящего формата в 32-битное число с плавающей точкой.
toDecimal32(value, scale) - возвращает число с фиксированной точностью (например, 2.0000 при scale=4).
toString() - преобразует любой тип данных в строку.

Функции для работы со строками

sql

SELECT lengthUTF8('абв');
-- Результат: 3

SELECT length('абв');
-- Результат: 6

SELECT length(toFixedString('123', 4));
-- Результат: 4

SELECT position('text', 'x');
-- Результат: 3

lengthUTF8() - возвращает длину строки в символах Unicode.
length() - возвращает длину строки в байтах. Например, строка 'абв' занимает 6 байт, так как каждый символ - это 2 байта в UTF-8.
toFixedString(str, N) - возвращает строку фиксированной длины N.
position(haystack, needle) - возвращает позицию подстроки в строке (начиная с 1).

Существуют также функции positionUTF8() и positionCaseInsensitiveUTF8() для поиска с учётом или без учёта регистра в Unicode-строках. Для поиска подстроки с учётом или без учёта регистра можно использовать стандартные операторы LIKE и ILIKE.

Базовые типы данных UUID и IPv4/IPv6 в ClickHouse

В ClickHouse существуют специальные типы данных для хранения уникальных идентификаторов и IP-адресов:

UUID - универсальный уникальный идентификатор, представляющий собой 16-байтовое число. Используется для уникальной идентификации записей.

IPv4, IPv6 - типы для хранения IP-адресов в соответствующих форматах.

Примеры работы с этими типами:

sql

SELECT generateUUIDv4();
-- Пример результата: 3e3adccf-bcbc-48fd-bb06-adce2180f7c9

SELECT toUUID('3e3adccf-bcbc-48fd-bb06-adce2180f7c9');
-- Преобразует строку в UUID: 3e3adccf-bcbc-48fd-bb06-adce2180f7c9

SELECT toTypeName(toIPv4('127.0.0.1'));
-- Результат: IPv4

SELECT notEmpty(toUUID('00000000-0000-0000-0000-000000000000'));
-- Результат: 0

SELECT notEmpty(array(1,2));
-- Результат: 1

generateUUIDv4() - генерирует случайный UUID версии 4.
toUUID() - преобразует строку в UUID.
toIPv4() - преобразует строку в 32-битный IPv4-адрес.
toTypeName() - возвращает строковое имя типа переданного значения.
notEmpty() - проверяет, не является ли значение пустым. Для UUID с нулями вернёт 0, для непустого массива - 1.

Типы данных для работы с датами и временем в ClickHouse

В ClickHouse представлены специальные типы данных для хранения дат и времени:

Date - 2-байтовый тип для хранения дат. Поддерживает ограниченный диапазон дат.
Date32 - 4-байтовый тип, позволяющий хранить даты в более широком диапазоне.
DateTime([timezone]) - тип для хранения даты и времени с точностью до секунды.
Часовой пояс (timezone) - опциональный параметр, который хранится в метаданных столбца, а не в самих данных.
Список поддерживаемых часовых поясов можно посмотреть в системном представлении system.time_zones.
DateTime64(precision, [timezone]) - тип для хранения даты и времени с точностью до наносекунд.
precision - целое число от 0 до 9, определяющее точность (например, 3 - миллисекунды, 6 - микросекунды, 9 - наносекунды).
Часовой пояс также опционален.

Примеры функций для работы с датами и временем

Преобразование строки в дату и время

sql

SELECT toDateTime64('2019-01-01 00:00:00', 4, 'Europe/Moscow');
-- Результат: 2019-01-01 00:00:00.0000

Арифметика с датами

sql

SELECT toDateTime('2017-01-01 00:00:00') + INTERVAL 1 MONTH;
-- Результат: 2017-02-01 00:00:00

Получение текущего времени

sql

SELECT now('Europe/Moscow');

Извлечение части даты

sql

SELECT dateName('month', toDate('2020-02-03'));
-- Результат: February

Функция dateName() позволяет извлекать различные части даты: год, месяц, неделю, день, час, минуту, секунду и т.д.

Композитные типы данных в ClickHouse

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

Массивы (Arrays)

Массивы в ClickHouse могут содержать несколько элементов одного типа данных, включая вложенные массивы.

Особенности массивов в ClickHouse:

Массивы создаются с помощью функции array(T), где T - тип элементов.
Массивы не поддерживают разнотипные элементы - все элементы должны быть одного типа.
Индексация начинается с 1 (первый элемент массива имеет индекс 1).
Размер массива можно узнать с помощью подстолбца size0 - это позволяет получить длину массива без необходимости читать весь столбец.
Для многомерных массивов используется подстолбец sizeN-1, где N - нужное измерение.

Пример. Создайте таблицу заказов, где в поле items будет храниться список покупок. Запишите данные, содержащие массив, в таблицу:

sql

CREATE TABLE orders (
    order_id UInt64,
    items Array(String)
) ENGINE = MergeTree() ORDER BY order_id;

INSERT INTO orders (order_id, items) VALUES
(1, ['laptop', 'mouse', 'keyboard']),(2, ['book', 'pen', 'notebook']);

Выполните запрос и посмотрите на результат.

sql

select order_id, items[2], items.size0, toTypeName(items) from orders;

Кортежи (Tuples)

Кортежи — это упорядоченные коллекции элементов разных типов данных: строк, чисел или массивов. Кортежи обычно используются для хранения связанных данных, например информации о пользователе (имя, фамилия, адрес, телефон).

Примет. Создайте таблицу, где поле «покупки» будет массивом для хранения кортежей с информацией обо всех покупках клиента: дат, ID товара и цены. Для этого выполните код:

sql

CREATE TABLE purchases (
id UInt64,
customer String,
purchase Array(Tuple(Date, UInt16, Decimal32(2)))
) ENGINE = MergeTree ORDER BY id;

INSERT INTO purchases (id, customer, purchase)
VALUES (1, 'Alice', [(toDate('2023-04-14'), 3, 19.99),(toDate('2023-04-15'), 10, 39.19)]);

Выполните запрос и посмотрите на результат.

sql

select id, customer, purchase[1] from purchases

Вложенные структуры (Nested Structures) в ClickHouse

Вложенная структура данных (Nested) в ClickHouse похожа на вложенную таблицу. При создании такого поля указываются имена и типы столбцов, как и в обычном запросе CREATE. Для каждой строки основной таблицы может быть произвольное количество строк во вложенной структуре.

Особенности работы с вложенными структурами

Вложенные структуры позволяют хранить связанные данные в виде массивов кортежей (например, для хранения истории изменений или связанных сущностей).
Для управления форматом хранения вложенных структур используется переменная flatten_nested.

Параметры переменной `flatten_nested`:

flatten_nested = 1 - поля внутри поля с типом данных Nested хранятся как отдельные поля типа массив. Это значение по умолчанию.
flatten_nested = 0 - поле типа Nested хранится как массив кортежей.

Пример. Сначала рассмотрим пример с flatten_nested = 0:

sql

SET flatten_nested = 0;

CREATE TABLE sales
(
    date     Date,
    product  String,
    quantity UInt32,
    price    Float32,
    region_info Nested (
        name String,
        population UInt64,
        status String
        )
) ENGINE = MergeTree ORDER BY date;

Теперь проверьте, что создалось:

sql

SHOW CREATE TABLE sales

В ClickHouse запрос SHOW CREATE TABLE sales выводит SQL-запрос, который можно использовать для создания таблицы sales.

Дальше произведите вставку и выборку записей:

sql

INSERT INTO sales (date, product, quantity, price, region_info)
VALUES('2022-01-01','Product A',100,2.99,[('East',100000,'developed')]);
SELECT * FROM sales;

Теперь рассмотрим пример с flatten_nested = 1:

sql

SET flatten_nested = 1;

CREATE TABLE sales_data_arr
(
    date     Date,
    product  String,
    quantity UInt32,
    price    Float32,
    region_info Nested (
        name String,
        population UInt64,
        status String
        )
) ENGINE = MergeTree ORDER BY date;

Проверьте, как создалась таблица:

sql

SHOW CREATE TABLE sales_data_arr

Обратите внимание, что вложенная структура теперь выглядит как набор полей с типом данных Array. То есть поля region_info в таблице не существует, но есть три других поля. В дальнейшем это нужно учитывать и при вставке и выборке данных.

sql

INSERT INTO sales_data_arr (date, product, quantity, price,
`region_info.name`, `region_info.population`,`region_info.status`)
VALUES ('2022-01-01','Product A',1000, 1.99, ['East', 'West'], [300, 200], ['done', 'dev']);

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

Тип Map в ClickHouse

Тип данных Map предназначен для хранения пар ключ-значение. В качестве ключей могут использоваться строки или числа, а значения могут быть любого типа, включая другие Map и Array.

Изучите работу Map на примере. Запустите код и посмотрите на результат:

sql

CREATE TABLE table_map (a Map(String, UInt64)) ENGINE=Memory;
INSERT INTO table_map VALUES ({'key1':1, 'key2':10}), ({'key1':2,'key2':20}), ({'key1':3,'key2':30});

SELECT a, a['key2'], a['some_key'] FROM table_map;

Тип Enum в ClickHouse

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

Применение Enum

Enum отлично подходит для хранения статусов операций, когда количество возможных значений ограничено. Например, для статусов заказа: Created, Processing, Shipped, Delivered, Cancelled. Использование Enum избавляет от необходимости хранить длинные текстовые строки или запоминать, что означает каждая цифра.

Преимущества использования Enum

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

Изучите работу Enum на примере. Запустите код и посмотрите на результат:

sql

CREATE TABLE t_enum
(
    x Enum('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog;

INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello');

SELECT * FROM t_enum;

Атрибуты LowCardinality и Nullable в ClickHouse

В ClickHouse для каждого поля таблицы можно задать дополнительные атрибуты, которые влияют на способ хранения и обработки данных. Два самых важных атрибута - это LowCardinality и Nullable.

Атрибут LowCardinality

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

Когда использовать LowCardinality:

Если у поля невысокое количество уникальных значений (например, до 100 000).
Особенно эффективно для строковых столбцов (например, статусы, категории, типы событий).

Преимущества:

Уменьшает объем занимаемой памяти.
Ускоряет выполнение запросов SELECT, особенно при группировке и фильтрации по этому полю.

Когда не стоит использовать:

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

Настройки:

Максимальный размер глобального словаря регулируется параметром low_cardinality_max_dictionary_size (по умолчанию 8192).
Если словарь переполняется, ClickHouse автоматически создает новый словарь для следующей части данных. Это поведение можно отключить с помощью параметра low_cardinality_use_single_dictionary_for_part = 1.

Атрибут Nullable

Nullable позволяет хранить в столбце значения NULL. По умолчанию в ClickHouse нельзя записывать NULL - если попытаться это сделать, возникнет ошибка "Cannot convert NULL to a non-nullable type".

Как работает Nullable:

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

Ограничения:

Атрибут Nullable нельзя применять к составным типам данных: Array, Tuple, Nested.
Использование Nullable может снизить производительность запросов на больших объемах данных, поэтому применять его стоит только при реальной необходимости.

Пример. Запустите этот код:

sql

CREATE TABLE attr_tbl (
    a Nullable(UInt32),
    b LowCardinality(String)
) ENGINE = MergeTree ORDER BY tuple();

INSERT INTO attr_tbl VALUES (NULL, 'test'), (1, 'test');
SELECT toTypeName(a), toTypeName(b) from attr_tbl;

Приведение типов в ClickHouse

В процессе работы с ClickHouse часто возникает необходимость преобразовывать типы данных. Это может понадобиться в следующих случаях:

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

Для этих целей в ClickHouse реализован широкий набор функций преобразования типов. Вот основные из них:

Функция `reinterpret(x, T)`

Интерпретирует значение x как тип T на уровне байтов.
Может вернуть неожиданный результат, если типы несовместимы.

Функция `CAST(x as T)`

Преобразует значение x к типу T.
Если преобразование невозможно, будет ошибка.
Если значение выходит за границы нового типа, происходит переполнение.
Например:
sql
```
SELECT CAST(-1 AS UInt8); -- Результат: 255
```

Семейство функций `to<Type>()`

Преобразует значение к нужному типу (например, toInt8(), toFloat64(), toDecimal32(), и др.).
Позволяет указать, что делать при невозможности преобразования с помощью постфиксов:
OrNull - возвращает NULL при ошибке.
OrZero - возвращает 0 при ошибке.
OrDefault - возвращает значение по умолчанию при ошибке.

Примеры:

sql

SELECT toInt8('123'); -- 123
SELECT toInt8('abc'); -- Ошибка
SELECT toInt8OrNull('abc'); -- NULL
SELECT toInt8OrZero('abc'); -- 0
SELECT toInt8OrDefault('abc'); -- 0 (для числовых типов)

SELECT reinterpret(toInt8(-1.1), 'UInt8') -- 255
SELECT CAST(2.3 as UInt8) -- 2
SELECT CAST(NULL, 'String') -- Error
SELECT toFloat64OrNull('a.1') -- NULL

Тип данных JSON в ClickHouse

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

sql

SET allow_experimental_object_type = 1;

Как работает тип JSON

Под капотом ClickHouse автоматически создаёт динамические вложенные столбцы для хранения структуры JSON, что обеспечивает быстрый доступ к данным.
Тип JSON особенно полезен, если структура данных заранее неизвестна или может меняться (например, при сборе логов из разных сервисов).
Если структура известна заранее и фиксирована, для вложенных данных лучше использовать тип Nested.

Чтобы протестировать работу с типом JSON, выполните запросы:

sql

SET allow_experimental_object_type = 1; -- включаем новый тип данных
CREATE TABLE json (o JSON) ENGINE = Memory; -- создаём таблицу
INSERT INTO json VALUES ('{"a": 1, "b": { "c": 2, "d": [1, 2, 3] }}');
SELECT o,o.a, o.b.c, o.b.d[3] FROM json;

Обращение к вложенным данным внутри JSON может производиться так же, как к Nested-структурам, с указанием пути через точку.

Добавьте строку, поменяв структуру данных:

sql

INSERT INTO json
VALUES ('{"gender": "male", "age": 40, "children_ages": [10, 15]}');

Вставка прошла успешно, несмотря на другую структуру данных.

**Преобразование JSON-объектов в строку и обратно: из строки в JSON-объект. **

JSON_QUERY(json, path) парсит JSON и извлекает значение как JSON-массив или JSON-объект.

sql

SELECT JSON_QUERY('{"obj":["val_1", "val_2"]}', '$.obj[*]')

Проверка на валидность JSON-документа

isValidJSON(json) проверяет, является ли переданная строка валидным JSON-значением.

sql

SELECT isValidJSON('{"a": "hello", "b": [-100, 200.0, 300]}')

Извлечение полей и ключей

JSONExtractFloat(json[, indices_or_keys]…) парсит JSON и извлекает значение.
sql
```
SELECT JSONExtractFloat('{"a": "hello", "b": [-100, 200.1, 300]}', 'b', 2)
```
JSONExtractKeys парсит строку JSON и извлекает ключи.
sql
```
SELECT JSONExtractKeys('{"a": "hello", "b": [-100, 200.0, 300]}')
```

Проверка на вхождение значения в JSON-документ

JSONHas(json[, indices_or_keys]…) — если значение существует в документе JSON, то возвращается 1.

sql

SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 'b')

Проверка типа элемента JSON-документа

sql

SELECT JSONType('{"a": "hello", "b": [-100, 200.0, 300]}')
SELECT JSONType('["a", "b", "c"]')

Запросы к JSON-документу при помощи JSONpath

sql

SELECT JSON_QUERY('{"obj":["val_1", "val_2"]}', '$.obj[*]')
SELECT JSON_VALUE('{"obj":["val_1", "val_2"]}', '$.obj[*]')
SELECT JSON_QUERY('{"array":[[0, 1, 2, 3, 4, 5], [0, -1, -2, -3, -4, -5]]}', '$.array[*][0 to 2, 4]')
SELECT JSON_VALUE('{"prop":200}', '$.prop')

Также JSON-данные в таблицах можно хранить в виде строк. Работать с таким полем можно при помощи функций из семейства функций обработки и запросов к JSON-строкам, в том числе используя JSONpath-запросы. Пример работы с JSON в таблицах в виде текстового поля выглядит так:

sql

CREATE TABLE tbl_with_txt_json (
    j String) ENGINE = Log;

INSERT INTO tbl_with_txt_json VALUES
    ('{"v":[{"a":"1000", "b":"2000", "ts": "2020-10-10 01:02:03"},{"a":"200"},{"t":"2020-10-20 01:01:01"}]}'),('{"v":[{"a":"3000", "ts": "2020-10-10 03:03:03"},{"a":"4000"},{"a":"3000","t":"2021-10-20 02:02:02"}]}');

SELECT
    JSONExtractRaw(j, 'v') as p_json,
    JSON_EXISTS(j, '$.v[*]'),
    JSON_QUERY(j, '$.v[*].a'),
    JSON_QUERY(j, '$.v[*].b')
FROM tbl_with_txt_json

Выводы по теме: движки таблиц и их свойства в ClickHouse

Универсальность и специализация движков

MergeTree - основной универсальный движок ClickHouse, поддерживающий все ключевые операции (SELECT, INSERT, DELETE, UPDATE), индексы и хранение на диске. Это оптимальный выбор для аналитических задач с большими объёмами данных.
Log - простой движок для хранения данных в порядке вставки. Подходит для небольших или промежуточных таблиц, поддерживает SELECT и INSERT, но не поддерживает DELETE и UPDATE.
Memory - хранит данные в оперативной памяти, обеспечивает максимальную скорость работы, но данные теряются при перезапуске сервера. Используется для временных данных и промежуточных расчётов.
Set и Join - специализированные движки для оптимизации работы с операциями IN и JOIN. Используются для хранения множеств и справочников.
Buffer - движок-буфер для ускорения массовой загрузки данных перед записью в основную таблицу.
URL, PostgreSQL, Kafka - интеграционные движки для работы с внешними источниками данных.

Практические рекомендации

Для аналитики и хранения больших объёмов данных используйте MergeTree.
Для временных или промежуточных данных - Memory или Log.
Для интеграции с внешними источниками - URL, PostgreSQL, Kafka.
Для оптимизации операций IN и JOIN - Set и Join.
Выбор движка влияет на производительность, доступность операций, возможность масштабирования и интеграции.

В целом, ClickHouse - это высокопроизводительная колоночная СУБД, в которой выбор движка таблицы позволяет гибко подстраивать систему под конкретные задачи: от временных вычислений до интеграции с внешними источниками и построения отказоустойчивых кластеров.

Структура базы данных ​

Основные движки таблиц в ClickHouse ​

Семейство движков *MergeTree ​

Разновидности движков семейства MergeTree ​

Базовый MergeTree ​

Расширенные движки на базе MergeTree ​

Движок SummingMergeTree ​

Движок AggregatingMergeTree ​

Движок ReplacingMergeTree ​

Ключевые особенности ReplacingMergeTree: ​

Движок CollapsingMergeTree ​

VersionedCollapsingMergeTree ​

Работа с состояниями ​

Семейство движков *Log ​

Основные свойства *Log-таблиц ​

Ограничения семейства *Log ​

Разновидности Log-движков ​

Движок TinyLog ​

Движок Log ​

Движок StripeLog ​

Движок EmbeddedRocksDB ​

Особенности движка EmbeddedRocksDB ​

Движок URL ​

Особенности движка URL ​

Движок File ​

Преимущества использования движка File: ​

Свойства движка File: ​

Движок Buffer ​

Асинхронная вставка в движке Buffer ​

Движок Memory ​

Свойства движка Memory ​

Движок Set ​

Свойства Set-таблиц ​

Работа с движком Set ​

Движок Join ​

Свойства движка Join ​

Параметры и настройка движка Join ​

Вспомогательные параметры Join-таблицы ​

Пример создание таблицы с типом Join ​

Движок S3 ​

Свойства движка S3 ​

Движок PostgreSQL ​

Движок Kafka ​

Типы данных ​

Простые типы данных ​

Базовые типы данных и сценарии их применения в ClickHouse ​

Целые числа ​

Числа с плавающей запятой ​

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

Преобразование типов ​

Функции для работы со строками ​

Базовые типы данных UUID и IPv4/IPv6 в ClickHouse ​

Примеры работы с этими типами: ​

Типы данных для работы с датами и временем в ClickHouse ​

Примеры функций для работы с датами и временем ​

Преобразование строки в дату и время ​

Арифметика с датами ​

Получение текущего времени ​

Извлечение части даты ​

Композитные типы данных в ClickHouse ​

Массивы (Arrays) ​

Кортежи (Tuples) ​

Вложенные структуры (Nested Structures) в ClickHouse ​

Особенности работы с вложенными структурами ​

Параметры переменной flatten_nested: ​

Тип Map в ClickHouse ​

Тип Enum в ClickHouse ​

Применение Enum ​

Преимущества использования Enum ​

Атрибуты LowCardinality и Nullable в ClickHouse ​

Атрибут LowCardinality ​

Атрибут Nullable ​

Приведение типов в ClickHouse ​

Функция reinterpret(x, T) ​

Функция CAST(x as T) ​

Семейство функций to<Type>() ​

Тип данных JSON в ClickHouse ​

Как работает тип JSON ​

Выводы по теме: движки таблиц и их свойства в ClickHouse ​

Универсальность и специализация движков ​

Структура базы данных

Основные движки таблиц в ClickHouse

Семейство движков *MergeTree

Разновидности движков семейства MergeTree

Базовый MergeTree

Расширенные движки на базе MergeTree

Движок SummingMergeTree

Движок AggregatingMergeTree

Движок ReplacingMergeTree

Ключевые особенности ReplacingMergeTree:

Движок CollapsingMergeTree

VersionedCollapsingMergeTree

Работа с состояниями

Семейство движков *Log

Основные свойства *Log-таблиц

Ограничения семейства *Log

Разновидности Log-движков

Движок TinyLog

Движок Log

Движок StripeLog

Движок EmbeddedRocksDB

Особенности движка EmbeddedRocksDB

Движок URL

Особенности движка URL

Движок File

Преимущества использования движка File:

Свойства движка File:

Движок Buffer

Асинхронная вставка в движке Buffer

Движок Memory

Свойства движка Memory

Движок Set

Свойства Set-таблиц

Работа с движком Set

Движок Join

Свойства движка Join

Параметры и настройка движка Join

Вспомогательные параметры Join-таблицы

Пример создание таблицы с типом Join

Движок S3

Свойства движка S3

Движок PostgreSQL

Движок Kafka

Типы данных

Простые типы данных

Базовые типы данных и сценарии их применения в ClickHouse

Целые числа

Числа с плавающей запятой

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

Преобразование типов

Функции для работы со строками

Базовые типы данных UUID и IPv4/IPv6 в ClickHouse

Примеры работы с этими типами:

Типы данных для работы с датами и временем в ClickHouse

Примеры функций для работы с датами и временем

Преобразование строки в дату и время

Арифметика с датами

Получение текущего времени

Извлечение части даты

Композитные типы данных в ClickHouse

Массивы (Arrays)

Кортежи (Tuples)

Вложенные структуры (Nested Structures) в ClickHouse

Особенности работы с вложенными структурами

Параметры переменной `flatten_nested`:

Тип Map в ClickHouse

Тип Enum в ClickHouse

Применение Enum

Преимущества использования Enum

Атрибуты LowCardinality и Nullable в ClickHouse

Атрибут LowCardinality

Атрибут Nullable

Приведение типов в ClickHouse

Функция `reinterpret(x, T)`

Функция `CAST(x as T)`

Семейство функций `to<Type>()`

Тип данных JSON в ClickHouse

Как работает тип JSON

Выводы по теме: движки таблиц и их свойства в ClickHouse

Универсальность и специализация движков