数据迁移工具kettle使用详解

由小麦苗 · 发布日期 2022年10月26日 · 已更新 2023年2月6日

2 387 1

👉 本文共约11373个字，系统预计阅读时间或需43分钟。

使用kettle实现Oracle到Oracle的数据同步

ETL技术是我们经常使用的数据库技术，大家可以通过很多种方式来实现。在大中型系统中，我们常用Oracle Data Integrator和Oracle GoldenGate来实现。但对于小型系统，大家认为简单易用的就是使用开源的Kettle，通过可视化的界面进行拖拽，轻松便捷地实现ETL。今天我们就和大家一起从安装开始，使用Kettle进行数据同步。

在今天的实验中，我们有3台独立的服务器，用途和分工如下：

主机1：源数据库：Oracle Database 19.5，数据库名称，orclpdb1
主机2：目标数据库：Oracle Database 19.5，数据库名称，orclpdb1
主机3：运行Kettle的Windows环境

Kettle可以运行在Windows、Linux以及Mac上。

第一步：安装JDK

今天我们以Windows作为Kettle运行的环境，在安装Kettle之前，需要安装JDK和JRE。您可以来到https://www.oracle.com/technetwork/java/javase/downloads/index.html下载适合您操作系统的JDK。在本实验中，我们将JDK安装在C盘下，路径如下：

在安装的过程中会提示我们安装JRE，我们也选择在C盘安装。

第二步：设定环境变量

首先设定用户环境变量，将JDK下面的bin加入到Path当中。

然后设定系统环境变量，加入JAVA_HOME，将JDK的路径加入其中。

设定系统环境变量CLASSPATH，将

.;%JAVA_HOME%\lib;%JAVA_HOME%\lib\tools.jar加入其中。

在命令行当中测试一下，看看java是否好用。

第三步：下载并解压并启动Kettle

您可以在

https://community.hitachivantara.com/s/article/data-integration-kettle下载Kettle

下载之后，执行解压文件夹下的Spoon.bat文件即可启动Kettle。Kettle的启动时间可能会稍微长一些，一般是因为JAVA的虚拟机配置的问题，您可以通过修改Spoon.bat这个文件中关于JAVA虚拟机的配置来调整Kettle的性能。比如修改这个文件中的如下代码：

if "%PENTAHO_DI_JAVA_OPTIONS%"=="" set PENTAHO_DI_JAVA_OPTIONS="-Xms512m" "-Xmx512m" "-XX:MaxPermSize=256m"

第四步：安装Oracle数据库驱动

Kettle自带了很多数据库的驱动，但是Oracle的数据库驱动没有在其中，我们需要下载与Kettle相匹配的数据库驱动，我们今天使用的数据库是Oracle Database19.5，但是通过测试发现，8.3的Kettle并不支持最新版的数据库驱动，所以我们下载Oracle Database 12c（

https://www.oracle.com/database/technologies/jdbc-drivers-12c-downloads.html

）的Java数据库驱动，并将它放在Kettle安装路径下的lib路径下。按照下图提示，将压缩文件下载之后，将里面的jar文件，放入Kettle安装路径下的lib当中。

数据迁移工具kettle使用详解

第五步：创建JNDI

连接数据库的方式有很多，在连接PDB的时候，我个人比较喜欢使用JNDI的方式进行连接，这种连接方式，简单明了。在Kettle的安装路径下，会找到simple-jndi路径，里面有一个jdbc.properties文件，编辑这个文件即可。

在今天的操作中，我们有两个数据库，我们给他们起名字为s_database和t_database，分别代表源数据库和目标数据库，请大家注意观察jdbc.properties文件中的写法。

第六步：创建数据库连接

来到Kettle双击下图中的（2）所示的Transformations，Kettle会自动创建一个Transformation，但是页面会直接切换到Design，我们要手动按照下图的操作，切换回VIEW，并创建数据库连接。

按照下面的图中的顺序创建s_database和t_database两个数据库连接。如果没有问题，将看到下图中（8）所示的连接成功提示。

第七步：设定源数据表

我们在s_database数据库中的chatbot这个schema当中，有个表叫做st001，数据有107条，有11个字段。

我们来到Kettle当中，按照下图所示，在input当中找到table input，将它拖拽到右侧空白处。

接下来，配置这个table input，双击它即可。我们只想同步employee_id,first_name和last_name这三个字段并且department_id为30的数据。

通过按Preview按钮，可以看到具体的查询结果。

第八步：设定目标数据表

我们在t_database数据库的HR schema下面有一张表，叫做tt001，结构和数据如下。

在Kettle当中将output当中的Table output拖入右侧空白处。

双击刚拖入的Table output图标，对它进行配置。

按住shift键，然后点击Table input，屏幕上会出现一个箭头，将箭头指向table output。

第九步：启动同步

按下图中红色箭头所指的图标，启动数据同步。

点击上面的图标之后，会出现下方的对话框，我们点击run即可。

按照要求，我们要将这个项目先保存，然后才可以执行。

执行之后的结果如下，在下方可以看到日志，如果有错误，会提示错误原因。

第十步：验证同步结果

我们发现原表中的数据已经同步过来了。

今天为大家只是做了一个简单的例子，更多信息可以访问Kettle官网获取。Kettle是一个基于Java的工具，在使用的时候，性能上可能会存在问题，对于中小型应用还可以，如果是大型系统，还是使用ODI和Oracle GoldenGate比较好，毕竟ODI结合OGG是企业级ETL解决方案。

其它同步配置

单表同步

参考：https://www.cnblogs.com/nothingonyou/p/11942461.html

配置kettle转换

双击打开spoon.bat，在左侧主对象菜单栏，新建一个转换，命名为o2m，如下图：

配置oracle和mysql连接

在DB连接一栏，右击新建连接，配置oracle连接:

点击测试，显示成功即为配置完成

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

配置mysql连接:

点击测试，显示成功即为配置完成

配置字符集

迁移时要确认两边数据库使用的是哪种字符集，不然可能会出现迁移后出现乱码的问题，我这里oracle是16GBK,mysql是UTF-8，UTF-8兼容16GBK，故没有配置。

配置迁移步骤

在左侧菜单栏【转换】里面，选择【核心对象】，【输入】一栏接着双击【表输入】，或者选中将【表输入】拖拽到右侧空白区域。

双击你拖进来的【表输入】，修改“步骤名称”，选择源数据，点击获取【获取SQL查询语句】，选择你想同步的表，点击确定后就可以了。当然也可以自己写sql语句，我这里是自己写的sql，点击【预览】可以查看要迁移的数据。

接下来配置表输出，在左侧菜单栏【转换】里面，选择【核心对象】，【输出】一栏接着双击【表输出】，或者选中将【表输出】拖拽到右侧空白区域。

单击【表输入】，按shift键连接【表输入】，建立起【表输入】和【表输出】的连接，如图：

双击【表输出】，配置要迁移的目标表，我这里两边数据库字段一样，故而可以不用修改，如图：

ctrl+s保存为o2m.ktr文件，到这里一个简单的转换就配置完成了。

2.4 运行转换

手工运行作业，点击下图红色圈圈里面的按钮。

点击启动，日志级别这里可以选择错误日志，这样只记录错误信息

转换输出栏如下图：

最后检测两边表，如记录数count(*)、表中数据对比等没问题的话表示转换完成。

文本文件输入和输出

文本文件输入参考：https://blog.51cto.com/51power/5222035

文本文件输出参考：https://blog.51cto.com/51power/5248688

csv文件输入：https://blog.51cto.com/51power/5240687

可通过此组件读取大量不同的文本文件，可指定读取的文件列表，或者用正则表达式表示的目录列表，适用于读取txt与csv文件。

可以用指定正则表达式通配符的形式来搜索文件。正则表达式比简单的用"?"和"*" 通配符更有效。

文件名	正则	选择的文件
/dafeige/	.userdata./.txt	所有在/dafeige/目录下的并且文件名包含userdata、以txt为后缀的文件。
/dafeige/	AAA.*	所有在/dafeige/目录下的并且文件名以AAA 开头的文件。
/dafeige/	[A-Z][0-9].*	所有在/dafeige/目录下的并且文件名以字母开头、紧接着一个数字的文件。

下面是标签的选项列表

选项	描述
文件类型	可以是CSV 或者Fixed length(固定长度)。
分隔符	在文本的单行中，一个或多个字符将被用来分隔字段，比较有代表性的是;或者一个tab 制表符。
文本限定符	一些字段能够被一对允许分隔的字符来封闭。封闭字符串是可选的。
逃逸字符	如果你的数据中有逃逸字符，就指定逃逸字符（或者逃逸字符串）。如果\作为逃逸字符，文本’Not the nineo\’clock news.’(’作为封闭字符)，将被解析成Notthe nine o’clock news.
头部/头部行数量	如果你的文本文件有头部行就使用这个。你可以指定头部行出现的次数。
尾部/尾部行数量	如果你的文本文件有尾部行就使用这个。你可以指定尾部行出现的次数。
包装行/包装行数量	利用这个来处理被某些页限制包装的数据行。注：头部和尾部从来不考虑被包装。
分页布局/每页行数/文档头部行	在行打印机上打印的时候，你可以用这个选项作为最终的手段。用头部行的数量来跳过介绍性的文本，用每页的行数来定位数据行。
压缩	如果你的文件是ZIP 文件或者GZIP 归档文件，就启用这个。注：此刻归档文件中仅仅第一个文件被读取。
没有空行	不往下一步发送空行。
文件名字段名称	包含文件名的字段名称。
输出包含行数	如果你想行数作为输出的一部分，可以启用这个。
行数字段名称	包含行数的字段名称。
根据文件获取行数	允许每个文件重置的行数。
格式	可以是DOS、UNIX 或者混合模式。UNIX 行终止可以是回车，DOS 中可以是回车或者换行。如果你选择混合模式，将不会验证。
编码方式	指定文本文件编码方式。如果不设置就使用系统默认的编码方式。如果想用Unicode，可以指定UTF-8 或者UTF-16。第一次使用的时候，Spoon 将搜索系统，寻找可用的编码。
记录数量限制	设置读取记录的行数。0 代表读取所有的。
解析日期时是否严格要求	如果你想严格的解析数据字段，可以禁用这个选项。如果启用的时候，Jan 32nd 将变成Feb 1st。
本地日期格式	在本地日期常常被解析为“February wnd,2006”的形式，在用法语本地化的系统中日期将不会被解析，因为在法语本地化中February 不能理解。
添加文件名	如果你想文件名作为输出的一部分，可以启用这个。

下面是标签的选项列表

选项	描述
忽略错误	如果在解析的时候忽略错误，就指定这个选项。
忽略错误文件	使用这个选项来跳过那些出现错误的文件。
错误文件字段名	在输出流行中增加一个字段，这个字段将包含错误发生的文件字段名。
文件错误信息字段名	在输出流行中增加一个字段，这个字段将包含错误发生的文件信息
跳过错误行	使用这个选项来跳过那些出现错误的行。你可以生成另外的文件来包含发生错误的行号。如果不跳过错误行，解析错误字段将是空的。
错误计数字段	在输出流行中增加一个字段，这个字段将包含错误发生的行数。
错误字段文件名	在输出流行中增加一个字段，这个字段将包含错误发生的文件名。
错误文本字段名	在输出流行中增加一个字段，这个字段将包含解析错误发生字段的描述。
告警文件目录	当警告发生的时候，它们将被放进这个目录。文件名将是<警告目录>/文件名.<日期时间>.<警告文件扩展>。
错误文件目录	当错误发生的时候，它们将被放进这个目录。文件名将是<错误文件目录>/文件名.<日期时间>.<错误文件扩展>。
失败行数文件目录	当解析行的时候发生错误，行号将被放到这个目录。文件名将是<错误行目录>/文件名.<日期时间>.<错误行扩展>。

下面是标签的选项列表

选项	描述
过滤字符	搜索字符串。
过滤位置	在行中过滤字符串必须存在的位置。0 是起始位置，如果你指定一个小于0 的值，过滤器将搜索整个字符。
停止在过滤器	如果你想在文本文件遇到过滤字符的时候，停止处理，就指定Y。
积极匹配	是: 将符合过滤器的数据保留, 否: 不保留符合过滤器的数据

下面是标签的选项列表

选项	描述。
名称	设置要在输出流中显示的字段名称。
类型	字段类型（String、Date、Number 等）。
格式	控制输入数据的格式(整数、有小数位、日期格式等)
位置	不用管它
长度	对于Number：有效数的数量。对于String：字符的长度。对于Date：打印输出字符的长度（例如4 代表返回年份）。
精度	对于Number：浮点数的数量。对于String,Date,Boolean：未使用。
货币类型	用来解释如$10,000.00 的数字。
小数	小数点可以是”.”(10;000.00)或者”,”(5.000,00)。
分组	分组可以是”.”(10;000.00)或者”,”(5.000,00)。
Null If	空值如何处理。
默认	字段为空的时候的默认值。
去空字符串	处理之前先去空。
重复	Y/N:如果在当前行中对应的值为空，则重复最后一次不为空的值。

下面是标签的选项列表

选项	描述
文件名字段	包括文件名称以及扩展名，以及文件路径的整体
扩展名字段	仅仅包括文件名称以及扩展名称
路径字段	仅仅包括文件的路径
文件大小字段	大小
是否为隐藏文件字段	是否隐藏
最后修改时间字段	最后一次此文件的修改时间
Uri字段	文件/目录的绝对路径
Root uri字段	根路径

表输入和输出

https://blog.51cto.com/51power/5223783

https://blog.51cto.com/51power/5256649

“表输入”的意思是把数据从数据库中读取到kettle中。

选项说明：

选项	描述
步骤名称	步骤的名称，在单一的步骤中，名称必需唯一。
数据库连接	读取数据的DB连接。
编辑	编辑选中的DB连接信息
新建	创建DB连接
Wizard	以向导的方式，创建DB连接
SQL	SQL 语句用来从数据库连接中读取数据。
获取SQL查询语句	通过选择数据库中的数据表生成默认的SQL语句
允许简易转换	大字段的延迟转换(延迟转换性能更高，采用byte方式处理，否则就是string方式)。勾选"允许简易转换"后，可以避免不必要的字段的数据类型转换，从而提高性能。但会有机率出现中文乱码。
替换SQL语句中的变量	如果需要传入参数变量则勾选上
从步骤插入数据	如果该组件有上一步骤，且需要从该步骤中获取变量作为参数则选中上一步骤。
执行每一行	如果上一步骤是一个集合且该表输入需要遍历集合进行查询则勾选上。
记录数量限制	限制要查询的数据记录数，0表示没有限制。

create table EMP
(
 EMPNO    NUMBER(4),
 ENAME    VARCHAR2(10),
 JOB      VARCHAR2(9),
 MGR      NUMBER(4),
 HIREDATE DATE,
 SAL      NUMBER(7,2),
 COMM     NUMBER(7,2),
 DEPTNO   NUMBER(2)
);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);
commit;

create table EMP

(

EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2)

);