拉链表的设计-订单

数仓之-拉链表的设计

拉链表设计图：

1、创建数据库

create database ods;
create database dwd;

2、在ods层中创建订单表

2.1、创建ods的订单表初始表(相当于是从mysql出来数据的，懒得去走mysql)

create table ods_order_init(
`id` string COMMENT '订单编号',
`order_status` string COMMENT '订单状态',
`user_id` string COMMENT '用户id',
`create_time` string COMMENT '创建时间',
`operate_time` string COMMENT '操作时间'
) COMMENT '订单表'

2.2、新增测试数据

insert into ods_order_init
(`id`,`order_status`,`user_id`,`create_time`,`operate_time`)
values
('1001','未支付','1001','2019-04-15','2019-04-15');

insert into ods_order_init
(`id`,`order_status`,`user_id`,`create_time`,`operate_time`)
values
('1002','未支付','1002','2019-04-15','2019-04-15');


insert into ods_order_init
(`id`,`order_status`,`user_id`,`create_time`,`operate_time`)
values
('1003','未支付','1003','2019-04-15','2019-04-15');

insert into ods_order_init
(`id`,`order_status`,`user_id`,`create_time`,`operate_time`)
values
('1001','支付成功','1001','2019-04-16','2019-04-16');

insert into ods_order_init
(`id`,`order_status`,`user_id`,`create_time`,`operate_time`)
values
('1002','支付成功','1002','2019-04-16','2019-04-16');

2.3、将ods层的init表数据插入到ods中的ods_order表

insert into table ods.ods_order
partition(dt = '2019-04-15')
select * from ods.ods_order_init where ods_order_init.operate_time='2019-04-15';

2.4、在dwd层创建dwd_order_info

create table dwd_order_info (
`id` string COMMENT '',
`order_status` string COMMENT ' 1 2  3  4  5',
`user_id` string COMMENT '用户id',
`create_time` string COMMENT '',
`operate_time` string COMMENT ''
) COMMENT '订单信息'
PARTITIONED BY ( `dt` string);

2.5、在dwd层创建订单拉链表

create table dwd_order_his(
`id` string COMMENT '订单编号',
`order_status` string COMMENT '订单状态',
`user_id` string COMMENT '用户id',
`create_time` string COMMENT '创建时间',
`operate_time` string COMMENT '操作时间',
`start_date` string COMMENT '有效开始日期',
`end_date` string COMMENT '有效结束日期'
) COMMENT '订单拉链表';

2.6、在dwd层创建订单临时表

create table dwd_order_tmp(
`id` string COMMENT '订单编号',
`order_status` string COMMENT '订单状态',
`user_id` string COMMENT '用户id' ,
`create_time` string COMMENT '创建时间',
`operate_time` string COMMENT '操作时间',
`start_end` string COMMENT '有效开始日期',
`end_date` string COMMENT '有效结束日期'
) COMMENT '订单拉链临时表';

2.7、初始化订单拉链表(也就是将订单表的数据全量导入)

insert overwrite table dwd.dwd_order_his
select
`id`,
`order_status`,
`user_id`,
`create_time`,
`operate_time`,
'2019-04-15' ,
'9999-99-99'
from ods.ods_order;

2.8、再次增加ods层数据(相当于是第二天的表数据)

insert into ods_order_init
(`id`,`order_status`,`user_id`,`create_time`,`operate_time`)
values
('1001','支付成功','1001','2019-04-16','2019-04-16');

insert into ods_order_init
(`id`,`order_status`,`user_id`,`create_time`,`operate_time`)
values
('1002','支付成功','1002','2019-04-16','2019-04-16');

2.9、然后将ods层数据同步给ods_order

insert into table ods.ods_order
partition(dt = '2019-04-16')
select * from ods.ods_order_init where ods_order_init.operate_time='2019-04-16';

2.10、之后将ods_order同步给我们的dwd_order_info(动态分区的形式插入数据)

set hive.exec.dynamic.partition.mode=nonstrict;
insert into table dwd.dwd_order_info
partition(dt)
select * from ods.ods_order o where o.dt='2019-04-16';

2.11、到了每天的时间，我们需要将dwd_order_info里的新增数据同步给我们的订单历史表

insert overwrite table dwd_order_tmp
select * from
(
select
id,
order_status ,
user_id,
create_time ,
operate_time ,
'2019-04-16' start_date,
'9999-99-99' end_date
from dwd.dwd_order_info where dt='2019-04-16'

union all

select
oh.id,
oh.order_status ,
oh.user_id,
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.dwd_order_his oh

left join

(select * from dwd_order_info where dt='2019-04-16') oi

on oh.id=oi.id and oh.end_date='9999-99-99'

)his

order by his.id,start_date

2.12、最后，将我们的数据同步回去

insert overwrite table dwd_order_his
select * from dwd_order_tmp;

2.13、查询我们拉链表的数据

通过 某个日期>=生效开始日期 且 某个日期<=生效结束日期
能够得到某个时间点的数据全量切片。

比如
select * from order_info where start_date<=’2019-01-01’ and end_date>=’2019-01-01’
就会得到 2019-01-01的历史切片

2.14、最终的拉链表样子