企业级ETL同步

最新推荐文章于 2024-03-07 17:04:14 发布
最新推荐文章于 2024-03-07 17:04:14 发布
阅读量137 收藏
点赞数
文章标签： etl
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36211262/article/details/132431753
版权
本文详细描述了一种企业级ETL同步解决方案，使用Kettle和Kettlepack进行数据迁移，涉及资源分配、表结构设计、单表同步策略（包括补偿设计和动态分表）、以及针对大数据量的更新分页查询优化。随着业务增长，同步策略进行了调整以减轻服务器压力并解决可能出现的问题。
摘要由CSDN通过智能技术生成

企业级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 单表同步（数据量小）

在这里插入图片描述

在这里插入图片描述

  1. 查询配置表的开始时间，全量查询大于这个时间的数据。因为单表数据较少，不考虑OOM
  2. 根据主键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 批量插入（初始化前）

  1. 查询下一次起始的主键ID，使用逻辑分页

  2. 分组统计本次查询的总记录数 total、主键ID最大值

  3. 完成插入后，更新下次分页的起始主键ID

  4. 将total与设置的分页大小做比较，如果小于代表拉的是最后一页，即第一次批量插入全部完成

  5. 最后更新批量插入标识和更新开始时间

在这里插入图片描述

在这里插入图片描述

4.3.3 增量更新（初始化后）
  1. 查询上一次完成更新的开始时间，大于这个时间，全量查询（定时任务每30秒同步一次，业务发生的增量更新不会很多，不需要分页）
  2. 根据主键ID，判断该条记录更新还是插入
  3. 全部完成后，使用分组组件，统计出最大更新时间
  4. 更新下一次的开始时间、以及补偿作业的结束时间

至此，单表的同步方案已经初步设计完成。但是思考一下，会有什么问题？

我们是根据更新时间同步的，如果在一次增量更新期间，业务数据在批量插入并且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分钟内，数据的更新数量可能会相对较大。这种变化可能会带来以下问题：

  1. 内存溢出：大量的数据可能会导致程序中使用的缓冲区或内存溢出，尤其是当应用程序使用有限的内存来存储查询结果时。
  2. 性能下降：加载和处理大量数据会消耗大量的CPU时间。这可能导致服务器负载增加，响应时间变长。
  3. 网络拥塞：在网络传输大量数据时，可能会消耗大量的带宽，导致其他网络活动受到影响。
  4. 数据库压力增加：大量的数据读取会增加数据库I/O，可能导致数据库系统性能下降，进而影响到其他用户或查询。
  5. 程序处理慢：应用程序在处理、遍历或显示大量数据时可能会变得缓慢或卡顿。

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 如何应对解决

​ 说白了，就是解决《如何实现更新分页查询》的问题，下图就是实现更新分页查询的方案

在这里插入图片描述

​

海是鹏印出的影
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
基于Kettle工具的企业级数据同步方案
08-09
基于Kettle工具的企业级数据同步方案是用于处理分布式企业环境中数据流动的重要技术。Kettle，也称为Pentaho Data Integration (PDI)，是一款强大的ETL（Extract, Transform, Load）工具，广泛应用于数据仓库和...
大数据ETL同步工具演示教程
最新发布
04-10
### 大数据ETL同步工具演示教程 ...它们不仅能够满足企业级应用对于高效数据同步的需求，同时也通过友好的用户界面降低了非专业技术人员的操作难度。这对于推动大数据技术在更多场景中的广泛应用具有重要意义。
ETL与抖音数据同步，让数据流动无阻
RestCloud的博客
03-07 639
ETL作为数据集成平台，提供与抖音等数据源的无缝集成，简化数据交换和整合。
怎么用ETL工具实现MQ消息同步
RestCloud的博客
02-05 211
ETL工具对MQ消息同步的数据分析和挖掘。
使用ETL工具Kettle实现，把一个数据库中的多张表的数据同步到另外一个数据库中
qq_58476985的博客
06-14 3913
数据准备：db03数据库中准备3张表：student01、student02、student03（作为输入表数据）。出现圈3说明连接成功。之后建另一个数据库db03_class以及表db03_to_class（作为输出表）。同步成功后去虚拟机里对应数据库里，查看到对应表的数据，查看到，则同步成功。（依次点击：转换——>主对象树——>DB连接——>点击新建——>测试）加入转换节点：转换——>插入/更新——>按住shift建立连接。加入输入节点：转换——>输入——>表输入。其余两张表同上所示操作即可。
ETL工具(数据同步)
qq_36602951的博客
07-20 3677
Kettle做跨库数据迁移工作
kettle简单的ETL抽取同步两个库之间的数据
brrby
01-10 1343
kettle的简单使用
跨数据库同步方案汇总
热门推荐
jnrjian的博客
04-04 1万+
Datax 一般比较适合于全量数据同步，对全量数据同步效率很高（任务可以拆分，并发同步，所以效率高），对于增量数据同步支持的不太好（可以依靠时间戳+定时调度来实现，但是不能做到实时，延迟较大）。 Canal 、databus 等由于是通过日志抓取的方式进行同步，所以对增量同步支持的比较好。 OGG 太贵 一、早期关系型数据库之间的数据同步 二、大数据时代下的数据同步 三、总结 一、早期关系型数据库之间的数据同步 1）、全量同步 比如从oracle数据库中同步一张表的数据到My
ETL工具kettle实现数据同步
kay'blog
12-19 4802
ETL工具kettle实现数据同步 摘要: 这次记录是因为前段时间公司新开的项目,新建了一个数据库,有部分数据在新的项目中用的到,需要从原来的数据库中同步过来, 原来的做法是在项目中使用task定时任务来定时从原来的数据库中拉取数据,但是在一个项目中实现跨数据源是一个很烦凡人的事请而且代码实现方式效率不高还可能遇到其他一些问题:比如项目挂了那你的定时任务自然也挂挂了,后台项目组长让使用ETL工具实现同步,找了半天找到一个开源免费的工具Kettle. 前提: 我使用的window如果你的linux版本可以自
ETL – ETL工具介绍
01-20
在现代企业中，数据来源多样，可能来自多个数据库、文件系统、API接口等，ETL工具能有效地整合这些异构数据，保证数据的一致性和完整性。 1. DataX：由阿里巴巴开源，它是一个支持多种异构数据源之间同步的工具，...
深入理解otter，利用OTTER实现准实时ETL、数据同步
07-04
深入理解otter，利用OTTER实现准实时ETL、数据同步 1. 中美同步需求 2. otter架构&设计 如何解决"差"网络 如何避免双向回环 如何处理数据一致性 如何高效同步数据 如何高效同步文件 如何支持系统HA 如何处理特殊业务同步 如何处理机房容灾 3. 相关产品对比 4. 其他
Kubernetes与微服务容器化方案
12-28
docker与kubernetes的培训PPT；涉及大型系统演进、系统现状分析、docker与k8s的基础与高级操作、系统后续改进方案与建议
ETL数据同步问题.doc
01-27
ETL 数据同步 五种方案 ETL 数据同步 五种方案 ETL 数据同步 五种方案
Kettle数据库同步工具详细设计-最新
09-13
是我的“Kettle数据库同步源码”对应的详细设计文档； 包含流程图 + 数据表结构设计！
kettle3开源的ETL工具
01-16
Kettle3是一款强大的开源ETL（提取、转换、加载）工具，它被广泛应用于数据集成和数据清洗领域。ETL是数据仓库系统的核心...无论是在企业级数据仓库建设，还是在日常的数据分析任务中，Kettle3都能发挥出显著的作用。
高效稳定！使用ETLCloud轻松同步千万数据
ETLCloud的博客
01-19 538
高效集成ETL工具
ETL工具(数据同步) 二
qq_36602951的博客
07-24 1207
kettle使用掌握之后，还需要掌握在linux下的定时执行的策略
使用Helm在Kubernetes部署DataEase（全组件模式）
范一刀的博客
06-16 771
此安装包支持选择部署模式：“精简模式” 和 “集群模式”；精简模式下仅部署dataease和MySQL，集群模式下将部署dataease、doris-fe、doris-be、kettle、mysql。 Doris在Kubernetes中的部署方式为 hostNetwork，PodIP 即节点 IP，如此可避免BE节点重启BEIP发生变化需重新ADD BACKEND，保证了Doris服务的连续性。............
Kettle在企业级数据同步中的优化策略与问题解析
在企业级数据同步中，Kettle作为一个强大的ETL（提取、转换、加载）工具，被广泛应用于数据迁移和整合。它支持分布式部署，适用于处理大量数据的实时同步需求，尤其是在数据更新时间允许重复，且对实时性要求较高的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值