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

Shinya Yaginuma
Oct 20, 2019 · 10 min read
Image for post
Image for post

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が実行されていきます。

  1. 変数を宣言
  2. 変数にクエリの実行結果を代入
  3. を呼んでクエリを実行

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

Image for post
Image for post

結果はこんな感じです。

Image for post
Image for post

通常のクエリを実行するとテーブル型で出力結果が表示されますが、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にはならないようですね。 それでは出力結果を確認してみましょう。一つ目のジョブの結果を表示してみます。

Image for post
Image for post

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

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

Declare

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

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

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

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

DECLARE today DATE DEFAULT CURRENT_DATE();
SELECT today;

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

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

Image for post
Image for post

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

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

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

Image for post
Image for post

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

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

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

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;
Image for post
Image for post

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

BEGIN, END

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

こんな感じで書きます。

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

結果です。

Image for post
Image for post
Image for post
Image for post

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;

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

Image for post
Image for post

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

Image for post
Image for post

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

Image for post
Image for post

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

また、のような条件に合わなかった場合の処理はをネストして書くようです。(ちなみにネストは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

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

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

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

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

CALL

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

まとめ

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

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

google-cloud-jp

Google Cloud Platform…

Shinya Yaginuma

Written by

Data Analyst @ mercari

google-cloud-jp

Google Cloud Platform 製品などに関連するコミュニティが記載したテクニカル記事集。掲載された意見は著者のものであり、必ずしも Google のものを反映するものではありません。

Shinya Yaginuma

Written by

Data Analyst @ mercari

google-cloud-jp

Google Cloud Platform 製品などに関連するコミュニティが記載したテクニカル記事集。掲載された意見は著者のものであり、必ずしも Google のものを反映するものではありません。

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store