Движки таблиц — продвинутые — ClickHouse: от схемы

Движки таблиц — продвинутые

Версии строк, состояния агрегатов и результат, который меняется после слияния

#Результат урока

Вы смоделируете поток версий и агрегатов, объясните семантику данных до фонового слияния и напишете корректный запрос для обоих состояний.

#Обзор специализированных движков

Семейство MergeTree включает специализированные движки для разных сценариев:

Движок	Назначение	Когда использовать
MergeTree	Базовый	Общие случаи
ReplacingMergeTree	Удаление дубликатов	Дедупликация, версии записей
SummingMergeTree	Суммирование агрегатов	Предварительная агрегация числовых данных
AggregatingMergeTree	Сложные агрегаты	Инкрементальные вычисления с состояниями
CollapsingMergeTree	Схлопывание пар строк	Эмуляция UPDATE/DELETE
VersionedCollapsingMergeTree	Схлопывание с версиями	Корректная обработка порядка вставки
GraphiteMergeTree	Агрегация метрик	Интеграция с Graphite/метриками

#ReplacingMergeTree

#Назначение

ReplacingMergeTree удаляет дубликаты строк с одинаковым ключом сортировки при слиянии частей.

Дедупликация происходит только во время фонового слияния. Сразу после вставки в таблице ещё могут быть несколько версий строки.

#Базовый синтаксис

CREATE TABLE users
(
    user_id     UInt64,
    name        String,
    email       String,
    updated_at  DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
PARTITION BY toYYYYMM(updated_at)
ORDER BY (user_id);

Параметр version_column (опциональный):

Если указан — оставляется строка с максимальным значением версии
Если не указан — оставляется последняя строка (недетерминировано)

#Пример: дедупликация без версии

CREATE TABLE events_dedup
(
    event_id   UInt64,
    event_time DateTime,
    user_id    UInt64
)
ENGINE = ReplacingMergeTree()
ORDER BY (event_id);

-- Вставка дубликатов
INSERT INTO events_dedup VALUES (1, '2026-03-01 10:00:00', 100);
INSERT INTO events_dedup VALUES (1, '2026-03-01 10:00:00', 100);
INSERT INTO events_dedup VALUES (1, '2026-03-01 10:00:00', 100);

-- Сразу после вставки: 3 строки
SELECT count() FROM events_dedup;  -- 3

-- После merge (может занять время):
SELECT count() FROM events_dedup;  -- 1

#Пример: версионирование с updated_at

CREATE TABLE products
(
    product_id  UInt64,
    name        String,
    price       Decimal(10, 2),
    stock       UInt32,
    updated_at  DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (product_id);

-- Вставка обновлений
INSERT INTO products (product_id, name, price, stock) 
VALUES (1, 'Widget', 10.00, 100);

INSERT INTO products (product_id, name, price, stock) 
VALUES (1, 'Widget', 12.00, 80);  -- Обновление цены

-- После merge останется строка с max(updated_at)
SELECT * FROM products;
-- product_id=1, name='Widget', price=12.00, stock=80

#Получение актуальных данных

Поскольку merge происходит асинхронно, для чтения актуальных данных используйте:

-- Вариант 1: FINAL модификатор (дорогой!)
SELECT * FROM products FINAL;

-- Вариант 2: Подзапрос с группировкой
SELECT 
    product_id,
    argMax(name, updated_at) AS name,
    argMax(price, updated_at) AS price,
    argMax(stock, updated_at) AS stock
FROM products
GROUP BY product_id;

-- Вариант 3: Ожидание завершения мутаций
OPTIMIZE TABLE products FINAL;
SELECT * FROM products;  -- Теперь без дубликатов

Важно: FINAL заставляет ClickHouse применять дедупликацию при чтении, но это значительно замедляет запросы.

#Когда использовать ReplacingMergeTree

Когда подходит:

Дедупликация событий при повторной вставке
Хранение последних версий документов
CDC (Change Data Capture) из транзакционных БД
Ситуации, где небольшая задержка на merge допустима

Когда лучше выбрать другой движок:

Требуется немедленная видимость последней версии
Критична уникальность в момент вставки
Частые точечные запросы к отдельным записям

#SummingMergeTree

#Назначение

SummingMergeTree суммирует значения числовых колонок для строк с одинаковым ключом сортировки при слиянии.

Используется для предварительной агрегации данных.

#Базовый синтаксис

CREATE TABLE sales_summary
(
    date        Date,
    product_id  UInt32,
    category_id UInt16,
    quantity    UInt32,           -- Будет суммироваться
    revenue     Decimal(12, 2)    -- Будет суммироваться
)
ENGINE = SummingMergeTree()
ORDER BY (date, product_id, category_id);

Правила суммирования:

Суммируются все числовые колонки (кроме тех, что в ORDER BY)
Строковые колонки берутся из произвольной строки (недетерминировано)
Можно явно указать суммируемые колонки

#Явное указание суммируемых колонок

CREATE TABLE sales_explicit
(
    date        Date,
    product_id  UInt32,
    category_id UInt16,
    quantity    UInt32,
    revenue     Decimal(12, 2),
    discount    Decimal(5, 2),
    notes       String            -- Не суммируется
)
ENGINE = SummingMergeTree((quantity, revenue, discount))
ORDER BY (date, product_id, category_id);

#Пример: агрегация продаж

CREATE TABLE sales_raw
(
    event_time  DateTime,
    product_id  UInt32,
    quantity    UInt32,
    revenue     Decimal(10, 2)
)
ENGINE = MergeTree()
ORDER BY (event_time, product_id);

CREATE TABLE sales_daily
(
    date        Date,
    product_id  UInt32,
    quantity    UInt32,
    revenue     Decimal(10, 2)
)
ENGINE = SummingMergeTree()
ORDER BY (date, product_id);

-- Вставка сырых данных
INSERT INTO sales_raw VALUES 
('2026-03-01 10:00:00', 1, 5, 100.00),
('2026-03-01 11:00:00', 1, 3, 60.00),
('2026-03-01 12:00:00', 1, 2, 40.00);

-- Агрегация в daily-таблицу
INSERT INTO sales_daily
SELECT 
    toDate(event_time) AS date,
    product_id,
    sum(quantity) AS quantity,
    sum(revenue) AS revenue
FROM sales_raw
GROUP BY date, product_id;

-- После merge: одна строка с суммой
SELECT * FROM sales_daily;
-- date=2026-03-01, product_id=1, quantity=10, revenue=200.00

#Получение агрегированных данных

-- С FINAL для гарантированной агрегации
SELECT 
    date,
    sum(quantity) AS total_quantity,
    sum(revenue) AS total_revenue
FROM sales_daily
GROUP BY date;

-- Или с FINAL (агрегация уже применена)
SELECT 
    date,
    sum(quantity),
    sum(revenue)
FROM sales_daily FINAL
GROUP BY date;

#Когда использовать SummingMergeTree

Когда подходит:

Предварительная агрегация метрик и продаж
Уменьшение объёма данных для дашбордов
Агрегация по времени (часы, дни, месяцы)

Когда лучше выбрать другой движок:

Нужны неагрегированные данные
Строковые колонки должны сохраняться корректно
Требуется точный контроль над агрегацией

#AggregatingMergeTree

#Назначение

AggregatingMergeTree хранит состояния агрегатных функций для инкрементальной агрегации.

Позволяет строить сложные преагрегации с uniq, quantile, groupArray и другими функциями.

#Базовый синтаксис

CREATE TABLE stats
(
    date        Date,
    category    String,
    uniq_users  AggregateFunction(uniq, UInt64),
    sum_time    SimpleAggregateFunction(sum, UInt32),
    max_value   SimpleAggregateFunction(max, Float64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (date, category);

#Типы колонок

AggregateFunction:

Хранит состояние агрегатной функции
Требует указания функции и типов аргументов
Используется с *State при вставке и *Merge при чтении

SimpleAggregateFunction:

Для простых агрегатов (sum, min, max, any, last)
Меньше служебных данных, чем у AggregateFunction
Используется с *Merge при чтении

#Пример: уникальные пользователи по дням

CREATE TABLE daily_users
(
    date        Date,
    platform    LowCardinality(String),
    uniq_users  AggregateFunction(uniq, UInt64),
    page_views  SimpleAggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (date, platform);

-- Вставка с состоянием агрегата
INSERT INTO daily_users
SELECT 
    toDate(event_time) AS date,
    platform,
    uniqState(user_id) AS uniq_users,
    sum(page_views) AS page_views
FROM events
GROUP BY date, platform;

-- Чтение с применением агрегации
SELECT 
    date,
    platform,
    uniqMerge(uniq_users) AS unique_users,
    sumMerge(page_views) AS total_views
FROM daily_users
GROUP BY date, platform;

#Пример: квантили времени ответа

CREATE TABLE response_times
(
    date        Date,
    endpoint    String,
    latency_p50 AggregateFunction(quantileTiming(0.50), UInt16),
    latency_p95 AggregateFunction(quantileTiming(0.95), UInt16),
    latency_p99 AggregateFunction(quantileTiming(0.99), UInt16)
)
ENGINE = AggregatingMergeTree()
ORDER BY (date, endpoint);

-- Вставка
INSERT INTO response_times
SELECT 
    toDate(timestamp) AS date,
    endpoint,
    quantileTimingState(0.50)(response_ms) AS p50,
    quantileTimingState(0.95)(response_ms) AS p95,
    quantileTimingState(0.99)(response_ms) AS p99
FROM api_logs
GROUP BY date, endpoint;

-- Чтение
SELECT 
    date,
    endpoint,
    quantileTimingMerge(0.50)(latency_p50) AS p50_ms,
    quantileTimingMerge(0.95)(latency_p95) AS p95_ms,
    quantileTimingMerge(0.99)(latency_p99) AS p99_ms
FROM response_times
GROUP BY date, endpoint;

#Комбинирование с материализованными представлениями

-- Сырая таблица
CREATE TABLE events_raw
(
    event_time DateTime,
    user_id    UInt64,
    platform   String
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);

-- Таблица агрегатов
CREATE TABLE daily_stats
(
    date       Date,
    platform   String,
    uniq_users AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (date, platform);

-- Материализованное представление для авто-агрегации
CREATE MATERIALIZED VIEW daily_stats_mv TO daily_stats
AS SELECT 
    toDate(event_time) AS date,
    platform,
    uniqState(user_id) AS uniq_users
FROM events_raw
GROUP BY date, platform;

-- Теперь при вставке в events_raw данные автоматически 
-- агрегируются в daily_stats
INSERT INTO events_raw VALUES (now(), 1, 'web');
INSERT INTO events_raw VALUES (now(), 2, 'web');
INSERT INTO events_raw VALUES (now(), 3, 'mobile');

-- Агрегаты уже посчитаны
SELECT 
    platform,
    uniqMerge(uniq_users) AS users
FROM daily_stats
GROUP BY platform;

#Когда использовать AggregatingMergeTree

Когда подходит:

Инкрементальная агрегация с complex функциями (uniq, quantile)
Предварительный расчёт метрик для дашбордов
Агрегация в реальном времени через материализованные представления

Когда лучше выбрать другой движок:

Простые SUM/COUNT (лучше SummingMergeTree)
Нужны детальные данные
Частые изменения исторических данных

#CollapsingMergeTree

#Назначение

CollapsingMergeTree схлопывает пары строк с одинаковым ключом сортировки и противоположными значениями колонки Sign (+1/-1).

Эмулирует UPDATE и DELETE в ClickHouse.

#Базовый синтаксис

CREATE TABLE orders
(
    order_id    UInt64,
    status      String,
    amount      Decimal(10, 2),
    Sign        Int8  -- +1 для вставки, -1 для удаления
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY (order_id);

#Пример: эмуляция UPDATE

-- Вставка заказа
INSERT INTO orders VALUES (1, 'pending', 100.00, +1);

-- "Обновление" статуса: удаляем старую строку, вставляем новую
INSERT INTO orders VALUES (1, 'pending', 100.00, -1);  -- Удаление
INSERT INTO orders VALUES (1, 'shipped', 100.00, +1);  -- Вставка

-- После merge останется одна строка
SELECT * FROM orders FINAL;
-- order_id=1, status='shipped', amount=100.00, Sign=+1

#Пример: эмуляция DELETE

-- Вставка
INSERT INTO orders VALUES (2, 'pending', 50.00, +1);

-- Удаление
INSERT INTO orders VALUES (2, 'pending', 50.00, -1);

-- После merge строка исчезнет
SELECT * FROM orders FINAL;  -- Пусто

#Проблема порядка вставки

Критично: Для корректного схлопывания строки должны вставляться парами в правильном порядке.

-- Плохо: если -1 вставлен до +1, строки не схлопнутся
INSERT INTO orders VALUES (3, 'pending', 75.00, -1);  -- Сначала удаление
INSERT INTO orders VALUES (3, 'pending', 75.00, +1);  -- Потом вставка

-- Результат: останутся ОБЕ строки!

Решение: VersionedCollapsingMergeTree.

#VersionedCollapsingMergeTree

#Назначение

VersionedCollapsingMergeTree решает проблему порядка вставки, используя колонку версии.

#Базовый синтаксис

CREATE TABLE orders_versioned
(
    order_id    UInt64,
    status      String,
    amount      Decimal(10, 2),
    version     UInt32,  -- Версия строки
    Sign        Int8
)
ENGINE = VersionedCollapsingMergeTree(Sign, version)
ORDER BY (order_id);

#Пример: корректное обновление

-- Версия 1
INSERT INTO orders_versioned VALUES (1, 'pending', 100.00, 1, +1);

-- Версия 2 (обновление статуса)
INSERT INTO orders_versioned VALUES (1, 'pending', 100.00, 1, -1);  -- Удаляем v1
INSERT INTO orders_versioned VALUES (1, 'shipped', 100.00, 2, +1);  -- Вставляем v2

-- Схлопывание работает корректно независимо от порядка вставки
SELECT * FROM orders_versioned FINAL;
-- order_id=1, status='shipped', version=2, Sign=+1

#Когда использовать Collapsing/Versioned

Когда подходит:

Эмуляция UPDATE/DELETE в потоковых данных
CDC из транзакционных БД
Ситуации, где нужны актуальные состояния объектов

Когда лучше выбрать другой движок:

Простая дедупликация (лучше ReplacingMergeTree)
Массовые обновления исторических данных
Высокая частота изменений: колонка Sign и пары строк увеличивают объём данных

#GraphiteMergeTree

#Назначение

GraphiteMergeTree агрегирует метрики в стиле Graphite по заданным правилам (retention policies).

#Пример конфигурации

CREATE TABLE metrics
(
    timestamp DateTime,
    metric    String,
    value     Float64
)
ENGINE = GraphiteMergeTree('graphite_config')
ORDER BY (timestamp, metric);

Требует конфигурации в config.xml:

<graphite_rollup>
    <pattern>
        <regexp>^traffic\.</regexp>
        <retention>
            <age>0</age>
            <precision>60</precision>  <!-- 1 минута -->
        </retention>
        <retention>
            <age>3600</age>            <!-- 1 час -->
            <precision>300</precision> <!-- 5 минут -->
        </retention>
    </pattern>
</graphite_rollup>

#Сравнение движков

Характеристика	Replacing	Summing	Aggregating	Collapsing
Дедупликация	Да	Нет	Нет	Парами строк
Суммирование	Нет	Да	Через функции	Нет
Состояния агрегатов	Нет	Нет	Да	Нет
Эмуляция UPDATE	Через версии	Нет	Нет	Да
Порядок вставки	Не важен	Не важен	Не важен	Критичен
Версионирование	Опционально	Нет	Нет	Требуется

#Как проверить выбор движка

ReplacingMergeTree:

Нужно удалить дубликаты по ключу
Есть колонка версии (updated_at)
Допустима задержка на merge

SummingMergeTree:

Нужно суммировать числовые метрики
Строковые колонки не важны
Простая агрегация SUM/COUNT

AggregatingMergeTree:

Нужны сложные агрегаты (uniq, quantile)
Инкрементальное обновление агрегатов
Материализованные представления

CollapsingMergeTree:

Нужна эмуляция UPDATE/DELETE
Поток изменений с парами +1/-1
Контроль порядка вставки

VersionedCollapsingMergeTree:

Как Collapsing, но порядок вставки не гарантирован
Есть версия для каждой строки

#Практика: данные до и после merge

Для ReplacingMergeTree вставьте три версии одного ключа в разные части. Покажите результат обычного запроса, запроса с FINAL и агрегации через argMax. Затем повторите после фонового или контролируемого учебного слияния. В отчёте назовите семантику, на которую может опираться приложение, не предполагая момент фонового слияния.

В следующем уроке эти модели проверяются запросами: обычными агрегатами, функциями для массивов, окнами и JOIN.

Движок

Назначение

Когда использовать

MergeTree

Базовый

Общие случаи

ReplacingMergeTree

Удаление дубликатов

Дедупликация, версии записей

SummingMergeTree

Суммирование агрегатов

Предварительная агрегация числовых данных

AggregatingMergeTree

Сложные агрегаты

Инкрементальные вычисления с состояниями

CollapsingMergeTree

Схлопывание пар строк

Эмуляция UPDATE/DELETE

VersionedCollapsingMergeTree

Схлопывание с версиями

Корректная обработка порядка вставки

GraphiteMergeTree

Агрегация метрик

Интеграция с Graphite/метриками

Характеристика

Replacing

Summing

Aggregating

Collapsing

Дедупликация

Да

Нет

Парами строк

Суммирование

Нет

Да

Через функции

Нет

Состояния агрегатов

Нет

Да

Нет

Эмуляция UPDATE

Через версии

Нет

Да

Порядок вставки

Не важен

Критичен

Версионирование

Опционально

Нет

Требуется