【Pentaho Lab】トレーニング1日目(Kettle:ケトル)の復習

 本日から3日間pentahoペンタホ)のトレーニングに参加させて頂いています。

iDempiereアイデンピエレ)を題材にして復習しておきたいと思います。1日目は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(ケトル)のインストール

 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をダブルクリックして起動させます。起動には少々時間がかかります。

pentaho ケトルのウェルカムスクリーン
pentaho ケトルのウェルカムスクリーン

 はじめてKettle(Spoon)を起動すると、下記のRepository Connectionというウィンドウが表示されます。何も知らなとこの辺で腰が引けますね!。とりあえずはじめのうちはファイルを保存する場所をあらかじめ設定しておく程度の理解でよいかと思います。実際にはKettleで作成したファイルのバージョン管理もしてくれるみたいです。

Repository Connection
Repository Connection

(プラス)ボタンをおして、リポジトリ(ファイルの保管場所)を設定していきます。プラスボタンを押すと下記のポップアップウィンドウが表示されるので"Kettole file repository:This is a repository stored in a file in a certain floder"を選択して、OKボタンを押します。

Select the repository type
Select the repository type

保存先のデイレクトリを"Base direcotory"に設定し、IDとNameフィールドを埋めます。そしてOKボタンを押します。

File repository settings
File repository settings

 Repository Connectionに1つ追加されました。

Repository Conneciton
Repository Conneciton

追加されたRepositoryを選択してOKボタンを押します。

今日のヒント
今日のヒント

 初期設定では今日のヒントが表示されるので、"閉じる"を押してスルーしておきます。

 これで、Kettle(Spoon)が無事表示されました。

Pentaho-Kettle-Spoon
Pentaho-Kettle-Spoon

データ変換の起動

 データ変換(Transformation)を起動するために、次の2つの方法があります。

ファイルメニューからの起動

ファイル -> 新規作成 -> データ変換

ファイルメニューからの起動
ファイルメニューからの起動

ツールバーのアイコンからの起動

新規作成ボタンを押し"Transformaition"を選択します。

ツールバーのアイコンからの起動
ツールバーのアイコンからの起動

データ変換が作成できるようになります。

試しに変換してみる:DB→Excel

 ETLの環境構築ができたので、試にiDempiereアイデンピエレ)のDBのC_Invoiceテーブルのデータをエクセルに出力させてみたいと思います。

Table input ステップ

Table input ステップ

iDempiere(アイデンピエレ)のデータベースをデータソースとする場合、Talbe input ステップを使用します。左側にあるステップのメニュー("パレットエリア")から"入力 -> Table input"をドラッグ&ドロップでデータ変換の設定を行う"ロジック定義エリア"に配置します。

 Table inputアイコンをダブルクリックするとテーブル入力ウィンドウが表示され、DBへのアクセス設定を行う事ができます。

テーブル入力
テーブル入力

 新たにDB接続を設定する場合は"新規作成"ボタンを押します。既存のDB接続設定を更新する場合には"編集"ボタンを押します。そうするとDatebase Connectionウィンドウが表示され、DBの接続情報を設定する事ができます。

 接続の設定が正しいか、どうかは"テスト接続"ボタンを押すと確認する事ができます。

 テーブル入力ウィンドウの"SQL選択”ボタンを押すと、Datebase Exploreが起動しデータを取得するテーブルやビューを選択する事ができます。

テーブル入力
テーブル入力

 Datebase Explorerから、データを取得するテーブルもしくはビューを選択します。

Database Explorer
Database Explorer

 データを取得するテーブルかビューを選択すると、次の確認ポップアップウィンドウが表示されますので"はい"を押してSQLを追加してもらいましょう!!

 そうするとSQLステートメントにデータを取得するためのSQLを自動で書いてくれます。”プレビュー”ボタンをおすと、実際の取得データを確認する事ができます。これでよければ"OK"ボタンを押します。

テーブル入力
テーブル入力

 これでiDempiereのテーブルからデータを抽出する事ができます。

 

補足1: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日)

 

 

補足2: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"はないそうです。

 

 

補足4: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"までドラッグしていきます。そうすると2つのアイコンが矢印の線でつながります。

 Microsoft Excel Outputアイコンをダブルクリックして、エクセルへの出力の設定を行っていきます。

  • ファイル名→ファイルの出力先とファイル名を指定します。
  • ファイル名に日付を含む→上記のファイル名に日付を加えたい場合にONにします。
  • ファイル名に時刻を含む→上記のファイル名に時刻を加えたい場合にONにします。
Excel出力 ファイルタブ
Excel出力 ファイルタブ

 Excel出力フィールドタブでは、”フィールドを取得”ボタンを押すとTable inputの情報を読み取って、出力するエクセルのフィールド一覧を自動作成します。このなかから必要な情報だけを残して”OK”ボタンをおします。

Excel出力 フィールドタブ
Excel出力 フィールドタブ

これで一通りの設定が完了したので"実行"ボタンを押します。

 実行ボタンをおすと、データ変換の"実行ウィンドウ"が表示されますので、デフォルトの状態で再度”実行”ボタンをおします。

 これで、先にしたした場所に、エクセルファイルが出来がっていると思います。エクセルファイルが正しく作成できたのを確認したら、このTransformationをファイルとして保存しておきます。拡張子が「.ktr」で保存されます。

JOBを作成してみる

 それでは今作った"Transformaition"をJOB化したいと思います。JOBを作成するには、メニューバーからファイル -> 新規作成 -> ジョブを選択います。

 もしくは、新規作成ボタンよりJobを選択します。

ロジック定義エリアの左端に”START”アイコンを置き、右端に"Success"アイコンを置きます。そして間に、先ほど作成したTransoformationを設置するための”Transoformation”アイコンをおき、この3つのアイコンを矢印でつなげます。

 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(パティ)を使ってトンネリングしてアクセスして行きたいと思います。

PuTTY
PuTTY

 源ポートでアクセスポートを設定します。ここでは仮に15432とします。転送先のポートにSSHで接続したサーバーの5432ポートを設定します。設定が完了したら"追加"ボタンを押して、保存します。

 設定を保存したら、SSHでAWS-EC2インスタンスにアクセスします。

Kettle-SpoonからAWS-EC2インスタンスのDB(PostgreSQL)にアクセスする。

 Spoonを起動し、Table Outputステップを”ロジック定義エリア”に1つ配置します。

 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でダウンロードしてエクセルのピボットテーブルで分析するのとやっている事は同じですが、モデルとか組み立てて保存しておけるので、繰り返し使えて使いまわしが簡単にできるという点で良いかなと思います。

参考サイト