В 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.