TalendでCSV入力にマスターデータを結合(Join)するには

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カラムに置き換えます。

JoinOnCustomerStateAndStatesPostal

以下の手順は、Talend Open Studioは、Data Integration 5.5で確認しています。
“TalendでCSV入力にマスターデータを結合(Join)するには” の続きを読む

オープンソースETLツールTalendでExcelのデータをデータベースにロードしてみた

オープンソースのETLツールTalendの使い方についてインストールから基本的な使い方までをまとめてみました。

Talendとは

TalendはオープンソースのData Integrationツールです。
利用用途としては主にETLという認識でしたが、HPをさっとみただけでは全容が把握できないほど多機能です。

HP: http://www.talend.com/
HP(日本):http://jp.talend.com/

有償版もあるので企業導入の際にも支障がありません。
コミュニティ版と企業版の違いは http://jp.talend.com/products/ の各ページに比較表があります。
表中で「Talend Open Studio for Data Integration」が無償のコミュニティ版、「Talend Enterprise Data Integration」と「Talend Platform for Data Management」が有償版です。

では、セットアップしてタイトルの通りExcelのデータをデータベースMySQLにロードしてみます。

セットアップ

1. コミュニティ版を入手

http://www.talend.com/download
にアクセスして[Data Integration]タブを開き[Talend Open Studio for Data Integration]の[Download Free Tool »]をクリックし、OSを選択するとDLが始まる。
コミュニティ版もサイズが450Mほどあるので時間がかかります。

2. 実行環境の準備

Talendは、Javaで動きますのでJava実行環境をインストールしておきます。

3. Talend Open Studio for Data Integration の起動

Talendの開発ツール Talend Open Studio for Data Integration は、Macの場合はインストール不要で、ダウンロードしたファイルを解凍して、すぐに利用できます。
TOS_DI-macosx-cocoa を開きます。

(Windowsはインストーラexeでインストールします)

4. ライセンスの承諾

初回起動時は、LGPL3ライセンスの承諾を要求されますので[承諾]をクリックしておきます。

プロジェクトの作成

Talend-create-project-1
新規プロジェクトの作成に「ExcelToMySQL」と入力して[作成]ボタンをクリックします。

Talend-create-project
[終了]をクリックするとプロジェクトが作成されます。

Talend-create-project-2
右上の[開く]をクリックします。

Talend-registration-to-cummunity
TalendForgeの登録は[スキップ]しておきます。

Talend-welcome
EclipseベースのTalend Open Studioが開きます。

ジョブの作成
[ようこそ]の画面中央の[新規作成…] > [Job]をクリックするか、 [ようこそ]を閉じて画面左の[ジョブ]の右クリックメニューから[ジョブの作成を]をクリックして新しいJobを作成していきます。

Talend-create-job
[Name]に適当なジョブ名(ここでは「FirstJob」)を入れて[終了]をクリックすると作成したジョブの編集画面が開きます。
なお、ジョブ名には空白や日本語は使えません。

Talend-create-job-2

Excelをデータソースとして定義

入力ファイルは以下のようなメールアドレスと名前のみを含むユーザリストのExcelデータです。
Talend-ExcelData

[メタデータ]を開き[Excelファイル]で右クリックし、メニューから[Excelファイルを作成]をクリックします。
Talend-Excel-1

[Name]のみが必須なので、[Name]を適当い入れて[次へ]をクリック
Talend-Excel-2

入力のExcelファイルを選択して、[シートパラメータの設定]で、入力のシートを選択します。ここでは”ユーザリスト”シートを選択しています。
Talend-Excel-3
[次へ]をクリックします。

[列名として先頭行を設定]にチェックをいれて[プレビューのリフレッシュ]ボタンをクリックすると、1行目が列名として設定されます。
Talend-Excel-4
Talendは日本語の扱いが微妙でExcelの先頭行が日本語だと”Cloumns1″の様な自動的に振られた名前になってしまいます。

[スキーマの説明]で[COLUMN]に1行目が列名として設定されていることを確認したら[終了]をクリックしてダイアログを閉じます。
Talend-Excel-5

MySQLのテーブルをデータソースとして追加

出力先のMySQLのテーブルは以下の通りです。
[SQL]
CREATE TABLE `users` (
`email` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`email`)
);
[/SQL]

MySQLのデータソースは、[メタデータ] > [DB接続]から[DB接続の作成]をクリックします。
Talend-MySQL-1

[Name]を入力して[次へ]
Talend-MySQL-2

[DB Type]で”MySQL”を選択肢、接続情報を入力していきます。
データベースはUTF-8なので、[追加パラメータ]には”&useUnicode=YES&characterEncoding=utf8″を追加します。
Talend-MySQL-3

接続のチェックのために[チェック]をクリックすると、接続にMySQLのJDBCドライバーが必要なためインストール用ダイアログが表示されます。
Talend-install-jdbc-1
[Download and install all module available]をクリックして、ライセンスを承諾するとモジュールのダウンロードが始まります。
インストールが終わると自動でダイアログが閉じ接続がチェックされます。
Talend-check-connection
[OK]をクリックして[次へ]をクリックします。

[次へ]が有効にならない場合は[終了]をクリックします。
データベース接続”users”が追加されているので、右クリックメニューを開き[スキーマ情報の取得]をクリックします。
Talend-get-scheme-info

すべてデフォルトのまま[次へ]をクリック
Talend-MySQL-4

[テーブル] “users”にチェックを入れて[次へ]
Talend-MySQL-5

内容を確認して[終了]
Talend-MySQL-6

ジョブを定義

Excelファイルを入力として定義します。
上で定義したExcelファイル”ユーザーリスト 0.1″をドロップするとExcelの入出力を選択するダイアログが表示されるので [tFileInputExcel]を選択します。
Talend-job-drop-Excel

DB接続を出力として定義します。
上で定義したDB接続”users”をドロップすると同様にダイアログが表示されるので[tMysqlOutput]を選択します。
Talend-job-drop-MySQL

これで、入出力が用意できました。
次に、入出力をつなげるマッピングを定義していきます。
マッピングを処理は右ペインから[tMap]をドロップします。ステンシルはたくさんあるので、検索を使っても探せます。
Talend-job-drop-tMap

Excel入力を[tMap]につなげるには、Excel入力上で右クリックメニューから[Row] > [メイン]を選択します。
Talend-mapping-1

選択するとExcel入力からの線が表示されるので、それを[tMap]にドロップします。
Talend-mapping-2

次に、[tMap]からMySQL出力へ接続します。
同様に、[tMap]の右クリックメニューから[Row] > [*New Output*(メイン)]を選択します。
Talend-mapping-3

出力名を入力して[OK]をクリック
Talend-mapping-4

[はい]をクリック
Talend-mapping-5

以下のように入出力がつながりました。
Talend-mapping-6

まだ、Excelの各列とMySQLのカラムがマッピングされていない状態なので、次にマッピングを定義します。
[tMap]をダブルクリックするとマッピングを定義用のウィンドウが表示されます。
Talend-mapping-7
[自動マップ]をクリックすると自動でExcelの列とMySQLのカラムを適当にマッピングしてくれます。
Talend-mapping-8

ジョブを実行

ジョブの実行は、下の[実行(ジョブFirstJob)]タブの中の[実行]ボタンをクリックすると開始します。
Talend-run-job

実行結果が表示されます。
Talend-run-result

MySQLのテーブルに追加されたことも確認できました。
Talend-select-result

以下、参考情報

Talend Open Studio for DI_v5「2つのテーブルスキーマを統合して集計」
DB等利用のJava自動生成 Talend Open Studio その5-1:DB入力表示
DB等利用のJava自動生成 Talend Open Studio 5-2.CSV書き出し
Talend Open Studio でジョブを作成する(CSVからデータベースへ)

JDBC を使用して Windows Azure SQL データベースに接続する方法