开发技术KETTLE JAVA API 开发实战记录.docx
《开发技术KETTLE JAVA API 开发实战记录.docx》由会员分享,可在线阅读,更多相关《开发技术KETTLE JAVA API 开发实战记录.docx(15页珍藏版)》请在冰豆网上搜索。
开发技术KETTLEJAVAAPI开发实战记录
开发技术:
KETTLEJAVAAPI开发实战记录
前言:
为什么要用Kettle和KETTLEJavaAPI?
Kettle是什么?
kettle:
是一个开源ETL工具。
kettle提供了基于java的图形化界面,使用很方便,kettle的ETL工具集合也比较多,常用的ETL工具都包含了。
为什么使用KETTLEJAVAAPI:
就像kettle文档所说:
KETTLEJAVAAPI:
ProgramyourownKettletransformation,kettle提供了基于JAVA的脚步编写功能,可以灵活地自定义ETL过程,使自行定制、批量处理等成为可能,这才是一个程序员需要做的工作,而不仅是象使用Word一样操作kettle用户界面。
KETTLEJAVAAPI实战操作记录:
一、搭建环境:
到http:
//www.kettle.be网站下载kettle的源码包,加压缩,例如解压缩到d:
\kettle目录
二、打开eclipse,新建一个项目,要使用jdk1.5.0,因为kettle的要使用System.getenv(),只有在jdk1.5.0才被支持。
提起getenv(),好像有一段几起几落的记录,曾一度被抛弃,现在又被jdk1.5支持了。
三、建一个class:
TransBuilder.java,可以把d:
\kettle\extra\TransBuilder.java的内容原样拷贝到你的TransBuilder.java里。
四、根据需要编辑源码。
并需要对原程序进行如下修改,在头部增加:
importorg.eclipse.swt.dnd.Transfer;
//这个包被遗漏了,原始位置kettle根目录 \libswt\win32\swt.jar
//addbychq(www.chq.name)on2006.07.20
(后来发现,不必加这个引用,因为编译时不需要)
五、编译准备,在eclipse中增加jar包,主要包括(主要依据extra\TransBuilder.bat):
\lib\kettle.jar
\libext\CacheDB.jar
\libext\SQLBaseJDBC.jar
\libext\activation.jar
\libext\db2jcc.jar
\libext\db2jcc_license_c.jar
\libext\edtFTPj-1.4.5.jar
\libext\firebirdsql-full.jar
\libext\firebirdsql.jar
\libext\gis-shape.jar
\libext\hsqldb.jar
\libext\ifxjdbc.jar
\libext\javadbf.jar
\libext\jconn2.jar
\libext\js.jar
\libext\jt400.jar
\libext\jtds-1.1.jar
\libext\jxl.jar
\libext\ktable.jar
\libext\log4j-1.2.8.jar
\libext\mail.jar
\libext\mysql-connector-java-3.1.7-bin.jar
\libext\ojdbc14.jar
\libext\orai18n.jar
\libext\pg74.215.jdbc3.jar
\libext\edbc.jar
(注意:
下面这个包被遗漏了,要加上。
原始位置kettle根目录\libswt\win32\swt.jar)
\libswt\win32\swt.jar
六、编译成功后,准备运行
为使程序不必登陆就可以运行,需要设置环境署文件:
kettle.properties,位置在用户目录里,一般在\DocumentsandSettings\用户\.kettle\,主要内容如下:
KETTLE_REPOSITORY=kettle@m80
KETTLE_USER=admin
KETTLE_PASSWORD=passwd
七、好了,现在可以运行一下了,看看数据是不是已经拷贝到目标表了。
以下为修改后的程序源码:
++++++++++++++++++++++++++++++++
packagename.chq.test;
importjava.io.DataOutputStream;
importjava.io.File;
importjava.io.FileOutputStream;
importbe.ibridge.kettle.core.Const;
importbe.ibridge.kettle.core.LogWriter;
importbe.ibridge.kettle.core.NotePadMeta;
importbe.ibridge.kettle.core.database.Database;
importbe.ibridge.kettle.core.database.DatabaseMeta;
importbe.ibridge.kettle.core.exception.KettleException;
importbe.ibridge.kettle.core.util.EnvUtil;
importbe.ibridge.kettle.trans.StepLoader;
importbe.ibridge.kettle.trans.Trans;
importbe.ibridge.kettle.trans.TransHopMeta;
importbe.ibridge.kettle.trans.TransMeta;
importbe.ibridge.kettle.trans.step.StepMeta;
importbe.ibridge.kettle.trans.step.StepMetaInterface;
importbe.ibridge.kettle.trans.step.selectvalues.SelectValuesMeta;
importbe.ibridge.kettle.trans.step.tableinput.TableInputMeta;
importbe.ibridge.kettle.trans.step.tableoutput.TableOutputMeta;
//这个包被遗漏了,原始位置kettle根目录\libswt\win32\swt.jar
//addbychq([link=http:
//www.chq.name]www.chq.name[/link])on2006.07.20
//importorg.eclipse.swt.dnd.Transfer;
/**
*Classcreatedtodemonstratethecreationoftransformationson-the-fly.
*
*@authorMatt
*
*/
publicclassTransBuilder
{
publicstaticfinalString[]databasesXML={
"
xmlversion=\"1.0\"encoding=\"UTF-8\"?
>"+
""+
"target"+
"192.168.17.35"+
"ORACLE"+
"Native"+
"test1"+
"1521"+
"testuser"+
"pwd"+
""+
""+
""+
""+
"EXTRA_OPTION_MYSQL.defaultFetchSize
500"+
"EXTRA_OPTION_MYSQL.useCursorFetch
true"+
"PORT_NUMBER
1521"+
""+
"",
"
xmlversion=\"1.0\"encoding=\"UTF-8\"?
>"+
""+
"source"+
"192.168.16.12"+
∙ "ORACLE"+
"Native"+
"test2"+
"1521"+
"testuser"+
"pwd2"+
""+
""+
""+
∙""+
"EXTRA_OPTION_MYSQL.defaultFetchSize
500"+
"EXTRA_OPTION_MYSQL.useCursorFetch
true"+
"PORT_NUMBER
1521"+
""+
""
};
/**
*CreatesanewTransformationusinginputparameterssUChasthetablenametoreadfrom.
*@paramtransformationNameThenameofthetransformation
*@paramsourceDatabaseNameThenameofthedatabasetoreadfrom
*@paramsourceTableNameThenameofthetabletoreadfrom
*@paramsourceFieldsThefieldnameswewanttoreadfromthesourcetable
*@paramtargetDatabaseNameThenameofthetargetdatabase
*@paramtargetTableNameThenameofthetargettablewewanttowriteto
*@paramtargetFieldsThenamesofthefieldsinthetargettable(samenumberoffieldsassourceFields)
*@returnAnewtransformation
*@throwsKettleExceptionIntherarecasesomethinggoeswrong
*/
publicstaticfinalTransMetabuildCopyTable(
StringtransformationName,StringsourceDatabaseName,StringsourceTableName,
String[]sourceFields,StringtargetDatabaseName,StringtargetTableName,
String[]targetFields)
throwsKettleException
{
LogWriterlog=LogWriter.getInstance();
EnvUtil.environmentInit();
try
{
//
//Createanewtransformation...
//
TransMetatransMeta=newTransMeta();
transMeta.setName(transformationName);
//Addthedatabaseconnections
for(inti=0;i {
DatabaseMetadatabaseMeta=newDatabaseMeta(databasesXML[i]);
transMeta.addDatabase(databaseMeta);
}
DatabaseMetasourceDBInfo=transMeta.findDatabase(sourceDatabaseName);
DatabaseMetatargetDBInfo=transMeta.findDatabase(targetDatabaseName);
//
//Addanote
//
Stringnote="Readsinformationfromtable["+sourceTableName+"]ondatabase["
+sourceDBInfo+"]"+Const.CR;
note+="Afterthat,itwritestheinformationtotable["+targetTableName+"]ondatabase["+targetDBInfo+"]";
NotePadMetani=newNotePadMeta(note,150,10,-1,-1);
transMeta.addNote(ni);
//
//createthesourcestep...
//
Stringfromstepname="readfrom["+sourceTableName+"]";
TableInputMetatii=newTableInputMeta();
tii.setDatabaseMeta(sourceDBInfo);
StringselectSQL="SELECT"+Const.CR;
for(inti=0;i {
/*modibychq(www.chq.name):
use*toreplacethefields,经分析,以下语句可以处理‘*‘*/
if(i>0)
selectSQL+=",";
elseselectSQL+=" ";
selectSQL+=sourceFields[i]+Const.CR;
}
selectSQL+="FROM"+sourceTableName;
∙tii.setSQL(selectSQL);
StepLoadersteploader=StepLoader.getInstance();
Stringfromstepid=steploader.getStepPluginID(tii);
StepMetafromstep=newStepMeta(log,fromstepid,fromstepname,(StepMetaInterface)tii);
fromstep.setLocation(150,100);
fromstep.setDraw(true);
fromstep.setDescription("Readsinformationfromtable["+sourceTableName
+"]ondatabase["+sourceDBInfo+"]");
transMeta.addStep(fromstep);
//
//addlogictorenamefields
//UsemetadatalogicinSelectValues,useSelectValueInfo...
//
/*不必改名或映射addbychq(www.chq.name)on2006.07.20
SelectValuesMetasvi=newSelectValuesMeta();
svi.allocate(0,0,sourceFields.length);
∙ for(inti=0;i {
svi.getMetaName()[i]=sourceFields[i];
svi.getMetaRename()[i]=targetFields[i];
}
Stringselstepname="Renamefieldnames";
Stringselstepid=steploader.getStepPluginID(svi);
StepMetaselstep=newStepMeta(log,selstepid,selstepname,(StepMetaInterface)svi);
selstep.setLocation(350,100);
selstep.setDraw(true);
selstep.setDescription("Renamefieldnames");
transMeta.addStep(selstep);
TransHopMetashi=newTransHopMeta(fromstep,selstep);
transMeta.addTransHop(shi);
fromstep=selstep;//设定了新的起点bychq([link=http:
//www.chq.name]www.chq.name[/link])on2006.07.20
*/
//
//Createthetargetstep...
∙ //
//
//AddtheTableOutputMetastep...
//
Stringtostep