Перейти к основному содержанию
Перейти к основному содержанию

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

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

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

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

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

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

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

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

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

Использование ReplacingMergeTree для операций upsert​

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

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', 'Это пост #1', 0),
   (2, 'ch_fan', 'Это пост #2', 0)

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

INSERT INTO hackernews_rmt VALUES
   (1, 'ricardo', 'Это пост №1', 100),
   (2, 'ch_fan', 'Это пост №2', 200)

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

SELECT *
FROM hackernews_rmt

┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ Это пост #2 │     0 │
│  1 │ ricardo │ Это пост #1 │     0 │
└────┴─────────┴─────────────────┴───────┘
┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ Это пост #2 │   200 │
│  1 │ ricardo │ Это пост #1 │   100 │
└────┴─────────┴─────────────────┴───────┘

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

SELECT *
FROM hackernews_rmt
FINAL

┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ Это пост №2     │   200 │
│  1 │ ricardo │ Это пост №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  │ Это пост №2     │        250 │
│  1 │ ricardo │ Это пост №1     │        150 │
└────┴─────────┴─────────────────┴────────────┘

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

Наш учебный модуль «Deleting and Updating Data» подробнее разбирает этот пример, в том числе показывает, как использовать столбец version с ReplacingMergeTree.

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

Обновление столбца подразумевает удаление существующей строки и замену её новыми значениями. Как вы уже видели, такие мутации в ClickHouse происходят не сразу, а во время слияний (merges). Если вам нужно обновить много строк, на практике может быть эффективнее не использовать 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 и столбец version. Вот как работает таблица:

  • Удаляется каждая пара строк с одинаковыми первичным ключом и версией, но разным знаком
  • Порядок вставки строк не имеет значения
  • Обратите внимание, что если столбец version не является частью первичного ключа, 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.