Kettle


原文链接: Kettle
  1. 安装 kettle
    unzip pdi-ce-7.1.0.0-12.zip -d /usr/local/
  2. 安装 mysql 组件
    cp mysql-connector-java-5.1.46-bin.jar /usr/local/data-integration/lib/

/usr/local/data-integration/etl.sh


#! /usr/bin/env bash
export JAVA_HOME=/usr/lib/jvm/jre
export PATH=$JAVA_HOME/bin:$PATH
export KETTLE_HOME=/root/
mkdir -p /var/log/kettle/
/usr/local/data-integration/kitchen.sh -norep -file='/root/etl/sys_trans.kjb' \
-param:hr_src_ip=10.20.24.207 \
-param:hr_src_port=3306 \
-param:hr_src_user=infotop \
-param:hr_src_pass=LzxtVXj1 \
-param:hr_src_dbname=infotop \
-param:hr_desc_ip=10.20.24.207 \
-param:hr_desc_port=3306 \
-param:hr_desc_user=infotop \
-param:hr_desc_pass=LzxtVXj1 \
-param:hr_desc_dbname=lzkp_bi \
-param:hr_month=1 \
-param:network_flag=0 \
-param:region_code=371300 \
-logfile="/var/log/kettle/kettle`date -I`.log"

-param:network_flag=0 \ #外网是0 内网是1
-param:region_code=371300 \ # 区划ID 和前端保持一直

使用

  • 添加数据库驱动mysql-connector-java-X.X.XX-bin.jar到lib目录下


$KETTLE_HOME/.kettle/kettle.properties 设置

# 设置NULL和空字符串区别开
# 解决Column 'xxxx' cannot be null
KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL=Y

数据库选项

# 日期返回0001-01-01 00:00:00.0
# 解决Value '0000-00-00' can not be represented
zeroDateTimeBehavior=round

# 日期返回NULL
# 解决Value '0000-00-00' can not be represented
zeroDateTimeBehavior=convertToNull

# 设置tinyint不转换为bool型
# 解决tinyint转换为bool型的问题
tinyInt1isBit=false

# 设置不转换为bool型
transformedBitIsBoolean=false

# 解决中文乱码
characterEncoding=utf-8

# 解决MySQL插入慢的问题
useServerPrepStmts=false
rewriteBatchedStatements=true
useCompression=true

# 设置游标流式读取数据以及每次读取的size
useCursorFetch=true
defaultFetchSize=1000

下面是使用kettle中可能会遇到的坑

net_write_timeout

  1. 把net_write_timeout设大一些,
  2. 如果第一种方法没有没有效果,则在mysql连接面板上,不选user result stream cursor选项。

JavaScript组件

  1. 使用isEmpty函数前要先判断!=null

timeout

# 连接握手失败重试次数
maxReconnects=12

# 每次连接超时时间
initialTimeout=10

# 自动重连
autoReconnect=true
failOverReadOnly=false
connectTimeout=120000
socketTimeout=120000

http://type-exit.org/adventures-with-open-source-bi/2010/07/using-named-parameters-in-kettle/

/usr/local/data-integration/kitchen.sh -norep -file='/docker/etl/sys_trans.kjb'
/usr/local/data-integration/pan.sh -norep -file='/docker/etl/sys_src_middle.ktr'
-param:HANGRUAN_ANALYSIS_SOURCE_DB_IP="10.114.0.7"
-param:HANGRUAN_ANALYSIS_SOURCE_DB_PORT="3306"
-param:HANGRUAN_ANALYSIS_SOURCE_DB_NAME="shizhi"
-param:HANGRUAN_ANALYSIS_SOURCE_DB_USER="lzkp"
-param:HANGRUAN_ANALYSIS_SOURCE_DB_PASS="***"
-param:HANGRUAN_ANALYSIS_DESC_DB_IP="10.114.0.7"
-param:HANGRUAN_ANALYSIS_DESC_DB_PORT="3306"
-param:HANGRUAN_ANALYSIS_DESC_DB_NAME="lzkpbi"
-param:HANGRUAN_ANALYSIS_DESC_DB_USER="lzkp"
-param:HANGRUAN_ANALYSIS_DESC_DB_PASS="
***"
-param:HANGRUAN_ANALYSIS_REPORT_MONTH_LENGTH="2"
-param:region_code="371300"
-param:network_flag="0"

/usr/local/data-integration/pan.sh -norep -file='/root/etl/sys_src_middle.ktr'
export JAVA_HOME=/usr/java/jre
export PATH=$JAVA_HOME/bin:$PATH
/usr/local/data-integration/kitchen.sh -norep -file='/root/etl/sys_trans.kjb'
-param:hr_desc_dbname=lzkp_bi
-param:hr_desc_ip=10.114.0.7
-param:hr_desc_pass=******
-param:hr_desc_port=3306
-param:hr_desc_user=lzkp
-param:hr_month=7
-param:hr_src_dbname=shizhi
-param:hr_src_ip=10.114.0.7
-param:hr_src_pass=******
-param:hr_src_port=3306
-param:hr_src_user=lzkp
-param:network_flag=0
-param:region_code=371300
-logfile="kettledate -I.log"

#!/bin/bash

#set -e

SERVER_DATA=$6
GPS_FOLDER=$7
HOSTNAME=$1
DATABASE_NAME=$3
PASSWORD=$5
PORT_NUMBER=$2
USERNAME=$4

###Updating GPS data###

#echo "Downloading data from database server"
scripts/get_files.sh $SERVER_DATA $GPS_FOLDER

echo "Getting Data Into Local Database"
/usr/local/data-integration/kitchen.sh -file="../ktrs/datario/1_endpoint_crossing/1 - Starter.kjb" -param:DATABASE_NAME=$DATABASE_NAME -param:PASSWORD=$PASSWORD -param:PORT_NUMBER=$PORT_NUMBER -param:USERNAME=$USERNAME -param:HOSTNAME=$HOSTNAME

echo "Generating Real Trips from GPS Data"

/usr/local/data-integration/pan.sh -file="../ktrs/datario/2_trip_generation/gen_trip.ktr" -param:DATABASE_NAME=$DATABASE_NAME -param:PASSWORD=$PASSWORD -param:PORT_NUMBER=$PORT_NUMBER -param:USERNAME=$USERNAME -param:HOSTNAME=$HOSTNAME

echo "Transform Data to Dimensions and Fact"

/usr/local/data-integration/kitchen.sh -file="../ktrs/datario/3_dw_development/export_to_dw.kjb" -param:DATABASE_NAME=$DATABASE_NAME -param:PASSWORD=$PASSWORD -param:PORT_NUMBER=$PORT_NUMBER -param:USERNAME=$USERNAME -param:HOSTNAME=$HOSTNAME
#!/bin/bash
FILE="$KETTLE_HOME/.kettle/kettle.properties"
KITCHEN=$PDI_HOME/data-integration/kitchen.sh

main() {
    {
        echo "DW_HOSTNAME=$DW_HOSTNAME"
        echo "DW_DB=$DW_DB"
        echo "DW_PORT=$DW_PORT"
        echo "DW_USER=$DW_USER"
        echo "DW_PASSWORD=$DW_PASSWORD"
        echo "COOG_HOSTNAME=$COOG_HOSTNAME"
        echo "COOG_DB=$COOG_DB"
        echo "COOG_PORT=$COOG_PORT"
        echo "COOG_USER=$COOG_USER"
        echo "COOG_PASSWORD=$COOG_PASSWORD"
    } >>"$FILE"
    cat "$FILE"
    $KITCHEN -file="$KETTLE_HOME/ETL/Main.kjb"
}

main "$@"

kettle 3.2 以前的版本里只有 variable 和 argument,kettle 3.2 中,又引入了 parameter 概念;variable 即environment variables(环境变量或全局变量),即使是不同的转换它们也拥有同样的值;而argument(位置参数)和parameter(命名参数),可以映射为局部变量,只针对一个特定的转换,比如像是限定结果集的大小和过滤条件。

1、 variable(变量)

variables 也可以认为叫做environment variables , 就像它的名字一样,主要是用来设定环境变量的,比如最常见的:文件的存放地址,smtp的配置等等,你也可以把它认为是编程语言里面的全局变量,即使是不同的转换它们也拥有同样的值;

变量可以用在转换或作业中,可以通过在转换中使用 Set Variable 步骤定义或在kettle.properties文件中定义,文件的路径默认如下:

$HOME/.kettle (Unix/Linux/OSX)

C:\Documents andSettings<username>.kettle\ (Windows)

C:\Users<username>.kettle(Windows Vista)

1.1、kettle.properties文件中定义全局变量

打开文件,直接在里面定义,如: TODAY=to_char(sysdate,'yyyy-mm-dd'),这里支持数据库函数,说的更直白点,就是这里定义是个啥,那么在transformation里边取到的它就是啥。在需要的地方,直接%%V_YES_DATE%%,或者${V_YES_DATE}就可以得到。需要提醒的是如果编辑中文,需要navationtoascii工具或在eclipse中编辑。定义后保存重启spoon才生效。

2、 garument(位置参数)

kettle 3.2 以前的版本里只有 argument,也叫Positional arguments,就是最多能设置的 10 个命令行参数,通过在命令行参数的位置来区别

3、 parameter(命名参数)

在 kettle 3.2 中,又引入了 parameter 概念,可以通过名称来区别,并可以在命令行中通过/param:name=value 的方式设置设置参数, 另外/listparam 可以列出一个ktr 或 kjb 里定义的parameter。

命名参数可以在转换或作业的设置对话框中定义,定义时给定默认值;当在SPOON中运行作业或转换时,在运行对话框中输入命名参数的值。在命令行中运行时,也可以通过-param:name=value给每个命名参数赋值,param:name=value在命令行中一个整体块,视为命令的一个参数。

例:运行job.kjb文件,有两个参数files.dir=/opt/files 、max.date=2010-06-02

Linux: ./kitchen.sh-file:job.kjb -param:files.dir=/opt/files -param:max.date=2010-06-02
Windows: Kitchen.bat -file:job.kjb "-param:files.dir=/opt/files" "-param:max.date=2010-06-02"

切记上边是英文双引号..

命名参数可作为变量,在运行时命名参数并映射为变量。如果你定义了命名参数为foo,你可以在任何地方引用通过${foo}.

tran

一 窥一斑而知全豹(分清楚kitchen.sh和pan.sh的区别)

1、kitchen.sh是用来运行作业的。我们来在命令行输入kitchen.sh,可以看到下面的页面:

  可以看到,这里kitchen.sh后面可以跟很多参数,而且对于每一个参数的含义都有详细的解释。
2,pan.sh是用来运行转换的。我们在命令行同样输入pan.sh,可以看到出现和kitchen.sh一样的结果:

   这里只要知道,kitchen.sh是运行作业的,pan.sh是运行转换的即可,详细的参数我下面做一个概要的介绍
二、他山之石,可以攻玉(使用kitchen.sh的方法与pan.sh的方法是如此的累雷同)

  这里通过传入参数的不同又可以将Kettle支持的对象分为四类:
  a、没有资源库的作业
  b、没有资源库的转换
  c、有资源库的作业
  d、有资源库的转换
下面举例来分析这4种对象的使用示例 :
(1)、如何使用命令行的方式运行“没有资源库的作业”?
    sh D:\data-integration\kitchen.sh -norep -file=C:\A\B\C\xx.job
        -norep的意思就代表没有资源库,-file就是指向要运行的job文件,注意:这里需要填写的是job的绝对路径
(2)、如何使用命令行的方式运行“没有资源库的转换”?
    sh D:\data-integration\pan.sh -norep -file=C:\A\B\C\xxx.ktr
        -norep的意思就代表没有资源库,-file就是指向要运行的ktr文件,注意:这里需要填写的是ktr的绝对路径
(3)、如何使用命令行的方式运行“有资源库的作业”?
    sh D:\data-integration\kitchen.sh -rep=rep_name -user=user_name -pass=password -dir=dir_path -job=XXXXX_job
             在这个例子中,因为是有资源库,所以需要指定资源库的名字(也就是上面例子中的rep_name),资源库的用户(上面的user_name),资源库的密码(上面的password)
             另外上面例子中的-dir指向的是job所在的资源库目录。-job自然就是要运行的job名字了,这里需要一个名字就可以了,不需要绝对路径
  (4)、如何使用命令行的方式运行“有资源库的转换”?
    sh D:\data-integration\pan.sh -rep=rep_name -user=user_name -pass=password -dir=dir_path -trans=XXXXX_trans
             这句话的解释可以参照(3)。

三、其他参数的使用

     -logfile:可以通过这个参数指定要输出的log存放位置。这里需要给定的是文件路径+文件名
     -param:这个就是所谓的命名参数,可以通过命名参数往转换,或是job中传递变量,得到的变量自然也就是命名变量了。使用的格式为:-param:param1='',param2=''
     -level:指定输出log的级别

     先这样,后续如果有新的感悟,还会添加新内容

crontab
kettle.sh

export JAVA_HOME=/usr/java/jre1.6.0_23
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
/opt/pentaho/data-integration/kitchen.sh -file=/opt/pentaho/KettleJob/test.kjb

Kettle调优

1、 调整JVM大小进行性能优化,修改Kettle定时任务中的Kitchen与Pan脚本中。

修改脚本代码片段

set OPT=-Xmx512m -cp %CLASSPATH% -Djava.library.path=libswt\win32\ -DKETTLE_HOME="%KETTLE_HOME%" -DKETTLE_REPOSITORY="%KETTLE_REPOSITORY%" -DKETTLE_USER="%KETTLE_USER%" -DKETTLE_PASSWORD="%KETTLE_PASSWORD%" -DKETTLE_PLUGIN_PACKAGES="%KETTLE_PLUGIN_PACKAGES%" -DKETTLE_LOG_SIZE_LIMIT="%KETTLE_LOG_SIZE_LIMIT%"

参数参考:

-Xmx1024m:设置JVM最大可用内存为1024M。
-Xms512m:设置JVM促使内存为512m。此值可以设置与-Xmx相同,以避免每次垃圾回收完成后JVM重新分配内存。
-Xmn2g:设置年轻代大小为2G。整个JVM内存大小=年轻代大小 + 年老代大小 + 持久代大小。持久代一般固定大小为64m,所以增大年轻代后,将会减小年老代大小。此值对系统性能影响较大,Sun官方推荐配置为整个堆的3/8。
-Xss128k:设置每个线程的堆栈大小。JDK5.0以后每个线程堆栈大小为1M,以前每个线程堆栈大小为256K。更具应用的线程所需内存大小进行调整。在相同物理内存下,减小这个值能生成更多的线程。但是操作系统对一个进程内的线程数还是有限制的,不能无限生成,经验值在3000~5000左右。

样例:OPT=-Xmx1024m -Xms512m

2、 调整提交(Commit)记录数大小进行优化

如修改RotKang_Test01中的“表输出”组件中的“提交记录数量”参数进行优化,Kettle默认Commit数量为:1000,可以根据数据量大小来设置Commitsize:1000~50000。

3、 调整记录集合里的记录数

4、尽量使用数据库连接池;

5、尽量提高批处理的commit size;

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

7、Kettle是Java做的,尽量用大一点的内存参数启动Kettle;

8、可以使用sql来做的一些操作尽量用sql;

  Group , merge , stream lookup,split field这些操作都是比较慢的,想办法避免他们.,能用sql就用sql;

9、插入大量数据的时候尽量把索引删掉;

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

11、能使用truncate table的时候,就不要使用deleteall row这种类似sql合理的分区,如果删除操作是基于某一个分区的,就不要使用delete row这种方式(不管是deletesql还是delete步骤),直接把分区drop掉,再重新创建;

12、尽量缩小输入的数据集的大小(增量更新也是为了这个目的);

13、尽量使用数据库原生的方式装载文本文件(Oracle的sqlloader, MySQL的bulk loader步骤);

14、尽量不要用kettle的calculate计算步骤,能用数据库本身的sql就用sql ,不能用sql就尽量想办法用procedure,实在不行才是calculate步骤;

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

16、远程数据库用文件+FTP的方式来传数据,文件要压缩。(只要不是局域网都可以认为是远程连接)。

二、索引的正确使用

在ETL过程中的索引需要遵循以下使用原则:

1、当插入的数据为数据表中的记录数量10%以上时,首先需要删除该表的索引来提高数据的插入效率,当数据全部插入后再建立索引。

2、避免在索引列上使用函数或计算,在where子句中,如果索引列是函数的一部分,优化器将不使用索引而使用全表扫描。

3、避免在索引列上使用 NOT和 “!=”,索引只能告诉什么存在于表中,而不能告诉什么不存在于表中,当数据库遇到NOT和 “!=”时,就会停止使用索引转而执行全表扫描。

4、索引列上用 >=替代 >

  高效:select * from temp where deptno>=4

  低效:select * from temp where deptno>3

  两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

三、数据抽取的SQL优化

1、Where子句中的连接顺序。

2、删除全表是用TRUNCATE替代DELETE。

3、尽量多使用COMMIT。

4、用EXISTS替代IN。

5、用NOT EXISTS替代NOT IN。

6、优化GROUP BY。

7、有条件的使用UNION-ALL替换UNION。

8、分离表和索引。

`