拉链表实现过程

原文：(77条消息) 什么是拉链表，并通过hive进行实现_大数据开发者Cd的博客-CSDN博客_hive拉链表脚本

@大数据开发者Cd

环境：Linux-CentOS7单机部署（windos虚拟机）
启动hadoop

启动hive

一、准备一份测试数据（原始表），并导入hive数据库

1.准备数据有两种方法：

1）直接在linux目录下创建文件：

cd /home/atstudy/data
vim test_data.txt

然后在文件中插入样例数据：

1，2021-11-20，2021-11-20，新建
2，2021-11-20，2021-11-20，新建
3，2021-11-20，2021-11-20，新建

2）在windows 的虚拟机共享文件夹下新建一个文本文档，在文档里加入样例数据。

2.将测试数据导入hive数据库中：

登录hive后在本地导入：

load data local inpath '/home/atstudy/data/test_data.txt' into table testdb.orders;

插入后查看orders表

hive> select * from orders;
OK
1	2021-11-20	2021-11-20	新建
2	2021-11-20	2021-11-20	新建
3	2021-11-20	2021-11-20	新建
Time taken: 6.287 seconds, Fetched: 3 row(s)

这是日期为2021-11-20的原始表

二、建立更新表、拉链表

1.更新表要建成分区表，这样在数据更新时，只需按天分区覆盖插入当天最新的数据时，就不会把以前的数据覆盖

hive> create table orders_up(
    > id int,
    > createtime string,
    > modifytime string,
    > status string
    > )
    > partitioned by (day string)
    > row format delimited fields terminated by ',';
OK
Time taken: 27.884 seconds

查看表结构

hive> desc orders;
OK
order_id            	int                 	                    
createtime          	string              	                    
modifytime          	string              	                    
status              	string              	                    
Time taken: 0.568 seconds, Fetched: 4 row(s)
hive> desc orders_up;
OK
id                  	int                 	                    
createtime          	string              	                    
modifytime          	string              	                    
status              	string              	                    
day                 	string      

 将当日orders 的数据更新到更新表中

hive> insert overwrite table orders_up partition(day='2021-11-20')
    > select order_id, createtime, modifytime, status from orders;

hive> select * from orders_up;
OK
1	2021-11-20	2021-11-20	新建	2021-11-20
2	2021-11-20	2021-11-20	新建	2021-11-20
3	2021-11-20	2021-11-20	新建	2021-11-20
Time taken: 1.617 seconds, Fetched: 3 row(s)

2.建立拉链表

 拉链表，顾名思义就是要在订单更新时，对旧状态做关链操作，对新状态做开链操作，就是对不同状态的变化做时间记录，记录每个状态的开始时间和终止时间，所以建拉链表要新增2个字段：start_time,end_time

hive> create table zipper_bgn(
    > id int,
    > createtime string,
    > modifytime string,
    > status string,
    > start_time string,
    > end_time string
    > )
    > row format delimited fields terminated by ',';
OK
Time taken: 1.504 seconds
hive> desc zipper_bgn;
OK
id                  	int                 	                    
createtime          	string              	                    
modifytime          	string              	                    
status              	string              	                    
start_time          	string              	                    
end_time            	string              	                    
Time taken: 0.868 seconds, Fetched: 6 row(s)

拉链表初始化（为更新表里的数据赋予拉链操作）

hive> insert overwrite table zipper_bgn
    > select id,createtime, modifytime, status, modifytime, '9999-12-31' from orders_up;

hive> select * from zipper_bgn;
OK
1	2021-11-20	2021-11-20	新建	2021-11-20	9999-12-31
2	2021-11-20	2021-11-20	新建	2021-11-20	9999-12-31
3	2021-11-20	2021-11-20	新建	2021-11-20	9999-12-31
Time taken: 0.672 seconds, Fetched: 3 row(s)

注：

1）插入时，要选择更新表的modifytime字段而不是createtime作为拉链表的start_time字段，因为订单的新状态的开始是以旧状态的修改时间为准，而不是旧状态的创建时间

 2）end_time为max值，其值可以设为’9999-12-31‘

三、更新操作

当时间来到2021-11-21，原始表的数据发生修改和新增：
1,2021-11-20,2021-11-21,支付
2,2021-11-20,2021-11-21,支付
4,2021-11-21,2021-11-21,新建

打开共享文件夹，建立新文本插入新数据，再将文本导入原始表

-- 将共享文件夹下的文本复制到存储数据的目录下
cp /mnt/hgfs/Linux-Windows/orders_inc.txt /home/atstudy/data 

hive> load data local inpath '/home/atstudy/data/orders_inc.txt' into table testdb.orders;
Loading data to table testdb.orders
OK
Time taken: 2.289 seconds
hive> select * from orders;
OK
1	2021-11-20	2021-11-21	支付
2	2021-11-20	2021-11-21	支付
4	2021-11-21	2021-11-21	新建
1	2021-11-20	2021-11-20	新建
2	2021-11-20	2021-11-20	新建
3	2021-11-20	2021-11-20	新建
Time taken: 0.951 seconds, Fetched: 6 row(s)

将原始表中在2021-11-21的更新数据更新到更新表中（orders_up）

hive> insert overwrite table orders_up partition(day='2021-11-21')
    > select * from orders where(createtime='2021-11-21' and modifytime='2021-11-21') or modifytime='2021-11-21';

hive> select * from orders_up;
OK
1	2021-11-20	2021-11-20	新建	2021-11-20
2	2021-11-20	2021-11-20	新建	2021-11-20
3	2021-11-20	2021-11-20	新建	2021-11-20
1	2021-11-20	2021-11-21	支付	2021-11-21
2	2021-11-20	2021-11-21	支付	2021-11-21
4	2021-11-21	2021-11-21	新建	2021-11-21
Time taken: 4.008 seconds, Fetched: 6 row(s)

现在需要对拉链表做更新操作：2021-11-21为当前日

将拉链表和更新表2021-11-21的分区数据左连接，这样产生的结果集中，拉链表有修改状态的订单就会一一在更新表有id对应，没有修改的订单在同行的更新表id就为null，当前日有新增的订单就直接插入到拉链表中，而有修改的订单，则依据更新表id是否为null，修改拉链表的订单end_time段。（旧状态订单沿用旧的start_time,修改end_time）

再将修改完状态的结果集（使用case语句）和 更新表合并union all（先将更新表2021-11-21的数据赋予拉链操作再合并，如二.2步骤），从而达到更新拉链表的操作。（新状态订单使用modifytime作为start_time，使用最大值9999-12-31为end_time）

效果如下：

代码如下：创建拉链表的更新操作表zipper_new,将操作的结果集存储到新表中 

hive> create table zipper_new as select * from (select z.id,z.createtime,z.modifytime,z.status,z.start_time,case when p.id is not null and z.end_time >'2021-11-21' then '2021-11-21' else z.end_time end as end_time from zipper_bgn z left join (select * from orders_up where day = '2021-11-21') p on z.id =p.id union all select id,createtime,modifytime,status,modifytime as start_time,'9999-12-31' as end_time from orders_up where day= '2021-11-21') b order by id,start_time;

hive> select * from zipper_new;
OK
1	2021-11-20	2021-11-20	新建	2021-11-20	2021-11-21
1	2021-11-20	2021-11-21	支付	2021-11-21	9999-12-31
2	2021-11-20	2021-11-20	新建	2021-11-20	2021-11-21
2	2021-11-20	2021-11-21	支付	2021-11-21	9999-12-31
3	2021-11-20	2021-11-20	新建	2021-11-20	9999-12-31
4	2021-11-21	2021-11-21	新建	2021-11-21	9999-12-31
Time taken: 4.784 seconds, Fetched: 6 row(s)