java 集成kettle资源库连接状态判断 kettle 数据源

一、项目背景及业务需求分析

近期在做的项目，自己主要负责服务端接口开发工作。在前期设计过程中，有一个比较棘手的业务流程，用户需要选择数据库中的表挂载到自己的数据资源目录，考虑到后续数据标签等进一步操作，在服务端我们需要把表结构及数据同步复制到本地数据库库中。这个需求在具体实施过程中可以简单描述为多源数据库（目前只限定Oracle、SqlServer、MySQL）的表结构同步及数据的本地化。

考虑到业务复杂度及数据传输等问题，进一步拆分业务需求，主要步骤如下：

（1）源数据表结构读取及本地化构建

（2）源数据表数据同步及全量更新

本次我们主要讨论如何用Kettle实现多源数据库数据同步及全量更新。

首先需要确定以下问题：

1. 多源数据库并不是指已知个数的明确的数据库，而是指通过动态指定连接字符串（服务器连接、数据库类型、数据库名称、用户名、密码等）参数的数据库。
2. 数据表结构也是不确定的

解决以上问题需要同时考虑多源数据连接及各类数据库对结构属性定义差异，同时还要做到定时对数据进行更新，而Kettle的优势恰恰可以满足这两个需求，通过kettle进行数据同步可以省去数据字段转换的复杂过程，使开发人员只需关注源和目标数据即可。

项目涉及的数据库主要分为三类，一类为平台数据库，用以存储业务数据及数据库连接等相关配置数据；一类为本地化数据库，用以存放多源数据本地化的数据；最后一类为源数据数据库。

二、技术路线

基于以上的需求分析，此次准备采用Kettle解决多源数据同步问题。解决方案的技术路线梳理为以下几步：

1. 获取待更新数据表列表
2. 删除本地库原表数据
3. 参数化配置动态数据库链接
4. 获取新数据并插入本地表

 

相关操作涉及的数据表结构如下：

RDM_DB_CONNECTION表存储了数据库连接信息，RDM_RESOURCE表存储资源相关信息，记录了远程源表与本地目标表信息。值得一提的是为了避免不同数据库表可能存在同一表名的情况，源表名与目标表名并不是一致的。此处的目标表名以关键字“T”+创建时间命名。

三、Kettle实现步骤

Kettle整个作业流程如下：

 

1. 参数化动态配置数据库连接

Kettle可以直接指定数据库连接信息，也可以通过指定变量，定义一个动态的数据库连接，在实际使用过程中，通过xml文件或者转换/作业命名参数的形式进行赋值。

变量的格式要求为“${Param}”，如下图，通过指定指定动态变量配置一个Oracle数据库连接。注意，在密码输入的时候虽然显示为“●“的形式，在输入时仍可用“${Param}”。

本项目主要设置了Oracle、SqlServer、MySQL3类参数化动态数据库连接实例，同时通过定参设定两个业务数据库和本地化数据库连接实例。设置完成后，对以上5个实例进行共享。

1. 获取待更新数据源

1. 获取创建及更新时间参数的设置目的是为了对RDM_RESOURCE数据进行过滤，筛选满足指定时间段内创建或者指定更新时间间隔的数据记录。

 

1. 获取待更新数据表：
2. 根据条件筛选待更新数据列表
3. 选择从步骤插入数据->字段选择，SQL语句中占位符“？”部分会根据“字段选择”结果列表依次替换

该步骤会关联RDM_RESOURCE表与RDM_DB_CONNECTION表，最终预览结果如下图：

1. 动态数据库表全量更新作业：

以作业形式进行动态数据库更新主要原因是在此过程中通过上一处理结果中CONNTYPE进行数据库类型选择判断，分别进行数据更新处理。

设置作业属性，指定该作业全局命名参数，并指定“复制上一步结果到命名参数”。

此设置可以将数据表配置参数转化为作业的命名参数，使得做业内种的转换/子作业可以直接使用参数值实例化前文配置的数据库连接。注意此处数据库连接的的命名参数名应与前文配置的数据库连接参数名保持一致。

 

1. 删除目标表数据：

该步骤通过采用占位符设置动态表参数“TARGET_TABLE”，删除本地数据库表原数据。

1. 检验CONNTYPE类型

即通过命名参数中CONNTYPE变量的值分发不同数据库处理转换作业。

 

1. Oracle/MySQL/SqlServer全量更新：

该转换实现源表数据到目标表数据的本地化全量更新。

 

1. 检验目标表是否存在

检验本地库中是否存在目标表，防止配置数据存储错误或在数据表创建失败情况。

SQL语句采用命名参数变量形式“${TARGET_TABLE}”

1. ORACEL-原始表输入：

原始表输入与上一步配置基本相同，数据库连接选择前文配置的Oracle动态数据库连接。

SQL语句中除原始表字段外，添加一个以目标表名为值的静态字段”TARGET_TABLE”，此处主要为下一步数据写入做准备。

1. 目标表输出：

由于输出表名也是存储在业务数据配置中，而“表输出”工具并不能像数据库连接等使用命名参数进行配置，所以此处采用指定上一步结果中的一个字段作为目标表名。这也是上一步中添加TARGET_TABLE字段的目的所在。

同时为了提升数据插入的速度，不仅工具内制定了使用批量插入，在作业层面通过指定并行进一步提升效率。

1. 资源表更新

在数据表数据更新完成后，进行最后一步，对资源表UPDATE_TIME字段数据更新，设置为当前最新时间。

由于kettle获取的系统时间格式为“yyyy/MM/dd HH:mm:ss.sss”，精确到小数秒，所以预先采用“字段选择”工具对时间进行格式转换。最后采用“执行SQL语句”工具进行数据更新。工具同样采用在SQL语句中使用占位符设置动态变量的方式。

四、测试与部署

1. 测试数据表与数据生成

为了测试Kettle工具是否可行，分别在Oracle/MySQL/MSSQL中新建了3个表，表结构如下：

用Kettle新建一个转换，用于模拟原始表数据更新。每次执行向各表中插入100条数据。

2. 项目部署

（1）创建.bat脚本文件

1. 使用Window自带任务计划程序分别部署KETTLE模拟数据更新任务和数据同步，触发器设置为每分钟循环一次。

3. 脚本执行

       通过以上步骤，脚本正常执行，可实现快速对数据的更新操作。

五、小结

此次利用Kettle实现多源数据的定时全量更新作业。梳理一遍实现过程，整个流程相对比较简单易懂，主要的精力还是耗在了如何实现参数化配置数据库连接，如何将业务数据库中的配置参数传到各个作业和转换过程中，并使之发挥应有的效用。

受限于时间紧张，整个作业目前还是存在不少可改正和优化的地方，

1. 本地数据删除步骤应放置再数据更新操作成功后，以防止数据更新操作失败，原始数据源又被删除的尴尬局面。修正这一个隐患可以对目标表数据结构进行改造，增加INSERT_TIME标识字段，以此作为识别标识，在数据删除时只需将大于更新周期的数据进行删除即可。
2. 由于没有一个比较好的思路可以识别源数据更新的数据，所以不能做到增量更新.

数据更新作业流程的设置容错性较差，任何一个步骤出现错误都会导致整个作业失败。针对数据库多类型、字段属性多类型这种情况，容错性有待进一步设计和提高。