はじめに

昨年(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文）とかにもチャレンジしていきたいと思います。