公共维度汇总层（DIM）

维表规范

公共维度汇总层（DIM）维表命名规范：dim{业务板块名称/pub}{维度定义}[_{自定义命名标签}]，pub是与具体业务板块无关或各个业务板块都可公用的维度。例如，时间维度，例如：

  • 公共区域维表dim_pub_area
  • A公司电商板块的商品全量表dim_asale_itm

    维表设计步骤

  1. 初步定义维度
    保证维度的一致性。
  2. 确定主维表
    通常是数据引入层（ODS）表，直接与业务系统同步。
  3. 确定相关维表
    根据对业务的梳理，确定哪些表和主维表存在关联关系，并选择其中的某些表用于生成维度属性。以商品维度为例，商品与类目、卖家和店铺等维度存在关联关系。
  4. 确定维度属性
    1. 第一个阶段是从主维表中选择维度属性或生成新的维度属性
    2. 第二个阶段是从相关维表中选择维度属性或生成新的维度属性 ```plsql CREATE TABLE IF NOT EXISTS dimasale_itm ( item_id BIGINT COMMENT ‘商品ID’, item_title STRING COMMENT ‘商品名称’, item_price DOUBLE COMMENT ‘商品成交价格元’, item_stuff_status BIGINT COMMENT ‘商品新旧程度_0全新1闲置2二手’, cate_id BIGINT COMMENT ‘商品类目ID’, cate_name STRING COMMENT ‘商品类目名称’, commodity_id BIGINT COMMENT ‘品类ID’, commodity_name STRING COMMENT ‘品类名称’, item_status BIGINT COMMENT ‘商品状态_0正常1用户删除2下架3未上架’, city STRING COMMENT ‘商品所在城市’, prov STRING COMMENT ‘商品所在省份’ ) COMMENT ‘商品全量表’ PARTITIONED BY (ds STRING COMMENT ‘日期,yyyymmdd’);

CREATE TABLE IF NOT EXISTS dim_pub_area ( city_code STRING COMMENT ‘城市code’, city_name STRING COMMENT ‘城市名称’, prov_code STRING COMMENT ‘省份code’, prov_name STRING COMMENT ‘省份名称’ ) COMMENT ‘公共区域维表’ PARTITIONED BY (ds STRING COMMENT ‘日期分区,格式yyyymmdd’) LIFECYCLE 3600; ```

数据引入层（ODS）

ODS层设计规范

命名规范

  • 表命名规范
    表命名规则：{层次}{源系统表名}{保留位/delta与否}。
    • 增量数据：{project_name}.s{源系统表名}delta。
    • 全量数据：{project_name}.s{源系统表名}。
    • ODS ETL过程的临时表：{project_name}.tmp{临时表所在过程的输出表}{从0开始的序号}。
    • 按小时同步的增量表：{projectname}.s{源系统表名}{delta}{hh}。
    • 按小时同步的全量表：{project_name}.s{源系统表名}{hh}。
    • 当不同源系统同步到同一个Project下的表命名冲突时，您需要给同步较晚的表名加上源系统的dbname以解决冲突。
  • 字段命名规范
    • 字段默认使用源系统的字段名。
    • 字段名与MaxCompute关键字冲突时，在源字段名后加上col，即源字段名col。

  • 同步任务命名规范

    • 任务名：{源系统表名}[delta]。说明 同一Project下异库同名表的任务名为{源系统表名}{tddl的appname}

      数据引入层存储

  • 增量存储
    以天为单位的增量存储，以业务日期作为分区，每个分区存放日增量的业务数据。
    说明 交易、日志等事务性较强的ODS表适合增量存储方式

  • 全量存储
    以天为单位的全量存储，以业务日期作为分区，每个分区存放截止到业务日期为止的全量业务数据。
    说明 对于小数据量的缓慢变化维度数据，例如商品类目，可直接使用全量存储。
  • 拉链存储拉链存储通过新增两个时间戳字段（start_dt和end_dt），将所有以天为粒度的变更数据都记录下来，通常分区字段也是这两个时间戳字段。 | 商品 | start_dt | end_dt | 卖家 | 状态 | | —- | —- | —- | —- | —- | | B | 20160101 | 20160102 | A | 上架 | | C | 20160101 | 30001231 | A | 上架 | | B | 20160102 | 30001231 | A | 下架 |

明细粒度事实层（DWD）

事实表的外键字段通过对应维度进行关联。维度退化：维度属性可以存储到事实表中，用来进行事实表的过滤查询、加速查询、实现聚合等操作
事实表中一条记录所表达的业务细节程度被称为粒度。通常粒度可以通过两种方式来表述：一种是维度属性组合所表示的细节程度，一种是具体业务含义。
作为度量业务过程的事实，通常为整型或浮点型的十进制数值，有可加性、半可加性和不可加性三种类型：

  • 可加性事实是指可以按照与事实表关联的任意维度进行汇总。
  • 半可加性事实只能按照特定维度汇总，不能对所有维度汇总。例如库存可以按照地点和商品进行汇总，而按时间维度把一年中每个月的库存累加则毫无意义。
  • 完全不可加性，例如比率型事实。对于不可加性的事实，可分解为可加的组件来实现聚集。

明细粒度事实表通常分为三种

  • 事务事实表：用来描述业务过程，跟踪空间或时间上某点的度量事件，保存的是最原子的数据，也称为原子事实表。
  • 周期快照事实表：以具有规律性的、可预见的时间间隔记录事实。

  • 累积快照事实表：用来表述过程开始和结束之间的关键步骤事件，覆盖过程的整个生命周期，通常具有多个日期字段来记录关键时间点。当累积快照事实表随着生命周期不断变化时，记录也会随着过程的变化而被修改。

    明细粒度事实层（DWD）规范

    命名规范为：dwd{业务板块/pub}{数据域缩写}{业务过程缩写}[{自定义表命名标签缩写}] _{单分区增量全量标识}
    pub表示数据包括多个业务板块的数据。单分区增量全量标识通常为：i表示增量，f表示全量。
    例如： dwd_asale_trd_ordcrt_trip_di（A电商公司航旅机票订单下单事实表，日刷新增量）
    dwd_asale_itm_item_df（A电商商品快照事实表，日刷新全量）

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

    聚集是指针对原始明细粒度的数据进行汇总。

  • 聚集是不跨越事实的。聚集是针对原始星形模型进行的汇总。为获取和查询与原始模型一致的结果，聚集的维度和度量必须与原始模型保持一致，因此聚集是不跨越事实的。

  • 聚集会带来查询性能的提升，但聚集也会增加ETL维护的难度。当子类目对应的一级类目发生变更时，先前存在的、已经被汇总到聚集表中的数据需要被重新调整。