🍣

cloudbuildでデータセットを再作成してviewテーブルを定義する

2020/11/09に公開

Google Cloud

前提

GCPです

どういう要件の時に役立つのか

データ基盤/ガバナンスの観点

viewテーブル(viewじゃなくてもいいけど）の定義SQLをgitで管理したい
データセット内で個々人がレビューフローを経ずに作ったテーブルは自動的に削除される仕様にしたい

継続的デプロイの観点

mergeしたら最新版に更新されてほしい

どうやって実現するのか

GCPのcloudbuild(以下GCB)を使って実現します。AWSだとCodeBuildでしょうか。CircleCIでも良いかもしれません。ただBQを起点に何某かをやるならGCP使った方が楽だと思います。

コード全体は👇（次のセクションで各ファイルについて説明します。)

また、GCBのトリガーは以下のように設定して作成します。

各ファイルについて

create-view-dataset.yaml

GCBのトリガーに指定するファイルです。このファイルが特定のブランチへのpushを皮切りに実行されるなどの設定ができます。

create-view-dataset.yaml


steps:
  - name: "gcr.io/cloud-builders/gcloud"
    entrypoint: "bq"
    args:
      - rm
      - --dataset
      - --recursive
      - --force
      - ${_DATASET}
    id: "drop-view-dataset"

  - name: "gcr.io/cloud-builders/gcloud"
    entrypoint: "bq"
    args:
      - mk
      - --dataset
      - --description="viewテーブルを入れるデータセット"
      - --data_location=US
      - ${_DATASET}
    waitFor: ["drop-view-dataset"]
    id: "create-view-dataset"

  - name: "gcr.io/cloud-builders/gcloud"
    entrypoint: "bash"
    args: ["./cloudbuild/create-view-table.sh"]
    waitFor: ["create-view-dataset"]
    id: "create-view-table"

create-view-table.sh

このshをyamlの最後で実行し、再作成したデータセットに対して管理しているSQLを順番に実行していきます

create-view-table.sh

# /bin/bash

set -eu -o pipefail
for pathfile in sql/*.sql ; do
  echo $pathfile
  bq query --use_legacy_sql=false < $pathfile
done

SQLの例

CREATE OR REPLACE VIEW 構文を使う事でviewテーブルを作成します
descriptionなどを書いてあげるとテーブルによっては初見での理解度が上がるのでオススメです（いわゆるdata dictionary)
以下に記載のSQLは架空のSQLです。ご自身の用途に合わせて作り込んでください

user_first_orders.sql

CREATE OR REPLACE VIEW view.user_first_orders
OPTIONS (description="ユーザー情報に最初に注文した日時の情報を付け加えたテーブル", labels=[("git", "true")]) AS

WITH first_order_each_users AS (

SELECT
  user_id,
  MIN(created) AS created
FROM your_dataset.orders
GROUP BY user_id 

)

SELECT
  u.*,
  o.created AS first_ordered
FROM your_dataset.users u
LEFT JOIN first_order_each_users o
ON u.id = o.user_id

前提

どういう要件の時に役立つのか

どうやって実現するのか

各ファイルについて

Discussion