数据迁移工具 Kettle

1. 背景知识

(1) 什么是 ETL

 ETL 是 Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取（extract）、交互转换（transform）、加载（load）至目的端的过程。

(2) Kettle 简介

 Kettle 是一款国外开源的 ETL 工具，纯 java 编写，可以在 Window、Linux、Unix 上运行，数据抽取高效稳定。Kettle 中文名称叫水壶，该项目的主程序员 MATT 希望把各种数据放到一个壶里，然后以一种指定的格式流出。我们常用它定时将一些库的数据稍做转换后存入其它库。

(3) 工作机制

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

2. 安装和运行

(1) 安装

 kettle 的最新下载地址：http://community.pentaho.com/projects/data-integration/ 我在 linux 下安装，机器之前已装好 java 环境，将下载的安装包解压到/usr/local 目录下。

  另外，还需要安装对应数据库的支持，比如我使用 mysql 数据库，则需要下载 mysql-connector-java-5.1.41-bin.jar 放到/usr/local/kettle/data-integration/lib 目录下。

(2) 启动图形界面

 kettle 提供图形界面和命令行两种方式，图形界面主要用于配置和测试，命令行主要用于运行。

1
2
$ cd /usr/local/kettle/data-integration/
$ ./spoon.sh

3. 准备工作

  一般使用 kettle 主要是导数据，因此以数据库作为实例，选建立数据库环境。如果只使用最简单的调度，也可以不使用数据库；如果已安装数据库，请忽略安装数据库，要建表即可。

(1) 安装 mysql 数据库

1
2
3
$ sudo apt-get install mysql-server
$ sudo apt isntall mysql-client
$ sudo apt install libmysqlclient-dev

(2) 建库建表

1
2
3
4
5
6
7
8
$ mysql -uroot -p
> create database test_db;
> use test_db;
> create table table1 (id INT, name VARCHAR(20), date DATE);
> create table table2 (id INT, name VARCHAR(20), date DATE);
> insert into table1 values(1,'xy','2018-11-04');
> insert into table1 values(2,'llx','2018-11-05');
$ select * from table1;

  此时建立了库 test_db, 库中有表 table1, table2, 表中各有三个字段 id, name, date，table1 中插入了两条记录，接下来的操作是将 table1 中的数据导入 table2。

4. 一个简单的作业调度

(1) 建立一个变换 Transformation

  1. 新建一个变换

  在 Kettle 界面上：菜单 ->New->Transformation

  1. 加一个输入库

  主界面：左侧 ->Input->Table Input，拉到工作区建立新的 table input。   双击 table input，调出其编辑界面。   在编辑界面点 Connection->New，建立一个数据库连接。   在连接界面，Connection Type 选 MySQL，设置 Host Name: 127.0.0.1, Database Name: test_db, Port Number: 3306, User Name: root, Password: 你的密码，Connection Name：test1，此时点 Test，可查看数据库是否连接成功，如果成功，点 OK 建立连接。   在 Table Input 编辑界面，选 Get SQL select statement…，从库中选择你所要输入的表 table1，此时自动生成了 select 查询语句（这就是 kettle 方便的地方，不用手敲，自动生成语句，简单编辑即可），选 OK 确定。

  1. 加一个输出库

  主界面：左侧 ->Output->Table Output，拉到工作区建立新的 table output。   双击 table output，调出其编辑界面。   在编辑界面，Conection 中选择刚才建立的数据库连接 test1，Target table 选中 table2，点 OK 确定。

  1. 连接输入和输出，并测试

  按住 shift 键，从 Table input 拖一条线到 Table output，用菜单 File->Save 保存该变换名为 test_trans，然后点上方的 Run 键运行，正常情况下，小图标右上角出现绿色对勾，说明运行正常。   在 mysql 中输入

1
> select * from table2;

  可以看到数据库 table1 中的数据已经被复制到 table2 中，此时一个 Transformation 就完成了。

(2) 建立一个工作流 Job

  1. 新建一个 Job

  在 Kettle 界面上：菜单 ->New->Job

  1. 加入模块

  主界面：左侧 ->General->START，拖出一个 START（可以设定执行时间）   主界面：左侧 ->General->Transformation，拖出一个，双击调出编辑界面，Browse 选刚才保存的 test_trans 文件，点 OK 确认。   主界面：左侧 ->General->Success，拖出一个 Success

  1. 连接模型，并测试

  按住 Shift 依次连线：Start->Transformation->Success，然后点上方的运行按钮，正常情况下就能正常运行了。

(3) 变量和选择分支

  1. 新建一个变换

  在 Kettle 界面上：菜单 ->New->Transformation。

  1. 建立变量

  主界面：左侧 ->Job->Set Variables，拖到主工作区，双击编辑，新建一个变量 flag，Default value 为 1。

  1. 建立选择分支

  主界面：左侧 ->Flow->Switch/Case，拖到主工作区，双击编辑，在 Field name to switch 指定变量 flag，然后在 Case values 中加两个值 0 和 1。

  主界面：左侧 ->Flow->Dummy，拖到主工作区 主界面：左侧 ->Flow->Abort，拖到主工作区

  1. 连接模型，并测试

  按住 Shift 依次连线：Set Variables->Switch/Case，Switch/Case 连 Abort 时选 This case target value 0，Switch/Case 连 Dummy 时选 This case target value 0。

  然后点上方的运行按钮，正常情况下就能正常运行了。这里设置的变量也可以在整个工作流中使用，操作数据库时使用方法是 ${变量名}，注意需要选中“use variables in script”。

(4) 其它

  除了以上的导库，设变量，分支，以外，常用的还有 Java 程序模型（左侧 Transformation->Steps->User defined Java Class），通常用于在数据库转换过程中做一些 sql 无法实现的数据变换，同样的，新建时，它也会生成一段基础代码，简单修改即可。

5. 用命令行运行任务

  上面建立的 Transformation 扩展名为 ktr，Job 扩展名为 kjb，可通过 kitchen.sh 执行，具体命令是：

1
./kitchen.sh -file=xxx.kjb

  如需不受控制台关闭的影响，可以使用以下命令，使 kettle 一直在后台运行：

1
nohup ./kitchen.sh -file=xxx.kjb &