本記事は Modeling nested data in Big Query Standard SQL 🇺🇸 の翻訳記事です。
Big Queryの標準SQL(Standard SQL Dialects)のリリースされ、LookMLでネストされたデータセットをモデル化することがはるかに簡単になりました。
ネストされたファイルと繰り返しファイルがBigQueryでどのように機能し、それらがなぜ重要なのかについての簡単な入門については、 ダニエルの投稿 をご覧ください(訳注:こちらはリンク切れです)。
また、こちらのリンクからGoogle Analytics PremiumとGA360用のBlocksを参照できます。こちらをご覧いただければ、以下に示すプラグ・アンド・プレイですぐに使えるLookMLのコンセプトがよく理解できると思います。
以下に示す例は、ネストされた繰り返しフィールドを多用するGoogle Analytics Premium(GAP)をBig Queryにエクスポートしたデータセットに基づいています。
Google Analytics Premiumスキーマには、セッションレベルのデータが格納されたテーブルが1つあり、個々のヒット(例えばイベント)レコードがそれぞれのセッション内にネストされています。ヒットレベルとセッションレベルのデータを同時にクエリする(たとえば、ヒットレベルとセッションレベルのデータを同時にカウントする(例えば、セッションの合計数とその時間内のヒット数の合計数)ために、これらの2つの異なるレベルのネストを参照するための新しい結合ベースの構文があります(ドキュメントはこちら )。 この構文を参考にし、モデルのセッションフィールドとヒットフィールドに個別のビューファイルを定義し、それらをExploreで結合します。
view: session {
sql_table_name: `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_*` ;;
# このViewファイルにセッションに関連する全てのディメンションとメジャーを定義します
}
view: hits {
# 注:以下の Explore に定義するので、ここには sql_table_name を記述しません。
# このViewファイルには、ヒットに関連する全てのディメンションとメジャーを定義します
}
explore: visits {
join: hits {
sql: ,UNNEST(visits.hits) as hits ;;
relationship: one_to_many
}
}
これは、GAPエクスポートスキーマでカスタムされたヒット属性を処理する場合に特に役立ちます。
dimension: custom_attribute_1_value {
type: string
sql: (SELECT value FROM UNNEST(${TABLE}.customdimensions) where index = 1) ;;
}
特定のイベントについて、複数のネストされた繰り返しレコードに集約することもできます。これもGROUP BYで使用できますが、メジャーとしてさらに集約することもできます。
dimension: number_of_custom_attributes {
type: number
sql: (SELECT count(*) FROM UNNEST(${TABLE}.customdimensions)) ;;
}
measure: average_number_of_custom_attributes {
type: average
sql: ${number_of_custom_attributes} ;;
}
こちらのDiscourseの記事では、BigQuery の新しいパーティション分割ツールを使用して、GAPスキーマの ga_sessions
テーブルなど、日付で区切られたテーブルをモデル化する方法について解説しています。
サブクエリーを使ったさらに複雑なディメンションの作成例です。
dimension: number_of_long_pauses {
sql:
/* イベント間でX時間が経過する回数を見つける */
( select count(*) -- 2. ヒットの回数をカウントする
from (
/* -- 1: ヒット(例:イベント)毎のタイムスタンプと、その一つ前のヒットのタイムスタンプを求める */
select time,
lag(time) over(order by time) as last_event_time
from unnest(hits)
)
where time - last_event_time > 1000
/* 3. 前回のヒットから1000ミリ秒以上経過したもので絞り込む */
) ;;
}
dimension: event_type_with_longest_pause {
sql:
(select last_value(event_category) over(order by time_spent asc)
from
(select
eventInfo.eventCategory as event_category,
lead(time) over(order by time) as time_spent
from unnest(hits))
limit 1
)
;;
}