🔖
Lookerの備忘録① PDT(Persistent Derived Table)について。遊戯王でいうところの永続魔法

2024/12/09に公開
効率化
 Lookerにおける備忘録Lookerに関する備忘録をこれから記事化していきます。

基本的には公式ドキュメントのまとめですが、僕自身が業務内で使っていて、便利かつ知っておくべきだろう知識をまとめていきます。
Looker Studioではないことを注意してください。

本当に全く違うので！笑
ちなみにXでフォローしているGoogleでLooker担当されている方のツイートでもこんな感じに言われています。

https://x.com/tomoya_cs/status/1738098253613482133
さて、蛇足はここらへんまでにしまして、ここからは真面目に話をしていきます。
今回はPDTに関して話していこうと思います。

元のDBにある膨大なデータを毎回クエリーするとLooker用に繋げているWHの大きさがツヨツヨでない限りは結果の表示にかなりの時間を要します。

Lookerに繋がれているWHがとても小さくてパフォーマンスを最適化しないといけない、という需要は今の強いWHによってでかいデータをぶん回して結果を毎回出せます！という世界とは裏腹に、一定まだあると思います。

今回はそのような似たような境遇の読者向けに記事を書いていきたいと思います。
環境
Looker (Google Cloud Host)
Snowflake (extra small WH)
今回はこのPDTに関して自身の後学のためなので、他の方と内容が被っていてもご容赦ください。

（基本はLookerの公式マニュアルを参照のもと）

 PDTとは何か？PDT（Persistent Derived Table）は、Lookerが管理・再構築する永続的な中間テーブルです。永続的と言われると遊戯王の永続魔法を思い出してしまいますが、まぁ似たようなもので永続的に指定したデータの結果をLookerという場（フィールド）に保持しておいてね（期間限定だけど）、というざっくり理解です。通常Lookerでは、ユーザーがExploreなどで可視化リクエストをするたびにDBへSQLを発行します。しかしデータ量が膨大だと、毎回のクエリ実行に時間がかかり、ユーザー体験が悪化します。

PDTを利用すると、よく使われる複雑な集計や結合結果を事前にLooker側でテーブル化・キャッシュ化できます。そのためユーザーがアクセスしたときには、わざわざDBを叩かずにPDTから結果を取得できるため、レスポンスタイムが大幅に向上します。（ということはDB側でそれ用のテーブルを用意するということもしなくてよくなる）
全体に対してまずはLookerとDB間の間でPDTを設定できます。

例として毎週の月曜から金曜日だけ毎時10分ごろにPDT構築をするか確認してください、というように設定できます。(Adminが必要です！)
これは永続魔法に対するきっかけのようなものですね。

なので土日とかはユーザーがキャッシュのクリアとリロードしない限りは更新されない＝コストを抑えることができます。

そもそもLookerは頭がいいのでcacheから結果を返せるものに関してはcacheから引用してくれるので、PDTとは別にコストを削減できたりします。

この公式ドキュメントによると
デフォルトのキャッシュ保持ポリシーは 1 時間です。
とのこと。

ただ肌感としては1日１回程度の更新の結果に関しては、結構キャッシュから返してくれるので、デフォでかなり節約してくれている感じはあります。（本当かどうかは確認してないですが、、、）
以下ではデフォルトキャッシュに依存しない方法を説明します。

 PDTとmaterializationの関係PDTは一種の「Looker版マテリアライズドビュー」です。

derived_tableパラメータで定義したSQLをLookerが定期的またはトリガーベースで再構築し、DB内に永続テーブルを保持します。

persist_forやsql_trigger_valueといったパラメータを用いることで、再構築タイミングを柔軟に制御できる点がポイントです。

 LookMLでのPDT実装方法
 時間指定でPDTをリフレッシュするpersist_forを使うことで、一定時間後に自動的にPDTを更新可能です。以下は24時間ごとに更新する例です。

例えば注文数のカウントとそれによる収益のデータを永続化したいとき。

このような場合は大体24時間後には元データが更新されるので24時間PDTがあれば事足ります。
view: orders_summary {
  derived_table: {
    sql:
      SELECT
            user_id
            ,COUNT(*) AS order_count
            ,SUM(price) AS total_revenue
      FROM
            WH.Schema.orders
      GROUP BY 1
    ;;
    persist_for: "24 hours"  # 24時間ごとにPDTを再作成
  }

  dimension: user_id {
    type: number
  }

  measure: order_count {
    type: count
    sql: ${TABLE}.order_count ;;
  }

  measure: total_revenue {
    type: sum
    sql: ${TABLE}.total_revenue ;;
  }
}
トリガーベースでPDTをリフレッシュする

sql_trigger_valueを用いると、任意のクエリ結果が変わったタイミングでPDTを再構築できます。

以下は更新日時の最大値が変化したら再生成する例です。

ここのポイントは元データの更新に合わせてPDTの更新をさせるということです。例えば24時間の保持だとデータ遅れなどの影響で更新されていないPDTデータが永続化されることになるので、動的にPDTさせたい場合はこちら。
view: product_sales_agg {
  derived_table: {
    sql:
      SELECT
            product_id
            ,SUM(sales) AS total_sales
            ,COUNT(DISTINCT order_id) AS unique_orders
      FROM
            WH.Schema.product_sales
      GROUP BY 1
    ;;
    sql_trigger_value: SELECT MAX(updated_at) FROM `my_project.my_dataset.product_sales` ;;
  }

  dimension: product_id {
    type: number
  }

  measure: total_sales {
    type: sum
    sql: ${TABLE}.total_sales ;;
  }

  measure: unique_orders {
    type: count_distinct
    sql: ${TABLE}.unique_orders ;;
  }
}

 応用的な利用例JOIN済みテーブルのPDT化：

複数テーブルをJOINした集計テーブルをPDTとして保持すれば、Explore上の操作はPDTへのクエリだけで済み、高速化できます。

このようなケースではJOINした後のテーブルを用意するよりも、二つの元テーブルがあることによって情報として元ソースを残しつつExplore上での体験をよりよくするということが可能です。

ここはいずれ記事にするSemantic Layer（セマンティックレイヤー）としての働きに大きく関わってきます。
たとえば、ordersテーブルとusersテーブルをJOINして、ユーザー単位の売上集計を行うPDTを作成するとします。
view: user_orders_summary {
  derived_table: {
    sql:
      SELECT
            u.user_id
            ,u.user_name
            ,COUNT(o.order_id) AS total_orders
            ,SUM(o.order_total) AS total_spent
      FROM
            WH.Schema.orders AS o
      LEFT JOIN
            WH.Schema.users AS u
        ON
             o.user_id = u.user_id
      GROUP BY
             u.user_id, u.user_name
    ;;
    persist_for: "24 hours" # 1日に1回集計テーブルを更新
  }

  dimension: user_id {
    type: number
  }

  dimension: user_name {
    type: string
  }

  measure: total_orders {
    type: count
    sql: ${TABLE}.total_orders ;;
  }

  measure: total_spent {
    type: sum
    sql: ${TABLE}.total_spent ;;
  }
}
既存マテリアライズドビューの補助として：

DB側のマテリアライズドビューと組み合わせて、さらに上乗せ集計をPDTで行うことも可能。

こうしたアプローチで、Looker上の利便性・パフォーマンスを一段引き上げることができます。
例としてDB側で既にtransactions_mvというマテリアライズドビュー(単なるviewでも良いし、物理テーブルをtaskやprocedureで作成しているものでも良い)を用いて売上に関するデータを含むトランザクションテーブルがあるとします。

しかし、Looker側で日次やカテゴリ別の分析を行いたい場合、再度計算が必要になります。

そこでtransactions_mvを取り込んだうえで、PDTでさらに細分化した集計（カテゴリ別・日次別など）を行います。

transactions_mv更新時にPDTも再構築することで、常に最新の細粒度データを高速に提供できます。

ここでのポイントは元の参照テーブルから派生するあらゆる結果をPDTとして迅速に参照できること。例えばSnowflakeにひとつひとつそれ用のテーブルを用意する、みたいなことは全くしなくて良い。
view: category_daily_sales {
  derived_table: {
    sql:
      SELECT
            category
            ,DATE(transaction_date) AS transaction_date
            ,SUM(total_revenue) AS daily_revenue
            ,COUNT(DISTINCT user_id) AS unique_customers
      FROM
            WH.Schema.transactions_mv
      GROUP BY
             category, DATE(transaction_date)
    ;;
    sql_trigger_value: SELECT MAX(last_updated) FROM WH.Schema.transactions_mv #動的にPDTを構築させるため
  }

  dimension: category {
    type: string
  }

  dimension: transaction_date {
    type: date
    sql: ${TABLE}.transaction_date ;;
  }

  measure: daily_revenue {
    type: sum
    sql: ${TABLE}.daily_revenue ;;
  }

  measure: unique_customers {
    type: count_distinct
    sql: ${TABLE}.unique_customers ;;
  }
}
次はDatagroupについてです
Lookerにおける備忘録

PDTとは何か？

PDTとmaterializationの関係

LookMLでのPDT実装方法

時間指定でPDTをリフレッシュする

応用的な利用例

Discussion
