Kettle定时执行数据库表增量同步

本文主要记录通过kettle实现定时执行数据库表增量同步。

思路

其实就是用kettle对比源数据库表和目标数据库表,筛选得到它们的差集,然后更新到目标数据库表中。

这个转换需要定时执行(每隔5分钟执行一次),这里有两种方法:

  1. Kettle作业,通过Start组件设置定时策略

job定时任务选择的时间间隔是在job执行结束后,然后再等待你设置的时间以后才会执行下一个job(因为数据量大时一个job可能执行很长时间)

  1. 编写执行”转换”的脚本,然后用Linux上的crontab来定时执行这个脚本

第一种方法的缺点就是窗口不可关闭,因此一般采用的都是第二种方法。

增量同步转换

下面是这个转换的整体流程:

表输入

源表和目标表输入抽数之后,需要按照关键字段进行排序,如果不排序,会导致两张表连接的时候出现问题,关联的数据变少,并且排序的顺序必须一致,比如A表的a字段和B表的b字段 是连接条件,那么这俩要排序,而且规则要一样。

排序的方法有:

  1. 直接在表中用order by排序(在数据库中排序)
  2. 使用排序记录组件进行(利用缓存排序)

记录集连接

记录集连接使用LEFT OUTER,然后通过一个过滤器过滤掉匹配上的行,剩下的就是我们要的差集

插入/更新

然后我们将前面得到的正确的输出给到插入/更新组件。

插入/更新组件介绍见:Kettle的插入更新组件详解

定时执行

执行”转换”的脚本编写

首先注意下kitchen和pan的区别:

  • kitchen是一个作业执行引擎(命令行执行工具),用来执行作业。具体参数:

    • -rep:Repository name 任务包所在存储名
    • -user:Repository username 执行人
    • -pass:Repository password 执行人密码
    • -job:The name of the job to launch 任务包名称
    • -dir:The directory(don’tforget the leading /or) 文件夹
    • -file:The file name(JobXML)to launch 要启动的文件名
    • -level:The logging level(Basic,Detailed,Debug,Rowlevel,Error,Nothing)指定日志级别
      • Error: 只显示错误
      • Nothing: 不显示任何输出
      • Minimal: 只使用最少的记录
      • Basic: 这是默认的基本日志记录级别
      • Detailed: 详细的日志输出
      • Debug: 以调试为目的,非常详细的输出
      • Rowlevel: 使用行级记录,会产生大量的数据
    • -log:The logging file to write to 指定日志文件
    • -listdir:List the directories in the repository 列出指定存储中的目录结构。
    • -listjobs:List the jobs in the specified directory 列出指定目录下的所有任务
    • -listrep:List the defined repositories 列出所有的存储
  • pan是一个转换执行引擎(命令行执行工具),用来执行转换。具体参数类似kitchen,仅列举几个重要的:

    • -version 显示版本信息
    • -file:filename 运行的文件
    • -param:key=value 指定命名参数
    • -log:logging filename 设置日志文件
    • -level:logging level 设置日志级别

因为我这里是去定时执行”转换”,所以这里选择pan。

Windows下使用的bat脚本:

1
2
3
D:
cd D:\data-integration
pan.bat /file D:\kettle\Example.ktr /level Basic /logfile D:\kettle\log\kettle_%date:~0,4%%date:~5,2%%date:~8,2%.log

Linux下使用的shell脚本:

1
2
3
4
5
6
7
8
#!/bin/bash
d1=`date +%Y%m%d%H%M%S`
cd /data/kettle/data-integration
export KETTLE_HOME=/data/kettle/data-integration
export JAVA_HOME=/opt/hadoop_client/JDK/jdk
export CLASSPATH=$JAVA_HOME/lib/
export PATH=$PATH:$JAVA_HOME/bin:$KETTLE_HOME
./pan.sh -file=/data/tding/kettle/kettle_xxx.ktr -level=basic >> /data/tding/kettle/logs/kettle_xxx_$d1.log

crontab总是不会缺省的从用户profile文件中读取环境变量参数,经常导致在手工执行某个脚本时是成功的,但是到crontab中试图让它定期执行时就是会出错。如执行ETL这个脚本需要JAVA_HOME变量,有可能你使用的这个用户的java版本与root不一致,或者root账户就没有安装java。

因此这里在脚本中设置环境变量

Windows下配置任务计划

如果是在Windows下定时执行脚本,那么需要在控制面板–管理工具–任务计划程序下建立任务。

  1. 在任务计划程序库中新建kettle文件夹,然后创建任务
  2. 常规设置:
    • 名称
    • 描述
    • 勾选不管用户是否登录都要运行
    • 勾选使用最高权限运行
  3. 触发器设置:
    • 新建触发器,根据实际需求设置时间
  4. 操作设置:
    • 新建操作,设置脚本路径
  5. 条件设置:根据实际需求设置
  6. 其他设置:根据实际需求设置

然后等待windows计划任务调度kettle转换,并查看执行结果。

Linux下通过crontab配置定时任务

Linux系统提供了使用者控制计划任务的命令–crontab命令。

我们可以通过crontab -e来编辑文件,格式如下:

1
2
3
4
5
6
7
8
9
.---------------------------------- minute (0 - 59) 表示分钟
| .------------------------------- hour (0 - 23) 表示小时
| | .---------------------------- day of month (1 - 31) 表示日期
| | | .------------------------- month (1 - 12) OR jan,feb,mar,apr ... 表示月份
| | | | .---------------------- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat 表示星期(0 或 7 表示星期天)
| | | | | .------------------- username 以哪个用户来执行
| | | | | | .------ command 要执行的命令,可以是系统命令,也可以是自己编写的脚本文件
| | | | | | |
* * * * * user-name command to be executed

例如:每天早上8:30执行一次kettle.sh

1
30 8 * * * sh /usr/local/kettle/kettle.sh > /dev/null 2>&1

参考


----------- 本文结束啦感谢您阅读 -----------

赞赏一杯咖啡

欢迎关注我的其它发布渠道