电商离线数仓-拉链表(面试常问)_拉链表怎么设计的面试题?

拉链表概念

拉链表,记录每条信息的生命周期为单位,一旦记录的生命周结束,就重新开始一条新的记录,并把当前日期放入生效开始日期。

举个例子来快速理解一下拉链表:一条订单,从2021-01-01这天处于下单状态,2021-01-02处于待支付状态,2021-12-31处于已支付状态,那么正常的从头到尾记录的话,这个表里面单单这一条记录一年就有365条。拉链表做的就是简化表的数量,只记录变化的条数,那么总的就变成了3条,下单,待支付,已完成。当然,拉链表适用于那些不经常变化(大部分时间不变化)的情况。

为什么要做拉链表?
Hive数仓不容易实现对数据的修改。数据量有一定规模,且无法按照每日全量的方式保存。比如:1亿用户*365天,每天一份用户信息。(无法做每日全量)

拉链表更新数据

有一点需要特别注意的是:
Hive数仓的最大特点就是不容易对数据进行更新修改。拉链表是如何对数据进行更改的? 最简单的办法就是不用修改了,直接overwrite覆盖了。

拉链表形成过程

1)假设,2019年1月1日的订单全量表是最初始的订单表,如下:

2)初始的拉链表等于最开始的2019年1月1日的订单全量表:

3)第二天1月2日订单全量表 (订单2发生状态修改;订单4,5增加)

4)根据订单表的创建时间和操作时间,得到订单变化表

5)订单变化表与之前的拉链表合并得到(将步骤2和步骤4的表合并)

如何使用拉链表

第一种:某个日期<=生效开始日期 且 某个日期>=生效结束日期,能够得到某个时间点的数据全量切片。
1)拉链表数据

2)获取2019-01-01的历史切片:

select * from order_info where start_date<='2019-01-01' and end_date>='2019-01-01'
1

如下:

3)获取2019-01-02的历史切片:

select * from order_info where start_date<='2019-01-02' and end_date>='2019-01-02'
1

拉链表制作流程图

!!!
订单当日全部数据和Mysql中每天变化的数据拼接在一起,形成一个新的临时拉链表数据。用临时的拉链表覆盖旧的拉链表数据。(这就解决了Hive表中数据不能更新的问题)
!!!

面试中常问的问题就是拉链表。为了解决Hive表中数据不能更新的问题,采用的解决方法是拉链表。订单当日全部数据形成订单拉链表(本来的表的数据是订单ID和状态,现在变成订单ID,状态,开始时间和结束时间),MYSQL中取出当日变化的数据,这两张表一合并形成一个新的订单拉链临时表。

拉链表制作过程

步骤1:初始化拉链表,首次独立执行

生成10条原始订单的数据

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

#利用时间写好的sqoop导入脚本导入数据 
sqoop_import.sh all 2019-02-13

ods_db.sh 2019-02-13

dwd_db.sh 2019-02-13
1
2
3
4
5
6
7
8

建立拉链表

drop table if exists dwd_order_info_his;
create 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");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

初始化拉链表

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';
1
2
3
4
5
6
7
8
9
10
11
12
13
14

查询拉链表中数据

select * from dwd_order_info_his limit 2;
1

步骤2:先制作当日变动(包括新增,修改)每日执行

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);
1

（2）通过Sqoop把2019-02-14日所有数据导入

sqoop_import.sh all 2019-02-14
1

（3）ODS层数据导入

ods_db.sh 2019-02-14
1

（4）DWD层数据导入

dwd_db.sh 2019-02-14
1

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

1)建立临时表:

drop table if exists dwd_order_info_his_tmp;
create external 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");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2)导入脚本

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

步骤4:把临时表覆盖给拉链表

hive (gmall)>
insert overwrite table  dwd_order_info_his 
select * from  dwd_order_info_his_tmp;
1
2
3

查询导入数据:

select * from dwd_order_info_his ;
1