1、为什么要做拉链表
拉链表,记录每条信息的生命周期,一旦一条记录的生命周期结束,就重新开始一条新的记录,并把当前日期放入生效开始日期。
拉链表适合于:数据会发生变化,但是大部分是不变的。
比如:订单信息从未支付、已支付、未发货、已完成等状态经历了一周,大部分时间是不变化的。
拉链表格式
订单ID | 订单金额 | 订单状态 | 生效开始日期 | 生效结束日期 |
---|---|---|---|---|
1 | 1000.00 | 未支付 | 2019-01-01 | 2019-01-01 |
1 | 1000.00 | 已支付 | 2019-01-02 | 2019-01-02 |
1 | 1000.00 | 未发货 | 2019-01-03 | 2019-12-11 |
1 | 1000.00 | 已完成 | 2019-12-12 | 9999-99-99 |
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';
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;
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;
4、临时表生成正式拉链表
insert overwrite table dwd_order_info_his
select * from dwd_order_info_his_tmp;