Lookerの派生テーブルを使って、集計量を減らす #SQL

この記事は ZOZOテクノロジーズ #1 Advent Calendar 2019 9日目の記事になります。
昨日の記事は @hsawada さんによる「Xcode11 での動画再生と Playback Controls について」でした。
コードや実際のスクショを用いた説明が多く、とても分かりやすかったです。

今回はBIツールであるLookerにおいて、派生テーブルを使って集計量を減らす話です。

発生する集計量の問題

LookerはGUIから見たい指標を選択するだけで、テーブルを叩くためのクエリを生成してデータを閲覧できるようにしてくれます。
適切にLookMLを記述することでクエリは複数のテーブルのJOINも自動で行ってくれるため、探索の時間を大幅に削減することが可能です。

例えば、以下のようなexploreとveiwが設定されているとします。


explore: user {
  join: message {
    type: inner
    relationship: one_to_many
    sql_on: ${user.user_id} = ${message.user_id} ;;
  }
}

view: user {
  sql_table_name: bigquery.user ;;

  dimension: user_id {
    type: number
    sql: ${TABLE}.user_id ;;
  }

  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }
}

view: message {
  sql_table_name: bigquery.message ;;

  dimension: user_id {
    type: number
    sql: ${TABLE}.user_id;;
  }

  measure: message_count {
    type: count
  }
}

このような場合にageとmessage_countと表示しようとすると
以下のようなクエリが発行されます

SELECT
  user.age  AS user_age,
  COUNT(*) AS message_message_count
FROM bigquery.user  AS user
INNER JOIN bigquery.message  AS message ON user.user_id = message.user_id

GROUP BY 1
ORDER BY 2 DESC
LIMIT 500

そのままですね。ただ、BigQueryのテーブルは1つ1つが膨大なことが多く、
実際に分析していく際は、JOIN前にWHEREなどで件数を絞って無駄なアクセスを減らすと思います。
LookerもGUIの画面からフィルターを選択することができます。
例えば、ageを30以下にするとこのようなクエリとなります。

SELECT
  user.age  AS user_age,
  COUNT(*) AS message_message_count
FROM bigquery.user  AS user
INNER JOIN bigquery.message  AS message ON user.user_id = message.user_id

WHERE
  (user.age  <= 30)
GROUP BY 1
ORDER BY 2 DESC
LIMIT 500

はい、JOIN後にWHEREが指定されているので、
userもmessageも全レコードをJOINしてしまうことになります。
このままでは時間もお金もかかりますね。

そこで活躍するのが派生テーブルです。

派生テーブルとは

派生テーブルはBigQueryのテーブルをそのまま使わず
特定のカラムであったり、ある程度集計を行った結果を1つのテーブルとして定義しておける機能です。

派生テーブルの中にもLookMLを活用したネイティブ派生テーブルと、SQLベースの派生テーブルの2種類があるのですが
今回はSQLベースの派生テーブルを説明します。
（ネイティブ派生テーブルの方法は他の記事で、、、）

派生テーブルを使ったフィルタ

先ほどの「ageを30以下にするフィルター」を派生テーブルを使って実現しようとすると
userのviewはこうなります。

view: user {
  derived_table: { # 派生テーブルの定義
    sql:
      SELECT user_id, age
      FROM bigquery.user
      WHERE age <= 30 ;;
  }

  dimension: user_id {
    type: number
    sql: ${TABLE}.user_id ;;
  }

  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }
}

この場合に発行されるクエリはこちらです。

WITH user AS (SELECT user_id, age
  FROM bigquery.user
  WHERE age <= 30 )
SELECT
  user.age  AS user_age,
  COUNT(*) AS message_message_count
FROM user
INNER JOIN bigquery.message  AS message ON user.user_id = message.user_id

GROUP BY 1
ORDER BY 2 DESC
LIMIT 500

勝手にWITH区を使ってくれていて、とてもクールですね。
ただこれだけだと、30以下という指定を柔軟に変更できないので自由に指定できるようにします。

view: user {
  derived_table: { # 派生テーブルの定義
    sql:
      SELECT user_id, age
      FROM bigquery.user
      WHERE {% condition age_filter %} age {% endcondition %} ;;
  }

  filter: age_filter { # 追加したフィルター
    type: number
  }

  dimension: user_id {
    type: number
    sql: ${TABLE}.user_id ;;
  }

  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }
}

{condition}はGUIで指定された範囲に合わせて、式を生成してくれるLookMLの組み込み関数です。
分析が柔軟になるだけではなく、LookMLも簡潔に記述できるのでおすすめです。

まとめ

やっていること自体はシンプルですが、これだけで探索の速度がとても上がります。
ネイティブ派生テーブルを使う方法は、もう少し勉強したら公開します。

明日は、@e_tyuboさんが公開予定です。そちらもぜひご覧下さい。