Pentaho導入覚書

研修で初めてPentahoを触ってきたのでここにメモ。

# Edition比較
PentahoはOSSベースで、Community Edition（CE）とEnterprise Edition（EE）が存在する。後者は無論のこと有償であり、サポートがつくのと、統合インストーラでソフトウェア・スイートをいちどきにインストールできる。また、チーム開発向けのバージョニング機能等がついていたりするらしい（研修でそう聞いたがCEで実現できるかどうか未確認）。が、いずれにせよバージョニングならgitなりなんなりで代替可能、というかその方が高機能であるため、Editionの機能差異はあまり問題にならない。コア機能に差異はないので、ここではCEを使用する。
なお、Pentahoの各コンポーネントはJavaで書かれているため、大抵のOS上で動作可能（例えばpentaho-serverはTomcat+SpringなWebアプリケーションとして実装されている）。

# 前提環境
+ OS: Mac OSX[^1]
+ DIツール: Pentaho Data Integration Community Edition 8.1
+ DIサーバ: Pentaho Server Community Edition 8.1
+ Java: Java(TM) SE Runtime Environment (build 1.8.0_151-b12) HotSpot

[^1]: Windowsの場合でも`*.sh`を`*.bat`に読み替えるなどで同じように操作可。

# 主要なWebリソース
+ [Pentaho Community](https://community.hitachivantara.com/community/products-and-solutions/pentaho)
+ [Pentaho Wiki](https://wiki.pentaho.com)
+ [Pentaho SourceForge](https://sourceforge.net/projects/pentaho/)
+ [Pentaho Support](https://support.pentaho.com/hc/en-us)
+ [Pentaho Documentation](https://help.pentaho.com/Documentation/)
+ [BEST PRACTICES AND GUIDELINES FOR PENTAHO](https://support.pentaho.com/hc/en-us/sections/360000574132-Best-Practices-and-Guidelines-for-Pentaho)
+ [Pentaho Data Integration（PDI）の各種ガイドライン](https://support.pentaho.com/hc/en-us/articles/360000307943-Pentaho-Data-Integration)

# インストールと起動
CEなので統合インストーラはない。とはいえCommunityサイトやWikiサイトからダウンロードリンクを辿るか、SourceForgeから直接zipファイルをダウンロードして適当なディレクトリに展開するだけ。今回は[SourceForge](https://sourceforge.net/projects/pentaho/)から"pdi-ce-8.1.0.0-365.zip"を直接ダウンロードし、ユーザのホームディレクトリ直下に"pentaho"ディレクトリを作成してこの中にzipを展開してできた"data-integration"を配置した。

```bash:インストール
mkdir ~/pentaho
cd ~/pentaho
unzip ~/Downloads/pdi-ce-8.1.0.0-365.zip
echo '~/pentaho/data-integration/spoon.sh' > /usr/local/bin/spoon
chmod 755 /usr/local/bin/spoon
```
```bash:DIツールの起動
spoon
```

起動は`spoon.sh`を実行するだけ。Javaが適切にセットアップされていれば特に問題なく起動する。必要なライブラリのパスを実行時に動的に取得するので、当該スクリプトをシンボリックリンク等で`/usr/local/bin`あたりに放り込んでも動かない。毎回フルパスやディレクトリ移動して実行するのは面倒なので、ラッパースクリプトを作っておいた。`spoon`で起動する。<img width="1552" alt="スクリーンショット 2018-05-23 17.42.02.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/503d4e27-20c6-8023-b7b0-846ed1527ba1.png">

また、これ単体に軽量のWebコンテナ（Carte）が実装されているので、あえてpentaho-serverを使用しなくてもある程度のことはお試し実行できる。今回はデータ統合のお勉強なのでこのまま進めるが、データベース接続設定（オブジェクト）を各ファイルで共有できないという大問題が発生するので、あとでみていく。

# ヒープ設定
しばらく使っているとわかるが、このspoon、結構頻繁に落ちる。おそらくヒープメモリ不足でOutOfMemoryになっていると思われる。`spoon.sh`をのぞいてみる。

```bash:spoon.sh（抜粋）
231 if [ -z "$PENTAHO_DI_JAVA_OPTIONS" ]; then
232     PENTAHO_DI_JAVA_OPTIONS="-Xms1024m -Xmx2048m -XX:MaxPermSize=256m"
233 fi
```
見ての通りで、環境変数`PENTAHO_DI_JAVA_OPTIONS`を設定してやればよいらしい。製品が提供するスクリプトを変更するのに抵抗がある文化で育ってきているので（パッチ当てたら戻っちゃったとか煩わしい）、外部環境変数として定義して起動スクリプトに渡るように、ここではとりあえず`.bash_profile`にて当該環境変数を設定し、起動時・最大ともヒープサイズ4GBにしておく。
もうひとつ、Java HotSpotでは1.8からメモリ構造が変わって、MaxPermSizeでサイズ指定していたヒープのパーマネント領域が廃止され、かわりにMetaspaceがNative領域に確保されるようになったはず。起動スクリプトの実行直後のコンソール出力を見るとちゃんとエラーというか無視する旨の警告メッセージが出力されていた。

```text
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=256m; support was removed in 8.0
```
あまりいじる必要はないかもしれないが、Metaspaceサイズを指定するなら`-XX:MetaspaceSize`と`-XX:MaxMetaspaceSize`オプションを使用する。

```bash
echo 'export PENTAHO_DI_JAVA_OPTIONS="-Xms4096m -Xmx4096m -XX:MetaspaceSize=512m -XX:MaxMetaspaceSize=1024m"' >>~/.bash_profile
. ~/.bash_profile
spoon
```
フルGCが頻発して動作が遅いなどあれば、適宜このあたりをいじる必要がある。
なお、spoonを起動するとプロパティファイルが`~/.kettle/kettle.properties`に作成されるので、これを編集することでカスタマイズできる（今はスルー）。`properties`なので使いなれたプロパティエディタで修正するもよし、spoonから編集するもよし。後者の場合、「編集」メニューの「kettle.properties〜」から。
<img width="559" alt="スクリーンショット 2018-05-23 18.30.56.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/e31b2c91-0422-efab-6330-9ae40f45724e.png">
<img width="2118" alt="スクリーンショット 2018-05-23 18.31.06.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/3d76045c-6540-293c-28a9-11ac044ab2b3.png">

# DB接続設定
有名なDB接続はサポートされている（JDBC接続なのでドライバは別途用意要）。というわけで、ここではお勉強のために公式配布されているサンプルデータを使用しつつ、DB接続する手順を記載しておく。

## サンプルDBのダウンロード
[SourceForge](https://sourceforge.net/projects/pentaho/)から、以下のように辿ってファイルを取得する。
<img width="1041" alt="スクリーンショット 2018-05-23 18.54.53.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/ea6f4fbf-dd75-764b-305b-368c3bed3ad4.png">
<img width="1080" alt="スクリーンショット 2018-05-23 18.55.04.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/27d88922-146b-da60-eddb-2f28cc74a548.png">
<img width="1050" alt="スクリーンショット 2018-05-23 18.55.55.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/cf1d9c37-288e-c3ec-c77b-43734faaafc4.png">
<img width="1037" alt="スクリーンショット 2018-05-23 18.56.10.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/a1fd17af-219d-d55c-ec96-1ab2a990a927.png">

１０年ほどメンテナンスされていないようだがまあいいか。`pentaho_sample_data-1.7.1.zip`はHypersonic（現HSQLDB）同梱（Java実装なので実体はjarファイル１つ）。他にもOracleやPostgreSQL用のSQLファイルが用意されている。`pentaho_demo_mysql5-1.7.1.zip`はOSS時代のjboss（つまり現WildFly）同梱で、同様にMySQL用のSQLファイルが`data/SampleDataDump_MySql.sql`に格納されている。今回は特段の理由もなく後者のMySQL版を使ってみる。他のRDBMSを使用する場合でもspoon自体の操作に大差はない。

## SQL修正
このSQLファイルはMySQL5.1で`mysqldump`したもののようで、そのままではMySQL5.7で実行できなかったので、以下のように若干修正した。

```sql:SampleDataDump_MySql.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ sampledata /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_cs */;
-- grant all on sampledata.* to pentaho_user identified by 'password';
-- grant all on sampledata.* to pentaho_admin identified by 'password';
＜中略＞
  REQUIREDDATE timestamp NOT NULL default '1970-01-02 00:00:01',
  SHIPPEDDATE timestamp NOT NULL default '1970-01-02 00:00:01',
＜中略＞
  REQUIREDDATE timestamp NOT NULL default '1970-01-02 00:00:01',
  SHIPPEDDATE timestamp NOT NULL default '1970-01-02 00:00:01',
```
今回はサンプルデータが使えればよいので、`hibernate`と`quartz`スキーマに関するDDLは全て削除し（`CREATE DATABASE sampledata`より上の行は全削除）、ユーザも既存のユーザを使用するので`grant`をコメントアウト、さらにtimestamp型列のデフォルト値を5.7系で許容できる値に置き換えた（5.1系は'0000-00-00 00:00:00'なんて値を許容していたのか？？？）。以下、[5.7系マニュアルの抜粋](https://dev.mysql.com/doc/refman/5.7/en/datetime.html)。

> 11.3.1 The DATE, DATETIME, and TIMESTAMP Types
> 
> The DATE, DATETIME, and TIMESTAMP types are related. This section describes their characteristics, how > they are similar, and how they differ. MySQL recognizes DATE, DATETIME, and TIMESTAMP values in several > formats, described in Section 9.1.3, “Date and Time Literals”. For the DATE and DATETIME range descriptions, “supported” means that although earlier values might work, there is no guarantee.
> 
> The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.
> 
> The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
> 
> The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

あとはPrimary Keyのないテーブルに対して`ALTER TABLE DISABLE|ENABLE KEYS`とかやっているので警告が出るのも無視。このSQLを実行すれば`sampledata`スキーマができあがり、サンプルデータがロードされる。

## ER図
MySQL Workbenchでリバースエンジニアリングしたもの。外部キーがDDLにちゃんと書かれていないので、リレーションはきちんと取得できていない。というかちゃんとRDBになっていない。`ORDERFACT`テーブルだけ、`PRODUCTS`テーブルの`PRODUCTCODE`が外部キーとして正式に設定されているので、ここだけ関連があるように見える。。。非常に残念ではあるものの、今回はデータをガリガリといじったりするわけではないので、このまま押し通すことにする。
それにしても、MySQL5.1の`mysqldump`コマンドがしょぼいのか、作った人が手を抜いたのか、どっちなんだろう。。。
<img width="983" alt="スクリーンショット 2018-05-23 20.48.35.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/267c8972-5aa5-dedc-f551-f535ba67e0b4.png">

## JDBCドライバのインストール
PDIにJDBCドライバを登録する。といってもドライバファイル（jar）をインストールディレクトリ下の`lib`ディレクトリに配置するだけ（今回はすでに入手してあったものをそのままコピーしたが、なければ[MySQLのサイトからConnector/Jをダウンロード](https://dev.mysql.com/downloads/connector/j/)する）。

```bash
cp -p ~/devel/lib/jdbc/mysql-connector-java-5.1.45-bin.jar ~/pentaho/data-integration/lib
```
そしてspoonを再起動。

```bash
spoon
```

## PDI設定
さて、PDI側の設定は、画面左上の新規作成ボタンからコンテクストメニューを出して、「データベース接続...」で行う、のだが当該項目はグレイアウト状態である（ファイルメニューの新規作成でも同じ）。
<img width="1552" alt="スクリーンショット 2018-05-23 20.57.51.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/1bd8b51d-5175-918c-c4e2-f7c1bd3a449d.png">
<img width="200" alt="スクリーンショット 2018-05-23 20.59.39.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/26190ac3-da9d-b353-68c5-c0c64d74295e.png">

焦らずにまずは「新規作成」から「データ変換」を選んでETLデータフロー作成画面を出す。
<img width="200" alt="スクリーンショット 2018-05-23 20.54.53.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/d101dd4b-097c-a77f-5bf7-7e57014ce15a.png">
再度新規作成ボタンを押す。
<img width="200" alt="スクリーンショット 2018-05-23 21.00.23.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/8a13c247-333f-43a7-65e6-56f94e31fa15.png">
今度は「データベース接続」が活性状態になった。データベース接続情報（オブジェクト）はspoonインスタンス自体に紐づいて持つものではないということ。この場合は新規作成した「データ変換」の文書内で、データベース接続できるようになります、ということである。
<img width="1050" alt="スクリーンショット 2018-05-23 21.01.18.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/fc23408e-8c77-810d-ddbf-ee6b27c5f5ac.png">
今回はMySQLなので、「接続タイプ」からこれを選んで必要な項目を埋める。そして「テスト」ボタンを押下。
<img width="1066" alt="スクリーンショット 2018-05-23 21.08.31.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/c63a49ca-0802-b259-ee59-f98163224739.png">
特段問題がなければこのように接続に成功する。これでDB接続準備は完了。「OK」して戻る。

# pentaho-serverの導入
ここでさらに新規のデータ変換を作成してみる。そして「ビュー」を表示すると。。。<img width="996" alt="スクリーンショット 2018-05-23 22.18.24.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/9f79ae42-426a-b8ab-9d0c-8d58404f8bd0.png">
<img width="996" alt="スクリーンショット 2018-05-23 22.18.17.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/dd5ad3fd-447a-8ca0-9bc2-27bd62beb895.png">
この通り新規作成したデータ変換からは、データベース接続が使用できない（各データ変換文書間でデータベース接続定義（オブジェクト）を共有できない）。このままでは毎ファイル作成するごとに、DB定義を設定しなおさなければならない。そこでpentaho-serverのリポジトリの登場となる。こいつを作成し、そのなかで作業をすることで、データベース接続定義（オブジェクト）を共有することができる（要は使いまわすことができる）。

## インストールと起動
PDI同様にSourceForgeから取ってくる。
<img width="1044" alt="スクリーンショット 2018-05-23 22.24.37.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/a4d62e8c-4ebd-3181-e10a-b6c6ab652393.png">
<img width="1050" alt="スクリーンショット 2018-05-23 22.25.00.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/7752104b-e3bb-1f6e-efa2-992c467f9c1d.png">
<img width="1055" alt="スクリーンショット 2018-05-23 22.25.12.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/6ebc00c0-cfc8-73f1-43a6-22c91c59ef8b.png">

```bash:インストール
cd ~/pentaho
unzip ~/Downloads/pentaho-server-ce-8.1.0.0-365.zip
```
Tomcatベースなので、他のTomcatインスタンス向けに`CATALINA_HOME`を定義している場合は値を変更しないといけない。起動スクリプト自体は用意されているので、環境変数を設定しつつスクリプトを実行するラッパースクリプトを書いておく（起動・停止スクリプト自体をいじるのは避けたいので。。。これを弄れ的なset-pentaho-env.shなるものもあるが、こちらもパッチ適用等して上書きされる可能性があるので、、、）。

```bash:スクリプト作成
echo 'CATALINA_HOME=~/pentaho/pentaho-server/tomcat ~/pentaho/pentaho-server/start-pentaho.sh' > /usr/local/bin/start-pentaho
echo 'CATALINA_HOME=~/pentaho/pentaho-server/tomcat ~/pentaho/pentaho-server/stop-pentaho.sh' > /usr/local/bin/stop-pentaho
chmod 755 /usr/local/bin/*-pentaho
```
```bash:起動
start-pentaho
```

## PDIからログイン
PDIからpentaho-serverへの接続は、spoonの画面右上の「Connect」ボタンを押す。
<img width="996" alt="スクリーンショット 2018-05-23 22.47.51.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/3bacfe54-0ed8-c49a-b222-4b19456ab809.png">
「Get Started」する。
<img width="968" alt="スクリーンショット 2018-05-23 22.49.32.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/9f40c529-e7d8-ca11-e9c8-7e869362d036.png">
「Display Name」に任意のリポジトリ表示名を入力し、次回以降面倒なので起動時に接続するようにし、「Finish」する。つまりspoon起動時にpentaho-serverも起動している必要がある。
<img width="968" alt="スクリーンショット 2018-05-23 22.50.18.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/f727cbd2-0a1a-b55e-98aa-aaf6138c904f.png">
無事にリポジトリができたので早速「Connect Now」する。
<img width="968" alt="スクリーンショット 2018-05-23 22.50.30.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/3d3c562f-34d0-fbf6-e063-d237560f4b19.png">
ユーザー名・パスワードは決まっていて`admin/password`。
<img width="968" alt="スクリーンショット 2018-05-23 22.50.40.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/2f149009-aff6-b6d0-3731-39fa334617b6.png">
無事に接続されると画面右上に接続先リポジトリが表示される。
<img width="996" alt="スクリーンショット 2018-05-23 22.50.48.png" src="https://qiita-image-store.s3.amazonaws.com/0/232587/5e6f39ea-1359-1518-b906-a5230ed2078f.png">
なお、研修環境だったVersion8.0 on Winserver 2012では、リポジトリ表示名にマルチバイト文字を入れると、この右上のボタン自体が表示されなくなるというバグ（仕様？）があった。8.1で修正されたものか定かではないが、今回の環境では再現しなかった。念のため復旧方法を記載しておくと、このリポジトリ情報は`~/.kettle/repository.xml`に保存される。このファイルを開き、`name`要素を書き換えて（問題になっている文字をなくして）spoonを再起動すれば直せる。

```xml:repository.xml
<?xml version="1.0" encoding="UTF-8"?>
<repositories>
  <repository>    <id>PentahoEnterpriseRepository</id>
    <name>Training</name>
    <description>Pentaho repository | http://localhost:8080/pentaho</description>
    <is_default>true</is_default>
    <repository_location_url>http://localhost:8080/pentaho</repository_location_url>
    <version_comment_mandatory>N</version_comment_mandatory>
  </repository>  <repository>    <id>PentahoEnterpriseRepository</id>
    <name>ほげ</name>
    <description>Pentaho repository | http://localhost:8080/pentaho</description>
    <is_default>false</is_default>
    <repository_location_url>http://localhost:8080/pentaho</repository_location_url>
    <version_comment_mandatory>N</version_comment_mandatory>
  </repository>  </repositories>
```

というわけで、リポジトリに接続できるようになったので、あとはこのなかにDB接続を作成しなおせば、複数のドキュメントで共有できる。というよりも、pentaho-serverを起動すると、hypersonicも起動してサンプルデータが参照可能になる。改めてIBM Data Studioでリレーションと主キーを予測オプションつきでリバースエンジニアリングしたER図。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/232587/bac6ce82-a0b1-3f58-762c-0c9197577c1c.png)

長くなったので今回はここまでにしておき、[次回](https://qiita.com/ch7821/items/a173318267ba13f208fd)はデータ変換のステップを逆引き的にまとめる。