ETL利器 Kettle实战应用解析系列(二)

清疚 2023-10-17 16:59 81阅读 0赞

**本文主要阅读目录如下：**

[1、应用场景][1]

[2、DEMO实战][2_DEMO]

[3、DEMO下载][3_DEMO]

**1、应用场景**

这里简单概括一下几种具体的应用场景，按网络环境划分主要包括：

*  表视图模式：这种情况我们经常遇到，就是在同一网络环境下，我们对各种数据源的表数据进行抽取、过滤、清洗等，例如历史数据同步、异构系统数据交互、数据对称发布或备份等都归属于这个模式；传统的实现方式一般都要进行研发（一小部分例如两个相同表结构的表之间的数据同步，如果sqlserver数据库可以通过发布/订阅实现），涉及到一些复杂的一些业务逻辑如果我们研发出来还容易出各种bug；
 *  前置机模式：这是一种典型的数据交换应用场景，数据交换的双方A和B网络不通，但是A和B都可以和前置机C连接，一般的情况是双方约定好前置机的数据结构，这个结构跟A和B的数据结构基本上是不一致的，这样我们就需要把应用上的数据按照数据标准推送到前置机上，这个研发工作量还是比较大的；
 *  文件模式: 数据交互的双方A和B是完全的物理隔离，这样就只能通过以文件的方式来进行数据交互了，例如XML格式，在应用A中我们开发一个接口用来生成标准格式的XML，然后用优盘或者别的介质在某一时间把XML数据拷贝之后，然后接入到应用B上，应用B上在按照标准接口解析相应的文件把数据接收过来；

综上3种模式如果我们都用传统的模式无疑工作量是巨大的，那么怎么做才能更高效更节省时间又不容易出错呢？答案是我们可以用一下Kettle-\_-！

**2、DEMO实战**

2、1 实例1：数据库TestA中的UserA表到数据库TestB的UserB表

1）为方便演示，我这边把Sql脚本贴出来，大家直接复制在sqlserver中运行即可,sql脚本如下：

![ExpandedBlockStart.gif][]

![复制代码][copycode.gif]

use master
    IF EXISTS(SELECT 1 FROM sysdatabases WHERE NAME=N'TestA')
    BEGIN
        DROP DATABASE TestA   --如果数据库存在先删掉数据库
    END
    GO
    
    CREATE DATABASE TestA
    ON
    PRIMARY  --创建主数据库文件
    (
        NAME='TestA',
        FILENAME='E:\ETL\DB\TestA.dbf',
        SIZE=5MB,
        MaxSize=20MB,
        FileGrowth=1MB
    )
    go
    use TestA
    -- 建立一个测试的用户表
    create table usersA
    (
        id int not null,
        userid varchar(50) not null,
        username varchar(200) null
    )
    go
    -- 插入3条记录
    insert into usersA values(1,'lisi','李四');
    insert into usersA values(2,'zhangsan','张三');
    insert into usersA values(3,'lisi','李四');
    go
    
    use master
    IF EXISTS(SELECT 1 FROM sysdatabases WHERE NAME=N'TestA')
    BEGIN
        DROP DATABASE TestA   --如果数据库存在先删掉数据库
    END
    GO
    CREATE DATABASE TestB
    ON
    PRIMARY  --创建主数据库文件
    (
        NAME='TestB',
        FILENAME='E:\ETL\DB\TestB.dbf',
        SIZE=5MB,
        MaxSize=20MB,
        FileGrowth=1MB
    )
    go
    use TestB
    -- 建立一个测试的用户表
    create table usersB
    (
        id int not null,
        userid varchar(50) not null,
        username varchar(200) null
    )
    go

![复制代码][copycode.gif]

**2）Kettle实现方式**

**功能简述：数据库TestA中的UserA表到数据库TestB的UserB表；**

**实现流程：建立一个转换和一个作业Job；**

A:建立一个转换：打开Kettle.exe，选择没有资源库，进入主界面，新建一个转换，转换的后缀名为ktr，转换建立的步骤如下：

步骤1：创建DB连接，选择新建DB连接，如下图，我们输入相应的Sqlserver配置信息之后点击Test按钮测试是否配置正确！

![16135830-573f1f91f44640d58ae628c6a76a2482.png][]

我们需要建立两个DB连接，分别为TestA和TestB；

步骤2：建立步骤和步骤关系，点击核心对象，我们从步骤树中选择【表输入】，如下图，这样拖拽一个表输入之后，我们双击表输入之后，我们自己可以随意写一个sql语句，这个语句表示可以在这个库中随意组合，只要sql语句没有错误即可，我这里只是最简单的把TestA中的所有数据查出来，语句为select \* from usersA。

![16140230-6fff35c8fa5d42a790539823d4b5e37e.png][]

接下来我们创建另外一个步骤【插入/ 更新】，然后在【表输入】上同时按住shift键和鼠标左键滑向【插入/ 更新】，这样建立两个步骤之间的连接，【插入/ 更新】执行的逻辑是如果UserA表中的记录在UserB中不存在那么就插入，如果存在就更新，如下图，在插入更新中我们可以做一些关键条件和字段映射，这里我们是最简单的！点击保存，把我们建立的转换保存一下。

![16141233-1f82813ebcae4b6ca0bb15b39758bd1d.png][]

建立好转换之后，我们可以直接运行这个转换，检查一下是否有错，如图，有错误都会在下面的控制台上输出。

![16141940-339571e5fe6f4c69a634daac6d584e6a.png][]

B:如果我们需要让这个转换定时执行怎么办呢，那么我们需要建立一个作业job，见下图，在简单表同步这个转换中，我们把在A步骤中建立的ktl配置上，注意路径的正确性；

![16142535-5751f4a4bed1430391ac5d00b2ad18da.png][]

这样我们在【Start】步骤上面双击，如图：

![16142923-eff7b8df5cd14a4796b04bea28277292.png][]

这样这个作业就制定好了，点击保存之后，我们就可以在图形化界面上点击开始执行了！

2、2 实例2：全面进阶的一个稍微复杂的例子

ž根据客户，帐户，交易表中的数据，生成对应的数据文件，将数据文件可以导入到对应表中，并且可以用job来调用整个流程。

ž目标：

ž1)，提交对应的Kettle文件

ž2)，Kettle流程可以正确执行，不报错

ž3)，对应的数据文件生成并格式无误，对应表中有数据并格式无误

ž说明：

ž源表：数据库etltest中存在3张表：

![16143552-f3a01c2eea9f40649e3ea61382e5062e.png][]

目标表：etltest中一张表

![16143650-6f22c8c33c5c4cfe922206618d9e6913.png][]

Kettle具体解决方式，步骤比较繁琐，大家可以直接到下载中下载Demo数据库文件和ktr、kjb来实战测试，这样是最好的，下面的操作仅供参考，转换预览图如下：

![16144841-59e02dd7de194bc7897939ce9f5ab0f2.png][]

操作步骤：  
在EtltestTrans页面下，点击左侧的【Core Objects】，点击【Input】，选中【表  
输入】，拖动到主窗口释放鼠标。  
双击【表输入】图标  
数据库连接选择刚刚创建好的etltest数据库连接，在主窗口写入对应的查询  
语句  
Select \* from trade ，如下图：

![16144107-ebcc0ba5e67449b08b168e674d38f90b.png][]

点击确定完成。  
点击左侧的【Lookup】，选中【数据库查询】，拖动到主窗口释放鼠标。  
按住shift键，用鼠标点中刚才创建的【表输入】，拖动到【数据库查询】上，  
则建立了两个环节之间的连接，如图：

![16144242-a14027a66a41467dbf12c0d8770103a4.png][]

双击【数据库查询】

步骤名称写入account 表查询，数据库连接选择刚刚创建好的etltest 数据库  
连接，查询的表写入account，查询所需的关键字中，表字段写入acctno，比较  
操作符写入“=”，字段1写入acctno。  
在查询表返回的值里面写入custno，确定完成，如下图：

![16144309-fd2e8f0c933a460f8c355486b590834a.png][]  
同上，再创建一个数据库查询，命名为cust表查询，查询的表写入cust，查  
询所需的关键字写入custno=custno，查询表返回的值写入custname，custid，  
custtype，如下图：

![16144356-84cd8d20ebe74dd78867eaff5c784db9.png][]  
点击左侧的【Transform】，选中【过滤记录】，拖动到主窗口释放鼠标。  
点击左侧的【Scripting】，选中两个【Modified Java Script Value】，拖动到主窗  
口释放鼠标。分别双击打开，重命名为“对公类型修改”和“对私类型修改”。  
同时，分别创建【过滤记录】和【对公类型修改】，【对私类型修改】的连接。  
双击【规律记录】打开。  
第一个<field>里面选择custtype，点击<value>，在Enter value 里面写入1，  
确定，如图：

![16144444-d10ef9ca4295425da8d0d9ec41552277.png][]  
在发送true数据给步骤里，选择【对私类型修改】，在发送false数据给步骤  
里，选择【对公类型修改】，确定保存，如图：

![16144509-9fa14f56af9040029043da5e7123fe9a.png][]  
双击【对公类型修改】，在里面写入javascript 脚本语句  
var custtype\_cn='对公客户交易'  
在字段中写入custtype\_cn，类型选为string。确定。  
同理，在【对私类型修改】中，在里面写入javascript脚本语句  
var custtype\_cn='对私客户交易'  
在字段中写入custtype\_cn，类型选为string。确定。  
点击左侧的【Transform】，选中两个【增加常量】，拖动到主窗口释放鼠标。  
分别双击打开，重命名为“增加对公常量”和“增加对私常量”。  
分别建立【对公类型修改】和【对私类型修改】与【增加对公常量】和【增  
加对私常量】的连接，如图：

![16144549-b2868a70ac134e4b959d0d99e1def43f.png][]  
双击【增加对公常量】，名称写入value，类型选择string，值写入“这是一  
笔对公客户发生的交易”，确定保存。  
同理，双击【增加对私常量】，名称写入value，类型选择string，值写入“这  
是一笔对私客户发生的交易”，确定保存。  
点击左侧的【Output】，选中【文本文件输出】，拖动到主窗口释放鼠标。  
建立【增加对公常量】，【增加对私常量】和【文本文件输出】的连接，如图：

![16144622-372f44b44cf94789935a0cf6dfa63f9d.png][]  
双击打开【文本文件输出】，文件名称写入D:\\etltest\\etltest.txt  
点击内容标签，根据情况进行修改，例如  
点击字段标签  
名称依次写入tradeid，acctno，amt，custno，custname，custid，custtype\_cn，  
value，类型根据各个字段实际类型进行选择  
确定保存  
点击保存创建好的transformation。  
点击运行这个转换。  
点击launch，开始运行  
当所有状态都变已完成时，则转换完成，如图：

![16144948-f8aa4e8c54a748a3980268d319410726.png][]

3、Demo下载

[1]: http://www.cnblogs.com/limengqiang/archive/2013/01/16/KettleApply2.html#yycj
[2_DEMO]: http://www.cnblogs.com/limengqiang/archive/2013/01/16/KettleApply2.html#sz
[3_DEMO]: http://www.cnblogs.com/limengqiang/archive/2013/01/16/KettleApply2.html#xiazai
[ExpandedBlockStart.gif]: http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif
[copycode.gif]: http://common.cnblogs.com/images/copycode.gif
[16135830-573f1f91f44640d58ae628c6a76a2482.png]: http://images.cnitblog.com/blog/418758/201301/16135830-573f1f91f44640d58ae628c6a76a2482.png
[16140230-6fff35c8fa5d42a790539823d4b5e37e.png]: http://images.cnitblog.com/blog/418758/201301/16140230-6fff35c8fa5d42a790539823d4b5e37e.png
[16141233-1f82813ebcae4b6ca0bb15b39758bd1d.png]: http://images.cnitblog.com/blog/418758/201301/16141233-1f82813ebcae4b6ca0bb15b39758bd1d.png
[16141940-339571e5fe6f4c69a634daac6d584e6a.png]: http://images.cnitblog.com/blog/418758/201301/16141940-339571e5fe6f4c69a634daac6d584e6a.png
[16142535-5751f4a4bed1430391ac5d00b2ad18da.png]: http://images.cnitblog.com/blog/418758/201301/16142535-5751f4a4bed1430391ac5d00b2ad18da.png
[16142923-eff7b8df5cd14a4796b04bea28277292.png]: http://images.cnitblog.com/blog/418758/201301/16142923-eff7b8df5cd14a4796b04bea28277292.png
[16143552-f3a01c2eea9f40649e3ea61382e5062e.png]: http://images.cnitblog.com/blog/418758/201301/16143552-f3a01c2eea9f40649e3ea61382e5062e.png
[16143650-6f22c8c33c5c4cfe922206618d9e6913.png]: http://images.cnitblog.com/blog/418758/201301/16143650-6f22c8c33c5c4cfe922206618d9e6913.png
[16144841-59e02dd7de194bc7897939ce9f5ab0f2.png]: http://images.cnitblog.com/blog/418758/201301/16144841-59e02dd7de194bc7897939ce9f5ab0f2.png
[16144107-ebcc0ba5e67449b08b168e674d38f90b.png]: http://images.cnitblog.com/blog/418758/201301/16144107-ebcc0ba5e67449b08b168e674d38f90b.png
[16144242-a14027a66a41467dbf12c0d8770103a4.png]: http://images.cnitblog.com/blog/418758/201301/16144242-a14027a66a41467dbf12c0d8770103a4.png
[16144309-fd2e8f0c933a460f8c355486b590834a.png]: http://images.cnitblog.com/blog/418758/201301/16144309-fd2e8f0c933a460f8c355486b590834a.png
[16144356-84cd8d20ebe74dd78867eaff5c784db9.png]: http://images.cnitblog.com/blog/418758/201301/16144356-84cd8d20ebe74dd78867eaff5c784db9.png
[16144444-d10ef9ca4295425da8d0d9ec41552277.png]: http://images.cnitblog.com/blog/418758/201301/16144444-d10ef9ca4295425da8d0d9ec41552277.png
[16144509-9fa14f56af9040029043da5e7123fe9a.png]: http://images.cnitblog.com/blog/418758/201301/16144509-9fa14f56af9040029043da5e7123fe9a.png
[16144549-b2868a70ac134e4b959d0d99e1def43f.png]: http://images.cnitblog.com/blog/418758/201301/16144549-b2868a70ac134e4b959d0d99e1def43f.png
[16144622-372f44b44cf94789935a0cf6dfa63f9d.png]: http://images.cnitblog.com/blog/418758/201301/16144622-372f44b44cf94789935a0cf6dfa63f9d.png
[16144948-f8aa4e8c54a748a3980268d319410726.png]: http://images.cnitblog.com/blog/418758/201301/16144948-f8aa4e8c54a748a3980268d319410726.png