Обновления в ClickHouse

Когда речь идёт об обновлениях, аналитические и транзакционные базы данных используют разные подходы в силу различий в архитектуре и целевых сценариях применения. ClickHouse — это столбцовая база данных, оптимизированная для аналитических нагрузок с преобладанием чтения и операций только с добавлением с высокой пропускной способностью. На практике таблицы часто перестраивают так, чтобы свести удаления и обновления к операциям добавления, которые обрабатываются асинхронно и/или во время чтения, используя сильные стороны ClickHouse для высокопроизводительной ингестии данных. ClickHouse также поддерживает полноценные операции обновления и удаления.

В этом руководстве представлен обзор доступных в ClickHouse методов обновления, а также рекомендации по выбору подходящей стратегии обновления для вашей рабочей нагрузки.

Существует два основных подхода к обновлению данных в ClickHouse:

1. Использование специализированных движков таблиц, которые выполняют обновления через вставки
2. Использование декларативных обновлений, таких как операторы UPDATE ... SET или ALTER TABLE ... UPDATE

В каждой из этих двух категорий есть несколько способов обновления данных. У каждого из них свои преимущества и особенности производительности, поэтому выбирать подходящий метод следует исходя из модели данных и объема данных, который вы планируете обновлять.

Специализированные движки таблиц — лучший выбор, если у вас большие объёмы обновлений, часто меняются отдельные строки или нужно обрабатывать непрерывный поток событий обновления и удаления.

Чаще всего встречаются следующие движки:

Поскольку движки таблиц семейства MergeTree выполняют слияние частей данных в фоновом режиме, они обеспечивают согласованность в конечном счёте, и при запросах к таблице нужно использовать ключевое слово FINAL, чтобы на промежуточном этапе обеспечить корректную дедупликацию. Существуют и другие типы движков, но именно эти используются чаще всего.

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

ReplacingMergeTree выполняет дедупликацию строк с одинаковым ключом сортировки во время фоновых слияний, оставляя только самую новую версию.

CREATE TABLE posts
(
    Id UInt32,
    Title String,
    ViewCount UInt32,
    Version UInt32
)
ENGINE = ReplacingMergeTree(Version)
ORDER BY Id

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

Чтобы обновить строку, просто вставьте новую версию с теми же значениями ключа сортировки и более высоким номером версии. Старые версии удаляются во время фоновых слияний. Поскольку дедупликация происходит не сразу (только во время слияний), для получения корректных дедуплицированных результатов следует использовать модификатор FINAL или эквивалентную логику запроса. Модификатор FINAL добавляет к запросу дополнительную нагрузку в диапазоне 21–550% в зависимости от данных.

ReplacingMergeTree не может обновлять значения ключа сортировки. Он также поддерживает столбец Deleted для логических удалений.

Подробнее: руководство по ReplacingMergeTree | справочник по ReplacingMergeTree

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

CREATE TABLE electric_vehicle_state
(
    vin String, -- идентификационный номер транспортного средства
    last_update DateTime64 Materialized now64(), -- необязательно (используется с argMax)
    battery_level Nullable(UInt8), -- в %
    lat Nullable(Float64), -- широта (°)
    lon Nullable(Float64), -- долгота (°)
    firmware_version Nullable(String),
    cabin_temperature Nullable(Float32), -- в °C
    speed_kmh Nullable(Float32) -- с датчика
)
ENGINE = CoalescingMergeTree
ORDER BY vin;

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

Когда строки с одинаковым ключом сортировки сливаются, CoalescingMergeTree сохраняет для каждого столбца последнее значение, отличное от NULL, вместо замены всей строки. Чтобы это работало как задумано, неключевые столбцы должны быть Nullable. Как и в случае с ReplacingMergeTree, для получения корректных результатов после коалесценции используйте FINAL.

Этот движок доступен начиная с ClickHouse 25.6.

Подробнее: CoalescingMergeTree

Исходя из того, что обновления обходятся дорого, но вместо них можно использовать вставки, CollapsingMergeTree использует столбец Sign, чтобы указать ClickHouse, как обрабатывать строки во время слияний. Если в столбец Sign вставляется значение -1, строка схлопывается (удаляется) при наличии соответствующей строки с +1. Строки для обновления определяются на основе ключа сортировки, заданного в выражении ORDER BY при создании таблицы.

CREATE TABLE user_activity
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID

-- Начальное состояние
INSERT INTO user_activity VALUES (4324182021466249494, 5, 146, 1)

-- Отменить старую строку и вставить новое состояние
INSERT INTO user_activity VALUES (4324182021466249494, 5, 146, -1)
INSERT INTO user_activity VALUES (4324182021466249494, 6, 185, 1)

-- Запрос с корректной агрегацией
SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM user_activity
GROUP BY UserID
HAVING sum(Sign) > 0

┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘

В отличие от ReplacingMergeTree, CollapsingMergeTree позволяет изменять значения ключа сортировки. Он хорошо подходит для обратимых операций с семантикой отмены, таких как финансовые транзакции или отслеживание состояния в игре.

Подробнее: CollapsingMergeTree

Эти методы работают с таблицами на движках семейства MergeTree.

Мутации (ALTER TABLE ... UPDATE) перезаписывают все части, содержащие строки, удовлетворяющие выражению WHERE.

ALTER TABLE posts UPDATE AnswerCount = AnswerCount + 1 WHERE AnswerCount = 0

Мутации создают высокую нагрузку на I/O, поскольку переписывают все части, соответствующие выражению WHERE. Этот процесс не является атомарным. Части заменяются мутированными версиями, как только те становятся готовы, и SELECT-запрос, который начинает выполняться во время мутации, увидит данные как из уже мутированных частей, так и из частей, которые еще не были мутированы. Отслеживать состояние прогресса можно через таблицу system.mutations.

Подробнее: ALTER TABLE UPDATE

При мутациях через ALTER TABLE ... UPDATE вам может потребоваться подождать, пока они будут применены фоновым процессом, прежде чем измененные значения отобразятся в результатах запросов. ClickHouse позволяет изменить это поведение с помощью "мутаций на лету". Когда мутации на лету включены, обновленные строки сразу помечаются как обновленные, и последующие запросы SELECT автоматически возвращают измененные значения.

Мутации на лету можно включить для таблиц семейства MergeTree, включив настройку уровня запроса apply_mutations_on_fly.

SET apply_mutations_on_fly = 1;

CREATE TABLE test_on_fly_mutations (id UInt64, v String)
ENGINE = MergeTree ORDER BY id;

-- Отключим фоновую материализацию мутаций, чтобы показать
-- поведение по умолчанию, когда мутации на лету не включены
SYSTEM STOP MERGES test_on_fly_mutations;
SET mutations_sync = 0;

-- Вставим несколько строк в нашу новую таблицу
INSERT INTO test_on_fly_mutations VALUES (1, 'a'), (2, 'b'), (3, 'c');

-- Обновим значения в строках
ALTER TABLE test_on_fly_mutations UPDATE v = 'd' WHERE id = 1;
ALTER TABLE test_on_fly_mutations DELETE WHERE v = 'd';
ALTER TABLE test_on_fly_mutations UPDATE v = 'e' WHERE id = 2;
ALTER TABLE test_on_fly_mutations DELETE WHERE v = 'e';

-- Явно отключим мутации на лету
SET apply_mutations_on_fly = 0;

SELECT id, v FROM test_on_fly_mutations ORDER BY id;

┌─id─┬─v─┐
│  1 │ a │
│  2 │ b │
│  3 │ c │
└────┴───┘

-- Включим мутации на лету
SET apply_mutations_on_fly = 1;

SELECT id, v FROM test_on_fly_mutations ORDER BY id;

┌─id─┬─v─┐
│  3 │ c │
└────┴───┘

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

Если в течение некоторого интервала времени число отправленных мутаций постоянно превышает число мутаций, обрабатываемых в фоновом режиме, очередь нематериализованных мутаций, которые нужно применить, будет продолжать расти. В конечном итоге это приведет к снижению производительности SELECT-запросов.

Мы рекомендуем включать настройку apply_mutations_on_fly вместе с другими настройками уровня MergeTree, такими как number_of_mutations_to_throw и number_of_mutations_to_delay, чтобы ограничить бесконтрольный рост числа нематериализованных мутаций.

Мутации на лету имеют ограниченную поддержку подзапросов и недетерминированных функций. Поддерживаются только скалярные подзапросы с результатом разумного размера (это контролируется настройкой mutations_max_literal_size_to_replace). Поддерживаются только константные недетерминированные функции (например, функция now()).

Это поведение контролируется следующими настройками:

Легковесные обновления используют "патч-части" — специальные части данных, содержащие только обновлённые столбцы и строки, — а не переписывают столбцы целиком, как при традиционных мутациях.

UPDATE posts SET AnswerCount = AnswerCount + 1 WHERE Id = 404346

Этот подход использует стандартный синтаксис UPDATE и сразу создает патч-части, не дожидаясь слияний. Обновленные значения сразу видны в запросах SELECT за счет применения патчей, но физически материализуются только во время последующих слияний. Это делает легковесные обновления оптимальным вариантом для обновления небольшого процента строк (до ~10% таблицы) с предсказуемой latency. Бенчмарки показывают, что они могут быть до 23 раз быстрее, чем мутации.

Компромисс заключается в том, что запросы SELECT требуют дополнительных затрат при применении патчей, а патч-части учитываются в лимитах на части. При превышении порога ~10% накладные расходы на применение патчей при чтении растут пропорционально, поэтому для более крупных обновлений синхронные мутации эффективнее.

Подробнее: Легковесный UPDATE

Мутации на лету позволяют обновлять строки так, что последующие запросы SELECT автоматически возвращают изменённые значения, не дожидаясь фоновой обработки. Тем самым устраняется ограничение атомарности, присущее обычным мутациям.

SET apply_mutations_on_fly = 1;

SELECT ViewCount FROM posts WHERE Id = 404346

┌─ViewCount─┐
│     26762 │
└───────────┘

-- Increment the count
ALTER TABLE posts UPDATE ViewCount = ViewCount + 1 WHERE Id = 404346

-- The updated value is immediately visible
SELECT ViewCount FROM posts WHERE Id = 404346

┌─ViewCount─┐
│     26763 │
└───────────┘

И для мутации, и для последующих запросов SELECT должен быть включен параметр apply_mutations_on_fly = 1. Условия мутации хранятся в ClickHouse Keeper, который держит всё в памяти, и применяются на лету во время выполнения запросов.

Обратите внимание, что для обновления данных всё равно используется мутация — просто она не материализуется сразу. Мутация по-прежнему будет применяться в фоновом режиме как асинхронный процесс и создаёт такие же существенные накладные расходы, как и обычная мутация. Выражения, которые можно использовать с этой операцией, также ограничены (см. подробности).

Подробнее: Мутации на лету

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

Если вам интересен подробный разбор того, как со временем развивались обновления в ClickHouse, а также анализ бенчмарков, см.:

• Обновления в ClickHouse, часть 1: Специализированные движки
• Обновления в ClickHouse, часть 2: Обновления в стиле SQL
• Обновления в ClickHouse, часть 3: Бенчмарки