BigQueryで取ってきた値でループする

BigQuery取ってきた値でループ処理したい

Google Cloud Champion Innovators Advent Calendar 2023 16日目です。
（ん？もう12/25？おっかしいなぁ。そんなことはさておきです。はい。すいません。）
BigQueryのクエリ結果を変数に入れて、ループ処理したいときって無いですか？
複数列を取ってきて、やりたいなんてことはちょいちょいあります。
そんなときに Array と Struct を便利に使ってやってみましょう！
今回はbigquery-public-data.austin_bikeshareを例にやってみたいと思います。

まずは変数の指定

DECLAREのところで変数を指定します。
複数行を入れるためにArrayを指定して、その中に複数列を入れるためにStructで構成します。
今回の場合はstation_id,name,statusの3つの列を入れていきます。

variable.sql

-- クエリ結果を入れる変数
DECLARE array_struct ARRAY<STRUCT<station_id INT64, name STRING, status STRING>>;

変数に値を入れてみる

変数に値を入れていきます。
SQLの実行結果を変数に入れるのですが、変数の方に合わせる必要があるので実行結果も同じように Array と Struct にする必要があります。
bigquery-public-data.austin_bikeshare.bikeshare_stationsのテーブルの値を入れていきます。

variable.sql

-- クエリ結果を入れる変数
DECLARE array_struct ARRAY<STRUCT<station_id INT64, name STRING, status STRING>>;

-- クエリ結果を変数に入れる
SET array_struct = (
  SELECT
    ARRAY_AGG(
      STRUCT(
        station_id,
        name,
        status
      )
    )
  FROM
    `bigquery-public-data.austin_bikeshare.bikeshare_stations`

  );

-- 変数に入ったものを見てみる
SELECT
  array_struct;

実行結果

ここでわかるのですが、変数は複数の値を持つことはできないので、複数の値をなんとか1つのところに入れています。
簡単に言うと小袋で分かれているポッキーが1つの箱に入って、またダースの箱に入って、そのダースの箱を1つの段ボールに入れているマトリョーシカみたいな感じで入れていると考えればわかりやすいかと。（わかりやすいか？）

結果を1つずつ取り出してみる

では、結果を１つずつ取り出してみましょう。
変数に入れたデータ分だけ、ループをまわすのでその数をさらに変数に追加していきます。
今回は1つずつ取り出して、さらにそれを変数に格納してみます。

variable.sql

-- クエリ結果を入れる変数
DECLARE array_struct ARRAY<STRUCT<station_id INT64, name STRING, status STRING>>;

-- array_structから取り出した値を入れる変数
DECLARE array_struct_row STRUCT<station_id INT64, name STRING, status STRING>;

-- array_structの長さを入れる変数
DECLARE loop_cnt INT64;

-- ループ用の変数
DECLARE i INT64 DEFAULT 0;

-- クエリ結果を変数に入れる
SET array_struct = (
  SELECT
    ARRAY_AGG(
      STRUCT(
        station_id,
        name,
        status
      )
    )
  FROM
    `bigquery-public-data.austin_bikeshare.bikeshare_stations`
  );

-- 変数に入ったものを見てみる
SELECT
  array_struct;

-- 変数array_structの長さをloop_cntに入れる
SET loop_cnt = ARRAY_LENGTH(array_struct);

-- ループ開始、loop_cnt分回す
WHILE loop_cnt > i
  DO
    -- 1行ずつ入れていく
    SET array_struct_row = (
      SELECT
        array_struct[offset(i)]
    );
  
    -- 確認用
    SELECT
      array_struct_row;
    
    -- 列を取り出したいときはこんな感じで、たとえばstation_idの場合
    SELECT
      array_struct_row.station_id;

    -- 次の行のために1つ増やす
    SET i = i + 1;
END WHILE;

最後に

こんな感じでクエリ結果を利用してループをまわすということができます。
僕自身はEXECUTE IMMEDIATEを利用してクエリ結果をファイルに吐き出す場合に、条件やファイル名などを一気に取ってきて、1つずつ処理させたりするときに利用しています。
では、2024年も楽しいBigQueryライフを！！！