この記事は 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さんが公開予定です。そちらもぜひご覧下さい。