はじめに

ここ最近、実務でBigQuery周りのお世話をする機会が多く、必要に応じてBigQueryの便利機能に色々と手を出しています。

今回は、2019年10月に発表されたばかりのBigQuery Scriptingというβ版の機能を使ってみた際に上手く行かなかったことを記事にしていきます。（※あくまでも2019.12.1現在の話です。）

なお、先日投稿した「【GCP】Schedule QueryとCloudSQL Federationを組み合わせて超簡単にMySQLデータをBigQueryへコピーする」と関連する内容です。

TL;DR

• 2019年10月に発表されたBigQuery Scripting（2019.12.1現在、β版）とSchedule Queryを組み合わせてバッチ処理を組もうとした結果、現時点ではそれらを組み合わせた時にデータの保存まではできないことが分かりました。
• BigQuery Scriptingが大変便利な機能なのは間違いないため、今後、Schedule Queryと組み合わせた時にも保存ができる機能が追加されていくことを期待したいです。

BigQuery Scriptingとは

2019年10月に発表されたばかりのβ版の機能であり、変数宣言やループ処理、条件分岐といった複雑な処理を記述できるようになるため、BigQuery上で表現できる幅が広がるという代物です。

例として、GCP公式ドキュメント - 標準 SQL のスクリプトより引用した例を下記します。

DECLAREの箇所で変数名や型を宣言し、SETの箇所で変数に値を入れると、SQLを書く際に最初に宣言した変数が使えるようになっているようです。

-- 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
);

BigQuery Scriptingを用いて実現したかったこと

先日、「【GCP】Schedule QueryとCloudSQL Federationを組み合わせて超簡単にMySQLデータをBigQueryへコピーする」という記事を投稿したのですが、ここに記載の内容の通り、EmbulkなどのETLツールなしでもMySQLデータをBigQuery上に保存する処理を定期実行させることができました。

一方で、使っていく中で、例えば、「毎月1日〜10日の間はクエリAを実行、毎月11日〜20日の間はクエリBを実行、そして毎月21日〜月末日まではクエリCを自動的に実行させるようにしたい」といった条件分岐を含んだ要望をBigQueryだけで叶えるのはなかなか難しいことが分かってきました。

というのも、色々と試行錯誤してみたのですが、現状、Schedule Queryのカスタムスケジュール（下図参照）では、「毎月◯日と◯日と...」のような指定はできなさそうだったのです。。。（僕ができなかっただけで、こうやったらできるよ、ってご存じの方は是非ご教示いただければ幸いです！！）

これらの要件を満たすには、上記の例だと毎月1日用のスケジューラー、毎月2日用のスケジューラー、・・・と大量のスケジューラーを用意する必要があり、流石にそれはキツイなと判断したため、最初はGASで実装しようかなと思っていたのですが、たまたまそのタイミングで「BigQuery ScriptingがBetaリリースされたので軽くウォークスルーしてみる」という記事を見つけました。

この記事を読んでいくと、BigQuey上で条件分岐もできることが分かり、これを使えばわざわざGASで実装しなくてもBigQuery上で完結できるから良さそうだなと考え、BigQuery Scriptingを試してみることにしました。

実際にやってみた結果

ドキュメントを参考に以下のようなスクリプトを組みました。

簡単に解説すると、

1. DECLAREでtodayというDATE型の変数を宣言
2. todayのデフォルト値はCURRENT_DATE()とすることで、現在の日付を取得
3. BigQueryの日付関数に標準装備されているEXTRACTを利用して今日が何日か取得
4. BigQuery Scriptingのif/elseを利用して今日の日付に応じて場合分け

ということをBigQuery上で表現しています。

-- 変数の宣言
DECLARE today DATE DEFAULT CURRENT_DATE();

-- 今日が1〜10日の場合、usersテーブルの内容を取得
IF EXTRACT(DAY FROM today) < 11 THEN
  SELECT * FROM EXTERNAL_QUERY(
    "mycurryapp.asia-northeast1.cloudsql-mycurryapp", 
    "SELECT * FROM users"
  );
-- 今日が21〜月末日の場合、shopsテーブルの内容を取得
ELSE
  IF EXTRACT(DAY FROM today) > 20 THEN
    SELECT * FROM EXTERNAL_QUERY(
      "mycurryapp.asia-northeast1.cloudsql-mycurryapp", 
      "SELECT * FROM shops"
    );
  -- 今日が11〜20日の場合、commentsテーブルの内容を取得
  ELSE
    SELECT * FROM EXTERNAL_QUERY(
      "mycurryapp.asia-northeast1.cloudsql-mycurryapp", 
      "SELECT * FROM comments"
    );
  END IF;
END IF;

これを実行してみると、以下のようにジョブが成功しました。
右下にある「結果を表示」から結果を見に行きます。今日は12/1なのでusersテーブルのデータを取得しているはずです。

狙い通り、usersテーブルの情報が取得できているようです。

さて、BigQuery Scriptingのif/elseによって場合分けができたので、あとはSchedule Queryを使ってバッチ処理を組んでいくだけです。

だがしかし、いざスケジュールを組もうとすると、BigQuery Scriptingを用いた場合、保存先の選択項目の表示がなく、データの保存ができないということが発覚しました。。。

探してみるとStack Overflowにも同様の話が挙がっていたので、BigQuery Scripting × Schedule Queryの組み合わせでは、現状、保存先を指定できないのでしょう。

ちなみに、UI上にある「結果を保存」からだと保存先を指定でき、実際にデータの保存はできました。

よって、クエリだけ用意しておいて、毎日手動で実行すればいいのですが、今回は自動化も要件に含まれていたため、BigQuery Scriptingの利用は諦めて、GASで実装することとなったというオチでした。

まとめ・所感

• BigQuery ScriptingとSchedule Queryを組み合わせてバッチ処理を組もうとすると、データの保存はできないということが分かりました。
• 恐らく、2019.12.1現在はβ版なので、まだ保存機能までサポートされていないだけであり、今後保存できるようにはなっていくのではないかと思います。
• 今後保存までできるようになれば、BigQueryだけで完結できることが増え、ますます便利になっていくのではないかと感じました。

参考URL

• 【GCP】Schedule QueryとCloudSQL Federationを組み合わせて超簡単にMySQLデータをBigQueryへコピーする
• 標準 SQL のスクリプト
• BigQuery ScriptingがBetaリリースされたので軽くウォークスルーしてみる
• BigQueryの日付関数
• Stack Overflow - Cannot set destination table with BigQuery Python API