ClickHouse

ClickHouse — колоночная OLAP-база данных от Yandex: аналитические запросы по миллиардам строк за доли секунды. Стандарт для метрик и логов.

Уровень: средний 18 разделов ⏱ 3–4 часа

1. Колоночные vs строковые БД

В строковых СУБД (как MySQL) данные хранятся по строкам — запись быстрая. ClickHouse хранит по столбцам — чтение только нужных колонок, поэтому агрегации по большим объёмам работают в десятки раз быстрее.

2. Установка

docker run -d --name ch -p 8123:8123 -p 9000:9000 \
  clickhouse/clickhouse-server

Веб-интерфейс http://localhost:8123/play, консоль: clickhouse-client.

3. Первые шаги: вставка и выборка

CREATE DATABASE analytics;

CREATE TABLE events (
    ts   DateTime,
    user_id UInt32,
    event  String,
    amount Float64
) ENGINE = MergeTree
ORDER BY (ts, user_id);

INSERT INTO events VALUES
  (now(), 1, 'view', 0.0),
  (now() - INTERVAL 1 HOUR, 2, 'buy', 120.5);

SELECT count() FROM events;

MergeTree — основной движок таблиц (в ClickHouse движки пишутся через ENGINE).

4. Агрегации и оконные функции

SELECT
    toDate(ts) AS day,
    countIf(event = 'buy') AS buys,
    sum(amount) AS revenue
FROM events
GROUP BY day
ORDER BY day DESC;

// скользящее среднее
SELECT ts, avg(amount) OVER (ORDER BY ts
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM events;

5. Типы данных

Основные: UInt32/Int64, Float64, String, DateTime/Date, массивы (Array), кортежи, Nullable, LowCardinality для справочников.

6. Мутация и распределённые таблицы

Update/Delete в ClickHouse поддерживаются ограниченно (асинхронные мутации через ALTER TABLE DELETE). Основной паттерн — append-only и пересоздание партиций.

Кластерная схема: каждая шарда — отдельный сервер (Shard), данные реплицируются через ReplicatedMergeTree, а Distributed-таблица даёт единую точку запроса.

7. TTL и партиционирование

CREATE TABLE metrics (
    ts DateTime,
    value Float64
) ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY ts
TTL ts + INTERVAL 90 DAY;

TTL автоматически удаляет старые строки — идеально для логов и метрик без ручной чистки.

8. Загрузка больших данных

cat events.csv | clickhouse-client \
    --query "INSERT INTO events FORMAT CSV"

Форматы: CSV, JSON, Parquet, Arrow, Native. Вставка пачками в тысячи/миллионы строк — быстро. Для потоков используй Kafka-движок (источники → таблица).

9. Мониторинг и адмика

Системные таблицы: system.parts, system.query_log, system.processes.
Метрики наружу: system.metrics, Prometheus endpoint, Grafana-плагин.

SELECT query, query_duration_ms
FROM system.query_log
ORDER BY query_duration_ms DESC
LIMIT 5;

10. Интеграции

Python: clickhouse-driver / clickhouse-connect.
Из пайплайна: Spark, Kafka напрямую в MergeTree.
Дашборды: Grafana native-источник ClickHouse.

Python-пример:

from clickhouse_connect import get_client

client = get_client(host='localhost', port=8123)
result = client.query('SELECT count() FROM events')
print(result.result_rows)

11. Словари: справочники в памяти

Словари (Dictionaries) загружают в память сервера данные, которые часто используются в запросах: курсы валют, названия городов, группы товаров. Так JOIN к справочнику превращается в дёшевый поиск по памяти.

CREATE DICTIONARY geo_dict
(
    id UInt64,
    name String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(TABLE 'geo'))
LIFETIME(300);

SELECT dictGet('geo_dict', 'name', toUInt64(101));

источники: таблица ClickHouse, файл, HTTP, MySQL;
обновляются по расписанию через LIFETIME или изменению поля;
типы: flat, hashed, complex_key_hashed, range.

Словари избавляют от огромных JOIN при фильтрации по справочным данным — это один из главных приёмов ускорения аналитики.

12. Материализованные представления

Материализованное представление непрерывно пересчитывает агрегаты по мере вставки данных и хранит результат отдельно. Идеально для дашбордов и отчётов по часам:

CREATE MATERIALIZED VIEW mv_daily
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, metric)
AS SELECT toDate(event_time) AS day, metric, sum(value) AS total
FROM events GROUP BY day, metric;

записывай события в обычную таблицу events;
представление само обновляет агрегаты по одной строке;
читай отчёт из mv_daily вместо сканирования сырых данных.

Данные в представлении вычисляются из вставленных строк — при загрузке истории значений «задним числом» агрегаты не пересчитаются автоматически.

13. Оптимизация: EXPLAIN, проекции и индексы

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

EXPLAIN SELECT ... FROM events WHERE user_id = 42;

-- блум-фильтр по секции: ускоряет точечные фильтры
ALTER TABLE events ADD INDEX idx_user user_id
TYPE bloom_filter GRANULARITY 1;
ALTER TABLE events MATERIALIZE INDEX idx_user;

Проекция — «вложенная» копия таблицы с другим порядком сортировки:

ALTER TABLE events ADD PROJECTION p_user
(SELECT * ORDER BY user_id);
ALTER TABLE events MATERIALIZE PROJECTION p_user;

Экспериментируй на данных из урока SQL: EXPLAIN покажет, применилась ли проекция (PROJECTION) или индекс (Index marks), и в каком порядке идут чтения.

14. Экспорт и резервное копирование

Данные выгружают в файлы, CI или хранилища напрямую запросами:

SELECT * FROM events INTO OUTFILE 'events.csv'
FORMAT CSVWithNames;
SELECT * FROM events FORMAT JSONEachRow;

Полноценные бэкапы делаются утилитой clickhouse-backup:

clickhouse-backup create --backup_name=b1
clickhouse-backup restore b1 --schema

Восстановление регулярно проверяй на тестовом сервере — бэкап, который не проверен восстановлением, почти бесполезен.

15. Пользователи, роли и права доступа

ClickHouse по умолчанию не требует пароля — открытый порт 8123 значит доступ к данным. Начни с создания пользователей и ролей:

CREATE USER analyst IDENTIFIED WITH sha256_password BY 'secret';
CREATE ROLE reports;
GRANT SELECT ON analytics.* TO reports;
GRANT reports TO analyst;

права: SELECT, INSERT, ALTER, а для администраторов — full; выдавай минимальное;
ограничивай не только таблицы: SETTINGS задают лимиты на max_memory_usage, max_execution_time, результаты;
все сессии можно посмотреть в system.sessions (для отслеживания подозрительной активности).

GRANT SELECT ON analytics.* TO analyst
WITH SETTINGS max_memory_usage = 2000000000;

Ключевой пароль по умолчанию в конфигурации users.xml — это шаблон, а не защита: меняй или добавляй своих пользователей при первом деплое.

16. Массивы и вложенные структуры

Колонки ClickHouse легко держат сложные типы — массивы, карты и вложенные структуры:

SELECT user_id,
       arraySum(tags, 0),           -- сумма значений
       length(tags)                -- число элементов массива
FROM events;

Для «разворачивания» массива в строки используй arrayJoin:

SELECT user_id, tag
FROM events
ARRAY JOIN tags AS tag;

Nested — структура для хранения нескольких списков одной строки (например, действия за посещение);
Map — ключи-значения без жёсткой схемы, достуются через map['key'];
функции groupArray и arrayMap работают «наоборот»: собирают массив из строк.

Массивы экономят строки и сеть, но не увлекайся: слишком широкие вложенные структуры усложняют запросы и затрудняют чтение кода команды.

17. Развитие схемы: ALTER и совместимость

Схема в аналитике меняется часто: добавить колонку, поднять точность, переименовать. Основные команды:

ALTER TABLE events ADD COLUMN IF NOT EXISTS platform String DEFAULT 'web';
ALTER TABLE events MODIFY COLUMN price Decimal(18, 4);
ALTER TABLE events DROP COLUMN tmp_field;

Типичный поток безопасного изменения:

добавь новую колонку с DEFAULT — старые читаются без переписывания файлов;
заполни значения через UPDATE или повторный импорт;
проверь на копии таблицы, затем делай MUTATION; данные переписываются асинхронно.

SELECT mutation_id, is_done FROM system.mutations
WHERE table = 'events';

Костыли вроде «уберём поле потом» копят мутации и тормозят чтение. Планируй MUTATION вне пиковых часов.

18. Кейс: аналитический пайплайн событий

Соберём end-to-end: приложение шлёт события → Kafka → ClickHouse → дашборд Grafana.

клиент пишет JSON-строку в топик events;
stream-потребитель вставляет батчами в таблицу events;
материализованное представление агрегирует метрики по часам;
Grafana рисует дашборд прямо по представлению.

INSERT INTO events (user_id, event, ts) SELECT
  JSONExtractString(json, 'user_id'),
  JSONExtractString(json, 'event'),
  now()
FROM kafka_events;

проверяй отставание потребителя: sumMessagesLag в system.metrics лучший показатель здоровья;
схема событий редко стабильна — используй JSON-колонку или запасную строку под произвольные поля.

Индекс не поможет при полном перескане по трём месяцам: фильтруй по дате и не вставляй события без party-ключа времени.

19. clickhouse-local: аналитика без сервера

Утилита clickhouse-local запускает движок прямо в терминале — без серверного процесса. Удобно для файлов, ad-hoc-запросов и конвейеров:

clickhouse-local --query "SELECT count() FROM file('events.tsv')"
clickhouse-local --query "SELECT toDate(ts), count()
FROM file('*.json') GROUP BY 1"

управляй форматами: --input-format, --output-format=JSONEachRow;
конвейер из одной строки: читаем CSV, пишем Parquet;
тестируй запрос локально до того, как грузить его в кластер.

clickhouse-local — идеальный полигон для отработки EXPLAIN, прежде чем трогать боевые таблицы.

20. Фоновые мержи: настройка и симптомы

За каруселью партов отвечают фоновые потоки. Копятся парты — падает производительность:

SELECT count() AS parts, table, partition
FROM system.parts
WHERE active AND table = 'events'
GROUP BY table, partition ORDER BY parts DESC LIMIT 10;

параметры слияния живут в настройках MergeTree (например, parts_to_throw_insert);
частые INSERT маленькими кусками — главный источник «паники мержей»;
при завале смотри system.merges и логи фоновых движков.

Не дёргай OPTIMIZE TABLE FINAL после каждого батча: это блокирует кластер и не заменяет фоновые мержи.

21. Репликация и шардирование: что выбрать

ReplicatedMergeTree решает отказоустойчивость, шарды — горизонтальный рост:

# движок таблицы с репликами
ENGINE = ReplicatedMergeTree('/clickhouse/tables/events', '{replica}')

стартуй с реплик на двух узлах — они защищают от потери данных;
шардируй при десятках ТБ и потоках, которые не переваривает одна машина;
распределённые таблицы лишь маршрутизируют запросы по шардам — сами они не ускоряют.

Желание «разделить всё сразу» даёт оверхед: реплики выгоднее по цене, пока не упёрлись в CPU.

22. Типичные грабли и чек-лист запроса

Клиент быстро прививает «плохие» привычки. Пройди по чек-листу перед «плановым» запросом:

фильтруй по ключу партиционирования — иначе полный скан;
не тяни *: запрашивай только нужные колонки;
count(x) игнорирует NULL — выбирай countIf явно;
по unix-колонке без функции индексы не помогут — работай с датами.

EXPLAIN PLAN indexes=1 SELECT ...
FROM events WHERE event_date = today() AND user_id = 1001;

Держи под рукой system.query_log: самый медленный запрос недели расскажет, что оптимизировать.

Что дальше

Для потоковой поставки данных в ClickHouse используй Kafka; аналитика на больших кластерах — Apache Spark.

← Вернуться к курсам