【Pentaho】Pentaho ～ DBを扱う ～

Pentaho

■ はじめに

https://dk521123.hatenablog.com/entry/2019/10/04/000000
https://dk521123.hatenablog.com/entry/2020/09/10/174223
https://dk521123.hatenablog.com/entry/2020/09/09/000000

の続き。

今回は、Pentaho での DB を使った処理を扱う。

Pentaho を使えば、簡単なデータの出し入れであれば、
システムにあまり精通をしていない人でもある程度、
DBを扱うことができる。

今回学んだこと 

１）Pentaho での DB接続およびテーブルへの出力
２）以下の使い方
　+ Filter rows ... 対象データ行の取得
　+ Table output ... テーブルへの書き出し

■ サポートDB について

* 主要なDBは、ほぼOK。（RedshiftやHiveもサポート）

* 最悪、JDBC接続をサポートしていれば、
　JDBCフォーマットをそのまま入れることも可能

* 詳細は、以下の公式サイトを参照。

https://help.pentaho.com/Documentation/8.1/Setup/JDBC_Drivers_Reference

■ 使用上の注意

* 上記のようにサポートDBはされているが、
　例えば、MariaDBの場合、ドライバをダウンロードして
　設定しておく必要がある
　（デフォルトのまま設定するとエラーが表示される）

* なお、PostgreSQL は、Pentaho の標準DBらしく、
　特に何もせず、使用できた

エラー内容 

Error connecting to database [sample_db] :org.pentaho.di.core.exception.KettleDatabaseException: 
Error occurred while trying to connect to the database

Driver class 'org.mariadb.jdbc.Driver' could not be found, make sure the 'MariaDB' driver (jar file) is installed.
org.mariadb.jdbc.Driver

例：MariaDBへの接続の場合

[1] 以下のサイトからMariaDB の JDBCドライバをダウンロードする
　⇒ 今回の場合、mariadb-java-client-2.6.2.jar

https://downloads.mariadb.org/connector-odbc/

[2] [1] の JDBCドライバを、<インストール場所>\data-integration\lib配下に格納する
　⇒ 今回の場合、C:\work\pentaho\pdi-ce-9.0.0.0-423\data-integration\lib\mariadb-java-client-2.6.2.jar

■ サンプル

*CSV=> DB に格納するようにする
* ただ、そのまま格納しても面白くないので、 Filter を使い、
　StateがCA（カルフォルニア州）のみを取得し、DBに格納する

使用するサンプルCSVファイル

https://dk521123.hatenablog.com/entry/2020/09/09/000000

C:\work\pentaho\pdi-ce-9.0.0.0-423\data-integration\samples\transformations\files\Zipssortedbycitystate.csv

Zipssortedbycitystate.csv (一部抜粋) 

CITY,STATE,POSTALCODE
ABBEVILLE,AL,36310
ABBEVILLE,LA,70510
ABBEVILLE,MS,38601
ABBOT,ME,4406
ABBOTT,TX,76621
ABBYVILLE,KS,67510
ABERCROMBIE,ND,58001
ABERDEEN,KY,42201
ABERDEEN,MS,39730
ABERDEEN,OH,45101
ABERDEEN,SD,57402
ABERDEEN PROVING GROUND,MD,21005
・・・略・・・

手順

１）DB Connectionの設定
２）入力の設定
３）フィルタの設定
４）出力の設定
５）関連付けおよび動作確認

１）DB Connectionの設定 

[1] Spoonを起動し、[File]-[New]-[Translation] を選択 (又は「Ctrl + Nキー」押下)

[2] 左ペインの [View]-[Transactions]-[Transactions 1]-[Database Connection]
　を選択した状態で右クリックし、[New]を選択

[3] 以下を入力（PostgreSQLの場合）
 + Connection Name : 任意の文字列（今回の場合「postgre_connect」）
 + Connection Type : 設定するDB（今回の場合「PostgreSQL」）
 + Access: Native(JDBC)
 + Settings
   + Host Name : DBのホスト名（今回の場合「localhost」）
   + Database Name : DB名（今回の場合「sample_db」）
   + Post Number : DBのポート番号（今回の場合「5432」）
   + Username : DBのユーザ名（今回の場合「postgres」）
   + Password : DBのパスワード

[4] 「Test」ボタン押下し、DBに接続できるかどうか確認する
　=> 問題なければ「Connection to ＜Connnection name＞ was successful.」が表示

[5] 「OK」ボタン押下

２）入力の設定 

[1] 左ペインの [Design]-[Input]-[CSV File Input] を選択

[2]  「CSV file input」アイコンをダブルクリックし、以下を入力
~~~~~
 File name : <インストール場所>\data-integration\samples\transformations\files\Zipssortedbycitystate.csv
 ~~~~~

[3] 「Get  Fields」ボタン押下

[4] 「Preview」ボタン押下し、データを確認し、「OK」ボタン押下

３）フィルタの設定 

[1] 左ペインの [Design]のSearch Text boxに「filter」と入力し
　[Filter rows]を選択
　(※選択後、Search Text boxの右側の×アイコンでクリアしておく)

[2]  「CSV file input」アイコンを選択し、
　　数秒待ち、右から2番目のアイコンを選択し
　　「Filter rows」アイコンにつなげ、
　　「Main out of step」を選択し、関連づける

[3] 「Filter rows」アイコンをダブルクリックし、以下を入力
 + 左側の Field =  STATE
 + 右側の Value =  CA <= CA(カルフォルニア州)のユーザを抽出

４）出力の設定 

[1] 左ペインの [Design]-[Output]-[Table output] を選択

[2] 「Table output」アイコンをダブルクリックし、以下を入力
~~~~~
 + Connection : １）- [3] で作成した接続名（今回の場合「postgre_connect」）
 + Target Table : 対象テーブル（今回の場合「city_info」）

 + 「Specify database field」にチェック
 + [Database fields]タブ を選択し、「Get Fields」ボタン押下
 + 「SQL」ボタン押下
　=> 自動生成されたSQLを、以下の「自動生成されるSQLの修正例」を参考に修正
　=> 「Execute」ボタン押下し、テーブルを作成
　=> 問題なければ、対象DBにテーブルが作成されているはず
~~~~~

[3] ダイアログをクローズし、「OK」ボタン押下

自動生成されるSQLの修正例 

CREATE TABLE city_info
(
  CITY VARCHAR(50) -- 23 => 50
, "STATE" VARCHAR(2)
, POSTALCODE INT -- BIGINT => INT
)
;

５）動作確認 

[1] [File]-[Save]などで任意の名前で保存
（保存しないと実行できない）

[2] 「再生（Run）」アイコンを押下し、実行
　⇒ 無事成功していれば、テーブル「city_info」にデータが存在しているはず。

出力例 

SELECT * FROM city_info LIMIT 5;
~~~~~~~~~
"ACAMPO"    "CA"    "95220"
"AGOURA HILLS"  "CA"    "91376"
"AGUANGA"   "CA"    "92536"
"ALAMEDA"   "CA"    "94502"
"ALHAMBRA"  "CA"    "91802"
~~~~~~~~~

SELECT COUNT(*) FROM city_info;
~~~~~~~~~
1346
~~~~~~~~~

関連記事

Pentaho ～ 基礎知識編 ～
https://dk521123.hatenablog.com/entry/2019/10/04/000000
Pentaho ～ 環境設定編 ～
https://dk521123.hatenablog.com/entry/2020/09/10/174223
Pentaho ～ 入門編 ～
https://dk521123.hatenablog.com/entry/2020/09/09/000000
Pentaho ～ 簡単なETL処理を行ってみる ～
https://dk521123.hatenablog.com/entry/2020/09/12/164214