ClickHouse 分析模式

概述

ClickHouse 是面向列式存储的数据库管理系统（DBMS），专为在线分析处理（OLAP）设计。本文档涵盖 ClickHouse 特有的高性能分析和数据工程模式，包括表设计、查询优化、数据插入、物化视图（Materialized Views）和数据管道（Data Pipeline）最佳实践。

表设计模式

MergeTree 引擎（最常用）

CREATE TABLE markets_analytics (
    date Date,
    market_id String,
    market_name String,
    volume UInt64,
    trades UInt32,
    unique_traders UInt32,
    avg_trade_size Float64,
    created_at DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)       -- 按月分区
ORDER BY (date, market_id)         -- 排序键
SETTINGS index_granularity = 8192; -- 索引粒度

ReplacingMergeTree（去重）

-- 用于可能有重复数据的场景（如来自多个数据源）
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)
PRIMARY KEY (user_id, event_id);

AggregatingMergeTree（预聚合）

-- 用于维护聚合指标
CREATE TABLE market_stats_hourly (
    hour DateTime,
    market_id String,
    total_volume AggregateFunction(sum, UInt64),
    total_trades AggregateFunction(count, UInt32),
    unique_users AggregateFunction(uniq, String)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, market_id);

-- 查询聚合数据
SELECT
    hour,
    market_id,
    sumMerge(total_volume) AS volume,
    countMerge(total_trades) AS trades,
    uniqMerge(unique_users) AS users
FROM market_stats_hourly
WHERE hour >= toStartOfHour(now() - INTERVAL 24 HOUR)
GROUP BY hour, market_id
ORDER BY hour DESC;

查询优化模式

高效过滤

-- 正确：先使用索引列过滤
SELECT *
FROM markets_analytics
WHERE date >= '2025-01-01'
  AND market_id = 'market-123'
  AND volume > 1000
ORDER BY date DESC
LIMIT 100;

-- 错误：先对非索引列过滤
SELECT *
FROM markets_analytics
WHERE volume > 1000
  AND market_name LIKE '%election%'
  AND date >= '2025-01-01';

聚合查询

-- 正确：使用 ClickHouse 特有的聚合函数
SELECT
    toStartOfDay(created_at) AS day,
    market_id,
    sum(volume) AS total_volume,
    count() AS total_trades,
    uniq(trader_id) AS unique_traders,  -- uniq 是 ClickHouse 的近似去重函数
    avg(trade_size) AS avg_size
FROM trades
WHERE created_at >= today() - INTERVAL 7 DAY
GROUP BY day, market_id
ORDER BY day DESC, total_volume DESC;

-- 正确：使用 quantile 计算百分位数（比 percentile 更高效）
SELECT
    quantile(0.50)(trade_size) AS median,   -- 中位数
    quantile(0.95)(trade_size) AS p95,      -- 第 95 百分位
    quantile(0.99)(trade_size) AS p99       -- 第 99 百分位
FROM trades
WHERE created_at >= now() - INTERVAL 1 HOUR;

窗口函数（Window Functions）

-- 计算累计总量
SELECT
    date,
    market_id,
    volume,
    sum(volume) OVER (
        PARTITION BY market_id
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_volume  -- 累计交易量
FROM markets_analytics
WHERE date >= today() - INTERVAL 30 DAY
ORDER BY market_id, date;

数据插入模式

批量插入（推荐）

import { ClickHouse } from 'clickhouse'

const clickhouse = new ClickHouse({
  url: process.env.CLICKHOUSE_URL,
  port: 8123,
  basicAuth: {
    username: process.env.CLICKHOUSE_USER,
    password: process.env.CLICKHOUSE_PASSWORD
  }
})

// 正确：批量插入（高效）
async function bulkInsertTrades(trades: Trade[]) {
  const values = trades.map(trade => `(
    '${trade.id}',
    '${trade.market_id}',
    '${trade.user_id}',
    ${trade.amount},
    '${trade.timestamp.toISOString()}'
  )`).join(',')

  await clickhouse.query(`
    INSERT INTO trades (id, market_id, user_id, amount, timestamp)
    VALUES ${values}
  `).toPromise()
}

// 错误：逐条插入（慢）
async function insertTrade(trade: Trade) {
  // 不要在循环中这样做！
  await clickhouse.query(`
    INSERT INTO trades VALUES ('${trade.id}', ...)
  `).toPromise()
}

流式插入（Streaming Insert）

// 用于持续数据摄入
import { createWriteStream } from 'fs'
import { pipeline } from 'stream/promises'

async function streamInserts() {
  const stream = clickhouse.insert('trades').stream()

  for await (const batch of dataSource) {
    stream.write(batch)
  }

  await stream.end()
}

物化视图（Materialized Views）

实时聚合

-- 创建物化视图用于每小时统计
CREATE MATERIALIZED VIEW market_stats_hourly_mv
TO market_stats_hourly
AS SELECT
    toStartOfHour(timestamp) AS hour,
    market_id,
    sumState(amount) AS total_volume,
    countState() AS total_trades,
    uniqState(user_id) AS unique_users
FROM trades
GROUP BY hour, market_id;

-- 查询物化视图
SELECT
    hour,
    market_id,
    sumMerge(total_volume) AS volume,
    countMerge(total_trades) AS trades,
    uniqMerge(unique_users) AS users
FROM market_stats_hourly
WHERE hour >= now() - INTERVAL 24 HOUR
GROUP BY hour, market_id;

性能监控

查询性能

-- 检查慢查询
SELECT
    query_id,
    user,
    query,
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 1000
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 10;

表统计信息

-- 检查表大小
SELECT
    database,
    table,
    formatReadableSize(sum(bytes)) AS size,  -- 可读的大小格式
    sum(rows) AS rows,
    max(modification_time) AS latest_modification
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes) DESC;

常用分析查询

时间序列分析

-- 每日活跃用户（DAU）
SELECT
    toDate(timestamp) AS date,
    uniq(user_id) AS daily_active_users
FROM events
WHERE timestamp >= today() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date;

-- 留存分析（Retention Analysis）
SELECT
    signup_date,
    countIf(days_since_signup = 0) AS day_0,   -- 注册当天
    countIf(days_since_signup = 1) AS day_1,   -- 次日留存
    countIf(days_since_signup = 7) AS day_7,   -- 7 日留存
    countIf(days_since_signup = 30) AS day_30  -- 30 日留存
FROM (
    SELECT
        user_id,
        min(toDate(timestamp)) AS signup_date,
        toDate(timestamp) AS activity_date,
        dateDiff('day', signup_date, activity_date) AS days_since_signup
    FROM events
    GROUP BY user_id, activity_date
)
GROUP BY signup_date
ORDER BY signup_date DESC;

漏斗分析（Funnel Analysis）

-- 转化漏斗
SELECT
    countIf(step = 'viewed_market') AS viewed,         -- 浏览
    countIf(step = 'clicked_trade') AS clicked,        -- 点击
    countIf(step = 'completed_trade') AS completed,    -- 完成
    round(clicked / viewed * 100, 2) AS view_to_click_rate,         -- 浏览到点击转化率
    round(completed / clicked * 100, 2) AS click_to_completion_rate  -- 点击到完成转化率
FROM (
    SELECT
        user_id,
        session_id,
        event_type AS step
    FROM events
    WHERE event_date = today()
)
GROUP BY session_id;

群组分析（Cohort Analysis）

-- 按注册月份的用户群组
SELECT
    toStartOfMonth(signup_date) AS cohort,           -- 群组（注册月）
    toStartOfMonth(activity_date) AS month,          -- 活跃月
    dateDiff('month', cohort, month) AS months_since_signup,  -- 注册后月数
    count(DISTINCT user_id) AS active_users          -- 活跃用户数
FROM (
    SELECT
        user_id,
        min(toDate(timestamp)) OVER (PARTITION BY user_id) AS signup_date,
        toDate(timestamp) AS activity_date
    FROM events
)
GROUP BY cohort, month, months_since_signup
ORDER BY cohort, months_since_signup;

数据管道模式

ETL 模式

// 提取（Extract）、转换（Transform）、加载（Load）
async function etlPipeline() {
  // 1. 从数据源提取
  const rawData = await extractFromPostgres()

  // 2. 转换数据
  const transformed = rawData.map(row => ({
    date: new Date(row.created_at).toISOString().split('T')[0],
    market_id: row.market_slug,
    volume: parseFloat(row.total_volume),
    trades: parseInt(row.trade_count)
  }))

  // 3. 加载到 ClickHouse
  await bulkInsertToClickHouse(transformed)
}

// 定期执行
setInterval(etlPipeline, 60 * 60 * 1000)  // 每小时一次

变更数据捕获（CDC, Change Data Capture）

// 监听 PostgreSQL 变更并同步到 ClickHouse
import { Client } from 'pg'

const pgClient = new Client({ connectionString: process.env.DATABASE_URL })

pgClient.query('LISTEN market_updates')

pgClient.on('notification', async (msg) => {
  const update = JSON.parse(msg.payload)

  await clickhouse.insert('market_updates', [
    {
      market_id: update.id,
      event_type: update.operation,  // INSERT, UPDATE, DELETE
      timestamp: new Date(),
      data: JSON.stringify(update.new_data)
    }
  ])
})

最佳实践

1. 分区策略

• 按时间分区（通常按月或按天）
• 避免过多分区（影响性能）
• 分区键使用 DATE 类型

2. 排序键

• 最常过滤的列放在最前面
• 考虑基数（Cardinality）——高基数列优先
• 排序顺序影响压缩率

3. 数据类型

• 使用最小的合适类型（UInt32 vs UInt64）
• 重复字符串使用 LowCardinality
• 分类数据使用 Enum

4. 避免的做法

• SELECT *（明确指定列）
• FINAL（查询前先合并数据）
• 过多的 JOIN（为分析场景做反范式化）
• 频繁的小量插入（改为批量插入）

5. 监控

• 跟踪查询性能
• 监控磁盘使用
• 检查合并操作
• 查看慢查询日志

核心原则： ClickHouse 擅长分析型工作负载。根据查询模式设计表结构，批量插入数据，利用物化视图实现实时聚合。