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

本記事は 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
  ) 
;;
}
1 0 1,083