企业级ETL同步
1. 技术选型
数据同步选用kettle,源项目地址:https://github.com/pentaho/pentaho-kettle
kettlepack负责调度kettle,支持Cron表达式,安装地址:https://www.congjing.net/h-col-139.html
如果有物理删除,还需要借助canal,由它监控binlog日志推送到MQ,服务端监听MQ消费,然后在目标库复现binlog操作
2. 资源配置
K8s:Kettle 5节点(2核6G),Kettlepack单节点(4核16G)
3. 业务背景
实时提供产品数据给客户,覆盖了银行、基金、保险、信托等金融机构
4. 同步方案设计
4.1 表结构设计
CREATE TABLE `kettle_task_config` (
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`next_insert_id` bigint(11) DEFAULT '0' COMMENT '第一次同步时,下一次插入大于这个ID,默认0',
`next_update_id` bigint(11) DEFAULT '0' COMMENT '下一次更新时,大于这个ID,默认0',
`next_start_time` datetime(3) NOT NULL DEFAULT '1970-01-01 00:00:00.000' COMMENT '下一次更新开始时间',
`next_end_time` datetime(3) NOT NULL DEFAULT '2099-12-31 23:59:59.000' COMMENT '下一次更新结束时间',
`sync_status` tinyint(4) unsigned DEFAULT '0' COMMENT '同步状态:0未同步(默认),1已同步',
`task_name` varchar(255) NOT NULL COMMENT '任务名称',
`task_type` tinyint(4) unsigned DEFAULT NULL COMMENT '任务类型:1是作业,2是转换',
`compensation_type` tinyint(4) unsigned DEFAULT NULL COMMENT '补偿类型:1是补偿,2是普通',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_task_name` (`task_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1252 DEFAULT CHARSET=utf8 COMMENT='kettle任务配置表';
以下简称它为配置表
4.2 单表同步(数据量小)
- 查询配置表的开始时间,全量查询大于这个时间的数据。因为单表数据较少,不考虑OOM
- 根据主键ID判断更新还是插入
4.3 单表同步(数据量大)
4.3.1 kettle 文件
update kettle_task_config set next_start_time=(select date_add(now(),interval -6 hour)),sync_status=1
where task_name='${TASKNAME}' or task_name=CONCAT('${TASKNAME}','(task_compensation)')
4.3.2 批量插入(初始化前)
-
查询下一次起始的主键ID,使用逻辑分页
-
分组统计本次查询的总记录数 total、主键ID最大值
-
完成插入后,更新下次分页的起始主键ID
-
将total与设置的分页大小做比较,如果小于代表拉的是最后一页,即第一次批量插入全部完成
-
最后更新批量插入标识和更新开始时间
4.3.3 增量更新(初始化后)
- 查询上一次完成更新的开始时间,大于这个时间,全量查询(定时任务每30秒同步一次,业务发生的增量更新不会很多,不需要分页)
- 根据主键ID,判断该条记录更新还是插入
- 全部完成后,使用分组组件,统计出最大更新时间
- 更新下一次的开始时间、以及补偿作业的结束时间
至此,单表的同步方案已经初步设计完成。但是思考一下,会有什么问题?
我们是根据更新时间同步的,如果在一次增量更新期间,业务数据在批量插入并且update_time都相同,那下一次更新时大于update_time,岂不是会漏数据
那么我们这里是如何解决的呢?请看下面
4.4 补偿设计
一开始我们也想了很多思路,但是最后实现下来复杂度较高,并且也不能够完全保证。那换个角度想,要想绝对保证数据的一致性,那必然要牺牲一定成本,但是这种漏更新的情况确很少出现。最终我们的思路就是,比如每半小时触发作业,重复更新防止遗漏。
其实也就是,在表中生成2条配置,但是它们的标签不一样,一个是正常30秒执行的(要求实时性),一个是每30分钟执行(补偿),彼此更新互不影响。但是另外又迎来一个问题:当2个任务同时执行时,岂不是会发生并发安全问题,这个问题很严重!
解决思路:正常任务执行结束后,会更新下一次的start_time,同时去更新补偿任务的end_time,这样补偿任务查询的范围与正常任务不会有重合的部分
4.5 动态分表同步
4.5.1 动态配置实现
① 独立开启一个作业:比如每个月1号 0-2 点执行
② get_all_sharding_table:查询 Mysql系统表 information_schema 所有的分表
-- table_input
SELECT TABLE_NAME from information_schema.TABLES
WHERE TABLE_SCHEMA = '${tableSchema}' and TABLE_NAME like CONCAT('${tableName}','_20%')
③ initial_kettle_task_config:循环遍历,如果不存在就生成配置
-- query_if_exist_of_config
SELECT count(0) as cnt FROM kettle_task_config WHERE task_name='${TABLENAME}';
-- execute_sql
INSERT INTO `kettle_task_config` (`task_name`, `task_type`, `compensation_type`) VALUES ('${TABLENAME}', 1, 2);
INSERT INTO `kettle_task_config` (`next_end_time`, `sync_status`, `task_name`, `task_type`, `compensation_type`) VALUES ('1970-01-01 00:00:00.000',1,CONCAT('${TABLENAME}','(task_compensation)'), 1, 1);
4.5.2 并发提高性能
4.5.3 负载均衡实现
load_balance_by_month:分表总数 / 并发线程数
-- query_all_tables
SELECT count(0) as total,if(FLOOR((count(0)/10))=0,1,FLOOR((count(0)/10))) as pageSize from kettle_task_config WHERE task_name like CONCAT('${tableName}','_20%') and compensation_type =2
4.5.4 设置变量
经调研,同一个Job中,kettle没有局部变量的概念
set_fromAndnum_2
get_table_name_m2:
SELECT task_name as TABLE_NAME_2 from kettle_task_config WHERE task_name like CONCAT('${tableName}','_20%') and compensation_type =2 LIMIT ${from_2},${num_2}
set_variables_2:
var prevRow=previous_result.getRows(); // copy_to_result
if (prevRow == null &&(prevRow.size()=0)) {
false;
}else{
parent_job.setVariable("tables2", prevRow);
parent_job.setVariable("size2", prevRow.size());
parent_job.setVariable("i2", 0);
parent_job.setVariable("TABLENAME2", prevRow.get(0).getString("TABLE_NAME_2",""));
var type = new Number(parent_job.getVariable("compensation_type"));
var tablename = parent_job.getVariable("TABLENAME2");
var i = tablename.indexOf("_20");
var ym = tablename.slice(i);
var joinTablename = parent_job.getVariable("joinTablename");
parent_job.setVariable("joinTableName2", joinTablename.concat(ym));
if(type == 1){
parent_job.setVariable("TASKNAME2", tablename.concat("(task_compensation)"));
}
if(type == 2){
parent_job.setVariable("TASKNAME2", tablename);
}
true;
}
loop_reset_variable_2:
var list_Tables =parent_job.getVariable("tables2").replace(" ","").replace("[","").replace("]","").split(",");
var size = new Number(parent_job.getVariable("size2"));
var i = new Number(parent_job.getVariable("i2"))+1;
if(i<size){
parent_job.setVariable("TABLENAME2", list_Tables[i]);
var type = new Number(parent_job.getVariable("compensation_type"));
var tablename = parent_job.getVariable("TABLENAME2");
var index = tablename.indexOf("_20");
var ym = tablename.slice(index);
var joinTablename = parent_job.getVariable("joinTablename");
parent_job.setVariable("joinTableName2", joinTablename.concat(ym));
if(type == 1){
parent_job.setVariable("TASKNAME2", tablename.concat("(task_compensation)"));
}
if(type == 2){
parent_job.setVariable("TASKNAME2", tablename);
}
}
parent_job.setVariable("i2",i);
true;
4.5.5 数据同步(与3.3 相同)
5. 适用场景
-
时效性比较高
-
要同步的表不是很多(资源有限)
-
大表较少
6. 后续改变
6.1 背景
经过前期的稳定执行,上述同步方案为公司持续带来了稳定的收益。然而,随着业务需求的逐渐增长,kettle机器节点所承受的压力也逐步上升
6.2 改变
考虑到部分上游表的数据更新并不总是频繁的,适当地调整同步策略有助于减轻服务器的负担,从而保障系统稳定运行。为了确保数据的实时性和完整性,与上游表确认其数据的更新频率显得尤为重要。这将助我们更精准地制定和优化定时同步策略。
6.3 带来的问题
原先,每个定时任务都是每30秒执行一次,从而变相地实现了分页功能。但为了减轻服务器压力,现在我们将定时策略调整为每10分钟执行一次。对于数据量较大的表,这意味着在这10分钟内,数据的更新数量可能会相对较大。这种变化可能会带来以下问题:
- 内存溢出:大量的数据可能会导致程序中使用的缓冲区或内存溢出,尤其是当应用程序使用有限的内存来存储查询结果时。
- 性能下降:加载和处理大量数据会消耗大量的CPU时间。这可能导致服务器负载增加,响应时间变长。
- 网络拥塞:在网络传输大量数据时,可能会消耗大量的带宽,导致其他网络活动受到影响。
- 数据库压力增加:大量的数据读取会增加数据库I/O,可能导致数据库系统性能下降,进而影响到其他用户或查询。
- 程序处理慢:应用程序在处理、遍历或显示大量数据时可能会变得缓慢或卡顿。
6.4 如何应对解决
说白了,就是解决《如何实现更新分页查询》的问题,下图就是实现更新分页查询的方案
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eqpXI021-1692694866719)(C:\Users\yinph\AppData\Roaming\Typora\typora-user-images\image-20230822161838933.png)]
*:加载和处理大量数据会消耗大量的CPU时间。这可能导致服务器负载增加,响应时间变长。
3. 网络拥塞:在网络传输大量数据时,可能会消耗大量的带宽,导致其他网络活动受到影响。
4. 数据库压力增加:大量的数据读取会增加数据库I/O,可能导致数据库系统性能下降,进而影响到其他用户或查询。
5. 程序处理慢:应用程序在处理、遍历或显示大量数据时可能会变得缓慢或卡顿。
6.4 如何应对解决
说白了,就是解决《如何实现更新分页查询》的问题,下图就是实现更新分页查询的方案