Pentaho Data Integration Spoonステップ逆引きまとめ

[前回](https://qiita.com/ch7821/items/166e566a4b017604b118)で導入手順をまとめたので、今回は実際の使い方をまとめ。

# 基礎用語概要
基本的にPentaho Data Integration（PDI）でできることは、色々なデータ（データソースや型）を入力・統合し変換して出力すること（ETL）であり、データの可視化（グラフ化等）もできる。
いろいろと用語があるので、必要最低限を簡単にまとめておく。

<dl>
        <dt>ストリーム</dt>
        <dd>PDIでは基本的にデータを入力してそれを次々に加工していく（ETL:Extract Transform Load）。このデータのフローのこと。</dd>
        <dt>ステップ</dt>
        <dd>データを扱う最小の単位。入力（Extract）だったり、変換（Transform）だったり、出力（Load）だったり。PDI上の操作としては、「デザイン」タブのメニューから実行したいETL動作を選択してメインエリアに配置する。</dd>
        <dt>ホップ</dt>
        <dd>データのフローを表す。ステップ間をホップでつないでデータを受け渡してストリームを構成する。PDI上の操作としては、メインエリアのアイコン同士を[Shift]+マウスドラッグで繋ぐ、出力側アイコンを選択しておいてデザインからダブルクリックでメインエリアに配置する等複数の方法がある（Windows等で３つボタンマウスを使用していれば、真ん中ボタンのドラッグでも可）。</dd>
        <dt>データ変換</dt>
        <dd>PDIで作成するファイルの種類というと語弊があるが、Wordでいう文書。ストリームが主役。</dd>
        <dt>ジョブ</dt>
        <dd>これもPDIで作成できる文書の種類。こちらはその名の通りなにがしかの処理を定義しておいて、Pentaho-Serverなどにスケジュール実行させたりする。</dd>
        <dt>
        </dt>
</dl>

# ステップ逆引き
やりたいことからどんなステップを使えば良いかまとめ（随時更新）。

## 指定したデータフィールド（固定値）をレコード生成する
「入力」ー「データグリッド」
<img width="1435" alt="スクリーンショット 2018-05-25 21.00.43.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/078af60a-39ce-2805-c6b3-a9c2e6c28282.png">
<img width="1435" alt="スクリーンショット 2018-05-25 21.01.00.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/9937b2ed-2ea0-b9f8-23df-d8cb47990fb3.png">
データは１レコードずつ任意の値を指定可能。
<img width="1463" alt="スクリーンショット 2018-05-25 21.01.17.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/9b5bd0e1-f3ef-4aa8-7e11-4f7c963653af.png">

## 指定したデータフィールド（固定値）を指定レコード数生成する
「入力」ー「行生成」<img width="1074" alt="スクリーンショット 2018-05-23 21.43.23.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/62b6fb03-e1bd-1624-26d3-7f03c26ea927.png">
<img width="996" alt="スクリーンショット 2018-05-23 21.44.17.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/8920c563-d4bc-cc4a-03f4-4083f6e2db81.png">

## データ型を変換する
「変換」ー「選択/名前変更」（のメタ情報）
例として文字列"\$100,000"と"\$200,000"をそれぞれInteger型に変換して足し算（Integer変換できていること確認）

1. 「入力」ー「データグリッド」でvar1およびvar2を生成
<img width="1435" alt="スクリーンショット 2018-05-25 20.53.30.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/828bc825-0a49-8d21-e16f-1450b29e327d.png">
<img width="1435" alt="スクリーンショット 2018-05-25 20.53.34.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/2489ea33-0949-706b-23b2-b2b90b303c12.png">

2. 「変換」ー「選択/名前変更」
<img width="1435" alt="スクリーンショット 2018-05-25 20.55.23.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/1222a081-976e-8361-e253-ecfb2fe73538.png">
var1, var2ともデータタイプに「Integer」、書式に「$#,###」を指定する。

3. 「変換」ー「計算」
<img width="1441" alt="スクリーンショット 2018-05-25 20.55.57.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/9c6ad8e4-5735-4079-a86b-e0a7f2992ea6.png">
出力フィールド名に任意の名称、計算式に「A + B」、フィールドAおよびBにそれぞれvar1, var2を指定。

4. 実行結果
<img width="1463" alt="スクリーンショット 2018-05-25 20.56.31.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/d5e1384f-7035-5999-9f83-885696afcf6e.png"><img width="1463" alt="スクリーンショット 2018-05-25 20.56.35.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/289a7cb2-c721-4fbc-7559-72a08334d1b5.png"><img width="1463" alt="スクリーンショット 2018-05-25 20.56.38.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/dd138386-7ff8-0844-fd76-c5bc6d2749b7.png">
resに"300,000"が計算されている。

## 正規表現で一致パターンのみ抽出する
「スクリプト」ー「正規表現」
例として日付文字列「YYYY/MM/DD HH:MM:SS.mm」から日付の「DD」のみを取り出す場合

1. 「入力」ー「システムデータ」でシステム日付を"var"として生成
<img width="1435" alt="スクリーンショット 2018-05-25 21.28.24.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/8bca06c0-935d-f254-855f-428ef8597579.png">

2. 正規表現
<img width="1435" alt="スクリーンショット 2018-05-25 21.35.50.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/e90d29fc-8abe-b28d-2652-9789cd47df9c.png">
結果フィールドは正規表現にマッチしたらTrue（Y）、しなかったらFalse（N）が入る。キャプチャして値を新規フィールドに入れるには、「キャプチャグループにフィールドを作成」にチェックし、フィールドを定義する。キャプチャする正規表現は、一般的な後方参照表現である`(pattern)`となる（参照する部分をカッコで囲む）。

3. 実行結果
<img width="1463" alt="スクリーンショット 2018-05-25 21.36.00.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/5beaaaf8-6002-520c-d114-b8ba24aeb53a.png">
<img width="1463" alt="スクリーンショット 2018-05-25 21.36.03.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/4472d3a8-eed3-c8a3-77c8-c2cf34b85e2d.png">
resultに「Y」、dayに「25」が入った。

## テキストの繰返しデータを行列データへ変換する
「変換」ー「行フラット化」
わかりづらい表題だが、例えば以下のようなデータを行列データへ変換する。

```text:data.txt
var1: var1 value1
var2: var2 value1
var3: var3 value1

var1: var1 value2
var2: var2 value2
var3: var3 value2
```
これを、

|var1|var2|var3|
|:--:|:--:|:--:|
|var1 value1|var2 value1|var3 value1|
|var1 value2|var2 value2|var3 value2|

こうする。

1. テキストファイルを読込む

2. 行のフラット化
Field2をvar1,var2,var3としてフラット化。
<img width="1435" alt="スクリーンショット 2018-05-25 22.57.39.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/d4ed9f6c-07e8-3fb5-5f84-6a6b40449bc6.png">

3. Field1が残るので「変換」ー「選択/名前変更」で除外。
<img width="1435" alt="スクリーンショット 2018-05-25 22.58.38.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/06e38b7c-e070-468c-fbde-6488c54d2902.png">

4. 実行結果
<img width="1463" alt="スクリーンショット 2018-05-25 22.59.29.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/5aa8a993-572b-c520-a213-25ed395422ee.png"><img width="1463" alt="スクリーンショット 2018-05-25 22.59.33.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/c0631dff-2d11-71a5-aa75-2e15bb6df716.png"><img width="1463" alt="スクリーンショット 2018-05-25 22.59.37.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/fcf531aa-24a7-0447-c71c-be260d1ebd59.png">

または、次のように一旦テキストファイルを１フィールドで読込んで加工する。

1. テキストファイルを１フィールドで読込む
<img width="1643" alt="スクリーンショット 2018-05-25 22.16.46.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/0c6ad006-4636-b575-c195-f472464e8343.png">
実際はカンマ区切りだがフィールド区切り文字をタブ文字等にして１フィールドで出力させる。
<img width="1435" alt="スクリーンショット 2018-05-25 22.16.56.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/cf6a7742-a3fd-da25-93fd-dba94582b9bd.png">

2. 行のフラット化
Field1をvar1,var2,var3としてフラット化。<img width="1435" alt="スクリーンショット 2018-05-25 22.17.06.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/919baa88-27ef-583d-b9b3-74b762d69a54.png">

3. 余計な文字をカット
<img width="1435" alt="スクリーンショット 2018-05-25 22.17.14.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/35a585a5-e330-da4a-8d49-121428f74bcc.png">
先頭の"var[1-3]: "６文字はデータとして不要なので、カットする。文字列カットは終点も固定値で指定しないといけないようなので、ここに入力したよりも長いデータが入るとデータが切れてしまう。。。あまり美しくないが今の所はこれでよしとする。

4. 実行結果
<img width="1463" alt="スクリーンショット 2018-05-25 22.17.21.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/e9f74e08-b1bf-8484-d9cb-66c40d8282aa.png"><img width="1463" alt="スクリーンショット 2018-05-25 22.17.24.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/7c3a3708-7c3a-19a1-f0df-5a6bc60759c7.png"><img width="1463" alt="スクリーンショット 2018-05-25 22.17.38.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/7365ea22-6883-981d-5500-0f13f30a218f.png">

## 文字列置換
「変換」ー「文字列置換」（正規表現も使用可能）
<img width="1435" alt="スクリーンショット 2018-05-25 23.17.08.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/24a20788-a389-7322-a2e6-448faf38d3df.png">
<img width="1463" alt="スクリーンショット 2018-05-25 23.17.26.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/e0f739dc-8ff9-c674-7ede-ce4eafa7136e.png">
<img width="1463" alt="スクリーンショット 2018-05-25 23.17.30.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/c4938f37-b1fb-a8f3-2790-93e7abcb7d1a.png">

## Javaのワンライナーでデータを加工
「スクリプト」ー「ユーザー定義Javaエクスプレッション」
<img width="1435" alt="スクリーンショット 2018-05-25 23.31.33.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/e4153568-b7bc-520b-3304-08d08b763b4f.png">
<img width="1435" alt="スクリーンショット 2018-05-25 23.31.49.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/25b62563-1ce2-c5c6-6d19-0a56329e71fd.png">
<img width="1463" alt="スクリーンショット 2018-05-25 23.33.43.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/eec64dec-e54a-be9b-a913-05757b672989.png">
<img width="1463" alt="スクリーンショット 2018-05-25 23.33.49.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/48604ff1-fe2d-8837-6071-636037100332.png">
<img width="1463" alt="スクリーンショット 2018-05-25 23.34.01.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/2b4321b1-576d-3873-38c5-9f89902120eb.png">

## データをつなげる
「フロー」ー「ストリーム結合」
<img width="1435" alt="スクリーンショット 2018-05-26 06.35.34.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/53af7831-5765-2c4d-5809-4fcd1913bc9c.png">

## Excelに出力
「出力」ー「Microsoft Excel Writer」
<img width="1435" alt="スクリーンショット 2018-05-26 06.41.30.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/a2e5bf35-dd90-aa72-7889-acf3ca00a491.png">
<img width="1477" alt="スクリーンショット 2018-05-26 06.43.06.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/bf4d3281-8b34-7c02-d3f2-d4c93e145505.png">
<img width="928" alt="スクリーンショット 2018-05-26 06.48.04.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/232119ae-4c3e-1f7f-7ec1-f28639dc1078.png">

## ステップ（処理）の同期を取る
「フロー」ー「ステップブロック」
特に同一ファイルへの入出力は自動でロックしてくれないため、指定のステップが終了するまで待つステップを入れる。
<img width="1463" alt="スクリーンショット 2018-05-26 06.57.20.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/a3d34765-a39f-a025-0004-4b36f7990a2c.png">
![スクリーンショット 2018-05-26 06.57.38.png](https://qiita-image-store.s3.amazonaws.com/0/232587/d98c38b8-810c-6a57-c56d-50fd95239ee7.png)

## 特定の条件を満たすレコード以外を除去する（データのフィルタリング）
「フロー」ー「フィルター」
<img width="1435" alt="スクリーンショット 2018-05-26 07.09.16.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/2a429647-932d-32c7-5235-5b692ed415ce.png">

## DBからデータ取得
「入力」ー「テーブル入力」
<img width="1435" alt="スクリーンショット 2018-05-27 09.05.12.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/0a477b9d-7454-fdf2-75d5-57d4355e27d0.png">
SQL文を自分で書ける。「SQLの選択」から対象テーブルをGUIで選択可能。
<img width="716" alt="スクリーンショット 2018-05-27 09.06.03.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/582ff1c0-0dcd-82c1-2c96-69ec3d9b2b18.png">

## 統計分析値の算出
「統計」ー「単変量統計」
<img width="1435" alt="スクリーンショット 2018-05-27 09.15.31.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/f9b91f9a-c7df-9445-a97a-de9e0a049d1e.png">
<img width="1463" alt="スクリーンショット 2018-05-27 09.16.02.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/13c422ef-56d6-40d0-c4e6-89b796d14bbb.png">

## 数値の範囲で出力値を分ける
「変換」ー「数値範囲」
SampleDataの"ORDERDETAILS"から取得した"QUANTITYORDERED(x)"を使ってグルーピング。

|group1|group2|group3|
|:--:|:--:|:--:|
|$x<30$|$30<x, x<40$|$40<x$|

<img width="1435" alt="スクリーンショット 2018-05-27 09.18.24.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/532811b3-7a8b-3619-afd8-38445b836025.png"><img width="1463" alt="スクリーンショット 2018-05-27 09.26.01.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/674c8ba7-d30e-b85f-571d-68b831a60312.png">

## ソートする
「変換」ー「行整列」
<img width="1435" alt="スクリーンショット 2018-05-27 09.39.27.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/718c4c19-85e8-862e-6e36-949535f54240.png">
※DBから取得するデータなら`ORDER BY`句をつける方が効率的

## 複数データ行にまたがった計算（値の参照）
「統計」ー「分析クエリ」
<img width="1435" alt="スクリーンショット 2018-05-27 16.36.27.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/c7c268fc-3dfd-169a-0568-09e827b08bd0.png">
<img width="1463" alt="スクリーンショット 2018-05-27 16.37.01.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/23b93e81-0c7a-c7ac-e3c9-e0dc0eefb47b.png">
`ORDERS`テーブルから`CUSTOMERNUMBER`と`ORDERDATE`を取得してソート。各行に`LAST_ORDERDATE`フィールド（１つ後ろのレコードの`ORDERDATE`）をセット。

## 条件によってステップを分岐させる
「フロー」ー「条件分岐(Switch / Case)」
<img width="1435" alt="スクリーンショット 2018-05-27 16.52.02.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/74e5206a-0d2e-1733-647a-914f19e5ca2b.png">
乱数を生成し、ユーザー定義Javaエクスプレッションで生成された乱数が偶数なら`true`、奇数なら`false`を`EvenOdd`フィールドに出力（`num % 2 == 0 ? tree : false`）、偶数ならダミーステップ、奇数ならダミー２ステップへ。