kettle 多表全删全插同步数据 两种方案

时间:2024-02-20 15:35:37

背景:

接到上级指示,要从外网某库把数据全部导入到内网,数据每天更新一次即可,大约几百万条数据,两个库结构一样,mysql的,两台数据库所在服务器都是windows server的,写个java接口实现下吧,给了一个外网数据库信息,好了,给你3天时间,开始搞吧。

分析:

用java接口写逻辑?不好意思,基本没思路,大神就不要喷我了。前公司的数据中台的数据都是通过kettle定时抽取的,虽然暂时我还不知道是个什么鬼,但总比me思路强,于是果断舍弃java接口,全面百度kettle,为了工作啊……

准备工作:

先不说抽取逻辑,咱得先把工具部署在自己电脑跑起来对吧,所以,准备下载部署开搞,不过好像kettle改名了,叫什么pdi……
kettle下载部署,官网上我找了好长时间不知道咋下载免费的,所以找到了这个网站https://sourceforge.net/projects/pentaho/files/Data%20Integration/ 我用的是7.1版本的,点击7.1,把zip压缩文件下载下来。
image
这个直接解压就可以,无需安装。
注意:1.先不急打开程序,kettle是纯java编写的,所以kettle工具的使用,必须要有java环境,尽量使用jdk1.8,因为别的版本我不确定会不会有问题,至于怎么安装jdk1.8和配置环境,请各位大佬自行百度。
2.根据百度经验,还有点问题需要自行处理,就是数据库的驱动包问题,因为我这只涉及mysql所以也就只对mysql的驱动包进行操作,其他数据库驱动连接是否有问题,不确定。
mysql官网驱动包下载地址:https://dev.mysql.com/downloads/connector/j/
image
点击历史版本,不要下最新版本或者比较高版本,为啥?因为不支持……
继续操作
image
最高选用5.1.49版本,接上一话题,为啥不用再高版本,因为你可以下载下来对比下,5.1.49的解压文件中,是有两种jar包的,
image
带bin的一定要有,没有就连不上数据库,5.1.49版本之后就没有带bin的了,不信你去看看。
把这两种jar包直接放在以下路径就好:
image

方案一:

在一个作业中使用多个转换,效果图:
image
当然这种方案的效果并不是都是这种的,但原理都是一样的,有可能是所有的转换是串行顺序执行的,一条流程走完,那样会造成其中某个转换有问题,就不往后执行了,我这种虽然看着密集了些,但不会因为某个表转换出错而停止。
接下来就是具体操作流程:

第一步:

创建转换,点击“文件”,点击“新建”,点击“转换”,建立转换空白页
image

第二步:

配置来源库和目标库,如下:
image
image
因为要配置来源库和目标库,所以以上操作需要再重复一遍

第三步:

建立转换逻辑脚本的内容:
表输入:
image
双击表输入:
image
点击确定后,弹出:
image
我选的是,效果:
image
最后点击确定即可;
使用插入/更新:为啥不用表输出呢,因为表输出没法更新,还有主键的一些限制,所以用插入/更新,可以先用这个,不行你再百度其他的……,反正我用的还不错。
image
image
一定要先建立上它俩之间的流程关系,再编辑插入/更新,否则,你在编辑插入/更新时,会在获取字段和获取映射关系时,没有反应……
接下编辑插入/更新
image
然后保存转换脚本,
image
我得是直接保存到桌面了,供演示用。
现在这一个转换脚本就完成了,因为是多表同步数据,所以你要把第三步重复执行多边,最后是一个数据库表对应一个转换文件,效果:
image
转换文件的全路径,尽量也是英文的,不要用中文的。

第四步:

建立定时作业任务,每天执行一遍数据抽取。
建立作业:
我参考了此篇博客:https://www.jianshu.com/p/bbc528a66b99
image
建立作业流程:
image
双击START按钮:
image
重复要勾选上,否则只执行一次哦,然后根据需求,选择定时类型,最后点击确定即可。
双击转换:
image
然后又是重复操作,再把一个转换框拖到空白面板,然后设置好流程控制,然后编辑转换,循环,最后就成了我一开始的效果图了。
最后作业也要保存起来,会生成一个kjb文件,保存好。
image
点击启动按钮,让他自己执行就好了,窗口可以最小化,但是不能关闭!!!!!!!!!!!!!!!!!!!!!!!!
关闭就不定时执行了。

方案二:

如果是上百张表,那第一种方案就不行了,需要更为便捷的方法,所以我参考的帖子:
https://www.cnblogs.com/dion-90/articles/8746184.html
https://blog.csdn.net/qq_35318838/article/details/53322530
https://www.freesion.com/article/3073451055/
以上文章,提供了具体思路和详细操作步骤,但是我按照帖子来,却怎么也实现不了,一直报错,解决不了,于是我就做了一下小改动,成功运行,数据也已经都抽取过来了,但spoon的详细日志里边会时不时的碰到一条数据插入有问题,主键冲突,是那张表的最后一条数据,不知道为何,还没研究,总之这种方案目前能用,至于稳定性和准确性,因为我在线上用的第一种方案,所以我也没法说好不好,各位大佬可以发挥一下……
好的,先上我得最终效果图:
image
所以我们分步骤实现,最后整合到一起。

第一步:

获取表名:
本质是一个转换,所以,点击“文件”,点击“新建”,点击“转换”
然后进行下列操作:
image
image
image
然后三者之间建立流程关系:
image
配置转换的数据库配置,因为这个转换的主要目的是获取哪些数据库表要进行数据同步,又因为我来源库与目标库数据库结构都一样,所以这个地方配置哪个库是都可以的,各位如果有自己需求的话,自己根据实际情况选择。
image
双击表输入,进行编辑:
image
双击字段选择,进行编辑:
image
复制记录到结果,这个不用编辑。
然后保存这个转换文件,改一下转换名,方便记

第二步:

数据抽取:
建立一个转换,点击“文件”,点击“新建”,选择“转换”
配置数据库
image
建立逻辑:
image
image
双击表输入:
image
双击表输出:
image
两个流程之间,建立流程关系,从表输入到表输出。
保存此转换文件,命名自定义,方便即可。

第三步:

开始整合,建立一个作业,点击“文件”,点击“新建”,点击“作业”:
然后,把作业的开头建立
image
双击START,进行编辑:
image
获取表名:
建立转换,编辑转换,建立流程控制
image
image
image
设置变量:
建立脚本,编辑脚本,建立流程控制
var prevRow=previous_result.getRows();
if (prevRow == null &&(prevRow.size()=0)){
false;
}else{
parent_job.setVariable("tables", prevRow);
parent_job.setVariable("size", prevRow.size());
parent_job.setVariable("i", 0);
parent_job.setVariable("TABLENAME", prevRow.get(0).getString("tablename",""));
true;
}

image
image
image
检验字段的值:
建立流程,编辑流程,建立流程控制
image
image
image
清除目标库表数据:
本来这一步不是单独的一步,看过之前我说的那几篇参考博客就知道,这一步应该是嵌套在数据抽取那个转换的,但我为什么给单独拿出来了呢,原因很简单,就是我放在那里边,整体运行时报错,想了一上午没想明白,就另辟蹊径,单独做了一步,这样就能整体运行了。
建立sql运行:
建sql脚本,编辑脚本,建立流程控制
truncate table ${TABLENAME}
image
image
image
抽取数据转换:
建立转换,编辑转换,建立流程控制
image
image
image
判断变量:
建立脚本,编辑脚本,建立流程控制
var list_Tables =parent_job.getVariable("tables").replace(" ","").replace("[","").replace("]","").split(",");
var size = new Number(parent_job.getVariable("size"));
var i = new Number(parent_job.getVariable("i"))+1;
if(i<size){
parent_job.setVariable("TABLENAME", list_Tables[i]);
}
parent_job.setVariable("i",i);
true;
image
image
image
点击确定即可
image
添加两个分支,不知道干啥的,这个随意,我试过,不加也行……
image
可以改名,自定义的。
保存此作业,会生成一个kjb文件。
运行:
image
还是强调一遍,作业启动之后,不要关窗口,否则就不执行了!!!!!!!!!!!!!!!!

结语:

以上就是多表数据同步的两种方案,有不对的地方请指教,我也是萌新啊……