hive拉链表详解

新增及变化数据

如下图所示，2020-01-01为初始化数据，在2020-01-02的时候jacky约增加了500，数据库里又新增了lucy这个新用户。对于数据仓库来说jacky就是变化的数据，lucy就是新增的数据。

如何将新增以及变化的数据导入数据仓库

如果在不占用过多空间的情况下，又能在保留历史数据的同时，体现最新的状态，拉链表是一个不错的选择

什么是拉链表

如下图所示，前一日全量数据和当日新增以及变化的数据合并形成一张新的全量表，就像是拉链的形状。

如何实现

如下图所示，user_info从ods层进入dwd层的时候增加两个字段，start_date为生效日期，end_date为失效日期，当end_date为9999-12-31的时候表示为最新数据

应用场景

一般用户表或者订单表使用拉链表的情况比较多，根据end_date可以追溯每个用户信息或者订单状态的修改状况

实例应用

创建三个 用户表

--ods层用户表
CREATE external TABLE `user.ods_user_info` (
  `id` bigint  COMMENT '主键',
    `name` string COMMENT '名字',
     `banlance` double COMMENT '余额',
  `create_time` string COMMENT '创建时间',
  `modify_time` string  COMMENT '更新时间'
)partitioned by (`dt` string)
row format delimited fields terminated by '\t';



--dwd层用户拉链临时表
CREATE external TABLE `user.dwd_dim_user_info_tmp` (
  `id` bigint  COMMENT '主键',
  `name` string COMMENT '名字',
   `banlance` double COMMENT '余额',
  `create_time` string COMMENT '创建时间',
  `modify_time` string  COMMENT '更新时间',
  `start_date` string COMMENT '开始时间',
  `end_date` string COMMENT '结束时间'
)
STORED AS ORC
TBLPROPERTIES ("orc.compress"="LZO");



--dwd层用户拉链表
CREATE external TABLE `user.dwd_dim_user_info` (
  `id` bigint  COMMENT '主键',
  `name` string COMMENT '名字',
   `banlance` double COMMENT '余额',
  `create_time` string COMMENT '创建时间',
  `modify_time` string  COMMENT '更新时间',
  `start_date` string COMMENT '开始时间',
  `end_date` string COMMENT '结束时间'
)
STORED AS ORC
TBLPROPERTIES ("orc.compress"="LZO");

数据

2020-01-01.txt
1    tom    1000    2020-01-01    
2    jacky    1000    2020-01-01    

2020-01-02.txt
2    jacky    1500    2020-01-01    2020-01-02
3    lucy    3000    2020-01-02    

#装载数据第一天数据
hive -e "load data inpath '/input/user/2020-01-01.txt' into table user.ods_user_info partition(dt='2020-01-01');"

#初始化拉链表
hive -e "insert into user.dwd_dim_user_info select id,name,banlance,create_time,modify_time,'2020-01-01','9999-12-31' from user.ods_user_info;"

hive -e "select * from user.ods_user_info"

#装载数据第二天数据
hive -e "load data inpath '/input/user/2020-01-02.txt' into table user.ods_user_info partition(dt='2020-01-02');"

#将旧数据和新增变化数据插入临时拉链表
hive -e "insert overwrite table user.dwd_dim_user_info_tmp
select 
    a.id,a.name,a.banlance,a.create_time,a.modify_time,a.start_date,
    if(b.id is not null and a.end_date='9999-12-31',date_sub('2020-01-02',1),a.end_date)
from
    user.dwd_dim_user_info a
left join
    (select * from `user.ods_user_info` where dt='2020-01-02') b
on 
    a.id=b.id
union all
select id,name,banlance,create_time,modify_time,'2020-01-02','9999-12-31' from user.ods_user_info where dt='2020-01-02'"

hive -e "insert overwrite table user.dwd_dim_user_info select * from user.dwd_dim_user_info_tmp"

hive -e "select * from user.dwd_dim_user_info"