Kettle

by · 2018年06月02日 · 4470 Words · ~9min reading time | Improve on


原文链接： 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、分离表和索引。

Tags:

`