基于多表增量同步的拉链表实现

老板,我要写光所有SQL

已于 2022-07-04 17:17:27 修改

阅读量710

点赞数

文章标签：大数据 sql MaxCompute 数据仓库

于 2022-07-04 16:55:48 首次发布

本文链接：https://blog.csdn.net/qq_40772429/article/details/125597249

版权

前言（屁话）：

其实一开始我是在做拉链表时遇到了一个难点，就是当你的增量数据如果要拿到最新数据的话，就得进行一个多表的全表扫描，然后进行排序。当时一个60w条用户的SQL任务，居然需要18分钟的时间（在Maxcompute上），这令我不禁疑惑自己的SQL能力，于是我进行了改进：

方法实现：

由于我开始无法优化拉链表任务，上网搜了不少关于拉链表实现的文章与博客，发现通通都是条件过于简单，源表只有一张的情况（要不就是抄别人的文章，要不就是乱说一通而且自己也没明白原理的，看了都浪费时间），于是我另辟蹊径打算通过自己的方式进行任务优化，以下是我的总结：拉链表的实质是区分最新数据与过期数据的问题，可以通过以下三个方法优化任务。

利用with关键字：

在你需要反复的利用某表时，不妨试试with关键字（大于一次利用某表都可以使用）。这样可以大幅减少开发代码以及运行时间。

提取公因式：

这里适合的场景是你SQL逻辑相近、且字段相同的语句。如下所示，那么表一就是可以写在with中的语句，要用到表二表三时，只需要添加对应的条件即可。

-- 表一

select 
    id,
    name,
    class
from student

-- 表二

select 
    id,
    name,
    class 
from student
where class='语文'

-- 表三

select 
    count(*)
from(
    select 
        id,
        name,
        class 
    from student
)t1
group by class
where id>100

提取公倍式：

适用于SQL语句挑选的字段来源于同一张表的场景。

-- 表一
select 
    id,
    name,
    class,
    teacher
from student


-- 表二

select 
    name,
    class
from student


-- 表三

select 
    id,
    class,
    grades
from student

如上所示，显然三张表字段都不同，但都是来自同一张表，于是我们可以在with里将所有字段的并集进行筛选。

select 
    id,
    name,
    class,
    grades,
    teacher
from student

当然逻辑复杂时，两种方式应该混合使用，可以保证效率的最高！！！（建议自行摸索锻炼一下）

通过with方式，我将拉链表任务的运行时间缩短至几分钟（越多重复筛选，效果越明显）。

多级full join数据：

数据处理通常没有想象中的简单，往往数据库中的表比较零散，需要多表join后才能放入维度表，而且在用户表这种大体量数据的情况下，增量同步的方式肯定更多，且在准实时如一小时一更新的场景下，难免出现任务复杂但要求时效性的情况。这时，如果对多分区、大体量数据进行全表扫描，with其实只是治标不治本，怎么避免对增量同步的表全表扫描是一个关键。

通常有些字段只需要对旧数据进行一次full join，这种字段只需要我们直接对最新分区进行full join获取最新数据即可

select
    nvl(new.id,old.id),
    nvl(new.name,old.name),
    nvl(new.class,old.class),
    nvl(new.phone,old.phone),
    nvl(new.create_time,old.create_time),
    nvl(new.birthday,old.birthday),
    father,
    mother
-- 取旧数据
from (
   select 
        id,
        name,
        class,
        phone,
        father,
        mother,
        create_time,
        birthday
    from dim_student    
    where pt='99990101' 
)old
-- 取最新数据
full join (
    select 
        id,
        name,
        class,
        phone,
        create_time,
        birthday
    from ods_student
    where pt='最新分区'
)new
on new.id=old.id

如上所示，我们可以直接获取到部分字段的最新数据，但是有些数据可能需要通过最新数据再次关联其他表才能得到，我们对其再次进行full join，每一次full join可以解决同一层级多个表关联，这样就解决了增量更新带来的全表扫描问题，大幅减少任务时间！！

select
    new.id,
    old.id,
    new.name,
    old.name,
    new.class,
    old.class,
    new.phone,
    old.phone,
    new.create_time,
    old.create_time,
    new.birthday,
    old.birthday,
    t1.father,
    t2.father,
    t1.mother,
    t3.mother
from (
    select
        new.id,
        old.id,
        new.name,
        old.name,
        new.class,
        old.class,
        new.phone,
        old.phone,
        new.create_time,
        old.create_time,
        new.birthday,
        old.birthday,
        father,
        mother
    -- 取旧数据
    from (
       select 
        id,
        name,
        class,
        phone,
        father,
        mother,
        create_time,
        birthday
        from dim_student    
        where pt='99990101' 
    )old
    -- 取最新数据
    full join (
        select 
        id,
        name,
        class,
        phone,
        create_time,
        birthday
        from ods_student
        where pt='最新分区'
    )new
    on new.id=old.id
)t1
full join(
    select
        id,
        father
    from ods_father
    where pt='最新分区'
)t2
on nvl(new.id,old.id)=t2.id
full join(
    select
        id,
        mother
    from ods_mother
    where pt='最新分区'
)t3
on nvl(new.id,old.id)=t3.id

拆分任务：

当然你也可以采取“逃避性”的拆分任务，也就是多表分装数据：将一个拉链表变成多个拉链表，同时运行，这样不仅逻辑清晰，而且运行起来是相当的快（几个小任务绝对比一个大任务要更加的轻松）。但并不值得推荐，因为通常一个维度的信息在一起查询更加便捷，当然如果有更小的粒度的话还是可以拆分的，比如用户表可能有多个体系，每个体系提供的字段可能都不同，这时你可以将其拆分成一个统一的基础信息表，和各个维度的细分信息表应对不同的需要。