Проблема: PostgreSQL не справляется с аналитикой

Классическая ситуация:

• У вас PostgreSQL с историей продаж за 2 года (500 млн строк)
• Запрос «ТОП товаров за месяц» выполняется 35 минут
• Утренние отчеты блокируют основную базу
• Нельзя делать ad-hoc анализ (слишком долго)

Почему так? PostgreSQL — это OLTP-база (для транзакций), не для аналитики.

OLTP vs OLAP

Параметр	OLTP (PostgreSQL)	OLAP (ClickHouse)
Назначение	Транзакции (INSERT, UPDATE)	Аналитика (SELECT, агрегаты)
Запросы	Точечные (по ID)	Сканирование больших таблиц
Хранение	Строчное (row-based)	Колоночное (columnar)
Скорость аналитики	Медленно	Быстро (10-1000×)
Компрессия	~2×	~10×

Решение: ClickHouse Data Warehouse

ClickHouse — OLAP-база от Яндекса:

• Колоночное хранение данных
• Распределенные запросы (горизонтальное масштабирование)
• Обработка петабайтов данных
• Открытый исходный код

Кейс: розничная сеть (50 магазинов)

Исходная ситуация:

• PostgreSQL с историей за 3 года: 500 млн строк в таблице чеков
• Запрос «ТОП товаров за месяц по всем магазинам» — 35 минут
• Нет возможности делать срезы и детализацию
• Утренние отчеты замедляют основную систему

Что сделали

1. Развернули ClickHouse кластер

-- Создаем таблицу для продаж
CREATE TABLE sales (
    date Date,
    datetime DateTime,
    store_id UInt16,
    product_id UInt32,
    product_name String,
    category String,
    quantity Float32,
    price Decimal(10, 2),
    revenue Decimal(12, 2)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, store_id, product_id);

Пояснения:

• ENGINE = MergeTree() — основной движок для аналитики
• PARTITION BY toYYYYMM(date) — партиционирование по месяцам (быстрое удаление старых данных)
• ORDER BY — сортировка (аналог индекса)

2. Настроили ETL из 1С каждые 15 минут

import clickhouse_connect
import requests
from datetime import datetime, timedelta

class OneCToClickHousePipeline:
    """ETL пайплайн: 1С → ClickHouse"""

    def __init__(self):
        self.ch_client = clickhouse_connect.get_client(
            host='clickhouse.local',
            port=8123,
            username='default',
            password=''
        )

    def extract_sales_from_1c(self, date_from: str, date_to: str):
        """Выгрузка продаж из 1С через OData"""
        url = "http://1c.company.local/odata/standard.odata/Document_РеализацияТоваровИУслуг"
        params = {
            "$filter": f"Date ge {date_from} and Date le {date_to}",
            "$expand": "Товары",
            "$select": "Date,Магазин,Товары"
        }
        response = requests.get(url, params=params, auth=('user', 'pass'))
        return response.json()['value']

    def transform(self, raw_data):
        """Трансформация данных"""
        rows = []
        for doc in raw_data:
            for item in doc['Товары']:
                rows.append({
                    'date': doc['Date'][:10],
                    'datetime': doc['Date'],
                    'store_id': doc['Магазин']['Code'],
                    'product_id': item['Номенклатура']['Code'],
                    'product_name': item['Номенклатура']['Description'],
                    'category': item['Номенклатура']['Группа'],
                    'quantity': item['Количество'],
                    'price': item['Цена'],
                    'revenue': item['Сумма']
                })
        return rows

    def load_to_clickhouse(self, rows):
        """Загрузка в ClickHouse"""
        self.ch_client.insert(
            'sales',
            rows,
            column_names=['date', 'datetime', 'store_id', 'product_id',
                         'product_name', 'category', 'quantity', 'price', 'revenue']
        )

    def run(self):
        # Выгружаем только новые данные за последние 15 минут
        date_from = (datetime.now() - timedelta(minutes=15)).isoformat()
        date_to = datetime.now().isoformat()

        raw_data = self.extract_sales_from_1c(date_from, date_to)
        transformed = self.transform(raw_data)
        self.load_to_clickhouse(transformed)

        print(f"Loaded {len(transformed)} rows to ClickHouse")

Автоматизация через Airflow:

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime

dag = DAG(
    'sync_1c_to_clickhouse',
    schedule_interval='*/15 * * * *',  # каждые 15 минут
    start_date=datetime(2025, 1, 1),
    catchup=False
)

sync_task = PythonOperator(
    task_id='sync_sales',
    python_callable=OneCToClickHousePipeline().run,
    dag=dag
)

3. Создали витрины данных

-- Материализованное представление для дневной аналитики
CREATE MATERIALIZED VIEW sales_daily_mv
ENGINE = SummingMergeTree()
ORDER BY (date, store_id, product_id)
AS SELECT
    date,
    store_id,
    product_id,
    product_name,
    category,
    sum(quantity) as total_quantity,
    sum(revenue) as total_revenue
FROM sales
GROUP BY date, store_id, product_id, product_name, category;

Почему это быстро?

• Данные предагрегированы (заранее посчитаны суммы)
• При INSERT автоматически обновляется
• Запросы к MV в 10-100× быстрее

Результаты

До: PostgreSQL

-- Запрос: ТОП-20 товаров за месяц
SELECT
    product_name,
    SUM(revenue) as total_revenue
FROM sales
WHERE date >= '2025-01-01' AND date < '2025-02-01'
GROUP BY product_name
ORDER BY total_revenue DESC
LIMIT 20;

-- Время выполнения: 35 минут

После: ClickHouse

-- Тот же запрос
SELECT
    product_name,
    sum(total_revenue) as total_revenue
FROM sales_daily_mv
WHERE date >= '2025-01-01' AND date < '2025-02-01'
GROUP BY product_name
ORDER BY total_revenue DESC
LIMIT 20;

-- Время выполнения: 0.3 секунды

Ускорение: 35 минут → 0.3 сек = 7000× быстрее

Метрика	До (PostgreSQL)	После (ClickHouse)
Запрос «ТОП товаров»	35 минут	0.3 сек
Размер БД	180 ГБ	18 ГБ (компрессия 10×)
Возможность ad-hoc анализа	Нет	Да
Нагрузка на боевую БД	Высокая	Нулевая

Продвинутая аналитика

1. Когортный анализ

-- Retention клиентов по когортам
SELECT
    toStartOfMonth(first_purchase_date) as cohort_month,
    dateDiff('month', first_purchase_date, date) as months_since_first,
    count(DISTINCT customer_id) as customers
FROM (
    SELECT
        customer_id,
        date,
        min(date) OVER (PARTITION BY customer_id) as first_purchase_date
    FROM sales
)
WHERE cohort_month >= '2024-01-01'
GROUP BY cohort_month, months_since_first
ORDER BY cohort_month, months_since_first;

-- Выполняется за 2 секунды на 500 млн строк

2. ABC-анализ товаров

-- Классификация товаров по выручке (Парето)
WITH product_revenue AS (
    SELECT
        product_id,
        product_name,
        sum(revenue) as total_revenue
    FROM sales
    WHERE date >= '2024-01-01'
    GROUP BY product_id, product_name
),
cumulative AS (
    SELECT
        product_id,
        product_name,
        total_revenue,
        sum(total_revenue) OVER (ORDER BY total_revenue DESC) as cumulative_revenue,
        sum(total_revenue) OVER () as total
    FROM product_revenue
)
SELECT
    product_id,
    product_name,
    total_revenue,
    round(cumulative_revenue / total * 100, 2) as cumulative_percent,
    CASE
        WHEN cumulative_revenue / total <= 0.8 THEN 'A'
        WHEN cumulative_revenue / total <= 0.95 THEN 'B'
        ELSE 'C'
    END as abc_class
FROM cumulative
ORDER BY total_revenue DESC;

3. Временные ряды и тренды

-- Скользящее среднее продаж за 7 дней
SELECT
    date,
    sum(revenue) as daily_revenue,
    avg(sum(revenue)) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7d
FROM sales
WHERE date >= '2024-01-01'
GROUP BY date
ORDER BY date;

Оптимизация запросов

1. Правильный ORDER BY (сортировка)

-- Плохо: сортировка не по ORDER BY таблицы
SELECT * FROM sales WHERE product_id = 12345;
-- Сканирует всю таблицу

-- Хорошо: фильтр по колонке из ORDER BY
SELECT * FROM sales WHERE date = '2025-01-15' AND store_id = 3;
-- Читает только нужную партицию и использует индекс

Правило: Колонки в WHERE должны совпадать с ORDER BY таблицы.

2. Материализованные представления (MV)

-- Создаем MV для частого запроса
CREATE MATERIALIZED VIEW sales_by_category_hourly_mv
ENGINE = SummingMergeTree()
ORDER BY (toStartOfHour(datetime), category)
AS SELECT
    toStartOfHour(datetime) as hour,
    category,
    sum(revenue) as revenue,
    count() as orders_count
FROM sales
GROUP BY hour, category;

-- Теперь запрос по категориям моментальный:
SELECT * FROM sales_by_category_hourly_mv WHERE hour >= now() - INTERVAL 24 HOUR;

3. PREWHERE вместо WHERE

-- Плохо: WHERE читает все колонки, потом фильтрует
SELECT product_name, revenue
FROM sales
WHERE date = '2025-01-15';

-- Хорошо: PREWHERE сначала фильтрует, потом читает колонки
SELECT product_name, revenue
FROM sales
PREWHERE date = '2025-01-15';

-- Ускорение: 2-5× на широких таблицах

Кейс 2: Телеком (200 млн CDR в день)

Исходная ситуация:

• Oracle DWH не справлялся: запросы выполнялись часами
• Нет real-time мониторинга сети
• Дорогие лицензии Oracle ($500k/год)

Решение

1. Миграция на ClickHouse

   • Streaming ETL через Kafka
   • Партиционирование по часам
   • Materialized Views для метрик

2. Результаты:

   • Запросы в 300× быстрее
   • Экономия $500k/год на лицензиях Oracle
   • Real-time дашборды для Service Desk
   • Детект аномалий за минуты

Архитектура ClickHouse DWH

┌────────────────────────┐
│  Источники данных      │
│  - 1С (OData)          │
│  - PostgreSQL (CDC)    │
│  - API (REST)          │
│  - CSV/Excel           │
└──────────┬─────────────┘
           │
┌──────────▼─────────────┐
│  ETL (Airflow/dbt)     │
│  - Extraction          │
│  - Transformation      │
│  - Validation          │
└──────────┬─────────────┘
           │
┌──────────▼─────────────┐
│  ClickHouse Cluster    │
│  ┌─────────────────┐   │
│  │ Shard 1         │   │
│  │ - Replica 1     │   │
│  │ - Replica 2     │   │
│  └─────────────────┘   │
│  ┌─────────────────┐   │
│  │ Shard 2         │   │
│  │ - Replica 1     │   │
│  │ - Replica 2     │   │
│  └─────────────────┘   │
└──────────┬─────────────┘
           │
┌──────────▼─────────────┐
│  Потребители           │
│  - Metabase / DataLens │
│  - Grafana             │
│  - Python/R notebooks  │
│  - API для приложений  │
└────────────────────────┘

Стоимость содержания

Сравнение с коммерческими DWH

Решение	Стоимость (500 ГБ данных)	Производительность
ClickHouse (3 сервера)	30 000 ₽/мес	⭐⭐⭐⭐⭐
Oracle Exadata	~$500k/год	⭐⭐⭐⭐
Snowflake	~$200k/год	⭐⭐⭐⭐
PostgreSQL	Бесплатно	⭐⭐ (не для OLAP)

ROI: Окупается за 6-12 месяцев vs коммерческие решения.

Когда нужен ClickHouse?

✅ Хорошо подходит для:

• Аналитика больших объемов (> 100 млн строк)
• Временные ряды (логи, метрики, IoT)
• Real-time dashboards
• Хранилище данных (Data Warehouse)

❌ Не подходит для:

• OLTP (транзакции, UPDATE/DELETE)
• Маленькие объемы (< 1 млн строк) — избыточно
• Сложные JOIN’ы больших таблиц — медленно

Частые вопросы

Q: Можно ли использовать ClickHouse вместо PostgreSQL?

A: Нет. ClickHouse — только для аналитики (SELECT). Для транзакций (INSERT/UPDATE/DELETE) нужна OLTP-база (PostgreSQL, MySQL).

---

Q: Сколько серверов нужно?

A: Зависит от объема:

• До 500 ГБ: 1 сервер (16 CPU, 64 GB RAM)
• 500 ГБ - 5 ТБ: кластер 3-5 серверов
• 5 ТБ+: 10+ серверов

---

Q: Сложно ли мигрировать с PostgreSQL?

A: Средняя сложность. Основные шаги:

1. Настроить ClickHouse
2. Создать схему данных
3. Написать ETL (Airflow/dbt)
4. Загрузить историю (bulk import)
5. Настроить инкрементальную синхронизацию

Срок: 4-8 недель.

Заключение

ClickHouse — это:

• ✅ Быстро: запросы в 10-1000× быстрее традиционных БД
• ✅ Экономично: в 5-10× дешевле коммерческих DWH
• ✅ Масштабируемо: от гигабайтов до петабайтов
• ✅ Open-source: бесплатная лицензия

Подходит для компаний с объемом данных от 100 млн строк.

---

Хотите построить DWH на ClickHouse?

Мы проектируем и разворачиваем аналитические хранилища на ClickHouse. Настраиваем ETL, создаем витрины данных, интегрируем с BI-инструментами.

📞 +7 (924) 547-36-78 📧 info@bi-ai.ru 💬 Telegram: @bi_ai_team