アグリゲートアウェアネスによるパフォーマンス改善

[shinichi_kawaka]

本トピックは、弊社の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が選択もしくは、フィルターで利用されている場合は、週次サマリーテーブルなどというルーティングを行います。 その結果、生のオーダートランザクションテーブルに対してトラフィックは遥かに少なくなります。

このアプローチにより、全体的なデータベース接続に対して劇的なインパクトがあります。特に、トランザクションやイベントレベルのデータに対して多量のクエリが発行されている場合。これは、遅いクエリが実行されていると、高速なクエリが背後でスタックしてしまうという事実に一部起因しています。上位で集約されたデータで対応できる要求が多いほど、データ要求の移動をより効率的に行うことが可能になります。この要求処理の強化により、接続単位での遅延の可能性減らし、ここのクエリ自体のレスポンスも向上します。

ETLにより集約できない場合 (PDTと組合せてこれを利用します)

上記のサンプルでは、これらのプログレッシブな集約を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変数と利用してにアグリゲート・アウェアネスを使用されることを推奨します。