Движок таблицы CollapsingMergeTree

Описание

Движок CollapsingMergeTree наследуется от MergeTree и добавляет логику схлопывания строк во время слияния. Движок таблицы CollapsingMergeTree асинхронно удаляет (схлопывает) пары строк, если все поля ключа сортировки (ORDER BY) совпадают, кроме специального поля Sign, которое может принимать значение 1 или -1. Строки, для которых нет пары с противоположным значением Sign, сохраняются. Подробнее см. в разделе Collapsing этого документа.

Этот движок может значительно уменьшить объем хранилища, что, в свою очередь, повышает эффективность запросов SELECT.

Параметры

Все параметры этого движка таблицы, за исключением параметра Sign, имеют то же значение, что и в MergeTree.

Sign — имя столбца, указывающего тип строки: 1 — «строка состояния», -1 — «строка отмены». Тип: Int8.

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

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) 
ENGINE = CollapsingMergeTree(Sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

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

Схлопывание

Данные

Рассмотрим ситуацию, когда вам нужно сохранять постоянно меняющиеся данные для некоторого объекта. Может показаться логичным хранить по одной строке для каждого объекта и обновлять её при каждом изменении, однако операции обновления для СУБД дороги и медленны, поскольку требуют перезаписи данных в хранилище. Если нам нужно быстро записывать данные, выполнять большое количество обновлений — не лучший подход, но мы всегда можем последовательно записывать изменения объекта. Для этого используется специальный столбец Sign.

Если Sign = 1, это означает, что строка является строкой состояния: строкой, содержащей поля, которые представляют текущее корректное состояние.
Если Sign = -1, это означает, что строка является строкой отмены: строкой, используемой для отмены состояния объекта с теми же атрибутами.

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

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

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

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

Первая строка аннулирует предыдущее состояние объекта (в данном случае представляющего пользователя). Она должна копировать все поля ключа сортировки для “отменяющей” строки, кроме Sign. Вторая строка выше содержит текущее состояние. Поскольку нам нужно только последнее состояние активности пользователя, исходная строка состояния и строка отмены, которую мы вставили, могут быть удалены, как показано ниже, путём схлопывания недействительного (старого) состояния объекта:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │ -- старую строку состояния можно удалить
│ 4324182021466249494 │         5 │      146 │   -1 │ -- строку отмены можно удалить
│ 4324182021466249494 │         6 │      185 │    1 │ -- новая строка состояния остаётся
└─────────────────────┴───────────┴──────────┴──────┘

CollapsingMergeTree выполняет именно такое схлопывание во время слияния частей данных.

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

Особенности такого подхода

Программа, записывающая данные, должна хранить состояние объекта, чтобы иметь возможность отменить его. Строка отмены должна содержать копии полей ключа сортировки строки состояния и противоположное значение Sign. Это увеличивает первоначальный объем хранилища, но позволяет быстро записывать данные.
Длинные, постоянно растущие массивы в столбцах снижают эффективность движка из-за повышенной нагрузки при записи. Чем проще данные, тем выше эффективность.
Результаты SELECT сильно зависят от согласованности истории изменений объекта. Будьте внимательны при подготовке данных для вставки. Если данные несогласованы, результаты могут быть непредсказуемыми. Например, отрицательные значения у неотрицательных метрик, таких как глубина сеанса.

Алгоритм

Когда ClickHouse выполняет слияние частей данных, каждая группа последовательных строк с одинаковым ключом сортировки (ORDER BY) сокращается не более чем до двух строк: «строки состояния» с Sign = 1 и «строки отмены» с Sign = -1. Иными словами, в ClickHouse записи схлопываются. Для каждой результирующей части данных ClickHouse сохраняет:


1.	Первую «строку отмены» и последнюю «строку состояния», если количество «строк состояния» и «строк отмены» совпадает и последняя строка — это «строка состояния».
2.	Последнюю «строку состояния», если «строк состояния» больше, чем «строк отмены».
3.	Первую «строку отмены», если «строк отмены» больше, чем «строк состояния».
4.	Ни одной строки, во всех остальных случаях.

Кроме того, если «строк состояния» как минимум на две больше, чем «строк отмены», или «строк отмены» как минимум на две больше, чем «строк состояния», слияние продолжается. Однако ClickHouse рассматривает эту ситуацию как логическую ошибку и записывает ее в журнал сервера. Такая ошибка может возникнуть, если одни и те же данные вставляются более одного раза. Таким образом, схлопывание не должно изменять результаты вычисления статистики. Изменения постепенно схлопываются, так что в итоге остается только последнее состояние почти каждого объекта. Столбец Sign обязателен, потому что алгоритм слияния не гарантирует, что все строки с одинаковым ключом сортировки окажутся в одной и той же результирующей части данных и даже на одном и том же физическом сервере. ClickHouse обрабатывает запросы SELECT несколькими потоками и не может предсказать порядок строк в результате. Агрегация необходима, если нужно получить полностью «схлопнутые» данные из таблицы CollapsingMergeTree. Чтобы завершить схлопывание, напишите запрос с GROUP BY и агрегатными функциями, учитывающими знак. Например, чтобы вычислить количество, используйте sum(Sign) вместо count(). Чтобы вычислить сумму чего-либо, используйте sum(Sign * x) вместе с HAVING sum(Sign) > 0 вместо sum(x), как в примере ниже. Агрегаты count, sum и avg можно вычислить таким способом. Агрегат uniq можно вычислить, если у объекта есть хотя бы одно несхлопнутое состояние. Агрегаты min и max вычислить нельзя, потому что CollapsingMergeTree не сохраняет историю схлопнутых состояний.

Если вам нужно извлечь данные без агрегации (например, чтобы проверить, присутствуют ли строки, чьи последние значения соответствуют определенным условиям), вы можете использовать модификатор FINAL для предложения FROM. Он выполнит слияние данных перед возвратом результата. Для CollapsingMergeTree возвращается только последняя строка состояния для каждого ключа.

Примеры

Пример использования

Даны следующие данные:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

Давайте создадим таблицу UAct с помощью CollapsingMergeTree:

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

Далее вставим данные:

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

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

Мы используем два запроса INSERT, чтобы создать две разные части данных.

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

Данные можно выбрать с помощью:

SELECT * FROM UAct

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

Давайте посмотрим на полученные выше данные и проверим, произошло ли схлопывание… С помощью двух запросов INSERT мы создали две части данных. Запрос SELECT выполнялся в двух потоках, поэтому строки вернулись в случайном порядке. Однако схлопывание не произошло, потому что слияния частей данных ещё не было, а ClickHouse выполняет слияние частей данных в фоновом режиме в непредсказуемый момент. Поэтому нам нужна агрегация, которую мы выполняем с помощью агрегатной функции sum и предложения HAVING:

SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0

┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘

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

SELECT * FROM UAct FINAL

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

Такой способ выборки данных менее эффективен и не рекомендуется при работе с большими объёмами сканируемых данных (миллионами строк).

Пример другого подхода

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

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
│ 4324182021466249494 │        -5 │     -146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

Для этого подхода необходимо изменить типы данных PageViews и Duration, чтобы в них можно было хранить отрицательные значения. Поэтому при создании таблицы UAct с помощью collapsingMergeTree мы меняем типы этих столбцов с UInt8 на Int16:

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

Давайте проверим этот подход, выполнив вставку данных в нашу таблицу. Однако для примеров или небольших таблиц такой вариант допустим:

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

SELECT * FROM UAct FINAL;

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

SELECT
    UserID,
    sum(PageViews) AS PageViews,
    sum(Duration) AS Duration
FROM UAct
GROUP BY UserID

┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘

SELECT COUNT() FROM UAct

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

OPTIMIZE TABLE UAct FINAL;

SELECT * FROM UAct

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

​Описание

​Параметры

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

​Схлопывание

​Данные

​Алгоритм

​Примеры

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

​Пример другого подхода

Описание

Параметры

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

Схлопывание

Данные

Алгоритм

Примеры

Пример использования

Пример другого подхода