BigQuery ScriptingがBetaリリースされたので軽くウォークスルーしてみる

Shinya Yaginuma

Published in

google-cloud-jp

10 min readOct 20, 2019

2019/10/3にBigQuery ScriptingがBetaになったとリリースノートに上がりました。

これによって、BigQueryで変数宣言やループ処理といった複雑な処理などができるようになりました。個人的には待ちに待った機能です。

テンションが上がったのでドキュメントをざっとウォークスルーしてみることにしました。ドキュメントはこちらです。

First BigQuery Script

最初にこちらのクエリを実行してみます。使っているテーブルがBigQueryのPublicテーブルなのでそのまま実行できます。

-- Declare a variable to hold names as an array.
DECLARE top_names ARRAY<STRING>;
-- Build an array of the top 100 names from the year 2017.
SET top_names = (
  SELECT ARRAY_AGG(name ORDER BY number DESC LIMIT 100)
  FROM `bigquery-public-data`.usa_names.usa_1910_current
  WHERE year = 2017
);
-- Which names appear as words in Shakespeare's plays?
SELECT
  name AS shakespeare_name
FROM UNNEST(top_names) AS name
WHERE name IN (
  SELECT word
  FROM `bigquery-public-data`.samples.shakespeare
);

それぞれのクエリは;で区切られるようです。ValidなSQLのリストがScriptとして定義されてます。

In BigQuery, a script is a SQL statement list to be executed in sequence. A SQL statement list is a list of any valid BigQuery statements that are separated by semicolons.

ここでは、次のステップでScriptが実行されていきます。

top_names変数を宣言
top_names変数にクエリの実行結果を代入
top_namesを呼んでクエリを実行

それでは実行してみましょう。初めての機能を使うのはわくわくしますね。

結果はこんな感じです。

通常のクエリを実行するとテーブル型で出力結果が表示されますが、Scriptingでは複数の出力結果が想定されているので、それぞれの出力結果を確認しに行けるようなUIになっているんですね。

ちなみにドキュメントにはこんな風に書かれています。

BigQuery interprets any request with multiple statements as a script, unless the statements consist of CREATE TEMP FUNCTION statement(s), with a single final query statement.

UDFの定義+1つのSELECT文でも複数のValidなSQLは含まれるますが、それではScriptにはならないようですね。それでは出力結果を確認してみましょう。一つ目のジョブの結果を表示してみます。

いつものBigQueryの実行結果が表示されました。加えて上の方を見ると、実行元クエリの一部っぽいものが表示されていますね。

次に個々の構文について見ていきましょう。

Declare

変数の宣言をします。文頭か後述するBEGINブロックの中にしか書けません。また、変数の容量制限があるので注意が必要です。

The maximum size of a variable is 1 MB, and the maximum size of all variables used in a script is 10 MB.

基本的にDECLAREで変数を宣言し、SETで変数に値を代入するのでSETとセットで使うことが多そうです。

それでは試しにいくつか例文を書いてみます。

DECLARE today DATE DEFAULT CURRENT_DATE();
SELECT today;

todayという変数を宣言しています。DEFAULTでデフォルト値を設定しています。デフォルト値が設定されていない場合はNULLで初期化されます。

このクエリを実行すると、CURRENT_DATEで初期化しているので今日の日付が返されます。

それでは、SETを使って別の日付を代入してみます。

DECLARE today DATE DEFAULT CURRENT_DATE();
SET today = '2019-01-01';
SELECT today;

today変数に2019/1/1を代入しています。実行結果を見ると、確かに値が代入されていることがわかります。

型が同じであれば、,区切りで同時に宣言ができます。型が違う場合は別のDECRALEを書いて宣言する必要があるようです。

DECLARE x, y, z INT64 DEFAULT 0;
DECLARE date DATE DEFAULT CURRENT_DATE();

SETでは型によらず,区切りでの複数同時代入ができます。

DECLARE x, y, z INT64 DEFAULT 0;
DECLARE date DATE DEFAULT CURRENT_DATE();
SET(x, y, z, date) = (1, 2, 3, '2019-01-01');SELECT x, y, z, date;

また、最初のスクリプトの用に、SELECT結果をそのまま変数に代入することもできます。

BEGIN, END

BEGINで始まりENDで終わるブロックの内部でのみ有効な変数を定義できるようです。具体的なユースケースをあまり思いつきませんでした。きっと使っているうちに必要な場面が出てくるのだと思います。（思いついたら追記します。）

こんな感じで書きます。

DECLARE x INT64 DEFAULT 10;
BEGIN
  DECLARE y INT64;
  SET y = x;
  SELECT y;
END;
SELECT x;

結果です。

IF

条件式の評価結果によって実行するSQLを変えられます。これはすごく便利そうです。それでは例文を見ていきましょう。

DECLARE target_word STRING DEFAULT 'exceeds';
IF EXISTS (
    SELECT 1 FROM `bigquery-public-data.samples.shakespeare`
    WHERE word = target_word) THEN
  SELECT 'found';
ELSE
  SELECT 'did not find';
END IF;

データセットに変数で宣言した単語は入っているかどうかを評価し、TRUEならfound, FALSEならdid not foundが出力されるScriptになっています。

実行結果を見ると、二つのジョブが実行されたことがわかります。それぞれ結果を見ていきましょう。

一つ目のジョブでは、条件式の評価がされ、trueが返されています。

二つ目のジョブでは、trueの処理に当たる部分が実行されています。多少タイプ量は多いですが、スクリプト型言語のような処理ができていることに驚きです。

また、ELSE IFのような条件に合わなかった場合の処理はIFをネストして書くようです。（ちなみにネストは50段までとのこと）

DECLARE target_word STRING;
IF EXISTS (
    SELECT 1 FROM `bigquery-public-data.samples.shakespeare`
    WHERE word = target_word) THEN
  SELECT 'found';
ELSE
  IF target_word IS NOT NULL THEN
    SELECT 'did not find';
  ELSE
    SELECT 'the target word is null!';
  END IF;
END IF;

これを利用して、軽いテストを書くこともできそうです。評価結果によってガラッと処理内容を変えられるのはすごく便利ですね。

LOOP

繰り返し処理もサポートされています。 LOOPで開始宣言、END LOOPで終了宣言をします。ループはLEAVEもしくはBREAKで抜けます。

DECLARE x INT64 DEFAULT 0;
LOOP
  SET x = x + 1;
  IF x >= 10 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;

配列のイテレーションみたいなよくやることはできなそうですが、N回ループを回すとかは実現できますね。ループ処理には他にもWHILEがありましたが、LOOPで内包できそうなので特に試しません。

また、pythonなど使っている方にはおなじみのCONTINUEで以降の処理をスキップして次のイテレーションを回すこともできます。

CALL

CALLを使って同じタイミングでBetaリリースされたStored Procedureを呼び出すことができます。ここはまたStored Procedureの別記事に書こうと思います。

まとめ

普段の分析用のクエリでは必要性の薄い機能かもしれませんが、Airflow, DigdagなどでSQLを定期実行するケースの増加に伴い可読性が高くメンテナンスコストの低いクエリが求められるようになってきていたり、複雑な加工をする際に条件分岐やループ処理を書きたいタイミングが出てきているように思います。

本記事で解説したBigQuery Scriptingは、そのようなニーズを解決できる有効な手段となると思います。早速筆者も普段の業務で使っていこうと思います！