Doris OLAP 数仓分层开发标准

一句话指令

基于Apache Doris构建ODS→DIM→DWD→DWS四层轻量数仓，DIM扁平化单层星型，DWD单业务清洗仅保留patient_id数字ID，DWS采用VIEW+MV混合策略承载主题宽表和基础聚合，所有MV使用Duplicate Key模型，COMMENT覆盖全部字段，禁用分区/生命周期/ADS层。

架构约束

ODS(贴源) → DIM(维度) → DWD(明细) → DWS(汇总)

| 层级 | 对象类型 | 数据源 | 刷新策略 | 禁止 | |------|---------|--------|---------|------| | ODS | 物理表 | MySQL CDC | 无MV | 过滤/JOIN/聚合/CASE | | DIM | 物化视图 | 仅1张低量ODS | ON COMMIT | 雪花模型/跨层关联 | | DWD | 物化视图 | 同业务子表 | SCHEDULE 5-30min | 跨业务JOIN/存储患者文本 | | DWS-VIEW | 逻辑视图 | DWD+DIM | 实时 | CASE分层/除法/同比 | | DWS-MV | 物化视图 | DWD聚合 | SCHEDULE 30-60min | 复杂CASE/窗口函数 |

命名规范

| 层级 | 前缀 | 规则 | 示例 | |------|------|------|------| | ODS | 无 | 源系统表名 | patient, appointment | | DIM | dim_ | dim_{实体} | dim_patient, dim_office | | DWD | dwd_ | dwd_{业务对象} | dwd_appointment | | DWS-VIEW | dws_ | dws_{主题}_{粒度}_wide | dws_ops_daily_wide | | DWS-MV | dws_ | dws_{维度}_{粒度}_mv | dws_appointment_daily_mv |

字段规范

| 规则 | 示例 | |------|------| | 小驼峰命名 | patientId, actualPayAmount | | 计数后缀Count | orderCount, visitedCount | | 金额后缀Amount | actualPayAmount, totalPriceAmount | | 患者数后缀Patients | appointmentPatients, chargePatients | | 日期字段statDate | statDate | | 主键/外键用INT | patientId INT, officeId INT | | 金额用DECIMAL(18,2) | actualPayAmount DECIMAL(18,2) | | 枚举值用VARCHAR | apptStateType VARCHAR(16) |

MV创建模板

DIM层（单表低量）

CREATE MATERIALIZED VIEW dim_xxx
(
    col1 COMMENT '字段注释1',
    col2 COMMENT '字段注释2'
)
BUILD IMMEDIATE REFRESH AUTO ON COMMIT
DISTRIBUTED BY HASH(col1) BUCKETS AUTO
PROPERTIES (grace_period = 300, replication_num = 1)
AS SELECT ... FROM ods_table WHERE IsInactive = FALSE;

DWD层（同业务多表）

CREATE MATERIALIZED VIEW dwd_xxx
(
    col1 COMMENT '字段注释1',
    col2 COMMENT '字段注释2'
)
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 11 MINUTE
DISTRIBUTED BY HASH(col1) BUCKETS AUTO
PROPERTIES (grace_period = 1500, replication_num = 1)
AS WITH cte AS (...) SELECT ... FROM cte;

DWS-MV（聚合）

CREATE MATERIALIZED VIEW dws_xxx_daily_mv
(
    officeId COMMENT '院区ID',
    statDate COMMENT '统计日期',
    metric1 COMMENT '指标1'
)
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 30 MINUTE
DISTRIBUTED BY HASH(officeId) BUCKETS AUTO
PROPERTIES (grace_period = 1500, replication_num = 1)
AS SELECT ... GROUP BY officeId, statDate;

DWS-VIEW（主题宽表）

CREATE VIEW dws_xxx_wide COMMENT '主题描述' AS
SELECT t.*, COALESCE(m.metric1, 0) AS metric1
FROM driver_table t
LEFT JOIN aggregate_table m ON t.key = m.key;

禁止清单

| 禁止项 | 说明 | |--------|------| | PARTITIONED BY | 不使用分区 | | lifecycle | 不使用生命周期 | | Unique Key (MV) | MV底层强制Duplicate Key | | Aggregate Key (MV) | MV底层强制Duplicate Key | | DWD跨业务JOIN | 仅同业务子表关联 | | DWD存储患者文本 | 仅保留patient_id数字ID | | DWS-VIEW CASE分层 | 比率/分层/同比在BI层计算 | | ADS层 | 不创建ADS层 | | ON COMMIT高频CDC | 仅低频批量表使用 |

部署顺序

1. ODS → 2. DIM(先ON COMMIT单表,再每日多表) → 3. DWD(先低量ON COMMIT,再多子表SCHEDULE) → 4. DWS-MV(刷新间隔>上游DWD) → 5. DWS-VIEW

质量检查清单

[ ] 所有MV使用Duplicate Key
[ ] 所有字段有COMMENT注释
[ ] 定时语法携带ON SCHEDULE
[ ] replication_num=1（单副本）
[ ] DWD禁止跨业务患者/支付关联
[ ] DWS-VIEW无CASE/除法/同比计算
[ ] 无ADS层对象
[ ] 无PARTITION/lifecycle语法