kettle查询mysql获取uuid_kettle生成主鍵UUID和調用轉換作業

kettle

項目中使用軟件，我的感覺是不必追求最新版，穩定是優先考慮的。3.2版本也是不錯的。使用中感覺kettle總體表現還是不錯的，使用方式也都比較好理解。但其中個別的地方還是給初學者制造了些小麻煩。

以下是我的一點經驗：

1. 資源庫在原有數據庫中建立了很多Kettle的表，建議最好不用，就用文件存儲

2. 參數的傳遞問題

Job 定時任務的時候，可以在其所包含的Transformation中，由“獲取系統信息”組件定義時間參數，由“表輸入”

組件的SQL動態獲取。

不過SQL中的參數不能用${param}這種形式獲取，而是用？來代替。其中的緣由可以參見官方FAQ里面Argument 和 variables 的區別。

3. UUID的生成

如果你的ID是UUID，需要kettle來生成。就用“腳本”里面的“Modified JavaScript Value”組件，代碼如下：

Js代碼

//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

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

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

Java代碼

@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

Java代碼

@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();

}

kettle

以下是我的一點經驗：

1. 資源庫在原有數據庫中建立了很多Kettle的表，建議最好不用，就用文件存儲

2. 參數的傳遞問題

Job 定時任務的時候，可以在其所包含的Transformation中，由“獲取系統信息”組件定義時間參數，由“表輸入”

組件的SQL動態獲取。

不過SQL中的參數不能用${param}這種形式獲取，而是用？來代替。其中的緣由可以參見官方FAQ里面Argument 和 variables 的區別。

3. UUID的生成

如果你的ID是UUID，需要kettle來生成。就用“腳本”里面的“Modified JavaScript Value”組件，代碼如下：

Js代碼

//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

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

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 執行TransportationJava代碼

@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 執行JobJava代碼

@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();

}