Удаление и обновление данных в Clickhouse

Игорь Грегорченко

14.01.2020 16:30

В Clickhouse нет поддержки удалений и обновлений. Однако существует большое [https://ruhighload.com/doc/clickhouse/ количество движков] для разных задач.

Если существует необходимость обновлять и удалять данные в таблицах, пригодятся два движка – CollapsingMergeTree и ReplacingMergeTree.

Обновление с помощью ReplacingMergeTree

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

**users**

user_id | email | team | position

Каждая запись однозначно определена первичным ключом (user_id). Некоторые колонки таблицы могут меняться (team и position). Именно для таких случаев подходит движок ReplacingMergeTree:

CREATE TABLE users

(

user_id UInt32,

email String,

team String,

position String,

updated_date date

)

ENGINE = **ReplacingMergeTree**(updated_date, (**user_id**), 8192)

## Обновляемая таблица users с уникальным ключом user_id

При вставке новых данных таблица работает привычным образом:
INSERT INTO users VALUES(1, 'den@ruhighload.com', 'ruhighload', 'engineer', today());

После вставки данные появятся в таблице:

:) SELECT * FROM users;

SELECT *
FROM users 

┌─user_id─┬─email──────────────┬─team───────┬─position─┬─updated_date─┐
│       1 │ den@ruhighload.com │ ruhighload │ engineer │   2018-04-14 │
└─────────┴────────────────────┴────────────┴──────────┴──────────────┘

1 rows in set. Elapsed: 0.003 sec.

Попробуем вставить другие данные с тем же ключом:
INSERT INTO users VALUES(1, 'den@ruhighload.com', 'ruhighload', '**author**', today());

В результате увидим такие данные в таблице:

:) SELECT * FROM users;

SELECT *
FROM users 

┌─user_id─┬─email──────────────┬─team───────┬─position─┬─updated_date─┐
│       **1** │ den@ruhighload.com │ ruhighload │ **engineer** │   2018-04-14 │
└─────────┴────────────────────┴────────────┴──────────┴──────────────┘
┌─user_id─┬─email──────────────┬─team───────┬─position─┬─updated_date─┐
│       **1** │ den@ruhighload.com │ ruhighload │ **author**   │   2018-04-14 │
└─────────┴────────────────────┴────────────┴──────────┴──────────────┘

2 rows in set. Elapsed: 0.003 sec.

Увидим обе записи. Дело в том, что Clickhouse “схлопывает” данные в фоне в неопределенный момент времени. Для того, чтобы сделать правильную выборку нужно к названию таблицы добавить модификатор FINAL:

:) SELECT * FROM users **FINAL**;

SELECT *
FROM users
FINAL 

┌─user_id─┬─email──────────────┬─team───────┬─position─┬─updated_date─┐
│       **1** │ den@ruhighload.com │ ruhighload │ **author**   │   2018-04-14 │
└─────────┴────────────────────┴────────────┴──────────┴──────────────┘

1 rows in set. Elapsed: 0.005 sec.

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

**session_durations**

session_id | duration | date/time

## Для каждой уникальной session_id будет хранится только один duration

В зависимости от метода подсчета, можно такое использовать [https://ruhighload.com/doc/clickhouse/table_engines/summingmergetree/ SummingMergeTree].

Удаление с помощью CollapsingMergeTree

Движок CollapsingMergeTree работает похожим образом. Однако кроме основных колонок, для него необходимо указывать еще и колонку версии:

CREATE TABLE users

(

user_id UInt32,

email String,

team String,

position String,

updated_date date,

**version Int8**

)

ENGINE = **CollapsingMergeTree**(updated_date, (**user_id**), 8192, **version**)

## version должна всегда иметь тип Int8

Если при вставке указать version = -1, запись будет удалена. При значениях version = 1 запись будет оставлена в таблице. Например:

INSERT INTO users VALUES(1, 'den@ruhighload.com', 'ruhighload', 'author', today(), **1**);

INSERT INTO users VALUES(2, 'anton@ruhighload.com', 'ruhighload', 'author', today(), **1**);

## вставка данных

После этой вставки в таблице будут две записи:

:) SELECT * FROM users FINAL;

SELECT *
FROM users
FINAL 

┌─user_id─┬─email────────────────┬─team───────┬─position─┬─updated_date─┬─version─┐
│       **2** │ anton@ruhighload.com │ ruhighload │ author   │   2018-04-14 │       **1** │
└─────────┴──────────────────────┴────────────┴──────────┴──────────────┴─────────┘
┌─user_id─┬─email──────────────┬─team───────┬─position─┬─updated_date─┬─version─┐
│       **1** │ den@ruhighload.com │ ruhighload │ author   │   2018-04-14 │       **1** │
└─────────┴────────────────────┴────────────┴──────────┴──────────────┴─────────┘

2 rows in set. Elapsed: 0.004 sec.

Для удаления одной из записей необходимо вставить ее с version = -1:

INSERT INTO users VALUES(**2**, 'anton@ruhighload.com', 'ruhighload', 'author', today(), **-1**);

## удаление записи

Теперь данные в таблице будут выглядеть так (не забывайте использовать FINAL):

:) SELECT * FROM users FINAL;

SELECT *
FROM users
FINAL 

┌─user_id─┬─email──────────────┬─team───────┬─position─┬─updated_date─┬─version─┐
│       **1** │ den@ruhighload.com │ ruhighload │ author   │   2018-04-14 │       1 │
└─────────┴────────────────────┴────────────┴──────────┴──────────────┴─────────┘

1 rows in set. Elapsed: 0.003 sec.

## в таблице осталась только одна запись

Движок CollapsingMergeTree также обновляет данные, в случае их изменения:

INSERT INTO users VALUES(1, 'den@ruhighload.com', 'ruhighload', '**human**', today(), 1);

Данные изменятся:

:) SELECT * FROM users FINAL;

SELECT *
FROM users
FINAL 

┌─user_id─┬─email──────────────┬─team───────┬─position─┬─updated_date─┬─version─┐
│       1 │ den@ruhighload.com │ ruhighload │ **human**    │   2018-04-14 │       1 │
└─────────┴────────────────────┴────────────┴──────────┴──────────────┴─────────┘

1 rows in set. Elapsed: 0.004 sec.

<h2>TL;DR

Для реализации удаления и обновления данных в Clickhouse можно использовать движки ReplacingMergeTree и CollapsingMergeTree. Для корректной работы выборок с этими движками необходимо использовать модификатор FINAL.