Какие БД подходят для хранения аналитики?

# Базы данных для хранения аналитики

Лучшие БД для аналитики

Выбор БД для аналитики зависит от объёма данных, скорости запросов и типа анализа. Вот основные варианты:

1. Аналитические СУБД (OLAP)

ClickHouse — лучше всего для времени реального

ClickHouse — это распределённая СУБД, специализированная на аналитике и больших объёмах данных:

from clickhouse_driver import Client

# Подключение
client = Client('localhost')

# Вставка данных
client.execute(
    'INSERT INTO events (timestamp, user_id, event_type, value) VALUES',
    [
        ('2024-01-01 10:00:00', 1, 'click', 100),
        ('2024-01-01 10:01:00', 2, 'view', 200),
    ]
)

# Аналитический запрос
result = client.execute(
    '''SELECT 
        toDate(timestamp) as date,
        count() as events,
        sum(value) as total_value
    FROM events
    GROUP BY date
    ORDER BY date DESC
    '''
)

for row in result:
    print(f'Дата: {row[0]}, События: {row[1]}, Сумма: {row[2]}')

Преимущества:

• Экстремально быстрые аналитические запросы
• Сжатие данных (10-40x раз)
• Поддержка распределённых систем
• Встроенная репликация

Недостатки:

• Плохо для транзакционных операций (ACID)
• Сложнее в администрировании

Apache Druid — для real-time аналитики

Apache Druid предназначена для real-time OLAP:

import requests
import json

# Загрузка данных
druid_url = 'http://localhost:8081'

data = {
    'queryType': 'timeseries',
    'dataSource': 'events',
    'granularity': 'day',
    'intervals': ['2024-01-01/2024-01-31'],
    'metrics': ['count', 'sum_value'],
    'filter': {
        'type': 'selector',
        'dimension': 'country',
        'value': 'USA'
    }
}

response = requests.post(
    f'{druid_url}/druid/v2/',
    data=json.dumps(data),
    headers={'Content-Type': 'application/json'}
)

results = response.json()
for result in results:
    print(f'Время: {result["timestamp"]}, Метрики: {result["result"]}')

Преимущества:

• Супер-быстрые real-time запросы
• Встроенные метрики и агрегации
• Масштабируемость

Недостатки:

• Нужно учиться
• Требует Kafka/другого message broker для real-time

2. Хранилища данных (Data Warehouses)

PostgreSQL с расширением Citus

PostgreSQL с расширением Citus может работать как аналитическое хранилище для средних объёмов:

import psycopg2
from datetime import datetime, timedelta

# Подключение
conn = psycopg2.connect(
    host='localhost',
    database='analytics',
    user='postgres'
)
cursor = conn.cursor()

# Создание партиционированной таблицы
cursor.execute('''
    CREATE TABLE IF NOT EXISTS events (
        id SERIAL,
        timestamp TIMESTAMP NOT NULL,
        user_id INT NOT NULL,
        event_type VARCHAR(50),
        value DECIMAL(10,2)
    ) PARTITION BY RANGE (timestamp);
    
    -- Партиции по месяцам
    CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
''')

# Индексы для быстрого поиска
cursor.execute('''
    CREATE INDEX idx_events_user_timestamp 
    ON events (user_id, timestamp DESC)
    WHERE event_type = 'purchase';
''')

# Аналитический запрос
cursor.execute('''
    SELECT 
        DATE_TRUNC('day', timestamp) as date,
        COUNT(*) as events,
        SUM(value) as revenue,
        AVG(value) as avg_value,
        MAX(value) as max_value
    FROM events
    WHERE timestamp >= NOW() - INTERVAL '30 days'
    GROUP BY DATE_TRUNC('day', timestamp)
    ORDER BY date DESC
''')

for row in cursor.fetchall():
    print(f'Дата: {row[0]}, События: {row[1]}, Выручка: {row[2]}')

conn.close()

Преимущества:

• Мощные запросы
• ACID транзакции
• Встроенная оптимизация

Недостатки:

• Медленнее, чем специализированные OLAP БД
• Требует оптимизации индексов

BigQuery (Google)

Google BigQuery — это облачное хранилище данных:

from google.cloud import bigquery
from google.cloud.bigquery import LoadJobConfig

# Инициализация
client = bigquery.Client(project='your-project')

# SQL запрос
query = '''
    SELECT 
        DATE(timestamp) as date,
        COUNT(*) as events,
        SUM(revenue) as total_revenue
    FROM `your-project.analytics.events`
    WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    GROUP BY DATE(timestamp)
    ORDER BY date DESC
'''

query_job = client.query(query)
results = query_job.result()

for row in results:
    print(f'Дата: {row.date}, События: {row.events}, Выручка: {row.total_revenue}')

Преимущества:

• Облачное решение (нет администрирования)
• Колоночное хранилище (очень быстро)
• Масштабируемость (петабайты данных)
• Встроенная машинное обучение (BigQuery ML)

Недостатки:

• Стоимость (pay-as-you-go)
• Lock-in в экосистему Google

3. Time Series БД

InfluxDB — для временных рядов

InfluxDB специализируется на времени (метрики, логи, события):

from influxdb_client import InfluxDBClient, Point
from influxdb_client.client.write_api import SYNCHRONOUS
from datetime import datetime

# Подключение
client = InfluxDBClient(
    url='http://localhost:8086',
    token='your-token',
    org='your-org'
)

write_api = client.write_api(write_type=SYNCHRONOUS)
query_api = client.query_api()

# Запись метрик
point = Point('user_events') \
    .tag('user_id', '123') \
    .tag('event_type', 'purchase') \
    .field('value', 99.99) \
    .time(datetime.utcnow())

write_api.write(bucket='analytics', record=point)

# Запрос за период
query = '''
    from(bucket: "analytics")
    |> range(start: -30d)
    |> filter(fn: (r) => r._measurement == "user_events")
    |> aggregateWindow(every: 1d, fn: sum)
'''

result = query_api.query(query)
for table in result:
    for record in table.records:
        print(f'Время: {record.get_time()}, Значение: {record.get_value()}')

client.close()

Преимущества:

• Оптимизирована для временных рядов
• Автоматическое удаление старых данных (retention)
• Встроенное сжатие

Недостатки:

• Не подходит для сложных связей

4. MongoDB + Aggregation Framework

MongoDB для аналитики в масштабе средних данных:

from pymongo import MongoClient
from datetime import datetime, timedelta

client = MongoClient('mongodb://localhost:27017')
db = client['analytics']
events = db['events']

# Aggregation Pipeline для аналитики
pipeline = [
    # Фильтр по дате
    {
        '$match': {
            'timestamp': {
                '$gte': datetime.now() - timedelta(days=30)
            }
        }
    },
    # Группировка по дню
    {
        '$group': {
            '_id': {'$dateToString': {'format': '%Y-%m-%d', 'date': '$timestamp'}},
            'count': {'$sum': 1},
            'revenue': {'$sum': '$value'},
            'avg_value': {'$avg': '$value'}
        }
    },
    # Сортировка
    {
        '$sort': {'_id': -1}
    }
]

results = list(events.aggregate(pipeline))
for result in results:
    print(f"Дата: {result['_id']}, События: {result['count']}, Выручка: {result['revenue']}")

Преимущества:

• Гибкость структуры
• Мощные aggregation pipelines
• Хороша для неструктурированных данных

Недостатки:

• Медленнее специализированных OLAP БД

Сравнение для разных сценариев

Real-time аналитика (каждая минута)

• ClickHouse — лучший выбор
• Apache Druid — альтернатива

Daily отчёты (обновление раз в день)

• PostgreSQL — достаточно
• ClickHouse — если > 10GB данных

Облачное решение (нет ops)

• BigQuery (Google)
• Snowflake
• AWS Redshift

Временные ряды (метрики, логи)

• InfluxDB — лучше всего
• TimescaleDB (PostgreSQL + расширение)

Неструктурированные данные

• MongoDB с aggregation
• Elasticsearch (для поиска + аналитики)

Рекомендации

✅ Стартап / маленькая компания:

• PostgreSQL с партиционированием
• Простой и достаточно быстрый

✅ Растущая компания (> 100GB):

• ClickHouse + Kafka для real-time
• Экономно по ресурсам

✅ Большая компания (облако):

• BigQuery или Snowflake
• Нет ops, масштабируемость, инструменты

Архитектура: OLTP vs OLAP

ОLTP (PostgreSQL, MySQL):  Много маленьких транзакций
  ↓
ETL процесс (Python, dbt)  Ночная обработка
  ↓
OLAP (ClickHouse, BigQuery): Аналитика и отчёты

Для аналитики выбирайте специализированные OLAP БД, которые отдельны от продакшена. Это обезопасит production и даст быстрые запросы.