どんな方法？

TROCCOで、文字列展開で行なっているカスタム変数ループについて、文字列展開している内容を一度テーブル化（＝永続化）しておくことで別のジョブでも使いまわせるようにします。

どんな時に使える？

サンプルケース

今回、次のような状況を考えます。

GA360のデータ¹を使って、ユーザ毎のPV数を月次で集計しているデータパイプラインがあります。集計テーブルはデータ提供先の都合で国ごとに別テーブルとなっています。

集計クエリ

CREATE OR REPLACE TABLE 
  `YOUR_PJ_ID.YOUR_DATASET.user_pv_by_country___$iso_country_code$` -- $iso_country_code$の値はパイプライン側から与えられる
AS

SELECT
  geoNetwork.country,
  fullVisitorId,
  COUNT(totals.pageviews) AS pageviews
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
  UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX BETWEEN '$start_date$' AND '$end_date' -- $start_date$, $end_date$の値はパイプライン側から与えられる
  AND hits.type = 'PAGE'
  AND geoNetwork.country = '$country$' -- $country$の値はパイプライン側から与えられる
GROUP BY
  1,2

このデータパイプラインに対して、集計ジョブのクエリをリファクタリングしました（以下、リファクタリング前のクエリを「既存クエリ」、後のクエリを「新クエリ」と呼ぶこととします）。実際に本番のジョブへ適用する前に既存クエリから差分が出ないことを確認したいです。

ただし、全テーブルに対して差分チェックを行うのはデータ量やテーブル数の多さから物理的に困難だったため、特定の1ヶ月について、特にデータ量の多い（＝差分が出てしまった場合に影響が大きい）上位5テーブルに絞って差分チェックを行うことになりました。

補足：理想としては…

今回挙げた状況の場合、事前に下記の改修を入れておくのが理想かと思います。

元々の集計ジョブの責務を「集計ジョブ」「データ提供ジョブ」へ分割
集計ジョブにて全国での集計テーブルを作成し、そこから国ごとにデータ提供を行う形式へ変更

全国での集計テーブルが存在していることで、今回のような差分チェックがやりやすくなります。

とはいえ、改修工数や優先度の問題で理想通りにはいかないこともあるかと思います。そういった場合に本記事の方法が役に立てば幸いです。

差分チェック用TROCCOワークフロー

1テーブルずつ差分チェックを行うのは大変なため、専用のワークフローをTROCCOで組んでみます。

単純に組むと次のようなワークフローになるかと思います。

3つのジョブのカスタム変数ループにはデータ量の多い上位5テーブル（＝5カ国）を指定しています。期間については適当な1ヶ月²を指定しています。

…が、3つのジョブに全く同じカスタム変数ループを設定する作業は少し面倒です。設定ミスを起こしやすいですし、比較対象を変更したくなった（5テーブル→10テーブルへ増やす、別の国へ変更する、など）際に面倒が増えてしまいます。

本記事の方法を使ってみる

そこで、カスタム変数ループで文字列展開している内容を一度テーブル化します。すると、これまで文字列展開で指定していた部分をBigQueryのクエリ結果で指定できるようになり、前述の課題を解消できます。

ワークフローは次のようになります。

カスタム変数指定用のジョブでは次のクエリを実行します。

CREATE OR REPLACE TABLE 
  `YOUR_PJ_ID.YOUR_DATASET.user_pv_by_country___diff_target`
AS

SELECT "us" AS iso_country_code, "United States" AS country
UNION ALL
SELECT "in", "India"
UNION ALL
SELECT "ca", "Canada"
UNION ALL
SELECT "uk", "United Kingdom"
UNION ALL
SELECT "de", "Germany"

カスタム変数ループの設定は次のようになります。

これで、差分チェックの対象を増やしたり変更したりしたい場合はカスタム変数指定用ジョブのクエリを変更するだけで簡単に対応できるようになりました。

まとめ

この記事では、文字列展開されたカスタム変数ループを複数のジョブで使い回す方法とそれが有用なサンプルケースについて紹介しました。

TROCCOは定期実行するデータパイプラインの構築だけでなく、本記事のようなデータパイプラインへのスポット検証にもサクッと使えるので、とても重宝しています。すでに動いているデータパイプラインだと柔軟な対応が中々難しいことも多いかなと思いますので、そういった部分をTROCCOでカバーできると良いのかなと思っています。

フリープランも用意されているので、検証用途での利用でも始めやすいかなと思います。

サービスページ:

フリープラン申込ページ:

ここまでお読みいただきありがとうございました！

今回はBigQueryの一般公開データセットのデータを使います ↩
今回は一般公開データセットにて公開されている2017年8月の1ヶ月間を使用しています ↩