社内に散逸する各サービスのデータを統合する手法はETLが一般的です。
ETLは
- E：Extract（抽出）
- T：Transformation（変換）
- L：Load（格納）
の3アクションに由来しており、データを抽出して分析用に加工し、DWHへ統合することを指しています。
ETLはデータの抽出と同時に分析用の加工も行うため、近年はデータ量の爆発的な増加に伴い、ETLではデータの転送処理が追いつかなくなってくるケースが発生しつつあります。
そこでETLの課題を補うために考案されたのがELTです。
ELTは一旦データをほぼ無加工でDWHへETLし、DWH上で分析用の加工を行います。
ETLもELTも分析用のデータを整えるという目的は同じですが、処理の順番が異なるという違いがあります。
ELTは一次データである生データの転送とデータマートに分類される分析用のテーブル作成の2段階に分かれており、DWHの処理能力を活かして分析用のデータ加工を行います。そのため、ETLと比べ大きなデータの加工にも強いのが特徴です。
詳細はこちらの記事で解説しているのでぜひご覧ください。
【IT用語】ELTとは？ETLとの違いやメリット、活用に成功した事例を解説
データマートとは？種類やメリット、データウェアハウスとの違いをわかりやすく解説
一方、ELTを実装する際はETLツールでデータをDWH上へ統合した後にDWHのコンソール画面からSQLを利用してデータを加工する必要があり、運用面では少し手間がかかるという特徴もあります。
そこで今回はデータ分析基盤構築サービスtrocco®を利用し、DWHのコンソール画面を触ることなくデータELTを実装するとともに、サンプルデータのETLから分析用のデータマート作成（ELT）という一連の流れを自動化させてみます。
最新のデータエンジニアリング手法に興味がある方はぜひご覧ください。
今回のゴール
trocco®の機能のみを利用してデータのELTを実装します。
今回はinvesting.comと日経平均プロフィルよりダウンロードしたサンプルデータを利用し、Google BigQuery上に擬似的な分析用のデータマートを作成してみます。
同時に、作成したデータマートを用いてLooker Studio上でデータの可視化も行ってみましょう。
trocco®でサンプルデータをETL
サンプルデータをGoogleスプレッドシートにインポートし、Google BigQuery上にETLします。
事前準備として、trocco®のアカウントや分析したいデータ、DWHが必要です。用意がない場合は事前にご準備ください。
trocco®はクレジットカード登録不要のフリープランも提供しています。まだ登録されていない方はぜひご利用ください。
ETLの具体的な手順はこちらの記事でくわしく解説しているので参考にしてください。
trocco®を利用してGoogleスプレッドシートのデータをGoogle BigQueryへ統合、Tableauで可視化する。
なお、trocco®は多くのサービスのETLに対応しており、元データはGoogleスプレッドシートである必要はありません。
セールス部門で使用するSalesforceやMAツールのMarketoなど多様なサービスからのデータETLが可能です。
ETLジョブを設定・実行し、Google BigQueryのコンソール画面から確認してみると、たしかにデータが転送されていることが確認できます。
ただしここで転送したデータは別々のルートで取得したデータなので形式がバラバラです。
たとえば、年月の書式は「YYYY/MM/」と「YYYY-MM」「YYYY年MM月」などがバリエーションとして考えられます。
異なる日付の形式を同じデータとして扱うには、分析の前にどれかひとつに統一しておく必要があります。
そのほか、単位や桁を揃えて4つのデータを統合し、分析用のデータマートを作成しましょう。
trocco®でデータマート定義（ELT）
いよいよ本題のELTをやってみます。
まずはトップページからデータマート定義をクリックします。
右上の「新規データマートを作成」からデータマートを作成します。
trocco®では3つの主要なDWHでのELTに対応しています。
今回はデータのETL先にGoogle BigQueryを利用したので、ELTでもGoogle BigQueryを利用します。
ETLジョブと同様にこのELTジョブに名前を設定します。
データマート定義の作成では2種類のクエリ記述モードが選択できます。
ごく簡単なデータの抽出程度であれば、「データ選択モード」を、複雑なデータの加工には「自由記述モード」を選択しましょう。
「データ転送モード」ではデータの抽出先をプルダウンで選択することも可能です。
クエリを記述したらELTジョブを実行します。
下の画面のように表示されれば成功です。
Looker Studioでの可視化
ELTジョブによって作成したデータマートを使用し、Looker Studio上でデータの可視化をやってみます。
Google BigQueryのコンソール画面から「エクスポート」 → 「Looker Studioで調べる」を選び、データマートとLooker Studioを接続させます。
Looker Studioの画面に遷移します。
画面右側に読み込んだデータソースがあるので、それらを配置していきます。
また、適切なディメンション、指標（メジャー）を配置してグラフを選択することで簡単にデータを可視化できます。
グラフを配置し、「テーマとレイアウト」からお好きな見た目を選択してグラフの完成です。
trocco®でレポートの自動化
これまでの工程はいずれも手動でデータETL、ELT（データマート作成）を行いました。
日々新たに生成されるデータをその都度転送するのはあまりにも非効率です。
そこで「カスタム変数機能を利用した差分の追従」「ワークフロー機能を利用した一連の転送の自動化」を行います。
カスタム変数の埋め込み設定
作成したデータの転送ジョブのひとつを編集し、カスタム変数の設定を行います。
trocco®には、転送ジョブの実行時点を基準に、日時のカスタム変数を作成する機能があります。これを利用し、画像のように「一日前」と「今日」を返すカスタム変数を作成します。
最後に作成したカスタム変数をデータの取得期間の項目に埋め込みます。
このように日時に関するカスタム変数を埋め込むことで、人の手でデータの取得期間を設定しなくてもデータが転送された時点を基準に一日前のデータを自動的に取得するという差分転送の設定ができました。
同様に残りの転送ジョブ、データマート定義にもカスタム変数の設定を行います。
毎回の転送ですべてのデータを洗い替え転送する全件転送も設定可能ですが、このような差分転送では必要なデータのみを転送するため、DWHのパフォーマンスを下げずに運用することができます。また全件転送と比べ運用コストも抑えることができるため、大規模なデータ基盤であるほど差分転送を意識することが大切です。
ワークフロー定義の設定
trocco®のワークフロー機能、カスタム変数機能を用いてこの一連の流れをすべて自動化します。
trocco®のトップページに戻り、「ワークフロー定義」をクリックします。
作成したワークフローが一覧で表示されますので、右上の「新規ワークフロー作成」をクリックします。
ワークフロー機能は複数の転送ジョブをひとまとめにして管理・実行する機能です。
後の工程で定める流れに沿ってワークフロージョブが実行されますが、途中のジョブで何かしらのエラーが起きてしまったケースなどを考え、設定を行います。
設定が済んだらフロー編集画面に進みましょう。
フロー編集画面ではこの緑枠に囲まれた各項目をフローチャートに配置することでワークフローを組み立てることができます。
これまでの流れに沿って、まずは「trocco転送ジョブ」から4つの転送ジョブをフローチャートに配置します。
ジョブはチャート左の「START」から順番に実行されていきますが、このままだと一つ転送のみでワークフローが停止してしまうので、4つのジョブを順番につないでいきます。
これで4つの転送ジョブを自動で順番に実行するワークフローが設定できました。
DWH側への負担を考慮する必要はありますが、複数のジョブを並列で実行するようなフローも設定できます。
最後に転送したデータからデータマートを作成するELTジョブを配置して完成です。
並列に実行した4つの転送ジョブの終点をデータマートの始点につないでいるため、4つの転送ジョブが完了し次第ELTジョブが実行されます。
trocco®はSlackとの連携にも対応しており、ワークフローの成功/失敗の際に通知を出すことも可能です。
ワークフローの設定ができたらそれを保存し、最後にスケジュール設定を行います。
スケジュール設定を行うことで、これら一連のワークフローを自動で実行できるようになります。スケジュールは毎日・毎週・毎時間など設定できます。転送したいデータの更新頻度に合わせてスケジュールを設定しましょう。
ただし、差分転送を上手く機能させるには各転送ジョブに埋め込んだカスタム変数とスケジュールの設定に注意が必要です。
まとめ
いかがでしたか。
trocco®を利用することでDWH（今回はGoogle BigQuery）のコンソール画面に触れることなくデータのETL＆ELTを行い、データ分析用のデータマートを作成できます。
また、複数の転送ジョブを一つのワークフローとしてまとめ、スケジュール設定を行うことでデータ分析までのデータフローを自動化できます。
今回はごく小さなサンプルデータを利用したフローでしたが、複数のサービスからデータを取得する大規模なデータ基盤ほどワークフロー機能の恩恵は大きくなります。
trocco®では、クレジットカード不要のフリープランをご案内しています。データ統合にご興味がある方はぜひこの機会に一度お試しください。
