Pentaho Spoon

Overview

Pentaho Spoon は ETL ツールであり、企業内で使用する様々なデータ(Excel, DB, CSVなど多数)に対して、以下を行うことができる。

Extract: Excel, DB, CSVなどから「データを抽出」
Transform: 抽出したデータを必要に応じて変換・加工
Load: 変換・加工したデータをロード(Excel, DB, CSVなどに出力)

このツールを利用することで「データ移行」や「集計表の作成」などの作業が簡単になることがある。

例えば、「データ移行」においてマスタテーブルの移行などは「データ変換ロジック」があまりないため、 Pentaho Spoon を利用するとかなり高速にデータ移行プログラムが作成できる。しかしながら「トランザクションデータ」のデータ移行などで複雑な「データ変換ロジック」が必要になる場合は、 Pentaho Spoon は適さない。Pentaho Spoon で「データ変換ロジック」を実装することは可能ではあるが、デバッグが難しく、結果として実装に長時間掛かってしまうことがある。

Pentaho Spoon が向いている処理

データ移行
- マスタテーブルのデータ移行
- 旧テーブルと新テーブルであまり変更がない場合のデータ移行
  - カラム名変更
  - データの単純な分割
集計
- DBテーブルから集計用のselect文を使ってデータを抽出し、報告用のExcelに出力

Pentaho Spoon が向いていない処理

データ移行
- 複雑なデータ変換ロジックを要するデータ移行
- ピボット分析(行を列に変換)を伴うデータ移行

Install

PentahoBIスイートコミュニティエディション（無償版）

Run

以下をダブルクリックすると Pentaho Spoon を起動できる。

data-integration\Spoon.bat

メモリ不足で起動しない場合がある。その場合は上記.batファイルをテキストエディタで開き、以下の部分の数値を下げると良い。

PENTAHO_DI_JAVA_OPTIONS="-Xms1024m" "-Xmx2048m"

Docs

Welcome to Pentaho Documentation
Pentaho Data Integration Steps
- 「テーブル入力」や「Excel出力」などのコンポーネントを Pentaho Spoon では「ステップ」と呼ぶ

Samples

以下に公式のサンプルがある。

data-integration\samples\transformations

Spoonの [ファイル(F)]→[開く(O)…] から拡張子 .ktr のファイルを指定することで、サンプルプログラムを開くことができる。

Case Study

DBテーブルの内容をExcelに出力する

完成後のデータ変換ファイル (Pentaho Spoonで開くことができる。)

完成後のイメージ

Step1: データ変換の新規作成

Spoonの [ファイル(F)]->[新規(N)]->[データ変換(T)] を選択する。

Step2: 「テーブル入力」ステップの利用

Spoonの左側にある「デザイン」タブを選択し、「入力」を選択して展開し、「テーブル入力」をSpoon中央に Drag&Drop する。

Step3: データソースを設定

*今回はPentahoが用意しているサンプルDBに接続する。この設定を適切に行えば、OracleやPostgreSQLなど様々なDBに接続できる。

先ほど Drag&Drop した「テーブル入力」をダブルクリックして、「データソース名」付近にある「新規作成(N)...」をクリック。
以下のように設定する。

接続名: testdb
接続タイプ: Generic database
カスタム接続URL: jdbc:h2:samples/db/sampledb
カスタムドライバークラス名: org.h2.Driver
ユーザー名: PENTAHO_USER
パスワード: PASSWORD

「テスト」をクリックして「成功」が表示されれば成功。
「OK」をクリックしてデータソースの設定は完了。

Step4: select文を設定する

*select文は接続するDBが解釈できるものであれば何でも発行できる。

引き続き「テーブル入力」の設定画面で「SQLの選択(S)」をクリック。
[testdb]->[テーブル]->[CUTOMERS] をダブルクリック。
「フィールド名をSQLに追加しますか?」で「OK」をクリック。
「プレビュー」をクリックしてデータが表示されれば成功。
「テーブル入力」の設定画面で「OK」をクリックしselect文の設定は完了。

Step5: 「Excel出力」ステップの利用

Spoonの左側にある「デザイン」タブを選択し、「出力」を選択して展開し、「Excel出力」をSpoon中央に Drag&Drop する。
「テーブル入力」ステップを「Shift + 左クリック」して「Excel出力」とコネクション(矢印)を設定する。
「Excel出力」をダブルクリックして「ファイル名」に出力したいExcelの場所を適当に設定する。（「参照」から指定すると簡単。)

Step6: データ変換の保存

[ファイル(F)]->[保存(S)] で適当な場所に保存する。

Step7: データ変換の実行

[アクション(A)]->[実行(R)] を選択、「実行(L)」ボタンをクリック。
「Excel出力」で設定した場所にExcelが出力されていれば成功。

データ移行で旧テーブルと新テーブルのカラム名が違う場合

Case Study 「DBテーブルの内容をExcelに出力する」の流れで出力先を「Excel出力」ではなく「テーブル出力」ステップ(insert文)または「挿入/更新」ステップ(insert文 or update文)に変更すれば良い。

「テーブル入力」で発行するselect文で以下のように AS 構文を利用すると、新テーブルのカラム名に変更することができる。

select 旧カラム名 as 新カラム名 from TABLE;

Spoonで用意されている「選択/名前変更」ステップでカラム名を変更することもできるが、上記の方法が最も手っ取り早い。

データ移行で旧テーブルと新テーブルのカラム名が違う場合(「選択/名前変更」ステップ利用版)

完成後のイメージ

H2データベースなどはAS句をサポートしていないため、SQLでカラム名の変更ができない。また、「Excel入力」や「CSV入力」などどもカラム名の変更は当然SQLで行うことはできない。

そのような場合はSpoonで用意されている「選択/名前変更」ステップでカラム名を変更することができる。

Spoonの左側にある「デザイン」タブを選択し、「変換」を選択して展開し、「選択/名前変更」をSpoon中央に Drag&Drop する。
「選択/名前変更」ステップを「テーブル入力」と「Excel出力」との結合線上に Drag&Drop する。
「結合を分割しますか?」で「はい」を選択。
「選択/名前変更」ステップをダブルクリックして、「フィールドの選択」ボタンをクリック。
変更したいカラム名を「変更名称」列に入力する。
「OK」ボタンをクリック。

空文字を空文字のまま扱う

Pentahoはデフォルトではデータ入力時に「空文字」があると、データ出力時に「NULL」として扱う。

出力先の制約(NOT NULLなど)により不都合がある場合は、以下の手順でNULLへの変換をしないように設定できる。

Spoonの[編集]→[kettle.propertiesファイル編集]を開く。
「KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL=y」を設定。

マスキング/Sanitization

「テーブル入力」時のselect文でmd5()などのチェックサム関数を入れると簡単にマスキングできる
Pentahoの「チェックサムを追加」ステップを利用してマスキングすることができる
マスキング用の変換テーブルを用意し「テーブル入力」時のselect文でjoinしてマスキングすることができる
1. select CONV.masking_name as name FROM ORIGINAL_TABLE left outer join CONV ORIGINAL_TABLE.id = CONV.id;」

目次