学习
实践
活动
工具
TVP
写文章

ETL-KETTLE工具使用

KETTLE 工具使用

1 介绍

1.1 ETL

ETL,是英文 Extract-Transform-Load 的缩写,用来描述将数据从来源端经过抽取(extract)、转换(transform)、加载(load)至目的端的过程。ETL 一词较常用在数据仓库,但其对象并不限于数据仓库。

ETL 是构建数据仓库的重要一环,用户从数据源抽取出所需的数据,经过数据清洗,最终按照预先定义好的数据仓库模型,将数据加载到数据仓库中去。

1.2 KETTLE

Kettle 是一款国外开源的 ETL 工具,纯 java 编写,可以在 Window、Linux、Unix 上运行,数据抽取高效稳定。中文名称叫水壶,该项目的主程序员 MATT 希望把各种数据放到一个壶里,然后以一种指定的格式流出。

Kettle 这个 ETL 工具集,它允许你管理来自不同数据库的数据,通过提供一个图形化的用户环境来描述你想做什么,而不是你想怎么做。

Kettle 中有两种脚本文件,transformation 和 job,transformation 完成针对数据的基础转换,job 则完成整个工作流的控制。

1.3 PENTAHO SERVER

Pentaho 是基于 Java 平台的商业智套件,之所以说它是套件是因为它包括一个 Web Server 平台和几个工具软件:报表、分析、图表、数据集成、数据挖掘等。功能强大,需要付费,我们在这里只将它用作是资源库,用来存储、发布、运行、管理客户端编辑的转换或者作业。

2 安装

2.1 下载安装包

下载客户端地址:

https://sourceforge.net/projects/pentaho/files/Data%20Integration

下载服务端地址:

pentaho 各版本官方文档:https://help.pentaho.com/Documentation pentaho

社区版合集下载:https://sourceforge.net/projects/pentaho/files/

2.2 安装环境

如介绍所说,kettle 是纯 java 编写,故需要安装 jdk 才可以运行,但这里不做 java 的安装说明。Pentaho Server 同样需要 jdk,且内嵌 tomcat 默认端口为 8080.

2.3 安装

①客户端:将下载的文件 pdi-ce-kettle-7.1.0.0-12.zip 解压到任意目录下,双击 data-integration 目录下的 spoon.bat 即可运行 kettle 工具。

②服务端:直接解压下载文件到任意目录,点击根目录下的 start-pentaho.bat 启动即可,启动以后可以在浏览器中输入http://server-ip:port/pentaho来访问管理界面,客户端也可以用此地址来进行资源库连接。

注:在 data-integration\samples 下有许多组件使用示例,可以参考学习。

2.4 界面介绍

客户端:

服务端:

3 客户端重要组件介绍

3.0 主对象树/核心对象

主对象树种显示的转换或者作业的信息,包含的数据库连接和步骤,子服务器等;

核心对象中显示的是所有的转换或者作业可用的组件。

3.1 输入

输入包含多个输入组件,用于从不同的数据源输入数据,类如 CSV,Excel,文本文件,系统信息,配置文件,随机数,表输入等。下面重点介绍部分组建

3.1.1 生成记录

默认生成有限条记录,如果勾选了 Never stop generating rows 选项,将持续生成记录,并记录每行的生成时间,还有前一行的生成时间???

记录的字段自定义,有名称类型精度值等等,完成之后可预览生成的记录。

3.1.2 生成随机数

可以选择要生成的随机数类型,生成的随机数将加入到流中的每行记录中。

3.1.3 获取系统信息

获取系统日期,特定时间,转换的信息,主机名,kettle 版本,jvm 信息等。

3.1.4 表输入

表输入组件用于读取已经建立连接的数据库表或视图,利用 sql 获取数据,支持变量替换,可以从之前的步骤中插入数据。

3.1.5 配置文件输入

这个步骤用于读取 java 配置文件(.properties),文件选项卡李曼可以配置文件的路径,可以输入多个配置文件,内容选项卡中用来配置文件类型,编码等。字段选项卡中配置读取的字段加入流中。

3.2 输出

3.2.1 删除

选择数据库连接和目标模式、目标表,“查询值所需的关键字”,根据这个选项中配置的条件进行删除值,即如果满足了各个表字段和流字段的比较符关系,将删除该行数据。

3.2.2 插入/更新

这个步骤首先用查询关键字查询表里的每一行,如果没有匹配到就插入新的一行,如果匹配到了并且更新的字段值不一样,就更新这一行数据,如果更新的字段值也一样,那什么也不会做。

3.2.3 数据同步

该步骤经常用于合并记录之后,合并记录步骤会为每行数据追加一个标志位,这个标志位有四中值:"identical", "changed", "new"或者"deleted"。这个标志位将被数据同步这个步骤用来增删改数据库表。

3.2.4 更新

这个步骤与“插入/更新”步骤类似,只不过只关心查询关键字的值相等,而更新字段的值不等的情况下更新行,而不会在没有找到查询关键字的时候新增行。

3.2.5 表输出

该步骤允许你加载数据到一张数据库表中。相当于 DML 操作中的 INSERT。

4 服务端功能介绍

4.1 Browse Files

在该功能下,可以浏览作业和转换的文件,可进行上传与下载,在进行部分文件的修改时,可以通过上传将修改的文件进行更新。遗憾的是只支持单文件上传。

4.2 Manage Data Sources

管理数据源,在这里设置编辑任何你在同步中需要用到的数据源,包括源数据库和目标数据库,也可以测试数据源是否可以正常连接。

4.3 Home>Schedules

管理定时任务,所有设置好的定时任务都在这里进行管理,可以进行任务的开启、暂停、删除,设置过滤条件查看任务。任务基本信息的展示,任务名称、用到的作业或者转换、重复规则、最后一次运行时间、下一次运行时间、当前状态等。

4.4 Home>Administration

管理权限、设置邮件、管理临时生成文件,这里主要介绍对临时文件的管理,因为定时任务在运行的时候,每一次都会以原始作业或者转换为模板复制出来一个副本,用这个副本来进行真正的同步,所以定时任务每一次的运行都会生成这样一个副本,这样这些临时文件就会不断增加,在该模块下除了手动删除超过 N 天的临时文件,并且可以设置定时任务对其进行自动删除。

5 效率提升

Kettle 作为一款 ETL 工具,肯定无法避免遇到效率问题,当很大的数据源输入的时候,就会遇到效率的问题。对此有几个解决办法: 

1)数据库端创建索引。对需要进行查询的数据库端字段,创建索引,可以在很大程度上提升查询的效率,最多的时候,我不创建索引,一秒钟平均查询 4 条记录,创建索引之后,一秒钟查询 1300 条记录。     

2)数据库查询和流查询注意使用环境。因为数据库查询为数据输入端输入一条记录,就对目标表进行一次查询,而流查询则是将目标表读取到内存中,数据输入端输入数据时,对内从进行查询,所以,当输入端为大数据量,而被查询表数据量较小(几百条记录),则可以使用流查询,毕竟将目标表读到内存中,查询的速度会有非常大的提升(内存的读写速度是硬盘的几百倍,再加上数据库自身条件的制约,速度影响会更大)。同理,对于目标表是大数据量,还是建议使用数据库查询,不然的话,一下子几百 M 的内存被干进去了,还是很恐怖的。 

 3)谨慎使用 JavaScript 脚本,因为 javascript 本身效率就不高,当你使用 js 的时候,就要考虑你每一条记录,就要执行一次 js 所需要的时间了。 

 4)数据库 commit 次数,一条记录和一百条记录 commit 对效率的影响肯定是不一样的。 

 5)表输入的 sql 语句的写法。有些人喜欢在表输入的时候,将所有关联都写进去,要么 from N 多个表,要么 in 来 in 去,这样,就要面对我在 2)里面说道的问题,需要注意。 

6)注意日志输出,例如选择数据库更新方式,而且日志级别是 debug,那么后台就会拼命的输出日志,会在很大程度上影响速度,此处一定要注意。

7)kettle 本身的性能绝对是能够应对大型应用的,一般的基于平均行长 150 的一条记录,假设源数据库,目标数据库以及 kettle 都分别在几台机器上(最常见的桌面工作模式,双核,1G 内存),速度大概都可以到 5000 行每秒左右,如果把硬件提高一些,性能还可以提升 , 但是 ETL 过程中难免遇到性能问题,下面一些通用的步骤也许能给你一些帮助

 ①尽量使用数据库连接池

 ②尽量提高批处理的 commit size

 ③尽量使用缓存,缓存尽量大一些(主要是文本文件和数据流)

 ④Kettle 是 Java 做的,尽量用大一点的内存参数启动 Kettle.

 ⑤可以使用 sql 来做的一些操作尽量用 sql,Group , merge , stream lookup ,split field 这些操作都是比较慢的,想办法避免他们.,能用 sql 就用 sql

 ⑥插入大量数据的时候尽量把索引删掉

 ⑦尽量避免使用 update , delete 操作,尤其是 update , 如果可以把 update 变成先 delete ,后 insert。

 ⑧能使用 truncate table 的时候,就不要使用 delete all row 这种类似 sql

 ⑨要知道你的性能瓶颈在哪,可能有时候你使用了不恰当的方式,导致整个操作都变慢,观察 kettle log 生成的方式来了解你的 ETL 操作最慢的地方

  • 发表于:
  • 本文为 InfoQ 中文站特供稿件
  • 首发地址https://www.infoq.cn/article/fe74192de11d85aca52016815
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码关注腾讯云开发者

领取腾讯云代金券