背景:现需要定时执行数据抽取功能,kettle工具是可以直接新建job的,不过这样比较麻烦,时间都浪费在新建kettle上,也不便于监控和维护。其实不同的ktr文件模板基本都是一样的,只是里面的数据不一样而已,这时就可以只新建一个kettle转换的ktr文件,java后台新建任务,这个任务包括频率、参数,由job调度根据每个task设置的频率去执行,读取这个ktr文件,把ktr文件里面的参数用当前任务的参数替换掉。
举例:
一、txt抽取到mysql:
模板:
其中:textFileInput:
除了一些公共的东西,剩下数据全部由代码设置:
public static KettleResult runTxtToMysqlTemplateKtr(Map<String, String> param) throws KettleException, InvalidKeyException, UnsupportedEncodingException {
//模板文件
TransMeta transMeta = new TransMeta(DirectoryUtil.txtToMysqlTemplatePath);
// 步骤:获取文件名
StepMeta findStep = transMeta.findStep("getFileName");
GetFileNamesMeta fileNamesMeta = (GetFileNamesMeta) findStep.getStepMetaInterface();
String fileName = param.get("sourcePath");
if (StringUtils.isNotEmpty(fileName)) {
if (fileName.contains("${PART}")) {
fileName = fileName.replace("${PART}", new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
}
fileNamesMeta.setFileName(new String[]{fileName});
}
// 步骤:文本文件输入
StepMeta textStep = transMeta.findStep("textFileInput");
TextFileInputMeta fileInputMeta = (TextFileInputMeta) textStep.getStepMetaInterface();
List<FieldMeta> fieldMetas = JSONUtils.parseList(param.get("mapping"), FieldMeta.class);
// 字段
BaseFileField[] baseFileInputFields = new BaseFileField[fieldMetas.size()];
// 映射
String[] inputFields = new String[fieldMetas.size()];
if (!CollectionUtils.isEmpty(fieldMetas)) {
for (int i = 0; i < fieldMetas.size(); i++) {
BaseFileField bf = new BaseFileField();
bf.setName(fieldMetas.get(i).getName());
bf.setType(fieldMetas.get(i).getDataType());
baseFileInputFields[i] = bf;
inputFields[i] = fieldMetas.get(i).getName();
}
}
fileInputMeta.inputFields = baseFileInputFields;
//fileInputMeta.inputFiles.includeSubFolders = includeSubFolderBoolean;
fileInputMeta.content.separator = param.get("division");
// 步骤:表输出
StepMeta tableStep = transMeta.findStep("tableOutput");
TableOutputMeta tableOutputMeta = (TableOutputMeta) tableStep.getStepMetaInterface();
String host = param.get("host");
String port = param.get("port");
String db = param.get("dataBase");
String table = param.get("tableName");
String user = param.get("user");
String password = PwdUtil.decrypt(param.get("password"));
MySQLDatabaseMeta mySQLDatabaseMeta = (MySQLDatabaseMeta) tableOutputMeta.getDatabaseMeta().getDatabaseInterface();
if (StringUtils.isNotEmpty(host)) {
mySQLDatabaseMeta.setHostname(host);
}
if (StringUtils.isNotEmpty(port)) {
mySQLDatabaseMeta.setDatabasePortNumberString(port);
}
if (StringUtils.isNotEmpty(db)) {
mySQLDatabaseMeta.setDatabaseName(db);
}
if (StringUtils.isNotEmpty(user)) {
mySQLDatabaseMeta.setUsername(user);
}
if (StringUtils.isNotEmpty(password)) {
mySQLDatabaseMeta.setPassword(password);
}
if (StringUtils.isNotEmpty(table)) {
tableOutputMeta.setTableName(table);
}
tableOutputMeta.setFieldStream(inputFields);
tableOutputMeta.setFieldDatabase(inputFields);
//是否全量,全量则删除表
if(Boolean.valueOf(param.get("isTruncateTable"))){
tableOutputMeta.setTruncateTable(true);
}
// 转换
Trans trans = new Trans(transMeta);
trans.execute(null);
// 等待转换执行结束
trans.waitUntilFinished();
// 抛出异常
if (trans.getErrors() > 0) {
throw new KettleException("There are errors during transformation exception!(传输过程中发生异常)");
}
Result result = trans.getResult();
KettleResult kettleResult = transResult(result);
return kettleResult;
}
二、mysql抽取到txt:
public static KettleResult runMysqlToTxtTemplateKtr(Map<String, String> param) throws KettleException, InvalidKeyException, UnsupportedEncodingException {
//读取模板文件
TransMeta transMeta = new TransMeta(DirectoryUtil.mysqlToTxtTemplatePath);
//1、表输入
StepMeta tableStep = transMeta.findStep("tableInput");
TableInputMeta tableInputMeta = (TableInputMeta) tableStep.getStepMetaInterface();
String host = param.get("host");
String port = param.get("port");
String db = param.get("dataBase");
String table = param.get("tableName");
String user = param.get("user");
String columnName = param.get("columnName");
String password = PwdUtil.decrypt(param.get("password"));
DatabaseMeta mySQLDatabaseMeta = new DatabaseMeta();
mySQLDatabaseMeta.setDatabaseType("mySQL");
String columns = "*";
if (StringUtils.isNotEmpty(host)) {
mySQLDatabaseMeta.setHostname(host);
}
if (StringUtils.isNotEmpty(port)) {
mySQLDatabaseMeta.setDBPort(port);
}
if (StringUtils.isNotEmpty(db)) {
mySQLDatabaseMeta.setDBName(db);
}
if (StringUtils.isNotEmpty(user)) {
mySQLDatabaseMeta.setUsername(user);
}
if (StringUtils.isNotEmpty(password)) {
mySQLDatabaseMeta.setPassword(password);
}
if (StringUtils.isNotEmpty(table)) {
tableInputMeta.setDatabaseMeta(mySQLDatabaseMeta);
}
if(StringUtils.isNotEmpty(columnName)){
columns = columnName;
}
tableInputMeta.setSQL("select "+columns+" from "+table);
//2、文本文件输出
StepMeta textStep = transMeta.findStep("textFileOutput");
//文本文件
String mysqlToTxtTemplatePath = param.get("outPutFileName");
TextFileOutputMeta textFileOutputMeta = (TextFileOutputMeta) textStep.getStepMetaInterface();
textFileOutputMeta.setFileName(mysqlToTxtTemplatePath);
//是否追加
if(Boolean.valueOf(param.get("appendFile"))){
textFileOutputMeta.setFileAppended(true);
}
//3、转换
Trans trans = new Trans(transMeta);
trans.execute(null);
// 等待转换执行结束
trans.waitUntilFinished();
// 抛出异常
if (trans.getErrors() > 0) {
throw new KettleException("There are errors during transformation exception!(传输过程中发生异常)");
}
Result result = trans.getResult();
KettleResult kettleResult = transResult(result);
return kettleResult;
}
三、抽取mongo到txt:
public static KettleResult runMongoTotxt(Map<String, String> param) throws KettleException, NoSuchMethodException, InvocationTargetException, IllegalAccessException, ClassNotFoundException, InstantiationException, UnsupportedEncodingException, InvalidKeyException {
String mongo = DirectoryUtil.mongoToTxtTemplatePath;
//读取模板文件
TransMeta transMeta = new TransMeta(mongo);
//获取MongoDBInput
StepMeta mongoStep = transMeta.findStep("MongoDBInput");
MongoDbInputMeta mongoDbInputMeta = new MongoDbInputMeta();
String host = param.get("host");
String port = param.get("port");
String db = param.get("dataBase");
String collection = param.get("collection");
String user = param.get("user");
String password = PwdUtil.decrypt(param.get("password"));
String mongoDb = param.get("mongoDb");
mongoDbInputMeta.setHostnames(host);
mongoDbInputMeta.setPort(port);
if(StringUtils.isNotEmpty(db) && StringUtils.isNotEmpty(user) && StringUtils.isNotEmpty(password)){
mongoDbInputMeta.setAuthenticationMechanism("MONGODB-CR");
mongoDbInputMeta.setAuthenticationDatabaseName(db);
mongoDbInputMeta.setAuthenticationUser(user);
mongoDbInputMeta.setAuthenticationPassword(password);
}
mongoDbInputMeta.setDbName(mongoDb);
mongoDbInputMeta.setCollection(collection);
List<MongoField> mongoFields = new ArrayList<>();
List<MongoFieldMeta> fieldMetas = JSONUtils.parseList(param.get("mongoMapping"), MongoFieldMeta.class);
for(MongoFieldMeta mongoFieldMeta :fieldMetas ){
MongoField mongoField = new MongoField();
mongoField.m_fieldName = mongoFieldMeta.getName();
mongoField.m_fieldPath = mongoFieldMeta.getPath();
mongoField.m_kettleType = mongoFieldMeta.getType();
mongoFields.add(mongoField);
}
mongoDbInputMeta.setMongoFields(mongoFields);
mongoDbInputMeta.setOutputJson(false);
//mongoDbInputMeta.setJsonFieldName("json");
mongoStep.setStepMetaInterface(mongoDbInputMeta);
//获取fileOutPut
StepMeta txtStep = transMeta.findStep("fileOutPut");
TextFileOutputMeta textFileOutputMeta = (TextFileOutputMeta) txtStep.getStepMetaInterface();
//不加后缀
String mongoToTxtTemplatePath = param.get("outPutFileName");
textFileOutputMeta.setFileName(mongoToTxtTemplatePath);
//3、转换
Trans trans = new Trans(transMeta);
trans.execute(null);
// 等待转换执行结束
trans.waitUntilFinished();
// 抛出异常
if (trans.getErrors() > 0) {
throw new KettleException("There are errors during transformation exception!(传输过程中发生异常)");
}
Result result = trans.getResult();
KettleResult kettleResult = transResult(result);
return kettleResult;
}
说明:mysql是kettle自带的,mongo这些属于插件,所以需要加载plugins插件,如我是从本地加载的pentaho-mongodb-plugin的jar包。这里MongoDbInputMeta 之所以不是从ktr文件里面获取
MongoDbInputMeta mongoDbInputMeta =(MongoDbInputMeta) mongoStep.getStepMetaInterface();
,而是new一个出来,是因为MongoDbInputMeta左值是java自带的类加载器加载出来的,而右值是pentaho自定义的加载器加载出来的,类加载器不一样,可以把两个来加载器打印出来看是不一样的。这样,即使是同一个包下的同一个类,=赋值也会报ClassCastException转换异常。这个问题我没有解决,所以单独new了一个。
四、抽取sqlserver到txt,模板同二:
/**
* sqlserver-txt抽取
*/
public static KettleResult runSqlServerToTxtTemplateKtr(Map<String, String> param) throws KettleException, InvalidKeyException, UnsupportedEncodingException {
//读取模板文件
TransMeta transMeta = new TransMeta(DirectoryUtil.mysqlToTxtTemplatePath);
//1、表输入
//表输入
StepMeta tableStep = transMeta.findStep("tableInput");
TableInputMeta tableInputMeta = (TableInputMeta) tableStep.getStepMetaInterface();
//MSSQLServerDatabaseMeta dbServer = (MSSQLServerDatabaseMeta)tableInputMeta.getDatabaseMeta().getDatabaseInterface();
MSSQLServerDatabaseMeta dbServer = new MSSQLServerDatabaseMeta();
String host = param.get("host");
String port = param.get("port");
String db = param.get("dataBase");
String table = param.get("tableName");
String user = param.get("user");
String columnName = param.get("columnName");
String password = PwdUtil.decrypt(param.get("password"));
String columns = "*";
if (StringUtils.isNotEmpty(host)) {
dbServer.setHostname(host);
}
if (StringUtils.isNotEmpty(port)) {
dbServer.setDatabasePortNumberString(port);
}
if (StringUtils.isNotEmpty(db)) {
dbServer.setDatabaseName(db);
}
if (StringUtils.isNotEmpty(user)) {
dbServer.setUsername(user);
}
if (StringUtils.isNotEmpty(password)) {
dbServer.setPassword(password);
}
tableInputMeta.getDatabaseMeta().setDatabaseInterface(dbServer);
if(StringUtils.isNotEmpty(columnName)){
columns = columnName;
}
String sql = "SELECT ";
String limit = param.get("limit");
if(StringUtils.isNotEmpty(limit)){
sql += " TOP "+limit;
}
sql += columns+" FROM "+table;
tableInputMeta.setSQL(sql);
//2、文本文件输出
StepMeta textStep = transMeta.findStep("textFileOutput");
//文本文件
String mysqlToTxtTemplatePath = param.get("outPutFileName");
TextFileOutputMeta textFileOutputMeta = (TextFileOutputMeta) textStep.getStepMetaInterface();
textFileOutputMeta.setFileName(mysqlToTxtTemplatePath);
//是否追加
if(Boolean.valueOf(param.get("appendFile"))){
textFileOutputMeta.setFileAppended(true);
}
//3、转换
Trans trans = new Trans(transMeta);
trans.execute(null);
// 等待转换执行结束
trans.waitUntilFinished();
// 抛出异常
if (trans.getErrors() > 0) {
throw new KettleException("There are errors during transformation exception!(传输过程中发生异常)");
}
Result result = trans.getResult();
KettleResult kettleResult = transResult(result);
return kettleResult;
}
五、执行SQL:
/**
* 执行sql
* @param param
*/
public static KettleResult runSql(Map<String,String> param) throws KettleException, UnsupportedEncodingException, InvalidKeyException {
//读取模板文件
TransMeta transMeta = new TransMeta(DirectoryUtil.sqlTemplatePath);
StepMeta sqlStep = transMeta.findStep("sqlStep");
ExecSQLMeta execSQLMeta = (ExecSQLMeta)sqlStep.getStepMetaInterface();
//参数
String host = param.get("host");
String port = param.get("port");
String dbName = param.get("dataBase");
String userName = param.get("user");
String pwd = PwdUtil.decrypt(param.get("password"));
String sql = param.get("sql");
String dbType = param.get("targetType");
if(DatabaseType.mysql.toString().equals(dbType)){
DatabaseInterface mysqlDb = new MySQLDatabaseMeta();
mysqlDb.setHostname(host);
mysqlDb.setDatabasePortNumberString(port);
mysqlDb.setDatabaseName(dbName);
mysqlDb.setUsername(userName);
mysqlDb.setPassword(pwd);
execSQLMeta.getDatabaseMeta().setDatabaseInterface(mysqlDb);
}else if(DatabaseType.sqlServer.toString().equals(dbType)){
MSSQLServerDatabaseMeta sqlServerDb = new MSSQLServerDatabaseMeta();
sqlServerDb.setHostname(host);
sqlServerDb.setDatabasePortNumberString(port);
sqlServerDb.setDatabaseName(dbName);
sqlServerDb.setUsername(userName);
sqlServerDb.setPassword(pwd);
execSQLMeta.getDatabaseMeta().setDatabaseInterface(sqlServerDb);
}
execSQLMeta.setSql(sql);
//替换数据库相关
//3、转换
Trans trans = new Trans(transMeta);
trans.execute(null);
trans.waitUntilFinished();
if (trans.getErrors() > 0) {
throw new KettleException("There are errors during transformation exception!(传输过程中发生异常)");
}
Result result = trans.getResult();
KettleResult kettleResult = transResult(result);
return kettleResult;
}
总的代码为:
<dependency>
<groupId>pentaho-kettle</groupId>
<artifactId>kettle-core</artifactId>
<version>${kettle.version}</version>
</dependency>
<dependency>
<groupId>pentaho-kettle</groupId>
<artifactId>kettle-engine</artifactId>
<version>${kettle.version}</version>
</dependency>
<dependency>
<groupId>pentaho</groupId>
<artifactId>metastore</artifactId>
<version>${kettle.version}</version>
</dependency>
<dependency>
<groupId>pentaho</groupId>
<artifactId>pentaho-mongodb-plugin</artifactId>
<version>${kettle.version}</version>
</dependency>
<!--sqlserver抽取驱动 -->
<dependency>
<groupId>net.sourceforge.jtds</groupId>
<artifactId>jtds</artifactId>
<version>1.3.1</version>
</dependency>
我这里的kettle.version版本为8.2.0.0-342
public class KettleUtil {
private static final Logger logger = LoggerFactory.getLogger(KettleUtil.class);
static {
try {
String kettlePluginFolder = "D://javas//kettle//data-integration//plugins";
StepPluginType.getInstance().getPluginFolders().add(new PluginFolder(kettlePluginFolder, false, true));
// 初始化
KettleEnvironment.init();
EnvUtil.environmentInit();
} catch (KettleException e) {
logger.error(e.getMessage(), e);
}
}
public static KettleFileRepository initFileRepository(Node node) {
Assert.notNull(node, "node must be not null.");
Assert.notEmpty(node.getProperties(), "node's properties must be not empty.");
String templatePath = node.getProperties().get("templatePath");
Assert.hasLength(templatePath, "template path must be not empty.");
KettleFileRepository repository = null;
try {
KettleEnvironment.init();
KettleFileRepositoryMeta repositoryMeta = new KettleFileRepositoryMeta(node.getId(), node.getName(), node.getDescription(), templatePath.substring(0, templatePath.lastIndexOf("/")));
repository = new KettleFileRepository();
repository.init(repositoryMeta);
} catch (Exception e) {
logger.error("加载kettle环境失败:" + e.getMessage(), e);
}
return repository;
}
public static void executeTrans(KettleFileRepository repository, String filePath, Map<String, String> properties) throws Exception {
Assert.notNull(repository, "kettle repository must be nut null.");
Assert.hasLength(filePath, "file path must be not empty.");
String fileName = filePath.substring(filePath.lastIndexOf('/') + 1, filePath.lastIndexOf('.'));
TransMeta transMeta = repository.loadTransformation(repository.getTransformationID(fileName, null), null);
Trans trans = new Trans(transMeta);
if (!CollectionUtils.isEmpty(properties)) {
for (Map.Entry<String, String> entry : properties.entrySet()) {
trans.setVariable(entry.getKey(), entry.getValue());
}
}
// 执行转换
trans.execute(null);
// 等待转换执行结束
trans.waitUntilFinished();
// 抛出异常
if (trans.getErrors() > 0) {
trans.stopAll();
throw new Exception("传输过程中发生异常");
}
}
public static KettleResult runTxtToMysqlTemplateKtr(Map<String, String> param) throws KettleException, InvalidKeyException, UnsupportedEncodingException {
//模板文件
TransMeta transMeta = new TransMeta(DirectoryUtil.txtToMysqlTemplatePath);
// 步骤:获取文件名
StepMeta findStep = transMeta.findStep("getFileName");
GetFileNamesMeta fileNamesMeta = (GetFileNamesMeta) findStep.getStepMetaInterface();
String fileName = param.get("sourcePath");
if (StringUtils.isNotEmpty(fileName)) {
if (fileName.contains("${PART}")) {
fileName = fileName.replace("${PART}", new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
}
fileNamesMeta.setFileName(new String[]{fileName});
}
// 步骤:文本文件输入
StepMeta textStep = transMeta.findStep("textFileInput");
TextFileInputMeta fileInputMeta = (TextFileInputMeta) textStep.getStepMetaInterface();
List<FieldMeta> fieldMetas = JSONUtils.parseList(param.get("mapping"), FieldMeta.class);
// 字段
BaseFileField[] baseFileInputFields = new BaseFileField[fieldMetas.size()];
// 映射
String[] inputFields = new String[fieldMetas.size()];
if (!CollectionUtils.isEmpty(fieldMetas)) {
for (int i = 0; i < fieldMetas.size(); i++) {
BaseFileField bf = new BaseFileField();
bf.setName(fieldMetas.get(i).getName());
bf.setType(fieldMetas.get(i).getDataType());
baseFileInputFields[i] = bf;
inputFields[i] = fieldMetas.get(i).getName();
}
}
fileInputMeta.inputFields = baseFileInputFields;
//fileInputMeta.inputFiles.includeSubFolders = includeSubFolderBoolean;
fileInputMeta.content.separator = param.get("division");
// 步骤:表输出
StepMeta tableStep = transMeta.findStep("tableOutput");
TableOutputMeta tableOutputMeta = (TableOutputMeta) tableStep.getStepMetaInterface();
String host = param.get("host");
String port = param.get("port");
String db = param.get("dataBase");
String table = param.get("tableName");
String user = param.get("user");
String password = PwdUtil.decrypt(param.get("password"));
MySQLDatabaseMeta mySQLDatabaseMeta = (MySQLDatabaseMeta) tableOutputMeta.getDatabaseMeta().getDatabaseInterface();
if (StringUtils.isNotEmpty(host)) {
mySQLDatabaseMeta.setHostname(host);
}
if (StringUtils.isNotEmpty(port)) {
mySQLDatabaseMeta.setDatabasePortNumberString(port);
}
if (StringUtils.isNotEmpty(db)) {
mySQLDatabaseMeta.setDatabaseName(db);
}
if (StringUtils.isNotEmpty(user)) {
mySQLDatabaseMeta.setUsername(user);
}
if (StringUtils.isNotEmpty(password)) {
mySQLDatabaseMeta.setPassword(password);
}
if (StringUtils.isNotEmpty(table)) {
tableOutputMeta.setTableName(table);
}
tableOutputMeta.setFieldStream(inputFields);
tableOutputMeta.setFieldDatabase(inputFields);
//是否全量,全量则删除表
if(Boolean.valueOf(param.get("isTruncateTable"))){
tableOutputMeta.setTruncateTable(true);
}
// 转换
Trans trans = new Trans(transMeta);
trans.execute(null);
// 等待转换执行结束
trans.waitUntilFinished();
// 抛出异常
if (trans.getErrors() > 0) {
throw new KettleException("There are errors during transformation exception!(传输过程中发生异常)");
}
Result result = trans.getResult();
KettleResult kettleResult = transResult(result);
return kettleResult;
}
public static KettleResult runMysqlToTxtTemplateKtr(Map<String, String> param) throws KettleException, InvalidKeyException, UnsupportedEncodingException {
//读取模板文件
TransMeta transMeta = new TransMeta(DirectoryUtil.mysqlToTxtTemplatePath);
//1、表输入
StepMeta tableStep = transMeta.findStep("tableInput");
TableInputMeta tableInputMeta = (TableInputMeta) tableStep.getStepMetaInterface();
String host = param.get("host");
String port = param.get("port");
String db = param.get("dataBase");
String table = param.get("tableName");
String user = param.get("user");
String columnName = param.get("columnName");
String password = PwdUtil.decrypt(param.get("password"));
DatabaseMeta mySQLDatabaseMeta = new DatabaseMeta();
mySQLDatabaseMeta.setDatabaseType("mySQL");
String columns = "*";
if (StringUtils.isNotEmpty(host)) {
mySQLDatabaseMeta.setHostname(host);
}
if (StringUtils.isNotEmpty(port)) {
mySQLDatabaseMeta.setDBPort(port);
}
if (StringUtils.isNotEmpty(db)) {
mySQLDatabaseMeta.setDBName(db);
}
if (StringUtils.isNotEmpty(user)) {
mySQLDatabaseMeta.setUsername(user);
}
if (StringUtils.isNotEmpty(password)) {
mySQLDatabaseMeta.setPassword(password);
}
if (StringUtils.isNotEmpty(table)) {
tableInputMeta.setDatabaseMeta(mySQLDatabaseMeta);
}
if(StringUtils.isNotEmpty(columnName)){
columns = columnName;
}
tableInputMeta.setSQL("select "+columns+" from "+table);
//2、文本文件输出
StepMeta textStep = transMeta.findStep("textFileOutput");
//文本文件
String mysqlToTxtTemplatePath = param.get("outPutFileName");
TextFileOutputMeta textFileOutputMeta = (TextFileOutputMeta) textStep.getStepMetaInterface();
textFileOutputMeta.setFileName(mysqlToTxtTemplatePath);
//是否追加
if(Boolean.valueOf(param.get("appendFile"))){
textFileOutputMeta.setFileAppended(true);
}
//3、转换
Trans trans = new Trans(transMeta);
trans.execute(null);
// 等待转换执行结束
trans.waitUntilFinished();
// 抛出异常
if (trans.getErrors() > 0) {
throw new KettleException("There are errors during transformation exception!(传输过程中发生异常)");
}
Result result = trans.getResult();
KettleResult kettleResult = transResult(result);
return kettleResult;
}
private static KettleResult transResult(Result result) {
KettleResult kettleResult = new KettleResult();
kettleResult.setEntryNr(result.getEntryNr());
kettleResult.setExitStatus(result.getExitStatus());
kettleResult.setLogChannelId(result.getLogChannelId());
kettleResult.setLogText(result.getLogText());
kettleResult.setNrErrors(result.getNrErrors());
kettleResult.setNrFilesRetrieved(result.getNrFilesRetrieved());
kettleResult.setNrLinesDeleted(result.getNrLinesDeleted());
kettleResult.setNrLinesInput(result.getNrLinesInput());
kettleResult.setNrLinesOutput(result.getNrLinesOutput());
kettleResult.setNrLinesRead(result.getNrLinesRead());
kettleResult.setNrLinesRejected(result.getNrLinesRejected());
kettleResult.setNrLinesUpdated(result.getNrLinesUpdated());
kettleResult.setNrLinesWritten(result.getNrLinesWritten());
kettleResult.setResult(result.getResult());
kettleResult.setStopped(result.stopped);
kettleResult.setRows(result.getRows());
kettleResult.setResultFiles(result.getResultFiles());
return kettleResult;
}
public static KettleResult runMongoTotxt(Map<String, String> param) throws KettleException, NoSuchMethodException, InvocationTargetException, IllegalAccessException, ClassNotFoundException, InstantiationException, UnsupportedEncodingException, InvalidKeyException {
String mongo = DirectoryUtil.mongoToTxtTemplatePath;
//读取模板文件
TransMeta transMeta = new TransMeta(mongo);
//获取MongoDBInput
StepMeta mongoStep = transMeta.findStep("MongoDBInput");
MongoDbInputMeta mongoDbInputMeta = new MongoDbInputMeta();
String host = param.get("host");
String port = param.get("port");
String db = param.get("dataBase");
String collection = param.get("collection");
String user = param.get("user");
String password = PwdUtil.decrypt(param.get("password"));
String mongoDb = param.get("mongoDb");
mongoDbInputMeta.setHostnames(host);
mongoDbInputMeta.setPort(port);
if(StringUtils.isNotEmpty(db) && StringUtils.isNotEmpty(user) && StringUtils.isNotEmpty(password)){
mongoDbInputMeta.setAuthenticationMechanism("MONGODB-CR");
mongoDbInputMeta.setAuthenticationDatabaseName(db);
mongoDbInputMeta.setAuthenticationUser(user);
mongoDbInputMeta.setAuthenticationPassword(password);
}
mongoDbInputMeta.setDbName(mongoDb);
mongoDbInputMeta.setCollection(collection);
/* mongoDbInputMeta.setHostnames("127.0.0.1");
mongoDbInputMeta.setPort("27017");
mongoDbInputMeta.setDbName("wechatMsg");
mongoDbInputMeta.setCollection("wechatmsg");*/
List<MongoField> mongoFields = new ArrayList<>();
List<MongoFieldMeta> fieldMetas = JSONUtils.parseList(param.get("mongoMapping"), MongoFieldMeta.class);
for(MongoFieldMeta mongoFieldMeta :fieldMetas ){
MongoField mongoField = new MongoField();
mongoField.m_fieldName = mongoFieldMeta.getName();
mongoField.m_fieldPath = mongoFieldMeta.getPath();
mongoField.m_kettleType = mongoFieldMeta.getType();
mongoFields.add(mongoField);
}
mongoDbInputMeta.setMongoFields(mongoFields);
mongoDbInputMeta.setOutputJson(false);
//mongoDbInputMeta.setJsonFieldName("json");
mongoStep.setStepMetaInterface(mongoDbInputMeta);
//获取fileOutPut
StepMeta txtStep = transMeta.findStep("fileOutPut");
TextFileOutputMeta textFileOutputMeta = (TextFileOutputMeta) txtStep.getStepMetaInterface();
//不加后缀
String mongoToTxtTemplatePath = param.get("outPutFileName");
textFileOutputMeta.setFileName(mongoToTxtTemplatePath);
//3、转换
Trans trans = new Trans(transMeta);
trans.execute(null);
// 等待转换执行结束
trans.waitUntilFinished();
// 抛出异常
if (trans.getErrors() > 0) {
throw new KettleException("There are errors during transformation exception!(传输过程中发生异常)");
}
Result result = trans.getResult();
KettleResult kettleResult = transResult(result);
return kettleResult;
}
}