【BigQueryでループ処理】を書く

概要

WHERE句にある絞り込み条件で同じSQLを流用しながら条件を変えつつforループのように実行したい時のBQでループを書く方法。

問題設定

今回は「アクセスログの中から、集計対象となるログをマスタテーブルに保持されているパラメータで絞り込みUU数を計算する」ということを想定してSQLを書く。

※この程度の処理であれば、マスタテーブルとログテーブルを突合してパラメータでGROUP BY すれば集計できますが、今回はループ処理の説明のために簡単な問題をテーマに扱って解説します。

使用するデータテーブル

▼masterテーブル

parameter
a
b
c
...

▼アクセスログ(access_log) テーブル

user_id	session	time_stamp	page_url	event
Aさん	XXX	1	https://hogehoge.com	NULL
Aさん	XXX	2	https://hogehoge.com	a
Bさん	YYY	3	https://hogehoge.com	b
...	...	...	...	...

• user_id：サイトに訪問したユーザのID
• session：セッション
• time_stamp：ページを閲覧した時の時刻
• page_url：閲覧したページのURL
• event：ユーザが事前定義されたアクションを行った際にパラメータが格納される（アクションしない場合はNULL）

処理フロー

処理フローを日本語で書くと、次のようなイメージ。

①初期値定義
②ループ回数(n)の計算
③処理開始
　(i)ループインデックス(i)回目のパラメータ値を抽出
　(ii)tempテーブルを作成
　(iii)各イテレーションの処理を書く
　(iv)ループ1回目ならoutputテーブル作成、2回目以降なら、outputテーブルにインサート
　(v)インデックス i = i+1 でインデックスを増やす
　(vi)インデックスiとループ回数(n)を比較。nより大きければループ終了

作成したSQL

--------------------------------------------------------
-- 変数定義 & 初期化
--------------------------------------------------------
-- ループ中のindex
DECLARE i INT64 DEFAULT 1;
-- ループしたい回数
DECLARE n INT64 DEFAULT 0;


--------------------------------------------------------
-- ループ回数の計算
--------------------------------------------------------
SET n = (
  --マスターテーブルの"ユニーク"なレコード数を数えて何回ループさせるかを計算
  WITH master AS (
    SELECT 
      parameter
    FROM
      `master_table`
  )
  SELECT COUNT(DISTINCT parameter)
  FROM master
);


--------------------------------------------------------
-- 処理開始

LOOP
    -- i = i でループしているときに条件判定で使うための変数値を抽出
    -- target_parameterにparameterがセットされる
    SET target_parameter = (
        WITH master AS (
            SELECT 
                DISTINCT parameter
            FROM
                `master_table`
        )
        -- 順番(インデックス)をつける
        ,order_master AS (
            SELECT
                parameter
                ,ROW_NUMBER() OVER(ORDER BY parameter DESC) AS ROW_NUMBER
            FROM
                master
        )
        -- インデックス通りの順番に並び替える
        ,ordered_master AS (
            SELECT
                parameter
                ,ROW_NUMBER
            FROM
                order_master
            ORDER BY
                ROW_NUMBER
        )
        -- ループインデックス(i)番目のparameterを抽出
        SELECT parameter
        FROM ordered_master
        WHERE ROW_NUMBER = i
    );


--------------------------------------------------------
-- ループ処理


--------------------------------------------------------
-- 1ループ毎に結果をtempというテーブルに格納する
CREATE OR REPLACE TABLE temp
  AS (

------------------------------------------------------------------------------------------------
--****************************実際の処理はここに書く*************************************************
--------------------------------------------------------------
--target_parameterを含んでいるアクセスログを抽出
WITH log AS (
    SELECT
        user_id     -- ユーザID
        ,session    -- セッション
        ,time_stamp -- ページを閲覧した時の時刻
        ,page_url   -- 閲覧したページのURL
    FROM
        `access_log`
    WHERE
    	event = target_parameter -- eventというカラムにtarget_parameterが含まれているレコードに限定
)

--------------------------------------------------------------
-- ユニークユーザ数のカウント

SELECT
  COUNT(DISTINCT user_id) AS uu
FROM
  log

--********************************処理終了*******************************************************
------------------------------------------------------------------------------------------------
);


--------------------------------------------------------
-- tempを都度参照し、ループ1回目なら新しくテーブルを作り、2回目以降なら結果を追加する
  IF i = 1 THEN
    CREATE OR REPLACE TABLE output AS (SELECT * FROM temp);
  ELSE
    INSERT INTO output SELECT * FROM temp;
  END IF;



  SET i = i + 1;
  IF i > n THEN
    LEAVE;
  END IF;
END LOOP;

ループ処理で書くメリット

ループを使わずともUNIONやWITH句を大量に作れば同じことはできますが、2つ問題があるかと思います

①実行可能性の問題

BigQueryではwith句やUNIONを多用するとリソース不足に陥りSQLが実行できなくなることがあります。ループを使うことでリソース不足に陥るリスクを低減できます

②開発/運用保守などの人間側の問題

ループ処理で書きたいということは同じような処理を繰り返し実行したいということですが、これを素直にWITH句などで記載すると、基本となるwith句をひたすら「コピペし、with句内のparameterを変えて…」ということの繰り返しになると思います。（記事下部に記載している「WITH句を大量に作る場合のSQLのイメージ」を参照）
人間が手作業をする量が増えれば増えるほど、ミス発生の可能性が高まるため、コピペする量が増えればparameterの変更漏れが発生してしまう可能性が高くなりますつまり、開発でのミスが発生する可能性が高まります。

また、付与されているパラメータの仕様が変わったりすると、全てのwith句で改修しなくてはいけなくなるため運用保守が大変になります。（筆者の実務ではパラメータを100個単位で変更することがザラにあったため、もしループではなく、with句を大量に使った場合はちょっとした変更が起きるたびに100個単位での変更が必要となり残業しないといけなくなっていたかもしれません）

ループ処理を使えば、パラメータの仕様が変わってもマスタ側のパラメータをアップデートすれば即座に対応できるため上記のような運用保守の手間から解放されます。

WITH句を大量に作る場合のSQLのイメージ

WITH table_a AS (
  SELECT
    user_id     -- ユーザID
    ,session    -- セッション
    ,time_stamp -- ページを閲覧した時の時刻
    ,page_url   -- 閲覧したページのURL
  FROM
    `access_log`
  WHERE
     event = 'a'
),
table_b AS (
  SELECT
    user_id     -- ユーザID
    ,session    -- セッション
    ,time_stamp -- ページを閲覧した時の時刻
    ,page_url   -- 閲覧したページのURL
  FROM
    `access_log`
  WHERE
     event = 'b'
),
table_c AS (
  SELECT
    user_id     -- ユーザID
    ,session    -- セッション
    ,time_stamp -- ページを閲覧した時の時刻
    ,page_url   -- 閲覧したページのURL
  FROM
    `access_log`
  WHERE
     event = 'c'
)
...
...
...

SELECT COUNT(DISTINCT user_id) FROM table_a
UNION ALL
SELECT COUNT(DISTINCT user_id) FROM table_b
UNION ALL 
SELECT COUNT(DISTINCT user_id) FROM table_c
...