Spring Boot 集成 Kettle 实现数据迁移自动任务

用户5133946410785
3,280 阅读2分钟

Spring Boot 管理Kettle定时任务具体步骤

  1. 在Kettle里创建一个转换,这里演示的转换就是插入一条新的记录。   1.png

并导出转换ktr文件

  1. 在kettle目录data-integration\lib下寻找以下jar包放入resource\lib文件夹

2.png  

在pom.xml添加下面的内容，这里以kettle 9.2.0.0-290版本为例。

<dependency>
    <groupId>com.jcraft</groupId>
    <artifactId>jsch</artifactId>
    <version>0.1.54</version>
    <scope>system</scope>
    <systemPath>${project.basedir}/src/main/resources/lib/jsch-0.1.54.jar</systemPath>
</dependency>

<dependency>
    <groupId>org.pentaho.di</groupId>
    <artifactId>kettle-core</artifactId>
    <version>9.2.0.0-290</version>
    <scope>system</scope>
    <systemPath>${project.basedir}/src/main/resources/lib/kettle-core-9.2.0.0-290.jar</systemPath>
</dependency>
<dependency>
    <groupId>org.pentaho.di</groupId>
    <artifactId>kettle-engine</artifactId>
    <version>9.2.0.0-290</version>
    <scope>system</scope>
    <systemPath>${project.basedir}/src/main/resources/lib/kettle-engine-9.2.0.0-290.jar</systemPath>
</dependency>

<dependency>
    <groupId>org.pentaho.metastore</groupId>
    <artifactId>metastore</artifactId>
    <version>9.2.0.0-290</version>
    <scope>system</scope>
    <systemPath>${project.basedir}/src/main/resources/lib/metastore-9.2.0.0-290.jar</systemPath>
</dependency>

<dependency>
    <groupId>org.pentaho.di</groupId>
    <artifactId>pentaho-encryption-support</artifactId>
    <version>9.2.0.0-290</version>
    <scope>system</scope>
    <systemPath>${project.basedir}/src/main/resources/lib/pentaho-encryption-support-9.2.0.0-290.jar</systemPath>
</dependency>

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-vfs2</artifactId>
    <version>2.7.0</version>
    <scope>system</scope>
    <systemPath>${project.basedir}/src/main/resources/lib/commons-vfs2-2.7.0.jar</systemPath>
</dependency>

<!-- https://mvnrepository.com/artifact/net.sourceforge.jtds/jtds -->
<dependency>
    <groupId>net.sourceforge.jtds</groupId>
    <artifactId>jtds</artifactId>
    <version>1.3.1</version>
</dependency>


<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-logging</artifactId>
    <version>1.2</version>
    <scope>system</scope>
    <systemPath>${project.basedir}/src/main/resources/lib/commons-logging-1.2.jar</systemPath>
</dependency>

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.2</version>
    <scope>system</scope>
    <systemPath>${project.basedir}/src/main/resources/lib/commons-io-2.2.jar</systemPath>
</dependency>

<!-- https://mvnrepository.com/artifact/commons-lang/commons-lang -->
<dependency>
    <groupId>commons-lang</groupId>
    <artifactId>commons-lang</artifactId>
    <version>2.6</version>
</dependency>

<dependency>
    <groupId>com.google.guava</groupId>
    <artifactId>guava</artifactId>
    <version>18.0</version>
</dependency>

<!-- DB driver 数据库驱动 -->
<dependency>
    <groupId>net.sourceforge.jtds</groupId>
    <artifactId>jtds</artifactId>
    <version>1.3.1</version>
</dependency>

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>9.2.1.jre8</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>

 

  1. 在resource下新建kettle-password-encoder-plugins.xml （这一步很关键，不添加会报错）
<password-encoder-plugins>

    <password-encoder-plugin id="Kettle">
        <description>Kettle Password Encoder</description>
        <classname>org.pentaho.di.core.encryption.KettleTwoWayPasswordEncoder</classname>
    </password-encoder-plugin>

</password-encoder-plugins>
  1. 创建自动任务，这里使用ruoyi自带的quartz自动任务，搭配KettleUtil.java 工具类。
/**
 * kettle version 9.2.0.0 - 290
 *
 * @author Wance
 */
public class KettleUtil {

    /** kettle job/tran file path (kettle 作业/任务 文件地址) */
    private static String KETTLE_PATH = "kettle";

    /**
     * 调用trans文件
     *
     * @param transFileName
     * @throws Exception
     */
    public static void callNativeTrans(String transFileName) throws Exception {
        callNativeTransWithParams(null, transFileName);
    }

    /**
     * 调用trans文件 带参数的
     *
     * @param params
     * @param transFileName
     * @throws Exception
     */
    public static void callNativeTransWithParams(String[] params, String transFileName) throws Exception {
        String path = KETTLE_PATH + File.separator + transFileName;
        File file = new File(path);
        if(!file.exists()) {
            throw new FileNotFoundException("File doesn't exists!(文件不存在)"+path);
        }
                // 初始化
        KettleEnvironment.init();
        EnvUtil.environmentInit();
        TransMeta transMeta = new TransMeta(path);
        //转换
        Trans trans = new Trans(transMeta);
        //执行
        trans.execute(params);
        //等待结束
        trans.waitUntilFinished();
        //抛出异常
        if (trans.getErrors() > 0) {
            throw new Exception("There are errors during transformation exception!(传输过程中发生异常)");
        }
    }

    /**
     * 调用job文件
     *
     * @param jobName
     * @throws Exception
     */
    public static void callNativeJob(String jobName) throws Exception {
        String path = KETTLE_PATH + File.separator + jobName;
        File file = new File(path);
        if(!file.exists()) {
            throw new FileNotFoundException("File doesn't exists!(文件不存在):"+path);
        }
        // 初始化
        KettleEnvironment.init();

        JobMeta jobMeta = new JobMeta(path, null);
        Job job = new Job(null, jobMeta);
        //向Job 脚本传递参数，脚本中获取参数值：${参数名}
        //job.setVariable(paraname, paravalue);
        job.start();
        job.waitUntilFinished();
        if (job.getErrors() > 0) {
            throw new Exception("There are errors during job exception!(执行job发生异常)");
        }
    }
}
  1. Quartz自动任务

注意System.out.println最好替换掉。

/**
 * 执行kjb文件
 * @param filename 文件名
 * dirPath 文件路径
 * @return
 */
public void runKjb(String filename) {
    System.out.println(filename+"文件开始执行！");

    try {
        KettleUtil.callNativeJob(filename);
        System.out.println(filename+"文件执行完毕！");
    } catch (Exception e) {
        e.printStackTrace();
        System.out.println(filename+"文件执行异常！");
    }
}

/**
 * 执行ktr文件
 * @param filename 文件名
 * dirPath 文件路径
 * @return
 */
public void runKtr(String filename) {
    System.out.println(filename+"文件开始执行！");

    try {
        KettleUtil.callNativeTrans(filename);
        System.out.println(filename+"文件执行完毕！");
    } catch (Exception e) {
        e.printStackTrace();
        System.out.println(filename+"文件执行异常！");
    }
}
  1. 将项目打包，以及在jar同目录下创建kettle文件,注意将kettle 库文件等打包正确，将需要运行的ktr文件放入其中，并运行jar包。

3.png

  1. 创建定时任务并使用，定时任务runKtr(‘ktr文件名称’)。

4.png

5.png

  1. 运行定时任务后会成功添加新记录。

6.png

avatar
文章
阅读
粉丝