1、调用本地Job和Trans
较简单不用多说没有遇到任何问题,以下是代码:
import org.pentaho.di.core.KettleEnvironment;import org.pentaho.di.core.database.DatabaseMeta;import org.pentaho.di.core.exception.KettleException;import org.pentaho.di.core.util.EnvUtil;import org.pentaho.di.job.Job;import org.pentaho.di.job.JobMeta;import org.pentaho.di.repository.Repository;import org.pentaho.di.trans.Trans;import org.pentaho.di.trans.TransMeta;import org.pentaho.di.repository.RepositoryDirectoryInterface;import org.pentaho.di.repository.kdr.KettleDatabaseRepository;import org.pentaho.di.repository.kdr.KettleDatabaseRepositoryMeta;public class MyUtils { public static void main(String[] args) { String[] params = {"1","content","d:\\test1.txt"}; runTransfer(params, "D:\\一起浪\\数据资源管理平台\\kettle\\test.ktr"); } /** * 运行转换文件方法 * @param params 多个参数变量值 * @param ktrPath 转换文件的路径,后缀ktr */ public static void runTransfer(String[] params, String ktrPath) { Trans trans = null; try { // // 初始化 // 转换元对象 KettleEnvironment.init();// 初始化 EnvUtil.environmentInit(); TransMeta transMeta = new TransMeta(ktrPath); // 转换 trans = new Trans(transMeta); // 执行转换 trans.execute(params); // 等待转换执行结束 trans.waitUntilFinished(); // 抛出异常 if (trans.getErrors() > 0) { throw new Exception( "There are errors during transformation exception!(传输过程中发生异常)"); } } catch (Exception e) { e.printStackTrace(); } } /** * java 调用 kettle 的job * * @param jobname * 如: String fName= "D:\\kettle\\informix_to_am_4.ktr"; */ public static void runJob(String[] params, String jobPath) { try { KettleEnvironment.init(); // jobname 是Job脚本的路径及名称 JobMeta jobMeta = new JobMeta(jobPath, null); Job job = new Job(null, jobMeta); // 向Job 脚本传递参数,脚本中获取参数值:${参数名} // job.setVariable(paraname, paravalue); job.setVariable("id", params[0]); job.setVariable("content", params[1]); job.setVariable("file", params[2]); job.start(); job.waitUntilFinished(); if (job.getErrors() > 0) { throw new Exception( "There are errors during job exception!(执行job发生异常)"); } } catch (Exception e) { e.printStackTrace(); } } }
2、连接oracle资源库
调用资源库中的Trans,并在本地执行,以下是代码:
/** * 执行存储在数据库资源库中的转换 * * @Description: * @throws KettleException * @author Jeffy * @since:2016-9-23 */ public static void executeTrans(String transName) throws KettleException { //初始化kettle环境 KettleEnvironment.init(); //创建资源库对象,此时的对象还是一个空对象 KettleDatabaseRepository repository = new KettleDatabaseRepository(); //创建资源库数据库对象,类似我们在spoon里面创建资源库 DatabaseMeta dataMeta = new DatabaseMeta("ETL", "Oracle", "Native(JDBC)", "127.0.0.1", "DBname", "1521", "username", "password"); //资源库元对象,名称参数,id参数,描述等可以随便定义 KettleDatabaseRepositoryMeta kettleDatabaseMeta = new KettleDatabaseRepositoryMeta("ETL", "ETL", "ETL description",dataMeta); //给资源库赋值 repository.init(kettleDatabaseMeta); //连接资源库 repository.connect("admin","admin"); //根据变量查找到模型所在的目录对象,此步骤很重要。 RepositoryDirectoryInterface directory = repository.findDirectory("/test"); //创建ktr元对象 TransMeta transMeta = ((Repository) repository).loadTransformation(transName, directory, null, true, null ) ; //执行参数 String[] params = {"1","07bb40f7200448","d:\\haha.txt"}; //创建ktr Trans trans = new Trans(transMeta); //执行ktr trans.execute(params); //等待执行完毕 trans.waitUntilFinished(); if(trans.getErrors()>0) { System.err.println("Transformation run Failure!"); } else { System.out.println("Transformation run successfully!"); } }
过程中,KettleEnvironment.init();语句报错一般由于jar包没有引全,可根据错误的包名,去找到应该引入却未引入的jar包。另外,loadTransformation时遇到一个比较特别的错误:
2016/10/08 10:32:19 - ETL - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : 当读共享文件时发生错误(继续加载): org.pentaho.di.core.exception.KettleDatabaseException: 2016/10/08 10:32:19 - ETL - Error determining value metadata from SQL resultset metadata2016/10/08 10:32:19 - ETL - For input string: "4294967295"2016/10/08 10:32:19 - ETL - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 2016/10/08 10:32:19 - ETL - Error determining value metadata from SQL resultset metadata2016/10/08 10:32:19 - ETL - For input string: "4294967295"2016/10/08 10:32:19 - ETL - 2016/10/08 10:32:19 - ETL - at org.pentaho.di.core.row.value.ValueMetaBase.getValueFromSQLType(ValueMetaBase.java:4588)2016/10/08 10:32:19 - ETL - at org.pentaho.di.core.database.Database.getValueFromSQLType(Database.java:2267)2016/10/08 10:32:19 - ETL - at org.pentaho.di.core.database.Database.getRowInfo(Database.java:2229)2016/10/08 10:32:19 - ETL - at org.pentaho.di.core.database.Database.getRow(Database.java:2341)2016/10/08 10:32:19 - ETL - at org.pentaho.di.core.database.Database.getLookup(Database.java:2713)2016/10/08 10:32:19 - ETL - at org.pentaho.di.core.database.Database.getLookup(Database.java:2703)2016/10/08 10:32:19 - ETL - at org.pentaho.di.core.database.Database.getLookup(Database.java:2699)2016/10/08 10:32:19 - ETL - at org.pentaho.di.repository.kdr.delegates.KettleDatabaseRepositoryConnectionDelegate.getTransAttributeRow(KettleDatabaseRepositoryConnectionDelegate.java:619)2016/10/08 10:32:19 - ETL - at org.pentaho.di.repository.kdr.delegates.KettleDatabaseRepositoryConnectionDelegate.getTransAttributeString(KettleDatabaseRepositoryConnectionDelegate.java:761)2016/10/08 10:32:19 - ETL - at org.pentaho.di.repository.kdr.delegates.KettleDatabaseRepositoryTransDelegate.getTransAttributeString(KettleDatabaseRepositoryTransDelegate.java:1481)2016/10/08 10:32:19 - ETL - at org.pentaho.di.repository.kdr.delegates.KettleDatabaseRepositoryTransDelegate.readTransSharedObjects(KettleDatabaseRepositoryTransDelegate.java:1490)2016/10/08 10:32:19 - ETL - at org.pentaho.di.repository.kdr.delegates.KettleDatabaseRepositoryTransDelegate.loadTransformation(KettleDatabaseRepositoryTransDelegate.java:500)2016/10/08 10:32:19 - ETL - at org.pentaho.di.repository.kdr.KettleDatabaseRepository.loadTransformation(KettleDatabaseRepository.java:278)2016/10/08 10:32:19 - ETL - at com.inspur.commons.MyUtils.executeTrans(MyUtils.java:107)2016/10/08 10:32:19 - ETL - at com.inspur.commons.NewTest.main(NewTest.java:126)2016/10/08 10:32:19 - ETL - Caused by: java.lang.NumberFormatException: For input string: "4294967295"2016/10/08 10:32:19 - ETL - at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)2016/10/08 10:32:19 - ETL - at java.lang.Integer.parseInt(Integer.java:495)2016/10/08 10:32:19 - ETL - at java.lang.Integer.parseInt(Integer.java:527)2016/10/08 10:32:19 - ETL - at oracle.jdbc.driver.OracleResultSetMetaData.getPrecision(OracleResultSetMetaData.java:331)2016/10/08 10:32:19 - ETL - at org.pentaho.di.core.row.value.ValueMetaBase.getOriginalColumnMetadata(ValueMetaBase.java:4608)2016/10/08 10:32:19 - ETL - at org.pentaho.di.core.row.value.ValueMetaBase.getValueFromSQLType(ValueMetaBase.java:4561)2016/10/08 10:32:19 - ETL - ... 14 more2016/10/08 10:32:19 - ETL - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : 一个数据库错误发生在从资源库文件读取转换时2016/10/08 10:32:19 - ETL - org.pentaho.di.core.exception.KettleDatabaseException: 2016/10/08 10:32:19 - ETL - ERROR executing query2016/10/08 10:32:19 - ETL - 2016/10/08 10:32:19 - ETL - Error determining value metadata from SQL resultset metadata2016/10/08 10:32:19 - ETL - For input string: "4294967295"
这是由于oracle的jar包版本不对,原本引入的是ojdbc14.jar,去kettle目录\pdi-ce-6.1.0.1-196\data-integration\lib里找到ojdbc14-10.2.0.1.0.jar,替换掉原来的jar包就可以了。
3、远程调用
nohup ./carte.sh 127.0.0.1 8080 > ./log/carte.log &