KETTLEJAVAAPI开发实战记录.doc

前言： 为什么要用Kettle和KETTLE JAVA API？Kettle是什么？kettle：是一个开源ETL工具。kettle提供了基于java的图形化界面，使用很方便，kettle的ETL工具集合也比较多，常用的ETL工具都包含了。为什么使用KETTLE JAVA API：就像kettle文档所说：KETTLE JAVA API ： Program your own Kettle transformation，kettle提供了基于JAVA的脚步编写功能，可以灵活地自定义ETL过程，使自行定制、批量处理等成为可能，这才是一个程序员需要做的工作，而不仅是象使用word一样操作kettle用户界面。KETTLE JAVA API 实战操作记录：一、 搭建环境 ：到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 extraTransBuilder.java的内容原样拷贝到你的TransBuilder.java里。四、 根据需要编辑源码。并需要对原程序进行如下修改，在头部增加：import org.eclipse.swt.dnd.Transfer；/这个包被遗漏了，原始位置kettle根目录libswtwin32swt.jar/add by chq（） on 2006.07.20（后来发现，不必加这个引用，因为编译时不需要）五、 编译准备，在eclipse中增加jar包，主要包括（主要依据extraTransBuilder.bat）：libkettle.jarlibextCacheDB.jarlibextSQLBaseJDBC.jarlibextactivation.jarlibextdb2jcc.jarlibextdb2jcc_license_c.jarlibextedtftpj-1.4.5.jarlibextfirebirdsql-full.jarlibextfirebirdsql.jarlibextgis-shape.jarlibexthsqldb.jarlibextifxjdbc.jarlibextjavadbf.jarlibextjconn2.jarlibextjs.jarlibextjt400.jarlibextjtds-1.1.jarlibextjxl.jarlibextktable.jarlibextlog4j-1.2.8.jarlibextmail.jarlibextmysql-connector-java-3.1.7-bin.jarlibextojdbc14.jarlibextorai18n.jarlibextpg74.215.jdbc3.jarlibextedbc.jar（注意 ：下面这个包被遗漏了，要加上。原始位置kettle根目录libswtwin32swt.jar）libswtwin32swt.jar六、 编译成功后，准备运行为使程序不必登陆就可以运行，需要设置环境署文件：perties，位置在用户目录里，一般在 Documents and Settings用户.kettle，主要内容如下：KETTLE_REPOSITORY=kettlem80KETTLE_USER=adminKETTLE_PASSWORD=passwd七、 好了，现在可以运行一下了，看看数据是不是已经拷贝到目标表了。 以下为修改后的程序源码：-package name.chq.test; import java.io.DataOutputStream;import java.io.File;import java.io.FileOutputStream; import be.ibridge.kettle.core.Const;import be.ibridge.kettle.core.LogWriter;import be.ibridge.kettle.core.NotePadMeta;import be.ibridge.kettle.core.database.Database;import be.ibridge.kettle.core.database.DatabaseMeta;import be.ibridge.kettle.core.exception.KettleException;import be.ibridge.kettle.core.util.EnvUtil;import be.ibridge.kettle.trans.StepLoader;import be.ibridge.kettle.trans.Trans;import be.ibridge.kettle.trans.TransHopMeta;import be.ibridge.kettle.trans.TransMeta;import be.ibridge.kettle.trans.step.StepMeta;import be.ibridge.kettle.trans.step.StepMetaInterface;import be.ibridge.kettle.trans.step.selectvalues.SelectValuesMeta;import be.ibridge.kettle.trans.step.tableinput.TableInputMeta;import be.ibridge.kettle.trans.step.tableoutput.TableOutputMeta; /这个包被遗漏了，原始位置kettle根目录libswtwin32swt.jar/add by chq(link=/link) on 2006.07.20/import org.eclipse.swt.dnd.Transfer; /* * Class created to demonstrate the creation of transformations on-the-fly. * * author Matt * */public class TransBuilder public static final String databasesXML = + + target + 5 + ORACLE + Native + test1 + 1521 + testuser + pwd + + + + + EXTRA_OPTION_MYSQL.defaultFetchSize500 + EXTRA_OPTION_MYSQL.useCursorFetchtrue + PORT_NUMBER1521 + + , + + source + 2 + ORACLE + Native + test2 + 1521 + testuser + pwd2 + + + + + EXTRA_OPTION_MYSQL.defaultFetchSize500 + EXTRA_OPTION_MYSQL.useCursorFetchtrue + PORT_NUMBER1521 + + ;前言：为什么要用Kettle和KETTLE JAVA API？Kettle是什么？kettle：是一个开源ETL工具。kettle提供了基于java的图形化界面，使用很方便，kettle的ETL工具集合也比较多，常用的ETL工具都包含了。为什么使用KETTLE JAVA API：就像kettle文档所说：KETTLE JAVA API ： Program your own Kettle transformation，kettle提供了基于JAVA的脚步编写功能，可以灵活地自定义ETL过程，使自行定制、批量处理等成为可能，这才是一个程序员需要做的工作，而不仅是象使用word一样操作kettle用户界面。KETTLE JAVA API 实战操作记录：一、 搭建环境 ：到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 extraTransBuilder.java的内容原样拷贝到你的TransBuilder.java里。四、 根据需要编辑源码。并需要对原程序进行如下修改，在头部增加：import org.eclipse.swt.dnd.Transfer；/这个包被遗漏了，原始位置kettle根目录libswtwin32swt.jar/add by chq（） on 2006.07.20（后来发现，不必加这个引用，因为编译时不需要）五、 编译准备，在eclipse中增加jar包，主要包括（主要依据extraTransBuilder.bat）：libkettle.jarlibextCacheDB.jarlibextSQLBaseJDBC.jarlibextactivation.jarlibextdb2jcc.jarlibextdb2jcc_license_c.jarlibextedtftpj-1.4.5.jarlibextfirebirdsql-full.jarlibextfirebirdsql.jarlibextgis-shape.jarlibexthsqldb.jarlibextifxjdbc.jarlibextjavadbf.jarlibextjconn2.jarlibextjs.jarlibextjt400.jarlibextjtds-1.1.jarlibextjxl.jarlibextktable.jarlibextlog4j-1.2.8.jarlibextmail.jarlibextmysql-connector-java-3.1.7-bin.jarlibextojdbc14.jarlibextorai18n.jarlibextpg74.215.jdbc3.jarlibextedbc.jar（注意 ：下面这个包被遗漏了，要加上。原始位置kettle根目录libswtwin32swt.jar）libswtwin32swt.jar六、 编译成功后，准备运行为使程序不必登陆就可以运行，需要设置环境署文件：perties，位置在用户目录里，一般在 Documents and Settings用户.kettle，主要内容如下：KETTLE_REPOSITORY=kettlem80KETTLE_USER=adminKETTLE_PASSWORD=passwd七、 好了，现在可以运行一下了，看看数据是不是已经拷贝到目标表了。 以下为修改后的程序源码：package name.chq.test; import java.io.DataOutputStream;import java.io.File;import java.io.FileOutputStream; import be.ibridge.kettle.core.Const;import be.ibridge.kettle.core.LogWriter;import be.ibridge.kettle.core.NotePadMeta;import be.ibridge.kettle.core.database.Database;import be.ibridge.kettle.core.database.DatabaseMeta;import be.ibridge.kettle.core.exception.KettleException;import be.ibridge.kettle.core.util.EnvUtil;import be.ibridge.kettle.trans.StepLoader;import be.ibridge.kettle.trans.Trans;import be.ibridge.kettle.trans.TransHopMeta;import be.ibridge.kettle.trans.TransMeta;import be.ibridge.kettle.trans.step.StepMeta;import be.ibridge.kettle.trans.step.StepMetaInterface;import be.ibridge.kettle.trans.step.selectvalues.SelectValuesMeta;import be.ibridge.kettle.trans.step.tableinput.TableInputMeta;import be.ibridge.kettle.trans.step.tableoutput.TableOutputMeta; /这个包被遗漏了，原始位置kettle根目录libswtwin32swt.jar/add by chq(link=/link) on 2006.07.20/import org.eclipse.swt.dnd.Transfer; /* * Class created to demonstrate the creation of transformations on-the-fly. * * author Matt * */public class TransBuilder public static final String databasesXML = + + target + 5 + ORACLE + Native + test1 + 1521 + testuser + pwd + + + + + EXTRA_OPTION_MYSQL.defaultFetchSize500 + EXTRA_OPTION_MYSQL.useCursorFetchtrue + PORT_NUMBER1521 + + , + + source + 2 + ORACLE + Native + test2 + 1521 + testuser + pwd2 + + + + + EXTRA_OPTION_MYSQL.defaultFetchSize500 + EXTRA_OPTION_MYSQL.useCursorFetchtrue + PORT_NUMBER1521 + + ;/ / create the source step. / String fromstepname = read from + sourceTableName + ; TableInputMeta tii = new TableInputMeta(); tii.setDatabaseMeta(sourceDBInfo); String selectSQL = SELECT +Const.CR; for (int i=0;i0) selectSQL+=, ; else selectSQL+= ; selectSQL+=sourceFieldsi+Const.CR; selectSQL+=FROM +sourceTableName; tii.setSQL(selectSQL); StepLoader steploader = StepLoader.getInstance(); String fromstepid = steploader.getStepPluginID(tii); StepMeta fromstep = new StepMeta(log, fromstepid, fromstepname, (StepMetaInterface) tii); fromstep.setLocation(150, 100); fromstep.setDraw(true); fromstep.setDescription(Reads information from table + sourceTableName + on database + sourceDBInfo + ); transMeta.addStep(fromstep); / / add logic to rename fields / Use metadata logic in SelectValues, use SelectValueInfo. / /* 不必改名或映射 add by chq() on 2006.07.20 SelectValuesMeta svi = new SelectValuesMeta(); svi.allocate(0, 0, sourceFields.length); for (int i = 0; i sourceFields.length; i+) svi.getMetaName()i = sourceFieldsi; svi.getMetaRename()i = targetFieldsi; String selstepname = Rename field names; String selstepid = steploader.getStepPluginID(svi); StepMeta selstep = new StepMeta(log, selstepid, selstepname, (StepMetaInterface) svi); selstep.setLocation(350, 100); selstep.setDraw(true); selstep.setDescription(Rename field names); transMeta.addStep(selstep); TransHopMeta shi = new TransHopMeta(fromstep, selstep); transMeta.addTransHop(shi); fromstep = selstep; /设定了新的起点 by chq(link=/link) on 2006.07.20 */ / / Create the target step. / / / Add the TableOutputMeta step. / String tostepname = write to + targetTableName + ; TableOutputMeta toi = new TableOutputMeta(); toi.setDatabase(targetDBInfo); toi.setTablename(targetTableName); toi.setCommitSize(200); toi.setTruncateTable(true); String tostepid = steploader.getStepPluginID(toi); StepMeta tostep = new StepMeta(log, tostepid, tostepname, (StepMetaInterface) toi); tostep.setLocation(550, 100); tostep.setDraw(true); tostep.setDescription(Write information to table + targetTableName + on database + targetDBInfo + ); transMeta.addStep(tostep); / / Add a hop between the two steps. / TransHopMeta hi = new TransHopMeta(fromstep, tostep); transMeta.addTransHop(hi);/ OK, if were still here: overwrite the current transformation. return transMeta; catch (Exception e) throw new KettleException(An unexpected error occurred creating the new transformation, e); /* * 1) create a new transformation * 2) save the transformation as XML file * 3) generate the SQL for the target table * 4) Execute the transformation * 5) drop the target table to make this program repeatable * * param args */ public static void main(String args) throws Exception EnvUtil.environmentInit(); / Init the logging. LogWriter log = LogWriter.getInstance(TransBuilder.log, true, LogWriter.LOG_LEVEL_DETAILED); / Load the Kettle steps & plugins StepLoader stloader = StepLoader.getInstance(); if (!stloader.read() log.logError(TransBuilder, Error loading Kettle steps & plugins. stopping now!); return; / The parameters we want, optionally this can be String fileName = NewTrans.xml; String transformationName = Test Transformation; String sourceDatabaseName = source; String sourceTableName = testuser.source_table; String sourceFields = * ; String targetDatabaseName = target; String targetTableName = testuser.target_table; String targetFields = * ; / Generate