大数据技术之Hive（五）拉链表的设计与实现

一、什么是拉链表

针对订单表、订单商品表，流水表，这些表中的数据是比较多的，如果使用全量的方式，会造成大量的数据冗余，浪费磁盘空间。

所以这种表，一般使用增量的方式，每日采集新增的数据。

在这注意一点：针对订单表，如果单纯的按照订单产生时间增量采集数据，是有问题的，因为用户可能今天下单，明天才支付，但是Hive是不支持数据更新的，这样虽然MySQL中订单的状态改变了，但是Hive中订单的状态还是之前的状态。

想要解决这个问题，一般有这么几种方案：

第一种：
每天全量导入订单表的数据，这种方案在项目启动初期是没有多大问题的，因为前期数据量不大，但是随着项目的运营，订单量暴增，假设每天新增1亿订单，之前已经累积了100亿订单，如果每天都是全量导入的话，那也就意味着每天都需要把数据库中的100多亿订单数据导入到HDFS中保存一份，这样会 极大的造成数据冗余，太浪费磁盘空间了。

第二种：
只保存当天的全量订单表数据，每次在导入之前， 删除前一天保存的全量订单数据，这种方式虽然不会造成数据冗余，但是 无法查询订单的历史状态，只有当前的最新状态，也不太好。

第三种：
拉链表，这种方式在普通增量导入方式的基础之上进行完善， 把变化的数据也导入进来，这样既不会造成大量的数据冗余，还可以查询订单的历史状态。

拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓 拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有历史变化的信息。

下面就是一张拉链表，存储的是用户的最基本信息以及每条记录的生命周期。我们可以使用这张表拿到当天的最新数据以及之前的历史数据。

二、如何制作拉链表

拉链表主要用在dwd层（明细数据层），用来及时记录每个事务状态的。加入ods层（原始数据层）数据发生的新增或者更新，相应的dwd层的数据也会改变。拉链表数据生成的思路是：ods更新或者新增的数据 + union +dwd拉链表历史数据（要更改历史数据中状态发生改变的字段）。

方法有两种： 窗口函数和union all 。

1. 数据初始化

导入数据到一张初始表

create database lalian;
use lalian;

drop table if exists orders;

# 创建外部表，并指定存放目录
create external table orders(
    orderid int,
    createdate string,
    modifiedtime string,
    status string
)
row format delimited fields terminated by '\t'
location '/tmp/lalian/orders';

[root@hadoop02 ~]# hdfs dfs -put /opt/stufile/orders.csv /tmp/lalian/orders

2. 创建ods层

将初始表添加到ods层分区表中，数据日期为2023-3-3

-- 创建增量表ods_orders_inc --
create table ods_orders_inc(
    orderid int,
    createdate string,
    modifiedtime string,
    status string
)partitioned by (day string)
row format delimited fields terminated by '\t';

insert overwrite table ods_orders_inc partition (day='2023-03-03')
select orderid,createdate,modifiedtime,status from orders 
    where modifiedtime='2023-03-03';

3. 创建dwd层拉链表

将ods层数据添加覆盖到dwd层，dwd表增加 start_time 和end_time 两列数据用来记录时间动态。

默认end_time 为时间极限值 '9999-12-31'

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'

insert overwrite table dws_orders_his
select orderid,createdate,modifiedtime,status,modifiedtime,'9999-12-30'
       from ods_orders_inc where day='2023-03-03';

4. 数据更新

--2023-03-04--
-- 创建增量表ods_orders_inc --
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');


-- dws_orders_his --
with t1 as (
    select orderid,createdate,modifiedtime,status,modifiedtime as start_time,'9999-12-30' as end_time
from ods_orders_inc where day='2023-03-04'
union
select t1.orderid,
       t1.createdate,
       t1.modifiedtime,
       t1.status,
       t1.start_time,
       case when t2.createdate is not null and t1.end_time>'2023-03-04'
           then '2023-03-04' else t1.end_time end end_time
from dws_orders_his t1 left join (
    select orderid,createdate from ods_orders_inc where day='2023-03-04'
    ) t2
on t1.orderid=t2.orderid
)