Базы данных временных рядов: ClickHouse

Показания приборов учета, сетевая телеметрия, логи серверов и финансовые тики принадлежат к особому классу данных — временным рядам (Time-Series Data). Они обладают характерными свойствами:

1. Каждая запись жестко привязана к метке времени (timestamp).
2. Данные неизменяемы (Append-Only): показание температуры за прошлый четверг никогда не меняется задним числом.
3. Огромный входящий поток: десятки и сотни тысяч записей в секунду.
4. Характер запросов аналитический: расчет средних, квантилей, агрегатов по скользящим временным окнам и поиск аномалий среди миллиардов исторических строк ¹.

Попытка использовать для таких задач традиционные строковые СУБД (PostgreSQL) приводит к исчерпанию дискового пространства и деградации производительности. Специализированной и наиболее быстрой колоночной аналитической СУБД реального времени стала система ClickHouse, разработанная в Яндексе для сервиса Яндекс.Метрика и открытая в 2016 году ².

В этой теме мы подробно разберем архитектуру ClickHouse: семейство табличных движков MergeTree, разреженные индексы, специализированные кодеки сжатия временных рядов (Gorilla, DoubleDelta) и непрерывное предагрегирование с материализованными представлениями.

Семейство движков MergeTree

Базовым фундаментом ClickHouse является семейство табличных движков MergeTree (дерево слияния). Название отражает ключевой принцип работы: новые порции данных записываются на диск отдельными неизменяемыми кусками (parts), которые затем в фоновом режиме непрерывно объединяются и упорядочиваются алгоритмом слияния (merge), аналогично структуре LSM-дерева (Log-Structured Merge-Tree) ³.

Синтаксис создания базовой таблицы для телеметрии:

CREATE TABLE telemetry_readings (
    device_id UInt32,
    metric_type LowCardinality(String),
    recorded_at DateTime64(3, 'UTC'),
    val Float64,
    status UInt8
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(recorded_at)
PRIMARY KEY (metric_type, device_id)
ORDER BY (metric_type, device_id, recorded_at);

Ключевые параметры MergeTree

1. PARTITION BY (партиционирование): Крупноблочное физическое разделение данных по отдельным каталогам на диске (чаще всего по месяцам: toYYYYMM(recorded_at)). Позволяет мгновенно удалять или архивировать старые данные командой ALTER TABLE DROP PARTITION без построчного удаления.
2. ORDER BY (ключ сортировки): Главный параметр, определяющий физический порядок хранения строк на диске. Данные внутри каждого куска отсортированы строго по кортежу (metric_type, device_id, recorded_at).
3. PRIMARY KEY (первичный ключ): В отличие от реляционных баз, в ClickHouse первичный ключ не обеспечивает уникальность и не строит плотное B-дерево для каждой строки! Он строит сверхкомпактный разреженный индекс (Sparse Index).

Разреженный индекс (Sparse Index)

В миллионной таблице создание B-дерева на каждую запись потребовало бы гигабайты оперативной памяти. ClickHouse использует разреженный индекс:

• Таблица логически нарезается на гранулы (по умолчанию index_granularity = 8192 строки).
• В оперативной памяти сохраняется только значение первичного ключа для первой строки каждой гранулы (индексная засечка):

Гранула 0: (metric="temp", id=101, time=10:00:00) --> Смещение в файле столбца 0
Гранула 1: (metric="temp", id=101, time=12:15:00) --> Смещение в файле столбца 8192
Гранула 2: (metric="temp", id=102, time=08:00:00) --> Смещение в файле столбца 16384

Весь индекс для таблицы на 1 миллиард строк занимает всего несколько десятков мегабайт и целиком помещается в RAM. При запросе с условием WHERE metric_type = 'temp' AND device_id = 101 ClickHouse по разреженному индексу за доли миллисекунды вычисляет номера гранул, в которых могут находиться нужные строки, и читает с диска только эти гранулы.

Специализированные алгоритмы сжатия временных рядов

Колоночное хранение позволяет ClickHouse применять цепочки кодирования и сжатия на уровне каждого отдельного столбца (CODEC(...)) ⁴:

CREATE TABLE compressed_telemetry (
    recorded_at DateTime CODEC(DoubleDelta, ZSTD),
    val Float64 CODEC(Gorilla, ZSTD)
) ENGINE = MergeTree() ORDER BY recorded_at;

1. DoubleDelta (двойная дельта): Для строго упорядоченных меток времени сохраняются не сами даты, и даже не разности между ними, а разности разностей (вторая производная). Если данные поступают строго раз в 10 секунд, вторая производная равна 0. Цепочка нулей упаковывается битовым сжатием практически в ноль байт.
2. Gorilla (алгоритм сжатия вещественных чисел от Facebook): Специализированный алгоритм для сжатия чисел с плавающей точкой Float32/Float64 ⁵. Он вычисляет побитовое исключающее ИЛИ (XOR) между соседними вещественными значениями. Если показания датчика меняются слабо (например, температура колеблется между 21.4 и 21.5), старшие биты экспоненты и мантиссы совпадают, и XOR порождает длинные серии нулевых бит, сжимаемые префиксным кодированием.

В результате телеметрия сжимается в 10–20 раз по сравнению с исходным текстовым форматом.

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

• ReplacingMergeTree: При фоновом слиянии кусков автоматически удаляет дубликаты записей по ключу сортировки, оставляя самую свежую версию по полю ревизии (используется для идемпотентной дозагрузки данных).
• SummingMergeTree: При слиянии автоматически суммирует числовые столбцы для строк с одинаковым ключом сортировки (идеально для счетчиков трафика и расхода).
• AggregatingMergeTree: Позволяет сохранять промежуточные состояния агрегатных функций (AggregateFunction), обеспечивая инкрементальный расчет квантилей, уникальных пользователей (uniqHLL12) и средних значений.

Материализованные представления (Materialized Views)

Одной из самых мощных возможностей ClickHouse для IoT являются материализованные представления (Materialized Views). В отличие от других СУБД, в ClickHouse Materialized View — это не снимок данных, а триггер на вставку (Insert Trigger):

-- Таблица агрегированных суточных показателей:
CREATE TABLE daily_metrics_mv (
    device_id UInt32,
    day Date,
    max_val Float64,
    total_samples UInt64
) ENGINE = SummingMergeTree()
ORDER BY (device_id, day);

-- Материализованное представление, наполняющееся на лету при каждой вставке:
CREATE MATERIALIZED VIEW mv_daily_telemetry TO daily_metrics_mv AS
SELECT device_id,
       toDate(recorded_at) AS day,
       max(val) AS max_val,
       count(*) AS total_samples
FROM telemetry_readings
GROUP BY device_id, day;

При поступлении сырых данных в telemetry_readings ClickHouse параллельно преобразует их и дописывает в агрегированную витрину daily_metrics_mv. Запросы дашбордов обращаются к предрассчитанной витрине, выполняясь мгновенно независимо от общего объема накопленной сырой телеметрии.

Итоги темы

ClickHouse воплотил в себе передовые идеи проектирования сверхскоростных аналитических хранилищ: колоночное хранение, семейство движков MergeTree с фоновым слиянием, компактные разреженные индексы и алгоритмы сжатия временных рядов (DoubleDelta, Gorilla). Использование материализованных представлений позволяет строить витрины непрерывной агрегации данных телеметрии в реальном времени с пропускной способностью в миллионы событий в секунду.

Литература

1. Kleppmann M. Designing Data-Intensive Applications. — O'Reilly Media, 2017 ¹.
2. ClickHouse Documentation. ClickHouse Architecture and Core Principles. — ClickHouse Inc., 2024. https://clickhouse.com/docs/ ².
3. O'Neil P., Cheng E., Gawlick D., O'Neil E. The Log-Structured Merge-Tree (LSM-Tree). — Acta Informatica, 1996, Vol. 33, No. 4, С. 351–385 ³.
4. ClickHouse Documentation. Column Compression Codecs: Gorilla and DoubleDelta. — ClickHouse Inc., 2024 ⁴.
5. Pelkonen T., Franklin S., Teller J., Cavallaro P., Huang Q., Meza J., Kaushik V. Gorilla: A Fast, Scalable, In-Memory Time Series Database. — Proceedings of the VLDB Endowment, 2015, Vol. 8, No. 12, С. 1816–1827 ⁵.

Дополнительные материалы

Вопросы для самопроверки

1. Каковы характерные особенности временных рядов (Time-Series) и почему строковые СУБД показывают низкую эффективность при их хранении?
2. В чём состоит ключевая идея движка MergeTree? Как устроен процесс фонового слияния кусков (parts)?
3. Чем первичный ключ в ClickHouse отличается от первичного ключа в PostgreSQL или MySQL? Почему он не проверяет уникальность?
4. Как устроен разреженный индекс (Sparse Index)? Что такое гранула и почему индекс занимает считанные мегабайты для миллиардной таблицы?
5. Как правильно выбрать порядок столбцов в кортеже ORDER BY (...) таблицы MergeTree?
6. Как работает алгоритм сжатия временных меток DoubleDelta? Почему регулярный шаг поступления данных даёт колоссальный коэффициент сжатия?
7. В чём математический принцип кодека Gorilla для вещественных чисел (Float)? Какую роль играет операция XOR между соседними значениями?
8. Чем табличный движок ReplacingMergeTree отличается от базового MergeTree? Когда фактически происходит дедупликация строк?
9. Каким образом материализованные представления (Materialized Views) в ClickHouse действуют как триггеры на вставку?
10. Почему операция удаления отдельных строк (DELETE WHERE ...) в ClickHouse является «тяжелой» мутацией и как партиционирование позволяет удалять старые данные бесплатно?

Глоссарий темы

Шпаргалка по аналитическим командам ClickHouse

-- Просмотр структуры кусков таблицы, их размеров и степени сжатия:
SELECT partition, name, rows, bytes_on_disk,
       formatReadableSize(bytes_on_disk) AS readable_size,
       round(data_uncompressed_bytes / bytes_on_disk, 2) AS compression_ratio
FROM system.parts
WHERE table = 'telemetry_readings' AND active = 1;

-- Принудительный запуск фонового слияния кусков:
OPTIMIZE TABLE telemetry_readings FINAL;

-- Мгновенное удаление исторической партиции за прошлый год:
ALTER TABLE telemetry_readings DROP PARTITION 202301;

-- Быстрый расчет 50-го, 90-го и 99-го квантилей потребления:
SELECT device_id,
       quantilesExactWeighted(0.5, 0.9, 0.99)(val, 1) AS quantiles
FROM telemetry_readings
GROUP BY device_id;

Типичные ошибки и подводные камни

1. Множество мелких вставок (Too many parts exception): Отправка каждого отдельного показания отдельным INSERT порождает тысячи мелких кусков, перегружает файловую систему и приводит к ошибке Too many parts in all data in table. Данные необходимо буферизовать на стороне продюсера (пакетами от 10 000 строк) или применять движок Buffer.
2. Неверный порядок столбцов в ORDER BY: Если первым столбцом в ключе сортировки указать монотонно растущий timestamp, а вторым device_id, то запрос с фильтром по конкретному датчику WHERE device_id = 42 вынужден будет сканировать весь индекс. Столбец низкой кардинальности (device_type, device_id) должен стоять в начале ключа сортировки.
3. Ожидание мгновенной дедупликации в ReplacingMergeTree: Движок ReplacingMergeTree выполняет схлопывание строк только во время случайных фоновых слияний. Выборка без модификатора FINAL или явной группировки может возвращать дубликаты.

Литература

1. ClickHouse Official Documentation. Core Concepts and Architecture. — 2024 ¹.
2. Pelkonen T. et al. Gorilla: A Fast, Scalable, In-Memory Time Series Database. — VLDB, 2015 ².
3. O'Neil P. et al. The Log-Structured Merge-Tree (LSM-Tree). — Acta Informatica, 1996 ³.

Лабораторная работа 8. Высокоскоростная аналитика телеметрии в ClickHouse: движки MergeTree и материализованные представления

• Раздел курса: тема 8 «Базы данных временных рядов: ClickHouse»

Введение

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

В этой лабораторной работе вы развернете хранилище телеметрии на базе СУБД ClickHouse, спроектируете оптимальный первичный ключ для разреженного индекса таблицы MergeTree, протестируете специализированные алгоритмы сжатия временных рядов Gorilla и DoubleDelta, а также настроите конвейер предагрегации данных на основе материализованных представлений (Materialized View).

Цель работы

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

После выполнения работы вы сможете:

• проектировать таблицы семейства MergeTree с учетом физического порядка сортировки данных;
• применять кодеки DoubleDelta и Gorilla для минимизации объема дискового пространства;
• исследовать структуру кусков и работу разреженного индекса через системную таблицу system.parts;
• создавать материализованные представления с автоматическим расчетом агрегатов при вставке данных.

Оборудование и учебная среда

Для выполнения работы потребуются:

• сервер ClickHouse (локальный или контейнер) и клиент clickhouse-client (либо веб-интерфейс / clickhouse-local);
• учебный набор данных почасовых показаний приборов учета readings_large.parquet (или CSV).

Порядок выполнения работы

Часть 1. Проектирование таблицы MergeTree и ключа сортировки

1. Подключитесь к ClickHouse через консольный клиент:

   clickhouse-client
   

2. Создайте базу данных smart_metering:

   CREATE DATABASE IF NOT EXISTS smart_metering;
   USE smart_metering;
   

3. Создайте базовую таблицу показаний с разреженным индексом по типу прибора, его идентификатору и времени:

   CREATE TABLE telemetry_raw (
       device_type LowCardinality(String),
       device_id UInt32,
       timestamp DateTime,
       consumption Float64,
       pressure Float32
   ) ENGINE = MergeTree()
   PARTITION BY toYYYYMM(timestamp)
   PRIMARY KEY (device_type, device_id)
   ORDER BY (device_type, device_id, timestamp);
   

4. Загрузите учебный набор данных в созданную таблицу:

   INSERT INTO telemetry_raw SELECT * FROM file('readings_large.parquet', 'Parquet');
   

Часть 2. Применение специализированных кодеков сжатия

1. Создайте вторую таблицу с явным указанием кодеков сжатия DoubleDelta для времени и Gorilla для вещественных показаний:

   CREATE TABLE telemetry_optimized (
       device_type LowCardinality(String),
       device_id UInt32,
       timestamp DateTime CODEC(DoubleDelta, ZSTD),
       consumption Float64 CODEC(Gorilla, ZSTD),
       pressure Float32 CODEC(Gorilla, ZSTD)
   ) ENGINE = MergeTree()
   PARTITION BY toYYYYMM(timestamp)
   PRIMARY KEY (device_type, device_id)
   ORDER BY (device_type, device_id, timestamp);
   

2. Скопируйте данные из первой таблицы во вторую:

   INSERT INTO telemetry_optimized SELECT * FROM telemetry_raw;
   

3. Сравните физический размер колонок и коэффициент сжатия обеих таблиц через системный каталог:

   SELECT table, column,
          formatReadableSize(data_compressed_bytes) AS compressed,
          formatReadableSize(data_uncompressed_bytes) AS uncompressed,
          round(data_uncompressed_bytes / data_compressed_bytes, 2) AS ratio
   FROM system.columns
   WHERE database = 'smart_metering' AND table IN ('telemetry_raw', 'telemetry_optimized')
   ORDER BY column, table;
   

Часть 3. Анализ чтения гранул разреженного индекса

1. Выполните точечный запрос показаний конкретного датчика за сутки с замером метрик выполнения:

   SELECT count(*), avg(consumption), max(pressure)
   FROM telemetry_optimized
   WHERE device_type = 'water' AND device_id = 4242
     AND timestamp BETWEEN '2024-03-01 00:00:00' AND '2024-03-01 23:59:59';
   

2. Посмотрите на системную строку выполнения: сколько строк и байт было прочитано с диска относительно общего объема таблицы.
3. Проанализируйте план запроса с помощью EXPLAIN indexes = 1:

   EXPLAIN indexes = 1
   SELECT count(*)
   FROM telemetry_optimized
   WHERE device_type = 'water' AND device_id = 4242;
   

   Убедитесь, что отсечение гранул отбросило подавляющую часть таблицы без чтения с диска.

Часть 4. Непрерывная агрегация через Materialized View

1. Создайте целевую таблицу для суточных агрегатов на движке SummingMergeTree:

   CREATE TABLE telemetry_daily_aggregates (
       device_type LowCardinality(String),
       device_id UInt32,
       date Date,
       total_consumption Float64,
       readings_count UInt32
   ) ENGINE = SummingMergeTree()
   ORDER BY (device_type, device_id, date);
   

2. Создайте материализованное представление, связывающее входящий поток с целевой таблицей:

   CREATE MATERIALIZED VIEW mv_telemetry_daily TO telemetry_daily_aggregates AS
   SELECT device_type,
          device_id,
          toDate(timestamp) AS date,
          sum(consumption) AS total_consumption,
          count(*) AS readings_count
   FROM telemetry_optimized
   GROUP BY device_type, device_id, date;
   

3. Вставьте новую тестовую пачку данных в telemetry_optimized и убедитесь, что данные мгновенно отразились в предрассчитанной витрине telemetry_daily_aggregates.

Журнал работы

Подготовьте отчет о выполнении лабораторной работы, содержащий:

1. DDL-запросы созданных таблиц telemetry_raw и telemetry_optimized.
2. Сравнительную таблицу объемов колонок на диске и коэффициентов сжатия со стандартными кодеками и с кодеками DoubleDelta/Gorilla.
3. Вывод EXPLAIN indexes = 1 с демонстрацией пропуска гранул разреженного индекса.
4. Проверку корректности работы материализованного представления при пакетной вставке.