ETLで実行したい処理の一つが、入力データにマスターデータを追加する処理です。

Talendの公式チュートリアル 5minDemoの3番目「Set up a Join」が、ちょうどマスターデータを参照する処理のチュートリアルになっています。
5minDemoチュートリアルは4つのセクションに分かれており3つめがこのJoinの例になっており、「1. Talend Open Studio for Data Integration Overview」と「2. tMap Essential Features」に続けて実施する構成になっています。
「Set up a Join」の手順を進めるにあたって最低限必要な部分のみ1、2の手順を実施した後に、複数データソースを結合する処理を追加していく手順をまとめてみました。

このデモでは、入力のCSVファイルCustomerのState列の値を、条件「CSVファイルCustomerのState列とStatesテーブルPostalカラムの値が等しい」マスターデータStatesテーブルの行のStateカラムに置き換えます。

以下の手順は、Talend Open Studioは、Data Integration 5.5で確認しています。

前準備CSV読み込みまで

まず、プロジェクトを作成してCSV入力をログにそのまま出力する処理をセットアップします。

1. チュートリアルデータのダウンロード

まずは、チュートリアルページからチュートリアル用のデータ exampleFile.zip をダウンロードして解凍します。

2. チュートリアル用プロジェクトの作成

Talend Studioを起動して新規プロジェクト「Demo5」を作成し、新規ジョブ「Demo5min」を作成します。

3. チュートリアル用データCSVファイルを[区切り記号付きファイル]として定義

CSVファイル「customers_demo5mn.csv」を入力とする[区切り記号付きファイル]を追加します。

この辺りの手順は、Talend Open Studio for Data Integration Overviewも参考にしてください。

3.1 リポジトリから[メタデータ] > [区切り記号付きファイル] > [区切り記号付きファイルの作成を]選択し、[区切り記号付きファイル]ウィザードを開きます。

3.2 新規区切り記号付きファイル ステップ1: Nameに「Customer」を入れて[次へ]

3.3 新規区切り記号付きファイル ステップ2: ファイルで「customers_demo5mn.csv」を選択して[次へ]

3.4 新規区切り記号付きファイル ステップ3: [列名として先頭行を設定]にチェックを入れて[プレビューのリフレッシュ]、CSVの内容を確認して[次へ]

3.5 新規区切り記号付きファイル ステップ4: [名称]に「Customer」と入力、[スキーマの説明]で「id」列のみ[キー]としてチェックを入れて[次へ]

区切り記号付きファイル「Customer」が[メタデータ]>[区切り記号付きフィアル]に下に追加されました。

4. CSV入力を[tMap]を介して[tLogRow]に出力

次に、Talend Tutorials – 5mn Demo – tMap Essential Featuresを参考に「customers_demo5mn.csv」がセットされた[区切り記号付きフィアル]を入力として、[tMap]に接続し、[tMap]の出力を[tLogRow]を使ってログとして確認するところまで実装します。

4.1. 3.で追加した[区切り記号付きファイル] > [Customer]ををジョブのキャンバスにドロップして[tFileInputDelemited]を選択します。

4.2 続いてパレットより[tMap]、[tLogRow]コンポーネント選択してキャンバスに追加します。

4.3 入力と出力を以下のようにつなげます。

この状態では、[tMap]の出力スキーマが定義されていないため警告が出ています。

4.4 [tMapエディタ]を開き入力を出力につなげます。
キャンバス上の[tMap_1]をダブルクリックして[tMapエディタ]を開きます。
ここでは、入力のカラムをすべてそのまま出力するように設定します。

※ チュートリアルでは、式を作成して[式ビルダ]を開き「lastname」を大文字に変換する処理を追加しますがここではJoinを試すことが目的なので割愛して先に進めます。

4.5 [tLogRow]でも出力スキーマをセット
[tMap]を設定するとエラーはなくなりますが、このまま実行すると[tLogRow]でスキーマの対応がないとエラーになる場合があります。
このときは、[tLogRow]を選択して[カラムの同期]をクリックして入力カラムを全て出力対象としてセットします。

5. ジョブ実行して確認

一旦、ここまでに定義したジョブを実行して動作を確認しておきます。
[実行(ジョブ Demo5min)]タブを選択して[実行]ボタンをクリックして、ジョブを実行します。

次から、目的のSet up a Joinの確認に入ります。

CSV入力にマスターデータと結合させたデータを出力する

1. マスターデータをMySQLにロード

マスターデータの「states_demo5mn.txt」をMySQLにロードします。

チュートリアル用のデータベース「5MinDemo」を作成して、テーブルを「states」を追加します。

mysql> create database 5minDemo default character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> use 5minDemo
Database changed
mysql> CREATE TABLE `states` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT,
    ->   `Postal` varchar(2) NOT NULL,
    ->   `State` varchar(14) NOT NULL,
    ->   `Capital` varchar(14) NOT NULL,
    ->   `MostPopulousCity` varchar(14) NOT NULL,
    ->   PRIMARY KEY (`ID`)
    -> );
Query OK, 0 rows affected (0.01 sec)

続けて、「states_demo5mn.txt」ファイルをテーブルにロードします。

mysql> LOAD DATA INFILE "/Users/hrendoh/Downloads/014/states_demo5mn.txt" INTO TABLE states FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;
Query OK, 50 rows affected, 2 warnings (0.00 sec)
Records: 50  Deleted: 0  Skipped: 0  Warnings: 2

2.「states」テーブルのデータを入力として定義

チュートリアルSet up a Joinに沿って「states」テーブルを入力として定義します。

2.1. [メタデータ] > [DB接続] を選択して、コンテキストメニューを表示、メニューから[新規DB接続]を選択して、[DB接続]ウィザードを開きます。

2.2. ステップ1: [Name]に「5MinDemo」と入力して[次へ]

2.3. ステップ2: 以下のように各項目をセット

DB Type: MySQL
ログイン: root
パスワード: xxxxx
サーバー: localhost
ポート: 3306
DataBase: 5minDemo

[チェック]ボタンをクリックして接続に成功したら[終了]をクリック

[メタデータ] > [DB接続]に「5MinDemo」が追加されました。

2.4. 追加された[5minDemo 0.1]でコンテキストメニューを表示し[スキーマ情報の取得]をクリックして[Schema]ウィザードを開きます。

2.5. テーブルをフィルタ: デフォルトのまま[次へ]

2.6. リポジトリに新規スキーマを追加: テーブル「states」を選択して[次へ]

2.7. リポジトリに新規スキーマを追加: [スキーマ]の内容を確認して[終了]

[5minDemo 0.1] > [テーブル]の下に[states]が追加されました。

3. マスターデータ テーブル「states」を入力としてジョブに追加

[メタデータ] > [DB接続] > [5minDemo 0.1] > [テーブル] > [states]をジョブキャンバスにドロップし、コンポーネント[tMysqlInput]を選択します。

4. SQL BuilderでSELECTするカラムを設定

4.1. ドロップした[states(tMysqlInput)]のコンポーネントビューを表示して、[クエリ]の右の[…]ボタン(以下の画像中の赤で囲んだボタン)をクリックしてSQL Builderを表示します。

4.2. SQL Builderが開いたら右側の[5minDemo(5MinDemo).sql]ビューで[デザイナ]タブを選択、[デザイナ]で「Postal」と「State」カラムのみ残して他のカラムはチェックを外す。一番左のクエリ実行アイコンをクリックして左下の[クエリ結果: 1]ビューに修正したクエリーで取得したデータを確認。[OK]してウィンドウを閉じます。

5. マスターデータ テーブル「states」の入力を[tMap]に接続

[states]の出力を[tMap_1]につなぎます。[row2 (lookup)]

[states]の右上の警告を確認すると「スキーマのデータタイプがこのコンポーネントに合っていないよ」と表示されています。
今回のチュートリアルでは、このままでも問題ないですが、一応解消しておきます。

[states(tMysqlInput)]の[コンポーネントビュー]で[Guess Schema]をクリックしてスキーマ情報を更新します。

[スキーマ:”states”]ウィンドウが開きます。
リストされているカラムがSELECTに指定したもののみに更新されていることを確認できます。
ここで、さらにDBタイプを以下のようにそれぞれ「states」テーブルに合わせて「VARCHAR」にセットします。

この設定をすると、[states]の右上の警告が消えます。

5.[tMap]でマスター「states」の入力を「Customer」に結合

[tMap]コンポーネントをダブルクリックして[tMapエディタ]を開きます。

6. ジョブを実行して確認

ジョブを実行すると、CSV入力Customerのstate列の値をMySQLのstatesテーブルから取得した値に置き換えられていることを確認できます。

元のチュートリアルではこれをMySQLのCustomerテーブルに出力していますが、結合の手順を確認できたので、ここまでにします。

参照

CSVをMySQLへ出力方法については、「オープンソースETLツールTalendでExcelのデータをデータベースにロードしてみた」も参考にしてみてください。

tMapを利用したフィルタリングにはTalendのサンプルサイトTalend by Exampleの「
tMap Joins & Filtering」も参考になります。