【Bigquery】パーティションテーブルを動的に変更する方法
目的
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