ETL工具：kettle的简单使用

史文豪
455 阅读6分钟

kettle是etl的开源工具，纯java语言开发，如果有实时性要求不高的数据同步工作，可以采用kettle进行定时同步

kettle安装和搭建

创建java的运行环境

解压jdk1.8jar包（注意解压目录避免中文以及特殊字符）

image.png 解压完成后，测试

在bin目录下输入cmd进入dos命令行，输入javac

image.png 如图即安装成功

全局使用，配置JAVA＿HOME的环境变量

计算机 -- 右键 -- 属性 -- 高级系统设置 -- 环境变量

image.png 在系统变量中，选择新建，将jdk安装目录复制过来，确定

image.png

在 path 中 配置 %JAVA_HOME%\bin

image.png 测试：在桌面win+r输入cmd，启动dos命令提示符，输入javac，无误即安装成功

kettle的安装

解压即安装

解压后，根目录下点击spoon.bat

image.png 启动后界面如下

image.png

问题解决：

spoon.bat如果启动不了，检查java环境变量

如果无误，可能是电脑配置较低，不满足kettle默认的最低内存需求

需要修改Spoon.bat/spoon.sh参数

if "%PENTAHO\_DI\_JAVA\_OPTIONS%"=="" set PENTAHO\_DI\_JAVA\_OPTIONS="-Xms1024m" "-Xmx2048m" "-XX:MaxPermSize=256m"

资料下载地址

kettle官网各个版本下载：sourceforge.net/projects/pe…

jdk官网下载：www.oracle.com/java/techno…

demo1：excel表到数据库

需求：

实现多表数据组合输入到数据库

image.png

image.png 将两张表的数据根据userID组合成一条传入mysql数据库

作业图设计

新建一个转换，选择excel输入和表输出

image.png

image.png

image.png

image.png

excel输入配置

文件选择

image.png

表和字段的选择

image.png

表一建立后，建立表二

流查询配置

建立流查询，根据userID实现两张表间的数据对应

image.png

表输出配置

点击表输出，配置数据库连接

连接数据库时，需要选择对应的数据库，配置数据库的连接参数

image.png

连接测试失败，需要将对应的数据库驱动包放到对应的文件夹lib下

image.png

各个数据库都有对应的驱动包，Mysql、sqlserver和Oracle

再次测试连接成功

添加字符集参数

image.png 数据库输出的字段设置

image.png

image.png

image.png

运行作业

image.png

保存

新建一个作业，构建一个start和一个转换，将转换保存的ktr文件存入转换中

image.png

执行保存即可

image.png

插入成功

demo2：实现数据库间的多表转换

作业图设计

建立表输入和表输出

image.png 配置数据的连接(见上页，注意配置字符集)

多表作业向导构建

点击工具，选择向导，选择多表向导

image.png

执行测试

image.png

image.png 多表传输成功

sql条件限制

使用sql限制语句输入

image.png

执行

image.png

迁移数据预览

预览传输的数据表

image.png

迁移实战

项目要求实现oracle数据库的数据迁移到mysql数据库,现连接局域网的Oracle数据库和本机的mysql数据库做案例测试

单表测试

使用标准库中的生产设备设施使用状况反馈表进行测试,迁移来源库为天津完整性的对应表字段内容

image.png 先做表输入和表输出的作业图连接

image.png 其中连接oracle数据库需要注意配置

image.png

连接不同数据库的驱动包问题

由于kettle连接Oracle的方式是jdbc连接,需要对应的驱动jar包

Oracle11g的驱动jar包是ojdbc-10.2.0.4.0jar

oracle12c的驱动jar包是ojdbc7.jar

Oracle的jar包下载官网:www.oracle.com/technetwork…

项目中还有SqlServer的数据库使用,也需要下载对应的jar包

驱动包置入lib文件夹,重启即可

测试,连接成功

image.png 需要注意的是Oracle连接和mysql不一致

连接Oracle的默认url是:[jdbc]:[oracle]:[thin]:[@host:port:SID]

sid也可以是server_name,就是实例名,默认都是ORCL

连接mysql的url是:@host:port:数据库名

继续配置mysql连接,测试也成功

image.png

sql限制迁移字段

在天津完整性中找到需要迁移到标准库的字段,写select查询语句,使用navicat先进行sql测试

select BRANCH,CUSTDEPTID,FACILITIES,PRODUCTIONTIME,PROFESSIONAL, SYS,DESCRIPTIONS,REASON,SUGGESTIONS, REASONTYPE,PROBLEMSSTAGE,STATUS,IMPORTANCE,CONTACT from C##TJFIMS.FIMS_SSQK_FEEDBACK

image.png

sql正确,输入到数据源限制sql中

image.png

需要注意这里的sql语句不允许换行,否则报sql语句错误

手动对应字段映射关系

建库脚本已经在本地mysql运行了,标准库的数据类型和字段长度建库时设置好即可

image.png 按照标准库的字段映射关系进行手动字段绑定

image.png 建设好字段后初步测试

image.png 可以看见对应的字段数据已经传输到标准库中

image.png

字段选择问题 数据传输成功了,但是kettle不同类型数据库进行数据传输时,采用的是默认方式进行类型转换

经查看数据源中的大量数据都是null,如果不进行数据转换会报传输错误

解决方案:加入字段选择,进行元数据的类型或者字段的手动控制

image.png

在元数据上进行字段转换

image.png

天津完整性的表字段先不进行字段转换

重复插入问题

测试后发现多次运行,会发生重复插入数据问题

解决方案:数据迁入之前先对表空间进行清空

image.png

在目标库的脚本中写sql语句进行该表的清空处理

sql语句:

truncate table jx_peq_equi_use_feedback_table;

image.png

解决主键id自增问题

插入时想要获取主键值,但是数据源的主键都是空值,要保证插入到标准库的数据主键唯一且自增

解决办法:使用kettle生成一个随机数,限制字段格式和内容

image.png 为迁移的数据拼一个主键字段,设置加密方式和主键字段名

image.png 在表关系中添加主键字段的映射关系

image.png 测试 image.png

解决多表字段拼表问题

有业务需要将数据源的a表和b表的关联数据共同插入到目标库的c表中

解决:使用共同关系字段建立流查询,拼表后再进行输出

image.png

还是以生产设备设施使用状况反馈表为例,现要将天津完整性和深圳完整性的数据字段进行拼接

先建立了深圳完整性表输入,编写了sql语句,获取了深圳完整性的作业区id和所属公司两个字段

通常流查询是使用id作为查询字段,但是此表的id均为空值,为了演示使用作业区id

image.png

以深圳完整性查询到的字段作为样本,作业区id为查询条件,查到的所属公司就是所需要的字段内容

在输出中进行字段映射的更改

image.png

即可完成天津完整性和深圳完整性的数据内容拼表到标准库

解决多表迁移的全局控制

标准库目前一共有91张表,要完成传输,不能单表传输,也要实现全局控制

可以使用作业图控制流程

image.png

在转换中添加单表的转换作业

image.png

实现线性的作业流程,也可以实现并行的流程设计

image.png

也可以在流程中加入检验步骤,检测目标表是否存在,防止多表作业时报错

image.png

采用定时管理

双击start按钮,进入定时设置中

image.png

定时任务调度一般时配合实时同步更新使用

还有很多细节问题有待深入学习

avatar
文章
阅读
粉丝