Question

BigQuery Standard SQLで、ネストされたデータをモデリングする

  • 23 December 2019
  • 0 replies
  • 655 views

Userlevel 2

本記事は 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にエクスポートしたデータセットに基づいています。


ネストされた繰り返しフィールド(例:STRUCTの配列)の結合ベースのクエリー、FLATTENSの置き換え


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
}
}

ネストされた繰り返し列から個々の要素を選択したり、複数の要素にわたって集計したり、それらのUNNESTした列をGROUP BYステートメントのディメンションとして使用することもできます。


これは、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} ;;
}

また、table_date_range() とtable_query() を置き換えることにも注意してください


こちらの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
)
;;
}

0 replies

Be the first to reply!

Reply