Подсчет в ClickHouse: точность, производительность и архитектура

Краткое введение

Подсчет данных - одна из базовых операций аналитических систем. В ClickHouse счет предоставляет отправной камень для метрик, ретроспективной аналитики и мониторинга бизнес-процессов. Правильная организация подсчета влияет на latency дашбордов, когерентность метрик между сервисами и экономичность использования ресурсов. Эта глава систематизирует теоретические основы, методологии и практические подходы к подсчету в ClickHouse: от обычного счётчика строк до эффективных подсчётов уникальных значений и агрегированных счетов по различным срезам данных. Особое внимание уделяется архитектурным решениям, которые позволяют держать подсчеты точными на больших объемах и в распределённых окружениях, а также рискам, ограничениями и типичным ошибкам.

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

Теоретические основы и терминология

Агрегатные функции и счетчики
- count() - базовый счёт строк в наборе данных. В большинстве сценариев даёт точное число строк, удовлетворяющих условиям фильтра.
- countIf(condition) - счёт строк, где условие истинно. Позволяет быстро получить метрики без явного фильтра в SELECT.
- count(column) - подсчёт ненулевых значений колонки. В ClickHouse трактуется как подсчёт не-null значений по указанной колонке.
- uniqExact(x) - точное количество уникальных значений; применяется для столбцов-дименсий, где необходима точность.
- uniq(x) - приближённое количество уникальных значений (HyperLogLog-подход). Меньше памяти, выше скорость на больших объёмах, но возможны отклонения.
- approxCountDistinct(x) - ещё один вариант приближенного подсчета уникальных значений, часто применяемый для больших выборок.
Разделение и распределение
- MergeTree и его производные (ReplicatedMergeTree, SummingMergeTree и пр.) обеспечивают эффективное хранение и агрегацию.
- Distributed engine - логическая обёртка над кластером, позволяющая выполнять запрос по всем shard’ам и агрегировать результаты.
Архитектурные концепты
- Точность против скорости: точные подсчёты требуют полного сканирования или пред-агрегирования; приближённые подсчёты - через гиперлоглог-листовые структуры.
- Предагрегирование: использование материализованных представлений и агрегирующих таблиц для ускорения повторяющихся подсчётов.
- Время жизни данных: partitioning по дате (или другому ключу) облегчает управление историей подсчётов и автоматическое удаление старых агрегатов.

Методологии и подходы

Выбор между точностью и скоростью
- Для оперативной аналитики в реальном времени чаще выбирают точность на уровне выборки с помощью countIf и суммирования по диапазонам, а для глобальных дашбордов - приближённую оценку уникальных значений и предварительную агрегацию.
Построение стратегий агрегирования
- Локальные подсчёты на узле: счета идут через локальные MergeTree-элементы и локальные агрегации.
- Глобальная агрегация на уровне кластера: через Distributed engine, который разворачивает запрос по всем узлам и приводит их к одной итоговой метрике.
- Материализованные представления (Materialized Views) для ежедневных/помесячных счетов.
Контроль качества и откликов
- Валидируйте точность с помощью выборок и сравнения с тестовыми наборами.
- Введите регрессионные тесты на ключевые метрики: общее число событий, число уникальных пользователей, число транзакций по дням.

Архитектура и технологическая реализация

Архитектурная схема подсчета
- Источник данных: события, логи, транзакции. Таблица в ClickHouse на MergeTree-основе.
- Ингест: логи или потоки событий через конвейеры ETL/ELT (Kafka, ClickHouse intake) с постоянной политикой датификации.
- Хранение: распределённые таблицы на основе ReplicatedMergeTree для устойчивости и более предсказуемой агрегации.
- Выровненные счётчики: использование Distributed таблиц для линейной масштабируемости.
- Расчёт и агрегация: локальные агрегации (count, countIf) и глобальные агрегации через Distributed.
- Предагрегирования: AggregatingMergeTree, materialized views для ежедневных/помесячных счетов.

Технологическая реализация и примеры

Пример структуры таблицы событий:


    CREATE TABLE events
    (
        event_time DateTime,
        user_id UInt64,
        event_type String,
        amount Float64,
        country String
    )
    ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
    PARTITION BY toYYYYMM(event_time)
    ORDER BY (event_time, user_id);

Пример локального счёта:


    SELECT count() AS total_events
    FROM events
## WHERE event_time >= toStartOfDay(now())
      AND event_time

Пример счёта по условию:


    SELECT countIf(event_type = 'purchase') AS purchases_today
    FROM events
## WHERE event_time >= toStartOfDay(now())
      AND event_time

Пример подсчёта уникальных пользователей:


    SELECT uniqExact(user_id) AS daily_unique_users
## FROM events
## WHERE event_time >= toDateTime('2024-12-01 00:00:00')
      AND event_time

Пример приближённого подсчёта уникальных значений:


    SELECT uniq(user_id) AS approx_unique_users
## FROM events
## WHERE event_time >= toDateTime('2024-12-01 00:00:00')
      AND event_time

Пример использования SAMPLE для ускорения:


    SELECT count() AS sampled_events
## FROM events SAMPLE 0.1
## WHERE event_time >= toDateTime('2024-12-01 00:00:00')
      AND event_time

Пример агрегации через materialized view:


    CREATE MATERIALIZED VIEW mv_daily_event_counts TO daily_counts AS
    SELECT toDate(event_time) AS dt,
           count() AS total_events,
           countIf(event_type = 'purchase') AS purchases
    FROM events
    GROUP BY dt;

Пример использования Distributed engine:
```
    CREATE TABLE events ON CLUSTER my_cluster
    AS ENGINE = Distributed(my_cluster, db, events, rand());
```
Риски, ограничения и типовые ошибки
Точность против масштабируемости
- Точные подсчёты across большой выборки требуют полного сканирования и времени. Приближённые методы (uniq, approxCountDistinct) экономят ресурсы, но приводят к отклонениям. Выбор зависит от требований бизнеса к точности.
Распределённые вычисления
- При неправильной конфигурации Distributed-таблиц возможны дубли подсчёта на уровне реплик. Чтобы избежать этого, используйте ReplicatedMergeTree с корректной настройкой зоопарка и избегайте прямых join-операций между репликами.
- В случае пустых partition ресурсов следует явно ограничивать диапазоны (например, по дате) или использовать sampling для быстрых предварительных подсчётов.
Предагрегирование и задержки
- Материализованные представления дают быструю доставку ready-метрик, но требуют синхронизации с основными данными. Они могут быть устаревшими на момент запроса, особенно в потоковых сценариях.
Влияние на производительность
- Частые полноскановые подсчёты на больших таблицах могут привести к деградации производительности, если запросы не ограничены по времени/дате. Рекомендуется разделение по partition, ограничение временных окон, использование индексов по ключам (ORDER BY) и разумное использование SAMPLE.
Особенности функций
- count() обычно точен на уровне локального узла, но суммарный результат через Distributed требует аккуратного проектирования, чтобы исключить дубли или пропуски из-за фильтров или времени.
- countIf в больших данных лучше выполнять на уровне локального узла, а затем агрегировать глобально, чтобы снизить пересылку данных.
Ошибки проектирования
- Неправильная выборка по времени, пропуск ключевых колонок, отсутствие предагрегирования по дате - всё это приводит к задержкам и неточным метрикам.
- Игнорирование различий между точными и приближенными счетами может привести к несоответствиям между источниками данных внутри организации.

Технические детали реализации (алгоритмы, схемы, протоколы, интеграции)

Алгоритм точного подсчета в распределённой среде
1. Каждый шард выполняет локальный count() по заданному окну времени.
2. Результаты отправляются в узел агрегации через Distributed-узел.
3. Финальная агрегация выполняется на уровне координации кластера, возвращая точное число.
Алгоритм подсчета с условием
- countIf(cond) после WHERE-условия выполняется локально на шардах и агрегируется глобально; это ускоряет процесс, избегая передачи большого объёма строк.
Подсчёт уникальных значений
- uniqExact(column) требует дополнительной памяти и времени, но даёт точное значение.
- uniq(column) и approxCountDistinct(column) быстро оценивают уникальные значения, при этом память и временные затраты существенно меньше.
Интеграции и рабочие паттерны
- Интеграция с потоками (Kafka, Apache Pulsar) для непрерывной инкрементной загрузки и подсчётов во времени.
- Материализованные представления: MV позволяют держать готовые агрегаты на сутки/неделю/месяц в отдельных таблицах, что ускоряет повторные запросы.
- Встраивание в дата-слой организации (ETL/ELT) с использованием предикатов по времени и по регионам.
Архитектурные решения: выбор движков
- ReplicatedMergeTree: устойчивость к сбоям и возможность параллельной агрегации на уровнях реплик.
- AggregatingMergeTree: для спецэффективной агрегации с несколькими стадиями, в частности для подсчётов по группам.
- Distributed: распределённое исполнение запросов по кластерам.
- Пример контура архитектуры подсчётов:
  - Источник: события -> таблица events (ReplicatedMergeTree)
  - Внутренний инжинг: поток через Kafka
  - Вспомогательные агрегаты: MV для ежедневного счета
  - Финальная выдача: запрос через Distributed на кластере, возвращающий итоговый счёт
Примеры реальных реализаций
- Open-source: ClickHouse и экосистема агрегатов; Druid и Pinot как альтернативы для специфических сценариев с быстрыми запросами по времени и мульти-датчиками.
- Российские продукты: Яндекс ЯDB (Яндекс) как пример распределённой SQL-базы; Яндекс.Облако предоставляет управляемый сервис ClickHouse и интеграцию с другими сервисами данных в рамках экосистемы. Эти решения часто используются для построения метрических дашбордов и CTR-аналитики в крупных проектах.
- Пример архитектурного паттерна на российской почве: входящие события и логирование в ClickHouse, затем использование MV и Distributed для подсчётов по регионам и дням, дополнительно интегрированное с YDB как источником уникальных пользователей для расчётов аудитории.

Примеры практической реализации

Подсчёт общего числа событий за день


  SELECT toDate(event_time) AS dt,
         count() AS total_events
## FROM events
## WHERE event_time >= toDateTime('2024-12-01 00:00:00')
    AND event_time

Подсчёт количества покупок за день


## SELECT toDate(event_time) AS dt,
         countIf(event_type = 'purchase') AS purchases
## FROM events
## WHERE event_time >= toDateTime('2024-12-01 00:00:00')
    AND event_time

Подсчёт уникальных пользователей за день (точно)


  SELECT toDate(event_time) AS dt,
         uniqExact(user_id) AS daily_unique_users
## FROM events
## WHERE event_time >= toDateTime('2024-12-01 00:00:00')
    AND event_time

Подсчёт уникальных пользователей за день (приближённо)


## SELECT toDate(event_time) AS dt,
         uniq(user_id) AS daily_unique_users_approx
## FROM events
## WHERE event_time >= toDateTime('2024-12-01 00:00:00')
    AND event_time

Пример использования PRE-агрегаций через MV


  CREATE MATERIALIZED VIEW mv_daily_purchases TO daily_counts AS
## SELECT toDate(event_time) AS dt,
         countIf(event_type = 'purchase') AS purchases
  FROM events
  GROUP BY dt;

Вопрос-Ответ (FAQ)

Какой режим выборов счётчика использовать: count() против countIf()?

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

Когда использовать uniqExact() вместо uniq()?

uniqExact() даёт точное число уникальных значений, но требует больше памяти и времени на больших данных. uniq() - быстрый и эффективный приближённый счётчик, полезен для дашбордов в реальном времени, где допускаются небольшие погрешности.

Что такое MV и как он помогает счётам?

Материализованные представления (MV) поддерживают предагрегированные счёты, например ежедневные итоги по событиям. Это позволяет быстро отдавать часто запрашиваемые метрики без повторного сканирования исходной таблицы.

Как избежать двойного счёта в Distributed?

Убедитесь, что используете Distributed engine на основе сверки по репликам и избегайте суммирования дубликатов. Репликационные движки (ReplicatedMergeTree) и корректная конфигурация ZooKeeper помогают снизить риск дублирования.

Можно ли считать по данным в реальном времени?

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

Какие риски связаны с SAMPLE?

SAMPLE даёт ускорение за счёт выборки части данных, но может ухудшить точность, особенно для редких событий. Используйте как предварительный скоринг и во вторую очередь - как основу для финального подсчета.

Какие типичные ошибки встречаются при проектировании подсчётов?

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

Какие российские и open-source примеры полезны для изучения?

Open-source: ClickHouse (сам проект), Apache Druid, Apache Pinot - для сравнения подходов к подсчетам и агрегациям в колонно-ориентированных системах и распределённых архитектурах.
Российские продукты: Яндекс ЯDB (Яндекс.ЯDB) как пример распределённой SQL-базы; Яндекс.Облако предлагает управляемый сервис ClickHouse и тесную интеграцию с данными экосистемы. Эти кейсы иллюстрируют применение точного и приближённого счётов в реальных продуктах и в инфраструктурах большой корпорации.

Какой подход выбрать для больших данных и долговременных метрик?

Комбинация: точный подсчёт по текущим окнам (count, countIf) на локальном уровне, приглушённый глобальный подсчёт через Distributed, и предагрегированные MV для часто используемых периодов (сутки, неделя). Это обеспечивает точность там, где она критична, и быстродействие там, где это возможно.

Что важно помнить при работе с подсчётами в кластере?

Определитесь с требованиями к точности, планируйте разбиение по времени, используйте MV и AggregatingMergeTree там, где это оправдано, и следите за задержками обновления агрегатов. Внимательность к архитектуре кластера и разумные параметры хранения позволят держать баланс между скоростью и точностью.

← Предыдущая статья

clickhouse оптимизация

Следующая статья →

clickhouse timestamp