はじめに
前回のTalendから、今回は別のものに変えて試してみます
前の記事：Talend Open StudioでAzure SQL Databaseと連携する
使用するETLツールは、Pentaho Data Integration になります。
Pentaho Data Integration とは
Pentaho Data Integration(以下「PDI」と略す)は、オープンソースBIスイート「Pentaho」が提供するETLツールです。
Spoon(ETL処理を定義するクライアントのデザインツール)、Pan&Kitchen(定義したETL処理をバッチ実行するツール)、Carte(定義したETL処理を実行するWebサーバ）で構成されています。
PDIは、Talendと同じように、GUIのデザインツールを用いて、ETL処理を定義できます。
また、未サポートデータベースでも、JDBC接続できるデータベースには接続が可能なので、前回と同じAzure SQL Database にて、データのインポートを試してみます。
Pentaho Data Integrationダウンロード: https://sourceforge.net/projects/pentaho/files/Data%20Integration/
前提動作環境
動作環境は下記になります。
- Pentaho Data Integration 7.1
- Windows Server 2012 R2 ※事前にインストールしておきます。
- jdk-8u152-windows-x64.exe ※事前にインストールしておきます。
- Azure SQL Database ※事前に作成しておきます。
- DB名：pdi
- スキーマ名：dbo
- テーブル名：test_table
- 項目定義：time(smalldatetime型)、name(ntext型)、age(smallint型)
Pentaho Data Integrationのインストール
Pentaho Data Integration v7.1である「pdi-ce-7.1.0.0-12.zip」をダウンロードして、「C:\pentaho」配下にzipファイルを展開します。
展開したら、「C:\Pentaho\data-integration\Spoon.bat」を実行すると、次の画面が開きます。
メニューから「ファイル」-> 「新規」-> 「データ変換」をクリックして、新しくデータ変換を作成します。
Pentaho Data Integrationの利用方法
PDIもTalendと同じように、1つ1つの機能をコンポーネントというパーツで提供しており、そのコンポーネントを相互に組み合わせることで、処理のフローを記述することができます。
下図のとおり、「JDBC経由でAzure SQL Databaseへのデータインポート」の処理フローを作成していきます。
以下で、各設定方法を説明していきます。
JDBCドライバの設定
こちらからをダウンロードしたSQL ServerのJDBCドライバを「C:\pentaho\data-integration\lib」配下に配置します。
配置したら、Spoonを再起動します。
メニューから「ファイル」-> 「新規」->「データベース接続作成」をクリックし、以下の「データベース接続」画面を開きます。
「データベース接続」画面の設定
画面左端：「一般」を選択
接続名：適宜指定
接続タイプ：「Generic database」を選択
アクセス：「Native(JDBC)」を選択
カスタム接続URL：jdbc:sqlserver://${Azure SQL Serverのサーバ名}.database.windows.net:1433;database=pdi;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30
カスタムドライバークラス名：com.microsoft.sqlserver.jdbc.SQLServerDriver
ユーザ名：${Azure SQL Databaseのユーザ}
パスワード：${Azure SQL Databaseのユーザのパスワード}
CSVファイルの読み込み
インポート対象のデータとして、以下のUTF8のCSV形式のファイル(ヘッダー付)を用意します。
time,name,age
2016/04/01 00:00:00,サイオス太郎,20
2017/05/31 00:00:00,ビッグデータ太郎,22
画面左のデザインタブから「入力」->「CSV入力」を右側のキャンバスに、ドラッグ＆ドロップで配置し、ダブルクリックします。
「CSV入力」の設定
ファイル名：${CSVファイルを配置したファイルパスを指定}
引用符：空にする
文字コード：UTF-8
文字コード欄下のフィールドの定義：以下「※フィールドの定義」のとおり設定する
※フィールドの定義
Azure SQL Databaseへのデータ挿入
画面左のデザインタブから「出力」->「テーブル出力」を右側のキャンバスに、ドラッグ＆ドロップで配置し、ダブルクリックします。
「テーブル出力」の設定
データソース名：上記データベース接続で適宜指定した接続名
スキーマ名：dbo
テーブル名：test_table
コミットサイズ：1
「CSV入力」と「テーブル出力」のマッピング
「CSV入力」アイコンから「テーブル出力」アイコンへと、Shiftキーを押しながらドラッグして矢印で結びます。
その後、「テーブル出力」アイコンを右クリックで表示される項目から、「このステップに対してマッピング生成」をクリックし、「マッピング入力」ダイアログを開きます。
以下のとおり、マッピングを追加してOKをクリックと、「テーブル出力 Mapping」アイコンがキャンバス上に現れます。
「テーブル出力 Mapping」アイコンが現れたので、各アイコン間の矢印を結び直します。
矢印上で右クリックすると表示される項目から「結合削除」をクリックすると、矢印を削除できます。
以下のとおり、Shiftキーを押しながらドラッグして矢印で結び直します。
- 「CSV入力」アイコンから「テーブル出力 Mapping」アイコンへ
- 「テーブル出力 Mapping」アイコンから「テーブル出力」アイコンへ
ジョブの実行
全ての設定が完了した後、ジョブの実行をします。ジョブの実行は、メニューから「アクション」->「実行」をクリックで、実行できます。
クライアント管理ツールのSSMS(SQL Server Management Studio)で確認してみます。
下図のとおり、CSVの内容がテーブルに挿入されています。
ここまでの手順で作成してきた作成してきた「データ変換」を「Spoon」上ではなく、バッチ実行するには、「C:\Pentaho\data-integration」配下にある「pan.bat」を使用します。
「データ変換」をファイル保存して作成された拡張子「ktr」のファイルを、以下のように「pan.bat」の引数に指定して実行します。
pan.bat /file:${拡張子「ktr」のファイルのフルパス} /level:Basic
まとめ
Talendと同様に、PDIでもノンプログラミングでAzure SQL Databaseとデータ連携できることを紹介しました。
