【数仓】拉链表

  拉链表是大数据开发面试重点，今天专门写一篇文章进行讨论。

1.什么是拉链表

用于记录每条信息的生命周期，有开始日期和结束日期字段。至今有效的用极大值表示。

| uid  | name | phone_number | start_date| end_date   |
| ---- | ---- | ------------ | ---------- | ---------- |
| 1    | 张三 | 188****7777  | 2020-01-01 | 2020-02-10 |
| 1    | 张三 | 135****6666  | 2020-02-11 | 2021-01-01 |
| 1    | 张三 | 181****5555  | 2021-01-02 | 9999-99-99 |

2.为什么要做拉链表

适合缓慢变化维度：数据会变化，但频率不高，做每日全量效率低。

3.如何使用

通过 开始日期<=某个日期 and 结束日期>=某个日期，可以得到某个日期的全量切片。

select * from user_info where start_date<='2020-03-01' and end_date>='2020-03-01'

4.如何形成

5.如何制作

以订单表为例：

• 创建订单表拉链表，字段跟订单表一样，只增加了有效开始日期和有效结束日期。初始日期，从订单变化表ods_order_info导入数据，且让有效开始时间=当前日期，有效结束日期=9999-99-99

• 建一张拉链临时表dwd_order_info_his_tmp，字段跟拉链表完全一致

• 新的拉链表中应该有这几部分数据：

  • 增加订单变化表 dwd_order_info 的全部数据

  • 旧的拉链表左关联订单变化表dwd_order_info，关联字段：订单 id, where 过滤出 end_date 等于 9999-99-99 的数据，如果旧的拉链表中的end_date不等于9999-99-99，说明已经是终态了，不需要再更新

  • 如果 dwd_order_info.id is null , 没关联上，说明数据状态没变，让 end_date 还等于旧的 end_date

  • 如果 dwd_order_info.id is not null , 关联上了，说明数据状态变了，让 end_date 等于当前日期 -1

  • 把查询结果插入到拉链临时表中

• 把拉链临时表覆盖到旧的拉链表中

具体过程如下：

insert overwrite table dwd_order_info_his_tmp
select * from
(
    select			# 筛选出当天变化的全部数据，开始日期为今天，结束日期极大值
        id,
        total_amount,
        order_status,
        user_id,
        payment_way,
        out_trade_no,
        create_time,
        '2019-01-02' start_date,
        '9999-99-99' end_date
    from dwd_order_info where dt='2019-01-02'
    union all
    select			# 旧的拉链表中筛选出信息
        oh.id,
        oh.total_amount,
        oh.order_status,
        oh.user_id,
        oh.payment_way,
        oh.out_trade_no,
        oh.create_time,
        oh.operate_time,
        oh.start_date,
        if(oi.id is null,oh.end_date,date_add(oi.dt,-1)) end_date	# 更改结束日期
    from dwd_order_info_his oh
    left join(
        select * from dwd_order_info
        where dt='2019-01-02'
    ) oi
    on oh.id=oi.id and oh.end_date='9999-99-99'		# 订单号做关联，并且结束日期是极大值才需要更改
) his
order by his.id,start_date;

欢迎关注公众号。