长春免费建站模板,安顺建设局网站官网,华大基因 网站建设公司,如何做网站的后台管理拉链表#xff08;一#xff09;拉链表#xff08;二#xff09;一、前言在上一节简单介绍了拉链表#xff0c;本节主要讲解如何通过binlog采集MySQL的数据并且按月分区的方式实现拉链表。这里以上节介绍的用户表(user) 举例二、涉及到的表1. 原始表#xff08;user…拉链表一拉链表二一、前言在上一节简单介绍了拉链表本节主要讲解如何通过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_date2019-07-01是因为7月1日之前未失效的数据会写入到开始时间为7月1日的分区中
-- 所以查开始分区只要查当月的即可
-- 结束分区用end_date2019-07-09而不用end_date9999-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_date2019-07-09
and start_date2019-07-01
and end_date2019-07-09
and name in
(select name from user_binlog where day_num2019-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_date2019-07-09
and start_date2019-07-01
and end_date2019-07-09
and name not in
(select name from user_binlog where day_num2019-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_timefrom user_binlogwhere day_num2019-07-10
) a
right join
(select name,max(binlog_time)from user_binlogwhere day_num2019-07-10and type in (insert,update) group by name
) b
on a.nameb.name
and a.binlog_timeb.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_date2019-06-30
and start_date2019-06-01
and end_date2019-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_date2019-06-30
and start_date2019-06-01
and end_date2019-06-30
and name not in
(select name from user_binlog where day_num2019-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_timefrom user_binlogwhere day_num2019-07-01
) a
right join
(select name,max(binlog_time)from user_binlogwhere day_num2019-07-10and type in (insert,update) group by name
) b
on a.nameb.name
and a.binlog_timeb.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_date2019-06-01 , start_date2019-07-01, end_date9999-12-31 );(3) 数据重跑如果某个日期同步的数据出现问题需要重跑数据则需要重跑从当日的同步SQL到当前日期所有的SQL才能保证数据准确。三、总结至此拉链表的同步过程就结束了。总体将拉链表的同步对资源消耗还是蛮多的。注意本文的实现还有需要考虑不周的地方在应用的时候需要根据自己的需求进行优化。
