Hive拉链表更新

      在数据仓库中，拉链表可以节省存储、方便历史查询等好处。本文章所有内容来自 lxw大数据天地，但是里面的实操部分是本人亲自实验，以深入学习拉链表的用法和体会拉链表的好处。其他详细请到文章来源查看：http://lxw1234.com/search/%E6%8B%89%E9%93%BE%E8%A1%A8

拉链表定义：在数据仓库的数据模型设计过程中，经常会遇到这样的需求

1. 数据量比较大;
2. 表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等;
3. 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，
   比如，查看某一个用户在过去某一段时间内，更新过几次等等;
4. 变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右;
5. 如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费;拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储;

以下介绍拉链表的更新，可和 lxw大数据天地中的数据仓库中历史拉链表的更新方法 文章一起看。

一、数据准备

     准备好order表数据，0820-0823四天的数据，每天都可能有增删改查操作。如果是每天都同步一次，那么每次的同步数据量很大，那么要查询历史数据时，每天或这个月存一份，当order表很大时，那会大大浪费存储。

order_820.txt
1,2015-08-18,2015-08-18,创建
2,2015-08-18,2015-08-18,创建
3,2015-08-19,2015-08-20,支付
4,2015-08-19,2015-08-20,完成
5,2015-08-19,2015-08-20,支付
6,2015-08-20,2015-08-20,创建
7,2015-08-20,2015-08-20,支付

 order_821.txt 
1,2015-08-18,2015-08-18,创建
2,2015-08-18,2015-08-18,创建
3,2015-08-19,2015-08-21,支付
4,2015-08-19,2015-08-21,完成
5,2015-08-19,2015-08-20,支付
6,2015-08-20,2015-08-20,创建
7,2015-08-20,2015-08-21,支付
8,2015-08-21,2015-08-21,创建

order_822.txt 
1,2015-08-18,2015-08-22,支付
2,2015-08-18,2015-08-22,完成
3,2015-08-19,2015-08-21,支付
4,2015-08-19,2015-08-21,完成
5,2015-08-19,2015-08-20,支付
6,2015-08-20,2015-08-22,支付
7,2015-08-20,2015-08-21,支付
8,2015-08-21,2015-08-22,支付
9,2015-08-22,2015-08-22,创建
10,2015-08-22,2015-08-22,支付

order_823.txt 
1,2015-08-18,2015-08-23,完成
2,2015-08-18,2015-08-22,完成
3,2015-08-19,2015-08-23,完成
4,2015-08-19,2015-08-21,完成
5,2015-08-19,2015-08-23,完成
6,2015-08-20,2015-08-22,支付
7,2015-08-20,2015-08-21,支付
8,2015-08-21,2015-08-23,完成
9,2015-08-22,2015-08-22,创建
10,2015-08-22,2015-08-22,支付
11,2015-08-23,2015-08-23,创建
12,2015-08-23,2015-08-23,创建
13,2015-08-23,2015-08-23,支付

二、创表

     创建表语句，这里测试，我把三个表放在了同一个库ods中，在实际环境中，按照数据仓库分层理论，应该把t_dw_orders_his表放在dw所在库。
      订单表是业务表，是业务库的表，也是我们要操作拉链表的目标表；t_ods_orders_inc是一个分区表，以天为分区键，存储每天的增删改数据(随说这个也有每天的历史数据，但是查询某个订单时不方便)；t_dw_orders_his就是拉链表，也有存储order的所有数据，还增加了两个字段，关于这两个字段的作用，下面用到时自然知道其作用。
      关于数据更新，源文章http://lxw1234.com/archives/2015/08/473.htm 也作了如下介绍：

• 数据仓库中订单历史表的刷新频率为一天，当天更新前一天的增量数据；
• 如果一个订单在一天内有多次状态变化，则只会记录最后一个状态的历史；
• 订单状态包括三个：创建、支付、完成；
• 创建时间和修改时间只取到天，如果源订单表中没有状态修改时间，那么抽取增量就比较麻烦，需要有个机制来确保能抽取到每天的增量数据；
• 本文中的表和SQL都使用Hive的HQL语法。

-- 订单号 orderid，数据创建时间 createtime，数据修改时间 modifiedtime，订单状态 status
create table ods.orders(orderid int,createtime string,modifiedtime string,status string) 
	row format delimited fields terminated by ',' 
	stored as textfile;

-- 以下会把表称为ods库的
create table ods.t_ods_orders_inc(orderid int,createtime string,modifiedtime string,status string) 
	partitioned by (day string) 
	row format delimited fields terminated by ',' 
	stored as textfile;

-- 以下会把表称为dw库的
create table ods.t_dw_orders_his(orderid int,createtime string,modifiedtime string,status string,dw_start_date string,dw_end_date string)  
	row format delimited fields terminated by ',' 
	stored as textfile;

三、实际操作

1. 首先把0820的数据放进订单表，假设现在就是8月20日，到今天为止的所有订单数据都已在order表中了

load data local inpath '/home/gwnet/order_820.txt' into table orders;

2. 8月21日凌晨零点，开始更新

--------2015-08-21操作，把昨天之前的所有数据放进历史表---------------
insert overwrite table t_ods_orders_inc partition (day='2015-08-20') select orderid,createtime,modifiedtime,status from orders where createtime<='2015-08-20';

insert overwrite table t_dw_orders_his select orderid,createtime,modifiedtime,status,createtime as dw_start_date,'9999-12-31' as dw_end_date from t_ods_orders_inc where day='2015-08-20';

3. 接着，8月21日一整天，订单表又有数据更新

--------2015-08-21 这一天更新了数据---------------
load data local inpath '/home/gwnet/order_821.txt' overwrite into table orders;

4. 8月22日零点，拉链表开始更新
   这里要介绍一下拉链表新增的两个字段的意思了，免得看更新的hql时不清楚(原文没有说明这两个字段，是我自己推测的意思)：
   dw_start_date：是该条数据入库时间(对于最新的数据而言，就是order表的modifiedtime，对于之前的数据就是第一次进入dw库的时间)
   dw_end_date ：是该数据最新状态的时间，9999-12-31表示最新状态

---------------2015-08-22 00:00:01更新ods表 ---------
insert overwrite table t_ods_orders_inc partition (day='2015-08-21') select orderid,createtime,modifiedtime,status from orders where createtime='2015-08-21' or modifiedtime='2015-08-21';

-- t_dw_orders_his 表要做两件事，21号增加和修改的数据要直接放进来，并且 t_dw_orders_his 表原来相同id的数据的最新一条的dw_end_date要改为前天的时间
-- 第一个子查询的是 把dw的历史数据和ods 22号分区的数据左关联，根据orderid关联，只要能关联上的说明这条数据修改过，把dw_end_date修改
-- 第二个子查询是21号分区的数据，因为是最新的数据，所以把dw_end_date改为9999-12-31后，直接放到 t_dw_orders_his表中
insert overwrite table t_dw_orders_his
select orderid,createtime,modifiedtime,status,dw_start_date,dw_end_date from 
	(select a.orderid,a.createtime,a.modifiedtime,a.status,a.dw_start_date,case when b.orderid is not null and a.dw_end_date>'2015-08-21' then '2015-08-20' else a.dw_end_date end as dw_end_date from t_dw_orders_his a 
		left outer join (select * from t_ods_orders_inc where day='2015-08-21') b 
		on (a.orderid=b.orderid)
	 union all select orderid,createtime,modifiedtime,status,modifiedtime as dw_start_date,'9999-12-31' as dw_end_date from t_ods_orders_inc where day='2015-08-21') x order by orderid,dw_start_date;

6. 8月22日，这一天order表又更新了数据

---------2015-08-22更新了数据--------------------------	
load data local inpath '/home/gwnet/order_822.txt' overwrite into table orders;

7. 8月23日零点，更新拉链表

----------------2015-08-23 00:00:01更新历史表-----------
insert overwrite table t_ods_orders_inc partition (day='2015-08-22') select orderid,createtime,modifiedtime,status from orders where createtime='2015-08-22' or modifiedtime='2015-08-22';

insert overwrite table t_dw_orders_his
	select orderid,createtime,modifiedtime,status,dw_start_date,dw_end_date from 
	(select a.orderid,a.createtime,a.modifiedtime,a.status,a.dw_start_date,case when b.orderid is not null and a.dw_end_date>'2015-08-22' then '2015-08-21' else a.dw_end_date end as dw_end_date from t_dw_orders_his a 
		left outer join (select * from t_ods_orders_inc where day='2015-08-22') b 
		on (a.orderid=b.orderid)
	 union all select orderid,createtime,modifiedtime,status,modifiedtime as dw_start_date,'9999-12-31' as dw_end_date from t_ods_orders_inc where day='2015-08-22') x order by orderid,dw_start_date;

接下来的操作就是上面的循环了，可以设置定时任务定时更新拉链表。

四、拉链表的使用

1. 查询某一天的历史数据

-- 这里查询8月21号的所有历史数据
select * from t_dw_orders_his where dw_start_date <= '2015-08-21' and dw_end_date >= '2015-08-21';

2. 查询某个订单的所有状态变化

-- 这里查询订单号为6的订单所有状态变化
select * from t_dw_orders_his where orderid=6;

3. 查询所有订单的最新状态

select * from t_dw_orders_his where dw_end_date = '9999-12-31';