romankovsv/claude-code-python-devops-mlops/clickhouse-io

CREATE TABLE events_analytics (
    date Date,
    event_id String,
    user_id String,
    event_type String,
    value Float64,
    created_at DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, user_id, event_type)
SETTINGS index_granularity = 8192;

CREATE TABLE user_events (
    event_id String,
    user_id String,
    event_type String,
    timestamp DateTime,
    properties String
) ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (user_id, event_id, timestamp);

CREATE TABLE stats_hourly (
    hour DateTime,
    dimension String,
    total_value AggregateFunction(sum, Float64),
    total_count AggregateFunction(count, UInt32),
    unique_users AggregateFunction(uniq, String)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, dimension);

from clickhouse_driver import Client
client = Client(host='localhost', port=9000, database='analytics')
# Batch insert (efficient)
data = [(row['date'], row['user_id'], row['value']) for row in rows]
client.execute(
    'INSERT INTO events_analytics (date, user_id, value) VALUES',
    data,
    types_check=True
)
# Query with parameters
result = client.execute(
    'SELECT date, uniq(user_id) AS dau FROM events_analytics '
    'WHERE date >= %(start)s AND date <= %(end)s GROUP BY date ORDER BY date',
    {'start': '2025-01-01', 'end': '2025-01-31'}
)

import clickhouse_connect
client = clickhouse_connect.get_client(host='localhost', port=8123)
# Query
result = client.query('SELECT * FROM events_analytics LIMIT 100')
df = result.result_set  # As list of tuples
# Insert from DataFrame
import pandas as pd
df = pd.DataFrame(data)
client.insert_df('events_analytics', df)

CREATE MATERIALIZED VIEW stats_hourly_mv
TO stats_hourly
AS SELECT
    toStartOfHour(timestamp) AS hour,
    dimension,
    sumState(value) AS total_value,
    countState() AS total_count,
    uniqState(user_id) AS unique_users
FROM raw_events
GROUP BY hour, dimension;

-- Use indexed columns first
SELECT * FROM events_analytics
WHERE date >= '2025-01-01' AND user_id = 'user-123'
ORDER BY date DESC LIMIT 100;
 
-- Use ClickHouse-specific functions
SELECT
    toStartOfDay(created_at) AS day,
    uniq(user_id) AS dau,
    quantile(0.95)(value) AS p95
FROM events_analytics
WHERE created_at >= today() - INTERVAL 7 DAY
GROUP BY day ORDER BY day;