【Talend Open Studio 入門05】データベース(SQLServer)を扱う。tMSSQLRowの使い方など

今回は、Talendでデータベースを扱います。
使用するコンポーネントは、SQLServerに関するものですが、コンポーネントが変わるだけで他のDBでも設定はほぼ同じです。
MySQLでもPostgreSQLでもOracleでも…同じように使うことができます。

今回も、今までと同じようにジョブを1つ作りながらやっていきましょう。

(以降データベースをDBと呼称します)

Youtubeでもレブル250/グラディウス400/GN125の情報を発信してます! (登録してもらえると超喜びます!!)

データベース連携コンポーネント

TalendにおけるDB連携は、主に3種類のコンポーネントを使用します。

tMSSQLConnection(DB接続)

DBに接続します。
このコンポーネントを使用しなくても、input部品の中で接続を書くことができますが、複数テーブルからレコードを取得する場合は、connectしておいた方が良いでしょう。

tMSSQLInput(レコード取得)

SQLを発行してレコードを取得します。
クエリはString文字列をセットする、という点だけ注意です。

tMSSQLOutput/tMSSQLRow(レコード挿入)

Talendで編集したレコードを、テーブルに挿入します。(tMSSQLOutput)
SQLのInsert文を発行して挿入を行いたい場合は、tMSSQLRowを使用してSQLを記述します。

この3種のコンポーネントは、前述の通り、他のDBでも同じコンポーネントが用意されています。

例えば、MySQLであれば、tMysqlConnectionなど、tMSSQLの部分が各DBの名称に変わるだけです。

今回作るバッチジョブの要件固め

[speech_bubble type=”std” subtype=”R1″ icon=”2.jpg” name=”支店長”]山田君!![/speech_bubble] [speech_bubble type=”std” subtype=”L1″ icon=”1.jpg” name=”山田”]はい。今回もデータ移行関連の話ですか?[/speech_bubble] [speech_bubble type=”std” subtype=”R1″ icon=”2.jpg” name=”支店長”]その通りだ。今までも、csvデータを項目編集して、csvデータに変換してもらったが、今度は、csvの販売実績データに、DBの品目マスタテーブルを紐付けて値を補完して、さらに、結果をDBの実績テーブルに挿入して欲しいんだ[/speech_bubble] [speech_bubble type=”std” subtype=”R1″ icon=”2.jpg” name=”支店長”]流石に…DBが絡むと…無理だろうか…?[/speech_bubble] [speech_bubble type=”std” subtype=”L1″ icon=”1.jpg” name=”山田”]問題ありません。Talendなら、20分もあればジョブを作れますよ[/speech_bubble] [speech_bubble type=”std” subtype=”R1″ icon=”2.jpg” name=”支店長”]す、素晴らしい!!来月から給料アップだ!宜しくたのむぞ![/speech_bubble]

会話をまとめると…

トランザクションデータ(csv)を、DBの品目マスタテーブルに紐づけていくつかの項目を補完した結果を、DBの実績テーブルに挿入するジョブを作ってくれ、ということらしいです。

もっと具体的に今回のジョブの要件を決めていきましょう。

  • 入力となるcsv実績データは、ID,ITEM_CD,SUURYO,TANNI,TANKAの項目を持つ
  • DBの品目マスタテーブルは、ITEM_CD, ITEM_NAME, ITEM_BUNRUIの項目を持つ
  • 出力となるDBの実績テーブルは、ID, ITEM_CD, ITEM_NAME, ITEM_BUNRUI, HANBAI_SUURYO, HANBAI_TANNI, HANBAI_TANKA, HANBAI_GAKU_KEI, HANBAI_USER_ID, HANBAI_DATE

出力の実績テーブルは下記の様にテーブル定義しておきます。

[sql]CREATE TABLE T_HANBAI_JISSEKI(
ID varchar(10)
,ITEM_CD varchar(10)
,ITEM_NAME varchar(50)
,ITEM_BUNRUI char(2)
,HANBAI_SURYO int
,HANBAI_TANI varchar(4)
,HANBAI_TANKA int
,HANBAI_GAKU_KEI int
,HANBAI_USER_ID varchar(10)
,HANBAI_DATE varchar(10)
)[/sql]

 

入力の品目マスタテーブルは、下記の様にテーブル定義しておきます。

[sql]CREATE TABLE M_ITEM (
ITEM_CD char(10)
,ITEM_NAME varchar(50)
,ITEM_BUNRUI char(2)
)
[/sql]

品目マスタテーブルにデータがないと始まらないので、下記の通りサンプルデータを入れておきます。

[sql]INSERT INTO M_ITEM
(ITEM_CD, ITEM_NAME, ITEM_BUNRUI)
VALUES
(‘0000000001′,’りんご’,’01’),
(‘0000000002′,’あめ’,’02’),
(‘0000000003′,’傘’,’04’),
(‘0000000004′,’みかん’,’01’),
(‘0000000005′,’もち’,’02’),
(‘0000000006′,’かがみ’,’04’),
(‘0000000007′,’コップ’,’03’),
(‘0000000008′,’歯ブラシ’,’03’),
(‘0000000009′,’すいか’,’01’),
(‘0000000015′,’メロン’,’01’),
(‘0000000031′,’パイナップル’,’01’)
[/sql]

入力とするcsvデータのサンプルはこんな感じにしておきます。

[text]ID,ITEM_CD,SUURYO,TANNI,TANKA
"0000000001","0000000001",3,"個",298
"0000000002","0000000004",11,"個",668
"0000000003","0000000001",2,"ダース",298
"0000000004","0000000015",1,"箱",2900
"0000000005","0000000009",13,"個",400
"0000000006","0000000031",7,"箱",1000
"0000000007","0000000004",4,"ダース",1200
[/text]

実装

それでは、実装を始めていきます!

今回は、1テーブルしか参照しませんが、接続⇒テーブル参照の形式でいきましょう。そちらの方が一般的なので。

DB接続

まずは、最初に書いた通り、tMSSQLConnectionコンポーネントを探してきて、ジョブに落っことします。

image56

このコンポーネントが走ると、DB接続が行われます。
コンポーネントタブを開いて、DB接続先設定を行っていきましょう。

  • ホスト
    • 接続先DBの存在するサーバを指定しますIPでもホスト名でもokです。
      今回は、ローカルにSQLServerをインストールしてあるので、接続先は127.0.0.1としています。
  • ポート
    • 接続先DBのポートです。特に設定を変えていなければ、1443ですね。
      セキュリティ上はポート番号を変えておいた方が望ましいですけど。
  • Schema(スキーマ)
    • スキーマ名を指定します。MySQLでは、スキーマ=DBらしいので、この項目は無いかもしれません。(こちらの記事を参照しました)
  • Database(データベース名)
    • 接続したいDB名を指定します。
  • ユーザ名/パスワード
    • まんまですね。接続のためのユーザ名/パスワードを指定します。

今回は、サンプルジョブですので、これらパラメータを直書きしていますが、実践ではコンテキストに外出しするのが望ましいと思います。
(コンテキスト(context)に関する記事はこちら⇒【Talend】グローバル変数の使い方(globalMapとcontext))

理由としては、実際の業務では、2か3ランドスケープの場合が多いので、contextでそれぞれに対応する環境変数を定義しておいて、各環境で実行時は、環境を切り替えるだけでDB接続先を変えられるようにするべきであるからです。

テーブルのレコード取得

テーブルのレコードを取得するためには、tMSSQLInputを使用しますので、パレットから探してきてジョブに落っことします。

image58

ついでに、一旦テーブルからのデータが取得できているかを確認するために、tLogRowも一緒に設置しておきます。

次に、コンポーネントタブで、レコード取得のための設定を行います。
既にコネクションを張っているので、その接続情報を利用するために、左上の「既存の接続先を使用」にチェックします。
すぐ右にコンポーネントリストで先ほどのDB接続部品が選択されていることを確認します。

クエリでテーブル名を記述しておくと、「テーブル名」のパラメータは特に与えなくても問題ないようです。

次に、一旦クエリに与えるパラメータを調整します。ここにSQL文を記述します。
クエリに与えるのはあくまで文字列ですので、ダブルクォーテーション囲みします。

次に、スキーマを定義します。

image59

ここはいつも通りにスキーマ定義します。
気を付けておくのは、Db Column名とDBタイプです。ここは、取得対象テーブルのものに合わせておきましょう。

ここまで設定できたら、tMSSQLConnectionとtMSSQLInputサブジョブを接続します。

image60

tMSSQLConnectionコンポーネントを右クリック⇒トリガ⇒On component OKで接続します。

一旦レコードが取得できているか確認

image61

正しくDB接続ができてSQLに問題がなければ、上記の様にtLogRowで取得結果が吐かれるはずです。

tMapでトランデータとマスタデータを紐付けする

テーブルからのデータ取得が問題無ければ、今度はcsvデータからトランザクションデータを取得し、tMapでマスタデータを紐付けします。

image62

tMap、tFileInputDelimitedコンポーネントをパレットから検索し、配置します。
tFileInputDelimitedは、サンプルcsvデータを読み込むように設定します。
(tFileInputDelimitedの設定については、こちらの記事をご覧ください⇒【Talend Open Studio 入門02】CSVデータを変換する。Talendの使い方からはじめます。)

tFileInputDelimitedの設定が終わったら、tMapに接続します。
また、tLogRowはもう不要なので、消してしまいました。
代わりに、tMSSQLInputをtMapに接続します。

要件として、csvデータ側がトランザクションデータで、こちらをメインストリームとしておきましょう。
画像と同じように、csvデータ側のRowの下にMainと表示されていれば、今回のケースでは合っています。
もし、これを変えたい場合は、「Row2(Main)」などと表示されている部分で右クリック⇒「この接続をMain(又はLookup)として設定」をクリックすれば、MainとLookupが切り替わります。

Mainというのは、tMap内で必ず参照する行、
Lookupというのは、参照側を意味し、tMap内ではMainのキーに一致しなかった場合、無視又はリジェクトする行です。

tMap内の設定を行っていきます。
(tMapの詳しい設定はこちらの記事をご覧ください⇒【Talend Open Studio 入門03】tMapでマスタデータを紐づける方法)

talend_db_tmapconf

Main、Lookupの設定が先ほどの通りになっていると、row2のcsv側が上に、row1が下に来ているはずです。
row1側にキー設定をしておきます。
ITEM_CDによって紐づけます。

次に、出力側の定義を行いますので、out1を定義、スキーマを定義していきます。
要件の通り、ID, ITEM_CD, ITEM_NAME, ITEM_BUNRUI, HANBAI_SUURYO, HANBAI_TANI, HANBAI_TANKA, HANBAI_GAKU_KEI, HANBAI_USER_ID, HANBAI_DATEを定義していきます。

次に、Varにて合計金額(GOKEI_KINGAKU)を計算しておきます。
(Varに関する記事はこちら⇒【Talend Open Studio 入門04】tMapのフィルタリング機能とVarの使い方)

ここまでできたら、row2, row1のデータをout1に、VarのGOKEI_GAKUをout1のHANBAI_GAKU_KEIにセットします。

 

tMapの設定まで無事完了したら、ここまで動作を確認しておきます。tMapの出力をtLogRowに繋げます。

talend_db_tmptlog

正しくログ出力されていることが確認できれば次に進みます。

tMSSQLOutputでレコード挿入

ここまでくれば、後は簡単です。
tMSSQLOutputコンポーネントを配置して、テーブルに編集後レコードを挿入します。

image65

パレットからtMSSQLOutputコンポーネントを検索し、ジョブに落っことします。
コンポーネントタブで各種設定を行います。

  • 既存の接続を使用
    • tMSSQLConnectionコンポーネントで接続済ですので、チェックしておきます。コンポーネントリストに表示されている名称が想定しているDBと一致していることを確認します(今回は1個しかないのでデフォルトでok)
  • テーブル
    • 挿入対象のテーブル名を設定します
  • テーブル操作
    • テーブルが無かった場合に、CREATEするかなどのオプションを指定します。今回は既に作ってあるので「何もしない」としておきます

スキーマは、デフォルトでtMapからのスキーマが引き継がれます。

が、Output側のDBタイプが抜けていて警告マークが付いてしまうことがありますので、忘れずに設定しておきます。
Talendで定義した型がそのままDBの型と一致しているのであれば、右下のDBマーク(保存フロッピーアイコンの左隣)で自動設定もできます。

コミット

ここまで定義したら、最後にコミットします。

image67

tMSSQLCommitというコンポーネントをパレットから探してきてジョブに落っことします。
このコンポーネントを配置しないと、コミットされません。

以上までの設定が上手くいっていれば、正しくテーブルにレコードが挿入されるはずです。

テーブル挿入をtMSSQLRowに置き換える

以上までで、テーブル挿入まで完了してはいるんですが、場合によってはtMSSQLOutpuが使用できないことがあります。(複雑なInsertをしないといけない時)

そういったケースでは、tMSSQLRowを使用して、SQL文によるInsertを行います。
今回作成したジョブではあまり意味はありませんが、下記のように置き換えることができます。

talend_db_mssqlrow

INSERT文をクエリパラメータに渡します。
クエリはStringですので、ダブルクォーテーションンで囲みつつ、バインドしたい項目などは、文字として + 演算子でくっつけていきます。

お気づきの方もいると思いますが、このINSERT文は、1Rowごとに発行されますので、正直イケてないです。
1度のINSERTで済ませたいなら、tJavaRowなどでVALUES句の中身をRowごと作成し、globalMapにいったん保存の後、
次のサブジョブでtMSSQLRowすれば1度のINSERT発行のみにするべきですね。

今回は単に置き換え、といいうことなので上記の様にしてあります。はい。

まとめ

  • TalendでDBを扱いたい場合は、tXXXConnection/Input/Output/Rowのコンポーネントを使用する。(XXXにはDBのプレフィックスが入る)
  • 編集したRowをそのままInsertできるならtXXXOutputで、SQLを書いてINSERTした方が良い場合はtXXXRowコンポーネントを使用する
Youtubeでもレブル250/グラディウス400/GN125の情報を発信してます! (登録してもらえると超喜びます!!)
最新情報をチェックしよう!