一般的なDBに慣れてきた人がBigQueryを扱う際にハマりやすいポイント(Legacy SQL編)

一般的なSQLに慣れてきた人がBigQuery(Legacy SQL)を使う際によくハマるポイント、
特にGoogleアナリティクス360(旧Googleアナリティクスプレミアム)が出力するログデータを扱う場合に直面する問題を中心に解説する。

Googleアナリティクス360のログデータはBigQueryの特徴的なところを嫌というほど満載している。
ということで、これを扱えれば大概の問題には対応できるようになるだろう。

なおLegacy SQLに限定した話。Standard SQLは未検証。

言語個別の仕様

ネストされたデータ形式

BigQueryでは各レコードの1個のカラムに複数の値、つまり複数行に相当するデータを入れることができる仕様になっているのが大きな特徴である。
1つのセルにスカラ値ではなくテーブルが入るイメージ。

データに親子関係があって一般的なリレーショナルデータベースでは子テーブルを作成していたようなケース(受注と受注明細、セッションとページビューなど)でも、
データの親子構造をそのまま一つのテーブルに格納することができる(非正規化された状態)。

一般的なRDBに慣れていればわざわざ自分でこのような機能を使わないが、GA360では当然のように使ってくる。
BigQueryの中では以下のような扱いになる。

  • type: RECORD型
  • mode: REPEATED

クエリでは

  • 親エンティティ: totals
  • 子エンティティ: totals.visits

のようにドットを付けたxxxxx.yyyyyの形式でアクセスできる。
親エンティティのtotalsがREPEATED(mode)のRECORD型(type)カラムになる。

RECORD型でもREPEATEDでなければ複数の行(1対N)を持つことができず、1対1となる→親子関係のない通常のレコードと同様にアクセスできる(カラム名のドットが付いているだけ)。RECORD型のカラム内のカラムがRECORD型になる構造(RECORD型のネスト)になってもいい。

たとえばGA360のテーブルでREPEATEDなRECORD型になるのは

  • customDimensions
  • hits
    • hits.product
      • hits.product.customDimensions
      • hits.product.customMetrics
    • hits.promotion
    • hits.customVariables
    • hits.customDimensions
    • hits.customMetrics

このうち

  • hits.product
    • hits.product.customDimensions
    • hits.product.customMetrics
  • hits.promotion

は拡張eコマース機能なので、高々2段階のネストまでを意識すればいい。

親子を同時にSELECT

正規化されていればJOINをするが、1つのテーブルになっているので自己結合などは不要。

第1階層と1種類の第2階層(hits.***)を抽出する場合

SELECT 
  fullVisitorId, 
  visitNumber, 
  visitStartTime, 
  date, 
  hits.hitNumber, 
  hits.page.pagePath, 
  totals.visits 
FROM 
  [99999999.ga_sessions_20151010] 
LIMIT 50;

LIMIT句は親エンティティに対して適用。

第1階層と2種類の第2階層(customDimensions.*** / hits.***)を抽出する場合

これはダメ、複数の第2階層が多対多になるため。

SELECT 
  fullVisitorId, 
  visitNumber, 
  visitStartTime, 
  date, 
  customDimensions.value, 
  hits.hitNumber, 
  hits.page.pagePath, 
  totals.visits 
FROM 
  [99999999.ga_sessions_20151010] 
LIMIT 50;

これを解決するのがFLATTEN

SELECT 
  fullVisitorId, 
  visitNumber, 
  visitStartTime, 
  date, 
  customDimensions.value, 
  hits.hitNumber, 
  hits.page.pagePath, 
  totals.visits 
FROM 
  FLATTEN([99999999.ga_sessions_20151010], customDimensions) 
LIMIT 50;

hitsの内容がcustomDimensionsに展開される。LIMIT句はcustomDimensionsに対して適用。

SELECT 
  fullVisitorId, 
  visitNumber, 
  visitStartTime, 
  date, 
  customDimensions.value, 
  hits.hitNumber, 
  hits.page.pagePath, 
  totals.visits 
FROM 
  FLATTEN([99999999.ga_sessions_20151010], hits) 
LIMIT 50;

customDimensionsの内容がhitsに展開される。LIMIT句はhitsに対して適用。

何をどこに展開するのか難しい。

ネストされたRECORDの集計

WITHIN修飾子を使う

たとえばネストされているhits.***のページビュー数の集計をする場合

SELECT
  fullvisitorId,
  visitId,
  CASE WHEN trafficSource.campaign = 'a001' THEN 'Remarketing' ELSE 'Broad' END AS display_type,
  EXACT_COUNT_DISTINCT(hits.page.pagePath) WITHIN RECORD AS num_unique_pages,
FROM
  [99999999.ga_sessions_20161001]
WHERE
  trafficSource.medium = 'display'

GROUP BYを使って単純集計してもいいのだが、CASE WHEN trafficSource.campaign = 'a001' THEN 'Remarketing' ELSE 'Broad' END AS display_typeのような列があるとやりにくくなる(クエリが無駄に複雑になる)。
簡単にネストされたレコードを集計して1行の結果として出すことができる。

日付

BigQueryの日時を表す変数はTIMESTAMP型。
インポートの都合で日あるいは時が文字列型(STRING)で格納されていることもあるが、これをTIMESTAMP型に変換するのがTIMESTAMP()関数。基本的にうまいことやってTIMESTAMP型に変換してくれる。

実行内容 戻り値
SELECT TIMESTAMP('20151020'); 2015-10-20 00:00:00 UTC
SELECT TIMESTAMP('2015-10-20'); 2015-10-20 00:00:00 UTC
SELECT TIMESTAMP('2015/10/20'); 2015-10-20 00:00:00 UTC
SELECT TIMESTAMP('2015-10-20 00:00:00'); 2015-10-20 00:00:00 UTC
SELECT TIMESTAMP('2015-10-20 9:00:00'); 2015-10-20 09:00:00 UTC
SELECT TIMESTAMP('2015-10-20 09:00:00'); 2015-10-20 09:00:00 UTC
SELECT TIMESTAMP('2015-10-20 18:5:7'); 2015-10-20 18:05:07 UTC
SELECT TIMESTAMP('2015-10-20 24:00:00'); null
SELECT TIMESTAMP('10/20/2015'); null

日付の計算

実行内容 戻り値
SELECT TIMESTAMP('2015-10-20') - TIMESTAMP('2015-10-15'); 432000000000
SELECT DATEDIFF(TIMESTAMP('2015-10-20'), TIMESTAMP('2015-10-15')); 5
SELECT DATE_ADD(TIMESTAMP('2015-10-20'), 5, 'DAY'); 2015-10-25 00:00:00 UTC
SELECT TIMESTAMP('2015-10-20') + 5 * 1000000 * 60 * 60 * 24; 1445731200000000
  • 日付の連番になっているテーブル群をまとめて扱う(table wildcard function)。
    メニューで「ga_sessions_ (331)」と表示されているテーブルは実は「ga_sessions_20160817」「ga_sessions_20160816」…の集約
SELECT ... FROM 
  TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2016-10-01'), TIMESTAMP('2016-10-05'));`

resources exceededのエラーがGROUP EACH BYJOIN EACHを使ったクエリで発生する。
これは大量のレコードが一つのキーに紐づくような歪んだデータによって発生する。
一部のデータでCOUNT(DISTINCT) over GROUP EACH BYを実行してみて、キーの分布を確認するといい。

よく使うクエリ

  • ページビュー数やセッション数のカウント
  • 複数セッションをまたいだカスタマージャーニーを見る
  • コンバージョンの発生したセッションにフラグを付ける(BigQueryのログにはコンバージョンデータが含まれていないのでページビューやイベントを参照して自前で付ける必要がある
  • その他特定の行動(特定階層の閲覧やイベント発生など)にフラグを付ける
    • ページビュー単位で見る
    • セッション単位で集計
  • 独自の流入元分類をする
    →2016年秋以降、チャネルグループ分類もログデータに出力されるようになった

これらはTODO

ハマるところ

  • ユニークカウントでCOUNT(DISTINCT ...)は概算。正確なユニークカウントはEXACT_COUNT_DISTINCT(...)を使う。

GAまわり

  • Adwordsと未連携のデータにはtrafficSource.adwordsClickInfoがないため、連携後のデータとUNIONするとエラーが発生

window関数

  • WITHIN節とwindow関数は同時に使えない。
  • 複数のwindowがあるときに列名エイリアスが使えない(エイリアスを使わないオリジナルの列名でSELECT済みの場合は除く)
  • LAST_VALUE()関数が使い物にならない。FIRST_VALUE() OVER (ORDER BY ... DESC)で。

JOINの罠

3個以上のテーブルをJOINするとき、
3個目以降のテーブルのカラムを指定する際は、
テーブル名を省略してはならない。

省略すると以下のエラーが発生する。

Field 'フィールド名' not found on either side of the JOIN

分散処理

BigQueryの処理は分割・分散処理が基本。
たとえば全レコードを1個のテーブルに収めるのではなく、分割して連番管理(日ごとにテーブルを分けるなど)したテーブルを扱うのがBigQueryの王道である。
GA360のログデータも日別のテーブルに格納されている。そのような分割されたテーブルを扱う際に発生する問題。

分散処理でたまたま先に返ってくる列の数が少ない場合

クエリ結果の列数が一致しないことがある。
というのも連番管理したテーブルもタイミングによって列が増えたり減ったりすることがある。
GA360ではAdWords連携すると、未連携時と前後で列の数が変わるため、この問題に直面する。
その他新しい仕様でカラムが追加されることもある(trafficSource.isTrueDirectなど)

(例)AdWords連携を2016-7-2に行った場合

これだとダメ

FROM 
  (SELECT * FROM TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2016-5-31'), TIMESTAMP('2016-7-1'))), 
  (SELECT * FROM TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2016-7-2'), TIMESTAMP('2016-7-3'))) 

これならOK

FROM 
  (SELECT *, trafficSource.adwordsClickInfo.adNetworkType AS adNetworkType FROM TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2016-7-2'), TIMESTAMP('2016-12-31'))), 
  (SELECT *, STRING(NULL) AS adNetworkType FROM TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2015-12-1'), TIMESTAMP('2016-7-1'))) 

ただし、(内部的に)先に返ってくるクエリ結果の列数のほうが多い場合はエラーが発生しない。
分散処理のタイミングに依存する。

FLATTEN節と連番テーブル

FLATTEN節はTABLE_DATE_RANGE()などを使った連番テーブルに対して適用できない。一度連番テーブル全体をSELECTした結果のサブクエリに対してFLATENを適用する。

これはOK

SELECT
  fullVisitorId,
  customDimensions.index,
  customDimensions.value,
  visitId,
  totals.hits,
  hits.type,
  hits.page.pagePath
FROM
  FLATTEN([99999999.ga_sessions_20151214], customDimensions )
WHERE
  (customDimensions.index = 1
    OR customDimensions.index IS NULL)
  AND hits.type = 'PAGE'

これはNG

SELECT
  fullVisitorId,
  customDimensions.index,
  customDimensions.value,
  visitId,
  totals.hits,
  hits.type,
  hits.page.pagePath
FROM
  FLATTEN(TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2015-12-14'), TIMESTAMP('2015-12-15')), customDimensions )
WHERE
  (customDimensions.index = 1
    OR customDimensions.index IS NULL)
  AND hits.type = 'PAGE'

こうする必要がある

SELECT
  *
FROM
  FLATTEN( (
    SELECT
      fullVisitorId,
      customDimensions.index,
      customDimensions.value,
      visitId,
      totals.hits,
      hits.type,
      hits.page.pagePath
    FROM
      TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2015-12-14'), TIMESTAMP('2015-12-15')) ), customDimensions )
WHERE
  (customDimensions.index = 1
    OR customDimensions.index IS NULL)
  AND hits.type = 'PAGE'

分割できない処理で扱うデータサイズが大きくなる場合

分割できない処理で扱うデータサイズが大きくなると「Resources exceeded during query execution.」というエラーが発生する。たとえば

SELECT
  visitStartTime,
  fullVisitorId,
  GAID,
  visitId,
  visitNumber,
  device.deviceCategory,
  totals.hits,
  hits.hitNumber,
  timeSec,
  hits.page.pagePath
FROM
  FLATTEN( (
    SELECT
      visitStartTime,
      fullVisitorId,
      customDimensions.index,
      customDimensions.value AS GAID,
      visitId,
      visitNumber,
      device.deviceCategory,
      totals.hits,
      hits.type,
      hits.hitNumber,
      hits.time / 1000 AS timeSec,
      hits.page.pagePath
    FROM
      TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2016-1-1'), TIMESTAMP('2016-1-31')) ), customDimensions )
WHERE
  (customDimensions.index = 1
    OR customDimensions.index IS NULL)
  AND hits.type = 'PAGE'
ORDER BY
  fullVisitorId,
  visitId,
  visitNumber,
  hits.hitNumber;

その他限界

GROUP BYしすぎると

Maximum number of keys in GROUP BY clause is 64, query has 65 GROUP BY keys.

DISTINCTなレコードを抽出するためにはGROUP BYが必要だが、列が多くなるとこのエラーが発生する。

データ分析 の記事一覧