Lookerの機能の一つである派生テーブルはご存じでしょうか？派生テーブルには2種類あり、前回は一時的派生テーブルについて紹介しました。
今回は、永続的派生テーブルについて詳しく紹介していきます。
永続的派生テーブル（PDT）とは
永続的派生テーブルはPDT（Persistent Derived Table）とも呼ばれる、データベースに直接書き込まれ、指定したスケジュールで再生成される派生テーブルのことです。PDTにおいても、『SQL派生テーブル』『ネイティブ派生テーブル』のいずれかで定義します。
PDTの作り方
PDTを作成するには、以下の設定が必要になります。
- PDTに対応しているデータベースで、PDTを有効にする
- PDTの書き込み用のスキーマを指定する
- 派生テーブルを永続化する
PDTの有効化と書き込み用のスキーマ指定
- まずはConnetionの設定画面を開き、PDTを有効にするをONにします
- また、データベース側で作成した、PDT書き込み用にスキーマを一時データベースに記載します
PDTの再生成ポリシーを適用して派生テーブルを永続化する
派生テーブルに以下のパラメーターでPDTの再生成ポリシーを適用することで、永続化することができます。
いわゆるPDTを作成する特別な設定はなく、派生テーブルにポリシーを適用した時点で派生テーブルが永続化されます。
datagroup_trigger
クエリのキャッシュポリシーの適用方法としても用いられるdatagroupを使用することでPDTの再生成ポリシーを適用します。datagroupは、最も柔軟性の高いポリシーを設定できます。この際、サブパラメーターとしてsql_triggerまたはinterval_triggerを使用して定義した場合、datagroup_triggerパラメータを使用してPDTの再生成ルールを定義できます。
公式Doc：データグループによるクエリのキャッシングとPDTの再構築
## model ファイルで datagroup の定義
datagroup: pdt_datagroup {
max_cache_age: "24 hours"
sql_trigger: SELECT MAX(date) ;;
}
## view ファイルで datagroup_trigger の適用
derived_table: {
sql: SELECT
date
FROM
`database_name.sample_superstore`
GROUP BY 1 ;;
datagroup_trigger: pdt_datagroup
}sql_trigger_value
1 つの値 (1 行と 1 列) だけを返すクエリを作成し、作成したSQLの結果に基づいてPDTの再生成をトリガーします。SQL文の結果が前の値と異なる場合、PDTが再生成されます。
##sql_trigger_value適用例
derived_table: {
sql: SELECT
date
FROM
`database_name.sample_superstore`
GROUP BY 1 ;;
sql_trigger_value: SELECT MAX(date) ;;
}なお、デフォルトでは、別の永続的な派生テーブルが構築中でない限り、Lookerはここで定義したSQLクエリを5分ごとに実行します。このスケジュールは、PDT およびデータグループ メンテナンス スケジュールで変更することができます。
interval_trigger
パラメータは、"24 hours" や "60 minutes" など、指定した時間間隔に基づいて、PDTの再生成をトリガーします。
##interval_trigger適用例
derived_table: {
sql: SELECT
date
FROM
`database_name.sample_superstore`
GROUP BY 1 ;;
interval_trigger: "24 hours"
}persist_for
PDTが作成されデータベースから削除されるまでの保存期間を設定します。このパラメーターを使用するとユーザーが最初にクエリを実行したときにPDTが作成され、persist_for パラメータで指定された期間PDTをデータベースに保持します。設定した期間を超過すると、LookerはデータベースからPDTを消去します。そのPDTはユーザーが次にPDTを含むクエリを実行したそのタイミングで再生成するため、予期せぬタイミングでPDTが作成されるケースもあるという点に注意が必要です。
##persist_for適用例
derived_table: {
sql: SELECT
date
FROM
`database_name.sample_superstore`
GROUP BY 1 ;;
persist_for: "24 hours"
}さいごに
改めて、派生テーブルの永続化方法について記載してみました。PDTは再生成ロジックを設定する事で作成される＝再生成のポリシーが必須で、更新ルールのないテーブルを使用できないようにする仕組みになっています。次回は、増分PDTについて確認していきたいと思います！
3
