拉链表的介绍和使用

lijian972

已于 2022-04-18 18:45:19 修改

阅读量4.6k

点赞数 4

分类专栏：数仓建模拉链表的介绍和使用文章标签： hive sql 大数据

于 2022-03-23 08:33:31 首次发布

本文链接：https://blog.csdn.net/lijian972/article/details/123676284

版权

数仓建模拉链表的介绍和使用专栏收录该内容

1 篇文章

订阅专栏

#博学谷IT学习技术支持#

1.拉链表的介绍

在数仓建模中，一般有渐变维度问题，不同的时间段有不同的状态，这就叫渐变维度问题。比如用户2021年在北京工作，2022年之后在上海工作，他的属性工作地发生的改变。
维度数据发生变化，如何处理发生变化的数据呢？
可以在表中添加两个字段start_time和end_time来标记这个属性的时间范围，如下表。

user_id	user_name	work_area	start_time	end_time
100	zhangsan	beijing	2021-01-01	2021-12-31
100	zhangsan	shanghai	2022-01-01	9999-12-31

其中9999-12-31表示目前最新的状态。一般拉链表用在dwd层（Data Warehouse Detail），数据来源于ods层（Operational Data Store）。

2.拉链表的使用

拉链表主要用在dwd层，用来及时记录每个事务状态的。加入ods层数据发生的新增或者更新，相应的dwd层的数据也会改变。拉链表数据生成的思路是：ods更新或者新增的数据 + union +dwd拉链表历史数据（要更改历史数据中状态发生改变的字段）。
方法有两种：窗口函数和union。
准备数据：ods层的新增数据和dwd层连链表的历史数据
dwd层连链表的历史数据

dwd层连链表的历史数据查询sql：

select
    userid,
    phone,
    nick,
    gender,
    addr,
    starttime,
    endtime
from yp_dwd.dw_zipper

ods层的新增数据

ods层的新增数据查询sql：

select
    userid,
    phone,
    nick,
    gender,
    addr,
    starttime,
    endtime
from  yp_ods.ods_zipper_update

方式一： union

--：合并拉链表与增量表（tmp_zipper为新建的临时表，建表语句和拉链表一样）
insert overwrite table tmp_zipper
select
    userid,
    phone,
    nick,
    gender,
    addr,
    starttime,
    endtime
from ods_zipper_update
union all
--查询原来拉链表的所有数据，并将这次需要更新的数据的endTime更改为更新值的startTime
select
    a.userid,
    a.phone,
    a.nick,
    a.gender,
    a.addr,
    a.starttime,
    --如果这条数据没有更新或者这条数据不是要更改的数据，就保留原来的值，否则就改为新数据的开始时间-1
    if(b.userid is null or a.endtime < '9999-12-31', a.endtime , date_sub(b.starttime,1)) as endtime
from dw_zipper a  left join ods_zipper_update b
                            on a.userid = b.userid ;

查询结果
union方式的合并结果
结果显示userid=008的用户用两条记录，一条是addr=gz的历史记录,其中endtime=2021-01-01，一条是addr=gz的最新状态，其中endtime=9999-12-31。userid=011和userid=012是新增的记录。
方式二：窗口函数

select userid,
       phone,
       nick,
       gender,
       addr,
       starttime,
       if(lead_val is not null, date_sub(lead_val, 1), '9999-12-31') endtime
from (select *,
             lead(starttime, 1) over (partition by userid order by starttime) as lead_val
      from (
               select *
               from yp_dwd.dw_zipper
               union all
               select *
               from yp_ods.ods_zipper_update
           ) t) tt;