はじめに
昨年(2019年)の10/3に、BigQuery ScriptingがBetaでリリースされました。
上手く使えばPythonの処理とか、置き換えられるんじゃない？と意気込んだのですが、そこまでプログラミングが得意でない私（＋チーム）には中々使い所が分からず。
実際に使ってらっしゃる「BigQuery ScriptingがBetaリリースされたので軽くウォークスルーしてみる」とかを見ても、使い所が難しい。
そんな苦節数ヶ月、やっとPythonっぽいループ処理ができたので、共有させていただきます。
BigQuery Scriptingとは？
BigQueryは、ご存知の通りSQLの処理を実行できるのですが、その実行するSQLを外部変数によって条件分岐したり、ループしたりしたいことありますよね。
通常であれば、それをPython等の別の言語で呼び出してSQL処理を分岐・繰り返しをするのですが、そこまでBigQueryに組み込んでしまおうというのが、Scriptingです。（多分）
使える処理は、リファレンスの「標準 SQL のスクリプト」が最も分かりやすいですが、次のような処理があります。（代表例）
- DECLARE : 変数の宣言
- SET : 変数への値代入
- IF~ELSE~END IF : 条件分岐
- LOOP / WHILE : ループ処理
やりたいこと
例えば、店舗別でSQLを回して売上合計を別々のテーブルに出したい時、PythonからBigQueryを呼び出すと下記のようにできます。
for store in stores:
query=f"""
SELECT
store, SUM(購入金額) AS 合計金額
FROM `myproject.mydataset.transaction_*`
WHERE store = {store_cd}
GROUP BY store
"""
BigQuery Scriptingでも、Loop処理があるので、こんなの簡単にできるでしょ。と思っていました。
しかし、「BigQuery ScriptingがBetaリリースされたので軽くウォークスルーしてみる」にもあるのですが、
Pythonのループでやるような配列のイテレーションみたいなことはできません。
それをなんとかやりたかったです。
やったこと
何かできないかと、色々試行錯誤しながらやっとできたのが、次の処理です。
# 変数の宣言
DECLARE stores ARRAY<STRING>; # stores という配列を作ってループを回します
DECLARE x INT64 DEFAULT 1; # stores で使う引数（デフォルト値＝1）
# 変数への代入
# ARRAYの値（店舗一覧）を自動的に作りたかったので、ARRAY_AGGで作成
SET stores = (
SELECT ARRAY_AGG(store_cd) as list
FROM (SELECT store_cd FROM `myproject.mydataset.mytable` GROUP BY store_cd ORDER BY store_cd)
);
# ループ処理
# storesの長さまで繰り返し
WHILE x <= array_length(stores) DO
SELECT store_cd, SUM(購入金額) AS 合計金額
FROM `myproject.mydataset.mytable`
WHERE store_cd=stores [ORDINAL(x)] # storesのX番目の値を取り出す
GROUP BY store_cd;
SET x = x + 1;
END WHILE;
うん。なんか、昔からあるJAVAとかで使われそうなLoop処理ができました。
おわりに
ひとまず、BigQuery中だけで、ループ処理ができました。
そもそも、ARRAYをあまり使わないで書いていたので、その処理を使うのが骨が折れました。（できてみれば、簡単そうなのですが）
今後、条件分岐（IF文）とかにもチャレンジしていきたいと思います。