Стратегии дедупликации

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

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

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

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

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

​

Варианты дедупликации

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

1. Движок таблицы ReplacingMergeTree: в этом движке таблицы повторяющиеся строки с одинаковым ключом сортировки удаляются во время слияний. ReplacingMergeTree — хороший вариант для эмуляции поведения upsert (когда нужно, чтобы запросы возвращали последнюю вставленную строку).

2. Схлопывание строк: движки таблиц CollapsingMergeTree и VersionedCollapsingMergeTree используют логику, при которой существующая строка «отменяется», а новая строка вставляется. Их сложнее реализовать, чем ReplacingMergeTree, но запросы и агрегации при этом писать проще, поскольку не нужно учитывать, были ли данные уже слиты. Эти два движка таблиц полезны, когда данные нужно часто обновлять.

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

​

Использование ReplacingMergeTree для апсертов

Рассмотрим простой пример, в котором таблица содержит комментарии Hacker News и столбец views, показывающий, сколько раз просмотрели комментарий. Предположим, что при публикации статьи мы вставляем новую строку, а затем раз в день выполняем апсерт новой строки с общим числом просмотров, если это значение выросло:

CREATE TABLE hackernews_rmt (
    id UInt32,
    author String,
    comment String,
    views UInt64
)
ENGINE = ReplacingMergeTree
PRIMARY KEY (author, id)

Вставим две строки:

INSERT INTO hackernews_rmt VALUES
   (1, 'ricardo', 'This is post #1', 0),
   (2, 'ch_fan', 'This is post #2', 0)

Чтобы обновить столбец views, вставьте новую строку с тем же первичным ключом (обратите внимание на новые значения в столбце views):

INSERT INTO hackernews_rmt VALUES
   (1, 'ricardo', 'This is post #1', 100),
   (2, 'ch_fan', 'This is post #2', 200)

В таблице теперь 4 строки:

SELECT *
FROM hackernews_rmt

┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ This is post #2 │     0 │
│  1 │ ricardo │ This is post #1 │     0 │
└────┴─────────┴─────────────────┴───────┘
┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ This is post #2 │   200 │
│  1 │ ricardo │ This is post #1 │   100 │
└────┴─────────┴─────────────────┴───────┘

Отдельные блоки выше в выводе показывают две части, которые остаются «за кулисами», — эти данные ещё не были объединены, поэтому дублирующиеся строки тоже ещё не были удалены. Давайте используем ключевое слово FINAL в запросе SELECT, что приведёт к логическому слиянию результата запроса:

SELECT *
FROM hackernews_rmt
FINAL

┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ This is post #2 │   200 │
│  1 │ ricardo │ This is post #1 │   100 │
└────┴─────────┴─────────────────┴───────┘

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

Использовать FINAL можно, если у вас небольшой объём данных. Если вы работаете с большим объёмом данных, FINAL, вероятно, не лучший вариант. Давайте рассмотрим более подходящий способ найти последнее значение столбца.

​

Избегаем FINAL

Давайте снова обновим столбец views для обеих уникальных строк:

INSERT INTO hackernews_rmt VALUES
   (1, 'ricardo', 'This is post #1', 150),
   (2, 'ch_fan', 'This is post #2', 250)

Теперь в таблице 6 строк, потому что фактического слияния ещё не было (было только слияние на этапе выполнения запроса, когда мы использовали FINAL).

SELECT *
FROM hackernews_rmt

┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ This is post #2 │   200 │
│  1 │ ricardo │ This is post #1 │   100 │
└────┴─────────┴─────────────────┴───────┘
┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ This is post #2 │     0 │
│  1 │ ricardo │ This is post #1 │     0 │
└────┴─────────┴─────────────────┴───────┘
┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ This is post #2 │   250 │
│  1 │ ricardo │ This is post #1 │   150 │
└────┴─────────┴─────────────────┴───────┘

Вместо FINAL используем бизнес-логику: мы знаем, что столбец views всегда растёт, поэтому после группировки по нужным столбцам можно выбрать строку с наибольшим значением с помощью функции max:

SELECT
    id,
    author,
    comment,
    max(views)
FROM hackernews_rmt
GROUP BY (id, author, comment)

┌─id─┬─author──┬─comment─────────┬─max(views)─┐
│  2 │ ch_fan  │ This is post #2 │        250 │
│  1 │ ricardo │ This is post #1 │        150 │
└────┴─────────┴─────────────────┴────────────┘

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

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

​

Использование CollapsingMergeTree для частого обновления столбцов

Обновление столбца включает удаление существующей строки и замену её новыми значениями. Как вы уже видели, в ClickHouse такие мутации происходят не сразу — во время слияний. Если вам нужно обновить много строк, на практике может быть эффективнее не использовать ALTER TABLE..UPDATE, а просто вставить новые данные рядом с уже существующими. Можно было бы добавить столбец, который показывает, устарели данные или они актуальны… и для этого уже есть движок таблицы, который очень хорошо реализует такое поведение, тем более что он автоматически удаляет устаревшие данные. Давайте посмотрим, как это работает.

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

Давайте определим таблицу для хранения количества просмотров:

CREATE TABLE hackernews_views (
    id UInt32,
    author String,
    views UInt64,
    sign Int8
)
ENGINE = CollapsingMergeTree(sign)
PRIMARY KEY (id, author)

Обратите внимание, что в таблице hackernews_views есть столбец Int8 с именем sign, который называют столбцом sign. Имя столбца sign произвольно, но тип данных Int8 обязателен. Также обратите внимание, что имя столбца передаётся в конструктор таблицы CollapsingMergeTree.

Что представляет собой столбец sign в таблице CollapsingMergeTree? Он отражает состояние строки, и столбец sign может принимать только значения 1 или -1. Вот как это работает:

• Если две строки имеют одинаковый первичный ключ (или одинаковый порядок сортировки, если он отличается от первичного ключа), но разные значения столбца sign, то последняя вставленная строка со значением +1 становится строкой состояния, а остальные строки взаимно компенсируют друг друга
• Строки, которые взаимно компенсируют друг друга, удаляются во время слияний
• Строки, для которых не находится пары, сохраняются

Давайте добавим строку в таблицу hackernews_views. Поскольку это единственная строка для данного первичного ключа, мы устанавливаем её состояние в 1:

INSERT INTO hackernews_views VALUES
   (123, 'ricardo', 0, 1)

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

INSERT INTO hackernews_views VALUES
   (123, 'ricardo', 0, -1),
   (123, 'ricardo', 150, 1)

Теперь в таблице 3 строки с первичным ключом (123, 'ricardo'):

SELECT *
FROM hackernews_views

┌──id─┬─author──┬─views─┬─sign─┐
│ 123 │ ricardo │     0 │   -1 │
│ 123 │ ricardo │   150 │    1 │
└─────┴─────────┴───────┴──────┘
┌──id─┬─author──┬─views─┬─sign─┐
│ 123 │ ricardo │     0 │    1 │
└─────┴─────────┴───────┴──────┘

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

SELECT *
FROM hackernews_views
FINAL

┌──id─┬─author──┬─views─┬─sign─┐
│ 123 │ ricardo │   150 │    1 │
└─────┴─────────┴───────┴──────┘

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

Значение, передаваемое в столбец views в нашем примере, на самом деле не нужно и не обязано совпадать с текущим значением views в старой строке. Более того, вы можете отменить строку, указав только первичный ключ и -1:

INSERT INTO hackernews_views(id, author, sign) VALUES
   (123, 'ricardo', -1)

​

Обновления в реальном времени из нескольких потоков

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

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

Рассмотрим пример. Допустим, мы хотим отслеживать число просмотров наших комментариев на Hacker News, а данные часто обновляются. Нам нужно, чтобы отчётность использовала самые свежие значения без принудительного запуска слияний и без ожидания их завершения. Начнём с таблицы, похожей на CollapsedMergeTree, но добавим столбец для хранения версии состояния строки:

CREATE TABLE hackernews_views_vcmt (
    id UInt32,
    author String,
    views UInt64,
    sign Int8,
    version UInt32
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
PRIMARY KEY (id, author)

Обратите внимание, что в таблице в качестве движка используется VersionsedCollapsingMergeTree, а также передаются столбец sign и столбец версии. Вот как работает эта таблица:

• Она удаляет каждую пару строк с одинаковыми первичным ключом и версией, но разными значениями sign
• Порядок, в котором были вставлены строки, не имеет значения
• Обратите внимание: если столбец версии не является частью первичного ключа, ClickHouse неявно добавляет его в первичный ключ последним полем

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

INSERT INTO hackernews_views_vcmt VALUES
   (1, 'ricardo', 0, 1, 1),
   (2, 'ch_fan', 0, 1, 1),
   (3, 'kenny', 0, 1, 1)

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

INSERT INTO hackernews_views_vcmt VALUES
   (1, 'ricardo', 0, -1, 1),
   (1, 'ricardo', 50, 1, 2),
   (2, 'ch_fan', 0, -1, 1),
   (3, 'kenny', 0, -1, 1),
   (3, 'kenny', 1000, 1, 2)

Мы выполним тот же запрос, что и раньше, который за счёт столбца sign корректно добавляет и вычитает значения:

SELECT
    id,
    author,
    sum(views * sign)
FROM hackernews_views_vcmt
GROUP BY (id, author)
HAVING sum(sign) > 0
ORDER BY id ASC

В результате получаем две строки:

┌─id─┬─author──┬─sum(multiply(views, sign))─┐
│  1 │ ricardo │                         50 │
│  3 │ kenny   │                       1000 │
└────┴─────────┴────────────────────────────┘

Давайте принудительно выполним слияние таблицы:

OPTIMIZE TABLE hackernews_views_vcmt

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

SELECT *
FROM hackernews_views_vcmt

┌─id─┬─author──┬─views─┬─sign─┬─version─┐
│  1 │ ricardo │    50 │    1 │       2 │
│  3 │ kenny   │  1000 │    1 │       2 │
└────┴─────────┴───────┴──────┴─────────┘

Таблица VersionedCollapsingMergeTree весьма удобна, если нужно реализовать дедупликацию при вставке строк из нескольких клиентов и/или потоков.

​

Почему мои строки не дедуплицируются?

Одна из причин, по которой вставленные строки могут не дедуплицироваться, — использование неидемпотентной функции или выражения в операторе INSERT. Например, если вы вставляете строки со столбцом createdAt DateTime64(3) DEFAULT now(), они гарантированно будут уникальными, потому что для столбца createdAt у каждой строки будет своё уникальное значение по умолчанию. Движок таблицы MergeTree / ReplicatedMergeTree не сможет дедуплицировать такие строки, поскольку каждая вставленная строка будет иметь уникальную контрольную сумму.

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