派生テーブルの使用

派生テーブルは分析の洗練度を高めることができるLookerの重要なツールです。クエリのパフォーマンスを向上させる重要な役割を担うこともあります。

簡単に説明すると、Lookerの派生テーブル機能はデータベース内にまだ存在しない新たなテーブルを作成するための手段です。

派生テーブルの定義は以下のいずれの方法で行います。

LookMLを使用してネイティブ派生テーブル（NDT）を定義する。

ネイティブ派生テーブルはLookMLで定義され、モデルに含まれるディメンションやメジャーが参照先となります。SQLベースの派生テーブルと比較して、データをモデル化するときの読みやすさ、理解しやすさ、推測しやすさが向上します。
SQLクエリを派生テーブルの定義に変換する。

このタイプの派生テーブルはSQLで定義され、データベースに含まれるテーブルと列が参照先となります。SQL Runnerを使用してSQLクエリを作成し、派生テーブルの定義に変えることができます。SQLベースの派生テーブルでLookMLのディメンションとメジャーを参照先とすることはできません。

その後、他のテーブルと同様に、LookMLビューを基礎として派生テーブルを作成できます。

簡単な例

概念を明確にするために、例を使用します。データベースにすでにorderという名前のテーブルが含まれ、これらの注文データの一部を顧客ごとにまとめたいとします。そのために、customer_order_factsという新しい派生テーブルを作成します。

NDTとSQLベースの派生テーブルとしてcustomer_order_facts派生テーブルを作成するためのLookMLは次のとおりです。

ネイティブ派生テーブル

view: customer_order_facts { derived_table: { explore_source: orders { column: customer_id { field: orders.customer_id } column: first_order { field: orders.first_order } column: lifetime_amount { field: orders.lifetime_amount } } } dimension: customer_id { type: number primary_key: yes sql: ${TABLE}.customer_id ;; } dimension_group: first_order { type: time timeframes: [date, week, month] sql: ${TABLE}.first_order_date ;; } dimension: lifetime_amount { type: number value_format: "0.00" sql: ${TABLE}.lifetime_amount ;; } }

SQLベースの派生テーブル

view: customer_order_facts { derived_table: { sql: SELECT customer_id, MIN(DATE(time)) AS first_order_date, SUM(amount) AS lifetime_amount FROM orders GROUP BY customer_id ;; } dimension: customer_id { type: number primary_key: yes sql: ${TABLE}.customer_id ;; } dimension_group: first_order { type: time timeframes: [date, week, month] sql: ${TABLE}.first_order_date ;; } dimension: lifetime_amount { type: number value_format: "0.00" sql: ${TABLE}.lifetime_amount ;; } }

留意すべき点は次のとおりです。

derived_tableパラメーターを派生テーブルの基礎となるビューとして使用しています。
ビューの最上部のいくつかの派生テーブルパラメーター（derived_table、さらにexplore_sourceまたはsql）を除けば、このビューは他のビューと同様に動作します。
結果セットの列名（${TABLE}.first_order_dateなど）が、ディメンションで参照を行うために必要な名前となります。そのため、SQLベースの派生テーブルを定義する場合は、ASを使用して、各列に明確なエイリアスを付ける必要があります。単なるMIN(DATE(time))ではなくMIN(DATE(time)) AS first_order_dateと記述したのはそのためです。
Google Cloud SQL、IBM Netezza、MemSQLなどの一部のダイアレクトは、単独のステップでのSQLCREATE TABLE AS SELECT文に対応していません。これらのダイアレクトでは、上記の方法でPDTを作成できません。ただし、create_processパラメーターを使用して複数のステップでPDTを作成することは可能です。
Googleの機械学習用BigQuery MLなど、カスタムDDLを用いるダイアレクトのPDTを作成する必要がある場合には、sql_createパラメーターを使用できます。

一時的な派生テーブルと永続的な派生テーブル

派生テーブルは一時的なものにする（エフェメラル）ことも、データベースに格納する（永続的）ことも可能です。

標準派生テーブルまたは”エフェメラル”派生テーブル

標準派生テーブルは、—エフェメラル（すぐに消える）派生テーブルとも呼ばれる—一時的なテーブルであり、データベースには書き込まれません。1つまたは複数の派生テーブルに関与するExploreクエリをユーザーが実行すると、Lookerは、それらの派生テーブルの、ダイアレクト固有のSQLの組み合わせと、リクエストされたフィールド、結合、フィルタ値を使用してSQLクエリを構築します。その組み合わせが以前に実行されたものであり、キャッシュ内の結果がまだ有効な場合には、Lookerはキャッシュされたこれらの結果を使用します。

エフェメラル派生テーブルは、ユーザーがテーブルのデータをリクエストするたびにデータベース上で新規のクエリを実行するため、派生テーブルの効率が良好で、データベースに過大な負荷をかけないことが重要となります。クエリの実行に時間を要する場合には、一般に永続的な派生テーブルが適しています。

派生テーブルに対応するデータベースダイアレクト

Lookerによる派生テーブルの提供可否は、データベースダイアレクトが派生テーブルをサポートするかどうかに依存します。次に、最新のLookerリリースにおいて派生テーブルに対応しているダイアレクトを示します。

永続的な派生テーブル

永続的な派生テーブル（PDT）は、データベースのスクラッチスキーマに書き込まれ、選択したスケジュールで再生成されます。大半の場合、ユーザーがテーブルからのデータをリクエストする際には、すでに作成されているので、クエリ時間とデータベースへの負荷が低減されます。

この機能を使用するには、データベース上でのスクラッチスキーマの作成が必要になります。また、データベースへの書き込みが可能であることも必須です。Lookerをデータベースにつなげる接続では、永続的な派生テーブルチェックボックスもオンにする必要があります。ほとんどの企業では、この設定をLookerの初回構成の際に行いますが（データベースダイアレクトに関する手順はLookerダイアレクトのドキュメンテーションページを参照）、初回構成後でも設定できます。

読み取り専用データベース構成の中には、永続性の動作を許可しないものもあります（Postgresホットスワップレプリカデータベースによく見られます）。こうした場合には、代わりにエフェメラル派生テーブルを使用できます。

PDTに対応するデータベースダイアレクト

LookerによるPDTの提供可否は、データベースダイアレクトがPDTをサポートするかどうかに依存します。次に、最新のLookerリリースにおいてPDTをサポートするダイアレクトを示します。

OAuthを使用するSnowflakeまたはGoogle BigQuery接続ではPDTはサポートされません。

永続性を追加する

データベースが書き込み可能で、スクラッチスキーマが作成されている場合には（データベースダイアレクトに関する手順は、Lookerダイアレクトのドキュメンテーションページを参照）、派生テーブルをデータベースに保管し、クエリ速度の向上とデータベースへの負荷低減を実現することができます。永続性を使用できるユーザーの多くは、一般に、追加のストレージ容量が必要になる点が問題にならないため、常に永続性を使用します。

永続性を回避する必要がある状況がいくつかあります。PDTの拡張ごとにデータベース内にテーブルの新しいコピーが作成されるため、拡張する派生テーブルには永続性を追加しないでください。また、テンプレートフィルタまたはLiquidパラメーターを使用する派生テーブルには永続性を追加できません。これらの機能では、可能なユーザー入力の数が無限であるため、データベース内の永続的なテーブルの数が過剰になり、管理できなくなる可能性があります。

派生テーブルを永続的なものにする

“この派生テーブルを永続的なものとする”ことを意味するパラメーターはありません。むしろ、永続性はdatagroup_trigger、sql_trigger_valueまたはpersist_forパラメーターの追加により生まれます。

データグループ

キャッシュポリシーについてdatagroupを定義している場合には、datagroup_triggerパラメーターを使用してキャッシュポリシーと同じスケジュールで永続的な派生テーブル（PDT）を再構築できます。これにより、ユーザーがPDTの作成を待つ必要がなくなります。

データグループは、最も柔軟性の高い永続性作成メソッドです。例えば、ETLの再構築時にPDTの再構築をトリガーするようデータグループを定義することも、データベースのETLサイクルとは別にデータグループをトリガーすることも可能です。

LookerでのPDTのキャッシュと再構築の概要は、データグループによるクエリのキャッシングとPDTの再構築のドキュメンテーションページを参照してください。

`sql_trigger_value`

PDTを再構築するスケジュールを指定することもできます。これにより、ユーザーがPDTの作成を待つ必要がなくなります。ユーザーが再構築中のPDTからのデータをリクエストすると、新しいテーブルが完成するまでの間は既存のテーブルのデータが返されます。

sql_trigger_valueパラメーターを使用することで、こうした永続性を実現することができます。

デフォルトでは、Lookerは5分ごとにテーブルの再生成の要否を確認します。ただし、このスケジュールはLookerの管理設定のPDT And Datagroup Maintenance Schedule設定から希望に応じて変更できます。

`persist_for`

また、派生テーブルを削除するまでの格納期間を設定することも可能です。ユーザーが設定した格納期間の終了前にテーブルに対してクエリを実行すると、既存のテーブルからデータが返されます。期間の終了後に次のユーザーがクエリを実行すると、テーブルのデータをリクエストする際にテーブルが再構築されます。したがって、このユーザーは再構築の間待つ必要があります。persist_forパラメーターを使用することで、こうした永続性を実現することができます。

PDTのインデックス化

PDTは実際にデータベースに格納されるため、対応のSQLダイアレクトのindexesパラメーターを使用してインデックスを指定する必要があります。または、Redshiftをお使いの場合には、通常のソートキー（sortkeysを使用）、インターリーブソートキー（indexesを使用）、ディストリビューションキー（distributionを使用）を指定できます。

上述の派生テーブルの例に永続性を追加するためには、order_datagroupデータグループがトリガーした際にテーブルが再構築されるよう設定し、customer_idとfirst_order_dateの両方に次のようにインデックスを追加できます。

view: customer_order_facts { derived_table: { explore_source: orders { … } datagroup_trigger: orders_datagroup indexes: ["customer_id", "first_order_date"] } }

インデックス（またはRedshiftの同等の機能）を追加しない場合には、クエリのパフォーマンス改善のため追加するようLookerからの警告が表示されます。

PDTの生成

永続性を使用する場合には、PDTの生成方法に影響を及ぼす、基礎となるクエリの所要時間とトリガーされる時に留意してください。以下の点に留意してください。

ユーザーがクエリするPDTが、まだビルドされていないPDTである場合、またはpersist_for永続性を使用するPDTであるがそのPDTのビルドが期限切れの場合には、クエリの実行前に新しいPDTビルドが完了している必要があります。
同一のdatagroupによりトリガーされるすべてのPDTは、同じ再生成プロセスで再ビルドされます。
接続設定の[Max PDT Builder Connections]フィールドで設定した値に応じて、接続に対して同時に1つ以上のPDTをビルドできます。例外については、次の箇条書き項目を参照してください。
persist_forパラメーターにより保持されるPDT、開発モードのPDT、および[Rebuild Derived Tables & Run]オプションを使用して再ビルドされるPDTは、並列的ではなく連続的にビルドされます。
PDTが2つ（PDT_1およびPDT_2とします）あり、PDT_2がPDT_1に依存するように設定した場合には、PDT_1テーブルの生成が完了しない限りLookerがPDT_2の生成を開始することはできません。PDT間の依存関係の設定については本ページの他の派生テーブルにおける派生テーブルの参照セクションで後述しています。

ここでのポイントは、実行時間の長いPDTにより、他のPDTのビルドが遅延する可能性があるという点です。大規模なPDTの生成はデータベースへの負荷が大きいため、ユーザーの他のクエリの処理速度が低下する場合もあります。

開発モードにおける永続性

派生テーブルに永続性を追加すると、開発モードでいくつかの特定の動作を示しますが、それについて知っておく必要があります。

開発モードでの派生テーブルの動作

開発モードでテーブルの定義に変更を加えることなくPDTに対してクエリを実行すると、LookerはそのPDTのプロダクションバージョンに対してクエリを実行します。ただし、PDTの定義になんらかの変更を加えてクエリを実行すると、PDTの新しい開発バージョンが即座に作成されます。これにより、エンドユーザーに影響を及ぼすことなく変更内容をテストできます。

PDTの開発バージョンが作成されると、使用した永続性メソッドに関係なく、そのバージョンは最大24時間保持されます。これにより、開発モードテーブルが頻繁にクリーンアップされ、データベースの乱雑化を防ぎます。

プロダクションに変更内容をプッシュすると、Lookerは開発テーブルをプロダクションテーブルとして即座に認識し始めます（後述の条件付きSQLが使用されていない場合に限り）。これにより、ユーザーがプロダクションテーブルの新しいバージョンの作成を待つ必要がなくなります。

テーブルの共有とクリーンアップ

Lookerインスタンス内でPDTの定義と永続性メソッド設定が同一である場合、ユーザー間でPDTを共有します。さらに、PDTの定義が存在しなくなった場合には、Lookerはそのテーブルを削除します。

これにはいくつかの利点があります。

開発モードでPDTに対して変更を加えていない場合には、既存のプロダクションテーブルがクエリで使用されます。
開発者2人が開発モードでPDTに同一の変更を加えた場合には、これらの開発者の間で同じ開発テーブルが共有されます。
開発モードからプロダクションモードへ変更内容をプッシュすると、古いプロダクションの定義は存在しなくなるため、古いプロダクションテーブルは削除されます。
開発モードの変更を廃棄することを決定した場合には、そのPDTの定義は存在しなくなるため、不要な開発テーブルは削除されます。

開発モードでの作業の効率化

最後に、作成しているPDTの生成に時間がかかる場合もあります。開発モードでの多数の変更内容をテストする際、結果までの時間が長引くのは苛立たしいものです。SQLベースの派生テーブルに関しては、Lookerは、この問題の管理に役立つ開発モードでの条件付きWHERE句に対応しています。

view: my_view { derived_table: { sql: SELECT columns FROM my_table WHERE -- if prod -- date > '2000-01-01' -- if dev -- date > '2015-01-01' ;; } }

この例では、プロダクション中のクエリには2000年以降のすべてのデータが含まれますが、開発モードのクエリには2015年以降のデータのみが含まれます。この機能を戦略的に使用して結果セットを制限し、クエリ速度を向上させることで、開発モードの変更の検証が大幅にしやすくなります。

この機能を使用する場合には、プロダクションモードと開発モードの間ではテーブルが共有されないことに留意してください。結果としてデータベースでのストレージ使用量が増加するため、プロダクションへの変更のコミット後にプロダクションモードのテーブルの再構築が必要となります。

ネイティブ派生テーブルは条件付きWHERE句には対応していませんが、NDTでのフィルタの作成やNDTの行の制限によりNDTのテストと開発を支援することができます。

永続性を上書き可能なユーザー権限

develop権限を持つユーザーは、永続性設定を上書きし、PDTを再構築して最新のデータを取得できます。テーブルを再構築するには、Exploreのメニューの[Rebuild Derived Tables & Run]オプションを使用します。

このオプションを使用するには、Exploreクエリの読み込みを待つ必要があります。このオプションにより、クエリで参照されているすべての永続的なテーブル（すべてのPDTおよび集計テーブル）が再構築されます。また、これらの集計テーブルとPDTに依存するすべての永続的なテーブルも再構築されます。

[Rebuild Derived Tables & Run]オプションを開始したユーザーの場合、テーブルが再構築されてからクエリの結果が読み込まれます。他のユーザーのクエリには、引き続き既存のテーブルが使用されます。永続的なテーブルが再構築された後、すべてのユーザーは再構築されたテーブルを使用します。

このプロセスは、テーブルの再構築中に他のユーザーのクエリを中断しないように設計されていますが、データベースへの負荷増大という形で他のユーザーに影響が及ぶ可能性はあります。業務時間内に再構築をトリガーするとデータベースに許容できない負荷が生じうる状況においては、その時間に特定のPDTや集計テーブルを再構築しないようユーザーへの周知が必要となる場合もあります。

他の派生テーブルにおける派生テーブルの参照

ある派生テーブルを別の定義で参照して、派生テーブルの”使用”（カスケード）と呼ばれるものを作成することができます。これらを使用するには、構文${derived_table_or_view_name.SQL_TABLE_NAME}を使用する必要があります。例えば、${clean_events.SQL_TABLE_NAME}と入力してclean_events派生テーブルを参照できます。${derived_table_or_view_name.SQL_TABLE_NAME}構文を使用してLookMLビューを参照することもできます。

他の派生テーブルやビュー名を参照する際は、リテラルストリングとしてSQL_TABLE_NAMEと入力します。

データグループはPDTの再構築をトリガーするための推奨される方法ですが、${derived_table_or_view_name.SQL_TABLE_NAME}構文はデータグループのsql_triggerパラメーターではサポートされていません。ただし、sql_trigger_valueパラメーターを使用してPDTの再構築をトリガーする場合は、${derived_table_or_view_name.SQL_TABLE_NAME}構文をsql_trigger_valueと共に使用できます。

必要でない場合もありますが、この形式で派生テーブルを参照する場合には、テーブルにエイリアスを付与することがしばしば有用です。次に例を示します。

${derived_table_or_view_name.SQL_TABLE_NAME} AS derived_table_or_view_name

これは、永続的な派生テーブルにはデータベース内で長いコードの名前が付けられているためです。場合によっては（特にON句を使用する場合）、この長い名前を取得するために${derived_table_or_view_name.SQL_TABLE_NAME}構文を使用する必要があることを忘れがちになります。エイリアスを付与することで、こうした誤りを避けることができます。

この例では、不要な行を一掃するeventsから派生テーブルを作成し、その派生テーブルの要約を作成しています。event_summaryテーブルは、新しい行がclean_eventsに追加されるたびに再生成されます。

view: clean_events { derived_table: { sql: SELECT * FROM events WHERE type NOT IN ('test', 'staff') ;; sql_trigger_value: SELECT CURDATE() ;; } } view: events_summary { derived_table: { sql: SELECT type, date, COUNT(*) AS num_events FROM ${clean_events.SQL_TABLE_NAME} AS clean_events GROUP BY type, date ;; sql_trigger_value: SELECT MAX(id) FROM ${clean_events.SQL_TABLE_NAME} ;; } }

この特定の例は、1つの派生テーブルでより効率的に行うことができますが、派生テーブルの参照を示すのに役立ちます。

永続的な派生テーブルの監視とトラブルシューティング

多くのPDTを使用する企業、特にカスケードを行う企業では、多くの場合、PDTのステータスを把握する必要があります。Lookerは、このようなテーブルを調査するためのツールをいくつか用意しています。これらのツールについては、管理者設定 - 永続的な派生テーブルドキュメンテーションページで説明されています。

永続的な派生テーブルのトラブルシューティングの際の留意点は次のとおりです。

派生テーブルログの調査時には、開発テーブルとプロダクションテーブルの対比に特に注意を払う。
Lookerが永続的な派生テーブルを格納するスクラッチスキーマに変更が加えられていないことを確認する。変更が加えられている場合には、Lookerの[Admin]セクションの[接続]設定の更新とLookerの再起動による通常のPDT機能の復旧が必要となる可能性があります。
すべてのPDTに関して問題があるのか、または1つのみに関して問題があるのかを判断する。1つのみに問題がある場合には、LookMLまたはSQLのエラーが問題の原因である可能性が高くなります。
PDTに関する問題が再構築のスケジュールされている時間に発生するかどうかを判断する。
すべてのsql_trigger_valueクエリの評価が正しく行われており、1行および1列のみを返していることを確認する。SQLベースのPDTの場合には、SQL Runnerでクエリを実行して確認できます（LIMITによるランナウェイクエリからの保護を適用します）。SQL Runnerを使用した派生テーブルのデバッグについての詳細は、こちらのコミュニティのトピックを参照してください。
SQLベースのPDTの場合には、SQL Runnerを使用して対象のPDTのSQLがエラーなく実行することを検証する（適切なクエリ時間を保つため、SQL Runnerでは必ずLIMITを適用してください）。
SQLベースのDTおよびPDTの場合は、共通テーブル式（CTE）の使用は避ける。DTでCTEを使用すると、ネストされたWITHステートメントが作成され、警告なしでPDTが失敗する可能性があります。代わりに、CTEのSQLを使用してセカンダリDTを作成し、${derived_table_or_view_name.SQL_TABLE_NAME}構文を使用して最初のDTからそのDTを参照してください。
問題のPDTの依存対象となるテーブル—通常のテーブルであってもPDT自体であっても—が存在し、それに対してクエリを実行できることを確認する。
問題のPDTの依存対象となるテーブルに、共有ロックや排他ロックがないことを確認する。LookerがPDTを正常にビルドするには、更新対象のテーブルの排他ロックを獲得する必要があります。これは、テーブルに現在適用されている共有ロックまたは排他ロックと競合します。他のすべてのロックがクリアされるまで、LookerはPDTを更新できません。このことは、LookerがPDTをビルドする際のベースとなるテーブルのどの排他ロックにも該当します。テーブルに排他ロックがある場合、排他ロックがクリアされるまでは、Lookerはクエリ実行のために共有ロックを獲得することができません。
SQL Runnerの[Show Processes]ボタンを使用する。多数のプロセスがアクティブとなっている場合には、クエリに要する時間が長くなる可能性があります。
対象のクエリ内のコメントを監視する。データベース管理者は、通常のクエリとPDTを生成するクエリを簡単に見分けることができます。Lookerは、対象のPDTのLookMLモデルおよびビュー、さらにLookerインスタンスの一意の識別子（スラッグ）を含むCREATE TABLE ... AS SELECT ...ステートメントにコメントを追加します。対象のPDTがユーザーに代わり開発モードで生成されている場合には、コメントにはそのユーザーのIDが表示されます。このコメントは、次の開発モードで生成されるPDTの例のように、データベースコンソールから確認できます。

Exploreのクエリに代わりLookerがPDTを生成した場合には、PDT生成コメントがExploreのSQLタブにも表示されます。コメントは次の例のようにSQL文の最上部に表示されます。

最後に、PDT生成コメントが[クエリ]管理ページの詳細に次の例のように表示されます。

LookMLでの結合の使用

ネイティブ派生テーブルの作成