BigQueryの動的SQLでシャーディングテーブルを作成する

課題

UniversalAnalyticsのBigQueryテーブルのデータ抽出において、UNNESTの処理によって想定外に処理量が増えてしまうケースがあります。

直近30日分を取得するクエリ

例えば以下のようなクエリでGAテーブルから直近30日分を抽出するとします。

DECLARE FROM_DATE, TO_DATE STRING;

SET
  FROM_DATE = CAST(FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AS STRING);
SET
  TO_DATE = CAST(FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS STRING);
  
SELECT
  date,
  (
  SELECT
    MAX(
    IF
      (INDEX = 1,
        value,
        NULL))
  FROM
    UNNEST(hits.customDimensions)) AS cd1
FROM
  `projectName.datasetName.ga_sessions_*`,
  UNNEST(hits) AS hits
WHERE
  _table_suffix BETWEEN FROM_DATE
  AND TO_DATE

データ処理量を比較する

このクエリを「カスタムディメンションへのUNNEST・SELECTの有無」で比較してみると、データ処理量には大きな差があることが分かります。

	カスタムディメンションを UNNEST・SELECTする	カスタムディメンションを SELECTしない	比率
GAテーブル A	80GB	260MB	307
GAテーブル B	644GB	870MB	740

2つのGoogleAnalyticsテーブルで確認したところ、カスタムディメンションへのUNNEST・SELECTによって、処理量がそれぞれ307倍、740倍に増えています。

このようなクエリを日次でバッチ実行するとしたら費用が大幅に増えることは想像に難しくないでしょう。実際、私の場合は2つの日次のバッチ処理によって請求額が6万円/月の増加となってしまいました。

やり方を変える

そもそも日次で過去30日分を取得すること自体に無駄があるので、日次で前日分のデータ取得して日付別のテーブルを作成します。

ここでSQLを動的に扱うためのEXECUTE IMMEDIATEを利用します。

EXECUTE IMMEDIATE は静的文字列と変数の組み合わせの式を受け取り、有効な SQL ステートメントをその場ですぐに生成して即座に実行します。

ユーザーを笑顔にする BigQuery の使いやすい SQL 新機能 | Google Cloud Blog

クエリのスケジューリング実行

日次で前日のデータを抽出して前日(YYYYMMDD)のテーブル名のシャード化テーブルを作成します。具体的には以下クエリをスケジュールクエリで実行します。

DECLARE
  yyyymmdd INT64 DEFAULT 20220101;
SET
  yyyymmdd = (
  SELECT
    CAST(FORMAT_DATE("%Y%m%d", CURRENT_DATE() -1) AS INT64)) ;
EXECUTE IMMEDIATE
  FORMAT("""

  CREATE OR REPLACE TABLE
    `projectName.datasetName.tableName_%d` AS (
      SELECT
        date,
        (
        SELECT
          MAX(
          IF
            (INDEX = 1,
              value,
              NULL))
        FROM
          UNNEST(hits.customDimensions)) AS cd1
      FROM
        `projectName.datasetName.ga_sessions_%d`,
        UNNEST(hits) AS hits
    )
    
""", yyyymmdd, yyyymmdd);

※BigQueryでは _YYYYMMDDの形式でサフィックスをつけることで日付別シャード化テーブルとして認識されます。

これを日次で実行することで、前日分のテーブルが毎日自動生成されていきます。

なお同じことはスケジュールクエリでのrun_timeパラメータでも対応できます。（以下記事参考）

LOOPを使って複数テーブルを一括生成

過去分の複数テーブルが必要な場合には、クエリをループ処理してテーブルをまとめて作成します。以下のクエリではLOOP構文を利用して過去60日分のテーブルを作成しています。

DECLARE yyyymmdd INT64 DEFAULT 20220101;
DECLARE counter INT64 DEFAULT 0;
DECLARE PERIOD INT64 DEFAULT 60;

LOOP
  SET counter = counter + 1;
  IF counter >= PERIOD THEN
    LEAVE;
  END IF;

  SET yyyymmdd = (SELECT CAST(FORMAT_DATE("%Y%m%d",CURRENT_DATE() -counter) as INT64)) ;

  EXECUTE IMMEDIATE format("""

  CREATE OR REPLACE TABLE
    `projectName.datasetName.tableName_%d` AS (
      SELECT
        date,
        (
        SELECT
          MAX(
          IF
            (INDEX = 1,
              value,
              NULL))
        FROM
          UNNEST(hits.customDimensions)) AS cd1
      FROM
        `projectName.datasetName.ga_sessions_%d`,
        UNNEST(hits) AS hits
    )

    """, yyyymmdd,yyyymmdd);
  
END LOOP;

※誤ってLOOPから抜けないクエリを書いてしまうと大変なことになりそうですね・・。

ビュー(論理）テーブルを作成

最後にBIツール等から参照するためのビューテーブルを作成します。

CREATE OR REPLACE VIEW 'projectName.datasetName.viewName' AS (
 SELECT
  *
 FROM
  `projectName.datasetName.tableName_*`
)

おわりに

1テーブルのレコード数やカスタムディメンションのスロット数にも左右されるとは思いますが、UNNESTを利用する場合には発生するコストに気を配っておきたいです。

参考記事