hive中拉链表

既反映了数据历史状态,又比较节省空间

适用于

1. 数据量比较大，而且会不断增长（典型：订单表）
2. 表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等;
3. 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，
   比如，查看某一个用户在过去某一段时间内，更新过几次等等;
4. 变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右;
5. 如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费;

1.业务库

比如订单表中的订单的状态会发生变化,这条数据会update

11号订单表

订单创建日期	订单编号	订单状态
2020-03-11	0001	创建订单
2020-03-11	0002	创建订单

12号订单表

订单创建日期	订单编号	订单状态
2020-03-11	0001	支付完成
2020-03-11	0002	创建订单
2020-03-12	0003	创建订单

2.数据仓库

• 只保留一份全量的话(比如当天是12号),那么想要查看12号之前的订单状态就查不到了.

• 每天都保存一份全量数据,像有好多订单的状态几天都没有变化的数据,每天的全量数据中都有重复记录,会造成很大的存储浪费.

• 做成历史拉链表 保存该表 既反映了数据历史状态,又比较节省空间

2.1历史拉链表

11号的历史拉链表

订单创建日期	订单编号	订单状态	dw_begin_date	dw_end_date
2020-03-11	0001	创建订单	2020-03-11	9999-12-3
2020-03-11	0002	创建订单	2020-03-11	9999-12-3

12号的历史拉链表

订单创建日期	订单编号	订单状态	dw_begin_date	dw_end_date
2020-03-11	0001	创建订单	2020-03-11	2020-03-11
2020-03-11	0001	支付完成	2020-03-12	9999-12-31
2020-03-11	0002	创建订单	2020-03-11	2020-03-11
2020-03-12	0003	创建订单	2020-03-12	9999-12-31

逻辑图

实现

--拉链表
--表名 dwd_yw_zpo_sta a
--字段 create_dt,orderid,state,dw_begin_date,dw_end_date
--数据 2020-03-11 的拉链表数据
2020-03-11,0001,创建订单,2020-03-11,9999-12-31
2020-03-11,0002,创建订单,2020-03-11,9999-12-31
create table dwd_yw_zpo_sta(
create_dt string,  --订单创建时间
orderid string,
state string,
dw_begin_date string, --状态的开始时间
dw_end_date string   --装态的结束时间
)
row format delimited fields terminated by ',';


--增量表
--表名 ods_yw_bko_day b
--字段 create_dt,orderid,state
--数据 2020-03-12
2020-03-11,0001,支付完成
2020-03-12,0003,创建订单
create table ods_yw_bko_day(
create_dt string,
orderid string,
state string
)
partitioned by (dt string)
row format delimited fields terminated by ','
;

insert overwrite table dwd_yw_zpo_sta partition (dt='2020-03-12')
select
if(a.orderid is null,b.create_dt,a.create_dt) as create_dt,
if(a.orderid is null,b.orderid,a.orderid) as orderid,
if(a.orderid=b.orderid,a.state,if(a.orderid is null,b.state,a.state)) as state,
if(a.orderid is null,b.create_dt,a.dw_begin_date) as dw_begin_date,
if(a.orderid=b.orderid,date_sub(b.dt,1),if(a.orderid is null,'9999-12-31',a.dw_end_date)) as dw_end_date
from dwd_yw_zpo_sta a
full join ods_yw_bko_day b
on b.dt='2020-03-12' and a.orderid=b.orderid
union all
select
a.create_dt as create_dt,
a.orderid as orderid,
b.state as state,
b.dt as dw_begin_date,
'9999-12-31' as dw_end_date
from dwd_yw_zpo_sta a
join ods_yw_bko_day b
on a.orderid=b.orderid and b.dt='2020-03-12'
;
+----------------+--------------+------------+--------------------+------------------+--+
| _u1.create_dt  | _u1.orderid  | _u1.state  | _u1.dw_begin_date  | _u1.dw_end_date  |
+----------------+--------------+------------+--------------------+------------------+--+
| 2020-03-11     | 0001         | 创建订单       | 2020-03-11         | 2020-03-11       |
| 2020-03-11     | 0002         | 创建订单       | 2020-03-11         | 9999-12-31       |
| 2020-03-12     | 0003         | 创建订单       | 2020-03-12         | 9999-12-31       |
| 2020-03-11     | 0001         | 支付完成       | 2020-03-12         | 9999-12-31       |
+----------------+--------------+------------+--------------------+------------------+-