分类导航
程序笔记程序问答

拉链表的创建、查询和回滚

程序笔记   发布时间：2022-07-15  发布网站：大佬教程  code.js-code.com
大佬教程收集整理的这篇文章主要介绍了拉链表的创建、查询和回滚，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

概述

使用这种方式即可以记录历史，而且最大程度的节省存储。这里简单介绍一下这种历史拉链表的更新方法。此文参考链接
本文中假设：
  1. 数据仓库中订单历史表的刷新频率为一天，当天更新前一天的增量数据；
  2. 如果一个订单在一天内有多次状态变化，则只会记录最后一个状态的历史；
  3. 订单状态包括三个：创建、支付、完成；
  4. 创建时间和修改时间只取到天，如果源订单表中没有状态修改时间，那么抽取增量就比较麻烦，需要有个机制来确保能抽取到每天的增量数据；
  5. 本文中的表和SQL都使用Hive的HQL语法；
 

初始化

假设我们有三天数据，【2015-08-20，2015-08-21，2015-08-22】。
数据流向：（原系统表）orders ==> （增量表）t_ods_orders_inc ==> （拉链表）t_dw_orders_his 
建表脚本
-- 源系统中订单表
CREATE TABLE@H_801_41@ orders (
    orderid INT@H_801_41@,
    createtime StriNG,
    modifiedtime StriNG,
    status StriNG
) stored AS@H_801_41@ textfile;

-- 订单的增量数据表，按天分区，存放每天的增量数据，保留半年左右
CREATE TABLE@H_801_41@ t_ods_orders_inc (
    orderid INT@H_801_41@,
    createtime StriNG,
    modifiedtime StriNG,
    status StriNG
) PARTITIONED BY (day@H_801_41@ StriNG)
stored AS@H_801_41@ textfile;

-- 订单的历史数据拉链表
CREATE TABLE@H_801_41@ t_dw_orders_his (
    orderid INT@H_801_41@,
    createtime StriNG,
    modifiedtime StriNG,
    status StriNG,
    dw_start_date StriNG,
    dw_END_DATE StriNG
) stored AS textfile;

 

 
数据初始化
-- 1. 源表orders，假设此表为21日状态
insert into@H_801_41@ orders 
values@H_801_41@ 
(1,'2015-08-18','2015-08-18','创建'@H_801_41@),
(2,'2015-08-18','2015-08-18','创建'@H_801_41@),
(3,'2015-08-19','2015-08-21','支付'@H_801_41@),
(4,'2015-08-19','2015-08-21','完成'@H_801_41@),
(5,'2015-08-19','2015-08-20','支付'@H_801_41@),
(6,'2015-08-20','2015-08-20','创建'@H_801_41@),
(7,'2015-08-20','2015-08-21','支付'@H_801_41@),
(8,'2015-08-21','2015-08-21','创建'@H_801_41@);

-- 2. 初始化ODS增量表-21日数据(全量初始化，将21号前的累加到此分区)
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2015-08-21'@H_801_41@)
SELECT@H_801_41@ 
    orderid,
    createtime,
    modifiedtime,
    status
FROM@H_801_41@ orders
WHERE createtime <= '2015-08-21'@H_801_41@;

-- 3. 初始化ODS增量表-22日数据
insert overwrite table t_ods_orders_inc partition(day='2015-08-22'@H_801_41@)
values@H_801_41@
(1,'2015-08-18','2015-08-22','支付'@H_801_41@),
(2,'2015-08-18','2015-08-22','完成'@H_801_41@),
(6,'2015-08-20','2015-08-22','支付'@H_801_41@),
(8,'2015-08-21','2015-08-22','支付'@H_801_41@),
(9,'2015-08-22','2015-08-22','创建'@H_801_41@),
(10,'2015-08-22','2015-08-22','支付'@H_801_41@);

-- 4. 初始化ODS增量表-23日数据
insert overwrite table t_ods_orders_inc partition(day='2015-08-23'@H_801_41@)
values@H_801_41@
(1,'2015-08-18','2015-08-23','完成'@H_801_41@),
(3,'2015-08-19','2015-08-23','完成'@H_801_41@),
(5,'2015-08-19','2015-08-23','完成'@H_801_41@),
(8,'2015-08-21','2015-08-23','完成'@H_801_41@),
(11,'2015-08-23','2015-08-23','创建'@H_801_41@),
(12,'2015-08-23','2015-08-23','创建'@H_801_41@),
(13,'2015-08-23','2015-08-23','支付');

 

拉链表创建

假设
@H_111_675@
  • 跑数时间 T= '${dt1}'
  • 拉链表有开始日期（生效日期）和结束日期（失效日期，最新记录此列 = '9999-12-31'）
    •  

    初始化

    当21号跑数时，需要全量初始化拉链表，此时，拉链表就是21日的切片数据
    -- 初始化拉链表,假设21号的就是原始数据
INSERT overwrite TABLE@H_801_41@ t_dw_orders_his
SELECT@H_801_41@ 
    orderid,
    createtime,
    modifiedtime,
    status,
    createtime   AS@H_801_41@ dw_start_date,
    '9999-12-31' AS@H_801_41@ dw_END_DATE
FROM@H_801_41@ t_ods_orders_inc
WHERE day = '2015-08-21';

     

    增量更新

    当22号跑数时，需要把要处理的所有数据分成两部分，处理思路为：
    @H_111_675@
  • 新增，22号增量数据，结束日期= '9999-12-31'
  • 更新，历史拉链表与增量表进行比对
    • @H_111_675@
  • 当增量表中存在记录，开始日期=历史拉链表开始日期，结束日期= date_add('${dt1}',-1)
  • 当增量表中不存在此记录，代表不需要更新
    •  
    @H_262_768@具体操作
    创建一张临时表保存比对的结果数据。
    -- 22号增量数据进来后，与21号的状态数据(t_dw_orders_his)比对，更新拉链表
-- ${dt1} = '2015-08-22'
DROP TABLE IF EXISTS@H_801_41@ t_dw_orders_his_tmp;
CREATE TABLE t_dw_orders_his_tmp AS 
SELECT@H_801_41@  orderid,
        createtime,
        modifiedtime,
        status,
        dw_start_date,
        dw_END_DATE 
FROM@H_801_41@ (
    -- 22号前需更新状态的数据
    SELECT@H_801_41@  a.orderid,
            a.createtime,
            a.modifiedtime,
            a.status,
            a.dw_start_date,
            CASE WHEN b.orderid IS NOT NULL AND a.dw_END_DATE = '9999-12-31' 
                THEN date_add('${dt1}',-1) -- 把22号前有效的数据失效日期置为dt1的上一日
            ELSE a.dw_END_DATE END AS@H_801_41@ dw_END_DATE 
    FROM@H_801_41@ t_dw_orders_his a 
    left join@H_801_41@ t_ods_orders_inc b 
      ON a.orderid =@H_801_41@ b.orderid
     and b.day = '${dt1}'
    UNION ALL 
    -- 22号最新状态数据
    SELECT@H_801_41@  orderid,
            createtime,
            modifiedtime,
            status,
            modifiedtime AS@H_801_41@ dw_start_date,
            '9999-12-31' AS@H_801_41@ dw_END_DATE 
    FROM@H_801_41@ t_ods_orders_inc 
    WHERE day = '${dt1}'@H_801_41@ 
) x 
ORDER BY@H_801_41@ orderid,dw_start_date;

-- 临时数据正式入库
INSERT overwrite TABLE@H_801_41@ t_dw_orders_his
SELECT * FROM@H_801_41@ t_dw_orders_his_tmp;

-- 23号数据入t_dw_orders_his表请重复上述22号的刷新步骤
-- ${dt1} = '2015-08-23'
-- ... ...

     

    　
     
    查询和使用场景
    1. 查询拉链表最新状态数据
    SELEct * from t_dw_orders_his where dw_END_DATE='9999-12-31';

     

     
    2.查询某日所有订单快照
    --假设查询22号数据状态 
--'${dt1}' = '2021-08-22'
SELEct 
* 
from@H_801_41@ t_dw_orders_his 
where dw_start_date<='${dt1}'
and dw_END_DATE>='${dt1}';

     

    回滚方法

     
    @H_262_768@方法
    先把拉链表的数据分为三份，分别为 T-N、T、T+N 的数据，T是回滚日期。
    那么我们假设，在23号发现数据有问题，需要回滚22号的数据，此时拉链表数据有三块。
     

    拉链表的创建、查询和回滚

     
    其中：
    @H_111_675@
  • 对于结束日期为21号及之前的数据，【保留】，下图绿色
  • 对于22日有效的数据，【更新】，其数据又分两种
    • @H_111_675@
  • 一种是结束日期是22日的，把结束日期 = '9999-12-31'即可，下图黄色
  • 一种是22日前创建，22日后还有效的数据，把结束日期 = '9999-12-31'，下图蓝色
  • 对于22日后产生的数据，【删除】，下图红色
    •  
    所以，拉链表的回滚过程的增删改就如下图所示：
     

    拉链表的创建、查询和回滚

     
    @H_262_768@具体操作
    创建三个临时表，分别保存 T-N、T日的数据，最后合到一张结果表中。
    -- 1. 绿色，保留
DROP TABLE@H_801_41@ t_dw_orders_his_tmp1;
CREATE TABLE@H_801_41@ t_dw_orders_his_tmp1
AS
SELECT@H_801_41@ 
　　orderid,
　　createtime,
　　modifiedtime,
　　status,
　　dw_start_date,
　　dw_END_DATE
FROM@H_801_41@ 
　　t_dw_orders_his
WHERE@H_801_41@ 
　　dw_END_DATE < '2015-08-22'@H_801_41@;

-- 2. 黄色，更新-当日生效的数据
DROP TABLE@H_801_41@ t_dw_orders_his_tmp2;
CREATE TABLE@H_801_41@ t_dw_orders_his_tmp2 
AS 
SELECT@H_801_41@ 　　
　　orderid,
　　createtime, 　　
　　modifiedtime, 　　
　　status, 　　
　　dw_start_date, 　　
　　'9999-12-31' AS@H_801_41@ dw_END_DATE 
FROM@H_801_41@ 
　　t_dw_orders_his
WHERE@H_801_41@ 
　　dw_END_DATE = '2015-08-22'@H_801_41@;
  
-- 2. 蓝色，更新-22号前到22号后还生效的数据
DROP TABLE@H_801_41@ t_dw_orders_his_tmp3;
CREATE TABLE@H_801_41@ t_dw_orders_his_tmp3
AS
SELECT@H_801_41@ 
　　orderid,
　　createtime,
　　modifiedtime,
　　status,
　　dw_start_date,
　　'9999-12-31'@H_801_41@ dw_END_DATE
FROM@H_801_41@ 
　　t_dw_orders_his
WHERE@H_801_41@ 
　　dw_start_date <= '2015-08-22' AND dw_END_DATE > '2015-08-22'@H_801_41@;
  
-- 4. 数据插入到新表  
CREATE TABLE@H_801_41@ t_dw_orders_his_new
AS
SELEct * 
from@H_801_41@ ( 
    SELECT a.* ,'绿色，保留,号前的数据' FROM@H_801_41@ t_dw_orders_his_tmp1 a
    UNION ALL
    SELECT b.*,'黄色，更新-当日生效的数据' FROM@H_801_41@ t_dw_orders_his_tmp2 b
    UNION ALL
    SELECT c.*,'蓝色，更新-22号前到22号后还生效的数据' FROM@H_801_41@ t_dw_orders_his_tmp3 c
 ) a 
ORDER BY a.orderid,a.dw_start_date;

     

    总结

    拉链表的出现是为了压缩存储和记录数，针对频繁更新的数据会很有效。但是回滚操作不方便，查询的时候也必须要指定时间才能正确取数，操作成本高，得权衡利弊后再确认是否合适自己使用。

    　　

    大佬总结

    以上是大佬教程为你收集整理的拉链表的创建、查询和回滚全部内容，希望文章能够帮你解决拉链表的创建、查询和回滚所遇到的程序开发问题。

    如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

    本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
    如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。
    标签：hiveloadpartitionphpsql程序员
    猜你在找的程序笔记相关文章
    其他相关热搜词更多
    phpJavaPython程序员load中如何string使用参数jquery开发安装listlinuxiosandroid工具javascriptcap