WITH句でGA4のBigQueryテーブルを再現してみた

SQLにWITHという命令がありまして、非常に便利なんです。仮想のテーブルを作る命令でして、SQL文を発行する先のテーブルが実在しなくても、WITH句で、そのテーブルを作れてしまうんです。例を挙げたほうが分かりやすいと思います。以下は、GoogleのSQLの公式ヘルプのCOUNTという関数の説明の一部です。実際にその関数が利用された実例を示すと、その関数の機能を分かりやすく説明できます。

一方、実例を示すということは、対象となるデータを格納したテーブルが必要です。しかし、そんなに都合よく対象となるテーブルがある訳でもないですし、あったとしても「これこれこういう構成のテーブルです」と説明するのは大変です。

便利なWITH句

ところがWITH句を使うと、どんなテーブルか？をくどくど説明しなくても、見ただけでわかってしまうんです。

具体的には、赤枠の記述で、Eventsという名前をつけた、event_dateと、event_typeというフィールドを持つ、2列構成、6レコードの仮想テーブルを作成しています。event_dateの前には DATEというキーワードがあり、このカラムは日付型、event_typeは、シングルコーテーションマークで囲まれた文字列型だということも分かります。

試しに、上記のWITH句をコピーし、全列全行を取得してみましょう。

本体のSQL文（14行目）でWITH句が作成する仮想テーブル Eventsの全体像を示しています。WITH句が仮想のテーブルを作ってくれるという点については、なんとなく理解できたかと思いますので、本題に入ります。

WITH句でGA4のBigQueryテーブルを再現する

GA4がBigQueryにエクスポートするテーブルには、データ型がRECORD型と分類される、入れ子になった列が含まれています。以下がDEMOアカウントのBigQueryサンプルデータのテーブルのスキーマ（列定義）ですが、STRING（文字列型）やINTEGER(整数型）に混じって、ところどころ、RECORD型の列が存在するのが見て取れます。

この、GA4がBigQueryにエクスポートする、入れ子を含むテーブルをWITH句で再現する。というのが本ブログ記事のテーマです。再現できたとしても特に何も良いことはありません。少し、テーブルの構造についての理解が進んだり、配列や構造体についての「食わず嫌い」が薄らぐくらいです。なので、「この先を読むとなにかいいことがあるんじゃないか？」とご期待の方は、すみません。この先は趣味の世界です。

3種類のRECORD型カラム

まずは、GA4がBigQueryにエクスポートしたテーブルの、RECORD型のカラムを分析します。ポイントは、以下の2つです。

1ヒットの中で「縦方向」に値が並んでいるかどうか
カラム名の「ドット」の数

「GA4がBigQueryにエクスポートしたテーブルには入れ子構造だ」とは聞いたことがある方は多いと思うのですが、同じRECORD型のカラム（＝列）でも、詳しく見ると以下の4通りのパターンに分けることができます。

ARRAY(配列）とSTRUCT(構造体）

上記のような4つのパターンを、WITH句でどう再現するか？というお題にあたっては、BigQueryのARRAY（配列）と、STRUCT（構造体）を理解する必要があります。

詳しくは、Googleの公式ヘルプ　https://cloud.google.com/bigquery/docs/best-practices-performance-nested?hl=ja　を参照して頂く必要があります。が、内容が結構難しいです。なので、WITH句でGA4のBigQueryテーブルを再現するというお題を解くための最低限の知識として、私なりにまとめてみたのが、以下です。

入れ子は STRUCT式で作成する。入れ子を作成すると、カラムが「横」に増えていく
STRUCT式（一重）は、こんな感じで記述する　STRUCT(“Japan” AS country, “Chiba” AS region) AS geo
STRUCT式を使うと外側のエイリアス（AS句での命名）、ドット、内側エイリアスというカラム名になる（2を実行すると、　geo.country、geo.regionという2カラムが生成できる）
入れ子を二重にしたい場合にはSTRUCT式の中にSTRUCT式を記述する
ドットは、入れ子の深さを表している。ドット一つは一重、2つは二重
縦方向に値を並べるには、ARRAY（配列）を使う
配列は、[a, b, c]のように、角括弧 [ ] に要素を含める

完成したWITH句

上記の7個の知識だけを使って、「お手本」となるGA4がエクスポートしたBigQueryテーブルと慎重に見比べながら完成させたのが、以下のWITH句です。画像（こちらの方が色分けされていて見やすい）と、テキスト（こちらはコピー可能。誰がコピーするんだろう？と思いながらも^^;）の両方を載せておきますね。構造を再現することが目的ですので値は適当です。

WITH master AS (
  SELECT
    "page_view" AS event_name,
    [
      STRUCT("page_tittle" AS key, STRUCT("hoge" AS string_value, NULL AS int_value) AS value),
      STRUCT("ga_session_number" AS key, STRUCT(NULL, 1) AS value)
    ] AS event_params,
    STRUCT("Japan" AS country, "Chiba" AS region) AS geo,
    STRUCT("desktop" AS category, STRUCT("chrome" AS browser, "122.0.6261" AS browser_version) AS web_info) AS device,
    [
      STRUCT(123 AS item_id, "iphone13" AS item_name),
      STRUCT(124, "iphone14")
    ] AS items
)

で、実行した結果が以下です。横長なので、左右に分割してます。