BigQuery Dataform でワークフローを作成する

BigQuery Dataform とは

BigQuery Dataform とは、BigQuery で行うワークフローの開発・バージョン管理・スケジュール設定などを行うためのサービスです。依存関係による実行順序の制御やグラフによる可視化などができます。

この記事では、公式データセット bigquery-public-data.google_trends を使って

• テーブルとビューを作成する
• 作ったワークフローのスケジュールを設定する

をやってみました。

検証

今回作成するワークフローは、2つの工程を実施します。

1. bigquery-public-data.google_trends.top_terms と bigquery-public-data.google_trends.top_rising_terms から過去2週間のキーワードと日付を取得し、ビューを作成する
2. 1. で取得した日付をキーにして、日付・top_terms・top_rising_terms のテーブルを作成する

環境作成

BigQuery のメニューから「Dataform」を選択し、「リポジトリを作成」をクリックします。

公式データセット bigquery-public-data.google_trends は US にあるので、リポジトリのリージョンを US 内に指定します。その他の設定はデフォルトの値を使いました。

作成時指定したサービスアカウントに必要な権限を追加します。
「IAM」ページにアクセスし、サービスアカウントに

• BigQuery ジョブユーザー
• BigQuery データ編集者
• BigQuery データ閲覧者

を付与します。

BigQuery の Dataform ページに戻り、作成したリポジトリ名をクリックし、「開発ワークスペースを作成」をクリックします。
作成モーダルでは任意のワークスペースIDを指定します。

作成したワークスペース名をクリックし、「ワークスペースを初期化」ボタンをクリックします。
完了すると左側にファイルツリーが表示されます。

ファイルツリーより workflow_settings.yaml を開きます。ワークフロー実行時に作成されるテーブル・ビューの格納先データセット名を defaultDataset で指定します。

workflow_settings.yaml

defaultProject: <プロジェクトID>
defaultLocation: US
defaultDataset: dataform_google_terms
defaultAssertionDataset: dataform_assertions
dataformCoreVersion: 3.0.0-beta.4

ワークフローの作成

definitions フォルダに実行したい SQL を作成します。

1. 公式データセットからビューを作成する

まず bigquery-public-data.google_trends.top_terms テーブルに実行するクエリを作成します。

ツリーの definitions フォルダの「その他のファイル操作」より、「ファイルを作成」をクリックします。

ファイル名に definitions/top_terms_view.sqlx を入力し、ファイルを作成します。
作成したファイルに以下クエリを記述します。

top_terms_view.sqlx

config {type: "view"}

-- This query shows a list of the daily top Google Search terms.
SELECT
   refresh_date AS Day,
   term AS Top_Term,
       -- These search terms are in the top 25 in the US each day.
   rank,
FROM `bigquery-public-data.google_trends.top_terms`
WHERE
   rank = 1
       -- Choose only the top term each day.
   AND refresh_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK)
       -- Filter to the last 2 weeks.
GROUP BY Day, Top_Term, rank
ORDER BY Day DESC
   -- Show the days in reverse chronological order.

クエリの実行結果をビューで持ちたいので、1行目の config { ... } で view を指定しました。
3行目以降の SELECT 文は BigQuery のサンプルクエリ（↓画像より開くクエリ）を使いました。

同様に definitions/top_rising_terms_view.sqlx ファイルを新規作成し、bigquery-public-data.google_trends.top_rising_terms テーブルに実行するクエリを記述します。

top_rising_terms_view.sqlx

config {type: "view"}

-- This query shows a list of the daily top Google Search terms.
SELECT
   refresh_date AS Day,
   term AS Top_Rising_Term,
       -- These search terms are in the top 25 in the US each day.
   rank,
FROM `bigquery-public-data.google_trends.top_rising_terms`
WHERE
   rank = 1
       -- Choose only the top term each day.
   AND refresh_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK)
       -- Filter to the last 2 weeks.
GROUP BY Day, Top_Rising_Term, rank
ORDER BY Day DESC
   -- Show the days in reverse chronological order.

2. 作成したビューからテーブルを作成する

1.で作成したビューの Day カラムをキーにし、Day・top_terms・top_rising_terms のテーブルを作成します。
definitions/terms_table.sqlx ファイルを新規作成し、以下クエリを記述します。

terms_table.sqlx

config {type: "table"}

SELECT
    top_terms_view.Day,
    top_terms_view.Top_Term,
    top_rising_terms_view.Top_Rising_Term
FROM
    ${ref("top_terms_view")} as top_terms_view
    INNER JOIN ${ref("top_rising_terms_view")} as top_rising_terms_view
    ON top_terms_view.Day = top_rising_terms_view.Day

今回はテーブルを作成したいので、1行目は config {type: "table"} を指定します。
前述の .sqlx ファイルで作成したビューやテーブルを、${ref("<ファイル名>")} で指定できるので、実際のビュー名を気にせず実装できます。

3つのファイルを作成したら、ヘッダーの「実行を開始」-「すべてのアクション」をクリックし、実行モーダルの「実行を開始」ボタンをクリックします。

ヘッダーの「COMPILED GRAPH」タブを見ると、3つのファイルの依存関係がグラフ化されています。
今回の場合、terms_table は top_terms_view と top_rising_terms_view を参照しているので、2つのビューからテーブルに向って矢印が伸びています。

ワークスペース一覧に戻り、「WORKFLOW EXECUTION LOGS」タブを見ると、実行結果のログが表示されます。

3. 作成したワークフローの実行をスケジューリングする

作成したワーフフローをスケジューリングするには、編集したファイルを commit・push します。

ファイルツリーのすぐ上の「5件の変更を COMMIT」ボタンをクリックし、コミットログを入力してコミットを行います。
その後、「PUSH TO DEFAULT BRANCH」ボタンよりコミットをプッシュします。
プッシュが完了したら、ワークスペース一覧に戻り「リリースとスケジュール」タブを開きます。
「製品版リリースの作成」ボタンをクリックし、デフォルト値のまま「作成」ボタンをクリックすると、7時に実行するスケジュールが作成されます。

スケジュールが実行されると「最終更新日時」欄が更新され、「名前」欄のリンクをクリックすると実行結果が確認できました。

使ってみて

始める前は sqlx ファイルを使うのに抵抗感があったのですが、今回のような簡単なクエリであればそこまで難しくなかったです。
一つの SQL クエリを1ファイルに管理し、それがグラフ化されるので、ワークフローの全体像の把握がしやすく、チーム開発や大規模開発に向いていると感じました。
Google Cloud Next '24 で、同じくワークフロー開発のための機能 BigQuery Workflows が発表されたので、Dataform との違いを体験するのが楽しみです。

参考

以上です。
どなたかの参考になれば幸いです。