首先说一下我开发的内容,是要把一个oracle库的某个表数据,抽到其他的oracle库和sqlservery库中,kettley就是把这些操作写到.ktr文件,java执行这个文件即可完成
import java.io.File;
import org.apache.commons.io.FileUtils;
import org.pentaho.di.core.KettleEnvironment;
import org.pentaho.di.core.database.DatabaseMeta;
import org.pentaho.di.core.exception.KettleXMLException;
import org.pentaho.di.core.plugins.PluginRegistry;
import org.pentaho.di.core.plugins.StepPluginType;
import org.pentaho.di.trans.TransHopMeta;
import org.pentaho.di.trans.TransMeta;
import org.pentaho.di.trans.step.StepMeta;
import org.pentaho.di.trans.steps.tableinput.TableInputMeta;
import org.pentaho.di.trans.steps.tableoutput.TableOutputMeta;
public class TransDemo {
public static TransDemo transDemo;
/**
* 两个库中的表名
*/
public static String bjdt_tablename = "TESTA";
public static String kettle_tablename = "TESTA";
/**
* 数据库连接信息,适用于DatabaseMeta其中 一个构造器DatabaseMeta(String xml)
*/
public static final String[] databasesXML = {
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
"<connection>" +
"<name>qdDB</name>" +
"<server>服务器地址</server>" +
"<type>MSSQL</type>" +
"<access>Native</access>" +
"<database>库名</database>" +
"<port>端口</port>" +
"<username>用户名</username>" +
"<password>密码</password>" +
"</connection>",
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
"<connection>" +
"<name>bjdt</name>" +
"<server>服务器地址</server>" +
"<type>Oracle</type>" +
"<access>Native</access>" +
"<database>orcl</database>" +
"<port>1521</port>" +
"<username>用户名</username>" +
"<password>密码</password>" +
"</connection>",
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
"<connection>" +
"<name>kettle</name>" +
"<server>服务器地址</server>" +
"<type>Oracle</type>" +
"<access>Native</access>" +
"<database>orcl</database>" +
"<port>1521</port>" +
"<username>用户名</username>" +
"<password>密码</password>" +
"</connection>"
};
/**
* @param args
*/
public static void main(String[] args) {
try {
KettleEnvironment.init();
transDemo = new TransDemo();
TransMeta transMeta = transDemo.generateMyOwnTrans();
String transXml = transMeta.getXML();
System.out.println("transXml:"+transXml);
String transName = "etl/update_insert_Trans.ktr";
File file = new File(transName);
FileUtils.writeStringToFile(file, transXml, "UTF-8");
System.out.println(databasesXML.length+"\n"+databasesXML[0]+"\n"+databasesXML[1]);
} catch (Exception e) {
e.printStackTrace();
return;
}
}
/**
* 生成一个转化,把一个数据库中的数据转移到另一个数据库中,只有两个步骤,第一个是表输入,第二个是表输出操作
* @return
* @throws KettleXMLException
*/
public TransMeta generateMyOwnTrans() throws KettleXMLException{
System.out.println("************start to generate my own transformation***********");
TransMeta transMeta = new TransMeta();
//设置转化的名称
transMeta.setName("insert_update");
//添加转换的数据库连接
for (int i=0;i<databasesXML.length;i++){
DatabaseMeta databaseMeta = new DatabaseMeta(databasesXML[i]);
transMeta.addDatabase(databaseMeta);
}
//registry是给每个步骤生成一个标识Id用
PluginRegistry registry = PluginRegistry.getInstance();
//******************************************************************
//第一个表输入步骤(TableInputMeta)
TableInputMeta tableInput = new TableInputMeta();
String tableInputPluginId = registry.getPluginId(StepPluginType.class, tableInput);
//给表输入添加一个DatabaseMeta连接数据库
DatabaseMeta database_bjdt = transMeta.findDatabase("bjdt");
tableInput.setDatabaseMeta(database_bjdt);
String select_sql = "SELECT id , name, address from "+bjdt_tablename;
tableInput.setSQL(select_sql);
//添加TableInputMeta到转换中
StepMeta tableInputMetaStep = new StepMeta(tableInputPluginId,"table input",tableInput);
//分发还是复制,这样就把源数据抽到两个目标表中,如果是true,只会把源数据一部分,抽到目标表1,一部分抽到目标表2
tableInputMetaStep.setDistributes(false);
//给步骤添加在spoon工具中的显示位置
tableInputMetaStep.setDraw(true);
tableInputMetaStep.setLocation(100, 100);
transMeta.addStep(tableInputMetaStep);
//******************************************************************
//******************************************************************
//第二步骤表插入start....
TableOutputMeta tableOutputMeta = new TableOutputMeta();
String insertUpdateMetaPluginId = registry.getPluginId(StepPluginType.class, tableOutputMeta);
//添加数据库连接
DatabaseMeta database_kettle = transMeta.findDatabase("kettle");
tableOutputMeta.setDatabaseMeta(database_kettle);
//设置操作的表
tableOutputMeta.setTableName(kettle_tablename);
tableOutputMeta.setSpecifyFields(true);
//指定源表和目标表对应关系,是按位置顺序对应的
tableOutputMeta.setFieldDatabase(new String[]{"id","name","address"});
tableOutputMeta.setFieldStream(new String[]{"id","name","address"});
//第二步骤表插入end....
//添加步骤到转换中
StepMeta tableOutputStep = new StepMeta(insertUpdateMetaPluginId,"insert_update",tableOutputMeta);
tableOutputStep.setDraw(true);
tableOutputStep.setLocation(250,100);
transMeta.addStep(tableOutputStep);
//第二个表插入start...
TableOutputMeta tableOutputMeta1 = new TableOutputMeta();
String insertUpdateMetaPluginId1 = registry.getPluginId(StepPluginType.class, tableOutputMeta1);
//添加数据库连接
DatabaseMeta database_kettle1 = transMeta.findDatabase("qdDB");
tableOutputMeta1.setDatabaseMeta(database_kettle1);
//设置操作的表
tableOutputMeta1.setTableName(kettle_tablename);
tableOutputMeta1.setSpecifyFields(true);
tableOutputMeta1.setFieldDatabase(new String[]{"id","name","address"});
tableOutputMeta1.setFieldStream(new String[]{"id","name","address"});
StepMeta tableOutputStep1 = new StepMeta(insertUpdateMetaPluginId1,"insert_update1",tableOutputMeta1);
tableOutputStep1.setDraw(true);
tableOutputStep1.setLocation(250,100);
transMeta.addStep(tableOutputStep1);
//第二个表插入end...
//******************************************************************
//******************************************************************
//添加hop把两个步骤关联起来
transMeta.addTransHop(new TransHopMeta(tableInputMetaStep, tableOutputStep));
//第二个hop
transMeta.addTransHop(new TransHopMeta(tableInputMetaStep, tableOutputStep1));
System.out.println("***********the end************");
return transMeta;
}
}
这样就生成了.ktr文件,用java调用这个ktr文件,就会把源表数据抽目标表中
所用到的jar包:https://download.csdn.net/download/star_admin/10427782