Zenn
🌍

【Bigquery】パーティションテーブルを動的に変更する方法

2022/03/16に公開
初心者
BigQuery
#
dwh
#
動的sql
#
パーティションテーブル
tech

目的

Bigqueryで作成したパーティションテーブルを、動的に変更したい。

Bigqueryに登録しているパーティションテーブル名末尾の企業IDを変数化し、任意の企業の店舗別売上をBigqueryの動的SQLで表示すること。

前提

・GCPアカウント接続及び、パーティションテーブル作成済み
・スタースキーマ、パーティションテーブルをある程度把握している状態
・リージョンはUSを使用
・（BIツールであるmetabseを使って表示を行う）

対象読者

・プログラミング初心者
・SQL初心者
・Bigquery初心者

パーティションテーブル構造

テーブル構造

こちらはデータウェアハウス(DWH)のテーブル構造として一般的な、スタースキーマで作成しています。簡単に説明すると、リレーショナルデータベース(RDB)のように中間テーブルや細かいテーブルを作成しない = 分解しすぎないテーブル構造です。

上添付画像では、store_masterというテーブルと、salse_results_company_xxという企業単位で作成したテーブルの中に、作成時間(created_date)ごとの時間分割されたパーティションテーブルが作成されていきます。xxには1, 2, 3などのcompany_idが入ります。

このcompany_idを動的に変更することが目的！

ちなみに、Bigqueryで最も不安な要素であるのが課金だと思いますが、パーティションやクラスタリングという特有の構造をもつことで、参照するデータ量を抑えることができます。今回は、created_dateに沿ったパーティションテーブルを作成しています。

※初心者のため、最初は動的・静的の意味がよく分からなかったのですが、ユーザーごとにみたいページを切り替えたいときは動的、全ユーザー共通のページは静的という表現が一番しっくり来ています......

実際のクエリ文

Language: SQL

DECLARE company_id INT64 DEFAULT 38;

EXECUTE IMMEDIATE format("""
SELECT
    sales_results
    ,  look_ahead_sales_results
FROM `sample-data`.test.sales_results_company_%d as src
    LEFT JOIN `sample-data`.test.store_master as sm on sm.store_key = src.store_key
WHERE sm.store_id = 445 and
    _PARTITIONTIME >= DATE_SUB(DATE_TRUNC(CURRENT_TIMESTAMP, DAY), INTERVAL 1 DAY)
""", company_id);

解説

DECLARE company_id INT64 DEFAULT 38;
DECLAREで指定した型の変数宣言を行う。conpany_id(変数) = 38(数値型)を指定している。

EXECUTE IMMEDIATE format(""" select文 """, company_id)
動的SQLを実行する構文。format内に書かれたクエリ文はSTRING型にフォーマットされる。その中で、%d = 数値型, %s = 文字列などの引数を指定をすることが可能。DECLAREされた変数をformatの第1引数に代入している。

'sample-data'.test.sales_results_company_%d as src
formatの引数を代入する箇所に %dを使用。プロジェクト名.データセット名.テーブル名で呼び出す。

_PARTITIONTIME >= DATE_SUB(DATE_TRUNC(CURRENT_TIMESTAMP, DAY), INTERVAL 1 DAY)
パーティションの時間は、データが作成された日ごとに行っている。CURRENT_TIMESTAMP = 2022-03-16 15:00:01の時、上記の結果は2022-03-15 00:00:00 UTCと表示され昨日分の数値が表示される。

metabaseで使用したクエリ文

Language: SQL

DECLARE company_id INT64 DEFAULT {{company_id}};

EXECUTE IMMEDIATE format("""
SELECT
    sales_results
    ,  look_ahead_sales_results
FROM `sample-data`.test.sales_results_company_%d as src
    LEFT JOIN `sample-data`.test.store_master as sm on sm.store_key = src.store_key
WHERE sm.store_id = {{store_id}} and
    _PARTITIONTIME >= DATE_SUB(DATE_TRUNC(CURRENT_TIMESTAMP, DAY), INTERVAL 1 DAY)
""", company_id);

metabaseでの変数化は{{ }} で囲うことで指定できる。この時company_id、store_idはmetabase側のフィルタで変数代入を行っている。

まとめ

Bigqueryで動的SQLを書く際に、日本語の記事が少なかったため記録に残しました。私はMySQLをメインに操作することが多かったため、Bigquery特有の書き方になれるのに少し手間取りました。ただ、そこはさすがGoogleということで、公式ページが充実していたため、目的の箇所にたどり着ければ問題はあまりなかったですね。

適宜指摘などいただけると助かります！

参照記事

・ユーザーを笑顔にする BigQuery の使いやすい SQL 新機能
・スクリプト ステートメント
・FORMAT
・スタースキーマ(基礎)

Discussion