HIVE拉链表_拉链表怎么插入

拉链表概念

拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据（SCD2）的一种常见方式。

拉链表实现过程

1、采集当日全量数据到ND(NowDay当日)表；

2、可从历史表中取出昨日全量数据存储到OD(OldDay上日)表；

3、两个表进行全字段比较，(ND-OD)就是当日新增和变化的数据，也就是当天的增量，用W_I表示；

4、两个表进行全字段比较，(OD-ND)为状态到此结束需要封链的数据，需要修改END_DATE，用W_U表示；

5、将W_I表的内容全部插入到历史表中，这些是新增记录，start_date为当天，而end_date为max值，可以设为’9999-12-31‘；

6、对历史表进行W_U部份的更新操作，start_date保持不变，而end_date改为当天，也就是关链操作，历史表（OD）和W_U表比较，START_DATE，END_DATE除外，以W_U表为准，两者交集将其END_DATE改成当日，说明该记录失效。

拉链表举例说明

3月3日当天三条订单记录

3月4日当天3条订单记录

3月5日当天3条订单记录

将订单记录文件上传到HDFS文件目录
创建对应数据库和原始ODS表

create database lalian;
use lalian;
create external table  orders(
    orderid int,
    createdate string,
    modifiedtime string,
    status string
)
row format delimited fields terminated by '\t'
location '/tmp/lalian/orders';
1
2
3
4
5
6
7
8
9
10

创建按天分区的ODS表

create  table  ods_orders_inc(
    orderid int,
    createdate string,
    modifiedtime string,
    status string
)partitioned by (day string)
row format delimited fields terminated by '\t';
1
2
3
4
5
6
7

按分区插入数据

//3月3号数据
insert overwrite table ods_orders_inc partition (day='2023-03-03')
select orderid,createdate,modifiedtime,status from orders;
//3月4号数据
insert overwrite table ods_orders_inc partition (day='2023-03-04')
select orderid,createdate,modifiedtime,status from orders
where modifiedtime='2023-03-04';
//3月号数据
insert overwrite table  ods_orders_inc partition (day='2023-03-05')
select orderid,createdate,modifiedtime,status from orders
where modifiedtime='2023-03-05' or (createdate='2023-03-05' and modifiedtime='2023-03-05');
1
2
3
4
5
6
7
8
9
10
11

创建dws拉链表

create table dws_orders_his(
    orderid int,
    createdate string,
    modifiedtime string,
    status string,
    start_time string,
    end_time string
)
row format delimited fields terminated by '\t';
1
2
3
4
5
6
7
8
9

将每日数据进行处理后插入

//处理并插入3月3日数据
insert overwrite table dws_orders_his
select orderid,createdate,modifiedtime,status,modifiedtime,'9999-12-31'
from ods_orders_inc where day='2023-03-03';

1
2
3
4
5

3月3日当天拉链表

根据3月4日数据更新当前DWS表状态

insert overwrite table dws_orders_his
select
t1.orderid,
       createdate,
       modifiedtime ,
       status,
       start_time,
       case when t2.orderid is not null and end_time>'2023-03-04'then '2023-03-04' else end_time end end_time
from dws_orders_his t1 left join
    (select orderid from ods_orders_inc where day='2023-03-04')t2
on t1.orderid=t2.orderid
union all
select orderid,createdate,modifiedtime,status,modifiedtime as start_time,'9999-12-31' as end_time from ods_orders_inc
where day='2023-03-04';
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3月4日当天拉链表

根据3月5日数据更新当前DWS表状态

insert overwrite table dws_orders_his
select tb1.* from (
                  (select orderid,
                          createdate,
                          modifiedtime,
                          status,
                          modifiedtime as start_time,
                          '9999-12-31' as end_time
                   from ods_orders_inc
                   where day = '2023-03-05')
                  union all
                  (select t1.orderid,
                          createdate,
                          t1.modifiedtime,
                          status,
                          t1.start_time,
                          if(t2.orderid is not null and t1.end_time > '2023-03-05', '2023-03-05', t1.end_time) end_time
                   from dws_orders_his t1
                            left join (select orderid, modifiedtime
                                       from ods_orders_inc
                                       where day = '2023-03-05') t2 on t1.orderid = t2.orderid)
              )tb1 order by tb1.orderid,tb1.modifiedtime;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

3月5日当天拉链表

依次更新每日拉链信息保证拉链表的更新

拉链表补充

1. 使用拉链表的时候可以不加t_end_date，即失效日期，但是加上之后，能优化很多查询。
2. 可以加上当前行状态标识，能快速定位到当前状态。
3. 在拉链表的设计中可以加一些内容，因为我们每天保存一个状态，如果我们在这个状态里面加一个字段，比如如当天修改次数，那么拉链表的作用就会更大。