数据仓库之维度建模剖析

下面我们分别来学习维度表设计和事实表设计：

    • 维度表设计：代理键，稳定维度，缓慢渐变维，拉链表

    • 事实表设计：事实表设计，明细事实表，聚合事实表

    • 数据仓库之拉链表详解

 

1.维度表设计

1.1 代理键

    维度表中必须有一个能够唯一标识一行记录的列(最好是原子性的列，不要是组合键)， 通过该列维护维度表与事实表之间的关系，一般在维度表中业务主键符合条件可以当作维度主键。

    但是，数据仓库是整个公司数据的整合，这会涉及到多个数据源有相同维度，那么就会 出现以下两个问题:

    • 当整合多个数据源的维度时，不同数据源的业务主键重复怎么办?

    • 涉及维度拉链表时，同一主体多条记录，业务键重复怎么办?

 

    如上图所示，业务键重复，我们可以引入代理键，如下表所示:

    把多个系统的数据复合在一起，同时再维护一个代理键，而且代理键在这个维度表里是 唯一标识一条记录的，类似于业务系统的业务键。

    代理键是由数据仓库处理过程中产生的、与业务本身无关的、唯一标识维度表中一条 记录并充当维度表主键的列，也是描述维度表与事实表关系的纽带。

    在设计有代理键的维度表中，事实表中的关联键是代理键而不是原有的业务主键，即 业务关系是靠代理键维护，这样有效避免源系统变化对数仓数据对影响。

    在实际业务中，代理键通常是数值型、自增的值。

 

1.2 稳定维度

    部分维度表的维度是在维度表产生后，属性是稳定的、无变化的。比如时间维度、区域维度等，针对这种维度，设计维度表的时候，仅需要完整的数据，不需要天的快照数据， 因为当前数据状态就是历史数据状态。

 

1.3 缓慢渐变维

    维度数据会随着时间发生变化，变化速度比较缓慢，这种维度数据通常称作缓慢渐变维，例如电商平台的用户维度表，用户可能会随着时间推移改变收件地址，因此用户维度表 中的收件地址就是一个缓慢变化维。由于数据仓库需要追溯历史变化，尤其是一些重要的数 据，所以历史状态也需要采取一定的措施进行保存，保存历史状态的方式有以下三种:

    • 每天保存当前数据的全量快照数据(每天一个新增分区)，该方案适合数据量较小 (根据公司具体的配置而定)的维度，使用简单的方式保存历史状态。

    • 在维表中添加关键属性值的历史字段，仅保留上一个的状态值。可能同时有多个属 性都非常重要，而且只能追溯上一个数据，不是所有的历史数据，这种范式应用场景较少。

    • 拉链表:当维度数据发生变化时，将旧数据置为失效，将更改后的数据当作新的 记录插入到维度表中，并开始生效，这样能够记录数据在某种粒度上的变化历史。

1.4 拉链表

    将数据的变更当做流水记录下来 ，旧的设为失效，新的设为生效，如果粒度为天，那么就可以得到一天的最终状态作为最终状态。

    表 5-4 中每条记录都有一个 End_date，当有新的数据产生时，在旧数据的 End_date 字 段中插入日期，然后新插入一条数据，新数据的 End_date 字段中是一个永久有效的值，如果再发生更新，上一次更新数据的 End_date 字段设置为当前日期，然后再次插入新数据， 新数据的 End_date 字段中设置一个永久有效的值。

    如果想知道某个员工在 5 月 22 号时在哪个部门，那么可以通过如下 SQL: Select * from user where start_date<= 2018-05-22 and end_date>= 2018-05-22

    根据拉链表的结构，如果对维度表做拉链，那么一个维度实体必然存在多条记录，也就是一个主键 ID 对应多条数据，此时维度表的原子性主键也就没有意义了。

    维度表做拉链后会失去原子性主键，那么拉链维度表如何和事实表进行关联呢? 此时就要用到代理键，也就是在事实表和维度表中同时添加代理键，如下图所示:

    完成代理键的添加后，在之后的统计中，按照代理键进行聚合即可。

    事实表来源于业务事务表，代理键和业务本身没有关系，那么怎么在新增数据时在事实表中装载代理键?

    当事实表中有新增数据时，新增数据中记录了维度表中原有的原子性主键，可以根据原有的主键匹配维度表中的数据，然后根据新增数据的时间范围找到匹配的代理键，然后在事实表的新增数据中加入代理键。

    代理键是维度建模中极力推荐的方式，它的应用能有效的隔离源端变化带来的数仓结构 不稳定问题，同时也能够提高数据检索性能。

    但是代理键维护代价非常高，尤其是数据装载过程中，对事实表带来了较大的影响， 在基于 hive 的数据仓库建设影响更加严重，比如代理键的生成、事实表中关联键的装载、 不支持非等值关联等问题，带来 ETL 过程更加复杂。

    因此，在大数据体系下，谨慎使用代理键，同时对于缓慢渐变维场景，可以考虑用空间换取时间，每天保留维表全量快照，但这样会带来存储成本，根据实际情况衡量。

 

2.事实表设计

2.1 事实表设计

2.1.1 增量存储 

    当事实表数据无状态变化时，采用增量存储，即每周期仅处理增量部分的数据，纯增量采集。

2.1.2 全量快照

    状态有变化，但每天保存当前的快照数据，对于数据量在可控范围内的情况可以采用。

2.1.3 拉链

    数据量大，但缓慢变化，需要跟踪历史状态，和缓慢渐变维类似。

    如果变化非常快，拉链表的数据量会大于快照表数倍，一天变一次，那么一周就保存了 7 份数据，可以考虑把已经失效的数据转移到其他的存储介质或者冷盘上，或者定期(一个月)进行删除。

 

2.2 明细事实表

    事实表有粒度大小之分，基于数据仓库层次架构，明细事实表一般存在于 DWD 层，该层事实表设计不进行聚合、汇总动作，仅做数据规范化、数据降维动作，同时数据保持业务 事务粒度，确保数据信息无丢失。

    DWD 层与业务强相关，DWD 层的表就是业务表经过一系列规范化、降维之后的表。

 

2.3 聚合事实表

    相对于明细事实表，聚合事实表通常是在明细事实表的基础上，按照一定的粒度粗细进行的汇总、聚合操作，它的粒度较明细数据粒度粗，同时伴随着细节信息的丢失。 在数仓层次结构中，聚合事实表通常位于 DWS 层，一般作为通用汇总数据存在，也可以是更高粒度的指标数据。 聚合事实表的数据来源可以是两种明细事实表中的任意一种。

    • 日粒度

    • 周期性累积(周，月，年)

    • 历史累积(累计订单量、累计金额）

2.3.1 可累加事实与不可加事实

• 可累加事实：可累加事实是在一定的粒度范围内，可累加的事实度量，比如:订单金额、订单数。
• 不可累加事实：不可累加事实是在更高粒度上不可累加的事实，比如通过率、转化率等。 通常情况下，比率这种不可累积的事实，建议拆分存储，比如通过率拆分为通过数、申请数，由细粒度数据去重计算而得到的事实，正常存储，但是更粗粒度累积是不可直接使用。

2.3.2 聚合事实表分类

• 公共维度层/通用汇总层

    封装底层计算逻辑，做通用汇总，避免上层直接访问下层明细数据。

    应对大部分可预期的、常规的数据需求，通常针对模式相对稳定的分析、BI 指标计算、 特征提取等场景，封装部分业务处理、计算逻辑，尽量避免用户直接使用底层明细数据，该 层用到的数据范围比较广泛。

    通用汇总层需要满足 80%~90%的场景，对数据进行轻度汇总，避面直接访问明细层， 假设明细层有 1 亿条数据，这一层可能只有 1 千万条。

• 日粒度

    主要应对模式稳定的分析、BI 日报、特征提取场景，同时日粒度也为后续累积计算提供粗粒度的底层，数据范围一般为上一日的数据。 对可累加指标进行粗粒度的统计，周、月等粒度的统计可以在日粒度基础上计算，假设明细层 1 亿条数据，这一层可能只有 1 百万条。 2.3 周期性累积

    主要应对明确的周期性分析、BI 周期性报表，数据范围一般在某周期(周、月等)内 的。底层数据可以来自于公共维度层-通用汇总，也可以来自于日粒度。

• 历史累积

    顾名思义，历史以来某一特定数据的累积，通常在用户画像、经营分析、特征提取方面场景较多，设计数据范围比较广泛，通常是计算耗时较长的一部分，比如某门店累积营业额、某用户累积利润贡献、用户首次下单时间(非可度量、描述性)。

 

3.数据仓库之拉链表详解

3.1 什么是拉链表

 

 

3.2 为什么要做拉链表

 

3.3 如何使用拉链表

 

3.4 拉链表形成过程

 

3.5 拉链表制作过程图

 

3.6 拉链表制作过程

3.6.1 步骤0：初始化拉链表（首次独立执行）

// 生成10条原始订单数据
CALL init_data('2019-02-13',10,5,10,TRUE);
[mkluo@hadoop102 bin]$ sqoop_import.sh all 2019-02-13
[mkluo@hadoop102 bin]$ ods_db.sh 2019-02-13
[mkluo@hadoop102 bin]$ dwd_db.sh 2019-02-13


//建立拉链表
hive (gmall)>
drop table if exists dwd_order_info_his;
create external table dwd_order_info_his(
    `id` string COMMENT '订单编号',
    `total_amount` decimal(10,2) COMMENT '订单金额',
    `order_status` string COMMENT '订单状态',
    `user_id` string COMMENT '用户id' ,
    `payment_way` string COMMENT '支付方式',
    `out_trade_no` string COMMENT '支付流水号',
    `create_time` string COMMENT '创建时间',
    `operate_time` string COMMENT '操作时间',
    `start_date`  string COMMENT '有效开始日期',
    `end_date`  string COMMENT '有效结束日期'
) COMMENT '订单拉链表'
stored as parquet
location '/warehouse/gmall/dwd/dwd_order_info_his/'
tblproperties ("parquet.compression"="snappy");


//初始化拉链表
hive (gmall)>
insert overwrite table dwd_order_info_his
select
    id,
    total_amount,
    order_status,
    user_id,
    payment_way,
    out_trade_no,
    create_time,
    operate_time,
    '2019-02-13',
    '9999-99-99'
from ods_order_info oi
where oi.dt='2019-02-13’;


// 查询拉链表中数据
hive (gmall)> select * from dwd_order_info_his limit 2;

 

 

3.6.2 步骤1：制作当日变动数据（包括新增，修改）每日执行

• 如何获得每日变动表

        （1）最好表内有创建时间和变动时间（Lucky!）

        （2）如果没有，可以利用第三方工具监控比如canal，监控MySQL的实时变化进行记录(麻烦)。

        （3）逐行对比前后两天的数据, 检查md5(concat(全部有可能变化的字段))是否相同(low)

        （4）要求业务数据库提供变动流水（人品，颜值）

• 因为dwd_order_info本身导入过来就是新增变动明细的表，所以不用处理

        （1）2019-02-14日新增2条订单数据：CALL init_data('2019-02-14',2,5,10,TRUE);

        （2）通过Sqoop把2019-02-14日所有数据导入：sqoop_import.sh all 2019-02-14

        （3）ODS层数据导入：ods_db.sh 2019-02-14

        （4）DWD层数据导入：dwd_db.sh 2019-02-14

3.6.3 步骤2：先合并变动信息，再追加新增信息，插入到临时表中

// 建立临时表
hive (gmall)>
drop table if exists dwd_order_info_his_tmp;
create table dwd_order_info_his_tmp( 
    `id` string COMMENT '订单编号',
    `total_amount` decimal(10,2) COMMENT '订单金额', 
    `order_status` string COMMENT '订单状态', 
    `user_id` string COMMENT '用户id' ,
    `payment_way` string COMMENT '支付方式',  
    `out_trade_no` string COMMENT '支付流水号',  
    `create_time` string COMMENT '创建时间',  
    `operate_time` string COMMENT '操作时间',
    `start_date`  string COMMENT '有效开始日期',
    `end_date`  string COMMENT '有效结束日期'
) COMMENT '订单拉链临时表'
stored as parquet
location '/warehouse/gmall/dwd/dwd_order_info_his_tmp/'
tblproperties ("parquet.compression"="snappy”);


// 导入脚本
 
hive (gmall)>
insert overwrite table dwd_order_info_his_tmp
select * from 
(
select 
id,
    total_amount,
    order_status,
    user_id,
    payment_way,
    out_trade_no,
    create_time,
    operate_time,
    '2019-02-14' start_date,
    '9999-99-99' end_date
from dwd_order_info where dt='2019-02-14'
union all 
select oh.id,
    oh.total_amount,
    oh.order_status,
    oh.user_id,
    oh.payment_way,
    oh.out_trade_no,
    oh.create_time,
    oh.operate_time,
    oh.start_date,
    if(oi.id is null, oh.end_date, date_add(oi.dt,-1)) end_date
from dwd_order_info_his oh left join 
     (
select
*
from dwd_order_info
where dt='2019-02-14'
) oi
     on oh.id=oi.id and oh.end_date='9999-99-99'  
)his 
order by his.id, start_date;

 

3.6.4 步骤3：把临时表覆盖给拉链表

//导入数据
hive (gmall)>
insert overwrite table dwd_order_info_hi
select * from dwd_order_info_his_tmp;


//查询导入数
hive (gmall)> select * from dwd_order_info_his;