数据仓库模型设计及实施方法

目录

1 数仓的分层

2 ODS层的存储

3 公共维度汇总层（DIM）维表规范

4. 明细粒度事实层（DWD）

5. 公共汇总粒度事实层（DWS）

6 层次调用规范

7 数据仓库建模

7.1 什么是数据仓库建模

7.2 数据仓库建模流程

7.2.1 建模流程

7.2.2 建模的过程

7.2.3 事实表设计方法

7.2.4 维度设计的基本方法

7.3 模型的设计

7.3.1 模型设计的思路

7.3.2 模型落地实现

---

---

1 数仓的分层

（1）如何分层？

• ODS层：原始数据层，存放原始数据，直接加载原始日志，数据，数据保持原貌不做处理
• DWD层：数据明细层：结构和粒度保持原始表一致。主要作用是对ODS层数据进行清洗（去除空值NULL，脏数据，超过极限范围的数据如9999），当然也包括对原始解析处理，如串值的展开，json的解析等
• DWS层：数据汇总层或数据服务层：依赖于DWD层数据，按天粒度进行汇总
• DWT层：数据主题层：以DWS层为基础，按主题进行汇总。
• ADS层：为各种展示及统计报表提供数据。

（2）为什么要分层？

• 1）复杂问题简单化：类似于软件分层。将任务分层完成，每一层都有自己的作用域和职责，出现问题方便定位和维护
• 2）减少重复开发：规范数据分层，通过开发一些复用的中间结果供上层使用，可以极大的减少重复计算，重复开发，增加结果的复用性
• 3）隔离原始数据：不论是数据的异常还是数据的敏感性，将真实数据与统计数据进行解耦。

2 ODS层的存储

ods 层的存储可以采用增量、全量或拉链表的方式进行存储。为了满足历史数据分析需求，您可以在ODS层表中添加时间维度作为分区字段。

建表示例（s_auction）

CREATE TABLE IF NOT EXISTS s_auction
(
    id                             STRING COMMENT '商品ID',
    title                          STRING COMMENT '商品名',
    gmt_modified                   STRING COMMENT '商品最后修改日期',
    price                          DOUBLE COMMENT '商品成交价格，单位元',
    starts                         STRING COMMENT '商品上架时间',
    minimum_bid                    DOUBLE COMMENT '拍卖商品起拍价，单位元',
    duration                       STRING COMMENT '有效期，销售周期，单位天',
    incrementnum                   DOUBLE COMMENT '拍卖价格的增价幅度',
    city                           STRING COMMENT '商品所在城市',
    prov                           STRING COMMENT '商品所在省份',
    ends                           STRING COMMENT '销售结束时间',
    quantity                       BIGINT COMMENT '数量',
    stuff_status                   BIGINT COMMENT '商品新旧程度 0 全新 1 闲置 2 二手',
    auction_status                 BIGINT COMMENT '商品状态 0 正常 1 用户删除 2 下架 3 从未上架',
    cate_id                         BIGINT COMMENT '商品类目ID',
    cate_name                        STRING COMMENT '商品类目名称',
    commodity_id                     BIGINT COMMENT '品类ID',
    commodity_name                    STRING COMMENT '品类名称',
    umid                              STRING COMMENT '买家umid'
)
COMMENT '商品拍卖ODS'
PARTITIONED BY (ds         STRING COMMENT '格式：YYYYMMDD')
LIFECYCLE 400;

• 增量存储

以天为单位的增量存储，以业务日期作为分区，每个分区存放日增量的业务数据。举例如下：
1月1日，用户A访问了A公司电商店铺B，A公司电商日志产生一条记录t1。1月2日，用户A又访问了A公司电商店铺C，A公司电商日志产生一条记录t2。采用增量存储方式，t1将存储在1月1日这个分区中，t2将存储在1月2日这个分区中。
1月1日，用户A在A公司电商网购买了B商品，交易日志将生成一条记录t1。1月2日，用户A又将B商品退货了，交易日志将更新t1记录。采用增量存储方式，初始购买的t1记录将存储在1月1日这个分区中，更新后的t1将存储在1月2日这个分区中。

交易、日志等事务性较强的ODS表适合增量存储方式。这类表数据量较大，采用全量存储的方式存储成本压力大。此外，这类表的下游应用对于历史全量数据访问的需求较小（此类需求可通过数据仓库后续汇总后得到）。例如，日志类ODS表没有数据更新的业务过程，因此所有增量分区UNION在一起就是一份全量数据。

• 全量存储
  以天为单位的全量存储，以业务日期作为分区，每个分区存放截止到业务日期为止的全量业务数据。
  例如，1月1日，卖家A在A公司电商网发布了B、C两个商品，前端商品表将生成两条记录t1、t2。1月2日，卖家A将B商品下架了，同时又发布了商品D，前端商品表将更新记录t1，同时新生成记录t3。采用全量存储方式， 在1月1日这个分区中存储t1和t2两条记录，在1月2日这个分区中存储更新后的t1以及t2、t3记录。

对于小数据量的缓慢变化维度数据，例如商品类目，可直接使用全量存储。

• 拉链存储

拉链存储通过新增两个时间戳字段（start_dt和end_dt），将所有以天为粒度的变更数据都记录下来，通常分区字段也是这两个时间戳字段。

拉链存储举例如下。

这样，下游应用可以通过限制时间戳字段来获取历史数据。例如，用户访问1月1日数据，只需限制start_dt<=20160101并且 end_dt>20160101。

 缓慢变化维度

数仓建模过程中不推荐使用代理健，推荐使用自然键作为维度主键，其原因主要如下：

• 分布式计算引擎，生成全局唯一的代理键工作量非常大。当遇到大数据量情况下，这项工作就会更加复杂，且没有必要。

• 使用代理键会增加ETL的复杂性，从而增加ETL任务的开发和维护成本。

在不使用代理键的情况下，缓慢变化维度可以通过快照方式处理。

快照方式下数据的计算周期通常为每天一次。基于该周期，处理维度变化的方式为每天一份全量快照。

例如商品维度，每天保留一份全量商品快照数据。任意一天的事实表均可以取到当天的商品信息，也可以取到最新的商品信息，通过限定日期，采用自然键进行关联即可。该方式的优势主要有以下两点：

• 处理缓慢变化维度的方式简单有效，开发和维护成本低。

• 使用方便，易于理解。数据使用方只需要限定日期即可取到当天的快照数据。任意一天的事实快照与任意一天的维度快照通过维度的自然键进行关联即可。

该方法的弊端主要是存储空间的极大浪费。例如某维度每天的变化量占总体数据量比例很低，极端情况下，每天无变化，这种情况下存储浪费严重。该方法主要实现了通过牺牲存储获取ETL效率的优化和逻辑上的简化。请避免过度使用该方法，且必须要有对应的数据生命周期制度，清除无用的历史数据。

3 公共维度汇总层&#