hive 中的拉链表 2

发布时间：2020-07-16 20:24:00 作者：jackwxh
来源：网络阅读：1461

本例以hive为例，只考虑到实现，与性能无关

首先创建表

CREATE TABLE orders (

orderid INT,

createtime STRING,

modifiedtime STRING,

status STRING

) row format delimited fields terminated by '\t'

CREATE TABLE ods_orders_inc (

orderid INT,

createtime STRING,

modifiedtime STRING,

status STRING

) PARTITIONED BY (day STRING)

row format delimited fields terminated by '\t'

CREATE TABLE 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' ;

首先全量更新，我们先到2016-08-20为止的数据。

初始化，先把2016-08-20的数据初始化进去

INSERT overwrite TABLE ods_orders_inc PARTITION (day = '2016-08-20')

SELECT orderid,createtime,modifiedtime,status

FROM orders

WHERE createtime < '2016-08-21' and modifiedtime <'2016-08-21';

刷到dw中

INSERT overwrite TABLE dw_orders_his

SELECT orderid,createtime,modifiedtime,status,

createtime AS dw_start_date,

'9999-12-31' AS dw_end_date

FROM ods_orders_inc

WHERE day = '2016-08-20';

如下结果

select * from dw_orders_his;

OK

1 2016-08-20 2016-08-20 创建 2016-08-20 9999-12-31

2 2016-08-20 2016-08-20 创建 2016-08-20 9999-12-31

3 2016-08-20 2016-08-20 创建 2016-08-20 9999-12-31

相关阅读