update 两个表关联_拉链表（二）

拉链表（一）
拉链表（二）

一、前言

在上一节简单介绍了拉链表，本节主要讲解如何通过binlog采集MySQL的数据并且按月分区的方式实现拉链表。

这里以上节介绍的用户表(user) 举例

二、涉及到的表

1. 原始表（user）

原始表指的是MySQL中的表，表结构如下：

其中name为主键，如果没有主键则无法做拉链表。

2. binlog流水表(user_binlog)

操作类型字段枚举值为：insert、update、delete。

设置binlog时间 的目的是防止业务方没有设置modify_time导致获取不到最新的更新时间，所以增加binlog时间。

日期分区字段是从binlog_time计算得来，作为分区字段

3. 拉链表(user_link)

这里包含的字段除去原始表的字段增加了生效日期及失效日期具体作用已经在上一节介绍过，这里就不再赘述。

4. 临时表(user_link_tmp)

这张表的用途是： 在数据从user_binlog写入user_link时，临时表起到中转的作用。并且临时表没有分区。

三、计算流程

1. 整体数据流向

2. user到user_binlog

数据从user表到user_binlog表可以采用开源的采集binlog工具实时写入。具体的实施方案和选择的开源工具有关，这里不详细介绍。

3. user_binlog到user_link

(1) 常规流程

把数据从binlog表同步到拉链表中主要分两步：

• 删除拉链表中失效的数据: 这里包括update和delete类型的数据，都涉及到删除原始拉链表的数据。在这一步骤中有两个子步骤
  • 将拉链表中失效的失效日期字段改为批次日期
  • 从拉链表原有分区中删除失效的数据
• 插入新的数据：这一步骤涉及到的操作类型包含insert和update

接下来会以7月11日执行的SQL举例，详细介绍如何把binlog表的数据同步到拉链表中。其中的SQL涉及到先把binlog表中的数据同步到临时表，并把临时表写入到拉链表。

-- 先清空临时表的数据。
-- 理论上这张表已经是清空的。
-- 这里清空主要是防止异常清空，导致上一批次没有清空临时表
truncate table user_link_tmp;

-- 将拉链表中需要改为失效的数据的失效时间改为'2019-07-10',并把数据写入到临时表中
-- 其中start_date>='2019-07-01'是因为7月1日之前未失效的数据会写入到开始时间为7月1日的分区中，
-- 所以查开始分区只要查当月的即可
-- 结束分区用end_date>'2019-07-09'而不用end_date='9999-12-31'是防止历史数据重跑时前一中写法不会有问题，而第二种写法只有在正常逻辑中没有问题。
insert into table user_link_tmp
select 
    name,
    phone,
    sing_up_date,
    modify_time,
    start_date,
    '2019-07-10' as end_date
from user_link 
where start_date<='2019-07-09' 
and start_date>='2019-07-01' 
and end_date>'2019-07-09'
and name in
(
    select 
        name 
    from user_binlog 
    where day_num='2019-07-10' 
    and type in ('update','delete') 
    group by name
);

-- 将原始拉链表中未失效的数据原样写入到临时表中
-- 此步骤的目的是从原有分区中删除失效的数据
-- 即在把临时表的数据覆盖到拉链表中时会把失效的数据从原有未失效分区中删除。
insert into table user_link_tmp
select 
    name,
    phone,
    sing_up_date,
    modify_time,
    start_date,
    end_date
from user_link
where start_date<='2019-07-09' 
and start_date>='2019-07-01' 
and end_date>'2019-07-09'
and name not in
(
    select 
        name 
    from user_binlog 
    where day_num='2019-07-10' 
    and type in ('update','delete') 
    group by name
);

-- 将新增的数据写入到临时表中。
-- 并且开始时间为当前批次日期，结束日期为最大日期
insert into table user_link_tmp
select 
    a.name,
    a.phone,
    a.sing_up_date,
    a.modify_time,
    '2019-07-10' as start_date,
    '9999-12-31' as end_date
from
(
    select 
        name,
        phone,
        sing_up_date,
        modify_time,
        binlog_time
    from user_binlog
    where day_num='2019-07-10'
) a
right join
(
    select 
        name,
        max(binlog_time)
    from user_binlog
    where day_num='2019-07-10'
    and type in ('insert','update') 
    group by name
) b
on a.name=b.name 
and a.binlog_time=b.binlog_time
;

-- 将临时表中的数据覆盖到拉链表中。
insert overwrite table user_link partition(start_date)
select 
    name,
    phone,
    sing_up_date,
    modify_time,
    start_date,
    end_date
from user_link_tmp;

-- 删除临时表中的数据
truncate table user_link_tmp;

(2) 月初流程

在每个月月初会涉及到把上月还未失效的数据写入到开始时间为当月1日失效日期为9999-12-31的分区中，并把原始数据的失效日期改为上月末的逻辑。

接下来会以7月2日执行的SQL为例，来展示7月1日的数据是如何同步的。

truncate table user_link_tmp;

-- 把拉链表所有6月30日未失效的数据失效日期改为7月1日
insert into table user_link_tmp;
select 
    name,
    phone,
    sing_up_date,
    modify_time,
    start_date,
    '2019-07-01' as end_dat
from user_link
where start_date<='2019-06-30' 
and start_date>='2019-06-01' 
and end_date>'2019-06-30'

-- 把7月1日依然为失效的数据的开始日期改为7月1日失效日期改为9999-12-31
insert into table user_link_tmp
select 
    name,
    phone,
    sing_up_date,
    modify_time,
    '2019-07-01' as start_date,
    '9999-12-31' as end_date
from user_link
where start_date<='2019-06-30' 
and start_date>='2019-06-01' 
and end_date>'2019-06-30'
and name not in
(
    select 
        name 
    from user_binlog 
    where day_num='2019-07-01' 
    and type in ('update','delete') 
    group by name
);

-- 把7月1日新的数据写入到临时表中
insert into table user_link_tmp
select 
    a.name,
    a.phone,
    a.sing_up_date,
    a.modify_time,
    '2019-07-01' as start_date,
    '9999-12-31' as end_date
from
(
    select 
        name,
        phone,
        sing_up_date,
        modify_time,
        binlog_time
    from user_binlog
    where day_num='2019-07-01'
) a
right join
(
    select 
        name,
        max(binlog_time)
    from user_binlog
    where day_num='2019-07-10'
    and type in ('insert','update') 
    group by name
) b
on a.name=b.name 
and a.binlog_time=b.binlog_time

-- 将临时表中的数据覆盖到拉链表中。
insert overwrite table user_link partition(start_date)
select 
    name,
    phone,
    sing_up_date,
    modify_time,
    start_date,
    end_date
from user_link_tmp;

-- 删除临时表中的数据
truncate table user_link_tmp;

-- 删除6月份所有结束时间为9999-12-31分区的数据
alter table user_link_tmp drop if exists partition(stat_date>='2019-06-01' , start_date<'2019-07-01', end_date='9999-12-31' );

(3) 数据重跑

如果某个日期同步的数据出现问题需要重跑数据，则需要重跑从当日的同步SQL到当前日期所有的SQL才能保证数据准确。

三、总结

至此，拉链表的同步过程就结束了。总体将拉链表的同步对资源消耗还是蛮多的。

注意：本文的实现还有需要考虑不周的地方，在应用的时候需要根据自己的需求进行优化。