Looker の派生テーブルと事前集計について

この記事はLooker Advent Caledar 2021 21日目の記事となります。

前置き

Looker による集計の挙動

まずは派生テーブルを利用しない場合における、Looker の集計処理の挙動について整理します。

公式ドキュメントの通り、Looker では次の形式の SQL が自動生成されます。

sql

SELECT
   -- 集計軸
   <dimension>, <dimension>, …
   -- 集計値
   <measure>, <measure>, …
FROM <explore>
LEFT JOIN <view> ON …
LEFT JOIN <view> ON …
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND …
GROUP BY <dimension>, <dimension>, <dimension>, …
HAVING <measure_filter_expression> AND <measure_filter_expression> AND …
ORDER BY <dimension> | <measure>
LIMIT <limit>

Looker では未集計のまま生のテーブルを結合した後で、集計値 (measure) の評価が行われます (同一レコードが複数回結合されることで生じ得る、重複集計は対象集計の機能で排除されます)。

この方式により、Explore の画面で選ばれた dimension に応じて、measure の集計軸が動的に切り替わるため、自由度の高い分析を Explore で行うことができるようになります。

結合前の事前集計について

上記の通り、Looker ではテーブルの結合後に集計値の評価がされますが、以下のような理由から、結合前に集計を行いたい場合もあるかと思います。

集計結果に対して、再度集計処理を行いたい
dimension を跨いだ集計値を利用したい
結合前にレコードを減らし、結合処理のパフォーマンスを改善したい

サンプルデータについて

この記事では、米国海洋大気庁（NOAA）の Global Historical Climate Network (GHCN) による、2021年1月の日別平均気温のデータをサンプルとして利用します。

テーブルデータの用意

リンク先から 2021.csv をダウンロード後、下記コマンドで1月分の日別平均気温を抽出します。

bash
gzip -cd 2021.csv.gz | grep "202101..,TAVG" | cut -d, -f1-2,4- > 202101.csv
この CSV ファイルを、Looker が参照するデータベースへ取り込んでおきます。

出力 CSV には、次のようなデータが格納されています。

id	date	value	sflag
AEM00041194	20210101	178	S
AEM00041218	20210101	157	S
AEM00041217	20210101	155	S
AG000060590	20210101	1	S

この記事では、下記 3列を利用します (詳細は README を確認してください)。

列名	格納データ
id	観測地点の>ID
date	観測日
value	一日の平均気温 (単位は摂氏温度の>10倍)

一日の平均気温 (value) については、Looker の外部で行われた集計であることを分かりやすくするため、以降は一日の平均気温を “気温” と記載します。

目標

集計結果に対して再度集計処理を行うことを、この記事の目標にします。

具体例として、観測地点ごとの週の平均気温の最大値を見れるようにしたいとします。
ただし、最大値を探す観測地点は、自由に選べるようにしたいとします。

集計1：一週間の平均気温を計算する

週	観測地点	週の平均気温
2021-01-04	A	9℃
2021-01-04	B	10℃
2021-01-04	C	12℃
2021-01-11	A	11℃
2021-01-11	B	8℃
2021-01-11	C	10℃

集計2：週の平均気温の最大値を計算する (集計1に対する集計)

全ての観測地点で探す場合

週	週の平均気温最大値
2021-01-04	12℃
2021-01-11	11℃

観測地点 A, B のみで探す場合

週	週の平均気温最大値
2021-01-04	10℃
2021-01-11	11℃

集計1の結果に対して集計を行いたいので、集計1は事前に済ませておく必要があります。この記事では派生テーブルを利用して、集計1の処理を事前に行わせてみます。

派生テーブルによる事前集計

派生テーブルは、DB に格納されているテーブルではなく、クエリ結果を元に view 定義を行うための機能です。
派生テーブルに定義されたクエリの結果をテーブルとして扱い、dimension や measure はこの “テーブル” の持つカラムに対して定義していくイメージです。
派生テーブルには、SQLベースの派生テーブルとネイティブ派生テーブルの二種類があります。大雑把な比較は下表の通りです。

	SQL ベースの派生テーブル	ネイティブ派生テーブル
クエリ対象	テーブル	explore
クエリの定義方法	SQL	LookML
クエリ内で参照できる値	テーブルが持つカラム	explore が持つフィールド

SQL ベースの派生テーブルは view 定義の元になるクエリを、SQL で定義するのみなので学習コストは低いです。

一方、ネイティブ派生テーブルは explore に対するクエリ結果が出発点になるので、explore に結合された view が持つフィールドを使って、派生テーブルのフィールドを定義できます。
そのため、LookML の書き方を身につける必要はありますが、テーブルの結合条件や、既存の dimension などにおける計算ロジックを再定義しなくて済む利点があります。

どちらの派生テーブルでも、クエリ結果に基づいて view を定義することになるので、結合処理の前に、データ変換や集計のような処理を差し込むことができます。

実装

SQL ベースの派生テーブル、およびネイティブ派生テーブルを使って、目標で触れた事前集計を行ってみます。

派生テーブルで作りたいもの

目標で触れたとおり、最終的に欲しいものは観測地点における、週の平均気温の最大値です。

これを計算できるようにするため、次のような結果を返すクエリを、派生テーブルの定義に落とし込みます。

週	観測地点	週の平均気温
2021-01-04	A	9℃
2021-01-04	B	10℃
2021-01-11	A	11℃
2021-01-11	B	8℃

ポイントとして、派生テーブルはクエリ結果を参照する view なので、Looker による集計列も基本的に dimension として定義します。

サンプルの実装は、「週の平均気温」の dimension を定義し、この dimension に対する最大値の measure を定義する流れになります。

コードサンプル

下記コードの日別の気温用の view に、観測地点における週の平均気温を持たせた派生テーブルを結合してみます。
(SQL の dialect は BigQuery の Standard SQL です)

日別気温 view のコード

view: ghcn_daily {
  sql_table_name: ... ;;

  dimension: pk {
    primary_key: yes
    hidden: yes
    type: string
    sql: CONCAT(${id}, ${date}) ;;
  }

  dimension: id {
    label: "観測地点"
    type: string
    sql: ${TABLE}.id ;;
  }

  dimension: date {
    label: "観測日"
    type: date
    datatype: date
    sql: ${TABLE}.date ;;
  }

  dimension: week {
    label: "観測週"
    type: date
    datatype: date
    sql:
      -- 週の月曜日
      DATE_ADD(${date}, INTERVAL -MOD(EXTRACT(DAYOFWEEK FROM ${date}) + 5, 7) DAY)
      ;;
  }

  dimension: value {
    label: "気温"
    type: number
    sql: ${TABLE}.value / 10 ;;
  }

  measure: average_value {
    label: "平均気温"
    type: average
    sql: ${value} ;;
  }
}

Pattern 1: SQL ベースの派生テーブル

説明の都合上、次の流れでコードを作成していきます。

explore の作成
週の平均気温用 view の作成 (派生テーブル)

(1) explore の作成

まず、explore の大枠を作成しておきます。

先に作成した日別気温の view ghcn_daily を起点とし、これから作成する週の平均気温の view ghcn_weekly_sql を結合させます。

explore: ghcn_sql {
  from: ghcn_daily

  join: ghcn_weekly_sql {
    type: left_outer
    relationship: many_to_one
    sql_on:
      ${ghcn_sql.id} = ${ghcn_weekly_sql.id}
      AND ${ghcn_sql.week} = ${ghcn_weekly_sql.week}
      ;;
  }
}

# view 定義
view: ghcn_daily { ... 省略 ... }
view: ghcn_weekly_sql { ... 派生テーブルの定義 (後述) ... }

(2) 週の平均気温用 view の作成 (派生テーブル)

SQL ベースの派生テーブルは、テーブルに対するクエリ結果をテーブルとして扱い、view 定義を進めるイメージです。
(自動生成される SQL では、このクエリが WITH 句に切り出されます)

SQL 派生テーブルのサンプルを下に載せます。

週別平均気温 view のコード

view: ghcn_weekly_sql {
  derived_table: {
    # 観測地点ごとに、週の平均気温を求めるクエリ
    sql:
      SELECT
        t.id,
        DATE_ADD(t.date, INTERVAL -MOD(EXTRACT(DAYOFWEEK FROM t.date) + 5, 7) DAY) AS week,
        AVG(t.value) AS weekly_average_value,
      FROM
        -- view ghcn_daily の、sql_table_name パラメータを参照する
        ${ghcn_daily.SQL_TABLE_NAME} AS t
      WHERE
        -- テンプレートフィルタにより、集計対象にする観測地点を絞り込む
        {% condition ghcn_sql.id %}t.id{% endcondition %}
      GROUP BY
        id,
        week
    ;;
  }

  dimension: pk {
    hidden: yes
    primary_key: yes
    type: string
    sql: CONCAT(${id}, ${week}) ;;
  }

  dimension: id {
    hidden: yes
    type: string
    sql: ${TABLE}.id ;;
  }

  dimension: week {
    hidden: yes
    type: date
    datatype: date
    sql: ${TABLE}.week ;;
  }

  dimension: weekly_average_value {
    hidden: yes
    type: number
    sql: ${TABLE}.weekly_average_value / 10 ;;
  }

  measure: max_weekly_average_value {
    label: "週の平均気温 最大値"
    type: max
    sql: ${weekly_average_value} ;;
  }
}

派生テーブルで実行すべきクエリは、derived_table の sql パラメータで指定します。

ここでは、目標で触れたフィルタの動作を実現するため、派生テーブルによる事前集計のタイミングで、観測地点に対するレコードの絞り込みを行うクエリを設定しました。

詳細な説明は省きますが、view ghcn_daily の id に対するフィルタと同じ絞り込みを条件を事前集計のクエリに課すため、テンプレートフィルタを利用しました。

注意点として、SQL ベースの派生テーブルのクエリ内では、別の view の dimension や measure を参照できないため、これらのフィールドで定義される変換ロジックは派生テーブル側でも定義する必要があります。

Pattern 2: ネイティブ派生テーブル

SQL ベースの派生テーブルの節と同じく、次の流れでコードを作成していきます。

explore の作成
週の平均気温用 view の作成 (派生テーブル)

(1) explore の作成

先の LookML から、ghcn_weekly_sql → ghcn_weekly_ndt に名前を変更したのみです。

explore: ghcn_ndt {
  from: ghcn_daily

  join: ghcn_weekly_ndt {
    type: left_outer
    relationship: many_to_one
    sql_on:
      ${ghcn_ndt.id} = ${ghcn_weekly_ndt.id}
      AND ${ghcn_ndt.week} = ${ghcn_weekly_ndt.week}
      ;;
  }
}

# view 定義
view: ghcn_daily { ... 省略 ... }
view: ghcn_weekly_ndt { ... 派生テーブルの定義 (後述) ... }

(2) 週の平均気温用 view の作成 (派生テーブル)

ネイティブ派生テーブルは、explore へのクエリ結果をテーブルとして扱い、view 定義を進めるイメージです。

ネイティブ派生テーブルのサンプルを下に載せます。

週別平均気温 view のコード

view: ghcn_weekly_ndt {
  derived_table: {
    explore_source: ghcn_ndt {
      # 集計軸: 観測地点、週単位で集計処理を行わせる
      column: id { field: ghcn_ndt.id }
      column: week { field: ghcn_ndt.week }
      # 集計値
      column: weekly_average_value { field: ghcn_ndt.average_value }

      bind_filters: {
        from_field: ghcn_ndt.id
        to_field: ghcn_ndt.id
      }
    }
  }

  dimension: pk {
    hidden: yes
    primary_key: yes
    type: string
    sql: CONCAT(${id}, ${week}) ;;
  }

  dimension: id {
    hidden: yes
    type: string
    sql: ${TABLE}.id ;;
  }

  dimension: week {
    hidden: yes
    type: date
    datatype: datetime
    sql: ${TABLE}.week ;;
  }

  dimension: weekly_average_value {
    hidden: yes
    type: number
    sql: ${TABLE}.weekly_average_value ;;
  }

  measure: max_weekly_average_value {
    label: "週の平均気温 最大値"
    type: max
    sql: ${weekly_average_value} ;;
  }
}

ここでは、Explore の画面で集計結果を得る (=explore へのクエリを行う) 操作と対応付けながら、ネイティブ派生テーブルの定義方法を説明します (Explore を使ってコードを生成することもできます)。

出力列の選択

今回の事前集計では、下の表のように週単位で、観測地点ごとの平均気温を求めることが目標です。

週	観測地点	週の平均気温
2021-01-04	A	9 ℃
2021-01-04	B	10 ℃
2021-01-11	A	11 ℃
2021-01-11	B	8 ℃

この結果を Explore で得るには、週と観測地点のdimension と、週の平均気温の measure を選択することになるかと思います。

上の表の列を定義する (dimension, measure を選ぶ) ことをコードに落とし込む作業は、explore_source の column を定義することにあたります。

column の形式は次の通りです。

column: <カラム名> {
  field: <view名>.<フィールド名>
}

元にする explore のどのフィールドを参照するかは、field パラメータで指定します。<カラム名> は、派生テーブルのフィールド定義の際に、この column を参照するときに使う名前です。

ネイティブ派生テーブルでは、前置きで触れた形式の explore に対するクエリが WITH 句に切り出せれます。
そのため、column でどの dimension を指定するかで、派生テーブルの集計軸が決まります。一方、column に measure を指定することで、事前に行う集計処理を選ぶイメージになります。

フィルタ適用

次に、派生テーブルのクエリに、レコードの絞り込み条件を追加する方法を説明します。

ここでは、目標で触れたフィルタの動作を実現するため、bind_filters を使って、派生テーブルによるクエリの段階で集計対象にする観測地点を絞り込みます。

bind_filters で行うことは、「この派生テーブルを結合する explore」のフィールドに設定したフィルタを、「この派生テーブルからクエリする explore」のフィールドに引き継がせることになります。

イメージはこちらです。

bind_filters の形式は次の通りです。

bind_filters {
  from_field: <view名>.<フィールド名>
  to_field: <view名>.<フィールド名>
}

from_field では、フィルタの引き継ぎ元になる、この派生テーブルを結合する explore のフィールド名を指定します。一方、to_field は、フィルタの引き継ぎ先になる、この派生テーブルからクエリする explore のフィールド名を指定します。

このように、bind_filters を利用することで、派生テーブルのクエリに絞り込み条件を設定できます。

まとめ

Looker による集計処理の方式により、Explore の画面で選ばれた dimension に応じて集計軸が自動で切り替わるのは、非常に強力な点だと思います。
一方この記事では、集計軸は固定になるものの、派生テーブルを利用して結合前に事前集計を行わせてみました。Looker の集計処理の挙動や、派生テーブルの理解の役に立てれば幸いです。

クラウドエースは、Google Cloud 専門のシステムインテグレーターとして、 Looker の導入支援もご用意しております。データ分析基盤構築に定評のあるエンジニアが、お客様の現状ヒアリングからPOC・導入・運用・活用までをワンストップでサポートいたします。

Looker に関するお悩み事がありましたら、お気軽にお問い合わせください。

また、 Looker 導入丸わかり資料を公開しておりますので、こちらも合わせてぜひ御覧ください。

Looker の派生テーブルと事前集計について

前置き

Looker による集計の挙動

結合前の事前集計について

サンプルデータについて

テーブルデータの用意

目標

集計1：一週間の平均気温を計算する

集計2：週の平均気温の最大値を計算する (集計1に対する集計)

全ての観測地点で探す場合

観測地点 A, B のみで探す場合

派生テーブルによる事前集計

実装

派生テーブルで作りたいもの

コードサンプル

Pattern 1: SQL ベースの派生テーブル

(1) explore の作成

(2) 週の平均気温用 view の作成 (派生テーブル)

Pattern 2: ネイティブ派生テーブル

(1) explore の作成

(2) 週の平均気温用 view の作成 (派生テーブル)

出力列の選択

フィルタ適用

まとめ

Looker でヒストグラムを作る３つの手法

BI ツールとは？ Google Cloud のプロダクトを使ってデータを可視化

前置き

Looker による集計の挙動

結合前の事前集計について

サンプルデータについて

テーブルデータの用意

目標

集計1：一週間の平均気温を計算する

集計2：週の平均気温の最大値を計算する (集計1に対する集計)

全ての観測地点で探す場合

観測地点 A, B のみで探す場合

派生テーブルによる事前集計

実装

派生テーブルで作りたいもの

コードサンプル

Pattern 1: SQL ベースの派生テーブル

(1) explore の作成

(2) 週の平均気温用 view の作成 (派生テーブル)

Pattern 2: ネイティブ派生テーブル

(1) explore の作成

(2) 週の平均気温用 view の作成 (派生テーブル)

出力列の選択

フィルタ適用

まとめ

Looker でヒストグラムを作る３つの手法

BI ツールとは？ Google Cloud のプロダクトを使ってデータを可視化

関連記事

Looker でヒストグラムを作る３つの手法