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

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

https://cloud.google.com/dataform/docs/overview?hl=ja

この記事では、公式データセット `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」を選択し、「リポジトリを作成」をクリックします。

![01.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/ea996258-d4a1-50f0-0d76-81c385b8ae87.png)


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

作成時指定したサービスアカウントに必要な権限を追加します。
「IAM」ページにアクセスし、サービスアカウントに
 - BigQuery ジョブユーザー
 - BigQuery データ編集者
 - BigQuery データ閲覧者

を付与します。

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

![02.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/aa3bf643-9549-3386-d584-1f81c32c7677.png)

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

![03.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/d15ff3d0-1946-43ac-2660-31161281f627.png)

ファイルツリーより `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` フォルダの「その他のファイル操作」より、「ファイルを作成」をクリックします。

![04.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/fe2261d4-a525-a992-de84-0ec404bbe9b7.png)

ファイル名に `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 のサンプルクエリ（↓画像より開くクエリ）を使いました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/b2b51d2a-3511-b8ab-e781-d5857255eb6d.png)

同様に `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つのファイルを作成したら、ヘッダーの「実行を開始」-「すべてのアクション」をクリックし、実行モーダルの「実行を開始」ボタンをクリックします。

![05.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/5133a27d-b756-9346-1508-4b6a3d6d7537.png)

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

![06.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/beabebe0-b0b8-aeb8-b70d-f2e8195b12c7.png)


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

![07.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/be5f32c1-a19d-f88f-3ec6-0290b3b9e628.png)


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

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

![08.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/fd3e2a77-a561-a105-4223-7074696a9e93.png)

スケジュールが実行されると「最終更新日時」欄が更新され、「名前」欄のリンクをクリックすると実行結果が確認できました。
![01.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/af96b221-9a4c-25b5-ee8f-5397746ad7a8.png)

![02.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/426544/836ac5d0-bad4-7eb3-b497-5527e0e7941b.png)



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

## 参考

https://dev.classmethod.jp/articles/20240418-dataform-entry/

https://cloud.google.com/dataform/docs/quickstart-dev-environments?hl=ja

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