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

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

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

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

	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, которая называется колонка sign. Имя колонки sign произвольное, но тип данных Int8 обязателен, и обращаю внимание, что имя колонки было передано в конструктор таблицы CollapsingMergeTree.

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

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

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

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

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

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

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

примечание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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