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

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

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

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

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

Опции для дедупликации

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

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

2. Слияние строк: движки таблиц CollapsingMergeTree и VersionedCollapsingMergeTree используют логику, при которой существующая строка "отменяется", и вставляется новая строка. Они более сложны для реализации, чем ReplacingMergeTree, но ваши запросы и агрегации могут быть проще в написании, не беспокоясь о том, была ли еще объединена информация. Эти два движка таблиц полезны, когда вам нужно часто обновлять данные.

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

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

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

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

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

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

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

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

примечание

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

Избегание FINAL

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

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

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

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

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

Использование CollapsingMergeTree для Частого Обновления Колонок

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

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

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

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

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

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

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

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

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

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

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

примечание

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

Обновления в Реальном Времени из Нескольких Потоков

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

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

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

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

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

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

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

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

Результат — две строки:

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

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

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

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

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

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