yy16ki’s diary


スポンサードリンク

トップ > Talend > TalendでELT処理を実施する

TalendでELT処理を実施する

Talend ETLツール ELT ETL Snowflake

ETLツールとして有名なTalendですが、ELT処理を実施するためのコンポーネントが用意されています。データベース側の挙動もあわせて使用感を確認してみました。

前提条件

「Talend Open Studio for Data Integration」の「Version 8.0.1」で検証を実施しました。
ELT処理の対象となるデータベースには、Snowflakeを使用しました。 （Snowflake環境は構築済みとします。）

Snowflake側の準備

今回の検証用に、Snowflake側でデータベース・スキーマを定義します。

create database test;
create schema talend;

作成したスキーマに、統合処理に使うテーブルを定義します。

CREATE OR REPLACE TABLE STUDENT (
    ID VARCHAR(316),
    NAME VARCHAR(316)
);

INSERT INTO STUDENT (ID,NAME)
VALUES 
  ('C01','Shohei'),
  ('C02','Mike'),
  ('C03','Anthony'),
  ('C04','Jack'),
  ('C05','Max'),
  ('C06','Matt') ;

CREATE OR REPLACE TABLE TEST (
    ID VARCHAR(316),
    SUBJECT VARCHAR(316),
    SCORE NUMBER(38,0)
);

INSERT INTO TEST (ID,SUBJECT,SCORE)
VALUES 
  ('01','Math',60),
  ('01','English',70),
  ('02','Math',50),
  ('02','English',80),
  ('03','Math',70),
  ('03','English',80),
  ('04','Math',50),
  ('04','English',40),
  ('05','Math',80),
  ('05','English',30),
  ('06','Math',70),
  ('06','English',70)

下記の処理をTalendのELTコンポーネントで実装します。
SQLでいうところの、Join・Group By・Having句が入ってくるような処理を想定します。
１．「Student」テーブルと、「Test」をIDで結合（「Test」テーブルのID列の先頭に文字列'C'を付与し結合）
２．生徒ごとに全テストの合計点数を算出し、合計が130点以上の生徒を抽出

Talend側の処理

作成したジョブ

上記の処理をELTコンポーネントで、下図のように実装しました。

f:id:yy16ki:20220415184902p:plain
ジョブ全体図

Snowflakeとの接続

まずはリポジトリからSnowflakeの対象とするデータベースへの接続を作成します。

f:id:yy16ki:20220415183023p:plain
接続の作成

Snowflakeとの接続に必要な情報は下記の通りです。
Account：SnowflakeのコンソールのURLから取得
User ID：Snowflakeのコンソールへログインする際のユーザー名
Password：Snowflakeのコンソールへログインする際のパスワード
Warehouse：使用するウェアハウス（コンピュートエンジン）の名称
Schema：使用するスキーマの名称
Database：使用するデータベースの名称
Snowflakeのコンソールでは、URLのsnowflakecomputing.comの前に、アカウント情報・Snowflakeの構築に使用しているクラウドサービス情報が記載されています。Account欄にはこの情報を記載します。

f:id:yy16ki:20220415184344p:plain
アカウント情報

接続確認が取れたら、ジョブの作成に入ります。
ジョブ全体図のPrejob・Postjobの通り、データベースへの接続には各種の接続用コンポーネントを使用します。

tELTInputコンポーネント

ここから、ELT処理の実装に入ります。ELT処理を実施する際は、通常のInputコンポーネントではなく、tELTInputコンポーネントを使用します。今回は2つのテーブルを使用するので、2つ配置しています。 コンポーネントの設定は下図の通りです。

f:id:yy16ki:20220415185357p:plain
tELTInputコンポーネント：設定

スキーマ名をコンテキスト変数で設定し、テーブル名はそのまま入力しています。使用するテーブルのスキーマを定義し、MappingにMapping Snowflakeを指定します。

tELTMap

統合処理には、tELTMapコンポーネントを使用します。設定は下記の通りです。

f:id:yy16ki:20220415190417p:plain
tELTMapコンポーネント：設定
細かいですがポイントとなる箇所を①～⑤で示しています。それぞれの詳細を後述します。

①：結合条件の指定
今回の場合、TestテーブルのID列先頭に文字列を付与する必要があります。TestテーブルのID列をStudentテーブルのID列にドラッグアンドドロップします。

f:id:yy16ki:20220415190929p:plain
結合条件の指定

式ビルダを開き、下記の通り文字列の結合処理を定義します。

f:id:yy16ki:20220415191102p:plain
文字列結合処理

ここでは、concat()を使用し、TestテーブルのID列先頭に文字Cを付与しています。Talend Studioには無くても、Snowflakeで定義されていれば関数を使用することができます。
最後には、StudentテーブルのID列・NAME列も表示したいので、新しい出力を下記の通り作成しておきます。

f:id:yy16ki:20220415191737p:plain
出力の作成

②、③：集計処理
生徒ごとの点数合計を作成します。出力の設定で「Add an other (GROUP...) clause」を押下し、式ビルダを表示します。

f:id:yy16ki:20220415192044p:plain
GROUP BY句の追加

f:id:yy16ki:20220415192341p:plain
集計処理の式ビルダ

式ビルダには、下記のように入力します。

GROUP BY context.SCHEMA.STUDENT.ID ,context.SCHEMA.STUDENT.NAME

さらに今回は、集計後の値で絞り込みを行いたい（having句を記述したい）ので、再度、出力の設定から「Add an other (GROUP...) clause」を押下し、別の式ビルダを表示し下記の通り入力します。

HAVING SUM(context.SCHEMA.TEST.SCORE ) >= 130

入力後は下記のようになっているはずです。

f:id:yy16ki:20220415193413p:plain
集計処理：設定後

④，⑤：集計列の追加
集計した生徒の合計点数は、テーブルでも表示したいので、画面右下の出力スキーマの編集箇所より列を追加し、列名・データ型を指定します。

f:id:yy16ki:20220415193758p:plain
出力列の追加

スキーマを追加すると、出力欄にも列が追加されるので、式ビルダより下記のように入力します。

SUM(context.SCHEMA.TEST.SCORE )

f:id:yy16ki:20220415194143p:plain
集計列の追加

これで統合処理の設定は完了です。tELTMapコンポーネントの設定画面左下の「Generated SQL」タブを開くと設定した内容をもとに、SQL文が生成されています。この画面は随時更新されるので、ここを確認しながら設定を進めていくことになるかと思います。

f:id:yy16ki:20220415194446p:plain
生成されたSQL

"SELECT
" +context.SCHEMA+ ".STUDENT.ID, " +context.SCHEMA+ ".STUDENT.NAME, SUM(" +context.SCHEMA+ ".TEST.SCORE ) 
FROM
 " +context.SCHEMA+ ".STUDENT , " +context.SCHEMA+ ".TEST
WHERE
  " +context.SCHEMA+ ".STUDENT.ID = concat('C', " +context.SCHEMA+ ".TEST.ID) 
GROUP BY " +context.SCHEMA+ ".STUDENT.ID ," +context.SCHEMA+ ".STUDENT.NAME 
HAVING SUM(" +context.SCHEMA+ ".TEST.SCORE ) >= 130 
"

tELTOutputコンポーネント

作成した統合処理の内容を、データベースで実行する方法を指定します。
今回は、下記のように新規テーブルを作成することとしました。スキーマ同期を忘れないように注意してください。

f:id:yy16ki:20220415194845p:plain
tELTOutputコンポーネント：設定

Snowflakeのログを確認

エラー無くジョブを実行出来たら、Snowflake側でログを確認してみます。

f:id:yy16ki:20220415195522p:plain
Snowflakeログ：CREATE文

f:id:yy16ki:20220415195545p:plain
Snowflakeログ：INSERT文

生成した統合処理をもとに、新規テーブルが作成されていることが確認できます。データも確認します。

select * from TALEND_OUT ;

f:id:yy16ki:20220415195923p:plain
データの確認

合計が130以上の生徒のデータに絞り込まれていることを確認できます。

まとめ

ELT処理となると、多少SQLの知識が求められますが、GUIでスムーズに構築ができる点はTalendの特徴に感じました。また、Talendの機能は制限されてしまいますが、その代わりデータベースの関数をそのまま使用できるので、SQLに慣れている人からすると、この方がジョブも構築しやすいのではないでしょうか。
今回は機能を使ってみたのみで、ELTのメリットである、データベース側のリソースを使用することによるETLとの処理性能のような観点は見れていないですが、この点も確認してみると面白いかもしれません。
今回は以上になります。


スポンサードリンク