利用数据库保存源表中查询sql和目的表关系描述以及抽取字段配置进行定时执行抽取。
1、触发定时任务
2、读取数据库中数据库连接配置和数据库抽取关系配置
3、利用配置生成ktr任务文件
4、执行ktr文件
提供思路数据库结构:
定时任务类
package ins.platform.extractdata.util;
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.io.FileUtils;
import org.pentaho.di.core.KettleEnvironment;
import org.pentaho.di.trans.TransMeta;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import com.sun.istack.logging.Logger;
import ins.platform.extractdata.dao.ReserveDatasourceconfigDao;
import ins.platform.extractdata.dao.ReserveExtractConfigDao;
import ins.platform.extractdata.vo.ReserveDatasourceconfigVo;
import ins.platform.extractdata.vo.ReserveExtractConfigVo;
import ins.platform.extractdata.vo.SourceConfig;
/**
* 定时任务
* @author LIKUN
*
*/
@Component
public class TimingTask {
private static Logger logger = Logger.getLogger(TimingTask.class);
@Autowired
private ReserveDatasourceconfigDao reserveDatasourceconfigDao;
@Autowired
private ReserveExtractConfigDao reserveExtractConfigDao;
@Autowired
private ExtractUtil extractUtil;
/**
* 定时任务,每天晚上21:00点抽取数据
*/
@Scheduled(cron= "0 0 21 * * ?")
@RequestMapping(value = "/testTimetaisk",method=RequestMethod.GET)
public void calculatePremiuming(){
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String date = sf.format(new Date());
logger.info("数据组装定时任务配置开始!时间:"+date);
//组装数据库源等配置文件
List<SourceConfig> sourceConfigs = init();
//进行抽取
try {
logger.info("数据抽取定时任务开始!时间:"+date);
startPumingData(sourceConfigs);
} catch (Exception e) {
// TODO: handle exception
logger.info("抽取数据定时任务异常!时间:"+new Date());
e.getMessage();
}
logger.info("抽取数据定时任务完成!时间:"+new Date());
}
/**
* 1、查询数据库连接信息
* 2、我们的准备金系统数据库连接
* 3、组装查询sql
* 4、组装插入导入记录表数据字段配置数据记录(插入的记录表、字段、重规则)
* 5、组装插入业务表数据字段配置(插入的业务表、字段、重复规则)
* 6、返回组装好的配置
*/
public List<SourceConfig> init(){
List<SourceConfig> sourceConfigs = new ArrayList<>();
//查询出所有数据抽取配置
List<ReserveExtractConfigVo>reserveExtractConfigVos = reserveExtractConfigDao.findAllByStaticIsTrue();
//查询出数据库连接信息
ReserveDatasourceconfigVo reserveDatasourceconfigVo = reserveDatasourceconfigDao.findByName("admin");
//我们的数据库连接信息
ReserveDatasourceconfigVo myDatabaseConfig = extractUtil.getOwn();
for (ReserveExtractConfigVo reserveExtractConfigVo : reserveExtractConfigVos) {
//组装所有抽取配置
SourceConfig sourceConfig = ExtractUtil.copyConfig(reserveExtractConfigVo);
//设置数据库连接配置
sourceConfig.setDatabaseConfig(reserveDatasourceconfigVo);
//组装我们的数据库连接
sourceConfig.setMyDatabaseConfig(myDatabaseConfig);
sourceConfigs.add(sourceConfig);
}
return sourceConfigs;
}
//预留抽取前后可操作方法
public void startPumingData(List<SourceConfig> sourceConfigs){
generateFileAndImplementList(sourceConfigs);
}
/**
* 利用配置异步执行抽取
* @param sourceConfigs
*/
public void generateFileAndImplementList(List<SourceConfig> sourceConfigs){
//循环调用
for (SourceConfig sourceConfig : sourceConfigs) {
logger.info("-------------------------------------------------------------------------------------------");
logger.info("--------------------------------数据抽取开始!(start)---------------------------------");
logger.info("--------------------------------目标数据库地址:"+sourceConfig.getDatabaseConfig().getAddress()+"--------------------------------");
logger.info("--------------------------------目标数据库类型:"+sourceConfig.getDatabaseConfig().getDataBaseType()+"--------------------------------");
logger.info("--------------------------------目标数据库名称:"+sourceConfig.getDatabaseConfig().getDataBaseName()+"--------------------------------");
logger.info("--------------------------------目标数据库端口:"+sourceConfig.getDatabaseConfig().getDsPort()+"--------------------------------");
logger.info("--------------------------------目标数据库用户:"+sourceConfig.getDatabaseConfig().getUsername()+"--------------------------------");
logger.info("--------------------------------目标数据库密码:"+sourceConfig.getDatabaseConfig().getUserPass()+"--------------------------------");
generateFileAndImplement(sourceConfig);
logger.info("---------------------数据抽取结束!(end)---------------------");
logger.info("-------------------------------------------------------------------------------------------");
}
};
/**
* 单次生成抽取
* @param extractConfig
*/
@Async
public void generateFileAndImplement(SourceConfig sourceConfig){
logger.info("-------------------------------------------------------------------------------------------");
logger.info("--------------------------------生成抽取ktr文件开始!(start)--------------------------------");
logger.info("--------------------------------文件路径:"+sourceConfig.getKtrUrl()+"--------------------------------");
logger.info("--------------------------------文件名称:"+sourceConfig.getKtrName()+"--------------------------------");
logger.info("--------------------------------数据查询SQL:"+sourceConfig.getExtractSql()+"--------------------------------");
logger.info("--------------------------------目标表名:"+sourceConfig.getTableName()+"--------------------------------");
//生成
String fileName = generateFile(sourceConfig);
logger.info("--------------------------------生成抽取ktr文件结束!(end)--------------------------------");
logger.info("-------------------------------------------------------------------------------------------");
logger.info("-------------------------------------------------------------------------------------------");
logger.info("--------------------------------执行抽取ktr文件开始!(start)--------------------------------");
logger.info("--------------------------------执行抽取ktr文件路径:"+fileName+"--------------------------------");
//抽取
Implement(fileName);
logger.info("--------------------------------执行抽取ktr文件结束!(end)--------------------------------");
logger.info("-------------------------------------------------------------------------------------------");
}
/**
* 生成抽取文件
*/
public String generateFile(SourceConfig extractConfig){
String fileName = extractConfig.getKtrUrl()+extractConfig.getKtrName();
try {
KettleEnvironment.init();
EncapsulationKtr transDemo = new EncapsulationKtr();
//组装sql和数据库连接
TransMeta transMeta = transDemo.generateMyOwnTrans(extractConfig);
String transXml = transMeta.getXML();
//System.out.println("transXml:"+transXml);
File file = new File(fileName);
FileUtils.writeStringToFile(file, transXml, "UTF-8");
} catch (Exception e) {
// TODO Auto-generated catch block
logger.info("---------------------------------Kettle生成文件异常----------------------------------------");
e.printStackTrace();
}
return fileName;
}
/**
* 抽取
* @param fileName
*/
public void Implement(String fileName){
KettleExecu.runTrans(fileName);
}
}
获取数据库连接信息工具类
package ins.platform.extractdata.util;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import ins.framework.utils.Beans;
import ins.platform.extractdata.vo.ReserveDatasourceconfigVo;
import ins.platform.extractdata.vo.ReserveExtractConfigVo;
import ins.platform.extractdata.vo.SourceConfig;
import ins.platform.util.QuartzConfig;
/**
* 获取数据库连接信息工具类
* @author LIKUN
* @time 2019-02-27
*/
@Component
public class ExtractUtil {
@Autowired
private QuartzConfig quartzConfig;
/**
* 获取当前数据库连接信息
* @return
*/
public ReserveDatasourceconfigVo getOwn(){
String myDataSourceUrl = quartzConfig.getDataSourceUrl();
myDataSourceUrl = myDataSourceUrl.replace("?",":");
myDataSourceUrl = myDataSourceUrl.replace("/",":");
String[] urlStrs=myDataSourceUrl.split(":");
for(int i=0,len=urlStrs.length;i<len;i++){
System.out.println(urlStrs[i].toString());
}
ReserveDatasourceconfigVo databaseConfig = new ReserveDatasourceconfigVo();
databaseConfig.setDataBaseType(urlStrs[1]);
databaseConfig.setAddress(urlStrs[4]);
databaseConfig.setDsPort(urlStrs[5]);
databaseConfig.setDataBaseName(urlStrs[6]);
databaseConfig.setUsername(quartzConfig.getDataSourceUser());
databaseConfig.setUserPass(quartzConfig.getDataSourcePassword());
return databaseConfig;
}
/**
* 组装配置数据抽取关系工具
* @param reserveExtractConfigVo
* @return
*/
public static SourceConfig copyConfig(ReserveExtractConfigVo reserveExtractConfigVo){
SourceConfig sourceConfig = new SourceConfig();
Beans.copy().from(reserveExtractConfigVo).to(sourceConfig);
String[] updatelookup = reserveExtractConfigVo.getUpdatelookup().replace(" ", "").split(",");
String [] updateStream = reserveExtractConfigVo.getUpdateStream().replace(" ", "").split(",");
String [] keyCondition = reserveExtractConfigVo.getKeyCondition().replace(" ", "").split(",");
String[] updateOrNotString = reserveExtractConfigVo.getUpdateOrNot().replace(" ", "").split(",");
Boolean[] updateOrNot = new Boolean[updateOrNotString.length];
//统计条件的数量
int count = 0;
for (String condition : keyCondition) {
if(!"NOHAVE".equals(condition))
count++;
}
String [] keyConditionOk = new String[count];
String [] updatelookupCondition = new String[count];
String [] updateStreamCondition = new String[count];
count = 0;
for (int i = 0; i< updateOrNotString.length; i++) {
updateOrNot[i] = Boolean.parseBoolean(updateOrNotString[i]);
if(!"NOHAVE".equals(keyCondition[i])){
keyConditionOk[count] = keyCondition[i];
updatelookupCondition[count] = updatelookup[i];
updateStreamCondition[count] = updateStream[i];
count++;
}
}
sourceConfig.setUpdatelookup(updatelookup);
sourceConfig.setUpdateStream(updateStream);
sourceConfig.setUpdateOrNot(updateOrNot);
sourceConfig.setKeyCondition(keyConditionOk);
sourceConfig.setUpdatelookupCondition(updatelookupCondition);
sourceConfig.setUpdateStreamCondition(updateStreamCondition);
return sourceConfig;
}
}
抽取配置表
package ins.platform.extractdata.vo;
import java.io.Serializable;
import java.util.Date;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
*
* ReserveExtractConfigVo对象.对应实体描述:抽取配置表
*
*/
@Data
@ApiModel("ReserveExtractConfigVo对象")
public class ReserveExtractConfigVo implements Serializable {
private static final long serialVersionUID = 1L;
/** 对应字段:id */
@ApiModelProperty()
private Integer id;
/** 对应字段:exName,备注:名称 */
@ApiModelProperty("名称")
private String exName;
/** 对应字段:extractType,备注:类型(预留) */
@ApiModelProperty("类型(预留)")
private String extractType;
/** 对应字段:tableName,备注:需要操作的表名 */
@ApiModelProperty("需要操作的表名")
private String tableName;
/** 对应字段:extractSql,备注:查询sql */
@ApiModelProperty("查询sql")
private String extractSql;
/** 对应字段:updatelookup,备注:查询sql结果字段(格式:abc,abc,abc...) */
@ApiModelProperty("查询sql结果字段(格式:abc,abc,abc...)")
private String updatelookup;
/** 对应字段:updateStream,备注:插入表字段(格式:abc,abc,abc...) */
@ApiModelProperty("插入表字段(格式:abc,abc,abc...)")
private String updateStream;
/** 对应字段:keyCondition,备注:关系符号:=、= ~NULL、<>、<、<=、>、>=、LIKE、BETWEEN、IS NULL、IS NOT NULL */
@ApiModelProperty("关系符号:=、= ~NULL、<>、<、<=、>、>=、LIKE、BETWEEN、IS NULL、IS NOT NULL")
private String keyCondition;
/** 对应字段:updateOrNot,备注:是否更新:true/false */
@ApiModelProperty("是否更新:true/false")
private String updateOrNot;
/** 对应字段:ktrUrl,备注:存放地址 */
@ApiModelProperty("存放地址")
private String ktrUrl;
/** 对应字段:ktrName,备注:文件名称 */
@ApiModelProperty("文件名称")
private String ktrName;
/** 对应字段:validStatus,备注:状态:1有效,0无效 */
@ApiModelProperty("状态:1有效,0无效")
private String validStatus;
/** 对应字段:createTime,备注:创建时间 */
@ApiModelProperty("创建时间")
private Date createTime;
/** 对应字段:createUserCode,备注:创建用户 */
@ApiModelProperty("创建用户")
private String createUserCode;
/** 对应字段:updateTime,备注:修改时间 */
@ApiModelProperty("修改时间")
private Date updateTime;
/** 对应字段:updateUserCode,备注:更改时间 */
@ApiModelProperty("更改时间")
private String updateUserCode;
}
数据库连接信息
package ins.platform.extractdata.vo;
import java.io.Serializable;
import java.util.Date;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
*
* ReserveDatasourceconfigVo对象.
*
*/
@Data
@ApiModel("ReserveDatasourceconfigVo对象")
public class ReserveDatasourceconfigVo implements Serializable {
private static final long serialVersionUID = 1L;
/** 对应字段:id,备注:数据源配置主键ID */
@ApiModelProperty("数据源配置主键ID")
private Long id;
/** 对应字段:dsName,备注:数据源名称 */
@ApiModelProperty("数据源名称")
private String dsName;
/** 对应字段:dataBaseType,备注:数据源类型 */
@ApiModelProperty("数据源类型")
private String dataBaseType;
/** 对应字段:address,备注:数据源地址 */
@ApiModelProperty("数据源地址")
private String address;
/** 对应字段:userName,备注:数据源登录用户名 */
@ApiModelProperty("数据源登录用户名")
private String username;
/** 对应字段:userPass,备注:数据源登录密码 */
@ApiModelProperty("数据源登录密码")
private String userPass;
/** 对应字段:dsPort,备注:数据库连接端口 */
@ApiModelProperty("数据库连接端口")
private String dsPort;
/** 对应字段:dataBaseName,备注:数据库库名 */
@ApiModelProperty("数据库库名")
private String dataBaseName;
/** 对应字段:validStatus,备注:有效状态,1:有效,0:无效 */
@ApiModelProperty("有效状态,1:有效,0:无效")
private String validStatus;
/** 对应字段:updateTime,备注:更新时间 */
@ApiModelProperty("更新时间")
private Date updateTime;
/** 对应字段:updateUserCode,备注:更新用户code */
@ApiModelProperty("更新用户code")
private String updateUserCode;
/** 对应字段:CreateTime,备注:创建时间 */
@ApiModelProperty("创建时间")
private Date createTime;
/** 对应字段:createUserCode,备注:创建人 */
@ApiModelProperty("创建人")
private String createUserCode;
}
数据抽取配置文件
package ins.platform.extractdata.vo;
import lombok.Data;
/**
* 数据抽取配置文件
* @author LIKUN
* @time 2019-02-27
*
*/
@Data
public class SourceConfig {
/**数据库连接信息*/
private ReserveDatasourceconfigVo databaseConfig;
/**我们的准备金系统数据库连接*/
private ReserveDatasourceconfigVo myDatabaseConfig;
/**要操作的表名*/
private String tableName;
/**可直接拼写查询sql*/
private String extractSql;
/**提数表中的数据字段和我们的相匹配的字段*/
private String [] updatelookup;
/**提数表中的数据字段和我们的相匹配的字段*/
private String [] updateStream;
/**查询更新表中的字段*/
private String [] updatelookupCondition;
/**查询更新表中的字段*/
private String [] updateStreamCondition;
/**关系符号:=、= ~NULL、<>、<、<=、>、>=、LIKE、BETWEEN、IS NULL、IS NOT NULL*/
private String [] keyCondition;
/**是否更新:true/false*/
private Boolean[] updateOrNot;
/**ktr文件存放地址*/
private String ktrUrl;
/**ktr文件名称*/
private String ktrName;
}
生成ktr文件封装类
package ins.platform.extractdata.util;
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.insertupdate.InsertUpdateMeta;
import org.pentaho.di.trans.steps.tableinput.TableInputMeta;
import ins.platform.extractdata.vo.SourceConfig;
/**
* date time : 2019-02-27 author : likun function : 生成ktr文件封装类
*/
public class EncapsulationKtr {
/**
* 生成一个转化,把一个数据库中的数据转移到另一个数据库中,只有两个步骤,第一个是表输入,第二个是表插入与更新操作
*
* @return
* @throws KettleXMLException
*/
public TransMeta generateMyOwnTrans(SourceConfig sourceConfig) throws KettleXMLException {
/**
* 数据库连接信息,适用于DatabaseMeta其中 一个构造器DatabaseMeta(String xml)
*/
final String[] databasesXML = {
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" + "<connection>" + "<name>bjdt</name>" + "<server>"
+ sourceConfig.getDatabaseConfig().getAddress() + "</server>" + "<type>"
+ sourceConfig.getDatabaseConfig().getDataBaseType() + "</type>" + "<access>Native</access>"
+ "<database>" + sourceConfig.getDatabaseConfig().getDataBaseName() + "</database>" + "<port>"
+ sourceConfig.getDatabaseConfig().getDsPort() + "</port>" + "<username>"
+ sourceConfig.getDatabaseConfig().getUsername() + "</username>" + "<password>"
+ sourceConfig.getDatabaseConfig().getUserPass() + "</password>" + "</connection>",
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" + "<connection>" + "<name>kettle</name>" + "<server>"
+ sourceConfig.getMyDatabaseConfig().getAddress() + "</server>" + "<type>"
+ sourceConfig.getMyDatabaseConfig().getDataBaseType() + "</type>" + "<access>Native</access>"
+ "<database>" + sourceConfig.getMyDatabaseConfig().getDataBaseName() + "</database>" + "<port>"
+ sourceConfig.getMyDatabaseConfig().getDsPort() + "</port>" + "<username>"
+ sourceConfig.getMyDatabaseConfig().getUsername() + "</username>" + "<password>"
+ sourceConfig.getMyDatabaseConfig().getUserPass() + "</password>" + "</connection>" };
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);
tableInput.setSQL(sourceConfig.getExtractSql());
// 添加TableInputMeta到转换中
StepMeta tableInputMetaStep = new StepMeta("table input", tableInput);
// 给步骤添加在spoon工具中的显示位置
tableInputMetaStep.setDraw(true);
tableInputMetaStep.setLocation(100, 100);
transMeta.addStep(tableInputMetaStep);
// ******************************************************************
// ******************************************************************
// 第二个步骤插入与更新
InsertUpdateMeta insertUpdateMeta = new InsertUpdateMeta();
//String insertUpdateMetaPluginId =
registry.getPluginId(StepPluginType.class, insertUpdateMeta);
// 添加数据库连接
DatabaseMeta database_kettle = transMeta.findDatabase("kettle");
insertUpdateMeta.setDatabaseMeta(database_kettle);
// 设置操作更新的表
insertUpdateMeta.setTableName(sourceConfig.getTableName());
insertUpdateMeta.setUpdateLookup(sourceConfig.getUpdatelookup());
insertUpdateMeta.setUpdateStream(sourceConfig.getUpdateStream());
insertUpdateMeta.setUpdate(sourceConfig.getUpdateOrNot());
// 设置用来查询的关键字
insertUpdateMeta.setKeyLookup(sourceConfig.getUpdatelookupCondition());
insertUpdateMeta.setKeyStream(sourceConfig.getUpdateStreamCondition());
insertUpdateMeta.setKeyStream2(new String[sourceConfig.getKeyCondition().length]);// 一定要加上
insertUpdateMeta.setKeyCondition(sourceConfig.getKeyCondition());
// String[] lookup = insertUpdateMeta.getUpdateLookup();
// System.out.println("******:"+lookup[1]);
// System.out.println("insertUpdateMetaXMl:"+insertUpdateMeta.getXML());
// 添加步骤到转换中
StepMeta insertUpdateStep = new StepMeta("insert_update", insertUpdateMeta);
insertUpdateStep.setDraw(true);
insertUpdateStep.setLocation(250, 100);
transMeta.addStep(insertUpdateStep);
// ******************************************************************
// ******************************************************************
// 添加hop把两个步骤关联起来
transMeta.addTransHop(new TransHopMeta(tableInputMetaStep, insertUpdateStep));
System.out.println("***********the end************");
return transMeta;
}
}
调用Kettle执行文件工具类
package ins.platform.extractdata.util;
import org.pentaho.di.core.KettleEnvironment;
import org.pentaho.di.core.exception.KettleException;
import org.pentaho.di.core.exception.KettleXMLException;
import org.pentaho.di.job.Job;
import org.pentaho.di.job.JobMeta;
import org.pentaho.di.trans.Trans;
import org.pentaho.di.trans.TransMeta;
/**
* 调用Kettle执行文件工具类
* @author LIKUN
* @time 2019-02-28
*/
public class KettleExecu {
/**
* 执行kjb文件
* @param jobNameUrl
*/
public static void runJob(String jobNameUrl) {
try {
KettleEnvironment.init();
// jobNameUrl 是Job脚本的路径及名称
JobMeta jobMeta = new JobMeta(jobNameUrl, null);
Job job = new Job(null, jobMeta);
// 向Job 脚本传递参数,脚本中获取参数值:${参数名}
// job.setVariable(paraname, paravalue);
job.start();
job.waitUntilFinished();
if (job.getErrors() > 0) {
System.out.println("decompress fail!");
}
} catch (KettleException e) {
System.out.println(e);
}
}
/**
* 执行ktr文件
* @param filename
*/
public static void runTrans(String fileName) {
try {
KettleEnvironment.init();
TransMeta transMeta = new TransMeta(fileName);
Trans trans = new Trans(transMeta);
trans.prepareExecution(null);
trans.startThreads();
trans.waitUntilFinished();
if (trans.getErrors() != 0) {
System.out.println("Error");
}
} catch (KettleXMLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (KettleException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}