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

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

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

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

Youtubeでもレブル250/グラディウス400/GN125の情報を発信してます！

hidetasoさんはバイク好きっ!ちゃんねる

(登録してもらえると超喜びます!!)

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

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

tMSSQLConnection(DB接続)

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

tMSSQLInput(レコード取得)

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

ｔMSSQLOutput/tMSSQLRow(レコード挿入)

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

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

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

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

会話をまとめると…

トランザクションデータ(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

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

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

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

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

実装

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

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

DB接続

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

このコンポーネントが走ると、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を使用しますので、パレットから探してきてジョブに落っことします。

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

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

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

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

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

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

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

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

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

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

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

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

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でマスタデータを紐づける方法)

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に繋げます。

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

tMSSQLOutputでレコード挿入

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

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

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

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

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

コミット

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

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

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

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

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

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

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の情報を発信してます！

hidetasoさんはバイク好きっ!ちゃんねる

(登録してもらえると超喜びます!!)