本日から3日間pentaho（ペンタホ）のトレーニングに参加させて頂いています。
iDempiere（アイデンピエレ）を題材にして復習しておきたいと思います。１日目はETLツールのkettle(ケトル)が中心でした。
Kettle(ケトル)とは
Kettleとは、Kettle Extraction Transformation Transportation & Loading Environment の略だそうです。
Kettleは次のコンポーネントから成り立っています。
- Spoon(スプーン)…ETLをモデリングするGUIツール。Kettleの中心コンポーネント。
- Pan(パン)…SpoonでモデリングしたTransformationを実行するコマンドラインツール。
- Kitchen(キッチン)…SpoonでモデリングしたJobを実行するコマンドラインツール。
- Carte(カルテ)…Webサーバーとして動作し、リモート(サーバー側)でTransformationやJobを実行します。
Kettle(ケトル)のインストール
kettle単体のインストールは非常に簡単です。ダウンロードして、解凍するだけです。
2013年6月25日現在の安定版（スタッブルバージョンは）4.4のようです。pdi-ce-4.4.0-stable.zipをダウンロードし、インストール先に解凍します。私はCドライブの直下にpentahoフォルダを作成し、その中に解凍しました。
※pdi は "Pentaho Data Integration"の略で"kettle"と意味合い的には同じようです。
Kettle(ケトル)の起動
ダウンロードしたpdi-ce-4.4.0-stable.zipを解凍すると、解凍先に"data-integration"とうフォルダが作成されるので、その中のSpoon.batをダブルクリックして起動させます。起動には少々時間がかかります。
はじめてKettle(Spoon)を起動すると、下記のRepository Connectionというウィンドウが表示されます。何も知らなとこの辺で腰が引けますね！。とりあえずはじめのうちはファイルを保存する場所をあらかじめ設定しておく程度の理解でよいかと思います。実際にはKettleで作成したファイルのバージョン管理もしてくれるみたいです。
(プラス)ボタンをおして、リポジトリ（ファイルの保管場所）を設定していきます。プラスボタンを押すと下記のポップアップウィンドウが表示されるので"Kettole file repository:This is a repository stored in a file in a certain floder"を選択して、OKボタンを押します。
保存先のデイレクトリを"Base direcotory"に設定し、IDとNameフィールドを埋めます。そしてOKボタンを押します。
Repository Connectionに１つ追加されました。
追加されたRepositoryを選択してOKボタンを押します。
初期設定では今日のヒントが表示されるので、"閉じる"を押してスルーしておきます。
これで、Kettle(Spoon)が無事表示されました。
データ変換の起動
データ変換(Transformation)を起動するために、次の２つの方法があります。
ファイルメニューからの起動
ファイル -> 新規作成 -> データ変換
ツールバーのアイコンからの起動
新規作成ボタンを押し"Transformaition"を選択します。
データ変換が作成できるようになります。
試しに変換してみる：DB→Excel
ETLの環境構築ができたので、試にiDempiere（アイデンピエレ）のDBのC_Invoiceテーブルのデータをエクセルに出力させてみたいと思います。
Table input ステップ
iDempiere(アイデンピエレ)のデータベースをデータソースとする場合、Talbe input ステップを使用します。左側にあるステップのメニュー("パレットエリア")から"入力 -> Table input"をドラッグ＆ドロップでデータ変換の設定を行う"ロジック定義エリア"に配置します。
Table inputアイコンをダブルクリックするとテーブル入力ウィンドウが表示され、DBへのアクセス設定を行う事ができます。
新たにDB接続を設定する場合は"新規作成"ボタンを押します。既存のDB接続設定を更新する場合には"編集"ボタンを押します。そうするとDatebase Connectionウィンドウが表示され、DBの接続情報を設定する事ができます。
接続の設定が正しいか、どうかは"テスト接続"ボタンを押すと確認する事ができます。
テーブル入力ウィンドウの"SQL選択”ボタンを押すと、Datebase Exploreが起動しデータを取得するテーブルやビューを選択する事ができます。
Datebase Explorerから、データを取得するテーブルもしくはビューを選択します。
データを取得するテーブルかビューを選択すると、次の確認ポップアップウィンドウが表示されますので"はい"を押してSQLを追加してもらいましょう！！
そうするとSQLステートメントにデータを取得するためのSQLを自動で書いてくれます。”プレビュー”ボタンをおすと、実際の取得データを確認する事ができます。これでよければ"OK"ボタンを押します。
これでiDempiereのテーブルからデータを抽出する事ができます。
補足１：PostgreSQL以外のデータベースに接続する場合
PostgreSQLへの接続に関してはpentaho内にあらかじめJDBCが用意されているようなのですが、それ以外のDBについては、自分でJDBCを用意する必要があるそうです（大人の事情があるようです）。例えば、MySQLなどは自分でMySQLのJDBCを用意する必要があるとの事です。
トレーニングでは、"mysql-connector-java-3.1.14-bin.jar"を次のフォルダにコピーするように言われました。
- BI Server: /pentaho/server/biserver-ee/tomcat/lib/
- Enterprise Console: /pentaho/server/enterprise-console/jdbc/
- Data Integration Server: /pentaho/server/data-integration-server/tomcat/webapps/pentaho-di/WEB-INF/lib
- Data Integration client: /pentaho/design-tools/data-integration/libext/JDBC/
- Report Designer: /pentaho/design-tools/report-designer/lib/jdbc/
- Schema Workbench: /pentaho/design-tooles/schema-workbench/drivers/
- Aggregation Designer: /pentaho/design-tools/agg-designer/drivers/
- Metadata Editor: /pentaho/design-tools/metadata-editor/libext/JDBC/
※上記はトレーニング環境のパスになりますので、環境によって読み替えて参考にして下さい。
※参考：オープンソースBIのPentaho(ペンタホ)ブログ：JDBCドライバの追加(2013年3月25日)
補足２：Pentaho各クライアントソフト起動時の-Dfile.encoding設定
ここで必要となる操作ではありませんが、補足説明ということでまとめて記載しておきます。Pentahoの各クライアントの起動ファイルには-Dfile.encoding="UTF-8"と指定しておかないと、レポートのパブリッシュ時などに思わぬエラーが発生するそうです。
-Dfile.encoding="UTF-8"の記述を追記する必要があるファイルは下記のファイルだそうです。
\pentaho\design-tools\data-integration\Spoon.bat
\pentaho\design-tools\metadata-editor\metadata-editor.bat
\pentaho\design-tools\report-designer\report-designer.bat
\pentaho\design-tools\schema-workbench\workbench.bat
"JAVA_OPTION"のパス指定の所で、xmxやMaxPermSizeの直後に追記します。
※Linux環境の場合は拡張子は".bat"ではなく".sh"になります。
※上記はトレーニング環境のパスですので、環境によって読み替えて下さい。
Spoon.batの例
REM ******************************************************************
REM ** Set java runtime options **
REM ** Change 512m to higher values in case you run out of memory **
REM ** or set the PENTAHO_DI_JAVA_OPTIONS environment variable **
REM ******************************************************************
if "%PENTAHO_DI_JAVA_OPTIONS%"=="" set PENTAHO_DI_JAVA_OPTIONS="-Xmx512m" "-XX:MaxPermSize=256m" -Dfile.encoding="UTF-8"
set OPT=%PENTAHO_DI_JAVA_OPTIONS% "-Djava.library.path=%LIBSPATH%" "-DKETTLE_HOME=%KETTLE_HOME%" "-DKETTLE_REPOSITORY=%KETTLE_REPOSITORY%" "-DKETTLE_USER=%KETTLE_USER%" "-DKETTLE_PASSWORD=%KETTLE_PASSWORD%" "-DKETTLE_PLUGIN_PACKAGES=%KETTLE_PLUGIN_PACKAGES%" "-DKETTLE_LOG_SIZE_LIMIT=%KETTLE_LOG_SIZE_LIMIT%"
REM ***************
REM ** Run... **
REM ***************
@echo onstart "Spoon" "%_PENTAHO_JAVA%" %OPT% -jar launcher\launcher.jar -lib ..\%LIBSPATH% %_cmdline%
metadate-editor.batの例
REM ******************************************************************
REM ** Set java runtime options **
REM ** Change 128m to higher values in case you run out of memory. **
REM ******************************************************************
set OPT=-Xmx256m -Dfile.encoding="UTF-8" -cp %CLASSPATH% -Djava.library.path=%LIBSPATH%
report-designer.batの例
@echo off
@REM
@REM WARNING: Pentaho Report Designer needs JDK 1.6 or newer to run.
@REM
setlocal
cd /D %~dp0
set PENTAHO_JAVA=javaw
call "%~dp0set-pentaho-env.bat"
set OPT="-XX:MaxPermSize=512m" "-Xmx512M" -Dfile.encoding="UTF-8"
if not "%PENTAHO_INSTALLED_LICENSE_PATH%" == "" goto setLicenseParameter
goto skipToStartup
workbench.batの例
set PENTAHO_JAVA=java
call "%~dp0set-pentaho-env.bat"
"%_PENTAHO_JAVA%" -Xms100m -Xmx500m -Dfile.encoding="UTF-8" -cp "%CP%" -Dlog4j.configuration=file:///%ROOT%\.schemaWorkbench\log4j.xml mondrian.gui.Workbench
rem End workbench.bat
補足3:WindowsのUTF-8対応
Windowsのレジストリの以下のキーOptionにも-Dfile.encoding="UTF-8"を記述しておいて方が良いそうです。
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Apache Software Foundation\Procrun2.0\pentahobaserver\Parameters\Java
※WindowsXPの場合は、"Wow6432Node"はないそうです。
補足４:MySQLのUTF-8対応
MySQLを使用する場合には、次のUTF-8対応が必要だそうです。
\mysql\bin\my.iniに以下の内容を記述するそうです。
[client]
default-character=utf8
[mysqld]
character-set-server=utf8
skip-character-set-client-handshake
Microsoft Excle Outputステップ
エクセルにアウトプットする場合は、Microsoft Excel Outputステップを選択します。左側のパレットエリアから、ロジック定義エリアへドラッグ&ドロップします。
そうしたら、ロジック定義エリアの"DB input" のアイコンを"Shift"キーを押しながら"Microsoft Excel Output"までドラッグしていきます。そうすると２つのアイコンが矢印の線でつながります。
Microsoft Excel Outputアイコンをダブルクリックして、エクセルへの出力の設定を行っていきます。
- ファイル名→ファイルの出力先とファイル名を指定します。
- ファイル名に日付を含む→上記のファイル名に日付を加えたい場合にONにします。
- ファイル名に時刻を含む→上記のファイル名に時刻を加えたい場合にONにします。
Excel出力フィールドタブでは、”フィールドを取得”ボタンを押すとTable inputの情報を読み取って、出力するエクセルのフィールド一覧を自動作成します。このなかから必要な情報だけを残して”OK”ボタンをおします。
これで一通りの設定が完了したので"実行"ボタンを押します。
実行ボタンをおすと、データ変換の"実行ウィンドウ"が表示されますので、デフォルトの状態で再度”実行”ボタンをおします。
これで、先にしたした場所に、エクセルファイルが出来がっていると思います。エクセルファイルが正しく作成できたのを確認したら、このTransformationをファイルとして保存しておきます。拡張子が「.ktr」で保存されます。
JOBを作成してみる
それでは今作った"Transformaition"をJOB化したいと思います。JOBを作成するには、メニューバーからファイル -> 新規作成 -> ジョブを選択います。
もしくは、新規作成ボタンよりJobを選択します。
ロジック定義エリアの左端に”START”アイコンを置き、右端に"Success"アイコンを置きます。そして間に、先ほど作成したTransoformationを設置するための”Transoformation”アイコンをおき、この３つのアイコンを矢印でつなげます。
STARTアイコンをクリックすると”Job Scheduling”ウィンドウが起動し、jobのスケジューリング設定を行う事ができます。
Transformationアイコンをクリックすると、先ほど作成したTransformationのファイルを選択する事ができます。
ここまでできたら、保存ボタンをおして保存します。拡張子が「.kjb」というファイルができます。
これで一通りの設定が完了したので"実行"ボタンを押して、Jobを実行してみます。
ジョブを実行して、エクセルファイルが出来ていれば成功です。
JOBをバッチ化する(.batファイルを作成する)
kettle(ケトル)には、Kitchen(キッチン)というジョブの実行の仕組みが用意されています。
Kitchenでジョブを実行するためには次のようなバッチファイル(.bat)を作成します。
kitchen.batのpath /file ".kjbファイルのパス" /level:Basic > ログファイルのパスとログファイル
(例）Kitchen.batと同じフォルダにバッチファイルを作成した場合
Kitchen.bat /file "C:\pentaho\data-integration\ws\DBtoExcel.kjb" /level:Basic > C:\pentaho\ktr.log
出来上がったバッチファイルを実行すると、JOBがキックされ、エクセルファイルが作成されます。
ジョブのファイルである.kjbファイルには、.ktrファイルの保管場所の情報が保持されているようなので、.ktrファイルが想定の場所にないとエラーになったります。
JOBをスケジュール実行する
pentahoの有償版となるエンタープライズエディションには、JOBをスケジューリングする仕組みがあるそうですが、必ずしもその仕組みを使わないとスケジューリングできないわけではありません。
各種、スケジュール実行ソフトを使用する事ができるようです。Linuxであればcron(クーロン)などよく使用するとのことです。
Kettleの機能でいえば、このスケジューリングの機能があるかどうかが、エンタープライズエディション(EE)とコミュニティーエディション(CE)の大きな違いとの事です。
ですので、スケジューラーさえ自分で使いやすいものを用意できれば、ETLについては無償版で十分な様子です。
SpoonでもできるOLAP分析
最後に、私がKettleで一番びっくりした機能を紹介致します。KettleはETLツールなので、データを抽出して、変換して、インポートするための仕組みがたくさん提供されているのは、ツールの性格上驚きはしないのですが、Spoon上でOLAP分析やレポートも作成できる事に驚きました。特にOLAP分析は、pentaho エンタープライズエディションで（有料版）、を使ってWeb上で行うものだと思っていので、それとほぼ同じ事がETLツールのSpoonで実現できる事にびっくりしました。
Spoonは有料版も無料版も機能には違いは無いとの事で、無料版でクライアント側とは言え簡単にOLAP分析できるのはうれしいですね。SSHのトンネリング機能を使えば、セキュアにクラウド上のDBにアクセスしOLAP分析する事もできるはずです。
クライアントツールなので権限管理のような事まではできないと思いますが、アイディアしだいで上手に活用できれば面白いツールなのではないかと思います。
SSHのトンネリング設定
LANやVPN上にあるデータベースなら、そのままJDBCでアクセスしても問題ないかと思いますが、ここはクラウド上にあるデータベースにアクセスする想定でSSHのトンネリングを使用してセキュリティーを確保して行きたいと思います。
AWSのEC2上にあるiDempiere（アイデンピエレ）のデータベースPostgreSQL（ポート:5432）にSSHクライアントソフトPuTTY(パティ)を使ってトンネリングしてアクセスして行きたいと思います。
源ポートでアクセスポートを設定します。ここでは仮に15432とします。転送先のポートにSSHで接続したサーバーの5432ポートを設定します。設定が完了したら"追加"ボタンを押して、保存します。
設定を保存したら、SSHでAWS-EC2インスタンスにアクセスします。
Kettle-SpoonからAWS-EC2インスタンスのDB(PostgreSQL）にアクセスする。
Spoonを起動し、Table Outputステップを”ロジック定義エリア”に１つ配置します。
Table Outputアイコンをダブルクリックして、表示される”テーブル出力”の設定ウィンドウで、新規作成ボタンを押して、"Database Connection"設定を行います。トンネリングの設定をしていますので、localhostの15432ポートに接続すれば、AWS-EC2インスタンスの5432ポートに接続できるはずです。
確認のためにテスト接続します。”テスト接続”ボタンを押すと接続できた事が確認できます。
テストとしてC_Invoiceのデータをピボットテーブルで分析して行きたいと思います。
”ロジック定義エリア”のTable Outputアイコンを右クリックし、表示されるメニューの中からModel(モデル)を選択します。
そうすると、ModelerタブのAnalysisタブが立ち上ってきます。この画面で、分析軸となるDimensionsと、測定数値となるMeasuresを設定します。設定は、左側にあるC_InvoiceのカラムをDimensionsとMeasuresにドロップ＆ドラッグするだけです。
同じ方法で、Analysisタブの横のReportingタブも行っておきます。Reportingタブも設定しておかないとOLAPできないようになっている様子です。
簡単な例として組織と取引先を分析軸として総合計を見ていきたいと思います。Reportingタブでも同様の設定を行っていきます。
設定が一通りできたら、Analyzerの"GO"ボタンを押します。
すると、OLAP分析でぐりぐりできる画面が立ち上ってきます。
ちょっと試しに、列ラベルに取引先、行ヘッダーに組織を設定してみると次のような感じ表示されます。手間を惜しんで組織と取引先はIDで表示しているのでわかりずらいと思いますが、頑張ってもうちょっと手を加えてちゃんと名称で出すようにすれば、とても綺麗な分析画面になるかなと思います。
こんな感じでデータベースから直でOLAP分析が無料でできるのでびっくりしました。データをテーブルからCSVでダウンロードしてエクセルのピボットテーブルで分析するのとやっている事は同じですが、モデルとか組み立てて保存しておけるので、繰り返し使えて使いまわしが簡単にできるという点で良いかなと思います。