kettle
Kettle 已经归属Pentaho门下,4.1版本的下载地址:http://sourceforge.net/projects/pentaho/files/Data%20Integration/4.1.0-stable/
项目中使用软件,我的感觉是不必追求最新版,稳定是优先考虑的。3.2版本也是不错的。使用中感觉kettle总体表现还是不错的,使用方式也都比较好理解。但其中个别的地方还是给初学者制造了些小麻烦。
以下是我的一点经验:
1. 资源库在原有数据库中建立了很多Kettle的表,建议最好不用,就用文件存储
2. 参数的传递问题
Job 定时任务的时候,可以在其所包含的Transformation中,由“获取系统信息”组件定义时间参数,由“表输入”
组件的SQL动态获取。
不过SQL中的参数不能用${param}这种形式获取,而是用?来代替。其中的缘由可以参见官方FAQ里面Argument 和 variables 的区别。
3. UUID的生成
如果你的ID是UUID,需要kettle来生成。就用“脚本”里面的“Modified Java Script Value”组件,代码如下:
- //Script here
- function UUID(){
- this.id = this.createUUID();
- }
- UUID.prototype.valueOf = function(){ return this.id; }
- UUID.prototype.toString = function(){ return this.id; }
- UUID.prototype.createUUID = function(){
- var dg = new Date(1582, 10, 15, 0, 0, 0, 0);
- var dc = new Date();
- var t = dc.getTime() - dg.getTime();
- var h = '';
- var tl = UUID.getIntegerBits(t,0,31);
- var tm = UUID.getIntegerBits(t,32,47);
- var thv = UUID.getIntegerBits(t,48,59) + '1';
- var csar = UUID.getIntegerBits(UUID.rand(4095),0,7);
- var csl = UUID.getIntegerBits(UUID.rand(4095),0,7);
- var n = UUID.getIntegerBits(UUID.rand(8191),0,7) +
- UUID.getIntegerBits(UUID.rand(8191),8,15) +
- UUID.getIntegerBits(UUID.rand(8191),0,7) +
- UUID.getIntegerBits(UUID.rand(8191),8,15) +
- UUID.getIntegerBits(UUID.rand(8191),0,15);
- return tl + h + tm + h + thv + h + csar + csl + h + n;
- }
- UUID.getIntegerBits = function(val,start,end){
- var base16 = UUID.returnBase(val,16);
- var quadArray = new Array();
- var quadString = '';
- var i = 0;
- for(i=0;i<base16.length;i++){
- quadArray.push(base16.substring(i,i+1));
- }
- for(i=Math.floor(start/4);i<=Math.floor(end/4);i++){
- if(!quadArray[i] || quadArray[i] == '') quadString += '0';
- else quadString += quadArray[i];
- }
- return quadString;
- }
- UUID.returnBase = function(number, base){
- var convert = ['0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
- if (number < base) var output = convert[number];
- else {
- var MSD = '' + Math.floor(number / base);
- var LSD = number - MSD*base;
- if (MSD >= base) var output = this.returnBase(MSD,base) + convert[LSD];
- else var output = convert[MSD] + convert[LSD];
- }
- return output;
- }
- UUID.rand = function(max){
- return Math.floor(Math.random() * max);
- }
- var ID=new UUID().id;
4. Java 执行Transportation
- @Test
- public void executeTrans() throws KettleException {
- try {
- StepLoader.init();
- EnvUtil.environmentInit();
- TransMeta transMeta = new TransMeta("d:/test.ktr");
- Trans trans = new Trans(transMeta);
- trans.execute(null); // Pass arguments instead of null.
- trans.waitUntilFinished();
- if (trans.getErrors() > 0) {
- throw new RuntimeException("There were errors during transformation execution.");
- }
- } catch (KettleException e) {
- System.out.println(e);
- }
- }
5. Java 执行Job
- @Test
- public void executeTrans() throws KettleException {
- EnvUtil.environmentInit();
- JobEntryLoader.init();
- StepLoader.init();
- LogWriter log = LogWriter.getInstance("TransTest.log", true, LogWriter.LOG_LEVEL_DETAILED);
- JobMeta jobMeta = new JobMeta(log, "d:/testjob.kjb", null);
- Job job = new Job(log, StepLoader.getInstance(), null, jobMeta);
- jobMeta.setInternalKettleVariables(job);
- job.execute();
- job.waitUntilFinished();
- }