ClickHouse для аналитики веб-приложения
ClickHouse — колоночная СУБД для аналитических запросов (OLAP). Агрегирует миллиарды строк за секунды там, где PostgreSQL тратит минуты. Не замена транзакционной базе — дополнение: PostgreSQL для операционных данных, ClickHouse для аналитики.
Когда нужен ClickHouse
Типичные запросы, которые PostgreSQL переваривает плохо:
- «Дневная аудитория за последние 90 дней по источникам трафика»
- «Воронка конверсии по шагам за квартал»
- «Топ-1000 товаров по выручке за год с разбивкой по регионам»
- «Когортный анализ: retention пользователей по месяцам регистрации»
Если таблица событий больше 100 миллионов строк — ClickHouse даст ускорение в 10–100x.
Схема таблиц
ClickHouse использует специализированные движки. MergeTree — основной:
-- Таблица событий (page views, clicks, purchases)
CREATE TABLE events (
event_date Date,
event_time DateTime,
event_type LowCardinality(String), -- enum-like: 'pageview', 'click', 'purchase'
user_id UInt64,
session_id String,
tenant_id UInt32,
page_url String,
referrer String,
country LowCardinality(String),
device_type LowCardinality(String), -- 'desktop', 'mobile', 'tablet'
properties String -- JSON для произвольных атрибутов
) ENGINE = MergeTree()
ORDER BY (tenant_id, event_date, event_type, user_id)
PARTITION BY toYYYYMM(event_date);
-- Индексы (min/max для быстрого пропуска блоков)
ALTER TABLE events ADD INDEX idx_session session_id TYPE bloom_filter(0.01) GRANULARITY 4;
ORDER BY — ключ сортировки, по которому ClickHouse хранит данные. Запросы с фильтрами по tenant_id и event_date используют его для pruning.
LowCardinality — оптимизация для колонок с малым количеством уникальных значений (~10k). Хранит как dictionary encoding.
Вставка данных
Никогда не вставлять по одной строке — ClickHouse оптимизирован под batch inserts:
// Node.js + @clickhouse/client
import { createClient } from '@clickhouse/client';
const client = createClient({
host: process.env.CLICKHOUSE_HOST,
username: process.env.CLICKHOUSE_USER,
password: process.env.CLICKHOUSE_PASSWORD,
database: 'analytics',
});
// Буфер событий — сбрасывать каждые N секунд или M событий
class EventBuffer {
private buffer: EventRow[] = [];
private flushTimer: NodeJS.Timeout;
async push(event: EventRow) {
this.buffer.push(event);
if (this.buffer.length >= 1000) await this.flush();
}
async flush() {
if (!this.buffer.length) return;
const rows = [...this.buffer];
this.buffer = [];
await client.insert({
table: 'events',
values: rows,
format: 'JSONEachRow',
});
}
}
// Или через Kafka → ClickHouse Kafka Engine для высоких нагрузок
Аналитические запросы
-- DAU/MAU за последние 30 дней
SELECT
event_date,
uniqExact(user_id) AS dau
FROM events
WHERE tenant_id = 42
AND event_date >= today() - 30
AND event_type = 'pageview'
GROUP BY event_date
ORDER BY event_date;
-- Воронка конверсии (Funnel Analysis)
SELECT
countIf(event_type = 'product_view') AS step1_views,
countIf(event_type = 'add_to_cart') AS step2_cart,
countIf(event_type = 'checkout_start') AS step3_checkout,
countIf(event_type = 'purchase') AS step4_purchase,
round(100.0 * countIf(event_type = 'purchase') /
countIf(event_type = 'product_view'), 2) AS conversion_pct
FROM events
WHERE tenant_id = 42
AND event_date BETWEEN '2025-01-01' AND '2025-01-31';
-- Retention (когортный анализ)
SELECT
toStartOfMonth(first_event_date) AS cohort_month,
dateDiff('month', first_event_date, event_date) AS months_since_signup,
uniqExact(user_id) AS retained_users
FROM events e
JOIN (
SELECT user_id, min(event_date) AS first_event_date
FROM events WHERE event_type = 'signup'
GROUP BY user_id
) first ON e.user_id = first.user_id
WHERE event_type = 'active_session'
GROUP BY cohort_month, months_since_signup
ORDER BY cohort_month, months_since_signup;
uniqExact — точный подсчёт уникальных (дороже). uniq — приближённый (~2% погрешность), на порядок быстрее для больших данных.
Materialized Views
Для часто запрашиваемых агрегатов — материализованные представления, которые обновляются при вставке:
-- Предагрегированные дневные метрики
CREATE MATERIALIZED VIEW daily_metrics
ENGINE = SummingMergeTree()
ORDER BY (tenant_id, event_date, country, device_type)
AS SELECT
tenant_id,
event_date,
country,
device_type,
count() AS events_count,
uniqState(user_id) AS unique_users_state,
uniqState(session_id) AS unique_sessions_state
FROM events
GROUP BY tenant_id, event_date, country, device_type;
-- Запрос к материализованному представлению
SELECT
event_date,
sum(events_count) AS total_events,
uniqMerge(unique_users_state) AS unique_users
FROM daily_metrics
WHERE tenant_id = 42
AND event_date >= today() - 7
GROUP BY event_date;
Интеграция с Laravel
// config/database.php
'clickhouse' => [
'driver' => 'clickhouse',
'host' => env('CLICKHOUSE_HOST', 'localhost'),
'port' => env('CLICKHOUSE_PORT', 8123),
'database' => env('CLICKHOUSE_DATABASE', 'analytics'),
],
// Запрос через пакет sanchov/laravel-clickhouse
$results = DB::connection('clickhouse')
->select("
SELECT event_date, uniqExact(user_id) as dau
FROM events
WHERE tenant_id = ?
AND event_date >= today() - 30
GROUP BY event_date
ORDER BY event_date
", [$tenantId]);
Репликация и архивирование
-- ReplicatedMergeTree для HA
CREATE TABLE events ON CLUSTER analytics_cluster (
...
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
ORDER BY (tenant_id, event_date, event_type, user_id)
PARTITION BY toYYYYMM(event_date)
TTL event_date + INTERVAL 2 YEAR DELETE; -- автоматическое удаление старых данных
TTL — автоматическая очистка данных старше 2 лет. Для compliance можно настроить перемещение в холодное хранилище вместо удаления.
Сроки
ClickHouse настройка, схема events, Materialized Views для дневных метрик, интеграция с Laravel, базовые дашборды (DAU, воронка): 1–2 недели. Когортный анализ, retention charts, репликация, Kafka ingestion pipeline: 2–4 недели.