7
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

BigQuery ScriptingでPythonっぽいループ処理をしてみた

はじめに

昨年(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)] # storesX番目の値を取り出す
  GROUP BY store_cd;

  SET x = x + 1;
END WHILE;

うん。なんか、昔からあるJAVAとかで使われそうなLoop処理ができました。

おわりに

ひとまず、BigQuery中だけで、ループ処理ができました。
そもそも、ARRAYをあまり使わないで書いていたので、その処理を使うのが骨が折れました。(できてみれば、簡単そうなのですが)

今後、条件分岐(IF文)とかにもチャレンジしていきたいと思います。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
7
Help us understand the problem. What are the problem?