使用这种方式即可以记录历史,而且最大程度的节省存储。这里简单介绍一下这种历史拉链表的更新方法。
本文中假设:
-
数据仓库中订单历史表的刷新频率为一天,当天更新前一天的增量数据;
-
如果一个订单在一天内有多次状态变化,则只会记录最后一个状态的历史;
-
订单状态包括三个:创建、支付、完成;
-
创建时间和修改时间只取到天,如果源订单表中没有状态修改时间,那么抽取增量就比较麻烦,需要有个机制来确保能抽取到每天的增量数据;
-
本文中的表和SQL都使用Hive的HQL语法;
-
源系统中订单表结构为:
CREATE TABLE orders (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) stored AS textfile;
7.在数据仓库的ODS层,有一张订单的增量数据表,按天分区,存放每天的增量数据:
CREATE TABLE t_ods_orders_inc (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) PARTITIONED BY (day STRING)
stored AS textfile;
8. 在数据仓库的DW层,有一张订单的历史数据拉链表,存放订单的历史状态数据:
CREATE TABLE t_dw_orders_his (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING,
dw_start_date STRING,
dw_end_date STRING
) stored AS textfile;
9. 暂未考虑Hive上表的查询性能问题,只实现功能;
华丽的分割线:您可以关注 lxw的大数据田地 ,或者 加入邮件列表 ,随时接收博客更新的通知邮件。
10. 2015-08-21至2015-08-23,每天原系统订单表的数据如下,红色标出的为当天发生变化的订单,即增量数据:
全量初始化
在数据从源业务系统每天正常抽取和刷新到DW订单历史表之前,需要做一次全量的初始化,就是从源订单表中昨天以前的数据全部抽取到ODW,并刷新到DW。
以上面的数据为例,比如在2015-08-21这天做全量初始化,那么我需要将包括2015-08-20之前的所有的数据都抽取并刷新到DW:
第一步,抽取全量数据到ODS:
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = ‘2015-08-20′)
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime <= ‘2015-08-20′;
第二步,从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′;
完成后,DW订单历史表中数据如下:
-
spark-sql> select * from t_dw_orders_his;
-
1 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31
-
2 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31
-
3 2015-08-19 2015-08-21 支付 2015-08-19 9999-12-31
-
4 2015-08-19 2015-08-21 完成 2015-08-19 9999-12-31
-
5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31
-
6 2015-08-20 2015-08-20 创建 2015-08-20 9999-12-31
-
7 2015-08-20 2015-08-21 支付 2015-08-20 9999-12-31
-
Time taken: 2.296 seconds, Fetched 7 row(s)
华丽的分割线:您可以关注 lxw的大数据田地 ,或者 加入邮件列表 ,随时接收博客更新的通知邮件。
增量抽取
每天,从源系统订单表中,将前一天的增量数据抽取到ODS层的增量数据表。
这里的增量需要