Удаление дубликатов при повторных вставках

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

Когда выполняется повторная попытка вставки, ClickHouse пытается определить, были ли данные уже успешно записаны. Если вставленные данные помечены как дубликат, ClickHouse не вставляет их в целевую таблицу. Однако пользователь по-прежнему получит статус успешного выполнения операции, как если бы данные были вставлены обычным образом.

Ограничения

Неопределённый статус вставки

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

Ограничение окна дедупликации

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

Включение дедупликации вставок при повторах

Дедупликация вставок для таблиц

Только движки *MergeTree поддерживают дедупликацию при вставке.

Для движков *ReplicatedMergeTree дедупликация вставок включена по умолчанию и управляется настройками replicated_deduplication_window и replicated_deduplication_window_seconds. Для нереплицируемых движков *MergeTree дедупликация управляется настройкой non_replicated_deduplication_window.

Указанные выше настройки определяют параметры журнала дедупликации для таблицы. Журнал дедупликации хранит ограниченное число значений block_id, которые определяют работу механизма дедупликации (см. ниже).

Дедупликация вставок на уровне запроса

Настройка insert_deduplicate=1 включает дедупликацию на уровне запроса. Обратите внимание, что если вы вставляете данные с insert_deduplicate=0, эти данные не могут быть дедуплицированы, даже если вы повторите вставку с insert_deduplicate=1. Это связано с тем, что block_id не записываются для блоков при вставках с insert_deduplicate=0.

Как работает дедупликация вставок

Когда данные вставляются в ClickHouse, они разбиваются на блоки в зависимости от количества строк и объема данных в байтах.

Для таблиц, использующих движки *MergeTree, каждому блоку присваивается уникальный block_id, который представляет собой хэш данных в этом блоке. Этот block_id используется как уникальный ключ для операции вставки. Если тот же block_id найден в журнале дедупликации, блок считается дубликатом и не вставляется в таблицу.

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

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

Для запросов INSERT ... SELECT важно, чтобы часть запроса SELECT возвращала одни и те же данные в том же порядке при каждой операции. Учтите, что этого сложно добиться на практике. Чтобы обеспечить стабильный порядок данных при повторных попытках, задайте точный раздел ORDER BY в части запроса SELECT. Имейте в виду, что выбранная таблица может быть изменена между повторными попытками: результирующие данные могли измениться, и дедупликация не произойдет. Кроме того, в ситуациях, когда вы вставляете большие объемы данных, возможно, что количество блоков после вставок переполнит окно журнала дедупликации, и ClickHouse не сможет определить, что блоки нужно дедуплицировать.

Дедупликация вставок с материализованными представлениями

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

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

• replicated_deduplication_window
• replicated_deduplication_window_seconds
• non_replicated_deduplication_window

Также необходимо включить настройку профиля пользователя deduplicate_blocks_in_dependent_materialized_views. При включённой настройке insert_deduplicate=1 вставленные данные дедуплицируются в исходной таблице. Настройка deduplicate_blocks_in_dependent_materialized_views=1 дополнительно включает дедупликацию в зависимых таблицах. Для полной дедупликации необходимо включить обе настройки.

При вставке блоков в таблицы, которые являются целевыми для материализованных представлений, ClickHouse вычисляет block_id путём хеширования строки, объединяющей block_id из исходной таблицы и дополнительные идентификаторы. Это обеспечивает корректную дедупликацию в рамках материализованных представлений, позволяя различать данные по их исходной вставке, независимо от любых преобразований, применённых перед попаданием в целевую таблицу материализованного представления.

Примеры

Идентичные блоки после преобразований в материализованном представлении

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

Пример:

CREATE TABLE dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

CREATE MATERIALIZED VIEW mv_dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000
AS SELECT
    0 AS key,
    value AS value
FROM dst;

SET max_block_size=1;
SET min_insert_block_size_rows=0;
SET min_insert_block_size_bytes=0;

Настройки выше позволяют нам выбирать данные из таблицы, состоящей из серии блоков, каждый из которых содержит только одну строку. Эти небольшие блоки не объединяются и остаются неизменными до тех пор, пока не будут вставлены в таблицу.

SET deduplicate_blocks_in_dependent_materialized_views=1;

Необходимо включить дедупликацию в материализованном представлении:

INSERT INTO dst SELECT
    number + 1 AS key,
    IF(key = 0, 'A', 'B') AS value
FROM numbers(2);

SELECT
    *,
    _part
FROM dst
ORDER BY all;

┌─key─┬─value─┬─_part─────┐
│   1 │ B     │ all_0_0_0 │
│   2 │ B     │ all_1_1_0 │
└─────┴───────┴───────────┘

Здесь мы видим, что в таблицу dst были вставлены две части. 2 блока из SELECT — 2 части при INSERT. Эти части содержат разные данные.

SELECT
    *,
    _part
FROM mv_dst
ORDER BY all;

┌─key─┬─value─┬─_part─────┐
│   0 │ B     │ all_0_0_0 │
│   0 │ B     │ all_1_1_0 │
└─────┴───────┴───────────┘

Здесь мы видим, что в таблицу mv_dst были вставлены 2 части. Эти части содержат одинаковые данные, однако они не были дедуплицированы.

INSERT INTO dst SELECT
    number + 1 AS key,
    IF(key = 0, 'A', 'B') AS value
FROM numbers(2);

SELECT
    *,
    _part
FROM dst
ORDER BY all;

┌─key─┬─value─┬─_part─────┐
│   1 │ B     │ all_0_0_0 │
│   2 │ B     │ all_1_1_0 │
└─────┴───────┴───────────┘

SELECT
    *,
    _part
FROM mv_dst
ORDER by all;

┌─key─┬─value─┬─_part─────┐
│   0 │ B     │ all_0_0_0 │
│   0 │ B     │ all_1_1_0 │
└─────┴───────┴───────────┘

Здесь мы видим, что при повторной вставке все данные дедуплицируются. Дедупликация работает как для таблицы dst, так и для таблицы mv_dst.

Идентичные блоки при вставке

CREATE TABLE dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

SET max_block_size=1;
SET min_insert_block_size_rows=0;
SET min_insert_block_size_bytes=0;

Вставка:

INSERT INTO dst SELECT
    0 AS key,
    'A' AS value
FROM numbers(2);

SELECT
    'from dst',
    *,
    _part
FROM dst
ORDER BY all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐ │ from dst │ 0 │ A │ all_0_0_0 │ └────────────┴─────┴───────┴───────────┘

При указанных выше настройках запрос select возвращает два блока — следовательно, в таблицу `dst` должны быть вставлены два блока. Однако мы видим, что в таблицу `dst` был вставлен только один блок. Это произошло из-за дедупликации второго блока. Он содержит те же данные и ключ дедупликации `block_id`, который вычисляется как хеш от вставляемых данных. Такое поведение не соответствует ожидаемому. Подобные случаи встречаются редко, но теоретически возможны. Для корректной обработки таких ситуаций необходимо указать `insert_deduplication_token`. Рассмотрим исправление на следующих примерах:

### Идентичные блоки при вставке с `insert_deduplication_token`                                                                  \{#identical-blocks-in-insertion-with-insert_deduplication_token}

```sql
CREATE TABLE dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

SET max_block_size=1;
SET min_insert_block_size_rows=0;
SET min_insert_block_size_bytes=0;

Вставка:

INSERT INTO dst SELECT
    0 AS key,
    'A' AS value
FROM numbers(2)
SETTINGS insert_deduplication_token='some_user_token';

SELECT
    'из dst',
    *,
    _part
FROM dst
ORDER BY all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐
│ из dst     │   0 │ A     │ all_2_2_0 │
│ из dst     │   0 │ A     │ all_3_3_0 │
└────────────┴─────┴───────┴───────────┘

Два идентичных блока вставлены, как и ожидалось.

SELECT 'вторая попытка';

INSERT INTO dst SELECT
    0 AS key,
    'A' AS value
FROM numbers(2)
SETTINGS insert_deduplication_token='some_user_token';

SELECT
    'из dst',
    *,
    _part
FROM dst
ORDER BY all;

┌─'из dst'─┬─key─┬─value─┬─_part─────┐
│ from dst   │   0 │ A     │ all_2_2_0 │
│ from dst   │   0 │ A     │ all_3_3_0 │
└────────────┴─────┴───────┴───────────┘

Повторная вставка корректно дедуплицируется.

SELECT 'третья попытка';

INSERT INTO dst SELECT
    1 AS key,
    'b' AS value
FROM numbers(2)
SETTINGS insert_deduplication_token='some_user_token';

SELECT
    'из dst',
    *,
    _part
FROM dst
ORDER BY all;

┌─'из dst'───┬─key─┬─value─┬─_part─────┐
│ из dst     │   0 │ A     │ all_2_2_0 │
│ из dst     │   0 │ A     │ all_3_3_0 │
└────────────┴─────┴───────┴───────────┘

Эта вставка также будет дедуплицирована, даже если она содержит другие вставленные данные. Обратите внимание, что insert_deduplication_token имеет более высокий приоритет: ClickHouse не использует хэш-сумму данных, когда указан insert_deduplication_token.

Разные операции вставки генерируют одинаковые данные после трансформации в базовой таблице материализованного представления

CREATE TABLE dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

CREATE MATERIALIZED VIEW mv_dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000
AS SELECT
    0 AS key,
    value AS value
FROM dst;

SET deduplicate_blocks_in_dependent_materialized_views=1;

select 'first attempt';

INSERT INTO dst VALUES (1, 'A');

SELECT 'from dst', *, _part FROM dst ORDER by all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐ │ from dst │ 1 │ A │ all_0_0_0 │ └────────────┴─────┴───────┴───────────┘

SELECT 'from mv_dst', *, _part FROM mv_dst ORDER by all;

┌─'from mv_dst'─┬─key─┬─value─┬─_part─────┐ │ from mv_dst │ 0 │ A │ all_0_0_0 │ └───────────────┴─────┴───────┴───────────┘

select 'second attempt';

INSERT INTO dst VALUES (2, 'A');

SELECT 'from dst', *, _part FROM dst ORDER by all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐ │ from dst │ 1 │ A │ all_0_0_0 │ │ from dst │ 2 │ A │ all_1_1_0 │ └────────────┴─────┴───────┴───────────┘

SELECT 'from mv_dst', *, _part FROM mv_dst ORDER by all;

┌─'from mv_dst'─┬─key─┬─value─┬─_part─────┐ │ from mv_dst │ 0 │ A │ all_0_0_0 │ │ from mv_dst │ 0 │ A │ all_1_1_0 │ └───────────────┴─────┴───────┴───────────┘

Каждый раз мы вставляем разные данные. Однако в таблицу `mv_dst` вставляются одинаковые данные. Дедупликация не выполняется, так как исходные данные различались.

### Вставки из разных материализованных представлений в одну целевую таблицу с эквивалентными данными \{#different-materialized-view-inserts-into-one-underlying-table-with-equivalent-data}

```sql
CREATE TABLE dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

CREATE TABLE mv_dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

CREATE MATERIALIZED VIEW mv_first
TO mv_dst
AS SELECT
    0 AS key,
    value AS value
FROM dst;

CREATE MATERIALIZED VIEW mv_second
TO mv_dst
AS SELECT
    0 AS key,
    value AS value
FROM dst;

SET deduplicate_blocks_in_dependent_materialized_views=1;

select 'first attempt';

INSERT INTO dst VALUES (1, 'A');

SELECT
    'from dst',
    *,
    _part
FROM dst
ORDER by all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐
│ from dst   │   1 │ A     │ all_0_0_0 │
└────────────┴─────┴───────┴───────────┘

SELECT
    'from mv_dst',
    *,
    _part
FROM mv_dst
ORDER by all;

┌─'from mv_dst'─┬─key─┬─value─┬─_part─────┐
│ from mv_dst   │   0 │ A     │ all_0_0_0 │
│ from mv_dst   │   0 │ A     │ all_1_1_0 │
└───────────────┴─────┴───────┴───────────┘

В таблицу mv_dst вставлены два одинаковых блока (как и ожидалось).

SELECT 'вторая попытка';

INSERT INTO dst VALUES (1, 'A');

SELECT
    'из dst',
    *,
    _part
FROM dst
ORDER BY all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐ │ from dst │ 1 │ A │ all_0_0_0 │ └────────────┴─────┴───────┴───────────┘

SELECT 'from mv_dst', *, _part FROM mv_dst ORDER by all;

┌─'from mv_dst'─┬─key─┬─value─┬─_part─────┐ │ from mv_dst │ 0 │ A │ all_0_0_0 │ │ from mv_dst │ 0 │ A │ all_1_1_0 │ └───────────────┴─────┴───────┴───────────┘

Операция повтора дедуплицируется в обеих таблицах `dst` и `mv_dst`.