Данная статья — это подборка заметок про ClickHouse, которые я формировал в процессе прохождения курсов с сайта learn.clickhouse.com, чтения официальной документации, а также разных статей в интернете.

Contents

1 Первичные ключи в ClickHouse
2 Системные базы данных
3 Low cardinality колонки (Enum и LowCardinality типы данных)
- 3.1 Enumerations
- 3.2 LowCardinality
4 Как ClickHouse хранит данные используя parts и primary keys
5 Deduplication (Дедупликация)
6 ClickHouse Server Architecture
- 6.1 Обработка запросов ClickHouse
- 6.2 Еще раз про хранение данных и выполнение запроса внутри ClickHouse
7 Как работать с Parts и Partitions в ClickHouse. Что это такое?
8 Dictionary
9 Использование JOIN в ClickHouse. Типы объединений, оптимизация
10 Что такое мутации в ClickHouse? Почему их следует избегать? Как они работают?
- 10.1 Почему нужно избегать мутаций в ClickHouse?
11 Подходы к обновлению и удалению данных в ClickHouse
12 EXPLAIN query
- 12.1 Описание типов EXPLAIN
13 Использованные материалы для подготовки статьи

Первичные ключи в ClickHouse

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

Эта концепция granularity создает разреженный индекс (sparse index), который легко помещается в памяти. Granules (гранулы) представляют собой полосу наименьшего объема данных столбца, который обрабатывается во время запросов SELECT.

ПРИМЕЧАНИЕ. Другими словами, если вы запустите SELECT для одной строки, поток (thread), обрабатывающий этот запрос, обработает 8192 строки. Но имейте в виду, что при анализе больших наборов данных извлечение отдельных строк встречается нечасто. И даже если вам нужна одна строка, ClickHouse работает настолько быстро, что компромисс в производительности незаметен.

Определение первичного ключа

Давайте посмотрим на простой пример. Предположим, у вас есть таблица с двумя столбцами — целым числом и строкой фиксированной длины 1 (другими словами, символом):

CREATE TABLE helloworld
(
    x UInt32,
    y FixedString(1),
    PRIMARY KEY(x)
)

CREATE TABLE helloworld

(

x UInt32,

y FixedString(1),

PRIMARY KEY(x)

)

ПРИМЕЧАНИЕ. Столбец x является первичным ключом, а также порядком сортировки. Фактически, вам не нужно указывать первичный ключ в ClickHouse, если в вашей таблице есть предложение ORDER BY. (Первичным ключом по умолчанию будет предложение ORDER BY , если вы не укажете предложение PRIMARY KEY). Следующее определение helloworld идентично приведенному выше:

CREATE TABLE helloworld
(
    x UInt32,
    y FixedString(1)
)
ORDER BY (x)

CREATE TABLE helloworld

(

x UInt32,

y FixedString(1)

)

ORDER BY (x)

Давайте заполним таблицу с помощью функции numbers, которая генерирует последовательность чисел, начиная с 0 и заканчивая любым аргументом, который вы передаете. Следующий оператор вставляет 10 000 000 строк в таблицу helloworld:

INSERT INTO helloworld
   SELECT 
      number AS x, 
      char(modulo(rand(),26) + 97) AS y
   FROM numbers(10000000)

INSERT INTO helloworld

SELECT

number AS x,

char(modulo(rand(),26) + 97) AS y

FROM numbers(10000000)

Значения x — это просто целые числа от 0 до 10 миллионов, а значения y — случайные символы между «a» и «z».

SELECT * FROM helloworld LIMIT 10

0	u
1	n
2	z
3	i
4	h
5	d
6	n
7	m
8	f
9	j

SELECT * FROM helloworld LIMIT 10

0 u

1 n

2 z

3 i

4 h

5 d

6 n

7 m

8 f

9 j

Теперь, когда у вас есть таблица с 10 миллионами строк, давайте выполним несколько запросов.

Понимание granules (гранул)

Таблица helloworld имеет 10 миллионов строк, поэтому таблица логически разбита на 10 000 000/8 192 = 1 221 гранулу, причем каждая гранула состоит из 8 192 строк — за исключением последней гранулы, в которой всего 5 760 строк (не очень часто такое количество строки будут состоять из чисел, кратных 8192).

Гранула — это наименьший неделимый набор данных, который ClickHouse считывает при поиске по строкам. Это означает, что вместо чтения отдельных строк ClickHouse всегда читает (потоково и параллельно) целую группу (гранулу) строк.

Значения столбцов физически не хранятся внутри гранул: гранулы — это просто логическая организация значений столбцов для обработки запросов.

Первичный индекс имеет одну запись на гранулу.

Первичный индекс создается на основе гранул. Этот индекс представляет собой несжатый файл плоского массива (primary.idx), содержащий так называемые числовые индексные метки, начинающиеся с 0.

Индекс первичного ключа для этой таблицы содержит только первую строку каждой гранулы, поэтому он будет содержать запись, указывающую на первую строку (0, ‘u’), 8193-ю строку (8192, ‘d’), 16385 -я строка (16384,’n’) и так далее.

В чем преимущество этого редкого первичного индекса? Предположим, вы ищете определенную строку:

SELECT y FROM helloworld WHERE x = 20000

1	SELECT y FROM helloworld WHERE x = 20000

В зависимости от того, как сортируются ваши данные, ClickHouse знает, что результат вышеуказанного запроса SELECT должен находиться в грануле 3, и, используя индекс первичного ключа, ClickHouse знает, как быстро получить строки из гранулы 3, так что это единственная гранула, которая обрабатывается для вашего запроса. В ответе видно, что была обработана только 1 гранула:

┌─y─┐
│ q │
└───┘

1 rows in set. 
Elapsed: 0.211 sec. 
Processed 8.19 thousand rows, 
40.97 KB (38.87 thousand rows/s., 194.39 KB/s.)

┌─y─┐

│ q │

└───┘

1 rows in set.

Elapsed: 0.211 sec.

Processed 8.19 thousand rows,

40.97 KB (38.87 thousand rows/s., 194.39 KB/s.)

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

SELECT count(*) FROM helloworld WHERE y = 'c'

1	SELECT count(*) FROM helloworld WHERE y = 'c'

Поскольку вы фильтруете только по y, вам необходимо обработать все 10 миллионов строк:

┌─count()─┐
│  385070 │
└─────────┘

1 rows in set. 
Elapsed: 0.228 sec. 
Processed 10.00 million rows, 
10.00 MB (43.91 million rows/s., 43.92 MB/s.)

┌─count()─┐

│ 385070 │

└─────────┘

1 rows in set.

Elapsed: 0.228 sec.

Processed 10.00 million rows,

10.00 MB (43.91 million rows/s., 43.92 MB/s.)

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

ПРИМЕЧАНИЕ. Если вы запускаете больше запросов с фильтрацией по y , чем по любому другому столбцу, то y должен быть первой записью в кортеже первичного ключа.

Дизайн индекса для больших объемов

Предположим у нас есть таблица с 8,87 миллионами записей.

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

Поиск записи в B(+)-Tree структуре данных имеет среднюю временную сложность O(log n); точнее, log_b n = log_2 n / log_2 b где b– коэффициент ветвления B(+)-Tree и n – количество индексированных строк. Поскольку bобычно их размер составляет от нескольких сотен до нескольких тысяч, B(+)-Trees это очень мелкие структуры, и для поиска записей требуется несколько операций поиска по диску. При 8,87 миллионах строк и коэффициенте ветвления 1000 в среднем требуется 2,3 обращения к диску. За эту возможность приходится платить: дополнительные накладные расходы на диск и память, более высокие затраты на вставку при добавлении новых строк в таблицу и записей в индекс, а иногда и на перебалансировку B-дерева.

Учитывая проблемы, связанные с индексами B-Tree, табличные движки в ClickHouse используют другой подход. Семейство движков ClickHouse MergeTree было разработано и оптимизировано для обработки огромных объемов данных. Эти таблицы предназначены для приема миллионов вставок строк в секунду и хранения очень больших (сотни петабайт) объемов данных. Данные быстро записываются в таблицу по частям, при этом правила объединения частей применяются в фоновом режиме. В ClickHouse каждая часть имеет свой первичный индекс. Когда части объединяются, первичные индексы объединенной части также объединяются. В очень больших масштабах, для которых предназначен ClickHouse, крайне важно максимально эффективно использовать диск и память. Таким образом, вместо индексации каждой строки первичный индекс для части имеет одну запись индекса (известную как «mark» или «метка») на группу строк (называемую «granule» или «гранула») — этот метод называется разреженным индексом .

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

Чтобы обеспечить эффективное использование памяти, можно в инструкции Create table указать первичный ключ, который содержит только столбцы, по которым фильтруются наши запросы. Первичный индекс, основанный на первичном ключе, полностью загружается в основную память.

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

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

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

На втором этапе (чтение данных) ClickHouse находит выбранные гранулы, чтобы передать все их строки в механизм ClickHouse, чтобы найти строки, которые действительно соответствуют запросу.

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

Значение размера гранулы таблицы 8192 по умолчанию отлично подходит практически для всех случаев использования.

Пример определения Primary Key:

CREATE TABLE my_table1 (
  user_id UInt32,
  message String,
  timestamp DateTime, 
  metric Decimal(30,2),
  PRIMARY KEY (user_id, timestamp)
)
ENGINE = MergeTree

CREATE TABLE my_table1 (

user_id UInt32,

message String,

timestamp DateTime,

metric Decimal(30,2),

PRIMARY KEY (user_id, timestamp)

)

ENGINE = MergeTree

Если определяется одновременно PRIMARY KEY и ORDER BY, то PRIMARY KEY должен обязательно являться префиксом для ORDER BY tuple:

CREATE TABLE my_table1 (
  user_id UInt32,
  message String,
  timestamp DateTime,
  metric Decimal(30,2),
  PRIMARY KEY (user_id, timestamp)
)
ENGINE = MergeTree
ORDER BY (user_id, timestamp, message)

CREATE TABLE my_table1 (

user_id UInt32,

message String,

timestamp DateTime,

metric Decimal(30,2),

PRIMARY KEY (user_id, timestamp)

)

ENGINE = MergeTree

ORDER BY (user_id, timestamp, message)

PRIMARY KEY не может содержать меньше полей, чем ORDER BY.

Primary indexes должны помещаться в память, иначе ClickHouse не сможет запуститься или сервис может упасть.

Кейсы, когда может понадобиться создание дополнительных Primary Indexes

Создание двух таблиц для одинаковых данных.
Использование Projection — вы используете одну таблицу, но ClickHouse создает скрытую таблицу, которая хранит данные сортированные другим способом.
Использование materialized view:
- Данные хранятся в отдельной таблице на основе SELECT запроса.
- Сортировка данных выполняется на основе SELECT запроса.
Определяется skipping index

Системные базы данных

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

База данных INFORMATION_SCHEMA (с псевдонимом information_schema) содержит набор представлений стандарта ANSI, содержащих информацию о метаданных объектов базы данных (system.columns, system.databases и system.tables).

Low cardinality колонки (Enum и LowCardinality типы данных)

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

Используйте тип данных Enum для определения перечисления.
Оберните столбец в тип LowCardinality.

Enumerations

Начнем с примера перечисления. Значения Enum имеют формат string=integer. Целочисленное значение сохраняется, но в запросах вы используете строковые значения. Вот простой пример столбца device_type, где возможными типами устройств являются только «сервер», «контейнер» или «маршрутизатор»:

CREATE TABLE enum_demo
(
   device_id UInt32,
   device_type Enum('server' = 1, 'container' = 2, 'router' = 3)
)
ORDER BY tuple()

CREATE TABLE enum_demo

(

device_id UInt32,

device_type Enum('server' = 1, 'container' = 2, 'router' = 3)

)

ORDER BY tuple()

Значения device_type вставляются в виде строк:

INSERT INTO enum_demo VALUES 
   (123, 'router'),
   (456, 'container')

INSERT INTO enum_demo VALUES

(123, 'router'),

(456, 'container')

Ошибка возникает, если вы попытаетесь вставить строку, которая не является частью перечисления:

INSERT INTO enum_demo VALUES (789, 'pod')

Code: 36. DB::Exception: Unknown element 'pod' for enum: While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (BAD_ARGUMENTS)

INSERT INTO enum_demo VALUES (789, 'pod')

Code: 36. DB::Exception: Unknown element 'pod' for enum: While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (BAD_ARGUMENTS)

Используйте столбец Enum как строку в запросах:

SELECT * 
FROM enum_demo 
WHERE device_type = 'container'

SELECT *

FROM enum_demo

WHERE device_type = 'container'

Примечание.

Enumerations — это здорово, но у них есть один недостаток — в них нельзя добавлять значения без ALTER TABLE. Если вы точно знаете, какие значения может принимать столбец, то Enum — хорошее решение. Если у вас есть столбец, значения которого различаются, но общая уникальность значений низкая, вместо этого рассмотрите вариант LowCardinality.

LowCardinality

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

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

Пример

CREATE TABLE lowcardinality_demo
(
   x UInt64,
   y LowCardinality(String)
)
ORDER BY x

CREATE TABLE lowcardinality_demo

(

x UInt64,

y LowCardinality(String)

)

ORDER BY x

Как ClickHouse хранит данные используя parts и primary keys

Еще раз про primary keys

Table Engine определяет как и где данные хранятся в ClickHouse
Если Primary Key не определен явно, то он будет сформирован на основе условия Order By
Primary Key не уникальный для каждой строки
Используйте для Primary Key те колонки, по которым чаще всего выполняется поиск

Как вставляются данные в таблицу

Вставки выполняются в виде bulk
Каждый bulk INSERT создает part (part сохраняется в его собственную директорию)
Part может содержать десятки тысяч или даже миллионов строк

Part, part folder, bulk insert, merging of parts

Файлы столбцов сортируются по первичному ключу.

Каждый столбец в «части» имеет неизменяемый файл, содержащий только данные столбца.

granule — логическое разбиение строк внутри несжатого блока (по умолчанию 8192 строки);
primary key — порядок сортировки таблицы;
primary index — in-memory index, состоящий из значений primary keys первой строки каждой гранулы;
part — директория с файлами, состоящая из column files и index file подмножества данных таблицы.

Каждый bulk insert создает part

Со временем части можно объединить в фоновом режиме. Вот почему она называется таблицей MergeTree.

Схема объединения двух частей таблицы:

После мержинга, неиспользуемые parts удаляются:

Смерженные parts продолжают мержиться:

Partitions (Партиции) в ClickHouse

Partition (партиция) — это логическое объединение записей таблицы по заданному критерию. При доступе к данным ClickHouse использует минимально возможное подмножество разделов.

Пример определения партиции при создании таблицы (партиция по YYYYMM):

CREATE TABEL partition_demo
(
  user_id UInt32,
  message String,
  timestamp DateTime,
  metric Decimal(30,2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
PRIMARY KEY user_id

CREATE TABEL partition_demo

(

user_id UInt32,

message String,

timestamp DateTime,

metric Decimal(30,2)

)

ENGINE = MergeTree()

PARTITION BY toYYYYMM(timestamp)

PRIMARY KEY user_id

В большинстве случаев, вам не нужен ключ партицирования. Лучше используйте вместо партиций primary key.
В большинстве других случаев, вам не нужен partition key с большей гранулярностью, чем месяц.
- Никогда не используйте большое число партиций.
- Параметр max_partitions_per_insert_block по умолчанию равен 100, самое большое число партиций во вставляемом блоке, которое вы можете иметь.

Для чего используются в таком случае partitions (партиции) в ClickHouse?

Data Management:

Если partition key также является первым ключом в primary key, это потенциально улучшает сжатие.
Существует целый набор операций, которые можно выполнять над партициями (Detach Partition, Drop, Replace, Move, Freeze, Update, Attach и т.д.). То есть мы можем обновлять данные в таблице на основе партиций.

Merging partitions

Partitions доступны только для семейства MergeTree engines, а также для Materialized Views, которые основаны на MergeTree Family Tables.
Объединяются только части, имеющие одинаковый partition key:
- Вы увидите это, когда посмотрите на папки, созданные для каждой part.
- Значение partition key отображается в именах папок parts.

Хранение данных в parts с учетом partition key

Таблица состоит из data parts, отсортированных по первичному ключу.

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

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

Данные, относящиеся к разным партициям, разбиваются на разные куски. В фоновом режиме ClickHouse выполняет слияния (merge) parts данных для более эффективного хранения.

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

Parts данных могут храниться в формате Wide или Compact.

В формате Wide каждый столбец хранится в отдельном файле,
а в формате Compact все столбцы хранятся в одном файле.

Формат Compact может быть полезен для повышения производительности при частом добавлении небольших объемов данных.

Формат хранения определяется настройками движка min_bytes_for_wide_part и min_rows_for_wide_part. Если число байт или строк в part данных меньше значения, указанного в соответствующей настройке, тогда этот кусок данных хранится в формате Compact. В противном случае кусок данных хранится в формате Wide. Если ни одна из настроек не задана, куски данных хранятся в формате Wide.

Каждый part данных логически делится на гранулы. ClickHouse не разбивает строки и значения и гранула всегда содержит целое число строк. Первая строка гранулы помечается значением первичного ключа для этой строки (засечка). Для каждого куска данных ClickHouse создаёт файл с засечками (индексный файл). Для каждого столбца, независимо от того, входит он в первичный ключ или нет, ClickHouse также сохраняет эти же засечки. Засечки используются для поиска данных напрямую в файлах столбцов.

Размер гранул оганичен настройками движка index_granularity и index_granularity_bytes. Количество строк в грануле лежит в диапазоне [1, index_granularity], в зависимости от размера строк. Размер гранулы может превышать index_granularity_bytes в том случае, когда размер единственной строки в грануле превышает значение настройки. В этом случае, размер гранулы равен размеру строки.

Логика хранения данных MergeTree Table в ClickHouse

На следующем рисунке показана логика структуры хранения таблицы MergeTree:

Файлы данных: action_id.bin , avatar_id.bin
Файлы с идентификаторами меток: action_id.mrk2 , avatar_id.mrk2
Индекс первичного ключа: primary.idx
Индекс ключа раздела: minmax_regionidx и minmax_region_name.idx
Индекс пропуска: skp_idx_avatar_id_minmax.idx

Взаимосвязь между объектами выглядит примерно следующим образом:

Дополнительная схема связи между primary index mark и granules:

Deduplication (Дедупликация)

В этом разделе описываются варианты дедупликации данных в ClickHouse:

как реализовать дедупликацию в ClickHouse с помощью механизма таблиц ReplacingMergeTree, и как использовать этот механизм таблиц для реализации решения upsert.
как реализовать дедупликацию в ClickHouse с помощью механизма таблиц CollapsingMergeTree, который удаляет строки, которые «отменяют» друг друга, с помощью специального sign column.
как реализовать дедупликацию в ClickHouse с помощью механизмов таблиц VersionedCollapsingMergeTree.

Дедупликация — это процесс удаления повторяющихся строк набора данных. В базе данных OLTP это делается легко, поскольку каждая строка имеет уникальный первичный ключ, но за счет более медленной вставки. Каждую вставленную строку необходимо сначала найти и, если она найдена, заменить.

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

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

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

1. ReplacingMergeTree table engine

Благодаря этому table engine во время слияний удаляются повторяющиеся строки с одинаковым ключом сортировки (sorting key). ReplacingMergeTree — хороший вариант для эмуляции поведения upsert (когда вы хотите, чтобы запросы возвращали последнюю вставленную строку).

2. Collapsing rows (Свертывание строк)

Engines таблиц CollapsingMergeTree и VersionedCollapsingMergeTree используют логику, при которой существующая строка «отменяется» и вставляется новая строка.

ReplacingMergeTree реализовать немного проще, но оба механизма таблиц схожи в том, что удаленные строки «в конечном итоге» удаляются во время слияния, поэтому ваши запросы должны учитывать возможность возврата нескольких строк.

Для таблицы ReplacingMergeTree последняя вставленная строка — это строка, которая не удалена.
Для таблицы CollapsingMergeTree используется столбец sign типа Int8, которому присвоено значение 1 или -1. Чтобы отменить строку, вы вставляете строку с тем же ключом сортировки, но меняете столбец знака на -1.

ClickHouse Server Architecture

ClickHouse single node query model

Обработка запросов ClickHouse

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

Конструкция первичного индекса ClickHouse основана на алгоритме двоичного поиска , который эффективно ( временная сложность O(log2 n)) находит положение целевого значения в отсортированном массиве.

На диаграмме показано, как ClickHouse обычно выполняет запрос:

Шаг 1: Первичный индекс из задействованной таблицы загружается в основную память.
Шаг 2: Обычно с помощью двоичного поиска по записям индекса ClickHouse выбирает блоки строк, которые потенциально содержат строки, соответствующие предложению WHERE запроса.
Шаг 3: Выбранные блоки строк параллельно передаются в механизм запросов ClickHouse для дальнейшей обработки, а результат запроса передается вызывающей стороне.

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

Чем меньше данных ClickHouse необходимо передать с диска в основную память, тем быстрее будет выполняться запрос. Объем данных, необходимых для потоковой передачи с диска, можно свести к минимуму путем (1) правильного использования первичных индексов и (2) предварительного вычисления агрегатов.
Потоковую передачу и фактическую обработку данных можно ускорить за счет (3) увеличения уровня параллелизма, используемого внутри механизма обработки запросов ClickHouse.

Предложение таблицы ORDER BY определяет, как данные упорядочиваются на диске, а также записи основного индекса.

Еще раз про хранение данных и выполнение запроса внутри ClickHouse

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

Вот как данные сохраняются во время вставки:

Для обработки данных ClickHouse делит столбцы на «гранулы» (сжатые), это наименьший неделимый набор данных для обработки данных:

При создании таблицы свойство SETTINGS index_granularity = 8192; (значение по умолчанию) позволяет указать размер гранул. Очевидно, что увеличение или уменьшение этого значения может повлиять на производительность запроса.

Первичный индекс, связанный с первичным ключом, имеет одну запись на «гранулу», это и есть метки. Каждая метка несет в себе данные из первой строки связанной гранулы:

Clickhouse монтирует основной индексный файл в память (если памяти недостаточно, Clickhouse сообщит об ошибке)

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

EXPLAIN indexes = 1
SELECT URL, count(URL) AS Count
FROM hits_UserID_URL
WHERE UserID = 854
GROUP BY URL
ORDER BY Count DESC
LIMIT 10;

EXPLAIN indexes = 1

SELECT URL, count(URL) AS Count

FROM hits_UserID_URL

WHERE UserID = 854

GROUP BY URL

ORDER BY Count DESC

LIMIT 10;

Log

...Executor): Key condition: (column 0 in [854, 854])
...Executor): Running binary search on index range for part all_1_9_2 (1083 marks)
...Executor): Found (LEFT) boundary mark: 1
...Executor): Found (RIGHT) boundary mark: 2
...Executor): Found continuous range in 19 steps
...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,
1/1083 marks by primary key, 1 marks to read from 1 ranges
...Reading ...approx. 8192 rows starting from 8192

...Executor): Key condition: (column 0 in [854, 854])

...Executor): Running binary search on index range for part all_1_9_2 (1083 marks)

...Executor): Found (LEFT) boundary mark: 1

...Executor): Found (RIGHT) boundary mark: 2

...Executor): Found continuous range in 19 steps

...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,

1/1083 marks by primary key, 1 marks to read from 1 ranges

...Reading ...approx. 8192 rows starting from 8192

Explain

 Expression (Projection) 
   Limit (preliminary LIMIT (without OFFSET)) 
     Sorting (Sorting for ORDER BY) 
       Expression (Before ORDER BY) 
         Aggregating 
           Expression (Before GROUP BY) 
             Filter (WHERE) 
               SettingQuotaAndLimits (Set limit &amp; quota after reading from storage) 
                 ReadFromMergeTree 
                   Indexes: 
                     PrimaryKey 
                       Keys: 
                         UserID 
                       Condition: (UserID in [854, 854]) 
                       Parts: 1/1 
                       Granules: 1/1083

Expression (Projection)

Limit (preliminary LIMIT (without OFFSET))

Sorting (Sorting for ORDER BY)

Expression (Before ORDER BY)

Aggregating

Expression (Before GROUP BY)

Filter (WHERE)

SettingQuotaAndLimits (Set limit & quota after reading from storage)

ReadFromMergeTree

Indexes:

PrimaryKey

Keys:

UserID

Condition: (UserID in [854, 854])

Parts: 1/1

Granules: 1/1083

Как только гранула будет идентифицирована, Clickhouse должен знать физическое местоположение гранулы 1.

В файле markхранятся все физические местоположения «гранул»; один файл отметок на столбец:

Благодаря этим файлам mrk Clickhouse может найти блок в сжатом файле (.bin), следовательно, он может распаковать его в памяти, а затем, благодаря , granule_offset найти гранулу(ы), которые соответствуют:

Уровень параллелизма, используемый в конвейере запросов ClickHouse, задается с помощью параметра max_threads = 2. По умолчанию max_threads равно количеству ядер ЦП, которое ClickHouse видит на машине, на которой он работает.

Как работать с Parts и Partitions в ClickHouse. Что это такое?

Part — это физический файл на диске, в котором хранится часть данных таблицы.
Partition (партиция) — это логическое разделение данных таблицы, созданное с использованием Partition Key.

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

Parts — Части таблиц ClickHouse

Parts — это физические файлы на диске. По умолчанию все файлы, связанные с данными, находятся в каталоге /var/lib/clickhouse. Каждая таблица Merge Tree в ClickHouse имеет уникальный путь к каталогу для хранения частей. Вы можете получить доступ к фактическому расположению частей, именам частей, информации о разделах (если таковые имеются) и некоторой другой ценной информации из таблицы system.parts.

Ниже показан пример результата запроса из таблицы system.parts. Здесь в part_type Wide означает, что каждый столбец хранится в отдельном файле в файловой системе. С другой стороны, Compact означает, что все столбцы хранятся в одном файле файловой системы. Также в partition столбце tuple() — означает, что эта таблица не партицирована.

SELECT
    substr(table, 1, 22),
    partition AS prt,
    name,
    part_type,
    path
FROM system.parts
WHERE database = 'sampleDatasets'
ORDER BY
    table ASC,
    partition ASC,
    name ASC

Query id: 2a9462a8-7e74-4c0f-a89a-0bd2a31d0a46

┌─substring(table, 1, 22)─┬─prt─────┬─name──────────┬─part_type─┬─path──────────────────────────────────────────────────────────────────────────────┐
│ .inner_id.ca612a5e-5ee  │ tuple() │ all_1_1_1     │ Compact   │ /var/lib/clickhouse/store/4c6/4c6dfe9a-b697-4f9a-928f-f829bf44fb5c/all_1_1_1/     │
│ opensky                 │ tuple() │ all_1_1_1_5   │ Wide      │ /var/lib/clickhouse/store/1af/1afc664b-0a25-443a-a317-532532434753/all_1_1_1_5/   │
│ opensky                 │ tuple() │ all_2_2_1_5   │ Wide      │ /var/lib/clickhouse/store/1af/1afc664b-0a25-443a-a317-532532434753/all_2_2_1_5/   │
│ opensky                 │ tuple() │ all_3_3_0_5   │ Wide      │ /var/lib/clickhouse/store/1af/1afc664b-0a25-443a-a317-532532434753/all_3_3_0_5/   │
│ opensky                 │ tuple() │ all_4_4_0_5   │ Compact   │ /var/lib/clickhouse/store/1af/1afc664b-0a25-443a-a317-532532434753/all_4_4_0_5/   │
│ openskyDeletedRecord    │ tuple() │ all_2_2_2     │ Compact   │ /var/lib/clickhouse/store/5b0/5b0e6758-ae46-4c25-9599-f2cddc362b5e/all_2_2_2/     │
│ opensky_1000000         │ tuple() │ all_1_1_0     │ Wide      │ /var/lib/clickhouse/store/a2a/a2aa62d7-65bb-4bb2-bc5b-2c3d1befa147/all_1_1_0/     │
│ opensky_freeze_restore  │ tuple() │ all_1_1_1     │ Wide      │ /var/lib/clickhouse/store/2d0/2d0e6ad7-6fd4-408e-8c18-b39416ea8ff1/all_1_1_1/     │
│ opensky_freeze_restore  │ tuple() │ all_2_2_1     │ Wide      │ /var/lib/clickhouse/store/2d0/2d0e6ad7-6fd4-408e-8c18-b39416ea8ff1/all_2_2_1/     │
│ opensky_freeze_restore  │ tuple() │ all_3_3_0     │ Wide      │ /var/lib/clickhouse/store/2d0/2d0e6ad7-6fd4-408e-8c18-b39416ea8ff1/all_3_3_0/     │
│ opensky_freeze_restore  │ tuple() │ all_4_4_0     │ Compact   │ /var/lib/clickhouse/store/2d0/2d0e6ad7-6fd4-408e-8c18-b39416ea8ff1/all_4_4_0/     │
│ opensky_redo_test       │ tuple() │ all_1_2_1     │ Compact   │ /var/lib/clickhouse/store/e99/e99f1ab9-6d21-4d01-8f02-9f2aa0e6b45a/all_1_2_1/     │
│ opensky_redo_test2      │ tuple() │ all_103_104_1 │ Compact   │ /var/lib/clickhouse/store/fac/facadfc3-1c5b-4775-bea7-53c262f7e237/all_103_104_1/ │
│ test_table              │ tuple() │ all_1_2_1     │ Compact   │ /var/lib/clickhouse/store/024/024a3a77-4ca9-4761-a2f1-4b36eb4ece0e/all_1_2_1/     │
└─────────────────────────┴─────────┴───────────────┴───────────┴───────────────────────────────────────────────────────────────────────────────────┘

SELECT

substr(table, 1, 22),

partition AS prt,

name,

part_type,

path

FROM system.parts

WHERE database = 'sampleDatasets'

ORDER BY

table ASC,

partition ASC,

name ASC

Query id: 2a9462a8-7e74-4c0f-a89a-0bd2a31d0a46

┌─substring(table, 1, 22)─┬─prt─────┬─name──────────┬─part_type─┬─path──────────────────────────────────────────────────────────────────────────────┐

│ .inner_id.ca612a5e-5ee │ tuple() │ all_1_1_1 │ Compact │ /var/lib/clickhouse/store/4c6/4c6dfe9a-b697-4f9a-928f-f829bf44fb5c/all_1_1_1/ │

│ opensky │ tuple() │ all_1_1_1_5 │ Wide │ /var/lib/clickhouse/store/1af/1afc664b-0a25-443a-a317-532532434753/all_1_1_1_5/ │

│ opensky │ tuple() │ all_2_2_1_5 │ Wide │ /var/lib/clickhouse/store/1af/1afc664b-0a25-443a-a317-532532434753/all_2_2_1_5/ │

│ opensky │ tuple() │ all_3_3_0_5 │ Wide │ /var/lib/clickhouse/store/1af/1afc664b-0a25-443a-a317-532532434753/all_3_3_0_5/ │

│ opensky │ tuple() │ all_4_4_0_5 │ Compact │ /var/lib/clickhouse/store/1af/1afc664b-0a25-443a-a317-532532434753/all_4_4_0_5/ │

│ openskyDeletedRecord │ tuple() │ all_2_2_2 │ Compact │ /var/lib/clickhouse/store/5b0/5b0e6758-ae46-4c25-9599-f2cddc362b5e/all_2_2_2/ │

│ opensky_1000000 │ tuple() │ all_1_1_0 │ Wide │ /var/lib/clickhouse/store/a2a/a2aa62d7-65bb-4bb2-bc5b-2c3d1befa147/all_1_1_0/ │

│ opensky_freeze_restore │ tuple() │ all_1_1_1 │ Wide │ /var/lib/clickhouse/store/2d0/2d0e6ad7-6fd4-408e-8c18-b39416ea8ff1/all_1_1_1/ │

│ opensky_freeze_restore │ tuple() │ all_2_2_1 │ Wide │ /var/lib/clickhouse/store/2d0/2d0e6ad7-6fd4-408e-8c18-b39416ea8ff1/all_2_2_1/ │

│ opensky_freeze_restore │ tuple() │ all_3_3_0 │ Wide │ /var/lib/clickhouse/store/2d0/2d0e6ad7-6fd4-408e-8c18-b39416ea8ff1/all_3_3_0/ │

│ opensky_freeze_restore │ tuple() │ all_4_4_0 │ Compact │ /var/lib/clickhouse/store/2d0/2d0e6ad7-6fd4-408e-8c18-b39416ea8ff1/all_4_4_0/ │

│ opensky_redo_test │ tuple() │ all_1_2_1 │ Compact │ /var/lib/clickhouse/store/e99/e99f1ab9-6d21-4d01-8f02-9f2aa0e6b45a/all_1_2_1/ │

│ opensky_redo_test2 │ tuple() │ all_103_104_1 │ Compact │ /var/lib/clickhouse/store/fac/facadfc3-1c5b-4775-bea7-53c262f7e237/all_103_104_1/ │

│ test_table │ tuple() │ all_1_2_1 │ Compact │ /var/lib/clickhouse/store/024/024a3a77-4ca9-4761-a2f1-4b36eb4ece0e/all_1_2_1/ │

└─────────────────────────┴─────────┴───────────────┴───────────┴───────────────────────────────────────────────────────────────────────────────────┘

Также можно получить доступ к parts таблицы в папке /var/lib/clickhouse/data/<DBNAME>/<TABLENAME> в виде символической ссылки.

root@clickhouse01:/var/lib/clickhouse/data/sampleDatasets/opensky# ls -al
total 36
drwxr-x--- 7 clickhouse clickhouse 4096 Nov 21 10:00 .
drwxr-x--- 3 clickhouse clickhouse 4096 Oct 16 13:13 ..
drwxr-x--- 2 clickhouse clickhouse 4096 Nov 21 09:49 all_1_1_1_5
drwxr-x--- 2 clickhouse clickhouse 4096 Nov 21 09:49 all_2_2_1_5
drwxr-x--- 2 clickhouse clickhouse 4096 Nov 21 09:49 all_3_3_0_5
drwxr-x--- 2 clickhouse clickhouse 4096 Nov 21 09:49 all_4_4_0_5
drwxr-x--- 2 clickhouse clickhouse 4096 Nov  9 11:33 detached
-rw-r----- 1 clickhouse clickhouse    1 Oct 16 13:13 format_version.txt
-rw-r----- 1 clickhouse clickhouse  100 Nov 21 09:49 mutation_5.txt

root@clickhouse01:/var/lib/clickhouse/data/sampleDatasets/opensky# ls -al

total 36

drwxr-x--- 7 clickhouse clickhouse 4096 Nov 21 10:00 .

drwxr-x--- 3 clickhouse clickhouse 4096 Oct 16 13:13 ..

drwxr-x--- 2 clickhouse clickhouse 4096 Nov 21 09:49 all_1_1_1_5

drwxr-x--- 2 clickhouse clickhouse 4096 Nov 21 09:49 all_2_2_1_5

drwxr-x--- 2 clickhouse clickhouse 4096 Nov 21 09:49 all_3_3_0_5

drwxr-x--- 2 clickhouse clickhouse 4096 Nov 21 09:49 all_4_4_0_5

drwxr-x--- 2 clickhouse clickhouse 4096 Nov 9 11:33 detached

-rw-r----- 1 clickhouse clickhouse 1 Oct 16 13:13 format_version.txt

-rw-r----- 1 clickhouse clickhouse 100 Nov 21 09:49 mutation_5.txt

Таблица opensky в database «sampleDatasets» состоит из четырех parts. Каждая part имеет свою собственную директорию, а их имена начинаются с all_. Если говорить о части all_3_3_0_5, то:

3 — минимальный номер блока данных (data block).
3 — максимальный номер блока данных (data block).
0 — chunk level (глубина дерева слияния, из которого он сформирован).
5 — версия мутации (если часть мутирована).

Другой вариант получения этой информации: из таблицы system.parts

SELECT
    name,
    partition_id,
    min_block_number,
    max_block_number,
    level,
    data_version
FROM system.parts
WHERE (database = 'sampleDatasets') AND (table = 'opensky') AND (name = 'all_3_3_0_5')

Query id: 0f2bb404-11a0-4df3-b1c1-7941698b9560

┌─name────────┬─partition_id─┬─min_block_number─┬─max_block_number─┬─level─┬─data_version─┐
│ all_3_3_0_5 │ all          │                3 │                3 │     0 │            5 │
└─────────────┴──────────────┴──────────────────┴──────────────────┴───────┴──────────────┘

SELECT

name,

partition_id,

min_block_number,

max_block_number,

level,

data_version

FROM system.parts

WHERE (database = 'sampleDatasets') AND (table = 'opensky') AND (name = 'all_3_3_0_5')

Query id: 0f2bb404-11a0-4df3-b1c1-7941698b9560

┌─name────────┬─partition_id─┬─min_block_number─┬─max_block_number─┬─level─┬─data_version─┐

│ all_3_3_0_5 │ all │ 3 │ 3 │ 0 │ 5 │

└─────────────┴──────────────┴──────────────────┴──────────────────┴───────┴──────────────┘

Partitions — Партиции таблиц ClickHouse

Получить доступ к информации о Partitions таблиц Merge Tree можно из таблицы system.parts. Однако столбец Partition теперь представлен как нечто иное, чем tuple().

Для создания партицированной таблицы (partitioned table), прежде всего, вам необходимо использовать PARTITION BY expr при создании таблицы.

Например, предложение PARTITION BY toYYYYMMDD(start_time) создает ежедневную партицию на основе столбца start_time.

В следующем примере имена партиций и частей различаются. Кроме того, имена частей не начинаются со слова all, а начинаются с индикатора partition.
Partitioning — это что-то вроде логического разделения, но части располагаются в виде физических файлов. Одна партиция может содержать одну или несколько частей.

SELECT
    partition,
    name,
    active
FROM system.parts
WHERE (table = 'backups') AND (database = 'RECMAN')


┌─partition─┬─name───────────┬─active─┐
│ 20221017  │ 20221017_1_1_0 │      1 │
│ 20221018  │ 20221018_2_2_0 │      1 │
│ 20221114  │ 20221114_3_3_0 │      1 │
└───────────┴────────────────┴────────┘

SELECT

partition,

name,

active

FROM system.parts

WHERE (table = 'backups') AND (database = 'RECMAN')

┌─partition─┬─name───────────┬─active─┐

│ 20221017 │ 20221017_1_1_0 │ 1 │

│ 20221018 │ 20221018_2_2_0 │ 1 │

│ 20221114 │ 20221114_3_3_0 │ 1 │

└───────────┴────────────────┴────────┘

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

Чтобы обратиться к определенной партиции, необходимо указать условия в предложении where или использовать скрытый столбец _partition_id.
Лучшим способом является обращение к партиции в условии WHERE. Но в некоторых случаях нам нужно использовать _partition_id.

Пример запроса к партицированной таблице.
Предположим, наша таблица (recoDB.opensky_partitioned) партицирована по столбцу lastseen. Мы можем получить доступ к определенным партициям, как с помощью столбца partition key, так и скрытого столбца _partition_id. Также мы можем запросить top 10 partition с помощью _partition_id.

SELECT count()
FROM recoDB.opensky_partitioned
WHERE toDate(lastseen) = '2019-02-25'

┌─count()─┐
│   69480 │
└─────────┘
#####################################

SELECT count()
FROM recoDB.opensky_partitioned
WHERE _partition_id = '20190225'

┌─count()─┐
│   69480 │
└─────────┘

SELECT count()

FROM recoDB.opensky_partitioned

WHERE toDate(lastseen) = '2019-02-25'

┌─count()─┐

│ 69480 │

└─────────┘

#####################################

SELECT count()

FROM recoDB.opensky_partitioned

WHERE _partition_id = '20190225'

┌─count()─┐

│ 69480 │

└─────────┘

еще пример

SELECT
    _partition_id,
    count()
FROM recoDB.opensky_partitioned
GROUP BY _partition_id
ORDER BY 2 DESC
LIMIT 10

┌─_partition_id─┬─count()─┐
│ 20190524      │   90358 │
│ 20190531      │   87917 │
│ 20190523      │   87023 │
│ 20190530      │   86945 │
│ 20190425      │   85524 │
│ 20190516      │   85348 │
│ 20190515      │   85287 │
│ 20190522      │   85056 │
│ 20190517      │   84986 │
│ 20190510      │   84585 │
└───────────────┴─────────┘

SELECT

_partition_id,

count()

FROM recoDB.opensky_partitioned

GROUP BY _partition_id

ORDER BY 2 DESC

LIMIT 10

┌─_partition_id─┬─count()─┐

│ 20190524 │ 90358 │

│ 20190531 │ 87917 │

│ 20190523 │ 87023 │

│ 20190530 │ 86945 │

│ 20190425 │ 85524 │

│ 20190516 │ 85348 │

│ 20190515 │ 85287 │

│ 20190522 │ 85056 │

│ 20190517 │ 84986 │

│ 20190510 │ 84585 │

└───────────────┴─────────┘

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

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

Операции манипулирования частями и партициями Таблицы (Manipulating Partitions and Parts)

Под манипулированием подразумеваются операции создания, изменения или удаления Parts/Partitions в таблице ClickHouse.

Примечание: Далее будет рассмотрен пример с таблицей opensky_partitioned, которая партицирована с помощью выражения PARTITION BY toYYYYMMDD(lastseen) в базе данных sampleDatasets.

DETACH/ATTACH PARTITION|PART — Операции отсоединения и присоединения партиции или части

С помощью операции DETACH указанные Parts или Partitions можно переместить в папку detached. Пользователь не может получить доступ к связанным Parts или Partitions, пока они присоеденены (attached). По умолчанию detached папка находится в каталоге /var/lib/clickhouse/data/<DATABASE_NAME>/<TABLE_NAME>.

С другой стороны, операция ATTACH — это присоединение частей или разделов из detached папки. Синтаксис операций DETACH и ATTACH в ClickHouse показан ниже:

#DETACH PART/PARTITION
ALTER TABLE &lt;DATABASE_NAME&gt;.&lt;TABLE_NAME&gt; [ON CLUSTER &lt;CLUSTER_NAME&gt;] DETACH PARTITION|PART &lt;PARTITION_EXPRESSION&gt;

#ATTACH PART/PARTITION
ALTER TABLE &lt;DATABASE_NAME&gt;.&lt;TABLE_NAME&gt; [ON CLUSTER &lt;CLUSTER_NAME&gt;] ATTACH PARTITION|PART &lt;PARTITION_EXPRESSION&gt;

#DETACH PART/PARTITION

ALTER TABLE <DATABASE_NAME>.<TABLE_NAME> [ON CLUSTER <CLUSTER_NAME>] DETACH PARTITION|PART <PARTITION_EXPRESSION>

#ATTACH PART/PARTITION

ALTER TABLE <DATABASE_NAME>.<TABLE_NAME> [ON CLUSTER <CLUSTER_NAME>] ATTACH PARTITION|PART <PARTITION_EXPRESSION>

Здесь partition expression должно быть имя PARTITION, идентификатор PARTITION или само выражение.

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

Сначала мы обнаружили партицию и имя части на данную дату. Как видите, эта дата находится в партиции «20190415», которая состоит из двух parts.

SELECT
    partition,
    name,
    partition_id
FROM system.parts
WHERE (database = 'sampleDatasets') AND (table = 'opensky_partitioned') AND (partition_id IN (
    SELECT DISTINCT _partition_id
    FROM sampleDatasets.opensky_partitioned
    WHERE toDate(lastseen) = '2019-04-15'
))


┌─partition─┬─name─────────────┬─partition_id─┐
│ 20190415  │ 20190415_62_62_0 │ 20190415     │
│ 20190415  │ 20190415_78_78_0 │ 20190415     │
└───────────┴──────────────────┴──────────────┘

SELECT

partition,

name,

partition_id

FROM system.parts

WHERE (database = 'sampleDatasets') AND (table = 'opensky_partitioned') AND (partition_id IN (

SELECT DISTINCT _partition_id

FROM sampleDatasets.opensky_partitioned

WHERE toDate(lastseen) = '2019-04-15'

))

┌─partition─┬─name─────────────┬─partition_id─┐

│ 20190415 │ 20190415_62_62_0 │ 20190415 │

│ 20190415 │ 20190415_78_78_0 │ 20190415 │

└───────────┴──────────────────┴──────────────┘

Затем DETACH партицию «20190415» и выполним тот же тест с одной из parts.

-- Count related date before detached
SELECT count()
FROM sampleDatasets.opensky_partitioned
WHERE toDate(lastseen) = '2019-04-15'
┌─count()─┐
│   78647 │
└─────────┘

-- detach partition
ALTER TABLE sampleDatasets.opensky_partitioned
    DETACH PARTITION 20190415
Ok.

-- Count related date after detached
SELECT count()
FROM sampleDatasets.opensky_partitioned
WHERE toDate(lastseen) = '2019-04-15'
┌─count()─┐
│       0 │
└─────────┘

-- Count related date before detached

SELECT count()

FROM sampleDatasets.opensky_partitioned

WHERE toDate(lastseen) = '2019-04-15'

┌─count()─┐

│ 78647 │

└─────────┘

-- detach partition

ALTER TABLE sampleDatasets.opensky_partitioned

DETACH PARTITION 20190415

Ok.

-- Count related date after detached

SELECT count()

FROM sampleDatasets.opensky_partitioned

WHERE toDate(lastseen) = '2019-04-15'

┌─count()─┐

│ 0 │

└─────────┘

Как ранее уже описывалось, партиции представляют собой логическое разделение таблиц и включают в себя хотя бы одну часть. В нашем примере партиция 20190415 состоит из двух parts. Пока партиции отсоеденены (detached), партиции все эти части перемещаются в папку /var/lib/clickhouse/data/<DATABASE_NAME>/<TABLE_NAME>/detached.

root@server:~# ls -1 /var/lib/clickhouse/data/sampleDatasets/opensky_partitioned/detached
20190415_62_62_0
20190415_78_78_0

root@server:~# ls -1 /var/lib/clickhouse/data/sampleDatasets/opensky_partitioned/detached

20190415_62_62_0

20190415_78_78_0

Теперь мы ATTACH партицию 20190415 следующим образом:

--attach partition
ALTER TABLE sampleDatasets.opensky_partitioned
    ATTACH PARTITION 20190415

Ok.
--Count related date after attach partition
SELECT count()
FROM sampleDatasets.opensky_partitioned
WHERE toDate(lastseen) = '2019-04-15'
┌─count()─┐
│   78647 │
└─────────┘

--attach partition

ALTER TABLE sampleDatasets.opensky_partitioned

ATTACH PARTITION 20190415

Ok.

--Count related date after attach partition

SELECT count()

FROM sampleDatasets.opensky_partitioned

WHERE toDate(lastseen) = '2019-04-15'

┌─count()─┐

│ 78647 │

└─────────┘

Давайте проведем тот же тест для part.

--find parts for given date
SELECT
    partition,
    name,
    partition_id
FROM system.parts
WHERE (database = 'sampleDatasets') AND (table = 'opensky_partitioned') AND (partition_id IN (
    SELECT DISTINCT _partition_id
    FROM sampleDatasets.opensky_partitioned
    WHERE toDate(lastseen) = '2019-04-15'
))
┌─partition─┬─name───────────────┬─partition_id─┐
│ 20190415  │ 20190415_142_142_0 │ 20190415     │
│ 20190415  │ 20190415_143_143_0 │ 20190415     │
└───────────┴────────────────────┴──────────────┘

-- Count before detach part operation
SELECT count()
FROM sampleDatasets.opensky_partitioned
WHERE toDate(lastseen) = '2019-04-15'
┌─count()─┐
│   78647 │
└─────────┘

-- detach any of the parts
ALTER TABLE sampleDatasets.opensky_partitioned
    DETACH PART '20190415_142_142_0'
Ok.

-- Count after detach part operation
SELECT count()
FROM sampleDatasets.opensky_partitioned
WHERE toDate(lastseen) = '2019-04-15'
┌─count()─┐
│   78517 │
└─────────┘

-- Attach related part
ALTER TABLE sampleDatasets.opensky_partitioned
    ATTACH PART '20190415_142_142_0'

-- Count after attach the part
SELECT count()
FROM sampleDatasets.opensky_partitioned
WHERE toDate(lastseen) = '2019-04-15'
┌─count()─┐
│   78647 │
└─────────┘

--find parts for given date

SELECT

partition,

name,

partition_id

FROM system.parts

WHERE (database = 'sampleDatasets') AND (table = 'opensky_partitioned') AND (partition_id IN (

SELECT DISTINCT _partition_id

FROM sampleDatasets.opensky_partitioned

WHERE toDate(lastseen) = '2019-04-15'

))

┌─partition─┬─name───────────────┬─partition_id─┐

│ 20190415 │ 20190415_142_142_0 │ 20190415 │

│ 20190415 │ 20190415_143_143_0 │ 20190415 │

└───────────┴────────────────────┴──────────────┘

-- Count before detach part operation

SELECT count()

FROM sampleDatasets.opensky_partitioned

WHERE toDate(lastseen) = '2019-04-15'

┌─count()─┐

│ 78647 │

└─────────┘

-- detach any of the parts

ALTER TABLE sampleDatasets.opensky_partitioned

DETACH PART '20190415_142_142_0'

Ok.

-- Count after detach part operation

SELECT count()

FROM sampleDatasets.opensky_partitioned

WHERE toDate(lastseen) = '2019-04-15'

┌─count()─┐

│ 78517 │

└─────────┘

-- Attach related part

ALTER TABLE sampleDatasets.opensky_partitioned

ATTACH PART '20190415_142_142_0'

-- Count after attach the part

SELECT count()

FROM sampleDatasets.opensky_partitioned

WHERE toDate(lastseen) = '2019-04-15'

┌─count()─┐

│ 78647 │

└─────────┘

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

ALTER TABLE &lt;DESTINATION_TABLE&gt; [ON CLUSTER &lt;CLUSTER_NAME&gt;] ATTACH PARTITION &lt;PARTITION EXPRESSION&gt; FROM &lt;SOURCE_TABLE&gt;

1	ALTER TABLE <DESTINATION_TABLE> [ON CLUSTER <CLUSTER_NAME>] ATTACH PARTITION <PARTITION EXPRESSION> FROM <SOURCE_TABLE>

Для запуска этой команды таблицы источника и назначения имеют одно и то же:

Одинаковая структура таблиц.
Partition key, тот же order by kye и тот же primary key.
Политика хранения (Storage policy).

DROP PARTITION/PART

DROP удаляет указанную Part или Partition из таблицы. Удаленная part или partition помечается как неактивный в таблице system.parts.

Эта часть/партиция остается в папке /var/lib/clickhouse/data/<имя_базы_данных>/<имя_таблицы>/ еще примерно в течение 10 минут после операции удаления.

Вы можете найти синтаксис команды drop и пример операции удаления, как показано ниже:

-- Drop from table itself
ALTER TABLE &lt;DATABASE_NAME&gt;.&lt;TABLE_NAME&gt; [ON CLUSTER &lt;CLUSTER_NAME&gt;] DROP PARTITION|PART 'PART/PARTITON EXPRESSION'

-- Remove specified part/partition from detached folder
ALTER TABLE &lt;DATABASE_NAME&gt;.&lt;TABLE_NAME&gt; [ON CLUSTER &lt;CLUSTER_NAME&gt;] DROP DETACHED PARTITION|PART 'PART/PARTITON EXPRESSION'

-- Drop from table itself

ALTER TABLE <DATABASE_NAME>.<TABLE_NAME> [ON CLUSTER <CLUSTER_NAME>] DROP PARTITION|PART 'PART/PARTITON EXPRESSION'

-- Remove specified part/partition from detached folder

ALTER TABLE <DATABASE_NAME>.<TABLE_NAME> [ON CLUSTER <CLUSTER_NAME>] DROP DETACHED PARTITION|PART 'PART/PARTITON EXPRESSION'

-- Specified part/partition is active before drop operation
┌─partition─┬─name───────────────┬─partition_id─┬─active─┐
│ 20190415  │ 20190415_147_147_0 │ 20190415     │      1 │
└───────────┴────────────────────┴──────────────┴────────┘

-- Drop partition
ALTER TABLE sampleDatasets.opensky_partitioned
    DROP PARTITION 20190415

-- Specified part/partition is not active(active=0) after drop partition
┌─partition─┬─name───────────────┬─partition_id─┬─active─┐
│ 20190415  │ 20190415_147_147_1 │ 20190415     │      0 │
└───────────┴────────────────────┴──────────────┴────────┘

-- Specified part/partition is completely deleted after 10 minutes

-- Specified part/partition is active before drop operation

┌─partition─┬─name───────────────┬─partition_id─┬─active─┐

│ 20190415 │ 20190415_147_147_0 │ 20190415 │ 1 │

└───────────┴────────────────────┴──────────────┴────────┘

-- Drop partition

ALTER TABLE sampleDatasets.opensky_partitioned

DROP PARTITION 20190415

-- Specified part/partition is not active(active=0) after drop partition

┌─partition─┬─name───────────────┬─partition_id─┬─active─┐

│ 20190415 │ 20190415_147_147_1 │ 20190415 │ 0 │

└───────────┴────────────────────┴──────────────┴────────┘

-- Specified part/partition is completely deleted after 10 minutes

MOVE PARTITION/PART

Вы можете перемещать партиции в другую таблицу в Clickhouse. В этом случае исходная и целевая таблицы имеют одинаковую структуру, partition by key, order by key, primary key, политику хранения и engine family.

Другой вариант перемещения — перемещение part или partition на другой диск или том для таблиц merge tree engine.

Пример перемещения партиции в другую таблицу:

-- Usage: ALTER TABLE &lt;DATABASE_NAME.SOURCE_TABLE&gt; [ON CLUSTER &lt;CLUSTER_NAME&gt;] MOVE PARTITION &lt;PARTITION EXPRESSION&gt; TO TABLE &lt;DATABASE_NAME.DESTINATION_TABLE&gt;

-- 1. Create empty opensky_partitioned_new table same as the opensky_partitioned

-- 2. Move 20190415 partition from opensky_partitioned to opensky_partitioned_new
ALTER TABLE sampleDatasets.opensky_partitioned
    MOVE PARTITION '20190415' TO TABLE sampleDatasets.opensky_partitioned_new
Ok.
--3. Take partition count for 20190415 in source table
SELECT count()
FROM sampleDatasets.opensky_partitioned
WHERE _partition_id = '20190415'
┌─count()─┐
│       0 │
└─────────┘

--4 Take partition count for 20190415 in destination table
SELECT count()
FROM sampleDatasets.opensky_partitioned_new
WHERE _partition_id = '20190415'

Query id: 49319c73-c84f-4fbf-838d-ab787971eaad

┌─count()─┐
│   78647 │
└─────────┘

-- Usage: ALTER TABLE <DATABASE_NAME.SOURCE_TABLE> [ON CLUSTER <CLUSTER_NAME>] MOVE PARTITION <PARTITION EXPRESSION> TO TABLE <DATABASE_NAME.DESTINATION_TABLE>

-- 1. Create empty opensky_partitioned_new table same as the opensky_partitioned

-- 2. Move 20190415 partition from opensky_partitioned to opensky_partitioned_new

ALTER TABLE sampleDatasets.opensky_partitioned

MOVE PARTITION '20190415' TO TABLE sampleDatasets.opensky_partitioned_new

Ok.

--3. Take partition count for 20190415 in source table

SELECT count()

FROM sampleDatasets.opensky_partitioned

WHERE _partition_id = '20190415'

┌─count()─┐

│ 0 │

└─────────┘

--4 Take partition count for 20190415 in destination table

SELECT count()

FROM sampleDatasets.opensky_partitioned_new

WHERE _partition_id = '20190415'

Query id: 49319c73-c84f-4fbf-838d-ab787971eaad

┌─count()─┐

│ 78647 │

└─────────┘

Для перемещения part или partition на другой диск или том нам необходимо установить политику хранения и создать новую таблицу с этой политикой.

-- Our table's(opensky_partitioned_with_storage_policy) policy is "ssd_to_hdd" 
-- and this volume contain 2 disks(ssd and hdd)
SELECT *
FROM system.storage_policies
WHERE policy_name = (
    SELECT storage_policy
    FROM system.tables
    WHERE (database = 'sampleDatasets') AND (name = 'opensky_partitioned_with_storage_policy')

┌─policy_name─┬─volume_name─┬─volume_priority─┬─disks───┬─volume_type─┬─max_data_part_size─┬─move_factor─┬─prefer_not_to_merge─┐
│ ssd_to_hdd  │ ssd_volume  │               1 │ ['ssd'] │ JBOD        │                  0 │         0.1 │                   0 │
│ ssd_to_hdd  │ hdd_volume  │               2 │ ['hdd'] │ JBOD        │                  0 │         0.1 │                   0 │
└─────────────┴─────────────┴─────────────────┴─────────┴─────────────┴────────────────────┴─────────────┴─────────────────────┘

-- The 20190415 partition is stored under ssd disk.
SELECT
    partition,
    name,
    path
FROM system.parts
WHERE (database = 'sampleDatasets') AND (table = 'opensky_partitioned_with_storage_policy') AND (partition = '20190415')


┌─partition─┬─name───────────────┬─path────────────────────────────────────────────────────────────────────┐
│ 20190415  │ 20190415_107_107_0 │ /ssd/store/300/3005efb6-244d-473f-93ac-944233d79a55/20190415_107_107_0/ │
└───────────┴────────────────────┴─────────────────────────────────────────────────────────────────────────┘

-- Let's move it to the hdd disk
ALTER TABLE sampleDatasets.opensky_partitioned_with_storage_policy
    MOVE PARTITION '20190415' TO DISK 'hdd'

-Check the new volume
SELECT
    partition,
    name,
    path
FROM system.parts
WHERE (database = 'sampleDatasets') AND (table = 'opensky_partitioned_with_storage_policy') AND (partition = '20190415')

┌─partition─┬─name───────────────┬─path────────────────────────────────────────────────────────────────────┐
│ 20190415  │ 20190415_107_107_0 │ /hdd/store/300/3005efb6-244d-473f-93ac-944233d79a55/20190415_107_107_0/ │
└───────────┴────────────────────┴─────────────────────────────────────────────────────────────────────────┘

-- You can move parts and also you can move volume instead of disk

-- Our table's(opensky_partitioned_with_storage_policy) policy is "ssd_to_hdd"

-- and this volume contain 2 disks(ssd and hdd)

SELECT *

FROM system.storage_policies

WHERE policy_name = (

SELECT storage_policy

FROM system.tables

WHERE (database = 'sampleDatasets') AND (name = 'opensky_partitioned_with_storage_policy')

┌─policy_name─┬─volume_name─┬─volume_priority─┬─disks───┬─volume_type─┬─max_data_part_size─┬─move_factor─┬─prefer_not_to_merge─┐

│ ssd_to_hdd │ ssd_volume │ 1 │ ['ssd'] │ JBOD │ 0 │ 0.1 │ 0 │

│ ssd_to_hdd │ hdd_volume │ 2 │ ['hdd'] │ JBOD │ 0 │ 0.1 │ 0 │

└─────────────┴─────────────┴─────────────────┴─────────┴─────────────┴────────────────────┴─────────────┴─────────────────────┘

-- The 20190415 partition is stored under ssd disk.

SELECT

partition,

name,

path

FROM system.parts

WHERE (database = 'sampleDatasets') AND (table = 'opensky_partitioned_with_storage_policy') AND (partition = '20190415')

┌─partition─┬─name───────────────┬─path────────────────────────────────────────────────────────────────────┐

│ 20190415 │ 20190415_107_107_0 │ /ssd/store/300/3005efb6-244d-473f-93ac-944233d79a55/20190415_107_107_0/ │

└───────────┴────────────────────┴─────────────────────────────────────────────────────────────────────────┘

-- Let's move it to the hdd disk

ALTER TABLE sampleDatasets.opensky_partitioned_with_storage_policy

MOVE PARTITION '20190415' TO DISK 'hdd'

-Check the new volume

SELECT

partition,

name,

path

FROM system.parts

WHERE (database = 'sampleDatasets') AND (table = 'opensky_partitioned_with_storage_policy') AND (partition = '20190415')

│ 20190415 │ 20190415_107_107_0 │ /hdd/store/300/3005efb6-244d-473f-93ac-944233d79a55/20190415_107_107_0/ │

-- You can move parts and also you can move volume instead of disk

Dictionary

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

Словари полезны для:

Повышение производительности запросов, особенно при использовании с JOINs
Обогащение принимаемых данных на лету без замедления процесса приема

Использование JOIN в ClickHouse. Типы объединений, оптимизация

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

Синтаксис

SELECT &lt;expr_list&gt;
FROM &lt;left_table&gt;
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ASOF] JOIN &lt;right_table&gt;
(ON &lt;expr_list&gt;)|(USING &lt;column_list&gt;) ...

SELECT <expr_list>

FROM <left_table>

(ON <expr_list>)|(USING <column_list>) ...

Выражения из секции ON и столбцы из секции USING называются «ключами соединения».

Поддерживаемые типы соединения

Все типы из стандартного SQL JOIN поддерживаются:

INNER JOIN, возвращает только строки с совпадающими значениями в обеих таблицах.
LEFT OUTER JOIN, возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если в правой таблице нет соответствующих строк, результат будет иметь значения NULL для столбцов из правой таблицы.
RIGHT OUTER JOIN, возвращает все строки из правой таблицы и соответствующие строки из левой таблицы. Если в левой таблице нет соответствующих строк, результат будет иметь значения NULL для столбцов из левой таблицы.
FULL OUTER JOIN, возвращает все строки из обеих таблиц. Если в одной из таблиц нет соответствующих строк, результат будет иметь значения NULL для столбцов из этой таблицы.
CROSS JOIN, производит декартово произведение таблиц целиком, ключи соединения не указываются.

Без указания типа JOIN подразумевается INNER. Ключевое слово OUTER можно опускать. Альтернативным синтаксисом для CROSS JOIN является указание нескольких таблиц, разделённых запятыми, в секции FROM.

Обычно на практике (в аналитических задачах) используется Left Join и Inner Join. Их легче всего понять и проще делать отладку.

Дополнительные типы соединений, доступные в ClickHouse:

LEFT SEMI JOIN и RIGHT SEMI JOIN, белый список по ключам соединения, не производит декартово произведение.
LEFT ANTI JOIN и RIGHT ANTI JOIN, черный список по ключам соединения, не производит декартово произведение.
LEFT ANY JOIN, RIGHT ANY JOIN и INNER ANY JOIN, Частично (для противоположных сторон LEFT и RIGHT) или полностью (для INNER и FULL) отключает декартово произведение для стандартных видов JOIN.
ASOF JOIN и LEFT ASOF JOIN, Для соединения последовательностей по нечеткому совпадению.

Если настройка join_algorithm установлена в значение partial_merge, то для RIGHT JOIN и FULL JOIN поддерживается только уровень строгости ALL (SEMI, ANTI, ANY и ASOF не поддерживаются).

Ограничения по памяти при использовании JOIN

По умолчанию ClickHouse использует алгоритм hash join. ClickHouse берет правую таблицу и создает для нее хеш-таблицу в оперативной памяти. При включённой настройке join_algorithm = 'auto', после некоторого порога потребления памяти ClickHouse переходит к алгоритму merge join.

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

max_rows_in_join — ограничивает количество строк в хеш-таблице.
max_bytes_in_join — ограничивает размер хеш-таблицы.

По достижении любого из этих ограничений ClickHouse действует в соответствии с настройкой join_overflow_mode.

CROSS JOIN

CROSS JOIN создает полное декартово произведение двух таблиц без учета ключей соединения. Каждая строка левой таблицы объединяется с каждой строкой правой таблицы.

LEFT / RIGHT SEMI JOIN

Запрос LEFT SEMI JOIN возвращает значения столбцов для каждой строки из левой таблицы, которая имеет хотя бы одно совпадение ключа соединения в правой таблице. Возвращается только первое найденное совпадение (декартово произведение отключено).

Запрос RIGHT SEMI JOIN аналогичен и возвращает значения для всех строк из правой таблицы, имеющих хотя бы одно совпадение в левой таблице, но возвращается только первое найденное совпадение.

LEFT / RIGHT ANTI JOIN

LEFT ANTI JOIN возвращает значения столбцов для всех несовпадающих строк из левой таблицы.

Аналогично, RIGHT ANTI JOIN возвращает значения столбцов для всех несовпадающих правых строк таблицы.

LEFT / RIGHT / INNER ANY JOIN

LEFT ANY JOIN — это комбинация LEFT OUTER JOIN + LEFT SEMI JOIN, что означает, что ClickHouse возвращает значения столбца для каждой строки из левой таблицы либо в сочетании со значениями столбца соответствующей строки из правой таблицы, либо в сочетании со значениями по умолчанию. значения столбцов для правой таблицы, если совпадений не существует. Если строка из левой таблицы имеет более одного совпадения в правой таблице, ClickHouse возвращает только объединенные значения столбца из первого найденного совпадения (декартово произведение отключено).

Аналогично, RIGHT ANY JOIN представляет собой комбинацию RIGHT OUTER JOIN + RIGHT SEMI JOIN.

А INNER ANY JOIN — это INNER JOIN с отключенным декартовым произведением.

ASOF JOIN

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

Предложение ON в предложении ASOF JOIN является обязательным и определяет условие точного соответствия рядом с условием неточного соответствия в предложении AND.

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

Что необходимо учитывать при работе с JOIN в ClickHouse

При работе с объединениями в ClickHouse следует помнить о нескольких вещах, чтобы обеспечить хорошую производительность:

Выберите правильный тип соединения. Различные типы соединений могут иметь разные характеристики производительности в зависимости от данных и запроса. В общем, inner joins (внутренние соединения), как правило, выполняются быстрее, чем outer joins (внешние соединения), а соединения подзапросов (subquery joins) иногда могут быть быстрее, чем обычные соединения (regular joins).
Используйте соответствующие ключи соединения. Ключи соединения — это столбцы, которые используются для сопоставления строк между двумя таблицами. Выбор правильных ключей соединения важен для хорошей производительности. В идеале ключи соединения должны быть индексированы и иметь низкую мощность (т.е. небольшое количество различных значений).
Рассмотрите возможность денормализации. В некоторых случаях может оказаться полезным денормализовать данные путем объединения нескольких таблиц в одну. Это может полностью исключить необходимость в соединениях и повысить производительность запросов.
Используйте соответствующие типы данных. Типы данных ключей соединения также могут влиять на производительность. Например, использование строкового типа данных для ключа соединения может быть медленнее, чем использование целочисленного типа данных.
Оптимизируйте свои запросы. Наконец, важно оптимизировать ваши запросы для повышения производительности. Это может включать использование соответствующих индексов, минимизацию объема данных, которые необходимо прочитать, и избежание ненужных вычислений.

Что такое алгоритмы Join? Как работают алгоритмы Join «под капотом»

Чтобы обеспечить максимальное использование ресурсов, для ClickHouse было разработано 6 различных алгоритмов соединения. Эти алгоритмы определяют способ планирования и выполнения запроса на соединение.

ClickHouse позволяет пользователям самостоятельно указать желаемый алгоритм соединения.

На диаграмме представлен обзор алгоритмов на основе их относительного потребления памяти и времени выполнения:

На следующей диаграмме показано потребление памяти и время выполнения всех запросов на join (с max_threads, установленным на 30, и большой таблицей справа):

Дерево решений для выбора правильного алгоритма соединения, когда основным критерием является максимально быстрое выполнение соединений:

Алгоритм Direct Join

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

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

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

Алгоритм Hash join (Хэш-соединение)

Является быстрым и наиболее универсальным. Алгоритм хеш-соединения привязан к памяти.

Хеш -таблица в памяти может обслуживать 250 миллионов совершенно случайных запросов в секунду (и более миллиарда, если она помещается в кэше ЦП). Эта очень быстрая возможность поиска делает хеш-таблицу в памяти естественным общим выбором в ClickHouse для реализации соединений, когда невозможно или невозможно воспользоваться преимуществами сортировки таблиц.

Алгоритм хеш-соединения является наиболее универсальным из доступных реализаций соединения в ClickHouse.

Все данные из правой таблицы передаются (параллельно двумя потоками, поскольку max_threads = 2) в память, а затем ClickHouse заполняет этими данными хеш-таблицу в памяти.
Данные из левой таблицы передаются в потоковом режиме (параллельно двумя потоками, поскольку max_threads = 2)
и объединяются путем поиска в хеш-таблице.

Обратите внимание: поскольку ClickHouse берет правую таблицу и создает для нее хеш-таблицу в оперативной памяти, более эффективно использовать память, размещая меньшую таблицу в правой части JOIN.

Алгоритм Parallel hash join (Параллельное хеш-соединение)

Алгоритм параллельного хеш-соединения может работать быстрее с большими таблицами с правой стороны, но требует больше памяти.

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

Все данные из правой таблицы передаются (параллельно двумя потоками, потому что max_threads = 2) в память. Данные передаются поблочно. Строки из каждого потокового блока разбиваются на 2 сегмента (max_threads = 2) путем применения хэш-функции к ключам соединения каждой строки. Параллельно для каждого сегмента заполняется одна хеш-таблица в памяти с использованием одного потока.Обратите внимание, что хэш-функция для разделения строк на сегменты отличается от той, которая используется внутри хеш-таблиц.
Данные из левой таблицы передаются в потоковом режиме (параллельно двумя потоками, поскольку max_threads = 2), и та же самая «хеш-функция сегмента» из шага ① применяется к ключам соединения каждой строки для определения соответствующей хеш-таблицы,
и строки соединяются через выполнение поиска в соответствующей хеш-таблице.

Обратите внимание, что этот max_threads параметр определяет количество одновременных хеш-таблиц.

Алгоритм Grace hash join (Грейс-хеш-соединение)

Алгоритм не привязан к памяти, при выполнении которого данные временно переносятся на диск. Грейс-хеш-соединение не требует какой-либо сортировки данных и, следовательно, преодолевает некоторые проблемы с производительностью, присущие другим алгоритмам соединения, которые передают данные на диск, например алгоритму (частичного) соединения слиянием (partial merge join algorithm).

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

Что такое мутации в ClickHouse? Почему их следует избегать? Как они работают?

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

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

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

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

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

Почему нужно избегать мутаций в ClickHouse?

Мутации относятся к запросам ALTER , которые манипулируют данными таблицы путем удаления или обновления. В частности, это такие запросы, как ALTER TABLE… DELETE, UPDATE и т.д.

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

При обновлении вы можете избежать большого количества запросов на запись, используя специализированные обработчики таблиц, такие как ReplacingMergeTree или CollapsingMergeTree, вместо обработчика таблиц MergeTree по умолчанию.

Подходы к обновлению и удалению данных в ClickHouse

Lightweight Deletes (Облегченное удаление)

Облегченное удаление представляет собой предпочтительный и наиболее эффективный способ удаления данных из ClickHouse. Используя DELETE FROM синтаксис таблицы, пользователи могут указать условие для удаления определенных строк, как показано ниже:

DELETE FROM table WHERE col1 = 'Hi' AND col2 = 2

1	DELETE FROM table WHERE col1 = 'Hi' AND col2 = 2

По умолчанию эта операция является асинхронной, если для параметра mutations_sync не установлено значение 1.

Lightweight Deletes появились в версии 22.8 и на момент написания статьи фича является экспериментальной, облегченные удаления должны быть готовы к production в одном из следующих выпусков.

Mutations

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

Самый простой способ обновить данные в таблице ClickHouse — использовать оператор ALTER…UPDATE:

ALTER TABLE table
UPDATE col1 = 'Hi' WHERE col2 = 2

1 2	ALTER TABLE table UPDATE col1 = 'Hi' WHERE col2 = 2

Этот запрос будет обновлять col1 таблицу table с использованием заданного фильтра. Оператор ClickHouse ALTER UPDATE по умолчанию асинхронен. Это означает, что обновление происходит в фоновом режиме, и вы не получите мгновенного эффекта в таблице.

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

Удаление данных с помощью мутаций

ALTER TABLE table
DELETE WHERE col2 = 3

1 2	ALTER TABLE table DELETE WHERE col2 = 3

Удаления по умолчанию также выполняются асинхронно. Её можно сделать синхронным, используя ту же mutations_sync настройку.

Проверка прогресса мутации

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

SELECT
    command,
    is_done
FROM system.mutations
WHERE table = 'tablename'

┌─command───────────────────────────────┬─is_done─┐
│ UPDATE col1 = 'Hi' WHERE col2 = 2     │       1 │
│ UPDATE col1 = 'All hi' WHERE col2 > 0 │       0 │
└───────────────────────────────────────┴─────────┘

SELECT

command,

is_done

FROM system.mutations

WHERE table = 'tablename'

┌─command───────────────────────────────┬─is_done─┐

│ UPDATE col1 = 'Hi' WHERE col2 = 2 │ 1 │

│ UPDATE col1 = 'All hi' WHERE col2 > 0 │ 0 │

└───────────────────────────────────────┴─────────┘

Если значение is_done = 0 относится к конкретной мутации, она все равно выполняется. Мутации выполняются для каждой части таблицы, и мутировавшие части становятся доступными мгновенно:

Синхронные обновления

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

SET mutations_sync = 1

1	SET mutations_sync = 1

Теперь наш запрос на обновление будет ждать завершения мутации:

ALTER TABLE table
UPDATE col1 = 'bye' WHERE col2 > 0

0 rows in set. Elapsed: 1.182 sec.

ALTER TABLE table

UPDATE col1 = 'bye' WHERE col2 > 0

0 rows in set. Elapsed: 1.182 sec.

Полное обновление 1 столбца таблицы

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

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

ALTER TABLE table ADD COLUMN col1_new String DEFAULT 'global hi';

ALTER TABLE table
    RENAME COLUMN col1 TO col1_old,
    RENAME COLUMN col1_new TO col1,
    DROP COLUMN col1_old;

ALTER TABLE table ADD COLUMN col1_new String DEFAULT 'global hi';

ALTER TABLE table

RENAME COLUMN col1 TO col1_old,

RENAME COLUMN col1_new TO col1,

DROP COLUMN col1_old;

Эффективное удаление больших блоков (Large Blocks)

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

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

ALTER TABLE hits
DROP PARTITION 'c'

1 2	ALTER TABLE hits DROP PARTITION 'c'

Удаление и обновление с помощью CollapsingMergeTree

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

CREATE TABLE article_reads
(
    `user_id` UInt32,
    `article_id` UInt32,
    `read_to` UInt8,
    `read_start` DateTime,
    `read_end` DateTime,
    `sign` Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY (read_start, article_id, user_id)

CREATE TABLE article_reads

(

`user_id` UInt32,

`article_id` UInt32,

`read_to` UInt8,

`read_start` DateTime,

`read_end` DateTime,

`sign` Int8

)

ENGINE = CollapsingMergeTree(sign)

ORDER BY (read_start, article_id, user_id)

Специальный sign столбец используется для CollapsingMergeTree как способ сообщить ClickHouse, что мы хотим обновить конкретную строку. Если мы вставим -1 столбец sign, вся строка будет удалена. Если мы вставим строку с помощью sign = 1, ClickHouse сохранит ее. Строки, подлежащие обновлению, идентифицируются на основе ключа сортировки, используемого в ORDER BY () операторе DDL при создании таблицы:

Движок CollapsingMergeTree заботится об эффективном удалении отмененных строк из хранилища в фоновом режиме.

Upserts с использованием управления версиями и ReplacingMergeTree

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

EXPLAIN query

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

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

Предложение EXPLAIN помогает нам понять план, который выбрал ClickHouse. Использование этого предложения может быть полезным для понимания и оптимизации производительности.

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

Когда запрос отправляется в ClickHouse, первое, что он делает, это переводит текст в абстрактное синтаксическое дерево, которое описывает вычисление как последовательность логических шагов. На этом этапе ClickHouse также проверит, что запрос синтаксически корректен.

Затем AST будет пропущен через ряд шагов для оптимизации производительности запроса.

После этой оптимизации ClickHouse построит план запроса, описывающий, как он будет выполняться. Затем строится конвейер.

Описание типов EXPLAIN

Можно исследовать каждый шаг вышеописанного процесса:

EXPLAIN AST — абстрактное синтаксическое дерево (дерево абстрактного синтаксиса)
EXPLAIN SYNTAX — текст запроса после оптимизаций на уровне AST
EXPLAIN PLAN — план выполнения запроса (рассчитанный логический план)
EXPLAIN PIPELINE — конвейер выполнения запросов
EXPLAIN ESTIMATE — Оценки для выборочного запроса, доступны с ClickHouse® 21.9
EXPLAIN indexes=1 поддерживается начиная с версии 21.6 (https://github.com/ClickHouse/ClickHouse/pull/22352)
EXPLAIN json=1 поддерживается начиная с версии 21.6 (https://github.com/ClickHouse/ClickHouse/pull/23082)

Синтаксис Explain:

EXPLAIN [AST | SYNTAX | QUERY TREE | PLAN | PIPELINE | ESTIMATE | TABLE OVERRIDE] [setting = value, ...]
    [
      SELECT ... |
      tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]
    ]
    [FORMAT ...]

[

SELECT ... |

tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]

]

[FORMAT ...]

Пример запроса Explain:

EXPLAIN AST
        SYNTAX
        PLAN indexes = 0,
             header = 0,
             description = 1,
             actions = 0,
             optimize = 1
             json = 0
        PIPELINE header = 0,
                 graph = 0,
                 compact = 1
        ESTIMATE
SELECT ...

EXPLAIN AST

SYNTAX

PLAN indexes = 0,

header = 0,

description = 1,

actions = 0,

optimize = 1

json = 0

PIPELINE header = 0,

graph = 0,

compact = 1

ESTIMATE

SELECT ...

Выполнение запроса разбивается на множество шагов. Каждый шаг выполнения запроса можно проанализировать и устранить неполадки с помощью соответствующего EXPLAIN запроса.

EXPLAIN AST: Цель парсера — преобразовать текст запроса в AST (Abstract Syntax Tree). Этот шаг можно визуализировать с помощью EXPLAIN AST.
Результатом является абстрактное синтаксическое дерево, которое можно визуализировать, как показано ниже:

Каждый узел имеет соответствующих потомков, а общее дерево представляет собой общую структуру вашего запроса. Это логическая структура, помогающая обрабатывать запрос. С точки зрения конечного пользователя (если только он не заинтересован в выполнении запроса), это не очень полезно; этот инструмент в основном используется разработчиками.

В настоящее время ClickHouse имеет две архитектуры для Analyzer. Вы можете использовать старую архитектуру, установив: allow_experimental_analyzer=0. Если вы хотите использовать новую архитектуру, вам следует установить allow_experimental_analyzer=1.
Новый анализатор находится в стадии бета-тестирования. Новая архитектура должна предоставить нам лучшую структуру для повышения производительности ClickHouse. Однако, учитывая, что это фундаментальный компонент этапов обработки запросов, он также может оказать негативное влияние на некоторые запросы. После перехода на новый анализатор вы можете заметить снижение производительности, сбои в запросах или запросы, дающие вам неожиданный результат. Вы можете вернуться к старому анализатору, изменив настройки allow_experimental_analyzerна уровне запроса или пользователя.

EXPLAIN QUERY TREE: Анализатор — важный этап выполнения запроса. Он берет AST и преобразует его в дерево запроса. Главное преимущество дерева запроса перед AST заключается в том, что многие компоненты будут разрешены, например, хранилище. Мы также знаем, из какой таблицы читать, псевдонимы также разрешены, и дерево знает различные используемые типы данных. Благодаря всем этим преимуществам анализатор может применять оптимизации. Эти оптимизации работают через «проходы». Каждый проход будет искать различные оптимизации.

EXPLAIN PLAN: Планировщик берет дерево запроса и строит из него план запроса. Дерево запроса сообщает нам, что мы хотим сделать с конкретным запросом, а план запроса сообщает нам, как мы это сделаем. Дополнительные оптимизации будут выполнены как часть плана запроса. Вы можете использовать EXPLAIN PLAN или , EXPLAIN чтобы увидеть план запроса ( EXPLAIN выполнится EXPLAIN PLAN).

EXPLAIN PIPELINE: Конвейер запросов генерируется из плана запросов. Конвейер запросов очень похож на план запросов, с той разницей, что это не дерево, а граф. Он показывает, как ClickHouse собирается выполнить запрос и какие ресурсы будут использованы. Анализ конвейера запросов очень полезен для определения узкого места с точки зрения входов/выходов.

Визуализировать граф можно с помощью сервиса — https://dreampuf.github.io/GraphvizOnline/

Подробнее про EXPLAIN: Understanding Query Execution with the Analyzer

Использованные материалы для подготовки статьи

Первичные ключи в ClickHouse

Определение первичного ключа

Понимание granules (гранул)

Дизайн индекса для больших объемов

Кейсы, когда может понадобиться создание дополнительных Primary Indexes

Системные базы данных

Low cardinality колонки (Enum и LowCardinality типы данных)

Enumerations

LowCardinality

Как ClickHouse хранит данные используя parts и primary keys

Еще раз про primary keys

Как вставляются данные в таблицу

Part, part folder, bulk insert, merging of parts

Partitions (Партиции) в ClickHouse

Для чего используются в таком случае partitions (партиции) в ClickHouse?

Хранение данных в parts с учетом partition key​

Логика хранения данных MergeTree Table в ClickHouse

Deduplication (Дедупликация)

ClickHouse Server Architecture

Обработка запросов ClickHouse

Еще раз про хранение данных и выполнение запроса внутри ClickHouse

Как работать с Parts и Partitions в ClickHouse. Что это такое?

Parts — Части таблиц ClickHouse

Partitions — Партиции таблиц ClickHouse

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

Операции манипулирования частями и партициями Таблицы (Manipulating Partitions and Parts)

DETACH/ATTACH PARTITION|PART — Операции отсоединения и присоединения партиции или части

DROP PARTITION/PART

MOVE PARTITION/PART

Dictionary

Использование JOIN в ClickHouse. Типы объединений, оптимизация

Поддерживаемые типы соединения

Ограничения по памяти при использовании JOIN

CROSS JOIN

LEFT / RIGHT SEMI JOIN

LEFT / RIGHT ANTI JOIN

LEFT / RIGHT / INNER ANY JOIN

ASOF JOIN

Что необходимо учитывать при работе с JOIN в ClickHouse

Рекомендации по использованию индексов при выполнении JOIN

Что такое алгоритмы Join? Как работают алгоритмы Join «под капотом»

Алгоритм Direct Join

Алгоритм Hash join (Хэш-соединение)

Алгоритм Parallel hash join (Параллельное хеш-соединение)

Алгоритм Grace hash join (Грейс-хеш-соединение)

Что такое мутации в ClickHouse? Почему их следует избегать? Как они работают?

Почему нужно избегать мутаций в ClickHouse?

Подходы к обновлению и удалению данных в ClickHouse

Lightweight Deletes (Облегченное удаление)

Mutations

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

Удаление данных с помощью мутаций

Проверка прогресса мутации

Синхронные обновления

Полное обновление 1 столбца таблицы

Эффективное удаление больших блоков (Large Blocks)

Удаление и обновление с помощью CollapsingMergeTree

Upserts с использованием управления версиями и ReplacingMergeTree

EXPLAIN query

Описание типов EXPLAIN

Использованные материалы для подготовки статьи

Хранение данных в parts с учетом partition key