Всем привет! Эта статья продолжает первую часть, где я рассказывал о том, как мы в Just AI переводили пользовательскую аналитику на новую СУБД ClickHouse. В ней я на примерах показывал, как менялась схема данных и sql запросы, какие возникали сложности и удалось ли добиться прироста производительности.
Одной из сложностей, с которыми мы столкнулись во время перехода, оказалось обновление данных. ClickHouse не поддерживает привычную реляционным базам операцию UPDATE, и, хотя существуют различные альтернативы, каждая из них имеет свои недостатки.
В рамках данной статьи я хочу рассмотреть, какие есть способы обновления данных в ClickHouse и на каком из них остановились мы.
Задача
Вспомним, с какими данными мы работали в предыдущей статье. У нас есть клиенты, которые пишут сообщения ботам. Все эти сообщения мы сохраняем в таблицу message.
В этой таблице есть поле comment. Его особенность заключается в том, что владелец бота может его обновлять. С реляционной базой данных никаких проблем нет, в ней мы можем использовать запрос UPDATE. С ClickHouse, к сожалению, все не так просто.
Как я уже писал выше, ClickHouse не поддерживает привычные update’ы. Какие же у нас есть варианты решения этой проблемы?
CollapsingMergeTree и ReplacingMergeTree
ClickHouse поддерживает множество различных движков таблиц. С помощью некоторых из них, например, CollapsingMergeTree или ReplacingMergeTree, можно достичь обновления какой-либо записи.
CollapsingMergeTree позволяет удалить запись, если вставить новую строку, где все поля в ключе сортировки (ORDER BY) эквивалентны, за исключением специального поля Sign, которое может принимать значения 1 и -1.
При операции обновления для нашего примера нам бы пришлось:
Выполнить SELECT, чтобы получить все поля записи.
Выполнить вставку этой строки со значением поля Sign=-1.
Выполнить вставку этой строки с обновленным комментарием и Sign=1.
ReplacingMergeTree чем-то похож на CollapsingMergeTree. Он выполняет удаление дублирующихся записей с одинаковым значением ключа сортировки.
Для нашего примера алгоритм уменьшился бы на один пункт:
Выполняем SELECT, чтобы получить все поля записи.
Вставляем строку с обновленным комментарием.
Каждый из этих движков привносит свои нюансы и ограничения. Одним из примеров таких особенностей является то, что записи удаляются/заменяются не сразу, а асинхронно через какое-то время. Чтобы это обработать, нам бы пришлось добавлять лишнюю агрегацию для каждого запроса SELECT, даже если нам не нужна информация о комментариях.
Отдельная таблица
Другим вариантом решения нашей проблемы является запись новых комментариев в отдельную таблицу. В этом случае мы могли бы производить JOIN основной таблицы message с таблицей для комментариев. Кроме того, чтобы не хранить все операции обновления одного комментария, для этой таблицы мы можем использовать движок ReplacingMergeTree.
В этом варианте также есть свои ограничения. При соединении таблиц в ClickHouse правая из них должна помещаться в память. В случае, если в запросе будут участвовать очень много строк, это может сильно сказаться на производительности.
Мутации
В ClickHouse существует механизм мутаций, который напоминает обычные UPDATE’ы. В отличие от обновлений в OLTP базах данных, мутации не предназначены для частого использования, так как это тяжелая ресурсоемкая операция.
Область применения мутаций — достаточно тяжелые изменения, затрагивающие много строк в таблице, которые выполняются редко. По этой причине мы не можем создавать мутацию на каждое изменение данных в таблице message.
Решение
Чтобы обойти все существующие проблемы, можно объединить второй и третий вариант. Мы можем сохранять операции с комментариями в отдельной таблице, и раз в какое-то время, например, ночью, создавать мутацию на перенос этих данных в основную таблицу message. За счет этого в таблице с комментариями всегда будет небольшое количество записей, и при выполнении операции join они точно будут помещаться в память.
Такое решение также позволит нам настроить кэш на уровне кода, и в случае, если для конкретного бота нет свежих операций с комментариями, вообще избежать соединения с отдельной таблицей.
Перейдем к практике. Создадим новую таблицу comments, которая будет хранить операции с комментариями.
create table comments
(
bot_id String,
message_id String,
comment String,
timestamp UInt64
)
engine = MergeTree()
ORDER BY (bot_id, message_id);Теперь, чтобы получить сообщения с конкретным комментарием, можно составить следующий запрос:
select message_id
from message ANY LEFT JOIN (
select message_id, argMax(comment, timestamp) last_comment, max(timestamp) last_ts
from comments
where bot_id = 'botId-1'
group by message_id
) sub_query
using message_id
where bot_id = 'botId-1' and if(last_ts > 0, last_comment, message.comment) like '%приветствие%';Попробуем создать мутацию на перенос актуальных комментариев в таблицу message. Для этого хочется составить следующий запрос:
alter table message
update comment = (select argMax(comment, timestamp) from comments where message_id = message.message_id)
where message_id in (select distinct comments.message_id from comments);К большому удивлению, оказывается, что в отличие от UPDATE в реляционной БД, в подзапросах при создании мутации нельзя использовать значение из родительского запроса.
После долгих попыток обойти эту очередную особенность ClickHouse я наткнулся на функцию joinGet() и решил эту проблему следующим образом:
1. Перед созданием мутации создаем таблицу с движком Join.
create table if not exists actual_comments ENGINE Join(ANY, LEFT, message_id) as (
select distinct message_id, if(last_comment_ts > 0, last_comment, message.comment) as comment
from message any inner join (
select message_id, argMax(comment, timestamp) last_comment, max(timestamp) last_comment_ts
from comments
group by message_id
) sub using message_id
);2. Создаем мутацию следующим образом:
alter table message
update comment = joinGet(actual_comments, 'comment', message_id)
where message_id in (select distinct comments.message_id from comments);После создания мутация будет асинхронно обновлять записи в течение некоторого времени. После того, как она закончит, мы сможем удалить устаревшие записи в таблице comments и временную таблицу actual_comments.
alter table comments
delete where timestamp <= (
select max(create_time)
from system.mutations
where command like '%UPDATE comment%' and
is_done = 1
);Итоги
Мы рассмотрели несколько способов обновления данных в ClickHouse и у каждого из них есть свои недостатки. Давайте подведем итог по каждому из них:
Движки таблиц CollapsingMergeTree и ReplacingMergeTree
Для целей производительности операции выполняются асинхронно. Из-за этого может существовать несколько версий одной и той же строки и для обработки этого случая нужно существенно усложнять все запросы SELECT. Это усложнение также может значительно сказаться на производительности.
При вставке обновленной записи нужно знать все поля существующей. Это может потребовать выполнять дополнительный SELECT запрос.
Отдельная таблица
Простая вставка при обновлении. Не нужно выполнять дополнительных запросов.
Усложняются запросы на получение. Для получения актуального значения поля необходимо использовать JOIN.
Таблица с операциями над полем должна помещаться в память.
Мутации
Простые и производительные запросы на получение.
Тяжелая ресурсоемкая операция обновления. Мутации предназначены для редкого обновления большого количества данных.
Гибрид 2 и 3 пункта
Простая вставка при обновлении. Не нужно выполнять дополнительных запросов.
Усложняются запросы на получение. Для получения актуального значения поля необходимо использовать JOIN.
Нужно писать логику по переносу данных из таблицы с операциями в основную.
