拉链表的具体逻辑

拉链表的具体逻辑

业务数据=======
–6.1 业务表
6.1,o1,u1,创建
6.1,o2,u2,创建
6.1,o3,u2,创建

============

–6.2 业务表
6.1,o1,u1,支付
6.1,o2,u2,创建
6.1,o3,u2,支付
6.2,o4,u3,创建
6.2,o5,u4,创建

============

–6.3 业务表
6.1,o1,u1,支付
6.1,o2,u2,创建
6.1,o3,u2,发货
6.2,o4,u3,创建
6.2,o5,u4,创建
6.3,o6,u5,创建

=增量数据====
–6月1号增量表
6.1,o1,u1,创建
6.1,o2,u2,创建
6.1,o3,u2,创建

–6月2号增量表
6.1,o1,u1,支付
6.1,o3,u2,支付
6.2,o4,u3,创建
6.2,o5,u4,创建

–6月3号增量表
6.1,o3,u2,发货
6.3,o6,u5,创建

– 增量表建表
drop table demo_order_addition;
create table demo_order_addition(
create_time string,
order_id string,
user_id string,
order_status string
)
partitioned by (dt string)
row format delimited fields terminated by ‘,’
;

load data local inpath ‘/root/6.1.txt’ into table demo_order_addition partition(dt=‘2020-06-01’);
load data local inpath ‘/root/6.2.txt’ into table demo_order_addition partition(dt=‘2020-06-02’);
load data local inpath ‘/root/6.3.txt’ into table demo_order_addition partition(dt=‘2020-06-03’);

我们写拉链表需要用到今天的增量表和昨天的拉链表
先将拉链数据分为两类 一种是 end_time为 9999-12-31的一个不是9999-12-31的
然后再将9999-12-31的 full join 今天的在增量表

第一种

就是老订单出现了新状态,我们先将老订单的老状态end_time更改为昨天的日期,封存起来

第二种

是老订单出现了新状态,我们直接重新开启一条新数据用来显示老订单新状态end_time为9999-12-31

第三种

(今天的增量表)再就是 full join 不上的就是新订单end_time为9999-12-31

第四种

(昨天的拉链表)还有老订单的已经闭合过的老数据

最终

将四种数据union昨天的end_time不是9999-12-31的拉链表来生成最新的拉链表!

代码逻辑实现:

将full join之后的表做成一个tmp表

part1(步骤1)
老订单的最后状态处理 + 新订单

part2(步骤2):
为发生了变化的老订单，生成新数据记录

part3(步骤3):
老订单中早已封闭的状态数据

其中一个坑:hive在分区的时候on单表的过滤是不起作用的需要注意!!!

具体的代码实现:

--创建一个增量表
create table demo_order_addition(
create_time    string,
order_id       string,
user_id        string,
order_status   string
)
partitioned by (dt string)
row format delimited fields terminated by ','
;
--导入数据
load data local inpath '/root/6.1.txt' into table demo_order_addition partition(dt='2020-06-01');
load data local inpath '/root/6.2.txt' into table demo_order_addition partition(dt='2020-06-02');
load data local inpath '/root/6.3.txt' into table demo_order_addition partition(dt='2020-06-03');

-- 建表： 拉链表(附加其实和结束日期)
drop table demo_order_zip;
create table demo_order_zip(
create_time    string,
order_id       string,
user_id        string,
order_status   string,
begin_date     string,   -- 状态有效起始日期
end_date       string    -- 状态有效结束日期
);

-- 计算
-- 前拉链中每个订单的最后状态数据    full join  今增量
WITH tmp as(
    SELECT
       a.create_time   ,
       a.order_id      ,
       a.user_id       ,
       a.order_status  ,
       a.begin_date    , 
       a.end_date      ,
       b.create_time   as b_create_time  ,
       b.order_id      as b_order_id     ,
       b.user_id       as b_user_id      ,
       b.order_status  as b_order_status 
    FROM
    (
    -- 拉链表中每个订单的最后状态(9999-12-31)
      SELECT
       create_time ,
       order_id    ,
       user_id     ,
       order_status,
       begin_date  ,   -- 状态有效起始日期
       end_date        -- 状态有效结束日期
      FROM demo_order_zip 
	--在增量表中必须在这个位置做单表的过滤,在最后做这种分区表的过滤是不生效的!!!!!!!
	WHERE end_date='9999-12-31'
    ) a
    
    FULL JOIN 
    
    -- 当日的增量数据
    (
    SELECT 
     create_time    ,
     order_id       ,
     user_id        ,
     order_status   
    FROM demo_order_addition WHERE dt='2020-06-03'
    ) b
    ON a.order_id=b.order_id
)

--

INSERT OVERWRITE table demo_order_zip

-- part1：  老订单的最后状态处理  +  新订单

SELECT
  if(create_time is not null,create_time,b_create_time) as create_time,
  if(order_id is not null,order_id,b_order_id) as order_id,
  if(user_id is not null,user_id,b_user_id) as user_id,
  if(order_status is not null,order_status,b_order_status) as order_status,
  if(begin_date is not null,begin_date,'2020-06-03') as begin_date,
  if(order_id is not null and b_order_id is not null,date_sub('2020-06-03',1),'9999-12-31') as end_date
FROM tmp

UNION ALL

-- part2：为发生了变化的老订单，生成新数据记录
-- 取数：全部取新状态数据
SELECT
  b_create_time   as create_time,
  b_order_id      as order_id,
  b_user_id       as user_id,
  b_order_status  as order_status,
  '2020-06-03'    as begin_date,
  '9999-12-31'    as end_date
FROM tmp
WHERE order_id is not null and b_order_id is not null

UNION ALL

-- part3: 老订单中早已封闭的状态数据
SELECT
  create_time   ,
  order_id      ,
  user_id       ,
  order_status  ,
  begin_date    ,   -- 状态有效起始日期
  end_date          -- 状态有效结束日期
FROM demo_order_zip WHERE end_date!='9999-12-31'
;