kettle使用_ETL工具(kettle)使用系列(二)

## 本章介紹一些kettle技巧和相關的問題解決方案

kettle資料抽取中文亂碼(一)

==spoon.bak檔案找到

if "%PENTAHO_DI_JAVA_OPTIONS%"=="" set PENTAHO_DI_JAVA_OPTIONS="-Xms1024m" "-Xmx2048m" "-XX:MaxPermSize=256m"

這個後面添加 `"-Dfile.encoding=UTF-8"==

kettle進行中文亂碼問題(二)

在kettle腳本相關地方增加一個java元件處理腳本，java元件代碼如下

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 sickname = get(Fields.In, "SICKNAME").getString(r);
  if(sickname != null && !"".equals(sickname)) {
    try {
            sickname = new String(sickname.getBytes("ISO-8859-1"), "GBK");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

  }
  get(Fields.Out, "SICKNAME").setValue(r, sickname);

  putRow(data.outputRowMeta, r);

  return true;
}
           

詳細腳本:

[腳本進行中文亂碼](http://note.youdao.com/noteshare?id=c0e5290d9830f8b3b87c03a3b46b6497)

cmd運作test.kjb腳本

E:
cd E:softwareserverssqlkettle7.1.0data-integration
Kitchen /file:E:softwareserverssqlkettle7.1.0datadbjob.kjb /level:Basic>C:UsersthinkpadDesktopkettlekettle-job.log
           

性别1,2轉換為男女,用javaScript腳本

//Script here
//loop through the actual row
for (var i=0;i<getInputRowMeta().size();i++) {
  // Grab the metadata for this value
  var valueMeta = getInputRowMeta().getValueMeta(i);
  if (valueMeta.getName().equals("SEX")) {
    row[i]=replace(row[i],'1','男');
    row[i]=replace(row[i],'2','女');
  }
}
           

date類型和string類型互相轉換,格式化date

==用字段選擇:如下圖==