Движок таблицы 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, ...]

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

Примечание

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

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(date-column [, sampling_expression], (primary, key), index_granularity, Sign)

Sign — имя столбца типа Int8, в котором значение 1 обозначает строку состояния, а -1 — строку отмены.

Описание параметров запроса см. в разделе описание запроса.
При создании таблицы 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 │
└─────────────────────┴───────────┴──────────┴──────┘

Первая строка отменяет предыдущее состояние объекта (в данном случае представляющего пользователя). Она должна копировать все поля сортировочного ключа для строки «canceled», за исключением Sign. Вторая строка выше содержит текущее состояние.

Поскольку нам нужно только последнее состояние активности пользователя, исходную строку «state» и строку «cancel», которую мы вставили, можно удалить, как показано ниже, таким образом сворачивая недействительное (старое) состояние объекта:

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

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

Примечание

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

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

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

Algorithm

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

Для каждой результирующей части данных ClickHouse сохраняет:


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

Кроме того, когда строк с state как минимум на две больше, чем строк с cancel, или строк с cancel как минимум на две больше, чем строк с state, слияние продолжается. Однако 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 │
└─────────────────────┴───────────┴──────────┴──────┘

Примечание

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

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

Идея этого подхода состоит в том, что при слияниях учитываются только ключевые поля. В строке "cancel" мы, соответственно, можем указать отрицательные значения, которые при суммировании без использования столбца 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 │
└─────────────────────┴───────────┴──────────┴──────┘

Описание​

Параметры​

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

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

Данные​

Algorithm​

Примеры​

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

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

Описание

Параметры

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

Схлопывание

Данные

Algorithm

Примеры

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

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