kettle（二）java调用kettle api

w_t_y_y

已于 2023-12-30 11:08:13 修改

阅读量2.5k

点赞数 2

CC 4.0 BY-SA版权

分类专栏：常用技术文章标签： java 开发语言

于 2020-11-19 14:38:24 首次发布

本文链接：https://blog.youkuaiyun.com/w_t_y_y/article/details/95315480

常用技术专栏收录该内容

27 篇文章

订阅专栏

为定时执行数据抽取功能，避免用Kettle工具新建job的麻烦，可使用Java后台新建任务，由job调度根据任务频率执行，读取ktr文件并替换参数。文中还列举了txt到mysql、mysql到txt等多种数据抽取示例，以及执行SQL的情况，同时提到了加载插件和类加载器问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景：现需要定时执行数据抽取功能，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;
    }

}