ClickHouse

Что такое ClickHouse

ClickHouse -- колоночная СУБД для онлайн-аналитической обработки (OLAP). Разработана в Яндексе для аналитики Яндекс.Метрики: миллиарды строк, запросы за секунды.

Колоночное vs Строчное хранение

Строчное (PostgreSQL):         Колоночное (ClickHouse):

Row 1: [id|name|amount|date]   Column id:     [1,2,3,4,5...]
Row 2: [id|name|amount|date]   Column name:   [A,B,C,D,E...]
Row 3: [id|name|amount|date]   Column amount: [10,20,30,40,50...]
Row 4: [id|name|amount|date]   Column date:   [d1,d2,d3,d4,d5...]

Преимущества колоночного хранения для аналитики:

• Читает только нужные столбцы (из 100 столбцов используем 3)
• Отличная компрессия (одинаковые типы данных рядом)
• Векторные операции (SIMD) на столбцах
• Агрегации на порядки быстрее

MergeTree Engine

MergeTree -- основной движок хранения ClickHouse:

INSERT batch
    │
    ▼
┌──────────┐   Background merge
│  Part 1  │──────────────────┐
│ (sorted) │                  │
└──────────┘                  ▼
                        ┌──────────┐
┌──────────┐            │ Merged   │
│  Part 2  │──────────> │  Part    │
│ (sorted) │            │ (sorted) │
└──────────┘            └──────────┘

• Данные вставляются партиями (parts), каждая часть отсортирована
• Фоновый процесс сливает (merge) части для оптимизации
• Индексы: primary key (sparse), skip indexes

<?php

declare(strict_types=1);

/**
 * ClickHouse table design with MergeTree engine
 */
final class ClickHouseSchema
{
    public function __construct(
        private readonly \PDO $db, // ClickHouse via HTTP PDO or clickhouse-client
    ) {}

    public function createAnalyticsTable(): void
    {
        $this->db->exec(<<<SQL
            CREATE TABLE IF NOT EXISTS events (
                event_date Date,
                event_time DateTime,
                user_id UInt64,
                event_type LowCardinality(String),
                page_url String,
                referrer String,
                country LowCardinality(String),
                device LowCardinality(String),
                duration_ms UInt32,
                revenue Decimal64(2)
            )
            ENGINE = MergeTree()
            PARTITION BY toYYYYMM(event_date)
            ORDER BY (event_type, user_id, event_time)
            TTL event_date + INTERVAL 1 YEAR
            SETTINGS index_granularity = 8192
        SQL);
    }

    /**
     * ReplacingMergeTree: deduplication by primary key
     */
    public function createDeduplicatedTable(): void
    {
        $this->db->exec(<<<SQL
            CREATE TABLE IF NOT EXISTS user_profiles (
                user_id UInt64,
                name String,
                email String,
                updated_at DateTime
            )
            ENGINE = ReplacingMergeTree(updated_at)
            ORDER BY user_id
        SQL);
    }

    /**
     * AggregatingMergeTree: pre-aggregated data
     */
    public function createAggregatedTable(): void
    {
        $this->db->exec(<<<SQL
            CREATE TABLE IF NOT EXISTS daily_stats (
                event_date Date,
                event_type LowCardinality(String),
                country LowCardinality(String),
                views AggregateFunction(count, UInt64),
                unique_users AggregateFunction(uniq, UInt64),
                total_revenue AggregateFunction(sum, Decimal64(2))
            )
            ENGINE = AggregatingMergeTree()
            PARTITION BY toYYYYMM(event_date)
            ORDER BY (event_date, event_type, country)
        SQL);
    }
}

Materialized Views

Materialized views в ClickHouse автоматически обновляются при вставке данных:

<?php

declare(strict_types=1);

/**
 * Materialized views: automatic real-time aggregation
 */
final class MaterializedViewSetup
{
    public function __construct(
        private readonly \PDO $db,
    ) {}

    /**
     * Create materialized view that aggregates on INSERT
     */
    public function createRealTimeAggregation(): void
    {
        // Target table for aggregated data
        $this->db->exec(<<<SQL
            CREATE TABLE IF NOT EXISTS hourly_metrics (
                hour DateTime,
                event_type LowCardinality(String),
                event_count UInt64,
                unique_users UInt64,
                total_revenue Decimal64(2)
            )
            ENGINE = SummingMergeTree()
            PARTITION BY toYYYYMM(hour)
            ORDER BY (hour, event_type)
        SQL);

        // Materialized view: triggers on INSERT to events table
        $this->db->exec(<<<SQL
            CREATE MATERIALIZED VIEW IF NOT EXISTS mv_hourly_metrics
            TO hourly_metrics
            AS SELECT
                toStartOfHour(event_time) as hour,
                event_type,
                count() as event_count,
                uniq(user_id) as unique_users,
                sum(revenue) as total_revenue
            FROM events
            GROUP BY hour, event_type
        SQL);
    }
}

Важно: materialized views в ClickHouse работают на INSERT, а не хранят полный агрегат. При merge частей результаты объединяются через MergeTree.

Запросы из PHP

<?php

declare(strict_types=1);

/**
 * ClickHouse analytics queries from PHP
 */
final class ClickHouseAnalytics
{
    public function __construct(
        private readonly ClickHouseClient $client,
    ) {}

    /**
     * Funnel analysis: conversion through steps
     */
    public function funnelAnalysis(\DateTimeImmutable $from, \DateTimeImmutable $to): array
    {
        return $this->client->query(<<<SQL
            SELECT
                windowFunnel(86400)(
                    event_time,
                    event_type = 'page_view',
                    event_type = 'add_to_cart',
                    event_type = 'checkout',
                    event_type = 'purchase'
                ) as funnel_step,
                count() as users
            FROM events
            WHERE event_date BETWEEN :from AND :to
            GROUP BY funnel_step
            ORDER BY funnel_step
        SQL, [
            'from' => $from->format('Y-m-d'),
            'to' => $to->format('Y-m-d'),
        ]);
    }

    /**
     * Retention cohort analysis
     */
    public function retentionAnalysis(int $months = 6): array
    {
        return $this->client->query(<<<SQL
            WITH first_events AS (
                SELECT
                    user_id,
                    toStartOfMonth(min(event_date)) as cohort_month
                FROM events
                GROUP BY user_id
            )
            SELECT
                f.cohort_month,
                dateDiff('month', f.cohort_month, toStartOfMonth(e.event_date)) as month_number,
                uniq(e.user_id) as active_users
            FROM events e
            JOIN first_events f ON e.user_id = f.user_id
            WHERE f.cohort_month >= today() - INTERVAL :months MONTH
            GROUP BY f.cohort_month, month_number
            ORDER BY f.cohort_month, month_number
        SQL, ['months' => $months]);
    }

    /**
     * Insert batch of events (optimal way)
     */
    public function insertEvents(array $events): void
    {
        // ClickHouse: always insert in batches, never single rows
        $values = [];
        foreach ($events as $event) {
            $values[] = sprintf(
                "('%s', '%s', %d, '%s', '%s', '%s', '%s', '%s', %d, %s)",
                $event['event_date'],
                $event['event_time'],
                $event['user_id'],
                $event['event_type'],
                $event['page_url'],
                $event['referrer'],
                $event['country'],
                $event['device'],
                $event['duration_ms'],
                $event['revenue']
            );
        }

        $this->client->query(
            "INSERT INTO events VALUES " . implode(',', $values)
        );
    }
}

/**
 * Simple ClickHouse HTTP client
 */
final class ClickHouseClient
{
    public function __construct(
        private readonly string $host,
        private readonly int $port = 8123,
        private readonly string $database = 'default',
    ) {}

    public function query(string $sql, array $params = []): array
    {
        foreach ($params as $key => $value) {
            $sql = str_replace(":{$key}", $this->escape($value), $sql);
        }

        $ch = curl_init();
        curl_setopt_array($ch, [
            CURLOPT_URL => "http://{$this->host}:{$this->port}/?database={$this->database}",
            CURLOPT_POST => true,
            CURLOPT_POSTFIELDS => $sql . ' FORMAT JSONEachRow',
            CURLOPT_RETURNTRANSFER => true,
        ]);

        $result = curl_exec($ch);
        curl_close($ch);

        $rows = [];
        foreach (explode("\n", trim($result)) as $line) {
            if (!empty($line)) {
                $rows[] = json_decode($line, true);
            }
        }

        return $rows;
    }

    private function escape(mixed $value): string
    {
        if (is_int($value)) return (string) $value;
        if (is_float($value)) return (string) $value;
        return "'" . addslashes((string) $value) . "'";
    }
}

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

Итоги

• Колоночное хранение даёт на порядки более быструю аналитику
• MergeTree -- основной движок: сортированные части, фоновый merge
• Materialized views автоматически агрегируют данные при INSERT
• Вставка только батчами (тысячи строк за раз)
• ClickHouse не заменяет PostgreSQL, а дополняет его для OLAP-задач