大数据项目之电商数仓（总结）（八）：系统业务数据仓库-拉链表

问题导读

1.什么是拉链表？
2.拉链表如何更新？
3.拉链表制作包含哪些过程？


上一篇
大数据项目之电商数仓（总结）（八）：系统业务数据仓库
https://www.aboutyun.com/forum.php?mod=viewthread&tid=30286


第15章 订单表拉链表

15.1 什么是拉链表


15.2 为什么要做拉链表




15.3 拉链表形成过程



15.4 拉链表制作过程图



15.5 拉链表制作过程
15.5.1 步骤0：初始化拉链表（首次独立执行）




1）生成10条原始订单数据

CALL init_data('2019-02-13',10,5,10,TRUE);

[kgg@hadoop102 bin]$ sqoop_import.sh all 2019-02-13

[kgg@hadoop102 bin]$ ods_db.sh 2019-02-13

[kgg@hadoop102 bin]$ dwd_db.sh 2019-02-13
复制代码

2）建立拉链表

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");
复制代码

3）初始化拉链表

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-12-16',
    '9999-99-99'
from ods_order_info oi
where oi.dt='2019-12-16';
复制代码

4）查询拉链表中数据

hive (gmall)> select * from dwd_order_info_his limit 2;
复制代码

15.5.2 步骤1：制作当日变动数据（包括新增，修改）每日执行
1）如何获得每日变动表
（1）最好表内有创建时间和变动时间（Lucky!）
（2）如果没有，可以利用第三方工具监控比如canal，监控MySQL的实时变化进行记录(麻烦)。
（3）逐行对比前后两天的数据, 检查md5(concat(全部有可能变化的字段))是否相同(low)
（4）要求业务数据库提供变动流水（人品，颜值）

2）因为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层数据导入

sqoop_import.sh all 2019-02-14
复制代码

（4）DWD层数据导入

dwd_db.sh 2019-02-14
复制代码

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

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");
复制代码

2）导入脚本

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;
复制代码

15.5.4 步骤3：把临时表覆盖给拉链表
1）导入数据

hive (gmall)>
insert overwrite table dwd_order_info_his 
select * from dwd_order_info_his_tmp;
复制代码

2）查询导入数据

hive (gmall)> select * from dwd_order_info_his;
复制代码

最新经典文章，欢迎关注公众号

学习拉链表

支持你，辛苦