数仓拉链表的实现过程

1.简介

数据仓库中经常会遇到维度表缓慢变化的情况，针对这种情况可采用拉链表进行处理。拉链表就是记录历史数据的每个状态，数据量大，且存在数据的新增和变化。

2.实现过程

本文主要实现用户拉链表

2.1初始化用户拉链表

在DWD层创建用户的初始化拉链表，即在原始用户维度表上添加start_date和end_date列

DROP TABLE IF EXISTS gmall_2021.dwd_dim_user_info_his;
-- 创建初始化拉链表
CREATE EXTERNAL TABLE gmall_2021.dwd_dim_user_info_his
(
    id STRING,
    name STRING,
    birthday STRING,
    gender STRING,
    email STRING,
    user_level STRING,
    create_time STRING,
    operate_time STRING,
    start_date STRING COMMENT '有效开始日期',
    end_date STRING COMMENT '有效结束日期'
) COMMENT '用户拉链表'
STORED AS
    PARQUET
LOCATION
    '/warehouse/dwd/dwd_dim_user_info_his'
TBLPROPERTIES('parquet.compression'='lzo');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

插入数据到用户初始化拉链表

-- 加载数据到初始化的拉链表
INSERT OVERWRITE TABLE dwd_dim_user_info_his
SELECT
    id,
    name,
    birthday,
    gender,
    email,
    user_level,
    create_time,
    operate_time,
    '2021-02-05',
    '9999-99-99'
from
    ods_user_info
WHERE
    dt = '2021-02-05';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2.2创建临时拉链表

临时拉链表主要用于覆盖初始化的用户拉链表

-- 创建临时拉链表(用于对主拉链表进行覆盖)
DROP TABLE IF EXISTS gmall_2021.dwd_dim_user_info_his_tmp;
CREATE EXTERNAL TABLE gmall_2021.dwd_dim_user_info_his_tmp
(
    id STRING,
    name STRING,
    birthday STRING,
    gender STRING,
    email STRING,
    user_level STRING,
    create_time STRING,
    operate_time STRING,
    start_date STRING COMMENT '有效开始日期',
    end_date STRING COMMENT '有效结束日期'
) COMMENT '用户拉链表'
STORED AS
    PARQUET
LOCATION
    '/warehouse/dwd/dwd_dim_user_info_his_tmp'
TBLPROPERTIES('parquet.compression'='lzo');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2.3加载数据到临时拉链表

将初始化用户的拉链表(2021-02-05) left join 新增和变化的数据表（2021-02-06），根据id进行join，id相同且初始化拉链表中end_date='9999-99-99’的即为变化的数据，对于变化的数据只需要使用当前时间-1，替换掉之前的9999-99-99即可。最后将ODS层新增和变化的数据与替换过后的表进行union all，即可得到临时拉链表的数据。

-- 将主拉链表left join新增和变化的数据(即ODS层新增和变化的user_info数据)
-- 当id join上时，表示该条数据发生变化，只需要将变化的日期-1替换掉之前拉链表中的9999-99-99
INSERT OVERWRITE TABLE gmall_2021.dwd_dim_user_info_his_tmp
SELECT
    *
FROM(
    SELECT
        id,
        name,
        birthday,
        gender,
        email,
        user_level,
        create_time,
        operate_time,
        '2021-02-06' start_date,
        '9999-99-99' end_date
    from ods_user_info WHERE dt = '2021-02-06'
    
    UNION ALL
    
    select
        uh.id,
        uh.name,
        uh.birthday,
        uh.gender,
        uh.email,
        uh.user_level,
        uh.create_time,
        uh.operate_time,
        uh.start_date,
        if(ui.id is NOT NULL , date_add(ui.dt, -1), uh.end_date) end_date
    from dwd_dim_user_info_his uh
    LEFT JOIN
    (
        SELECT * from ods_user_info WHERE dt = '2021-02-06'
    ) ui
    ON
        uh.id = ui.id and uh.end_date = '9999-99-99'
        ORDER BY uh.id, uh.start_date
) his 
ORDER BY his.id, his.start_date;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

2.4覆盖原始拉链表

-- 将临时表覆盖原始拉链表
INSERT OVERWRITE TABLE dwd_dim_user_info_his
SELECT * from dwd_dim_user_info_his_tmp;
1
2
3

2.5查看结果

SELECT id, name, start_date, end_date from dwd_dim_user_info_his;
1