KETTLE封装

一、背景　　

　　每天好像都很忙碌，已经不记得上次写博客是什么时候，这两天公司项目一个特殊的组网环境，需要重新搭建了KETTLE的封装，实现java调用ETL完成数据抽取转换加载的一些功能，搞了这么这么久的ETL，没想到今天掉坑里了，浪费了小半天的时间，以前觉得写博客要记录一些重大突破的或者高频的技术点，经历了这次，决定把所有遇到的问题都记录一下，顺道分享一下技术实现。

二、问题

      1、kettle的java开发包里没有添加pom依赖，需要手动添加和组合，这就回到了最原始的项目开发模式，那问题也很突出了，jar包冲突或者jar包引入不合适

      2、kettle的java代码组件的适用

三、处理过程

      要搭建kettle的java运行环境首先要引入三个包：kettle-core、kettle-engine、kettle-dbdialog，maven引入方式如下： 

        <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-kettle</groupId>
            <artifactId>kettle-dbdialog</artifactId>
            <version>${kettle.version}</version>
        </dependency>

     然后封装ETL的执行代码：

public class KettleUtil {
    static {
        try {
            // 初始化
            KettleEnvironment.init();
        } catch (Exception e) {
            e.printStackTrace();
            throw new BusinessBaseException(new BusinessExceptionDesc("etl:100001",BusinessExceptionDesc.SHOW_TYPE.ERROR,"ETL执行异常"));
        }
    }
    public static String runTransfer(Map<String,String> map, String ktrPath) {
        Trans trans = null;
        String json = null;
        try {
            // 转换元对象
            TransMeta transMeta = new TransMeta(ktrPath);

            // 转换
            trans = new Trans(transMeta);

            if(null != map){
                for (Map.Entry<String,String> entry : map.entrySet()){
                    trans.setVariable(entry.getKey(),entry.getValue());
                }
            }

            // 执行转换
            trans.execute(null);

            // 记录最后一个步骤的数据
            final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>();
            RowListener rowListner = new RowListener() {
                public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row)
                        throws KettleStepException {
                    rows.add(new RowMetaAndData(rowMeta, row));
                }

                public void rowReadEvent(RowMetaInterface arg0, Object[] arg1)
                        throws KettleStepException {
                }

                public void errorRowWrittenEvent(RowMetaInterface arg0,
                                                 Object[] arg1) throws KettleStepException {
                }
            };

            //获取ETL步数
            List<StepMetaDataCombi> steps = trans.getSteps();

            //最后一个步骤名称
            String stepname = steps.get(steps.size() - 1).stepname;

            //添加行监听
            StepInterface stepInterface = trans.findRunThread(stepname);
            stepInterface.addRowListener(rowListner);

            // 等待执行完毕
            trans.waitUntilFinished();

            if(!rows.isEmpty()){
                if(rows.size() > 1){
                    StringBuffer resultBuf = new StringBuffer();
                    for(RowMetaAndData rmad : rows){
                        String rowData = rmad.getString("resultData",null);
                        if(null != rowData){
                            resultBuf.append(rowData).append(",");
                        }
                    }
                    if(resultBuf.length() > 0){
                        resultBuf.deleteCharAt(resultBuf.length() - 1);
                        json = "["+ resultBuf +"]";
                    }
                }else if(rows.size() == 1){
                    RowMetaAndData rmad = rows.get(0);
                    json = rmad.getString("resultData", null);
                }
            }

            // 抛出异常
            if (trans.getErrors() > 0) {
                String message = KettleLogStore.getAppender().getBuffer(trans.getLogChannelId(), false).toString();
                throw new BusinessBaseException(new BusinessExceptionDesc("etl:100003",BusinessExceptionDesc.SHOW_TYPE.ERROR,message));
            }
            return json;
        } catch (KettleException e) {
            e.printStackTrace();
            throw new BusinessBaseException(new BusinessExceptionDesc("etl:100002",BusinessExceptionDesc.SHOW_TYPE.ERROR,"ETL执行异常"));
        }
    }
}

        这个时候启动项目是没有什么问题，当我们用一个单元测试的方式测试时，会发现有好多的类都找不到，很明显的错误就是class not found，按照缺失的类名可以去https://mvnrepository.com/搜索或者去KETTLE的安装目录下的lib面搜索都可以解决问题：

nested exception is java.lang.NoClassDefFoundError: com/google/common/util/concurrent/SettableFuture
java.lang.NoClassDefFoundError: org/mozilla/javascript/JavaScriptException

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

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-vfs2</artifactId>
            <version>2.1</version>
        </dependency>

        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>

        <dependency>
            <groupId>com.sun.mail</groupId>
            <artifactId>javax.mail</artifactId>
            <version>1.6.2</version>
        </dependency>

        <dependency>
            <groupId>com.cite</groupId>
            <artifactId>jsonpath</artifactId>
            <version>1.0</version>
        </dependency>

        <dependency>
            <groupId>com.googlecode.json-simple</groupId>
            <artifactId>json-simple</artifactId>
            <version>1.1</version>
        </dependency>

 

       其中有一个特别的类处理时有个陷阱，JavaScript组件调用时需要，会有一个异常java.lang.NoClassDefFoundError: org/mozilla/javascript/JavaScriptException，从错误很明显可以知道是缺少jar包，两种解决途径确定是那个包，在kettle的安装路径搜索javascript，会发现存在的包并没有包含JavaScriptException类，然后可以去maven仓库搜索，找到的结果是，下面这个包：

dependency>
     <groupId>org.mozilla</groupId>
      <artifactId>javascript</artifactId>
      <version>1.7.2</version>
</dependency>

      重新构建项目，然后测试，可以发现ETL是可以执行了的，这里就是坑所在了，看似好了，其实并没有真的好了，在JavaScript组件中如果有JSON和String的转换操作时，代码就会报错：

Caused by: org.mozilla.javascript.EcmaError: ReferenceError: "JSON" is not defined. (script#4)
    at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3654)
    at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3632)
    at org.mozilla.javascript.ScriptRuntime.notFoundError(ScriptRuntime.java:3717)
    at org.mozilla.javascript.ScriptRuntime.name(ScriptRuntime.java:1692)
    at org.mozilla.javascript.gen.c1._c0(script:4)
    at org.mozilla.javascript.gen.c1.call(script)
    at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:398)
    at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3065)
    at org.mozilla.javascript.gen.c1.call(script)
    at org.mozilla.javascript.gen.c1.exec(script)
    at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.addValues(ScriptValuesMod.java:387)

        是不是很无解，ETL在kettle工具中是可以执行的，代码中调用就会报错，其实问题还是出自JavaScript包引入有问题，再次排查kettle安装路径会发现，lib下面有一个js-1.7R3.jar的，命名真的很出奇啊，然后再maven中央仓库中搜索发现并没有这个版本的，本地长传引入重新执行OK了！

四、java代码组件使用分享

     组件的使用就贴一段数据解密的代码吧，大家自己体会，但有一点要注意哈，组件中使用的类全部需要import，缺失任何一个都会有下面类似的错误：

       java代码组件如下：

import java.security.Key;
import java.security.KeyFactory;
import java.security.PrivateKey;
import java.security.interfaces.RSAKey;
import java.security.interfaces.RSAPrivateKey;
import java.security.interfaces.RSAPublicKey;
import java.security.spec.PKCS8EncodedKeySpec;
import java.security.spec.X509EncodedKeySpec;
import java.util.HashMap;
import java.util.Map;
import java.util.Base64;
import java.io.ByteArrayOutputStream;
import sun.misc.BASE64Decoder;
import javax.crypto.Cipher;
String resultData = null;
public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException
{
    if (first){
      first = false;
    }
    Object[] r = getRow();
    if (r == null) {
      setOutputDone();
      return false;
    }


    r = createOutputRow(r, data.outputRowMeta.size());
    String encryptionData = get(Fields.In, "encryptionData").getString(r);
    String key = get(Fields.In, "keyPrivate").getString(r);
    try {
         byte[] keyBytes = (new BASE64Decoder()).decodeBuffer(key);
        PKCS8EncodedKeySpec keySpec = new PKCS8EncodedKeySpec(keyBytes);
        KeyFactory keyFactory = KeyFactory.getInstance("RSA");
        PrivateKey privateKey = keyFactory.generatePrivate(keySpec);

        resultData = decryptByPrivateKey(encryptionData,privateKey);
    } catch (Exception e) {
        throw new KettleException(e);
    }


    get(Fields.Out, "resultData").setValue(r, resultData);
    putRow(data.outputRowMeta, r);
    return true;
}
public static String decryptByPrivateKey(String data, Key privateKey) {
    try {
         data = data.replaceAll("%2B", "+");
         data = data.replaceAll("%2F", "/");
         data = data.replaceAll("%25", "%");
         data = data.replaceAll("%3D", "=");
         byte[] bytes = Base64.getUrlDecoder().decode(data);
         return new String(decryptByPrivateKey(bytes, privateKey));
     } catch (Exception e) {
         throw new KettleException(e);
     }
}

public static byte[] decryptByPrivateKey(byte[] data, Key privateKey) throws Exception {
     try {
          Cipher cipher_d = Cipher.getInstance("RSA");
          cipher_d.init(Cipher.DECRYPT_MODE, privateKey);

          //模长
          int key_len = ((RSAKey) privateKey).getModulus().bitLength() / 8;

          ByteArrayOutputStream bout = new ByteArrayOutputStream(cipher_d.getOutputSize(data.length)); //输出缓冲区

          //分块加密
          for (int i = 0; i < (data.length + key_len - 1) / key_len; i++) {
              bout.write(cipher_d.doFinal(data, i * key_len,
              Math.min(key_len,data.length - (i * key_len))));
          }
          return bout.toByteArray();
      } catch (Exception e) {
          throw new Exception("解密失败", e);
      }
}