本トピックは、弊社のHelpに記載のAggregate Awareness using _in_queryを翻訳したものになります。
Liquid attribute の _in_query
を利用することで、Exploreでユーザがどのフィールドを選択し、SQLを動的に変更することが可能になります。これにより、多くの潜在的なユースケースが考えられます。この記事では、データベースリソースの消費を削減することで合理的にパフォーマンスを向上させる「アグリゲート・アウェアネス」と呼ばれる概念に焦点を当てています。
Lookerの多くのお客様は、イベントレベルまたはトランザクションレベルで非常に大量の詳細なデータをレポーティングしています。これは、個々のレコードの調査など、特定のシナリオには役立ちますが、基本的な統計だけが必要な場合、詳細なデータのクエリには多大なコストがかかります。 一般的な解決策は、これらのユースケース用に、より集約されたテーブルを作成することです。この場合、クエリのコストは低くなりますが、それぞれ独自のエクスプローラを利用する必要があります。そして、エンドユーザーが利用すべき最適なエクスプローラを知らない状況が発生する可能性があります。
_in_query
を利用することにより、一つのExploreだけを利用して、最適な集約データへのルーティングを行うことが可能になり、この問題を解決することができます。これは、基本的な統計に対しての問合せ時間を減らすことが可能です。
これは、LookerのLiquid variable ドキュメントにて提供されているものに近い基本的な利用例です:
view: orders {
sql_table_name:
{% if orders.created_date._in_query %}
orders
{% elsif orders.created_week._in_query %}
orders_smry_week
{% elsif orders.created_month._in_query %}
orders_smry_month
{% else %}
orders_smry_year
{% endif %} ;;
dimension_group: created {
type: time
timeframes: [date, week, month, year]
sql: ${TABLE}.created_at ;;
}
このサンプルにおいて、時間をベースに集約された異なるレベルのテーブルが4つあります: orders
, orders_smry_week
, orders_smry_month
, および orders_smry_year
。他の点においては、同じスキーマとなっています。if view_name.field_name._in_query
ロジックにより、created_date
が選択もしくは、フィルターで利用されている場合は、トランザクションテーブル。 created_week
が選択もしくは、フィルターで利用されている場合は、週次サマリーテーブルなどというルーティングを行います。 その結果、生のオーダートランザクションテーブルに対してトラフィックは遥かに少なくなります。
このアプローチにより、全体的なデータベース接続に対して劇的なインパクトがあります。特に、トランザクションやイベントレベルのデータに対して多量のクエリが発行されている場合。これは、遅いクエリが実行されていると、高速なクエリが背後でスタックしてしまうという事実に一部起因しています。上位で集約されたデータで対応できる要求が多いほど、データ要求の移動をより効率的に行うことが可能になります。この要求処理の強化により、接続単位での遅延の可能性減らし、ここのクエリ自体のレスポンスも向上します。
上記のサンプルでは、これらのプログレッシブな集約をLookerとは別に作成されることを前提としています。LookMLのモデリングレイヤを利用して実現する場合は、Persistent derived tables (PDTs) を利用します:
view: event_facts_yearly {
derived_table: {
sql_trigger_value: select DATE_TRUNC('year',current_date) ;;
distribution_style: all
sortkeys: ["TIME"]
sql:
SELECT
DATE_TRUNC('year',created_at) AS TIME
,event_type
,traffic_source
,browser
,os
,country
,uri
,COUNT(*) AS COUNT
,COUNT(DISTINCT user_id) AS DISTINCT_USERS
FROM
PUBLIC.EVENTS
WHERE
1=1
GROUP BY
1,2,3,4,5,6,7
;;
}
}
view: event_facts_monthly {
derived_table: {
sql_trigger_value: select DATE_TRUNC('month',current_date) ;;
distribution_style: all
sortkeys: ["TIME"]
sql: SELECT
DATE_TRUNC('month',created_at) AS TIME
,event_type
,traffic_source
,browser
,os
,country
,uri
,COUNT(*) AS COUNT
,COUNT(DISTINCT user_id) AS DISTINCT_USERS
FROM
PUBLIC.EVENTS
WHERE
1=1
GROUP BY
1,2,3,4,5,6,7
;;
}
}
view: event_facts_daily {
derived_table: {
sql_trigger_value: select current_date ;;
distribution_style: all
sortkeys: ["TIME"]
sql:
SELECT
DATE_TRUNC('day',created_at) AS TIME
,event_type
,traffic_source
,browser
,os
,country
,uri
,COUNT(*) AS COUNT
,COUNT(DISTINCT user_id) AS DISTINCT_USERS
FROM
PUBLIC.EVENTS
WHERE
1=1
GROUP BY
1,2,3,4,5,6,7
;;
}
}
view: events {
derived_table: {
sql:
SELECT * FROM
{% if time_date._in_query %}
${event_facts_daily.SQL_TABLE_NAME}
{% elsif time_month._in_query %}
${event_facts_monthly.SQL_TABLE_NAME}
{% elseif time_year._in_query %}
${event_facts_yearly.SQL_TABLE_NAME}
{% else %}
PUBLIC.EVENTS
{% endif %}
;;
}
dimension_group: time {
timeframes: [raw,date,month,year]
type: time
sql: ${TABLE}.TIME ;;
}
dimension: event_type {
type: string
sql: ${TABLE}.event_type ;;
}
dimension: traffic_source {
type: string
sql: ${TABLE}.traffic_source ;;
}
dimension: browser {
type: string
sql: ${TABLE}.browser ;;
}
dimension: os {
type: string
sql: ${TABLE}.os ;;
}
dimension: country {
type: string
sql: ${TABLE}.country ;;
}
dimension: uri {
type: string
sql: ${TABLE}.uri ;;
}
measure: count {
type: sum
sql: ${TABLE}.count ;;
drill_fields: [time_date,count]
}
measure: distinct_users {
type: sum
sql: ${TABLE}.distinct_users ;;
}
}
explore: test_events {}
このサンプルでは、各レベルの集約を独自のPDTとして作成しています(毎年、毎月、毎日)。 それ以外は、生のイベントテーブルを利用します。ビューにフィールドを定義する必要さえないことに注意してください。 しかし、イベントテーブルは、ユーザーが選択した内容に基づいて、ローテーブル、日次、月次、または年次のテーブルを選択する「WITH」句を記述します。 Lookerは、指定された sql_trigger_value
に従ってPDTを更新し続けます。
注: We are using a SQL derived table rather than
sql_table_name
よりも SQL派生テーブルを利用しています。なぜなら${pdt_view.SQL_TABLE_NAME}
をsql_table_name
の内部で利用できないからです。
上記サンプルでは、時間による集約となっていましたが、ディメンションのどのタイプでも利用可能です。
2つのテーブルからなる他の例を見てみましょう: 一つは基本的な集約テーブル、もう一つはuser_id
のようなカーディナリ変数を利用した例となります :
view: usage_facts {
sql_table_name:
{% if usage_facts.user_id._in_query %}
summarized_by_user_id
{% else %}
basic_summary
{% endif %} ;;
dimension: user_id {
type: number
sql: ${TABLE}.user_id ;;
}
dimension: usage {
type: number
sql: ${TABLE}.usage ;;
}
measure: {
type: sum
sql: ${usage} ;;
}
これで、利用量の情報が必要になったと場合、user_id
を選択もしくはフィルタで利用した場合のみ、よりコストのかかる、粒度の低いsummarized_by_user_id
を利用します。
ユースケースの要求に応じて高度な機能を実現できます。SQLとLiquid sql_table_name
の内部でLiquidを最大限に利用できます。パラメータフィールドへのユーザー入力に基づいてこれらを作成し、テーブル間の複雑なルーティングのifステートメントをネストできます。
以下は、すべてのコンセプトを一度に利用した複雑なサンプルです。例えば、ユーザー・レベルの情報があり、メジャーがuser_id
によりグループ化されているかにより変わってくるとしましょう。すでに既存で本情報を日次で集約されたものを持っていますが、月次でも集約したい。また、ユーザーレベルにおいても、日次・月次での集約が必要だとします。エンド・ユーザーは、user_id
を集約した合計に反映させるかを選択する必要があります。
本目的のため、以下のようなコードをを記載するでしょう:
view: event_facts_monthly {
derived_table: {
sql_trigger_value: select DATE_TRUNC('month',current_date) ;;
distribution_style: all
sortkeys: ["TIME"]
sql:
SELECT
DATE_TRUNC('month',created_at) AS TIME
,COUNT(*) AS COUNT
FROM
PUBLIC.EVENTS
WHERE
1=1
GROUP BY
1
;;
}
}
view: event_facts_user {
derived_table: {
sql_trigger_value: select DATE_TRUNC('day',current_date) ;;
distribution_style: all
sortkeys: ["TIME"]
sql:
SELECT
DATE_TRUNC('day',created_at) AS TIME
,user_id
,COUNT(*) AS COUNT
FROM
PUBLIC.EVENTS
WHERE
1=1
GROUP BY
1,2
;;
}
}
view: event_facts_user_monthly {
derived_table: {
sql_trigger_value: select DATE_TRUNC('month',current_date) ;;
distribution_style: all
sortkeys: ["TIME"]
sql:
SELECT
DATE_TRUNC('month',created_at) AS TIME
,user_id
,COUNT(*) AS COUNT
FROM
PUBLIC.EVENTS
WHERE
1=1
GROUP BY
1,2
;;
}
}
view: events {
derived_table: {
sql:
-- This notation is just to make create a more readable user_input variable for casing off the value the user put in {% assign user_input = events.calc_method_value_pass_through._sql %}
SELECT * FROM
-- Select the appropriate table for user_level calculation
{% if user_input contains 'User Level' %}
-- Select the appropriate table based on time grain chosen
{% if time_date._in_query or time_week._in_query %}
${event_facts_user.SQL_TABLE_NAME}
{% else %}
${event_facts_user_monthly.SQL_TABLE_NAME}
{% endif %}
{% else %}
-- Select the appropriate table based on time grain chosen
{% if time_date._in_query or time_week._in_query %}
schema.event_facts -- For example if this table already existed in another schema and was not a PDT assigned in Looker
{% else %}
${event_facts_monthly.SQL_TABLE_NAME}
{% endif %}
{% endif %}
;;
}
dimension_group: time {
type: time
timeframes: [date, week, month, year]
sql: ${TABLE}.TIME ;;
}
filter: calc_method {
type: string
suggestions: [
"User Level",
"Global"
]
}
dimension: calc_method_value_pass_through {
hidden: yes
type: string
sql: {% parameter calc_method %} ;;
}
measure: count {
type: sum
sql: ${TABLE}.count ;;
drill_fields: [time_date,count]
}
}
クエリの高速化を行い、データベース・リソースを有効活用するためにも、LookMLのベスト・プラクティスとして_in_query
変数と利用してにアグリゲート・アウェアネスを使用されることを推奨します。