拉链表-增量更新方法一
参考文档：http://lxw1234.com/archives/2015/08/473.htm
一、元表结构
1、定义业务库原始订单表：
drop table chavin.orders;
CREATE TABLE orders (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
)row format delimited fields terminated by '\t'
stored AS textfile;
--加载测试数据
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 创建
2、定义ODS订单表结构，采用日分区存储：
drop table t_ods_orders_inc;
CREATE TABLE t_ods_orders_inc (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) PARTITIONED BY (day STRING)
row format delimited fields terminated by '\t'
stored AS textfile;
3、创建dw层历史订单表：
drop table t_dw_orders_his;
CREATE TABLE 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 '\t'
stored AS textfile;
二、初始化dw层历史订单表：
1、将源库订单表历史数据插入到ods订单表中：
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2015-08-20')
SELECT orderid,createtime,modifiedtime,status
FROM chavin.orders
WHERE cast(createtime as date) <= '2015-08-20';
2、通过ods层订单表数据初始化dw层历史订单表：
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';
三、增量添加数据
1、将原始订单表增量数据插入到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';
2、通过dw历史数据和ods增量数据刷新dw历史数据，此处采用临时表方法：
DROP TABLE IF EXISTS t_dw_orders_his_tmp;
CREATE TABLE t_dw_orders_his_tmp AS
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;
3、根据历史表更新dw层历史订单表：
INSERT overwrite TABLE t_dw_orders_his
SELECT * FROM t_dw_orders_his_tmp;
4、根据上面步骤增加22号数据：
--加载增量数据到ods层订单表分区'2015-08-22'中：
1 2015-08-18 2015-08-22 支付
2 2015-08-18 2015-08-22 完成
6 2015-08-20 2015-08-22 支付
9 2015-08-22 2015-08-22 创建
8 2015-08-22 2015-08-22 支付
10 2015-08-22 2015-08-22 支付
alter table t_ods_orders_inc add partition(day='2015-08-22');
load data local inpath '/opt/datas/orders22.txt' into table chavin.t_ods_orders_inc partition(day='2015-08-22');
--根据历史订单数据和增量数据更新历史订单表数据，此处采用临时表：
DROP TABLE IF EXISTS t_dw_orders_his_tmp;
CREATE TABLE t_dw_orders_his_tmp AS
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;
--根据临时表更新历史订单表：
INSERT overwrite TABLE t_dw_orders_his
SELECT * FROM t_dw_orders_his_tmp;
5、查看2015-08-21、2015-08-21历史快照：
select * from t_dw_orders_his where dw_start_date <= '2015-08-21' and dw_end_date >= '2015-08-21';
select * from t_dw_orders_his where dw_start_date <= '2015-08-22' and dw_end_date >= '2015-08-22';
