トップ > BigQuery > BigQueryにexportしたGA4のシャーディングテーブルのスキーマに関するハマリ所

BigQueryにexportしたGA4のシャーディングテーブルのスキーマに関するハマリ所

BigQuery

前提: GA4とシャーディングテーブル

GA4のイベントデータを仕事で取り扱う人は多いと思う。BigQueryにexportする場合、GA4のイベントデータは[PREFIX]_YYYYMMDDのシャーディングテーブルになっており、さらにその中でevents_YYYYMMDDとevents_intraday_YYYYMMDDという2種類のシャーディングが存在する。

公式のドキュメントでは「パーティション分割テーブルのほうがパフォーマンスが向上するため、テーブルのシャーディングよりもパーティショニングをおすすめします」と記載されている通り、でかいテーブルを新規に作る場合はパーティション分割テーブルを作るほうがよい。

しかし、GA4のBigQueryにexportする場合、GA4より前のユニバーサル アナリティクスの時代以前からシャーディングでテーブルが保存されていたという背景がある。そのため、GA4のイベントデータもシャーディングテーブルとしてデータが日々exportされる。

シャーディングテーブルを使っていて、クエリが失敗するパターン

events_YYYYMMDDとevents_intraday_YYYYMMDDの両方をワイルドカードで指定して、以下のようなクエリを書くことは多いだろう。

SELECT
  collected_traffic_source.manual_campaign_name,
  collected_traffic_source.manual_marketing_tactic,
FROM
  `my-project.my_dataset.my_events_*`

あるいはワイルドカードを指定しつつ、intradayは除外するというユースケースも多い。

SELECT
  collected_traffic_source.manual_campaign_name,
  collected_traffic_source.manual_marketing_tactic,
FROM
  `my-project.my_dataset.my_events_*`
WHERE
  starts_with(_TABLE_SUFFIX, 'intraday_')

しかし、このクエリは失敗するケースがある。具体的にはevents_YYYYMMDDとevents_intraday_YYYYMMDDのスキーマが異なる場合だ。GA4のスキーマは経験的に半年から一年に一回くらいの頻度で新しいカラムが追加されることがある(上記の例だとmanual_marketing_tacticが該当)。つい先日も追加されたばかりだ。

また、先日新規に追加されたカラムはGA4の公式のドキュメントは2024/07/17現在まだ情報として反映されておらず、事前に検知するのは現実的に難しい*1。

GA4側の変更により、新規カラムが追加されスキーマが変更された場合、タイミングによってはevents_YYYYMMDDとevents_intraday_YYYYMMDDスキーマが異なり、クエリが落ちてしまう*2ことがありえる。

具体例を見てみよう。検証のための一時テーブルを作成する。events_YYYYMMDDは以下のようになっており、既存のカラム(manual_campaign_name)と新規のカラム(manual_marketing_tactic)が両方入った状態のスキーマになっている。

CREATE OR REPLACE TABLE
  `my-project.my_dataset.my_events_20240716` AS (
  SELECT
    STRUCT('hoge' AS manual_campaign_name,
      'fuga' AS manual_marketing_tactic) AS collected_traffic_source )

一方、events_intraday_YYYYMMDDは以下のようにmanual_marketing_tacticカラムがまだ存在せず、スキーマがずれていたとする。

CREATE OR REPLACE TABLE
  `my-project.my_dataset.my_events_intraday_20240716` AS (
  SELECT
    STRUCT('hoge' AS manual_campaign_name) AS collected_traffic_source )

この場合、以下のクエリは通るが

SELECT
  collected_traffic_source.manual_marketing_tactic,
FROM
  `my-project.my_dataset.my_events_*`

片方にしか存在しないカラムがある場合、クエリが落ちる(Field name manual_marketing_tactic does not exist in STRUCT<manual_campaign_name STRING> at [3:28]のようなエラーが返ってくる)。

SELECT
  collected_traffic_source.manual_campaign_name,
  collected_traffic_source.manual_marketing_tactic,
FROM
  `my-project.my_dataset.my_events_*`

クエリが落ちるのを避けようとして、以下のクエリを書いたとしても同様のエラーで失敗する。これはクエリを実際に走らせる前に、BigQuery側でクエリ内にあるシャーディングテーブルのスキーマを先にチェックする形式になっているからではないか、と思われる。

SELECT
  collected_traffic_source.manual_campaign_name,
  collected_traffic_source.manual_marketing_tactic,
FROM
  `my-project.my_dataset.my_events_*`
WHERE
  starts_with(_TABLE_SUFFIX, 'intraday_')

そのため、これを回避したいのであれば、以下のようにFROM句側でintradayが入ってこないようにするしかない(書き方がイマイチなのは認める)。

SELECT
  collected_traffic_source.manual_campaign_name,
  collected_traffic_source.manual_marketing_tactic,
FROM
  `my-project.my_dataset.my_events_20*`

追記

my_events_20*のような指定をする場合、WHERE句に入ってくる_TABLE_SUFFIXは20240716のような形式ではなく240716のようになってしまうので、さらに書き方的に微妙ですね。。

まとめ

  • GA4をBigQueryにexportする場合、GA4側の修正によりスキーマが変わっていることがある
    • 外部SaaSから提供されるデータを扱う場合、SELECT *ではなく明示的にカラムを列挙する形を取り、クエリが失敗しないように固い作りにしておこう
  • 同じGA4のデータであっても、events_YYYYMMDDとevents_intraday_YYYYMMDDでスキーマが異なる場合があり、その場合もクエリがこける
    • よりクエリを頑健にしたいのであれば、intradayの除外の仕方を工夫しよう

*1:ここは事前にアナウンスするなど、Google Cloud側が頑張ってやって欲しいところである...

*2:数分、とかではなく数時間単位という短かくないスパンで2つのスキーマが合わないということを観測した