微信搜索lxw1234bigdata | 邀请体验:数阅–数据管理、OLAP分析与可视化平台 | 赞助作者:赞助作者

数据仓库中历史拉链表的更新方法

数据仓库/数据库 lxw1234@qq.com 86708℃ 18评论

关键字:数据仓库、极限存储、历史拉链表、更新

在之前介绍过数据仓库中的历史拉链表《数据仓库数据模型之:极限存储–历史拉链表》,

使用这种方式即可以记录历史,而且最大程度的节省存储。这里简单介绍一下这种历史拉链表的更新方法。

本文中假设:

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

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层的增量数据表。
这里的增量需要通过订单表中的创建时间和修改时间来确定:
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = ‘${day}‘)
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime = ‘${day}’ OR modifiedtime = ‘${day}';

注意:在ODS层按天分区的增量表,最好保留一段时间的数据,比如半年,为了防止某一天的数据有问题而回滚重做数据。

增量刷新历史数据

从2015-08-22开始,需要每天正常刷新前一天(2015-08-21)的增量数据到历史表。

第一步,通过增量抽取,将2015-08-21的数据抽取到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′;

ODS增量表中2015-08-21的数据如下:

spark-sql> select * from t_ods_orders_inc where day = '2015-08-21';
3       2015-08-19      2015-08-21      支付    2015-08-21
4       2015-08-19      2015-08-21      完成    2015-08-21
7       2015-08-20      2015-08-21      支付    2015-08-21
8       2015-08-21      2015-08-21      创建    2015-08-21
Time taken: 0.437 seconds, Fetched 4 row(s)

第二步,通过DW历史数据(数据日期为2015-08-20),和ODS增量数据(2015-08-21),刷新历史表:

先把数据放到一张临时表中:

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;

其中:
UNION ALL的两个结果集中,第一个是用历史表left outer join 日期为 ${yyy-MM-dd} 的增量,能关联上的,并且dw_end_date > ${yyy-MM-dd},说明状态有变化,则把原来的dw_end_date置为(${yyy-MM-dd} – 1), 关联不上的,说明状态无变化,dw_end_date无变化。
第二个结果集是直接将增量数据插入历史表。

最后把临时表中数据插入历史表:
INSERT overwrite TABLE t_dw_orders_his
SELECT * FROM t_dw_orders_his_tmp;

华丽的分割线:您可以关注 lxw的大数据田地 ,或者 加入邮件列表 ,随时接收博客更新的通知邮件。

 

刷新完后,历史表中数据如下:

spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
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      2015-08-20
3       2015-08-19      2015-08-21      支付    2015-08-21      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-19      2015-08-20
4       2015-08-19      2015-08-21      完成    2015-08-21      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      2015-08-20
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      9999-12-31
Time taken: 0.717 seconds, Fetched 11 row(s)

由于在2015-08-21做了8月20日以前的数据全量初始化,而订单3、4、7在2015-08-21的增量数据中也存在,因此都有两条记录,但不影响后面的查询。

再看将2015-08-22的增量数据刷新到历史表:

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

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;

刷新完后历史表数据如下:

spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
1       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
1       2015-08-18      2015-08-22      支付    2015-08-22      9999-12-31
2       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
2       2015-08-18      2015-08-22      完成    2015-08-22      9999-12-31
3       2015-08-19      2015-08-21      支付    2015-08-19      2015-08-20
3       2015-08-19      2015-08-21      支付    2015-08-21      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-19      2015-08-20
4       2015-08-19      2015-08-21      完成    2015-08-21      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      2015-08-21
6       2015-08-20      2015-08-22      支付    2015-08-22      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-20      2015-08-20
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      2015-08-21
8       2015-08-21      2015-08-22      支付    2015-08-22      9999-12-31
9       2015-08-22      2015-08-22      创建    2015-08-22      9999-12-31
10      2015-08-22      2015-08-22      支付    2015-08-22      9999-12-31
Time taken: 0.66 seconds, Fetched 17 row(s)

华丽的分割线:您可以关注 lxw的大数据田地 ,或者 加入邮件列表 ,随时接收博客更新的通知邮件。

 

查看2015-08-21的历史快照数据:

spark-sql> select * from t_dw_orders_his where dw_start_date <= '2015-08-21' and dw_end_date >= '2015-08-21';
1       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
2       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
3       2015-08-19      2015-08-21      支付    2015-08-21      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-21      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      2015-08-21
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      2015-08-21

订单1在2015-08-21的时候还处于创建的状态,在2015-08-22的时候状态变为支付。

再刷新2015-08-23的增量数据:

按照上面的方法刷新完后,历史表数据如下:

spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
1       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
1       2015-08-18      2015-08-22      支付    2015-08-22      2015-08-22
1       2015-08-18      2015-08-23      完成    2015-08-23      9999-12-31
2       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
2       2015-08-18      2015-08-22      完成    2015-08-22      9999-12-31
3       2015-08-19      2015-08-21      支付    2015-08-19      2015-08-20
3       2015-08-19      2015-08-21      支付    2015-08-21      2015-08-22
3       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-19      2015-08-20
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      2015-08-22
5       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
6       2015-08-20      2015-08-20      创建    2015-08-20      2015-08-21
6       2015-08-20      2015-08-22      支付    2015-08-22      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-20      2015-08-20
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      2015-08-21
8       2015-08-21      2015-08-22      支付    2015-08-22      2015-08-22
8       2015-08-21      2015-08-23      完成    2015-08-23      9999-12-31
9       2015-08-22      2015-08-22      创建    2015-08-22      9999-12-31
10      2015-08-22      2015-08-22      支付    2015-08-22      9999-12-31
11      2015-08-23      2015-08-23      创建    2015-08-23      9999-12-31
12      2015-08-23      2015-08-23      创建    2015-08-23      9999-12-31
13      2015-08-23      2015-08-23      支付    2015-08-23      9999-12-31

订单1从20号-23号,状态变化了三次,历史表中有三条记录。

//查看2015-08-22当天的历史快照,可以看出,和上面图中2015-08-22时候订单表中的数据是一样的
spark-sql> select * from t_dw_orders_his where dw_start_date <= '2015-08-22' and dw_end_date >= '2015-08-22';
1       2015-08-18      2015-08-22      支付    2015-08-22      2015-08-22
2       2015-08-18      2015-08-22      完成    2015-08-22      9999-12-31
3       2015-08-19      2015-08-21      支付    2015-08-21      2015-08-22
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      2015-08-22
6       2015-08-20      2015-08-22      支付    2015-08-22      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-22      支付    2015-08-22      2015-08-22
9       2015-08-22      2015-08-22      创建    2015-08-22      9999-12-31
10      2015-08-22      2015-08-22      支付    2015-08-22      9999-12-31
Time taken: 0.328 seconds, Fetched 10 row(s)
//查看当前所有订单的最新状态
spark-sql> select * from t_dw_orders_his where dw_end_date = '9999-12-31';                                   
1       2015-08-18      2015-08-23      完成    2015-08-23      9999-12-31
2       2015-08-18      2015-08-22      完成    2015-08-22      9999-12-31
3       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
6       2015-08-20      2015-08-22      支付    2015-08-22      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-23      完成    2015-08-23      9999-12-31
9       2015-08-22      2015-08-22      创建    2015-08-22      9999-12-31
10      2015-08-22      2015-08-22      支付    2015-08-22      9999-12-31
11      2015-08-23      2015-08-23      创建    2015-08-23      9999-12-31
12      2015-08-23      2015-08-23      创建    2015-08-23      9999-12-31
13      2015-08-23      2015-08-23      支付    2015-08-23      9999-12-31
Time taken: 0.293 seconds, Fetched 13 row(s)

实际业务中,有可能某一天的数据有问题,需要回滚或重做,这点有点麻烦,后续文章再介绍。

相关阅读:

数据仓库数据模型之:极限存储–历史拉链表

华丽的分割线:您可以关注 lxw的大数据田地 ,或者 加入邮件列表 ,随时接收博客更新的通知邮件。

 

如果觉得本博客对您有帮助,请 赞助作者

转载请注明:lxw的大数据田地 » 数据仓库中历史拉链表的更新方法

喜欢 (144)
分享 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(18)个小伙伴在吐槽
  1. 文章写的非常的好,我们现在也在做拉链的表,在你的文章中,有几个地方不太明白: 1.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 说明状态有变化,则把原来的dw_end_date置为(${yyy-MM-dd} – 1), 关联不上的,说明状态无变化,dw_end_date无变化。 这个上面的(${yyy-MM-dd} – 1),我认为是在2015-8-22号做的增量数据,${yyy-MM-dd} – 1) 这个dw_end_date的时间应该为2015-8-21日期,为什么你的语句中是2015-8-20? 2.若是源库有删除的记录,在这个拉链表中如何把删除的置为失效状态?
    bingfeng20042016-04-16 17:58 回复
    • 1.${yyyy-MM-dd}这个时间已经是数据日期了,也就是2015-08-21,这些数据都是在2015-08-21产生的增量,发生的变化,因此要把以前的记录结束日期置为${yyyy-MM-dd,-1d}. 2.文章中确实没有考虑有删除记录的情况,一般业务系统肯定不会把记录物理删除,都是通过记录状态或者移动到另外一张表来实现,这样的话,就单独处理一下删除的这部分记录。如果真是物理删除,那就比较麻烦,需要通过DW历史表和业务系统表关联找到删除的记录,当然不能硬关联。
      lxw1234@qq.com2016-04-20 09:48 回复
  2. 你好!如果数据有问题,怎么实现数据重做呢?能讲解一下吗?谢谢!!!
    polo_suns2016-04-28 13:27 回复
  3. 你好,数据的回滚和重做,有什么好的方法吗?我现在的做法是每天拉链前先备份上一次拉链的结果,这种方式比较傻,只能当天发现问题后修复,第二天再看连备份的数据也是错的了。
    lx_sot2016-06-21 14:17 回复
  4. 非常期待 数据回滚 及重做拉链的 文章
    核动力贝贝2016-06-22 18:51 回复
  5. 您好,我看了下代码,发现历史表每次都要全表删除再插入数据,那么如果是几千万的历史表,效率应该会很低对不,导致增量更新很慢。 我觉得才用先update历史表的增量数据,再插入这种方法效率会好些。不知您是否这样觉得? :smile:
    烤年糕2017-04-07 15:15 回复
    • UPDATEL历史表的增量数据?不太理解哎
      沐沐林2017-05-17 19:48 回复
  6. 在ORALCE里面怎么添加这个临时表,
    沐沐林2017-05-17 19:24 回复
    • --拉链表测试 --需要原始表有一个代表记录有更新的时间戳字段 --订单原始表 create table orders ( orderid int, createtime varchar2(10), modifiedtime varchar2(10) , status varchar2(20)); -- 2015-08-21日的订单表 select * from orders ; -- 2015-08-22日的订单表 select * from orders1 ; -- 2015-08-23日的订单表 select * from orders2 ; --订单每日增量更新表 CREATE TABLE t_ods_orders_inc ( orderid INT, createtime varchar2(10), modifiedtime varchar2(10), status varchar2(20) ) ; select * from t_ods_orders_inc ; --订单历史表 CREATE TABLE t_dw_orders_his ( orderid INT, createtime varchar2(10), modifiedtime varchar2(10), status varchar2(20), dw_start_date varchar2(10), dw_end_date varchar2(10) ) ; select * from t_dw_orders_his ; --历史数据临时表 select * from t_dw_orders_his_tmp ; /* 在数据从源业务系统每天正常抽取和刷新到DW订单历史表之前, 需要做一次全量的初始化,就是从源订单表中昨天以前的数据全部抽取到ODW,并刷新到DW。 以上面的数据为例,比如在2015-08-21这天做全量初始化, 那么我需要将包括2015-08-20之前的所有的数据都抽取并刷新到DW: */ truncate table t_ods_orders_inc; select * from t_ods_orders_inc --第一步,抽取全量数据到ODS: INSERT into t_ods_orders_inc SELECT orderid,createtime,modifiedtime,status FROM orders WHERE createtime '2015-08-21' THEN '2015-08-20' ELSE a.dw_end_date END AS dw_end_date FROM t_dw_orders_his a left join t_ods_orders_inc 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 ) x ; commit; select * from t_dw_orders_his_tmp ; /* 其中: UNION ALL的两个结果集中,第一个是用历史表left outer join 日期为 ${yyy-MM-dd} 的增量, 能关联上的,并且dw_end_date > ${yyy-MM-dd},说明状态有变化,则把原来的dw_end_date置为(${yyy-MM-dd} – 1), 关联不上的,说明状态无变化,dw_end_date无变化。 第二个结果集是直接将增量数据插入历史表。*/ --最后把临时表中数据插入历史表: --清空历史表 t_dw_orders_his truncate table t_dw_orders_his ; INSERT into t_dw_orders_his SELECT * FROM t_dw_orders_his_tmp; select * from t_dw_orders_his ; /* 由于在2015-08-21做了8月20日以前的数据全量初始化,而订单3、4、7在2015-08-21的增量数据中也存在, 因此都有两条记录,但不影响后面的查询。 再看将2015-08-22的增量数据刷新到历史表:*/ truncate table t_ods_orders_inc ; INSERT into t_ods_orders_inc SELECT orderid,createtime,modifiedtime,status FROM orders1 --用对应的8月22号的全量订单表,取出新增数据 WHERE createtime = '2015-08-22' OR modifiedtime = '2015-08-22'; commit; -- 刷新到历史临时表: truncate table t_dw_orders_his_tmp ; insert into t_dw_orders_his_tmp 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 join t_ods_orders_inc 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 ) x ; commit; -- 刷新到历史表 truncate table t_dw_orders_his ; INSERT into t_dw_orders_his SELECT * FROM t_dw_orders_his_tmp; commit; select * from t_dw_orders_his ; -- 查看2015-08-21的历史快照数据: select * from t_dw_orders_his where dw_start_date = '2015-08-21'; -- 再刷新2015-08-23的增量数据: truncate table t_ods_orders_inc ; INSERT into t_ods_orders_inc SELECT orderid,createtime,modifiedtime,status FROM orders2 --用对应的8月22号的全量订单表,取出新增数据 WHERE createtime = '2015-08-23' OR modifiedtime = '2015-08-23'; commit; -- 刷新到历史临时表: truncate table t_dw_orders_his_tmp ; insert into t_dw_orders_his_tmp 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-23' THEN '2015-08-22' ELSE a.dw_end_date END AS dw_end_date FROM t_dw_orders_his a left join t_ods_orders_inc 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 ) x ; commit; -- 刷新到历史表 truncate table t_dw_orders_his ; INSERT into t_dw_orders_his SELECT * FROM t_dw_orders_his_tmp; commit; select * from t_dw_orders_his ; --查看2015-08-22当天的历史快照,可以看出,和上面图中2015-08-22时候订单表中的数据是一样的 select * from t_dw_orders_his where dw_start_date = '2015-08-22'; --查看当前所有订单的最新状态 select * from t_dw_orders_his where dw_end_date = '9999-12-31';
      高尔2017-06-22 15:00 回复
  7. 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 AND a.dw_end_date > '2015-08-21' 这个条件好像 :???: 多余了, 因为你的dw_end_date 都是9999-12-31,都大过任何日期值了,肯定是成立的, 从另外一个角度来说, union all 两个集合的时候,能关联上,就已经说明状态发生了变化,直接更改历史表的dw_end_date为T-1,表名该阶段状态生命周期的结束,另外全量插入增量数据。 不知道我理解的对不对,还望海涵, 博客很精致,内容很充实,学些了。
    高尔2017-06-22 14:59 回复
  8. 数据量大,效率太低下
    大大叔叔聚聚2017-09-16 09:56 回复
  9. 您好:请教个问题,假设20号新增一条数据,25号对这条数据进行了更新操作,那么这个时候想要查询21号的数据,根据您提供的sql,select * from t_dw_orders_his where dw_start_date = '2015-08-21'; 这样的结果不对吧? 谢谢
    炼心天毅2017-12-05 10:59 回复
  10. 您好:请教个问题,假设20号新增一条数据,25号对这条数据进行了更新操作,那么这个时候想要查询21号的数据,根据您提供的sql,select * from t_dw_orders_his where dw_start_date = '2015-08-21'; 这样查出的结果不对吧? 谢谢
    炼心天毅2017-12-05 11:01 回复
  11. 不好意思,这个sql select * from t_dw_orders_his where dw_start_date = '2015-08-21';
    炼心天毅2017-12-05 11:03 回复
  12. dw_start_date='2015-08-21' 不知道这个评论怎么回事
    炼心天毅2017-12-05 11:04 回复
  13. 增量检测到删除记录,需要特殊处理下join sql
    小白2019-06-12 14:31 回复
  14. 如果将此任务打包为job,每天自动运行,当这个job每天运行超过一次时, 数据重复如何解决?
    倚楼听风雨2019-07-27 16:31 回复
  15. 数据回滚怎么做呢
    哈哈2021-01-18 11:23 回复