ClickHouseNotes

第 24 章：数据建模

zjc 于 2026-01-24 发布

这是《ClickHouse 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 ClickHouse 建模围绕宽表、明细、汇总和维度展开。目标是让高频查询少扫描、少 JOIN、少运行时转换。

24.1 分层

ODS：原始明细，保留字段和可重放信息
DWD：清洗明细，统一类型和枚举
DWS：主题汇总，常用维度组合
ADS：报表结果，直接服务应用
DIM：维表和字典

示例：

层 表
ODS kafka raw events
DWD events_local
DWS city_daily_metrics
ADS realtime_dashboard
DIM city_dict、product_dim

24.2 明细表

CREATE TABLE analytics.dwd_events
(
    event_date Date,
    event_time DateTime,
    event_id String,
    user_id UInt64,
    device_id String,
    event_type LowCardinality(String),
    platform LowCardinality(String),
    city_id UInt32,
    channel LowCardinality(String),
    amount Decimal64(2),
    props Map(String, String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_type, city_id, channel, user_id)
TTL event_date + INTERVAL 13 MONTH;

设计原则：

  1. 高频过滤字段普通列化；
  2. 低基数字段使用 LowCardinality；
  3. 金额使用 Decimal；
  4. 时间字段明确时区；
  5. props 只放低频扩展属性；
  6. 生命周期通过 TTL 和分区表达。

24.3 汇总表

CREATE TABLE analytics.dws_city_daily
(
    stat_date Date,
    city_id UInt32,
    channel LowCardinality(String),
    pv UInt64,
    pay_count UInt64,
    gmv Decimal64(2)
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(stat_date)
ORDER BY (stat_date, city_id, channel);

查询：

SELECT stat_date, city_id, sum(pv), sum(gmv)
FROM analytics.dws_city_daily
WHERE stat_date >= today() - 7
GROUP BY stat_date, city_id;

汇总粒度来自高频报表，不是为了消灭明细。没有明细的口径，后期很难追溯。

24.4 状态表

CREATE TABLE analytics.dwd_order
(
    order_date Date,
    order_id String,
    user_id UInt64,
    order_status LowCardinality(String),
    amount Decimal64(2),
    updated_at DateTime,
    version UInt64
)
ENGINE = ReplacingMergeTree(version)
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_date, order_id);

查询最新状态：

SELECT
    order_id,
    argMax(order_status, version) AS status,
    argMax(amount, version) AS amount,
    max(updated_at) AS updated_at
FROM analytics.dwd_order
WHERE order_date = today()
GROUP BY order_id;

状态表要固定业务日期，避免同一订单跨分区或跨分片导致去重失败。

24.5 维表与字典

CREATE DICTIONARY analytics.city_dict
(
    city_id UInt64,
    city_name String,
    province_id UInt32
)
PRIMARY KEY city_id
SOURCE(CLICKHOUSE(TABLE 'city_dim'))
LIFETIME(MIN 300 MAX 600)
LAYOUT(HASHED());

查询：

SELECT
    dictGet('analytics.city_dict', 'city_name', toUInt64(city_id)) AS city_name,
    count()
FROM analytics.dwd_events
GROUP BY city_name;

小而稳定的维表适合字典；大维表或缓慢变化维表建议使用版本化维表和宽表策略。

24.6 宽表与 JOIN

宽表减少查询时 JOIN：

events + city + product + campaign -> event_wide

冗余字段选择标准：

  1. 查询频率高；
  2. 变化不频繁；
  3. 类型稳定；
  4. 对存储影响可接受；
  5. 不引起口径混乱。

易变字段保留版本或查询时关联，避免宽表频繁重写。

24.7 指标口径

指标必须定义清楚：

字段 示例
指标名 付费 GMV
聚合 sum(amount)
过滤条件 status = PAID
去重规则 订单号唯一
时间口径 支付时间
时区 Asia/Shanghai
延迟处理 支持重跑窗口

同一指标在明细表和汇总表必须可对账。对账 SQL 应成为上线检查项，而不是事后人工记忆。

24.8 建模流程

1. 收集业务问题
2. 定义指标和维度
3. 设计 DWD 明细
4. 选择表引擎
5. 设计分区和排序
6. 构建 DWS 汇总
7. 建立字典和权限
8. 对账和压测
9. 接入查询服务

本章小结

ClickHouse 建模以宽表和明细为主，辅以主题汇总、状态表和字典。高频字段普通列化，可累加指标预聚合，状态数据用版本去重。指标口径和可对账性比表数量更重要。

思考题

  1. ODS、DWD、DWS 分别解决什么问题？
  2. 哪些字段应该从 Map 提升为普通列？
  3. 状态表为什么用 ReplacingMergeTree？
  4. 宽表冗余维度的取舍是什么？
  5. 如何保证汇总表和明细表口径一致？