【Pentaho】Pentaho ~ DBを扱う ~

■ はじめに

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を扱うことができる。

今回学んだこと

1)Pentaho での DB接続およびテーブルへの出力
2)以下の使い方
 + 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
・・・略・・・

手順

1)DB Connectionの設定
2)入力の設定
3)フィルタの設定
4)出力の設定
5)関連付けおよび動作確認

1)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」ボタン押下

2)入力の設定

[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」ボタン押下

3)フィルタの設定

[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(カルフォルニア州)のユーザを抽出

4)出力の設定

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

[2] 「Table output」アイコンをダブルクリックし、以下を入力
~~~~~
 + Connection : 1)- [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
)
;

5)動作確認

[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