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

В 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

В зависимости от метода подсчета, можно такое использовать 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.

TL;DR

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