ETL工具kettle8.2集成spring boot数据抽取

1.添加kettle所需依赖包<dependency><groupId>org.pentaho</groupId><artifactId>kettle-core</artifactId><version>${kettle.version}</version>&

柠檬冰块

2088人浏览 · 2022-04-24 15:42:11

柠檬冰块 · 2022-04-24 15:42:11 发布

1.添加kettle所需依赖包

<!-- kettle 8.3 集成包-->
<dependency>
   <groupId>org.pentaho</groupId>
   <artifactId>kettle-core</artifactId>
   <version>${kettle.version}</version>
</dependency>
<dependency>
   <groupId>org.pentaho</groupId>
   <artifactId>kettle-engine</artifactId>
   <version>${kettle.version}</version>
</dependency>
<dependency>
   <groupId>org.pentaho</groupId>
   <artifactId>metastore</artifactId>
   <version>${kettle.version}</version>
</dependency>
<dependency>
   <groupId>org.pentaho</groupId>
   <artifactId>pentaho-vfs-browser</artifactId>
   <version>${kettle.version}</version>
</dependency>
<dependency>
   <groupId>org.pentaho</groupId>
   <artifactId>pdi-dataservice-client-plugin</artifactId>
   <version>${kettle.version}</version>
</dependency>
<dependency>
   <groupId>org.pentaho</groupId>
   <artifactId>pentaho-mongo-utils</artifactId>
   <version>${kettle.version}</version>
</dependency>
<dependency>
   <groupId>org.pentaho</groupId>
   <artifactId>pentaho-mongodb-plugin</artifactId>
   <version>${kettle.version}</version>
</dependency>
<!--        <dependency>-->
<!--            <groupId>org.pentaho</groupId>-->
<!--            <artifactId>scannotation</artifactId>-->
<!--            <version>1.0.2</version>-->
<!--        </dependency>-->

 <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.46</version>
       </dependency>
<dependency>
   <groupId>org.pentaho</groupId>
   <artifactId>json</artifactId>
   <version>${kettle.version}</version>
</dependency>
<dependency>
                 <groupId>org.pentaho</groupId>
                 <artifactId>jsonpath</artifactId>
                 <version>1.0</version>
             </dependency>
             <dependency>
                 <groupId>org.pentaho</groupId>
                 <artifactId>json-simple</artifactId>
                 <version>1.1</version>
             </dependency>
             <dependency>
                 <groupId>org.pentaho</groupId>
                 <artifactId>simple-jndi</artifactId>
                 <version>1.0.2</version>
       </dependency>
<!-- 其他需要的基础包 -->
<dependency>
   <groupId>org.owasp.encoder</groupId>
   <artifactId>encoder</artifactId>
   <version>1.2.1</version>
</dependency>
<dependency>
   <groupId>org.javassist</groupId>
   <artifactId>javassist</artifactId>
   <version>3.20.0-GA</version>
</dependency>
<dependency>
   <groupId>com.google.guava</groupId>
   <artifactId>guava</artifactId>
   <version>17.0</version>
</dependency>
<dependency>
   <groupId>commons-codec</groupId>
   <artifactId>commons-codec</artifactId>
   <version>1.10</version>
</dependency>
<dependency>
   <groupId>org.apache.commons</groupId>
   <artifactId>commons-vfs2</artifactId>
   <version>2.2</version>
</dependency>
<dependency>
   <groupId>org.codehaus.janino</groupId>
   <artifactId>commons-compiler</artifactId>
   <version>3.0.8</version>
</dependency>
<dependency>
   <groupId>org.codehaus.janino</groupId>
   <artifactId>janino</artifactId>
   <version>3.0.8</version>
</dependency>
<dependency>
   <groupId>org.apache.httpcomponents</groupId>
   <artifactId>httpclient</artifactId>
   <version>4.5.3</version>
</dependency>

2，maven 添加命令

mvn install:install-file -DgroupId=org.pentaho -DartifactId=kettle-core -Dversion=8.3.0.0-371 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\kettle-core-8.3.0.0-371.jar

mvn install:install-file -DgroupId=org.pentaho -DartifactId=kettle-engine -Dversion=8.3.0.0-371 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\kettle-engine-8.3.0.0-371.jar


mvn install:install-file -DgroupId=org.pentaho -DartifactId=metastore -Dversion=8.3.0.0-371 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\metastore-8.3.0.0-371.jar
mvn install:install-file -DgroupId=org.pentaho -DartifactId=pentaho-vfs-browser -Dversion=8.3.0.0-371 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\pentaho-vfs-browser-8.3.0.0-371.jar
mvn install:install-file -DgroupId=org.pentaho -DartifactId=pdi-dataservice-client-plugin -Dversion=8.3.0.0-371 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\pdi-dataservice-client-plugin-8.3.0.0-371.jar
mvn install:install-file -DgroupId=org.pentaho -DartifactId=pentaho-mongo-utils -Dversion=8.3.0.0-371 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\pentaho-mongo-utils-8.3.0.0-371.jar
mvn install:install-file -DgroupId=org.pentaho -DartifactId=pentaho-mongodb-plugin -Dversion=8.3.0.0-371 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\pentaho-mongodb-plugin-8.3.0.0-371.jar
mvn install:install-file -DgroupId=org.pentaho -DartifactId=json -Dversion=8.3.0.0-371 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\json-8.3.0.0-371.jar
mvn install:install-file -DgroupId=org.pentaho -DartifactId=scannotation -Dversion=1.0.2 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\scannotation-1.0.2.jar
mvn install:install-file -DgroupId=org.pentaho -DartifactId=jsonpath -Dversion=1.0 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\jsonpath-1.0.jar
mvn install:install-file -DgroupId=org.pentaho -DartifactId=json-simple -Dversion=1.1 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\json-simple-1.1.jar

mvn install:install-file -DgroupId=org.pentaho -DartifactId=simple-jndi -Dversion=1.0.2 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\simple-jndi-1.0.2.jar


mvn install:install-file -DgroupId=javax.mail -DartifactId=mail -Dversion=1.6.1 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\javax.mail-1.6.1.jar


mvn install:install-file -DgroupId=mysql -DartifactId=mysql-connector-java -Dversion=5.1.46 -Dpackaging=jar -Dfile=E:\BaiduNetdiskDownload\apache-maven-3.3.9\repository3\kettle\lib\mysql-connector-java-5.1.46-bin.jar

3，Kettle 实现类

package com.yunshidi.freight.wf.shipper.kettle.implement;

import com.yunshidi.freight.wf.shipper.kettle.report.KettleResult;
import org.pentaho.di.core.KettleEnvironment;
import org.pentaho.di.core.exception.KettleException;
import org.pentaho.di.job.Job;
import org.pentaho.di.job.JobMeta;
import org.pentaho.di.trans.Trans;
import org.pentaho.di.trans.TransMeta;

import java.util.Iterator;
import java.util.Map;

/**
 * @program: collection-parent
 * @description: kettle执行类
 * @author: fansong
 * @create: 2019-12-20 15:03
 **/
public class KettleImplement {

    /**
     * 执行ktr文件
     *
     * @param fname  ktr文件地址
     * @param params 传入参数
     * @return
     */
    public static KettleResult runKtr(String fname, Map<String, String> params) throws KettleException {
        KettleResult kettleResult = new KettleResult();
        KettleEnvironment.init();
        TransMeta tm = new TransMeta(fname);
        Trans trans = new Trans(tm);
        if (params != null) {
            Iterator<Map.Entry<String, String>> entries = params.entrySet().iterator();
            while (entries.hasNext()) {
                Map.Entry<String, String> entry = entries.next();
                trans.setParameterValue(entry.getKey(), entry.getValue());
            }
        }
        trans.execute(null);
        trans.waitUntilFinished();

        kettleResult.setTrans(trans);
        return kettleResult;
    }


    /**
     * 执行kjb文件
     *
     * @param fname
     * @param params
     * @return
     */
    public static KettleResult runKjb(String fname, Map<String, String> params) throws KettleException {
        KettleResult kettleResult = new KettleResult();
        KettleEnvironment.init();
        JobMeta jm = new JobMeta(fname, null);
        Job job = new Job(null, jm);
        if (params != null) {
            Iterator<Map.Entry<String, String>> entries = params.entrySet().iterator();
            while (entries.hasNext()) {
                Map.Entry<String, String> entry = entries.next();
                job.setVariable(entry.getKey(), entry.getValue());
            }
        }
        job.start();
        job.waitUntilFinished();
        kettleResult.setJob(job);
        return kettleResult;
    }
}

4，编写ktr文件，放到项目下

5，调用kettle执行ktr

public static void main(String[] args) {
    String path = Thread.currentThread().getContextClassLoader().getResource("").getPath()+"kettleFile/data.ktr";
        path = "E:/project/test/target/classes/kettleFile/data.ktr";
    System.out.println("===="+path);
    try {
        KettleResult kettleResult = KettleImplement.runKtr(path,null);
        System.out.println("执行成功" + kettleResult.getStatus());
    } catch (KettleException e) {
        e.printStackTrace();
    }
}


/**
 * 子平台统计列表+饼图
 *
 * @param request
 * @return
 */
@RequestMapping(value = "/children/statistic/info/list")
public ResultModel<?> childrenPlatformInfoList(OperSaasDayCountRequest request) {
    return ResultModel.success(userService.childrenPlatformListKet(request));
}

/**
 * 子平台统计列表
 *
 * @param request
 * @return
 */
public List<OperSaasDayCountModel> childrenPlatformListKet(OperSaasDayCountRequest request) {
    PlatformRequest platformRequest = new PlatformRequest();
    platformRequest.setPlatformId(request.getPlatformId());
    List<PlatformInfoVo> platformInfoVoList = platformService.getList(platformRequest);

    QueryOperSaasDayCountModel queryOperSaasDayCountModel1 = new QueryOperSaasDayCountModel();
    queryOperSaasDayCountModel1.setTimeStart(request.getDateTimeStart());
    queryOperSaasDayCountModel1.setTimeEnd(request.getDateTimeEnd());
    queryOperSaasDayCountModel1.setPlatformInfoVoList(BeanUtil.copyToList(platformInfoVoList, PlatformInfoModel.class));
    queryOperSaasDayCountModel1.setFlag(true);
    return operSaasDayCountRemote.childrenPlatformInfStatisticByDay(queryOperSaasDayCountModel1);
}