💻
BigQuery Scriptingを使ってデータセット内のテーブルにループ処理を行う
BigQueryのあるデータセット内の全テーブルに対して、同じ処理を行いたいときに、BigQuery Scriptingを使うとループ処理を実行することができます。
▼BigQuery Scripting（和訳は手続き型言語というらしい）
最終的なクエリ
シチュエーション
あるデータセット（）内に、
- table_a
- table_b
- table_c
- ….
- table_z
のように、a~zまでの26個のテーブルがあったとします。
そのうち、2022年1月1日に作られたレコードだけを各テーブルから抜き出して、1つのテーブルにまとめたい状況があったとします。
を使用して、全てのテーブルを縦に連結すれば実現可能ですが、
- クエリが長くなる
- table名を1つ1つ入力しなければいけない
- tableが増減した際にクエリを修正しなければならない
のような問題があり、ループ処理が適している場面と言えるでしょう。
BigQuery Scriptingによるループ処理
冒頭の公式ドキュメントを見ると、BigQuery Scriptingには色々なループ処理の記法が存在します。LOOP, REPEAT, WHILE, FOR…INなど、他のプログラミング言語でも見られるロジックがあります。
今回のシチュエーションでは、データセット内のテーブル一覧を抜き出して、その要素1つ1つに対して処理を実行していくので、FOR…INループが適していると思います。
FOR…INループのサンプルクエリは以下のような感じです。
基本的には、Pythonのループと同じですが、の後にから始まる一連のクエリを書くことができ、その実行結果を1レコードずつループして処理を実行していくことができます。
1レコードずつ実行したい処理は、 の後に記述し、この例だと、, の2列を 句で抽出しているだけということになります。
それでは、ループを使って、ループ処理を書いていきます。
アルゴリズムの全体像
アルゴリズムの全体像は以下の通りです。
ポイントは3つで、
- データセットからテーブル名一覧を取得する
- ループ処理で、各テーブルから対象データを抽出する
- 結果をひとつのテーブルに格納する
という流れになっています。
それでは、順にみていきましょう。
1. データセット内のテーブル名一覧を取得する
まず、「処理を行いたいテーブル名のリスト」を取得していきます。
2. 1テーブルずつクエリを実行する
次に、前のステップで抽出したテーブル名を1つずつ取得して、各テーブルに対してクエリを実行する処理を書きます。
しかし、テーブル名を変数で渡して、句で受け取る際には、以下のようにひと手間加える必要があります。
の後に という記述をしています。
かなりざっくりと説明すると、
- format関数で、変数として渡されるテーブル名を文字列にマッピングする
- で、文字列で記述されたSQLを実行する
という処理を、各ループで行っています。
これにより、 内の各テーブルに対して、2022年1月1日のレコードだけを抽出するという処理がループ処理で実行できます。
3. 結果をひとつのテーブルにまとめる
前のステップまでで、クエリ実行自体はループ処理が可能になりました。
しかし、実行結果は別々のジョブとして実行され、別々のテーブルとして吐き出されています。最後にこれをひとつのテーブルにまとめる処理を追加したいと思います。
その処理を追加したものが以下のクエリになります。
変更点としては、
- でクエリを実行した結果を、一時テーブルとして保持する
- if文を使い、1回目のループは結果テーブルの作成、2回目以降は結果の を行う
という処理を追加しています。
これで、各ループで各テーブルに対してクエリを実行した結果を、ひとつのテーブル（）に格納することができます。
参考
この記事を書くにあたり、以下の記事を参考にさせて頂きました。