拉链表的制作_基于客户信息表制作月表拉链表

1、为什么要做拉链表

拉链表，记录每条信息的生命周期，一旦一条记录的生命周期结束，就重新开始一条新的记录，并把当前日期放入生效开始日期。
拉链表适合于：数据会发生变化，但是大部分是不变的。
比如：订单信息从未支付、已支付、未发货、已完成等状态经历了一周，大部分时间是不变化的。
拉链表格式

2、拉链表的制作过程

过程：订单当日全部数据和MySQL中每天变化的数据拼接在一起，形成一个新的临时拉链表数据。用临时的拉链表覆盖旧的拉链表数据.（这就解决了hive表中数据不能更新的问题）

1、初始化拉链表

-- 拉链表的建表语句
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;

-- 数据加载
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
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

2、生成临时拉链表

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；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3、临时表加载数据

逻辑：
1、先将今天的数据（新增+变化）全部生成拉链表，开始日期为今天，结束日期为’9999-99-99’
2、将与拉链表与今天的数据比较判断出变化的数据，并将结束时间修改
3、将1 union all 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
38
39

4、临时表生成正式拉链表

insert overwrite table dwd_order_info_his 
select * from dwd_order_info_his_tmp;
1
2