Обновление данных в ClickHouse

Всем привет! Эта статья продолжает первую часть, где я рассказывал о том, как мы в Just AI переводили пользовательскую аналитику на новую СУБД ClickHouse. В ней я на примерах показывал, как менялась схема данных и sql запросы, какие возникали сложности и удалось ли добиться прироста производительности.

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

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

Задача

Вспомним, с какими данными мы работали в предыдущей статье. У нас есть клиенты, которые пишут сообщения ботам. Все эти сообщения мы сохраняем в таблицу message.

В этой таблице есть поле comment. Его особенность заключается в том, что владелец бота может его обновлять. С реляционной базой данных никаких проблем нет, в ней мы можем использовать запрос UPDATE. С ClickHouse, к сожалению, все не так просто. 

Как я уже писал выше, ClickHouse не поддерживает привычные update’ы. Какие же у нас есть варианты решения этой проблемы?

CollapsingMergeTree и ReplacingMergeTree

ClickHouse поддерживает множество различных движков таблиц. С помощью некоторых из них, например, CollapsingMergeTree или ReplacingMergeTree, можно достичь обновления какой-либо записи. 

CollapsingMergeTree позволяет удалить запись, если вставить новую строку, где все поля в ключе сортировки (ORDER BY) эквивалентны, за исключением специального поля Sign, которое может принимать значения 1 и -1.

При операции обновления для нашего примера нам бы пришлось:

1. Выполнить SELECT, чтобы получить все поля записи. 

2. Выполнить вставку этой строки со значением поля Sign=-1.

3. Выполнить вставку этой строки с обновленным комментарием и Sign=1.

ReplacingMergeTree чем-то похож на CollapsingMergeTree. Он выполняет удаление дублирующихся записей с одинаковым значением ключа сортировки.

Для нашего примера алгоритм  уменьшился бы на один пункт:

1. Выполняем SELECT, чтобы получить все поля записи.

2. Вставляем строку с обновленным комментарием.

Каждый из этих движков привносит свои нюансы и ограничения. Одним из примеров таких особенностей является то, что записи удаляются/заменяются не сразу, а асинхронно через какое-то время. Чтобы это обработать, нам бы пришлось добавлять лишнюю агрегацию для каждого запроса 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 и у каждого из них есть свои недостатки. Давайте подведем итог по каждому из них:

1. Движки таблиц CollapsingMergeTree и ReplacingMergeTree

   • Для целей производительности операции выполняются асинхронно. Из-за этого может существовать несколько версий одной и той же строки и для обработки этого случая нужно существенно усложнять все запросы SELECT. Это усложнение также может значительно сказаться на производительности.

   • При вставке обновленной записи нужно знать все поля существующей. Это может потребовать выполнять дополнительный SELECT запрос.

2. Отдельная таблица

   • Простая вставка при обновлении. Не нужно выполнять дополнительных запросов.

   • Усложняются запросы на получение. Для получения актуального значения поля необходимо использовать JOIN.

   • Таблица с операциями над полем должна помещаться в память.

3. Мутации

   • Простые и производительные запросы на получение.

   • Тяжелая ресурсоемкая операция обновления. Мутации предназначены для редкого обновления большого количества данных.

4. Гибрид 2 и 3 пункта

   • Простая вставка при обновлении. Не нужно выполнять дополнительных запросов.

   • Усложняются запросы на получение. Для получения актуального значения поля необходимо использовать JOIN.

   • Нужно писать логику по переносу данных из таблицы с операциями в основную.