import org.pentaho.di.core.KettleEnvironment;
import org.pentaho.di.core.RowMetaAndData;
import org.pentaho.di.core.database.DatabaseMeta;
import org.pentaho.di.core.exception.KettleException;
import org.pentaho.di.core.exception.KettleStepException;
import org.pentaho.di.core.row.RowMetaInterface;
import org.pentaho.di.trans.Trans;
import org.pentaho.di.trans.TransHopMeta;
import org.pentaho.di.trans.TransMeta;
import org.pentaho.di.trans.step.RowListener;
import org.pentaho.di.trans.step.StepInterface;
import org.pentaho.di.trans.step.StepMeta;
import org.pentaho.di.trans.steps.dummytrans.DummyTransMeta;
import org.pentaho.di.trans.steps.insertupdate.InsertUpdateMeta;
import org.pentaho.di.trans.steps.tableinput.TableInputMeta;
/**
* 使用kettle从一张表读取数据,对另一张表进行插入更新操作
* @author zhanghc
*
*/
public class TableInput {
public static void main(String[] args) throws KettleException {
TableInput t = new TableInput();
t.read();
}
private static String STEP_READ_FROM_TABLE = "Read data from table";
private static String STEP_INSERT_UPDATE = "Insert or update";
private static String STEP_DUMMY = "Dummy";
public void read() throws KettleException {
KettleEnvironment.init();
TransMeta transMeta = new TransMeta();
transMeta.setName("数据同步");
//添加数据连接
DatabaseMeta dataBaseInput = new DatabaseMeta("Input", "Mysql", "Native", "127.0.0.1", "kettletask", "3306", "root", "root");
DatabaseMeta dataBaseOutput = new DatabaseMeta("Output", "Mysql", "Native", "127.0.0.1", "kettletask", "3306", "root", "root");
transMeta.addDatabase(dataBaseInput);
transMeta.addDatabase(dataBaseOutput);
//表输入
TableInputMeta tableInputMeta = new TableInputMeta();
tableInputMeta.setDatabaseMeta(transMeta.findDatabase("Input"));
tableInputMeta.setSQL("SELECT * FROM task");
StepMeta inputStep = new StepMeta(STEP_READ_FROM_TABLE,tableInputMeta);
inputStep.setLocation(50,50);
inputStep.setDraw(true);
transMeta.addStep(inputStep);
//出入更新
InsertUpdateMeta insertUpdateMeta = new InsertUpdateMeta();
insertUpdateMeta.setDatabaseMeta(transMeta.findDatabase("Output"));
insertUpdateMeta.setTableName("task_copy");//设置要更新的表
//设置用来查询的关键字
insertUpdateMeta.setKeyLookup(new String[] {"id"});
insertUpdateMeta.setKeyStream(new String[] {"id"});
insertUpdateMeta.setKeyStream2(new String[] {""});
insertUpdateMeta.setKeyCondition(new String[] {"="});
//设置更新的字段
String [] updateLookup = {"id","type","title","path","corn"};
String [] updateStream = {"id","type","title","path","corn"};
Boolean [] updateOrNot = {false,true,true,true,true};
insertUpdateMeta.setUpdateLookup(updateLookup);
insertUpdateMeta.setUpdateStream(updateStream);
insertUpdateMeta.setUpdate(updateOrNot);
StepMeta insertUpdateStep = new StepMeta(STEP_INSERT_UPDATE,insertUpdateMeta);
insertUpdateStep.setLocation(150, 50);
insertUpdateStep.setDraw(true);
transMeta.addStep(insertUpdateStep);
//空步骤
DummyTransMeta dummyMeta = new DummyTransMeta();
StepMeta dummyStep = new StepMeta(STEP_DUMMY,dummyMeta);
dummyStep.setLocation(200,50);
dummyStep.setDraw(true);
transMeta.addStep(dummyStep);
//设置步骤直接的关系
TransHopMeta hop = new TransHopMeta(inputStep, insertUpdateStep);
transMeta.addTransHop(hop);
TransHopMeta hop2 = new TransHopMeta(insertUpdateStep, dummyStep);
transMeta.addTransHop(hop2);
//开始执行
Trans trans = new Trans(transMeta);
trans.prepareExecution(null);
trans.startThreads();
trans.waitUntilFinished();
if(trans.getErrors() > 0) {
System.out.println(">>>>>>>>>> ERROR");
}else {
System.out.println(">>>>>>>>>> SUCCESS ");
}
}
}
import java.io.DataOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import org.pentaho.di.core.Const;
import org.pentaho.di.core.KettleEnvironment;
import org.pentaho.di.core.RowMetaAndData;
import org.pentaho.di.core.database.DatabaseMeta;
import org.pentaho.di.core.exception.KettleException;
import org.pentaho.di.core.exception.KettleStepException;
import org.pentaho.di.core.row.RowMetaInterface;
import org.pentaho.di.core.vfs.KettleVFS;
import org.pentaho.di.core.xml.XMLHandler;
import org.pentaho.di.trans.Trans;
import org.pentaho.di.trans.TransHopMeta;
import org.pentaho.di.trans.TransMeta;
import org.pentaho.di.trans.step.RowListener;
import org.pentaho.di.trans.step.StepInterface;
import org.pentaho.di.trans.step.StepMeta;
import org.pentaho.di.trans.steps.dummytrans.DummyTransMeta;
import org.pentaho.di.trans.steps.insertupdate.InsertUpdateMeta;
import org.pentaho.di.trans.steps.tableinput.TableInputMeta;
/**
* 使用kettle从一张表读取数据,对另一张表进行插入更新操作
* 步骤之间有参数传递
* @author zhanghc
*
*/
public class TableInput2 {
public static void main(String[] args) throws KettleException {
TableInput2 t = new TableInput2();
t.read();
}
private static String STEP_READ_PARAM = "Read param";
private static String STEP_READ_FROM_TABLE = "Read data from table";
private static String STEP_INSERT_UPDATE = "Insert or update";
private static String STEP_DUMMY = "Dummy";
public void read() throws KettleException {
KettleEnvironment.init();
TransMeta transMeta = new TransMeta();
transMeta.setName("数据同步2");
//添加数据连接
DatabaseMeta dataBaseInput = new DatabaseMeta("Input", "Mysql", "Native", "127.0.0.1", "kettletask", "3306", "root", "root");
DatabaseMeta dataBaseOutput = new DatabaseMeta("Output", "Mysql", "Native", "127.0.0.1", "kettletask", "3306", "root", "root");
transMeta.addDatabase(dataBaseInput);
transMeta.addDatabase(dataBaseOutput);
//条件查询
TableInputMeta paramMeta = new TableInputMeta();
paramMeta.setDatabaseMeta(transMeta.findDatabase("Output"));
paramMeta.setSQL("SELECT MAX(id) FROM task");
StepMeta paramtStep = new StepMeta(STEP_READ_PARAM,paramMeta);
paramtStep.setLocation(0,50);
paramtStep.setDraw(true);
transMeta.addStep(paramtStep);
//表输入
TableInputMeta tableInputMeta = new TableInputMeta();
tableInputMeta.setDatabaseMeta(transMeta.findDatabase("Input"));
tableInputMeta.setSQL("SELECT * FROM task WHERE id > ?");
tableInputMeta.setVariableReplacementActive(true);//替换sql中的变量
tableInputMeta.setLookupFromStep(paramtStep);//从此步骤中读取参数值
StepMeta inputStep = new StepMeta(STEP_READ_FROM_TABLE,tableInputMeta);
inputStep.setLocation(50,50);
inputStep.setDraw(true);
transMeta.addStep(inputStep);
//出入更新
InsertUpdateMeta insertUpdateMeta = new InsertUpdateMeta();
insertUpdateMeta.setDatabaseMeta(transMeta.findDatabase("Output"));
insertUpdateMeta.setTableName("task_copy");//设置要更新的表
//设置用来查询的关键字
insertUpdateMeta.setKeyLookup(new String[] {"id"});
insertUpdateMeta.setKeyStream(new String[] {"id"});
insertUpdateMeta.setKeyStream2(new String[] {""});
insertUpdateMeta.setKeyCondition(new String[] {"="});
//设置更新的字段
String [] updateLookup = {"id","type","title","path","corn"};
String [] updateStream = {"id","type","title","path","corn"};
Boolean [] updateOrNot = {false,true,true,true,true};
insertUpdateMeta.setUpdateLookup(updateLookup);
insertUpdateMeta.setUpdateStream(updateStream);
insertUpdateMeta.setUpdate(updateOrNot);
StepMeta insertUpdateStep = new StepMeta(STEP_INSERT_UPDATE,insertUpdateMeta);
insertUpdateStep.setLocation(150, 50);
insertUpdateStep.setDraw(true);
transMeta.addStep(insertUpdateStep);
//空步骤
DummyTransMeta dummyMeta = new DummyTransMeta();
StepMeta dummyStep = new StepMeta(STEP_DUMMY,dummyMeta);
dummyStep.setLocation(200,50);
dummyStep.setDraw(true);
transMeta.addStep(dummyStep);
//设置步骤直接的关系
TransHopMeta hop2 = new TransHopMeta(paramtStep, inputStep);
transMeta.addTransHop(hop2);
TransHopMeta hop = new TransHopMeta(inputStep, insertUpdateStep);
transMeta.addTransHop(hop);
String xml = XMLHandler.getXMLHeader()+transMeta.getXML();
DataOutputStream dos = new DataOutputStream(KettleVFS.getOutputStream("F:/csv.ktr", false));
try {
dos.write(xml.getBytes(Const.XML_ENCODING));
dos.close();
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//开始执行
Trans trans = new Trans(transMeta);
trans.prepareExecution(null);
trans.startThreads();
trans.waitUntilFinished();
if(trans.getErrors() > 0) {
System.out.println(">>>>>>>>>> ERROR");
}else {
System.out.println(">>>>>>>>>> SUCCESS ");
}
}
}