Lookerでは、派生テーブルとは、クエリ結果をデータベース内の実際のテーブルのように使用できるクエリを指します。
例えば、多数の列のあるorders
という名前のデータベーステーブルがあるとします。それぞれのお客様が行った注文の数や、それぞれのお客様が最初の注文を行った時刻など、お客様レベルの集計メトリクスを計算する必要があります。ネイティブ派生テーブルか SQLベースの派生テーブルのいずれかを使用して、これらのメトリクスを含むcustomer_order_summary
という名前の新しいデータベーステーブルを作成できます。
その後、customer_order_summary
派生テーブルをデータベース内の他のテーブルと同様に扱えるようになります。
ネイティブ派生テーブルとSQLベースの派生テーブル
Lookerプロジェクトに派生テーブルを作成する場合、viewパラメーターの下でderived_table
パラメーターを使用します。derived_table
パラメーター内には、派生テーブルのクエリを次の2つの方法のいずれかで定義できます。
- ネイティブ派生テーブルの場合は、LookMLベースのクエリで派生テーブルを定義します。
- SQLベースの派生テーブルの場合は、SQLクエリで派生テーブルを定義します。
例えば、以下のビューファイルは、LookMLを使用してcustomer_order_summary
派生ファイルからビューを作成する方法を示しています。2つのバージョンのLookMLは、LookMLとSQLのどちらを使用して派生テーブルのクエリを定義しても、同等の派生テーブルを作成できることを示しています。
どちらのバージョンも、列customer_id
、first_order,
、およびtotal_amount
を含むorders
テーブルに基づくcustomer_order_summary
と呼ばれるビューを作成します。
derived_table
パラメーターとそのサブパラメーターを除けば、このcustomer_order_summary
ビューは他のビューファイルと同様に動作します。派生テーブルのクエリをLookMLまたはSQLのどちらで定義しても、派生テーブルの列に基づくLookMLのMeasureやDimensionを作成できます。
派生テーブルの定義が完了したら、データベース内の他のテーブルと同様に使用することができます。
ネイティブ派生テーブル
ネイティブ派生テーブルは、LookMLの用語を使用して定義するクエリに基づいています。ネイティブ派生テーブルを作成する場合、viewパラメーターのderived_table
パラメーター内部でexplore_source
パラメーターを使用します。ネイティブ派生テーブルの列は、モデル内のLookML DimensionまたはMeasureを参照して作成します。ネイティブ派生テーブルのビューファイルについては、前の例を参照してください。
SQLベースの派生テーブルと比較すると、ネイティブ派生テーブルでは、データをモデル化するときの読みやすさと理解しやすさが向上します。
ネイティブ派生テーブルの作成の詳細については、ネイティブ派生テーブルの作成のドキュメンテーションページを参照してください。
SQLベースの派生テーブル
SQLベースの派生テーブルを作成するには、SQL用語でクエリを定義し、SQLクエリを使用してテーブルに列を作成します。SQLベースの派生テーブルでLookMLのDimensionとMeasureを参照先とすることはできません。SQLベースの派生テーブルのビューファイルについては、前の例を参照してください。
一般に、SQLクエリはviewパラメーターのderived_table
パラメーター内部でsql
パラメーターを使用して定義します。
LookerでのSQLベースのクエリ作成に役立つショートカットは、SQL Runnerを使用してSQLクエリを作成し、派生テーブルの定義に変えることができます。
特定のエッジケースでは、sql
パラメーターの使用が許可されない場合があります。そのような場合、Lookerでは永続的な派生テーブル(PDT)のSQLクエリの定義のために以下のパラメーターをサポートしています。
create_process
:PDTにsql
パラメーターを使用する場合、バックグラウンドでLookerがダイアレクトのCREATE TABLE
データ定義言語(DDL)文でクエリを囲み、SQLクエリからPDTを作成します。一部のダイアレクトは、単独のステップでのSQLCREATE TABLE
文に対応していません。このようなダイアレクトには、sql
パラメーターでPDTを作成することはできません。代わりに、create_process
パラメーターを使用すると、複数のステップでPDTを作成できます。この情報と例については、create_process
パラメーターのドキュメンテーションページを参照してください。sql_create
:ユースケースでカスタムのDDLコマンドが必要でありダイアレクトがDDL(Googleの予測BigQuery MLなど)をサポートしている場合、sql
パラメーターではなくsql_create
パラメーターを使用してPDTを作成できます。この情報と例については、sql_create
のドキュメンテーションページを参照してください。
sql
、create_process
、sql_create
のどのパラメーターを使用している場合であっても、派生テーブルの定義にはSQLクエリが使用されるため、これらはすべてSQLベースの派生テーブルと見なされます。
SQLベースの派生テーブルを定義する場合は、AS
を使用して、各列に明確な別名を付ける必要があります。これは、Dimensionで結果セットの列名(${TABLE}.first_order
など)の参照が必要になるためです。前の例で単純なMIN(DATE(time))
ではなく、MIN(DATE(time)) AS first_order
を使用しているのはそのためです。
増分PDTとして使用する場合、SQLベースの派生テーブルは
sql
パラメーターを使用して定義しなければなりません。sql_create
パラメーターまたはcreate_process
パラメーターを指定して定義されているSQLベースの派生テーブルについては、増分構築できません。
一時的な派生テーブルと永続的な派生テーブル
ネイティブ派生テーブルとSQLベースの派生テーブルの対比だけではなく、一時的な派生テーブル(データベースに書き込まれない)と永続的な派生テーブル(データベースのスクラッチスキーマに書き込まれる)の違いもあります。
ネイティブ派生テーブルとSQLベースの派生テーブルは、一時的、または永続的のどちらにもできます。
一時的な派生テーブル
前述の派生テーブルは、一時的な派生テーブルの例です。これは、永続性戦略がderived_table
パラメーターに定義されていないため一時的なものです。
一時的な派生テーブルは、データベースに書き込まれません。1つまたは複数の派生テーブルに関与するExploreクエリをユーザーが実行すると、Lookerは、それらの派生テーブルの、ダイアレクト固有のSQLの組み合わせと、リクエストされたフィールド、結合、フィルタ値を使用してSQLクエリを構築します。その組み合わせが以前に実行されたものであり、キャッシュ内の結果がまだ有効な場合には、Lookerはキャッシュされた結果を使用します。Lookerでのクエリのキャッシュの詳細については、データグループによるクエリのキャッシングとPDTの再構築のドキュメンテーションページを参照してください。
一方、キャッシュされた結果を使用できない場合、Lookerはユーザーによる一時的な派生テーブルのデータのリクエストごとに、ユーザーのデータベースで新しいクエリを実行することが必要になります。このため、一時的な派生テーブルが高性能であり、データベース上で負荷を増大させるものではないことを確認する必要があります。クエリの実行に時間を要する場合には、一般に永続的な派生テーブルが適しています。
一時的な派生テーブルに対応するデータベースダイアレクト
Lookerプロジェクトで派生テーブルに対応するためには、データベースダイアレクトでも派生テーブルに対応している必要があります。次の表は、Looker22.14の派生テーブルに対応しているダイアレクトを示しています。
永続的な派生テーブル(PDT)
永続的な派生テーブル(PDT)は、データベースのスクラッチスキーマに書き込まれ、永続性戦略で指定したスケジュールで再生成された派生テーブルです。
PDTはネイティブ派生テーブルまたはSQLベースの派生テーブルのどちらでもかまいません。
PDTは、ユーザーがテーブルからのデータをリクエストする際にはテーブルがすでに作成されているので、クエリ時間とデータベースへの負荷の低減に役立ちます。
LookerプロジェクトでPDTを使用するには、以下のものが必要です。
- PDTに対応するデータベースダイアレクト。このページの後半のPDTに対応するデータベースダイアレクトのセクションにある、永続的なSQLベースの派生テーブルおよび永続的なネイティブ派生テーブルに対応するダイアレクトのリストを参照してください。
- データベースのスクラッチスキーマ。これは、データベース上のどのスキーマでもかまいませんが、この目的専用の新しいスキーマを作成することをお勧めします。データベース管理者は、Lookerデータベースユーザーの書き込みを許可するスキーマを構成する必要があります。
- 永続的な派生テーブルオプションを有効にして構成されたLooker接続。通常、この設定はLookerの初回構成の際に行いますが(データベースダイアレクトに関する手順はLookerダイアレクトのドキュメンテーションページを参照)、初回構成後に接続に対してPDTを有効にすることもできます。
PDTに対応するデータベースダイアレクト
LookerプロジェクトでPDTに対応するためには、データベースダイアレクトでもPDTに対応している必要があります。
永続的な派生テーブルの種類(LookMLベースまたはSQLベース)にかかわらず、ダイアレクトは他の要件に加えて、データベースへの書き込みに対応している必要があります。読み取り専用データベース構成の中には、永続性の動作を許可しないものもあります(Postgresホットスワップレプリカデータベースによく見られます)。このような場合には、代わりに一時的な派生テーブルを使用できます。
次の表は、Looker22.14の永続的なSQLベースの派生テーブルに対応しているダイアレクトを示しています。
永続的なネイティブ派生テーブル(LookMLベースのクエリを含む)に対応するには、ダイアレクトがCREATE TABLE
DDL関数にも対応している必要があります。ここでは、Looker22.14の永続的なネイティブ(LookMLベースの)派生テーブルに対応しているダイアレクトのリストを示します。
OAuthを使用するSnowflakeまたはGoogle BigQuery接続ではPDTはサポートされません。
PDTの増分構築
ご使用のダイアレクトでサポートされているなら、プロジェクトに増分PDTを作成できます。増分PDTとは、テーブル全体を再構築するのではなく、新しいデータをテーブルに追加してビルドする、永続的な派生テーブル(PDT)です。詳細については、増分PDTのドキュメンテーションページを参照してください。
増分PDT対応のデータベースダイアレクト
Lookerプロジェクトで増分PDTに対応するためには、データベースダイアレクトでも増分PDTに対応している必要があります。次の表は、Looker 22.14において増分PDTに対応しているダイアレクトを示しています。
永続的な派生テーブルの作成
派生テーブルを永続的な派生テーブルにするには、そのテーブルに永続性戦略を定義します。パフォーマンスを最適化するには、最適化戦略も追加する必要があります。
永続性戦略
派生テーブルの永続性は、Lookerで管理できます。マテリアル化ビューをサポートするダイアレクトの場合は、マテリアル化ビューを使用してデータベースで管理できます。
派生テーブルを永続的なものにするには、以下のいずれかのパラメーターをderived_table
定義に追加します。
- Looker管理の永続性パラメーター:
- データベース管理の永続性パラメーター:
トリガーベースの永続性戦略(datagroup_trigger
、sql_trigger
、およびinterval_trigger
)では、PDTの再構築がトリガーされるまで、LookerはそのPDTをデータベースに保持します。PDTがトリガーされると、LookerはPDTを再構築し、前のバージョンと置き換えます。つまり、トリガーベースのPDTでは、ユーザーがPDTからのExploreクエリの回答を得るために、PDTの構築を待つ必要はありません。
特定のユーザーは、PDTの永続性設定を上書きすることができます。
develop
権限を持つユーザーは、Exploreのメニューから[Rebuild Derived Tables & Run]オプションを使用して永続性設定を上書きし、Exploreの現在のクエリに必要なすべてのPDTを再構築することができます。詳細については、このページの手動によるクエリ用のPDTの再構築に関するセクションを参照してください。
datagroup_trigger
データグループは、最も柔軟性の高い永続性作成メソッドです。キャッシュポリシーについてdatagroup
を定義している場合には、datagroup_trigger
パラメーターを使用してキャッシュポリシーと同じスケジュールでPDTの再構築を開始することができます。
Lookerでは、PDTのデータグループがトリガーされるまで、そのPDTをデータベースに保持します。データグループがトリガーされると、LookerはPDTを再構築し、前のバージョンと置き換えます。ほとんどの場合、これにより、ユーザーがPDTの構築を待つ必要がなくなります。PDTが構築中であり、クエリ結果がキャッシュされていない場合にユーザーがPDTからデータをリクエストすると、Lookerは新しいPDTがビルドされるまで、既存のPDTからデータを返します。データグループの概要については、データグループによるクエリのキャッシングとPDTの再構築のドキュメンテーションページを参照してください。
リジェネレーターのPDTの構築方法については、Lookerリジェネレーターのセクションを参照してください。
sql_trigger_value
sql_trigger_value
パラメーターは、指定したSQL文に基づいてPDTの再生成をトリガーします。SQL文の結果が前の値と異なる場合、PDTが再生成されます。それ以外の場合は、既存のPDTがデータベースに保持されます。ほとんどの場合、これにより、ユーザーがPDTの構築を待つ必要がなくなります。PDTの構築中で、クエリ結果がキャッシュされていない場合にユーザーがPDTのデータをリクエストすると、Lookerは新しいPDTが構築されるまで既存のPDTからのデータを返します。
リジェネレーターのPDTの構築方法については、Lookerリジェネレーターのセクションを参照してください。
interval_trigger
interval_trigger
パラメーターは、"24 hours"
や"60 minutes"
など、指定された時間間隔に基づいてPDTの再生成をトリガーします。sql_trigger
パラメーターと同様、ユーザーがクエリを実行するときには、通常PDTが事前構築されています。PDTの構築中にユーザーがPDTからデータをリクエストしたために、クエリ結果がキャッシュされない場合、Lookerは新しいPDTが構築されるまで既存のPDTからのデータを返します。
persist_for
別のオプションとしては、persist_for
パラメーターを使用して、派生テーブルが期限切れとマークされ、クエリに使用されなくなりデータベースから削除されるまでの保存期間を設定するというものがありますです。
persist_for
PDTは、ユーザーがPDTのクエリを最初に実行したときにビルドされます。その後、Lookerは、PDTのpersist_for
パラメーターで指定した期間まで、このPDTをデータベースに保持します。ユーザーがpersist_for
期間中にPDTのクエリを実行すると、Lookerは可能であればキャッシュされている結果を使用し、それ以外の場合はそのPDTでクエリを実行します。
persist_for
期間を超過すると、LookerはデータベースからPDTを消去し、そのPDTは次回、ユーザーがクエリしたときに再構築されます。したがって、そのクエリでは再構築を待つ必要があります。
persist_for
を使用するPDTは、PDTの依存カスケード依存である場合を除き、Lookerのリジェネレータで自動的に再構築されません。persist_for
テーブルがトリガーベースのPDT(datagroup_trigger
、interval_trigger
、またはsql_trigger_value
永続性戦略を使用するPDT)を使用する依存カスケードの一部である場合、リジェネレータは、persist_for
テーブルを監視し、再構築して、カスケード内の他のテーブルを再構築します。このページのLookerによるカスケード派生テーブルのビルド方法のセクションを参照してください。
materialized_view: yes
マテリアル化ビューは高度な機能です。ダイアレクトによっては、マテリアル化ビューが膨大なリソースを消費することがあるため、ダイアレクトへのマテリアル化ビューの導入がどういったものか十分理解しておくことが重要です。ダイアレクトの動作や、マテリアル化ビューでのダイアレクトのデータ更新頻度の情報については、ダイアレクトのドキュメンテーションを参照してください。
マテリアル化ビューでは、データベースの機能を利用して、Lookerプロジェクトで派生テーブルを永続化させることができます。データベースダイアレクトがマテリアル化ビューをサポートし、Looker接続設定の永続的な派生テーブルオプションが有効になっている場合、派生テーブルにmaterialized_view: yes
を指定するとマテリアル化ビューを作成できます。マテリアル化ビューは、ネイティブ派生テーブルとSQLベースの派生テーブルの両方でサポートされています。
永続的な派生テーブル(PDT)と同様、マテリアル化ビューは、データベースのスクラッチスキーマでテーブルとして保存されるクエリの結果です。PDTとマテリアル化ビューの主な違いは、テーブルの更新方法です。
- PDTでは、永続性戦略はLookerで定義され、その永続性はLookerで管理されます。
- マテリアル化ビューでは、データベースがテーブル内のデータの管理と更新を担当します。
このため、マテリアル化ビューの機能を使用するには、ダイアレクトとその機能に関する高度な知識が必要になります。ほとんどの場合、データベースでは、マテリアル化ビューでクエリされるテーブル内に新規データが検出されるたびに、マテリアル化ビューが更新されます。マテリアル化ビューは、リアルタイムデータが必要なシナリオに最適です。
ダイアレクトのサポート、要件、重要な考慮事項については、materialized_view
パラメーターのドキュメンテーションページを参照してください。
最適化戦略
PDTはデータベースに保存されるため、ダイアレクトでサポートされているとおり、以下の戦略を使用してPDTを最適化する必要があります。
例えば、派生テーブルの例に永続性を追加するには、order_datagroup
データグループがトリガーした際にテーブルが再構築されるよう設定し、customer_id
とfirst_order
の両方に次のようにインデックスを追加します。
インデックス(またはご使用のダイアレクトでそれに相当するもの)を追加しない場合、クエリのパフォーマンス改善のため追加するようLookerから警告が表示されます。
カスケード派生テーブル
場合によっては、ある派生テーブルを別の定義で参照して、連鎖するカスケード派生テーブルまたはカスケードPDTを作成することができます。カスケード派生テーブルの例は、テーブルTABLE_D
です。このテーブルは別のテーブルTABLE_C
に依存し、TABLE_C
はTABLE_B
に依存し、TABLE_B
はTABLE_A
に依存します。
カスケード派生テーブルを設定する前に、カスケードテーブルがLookerのリジェネレーターによってどのように自動的に再構築されるか、およびユーザーによってどのように手動で再構築されるかを理解していることが重要です。また、他にも永続テーブルの導入に関する重要な考慮事項があります。これらのトピックはすべて、このページの後半で扱います。
派生テーブル参照の構文
他の派生テーブル内の派生テーブルを参照するには、次の構文を使用します。
この書式設定では、SQL_TABLE_NAME
はリテラル文字列です。例えば、次の構文でclean_events
派生テーブルを参照できます。
この同じ構文を使用してLookMLビューを参照することもできます。この場合も、SQL_TABLE_NAME
はリテラル文字列です。
${derived_table_or_view_name.SQL_TABLE_NAME}
構文はデータグループのsql_trigger
パラメーターではサポートされていないため、派生テーブルを使用してデータグループをトリガーすることはできません。ただし、sql_trigger_value
パラメーターで${derived_table_or_view_name.SQL_TABLE_NAME}
構文を使用し、PDTの再構築をトリガーすることができます。
次の例では、clean_events
PDTがデータベースのevents
テーブルから作成されます。clean_events
PDTはevents
データベーステーブルから不要な行を除外します。その後、clean_events
PDTをまとめた2番目のPDTである、event_summary
PDTが表示されます。event_summary
テーブルは、新しい行がclean_events
に追加されるたびに再生成されます。
event_summary
PDTおよびclean_events
PDTはカスケードPDTであり、(event_summary
はclean_events
PDTを使用して定義されているため)event_summary
がclean_events
に依存します。(この特定の例は、1つのPDTでより効率的に行うことができますが、派生テーブルの参照を示すのに役立ちます。)
必ず必要なわけではありませんが、この方法で派生テーブルを参照する場合は、一般に次の形式でテーブルのエイリアスを作成すると有用です。
前の例では次のように行います:
ここでは、PDTにはデータベース内で長いコードの名前が付けられているため、エイリアスの使用が役立ちます。場合によっては(特にON
句を使用する場合)、この長い名前を取得するために${derived_table_or_view_name.SQL_TABLE_NAME}
構文を使用する必要があることを忘れがちになります。エイリアスを付与することで、こうした誤りを避けることができます。
Lookerによるカスケード派生テーブルのビルド方法
一時的な派生テーブルをカスケードする場合にユーザーのクエリ結果がキャッシュになければ、Lookerはそのクエリに必要なすべての派生テーブルをビルドします。TABLE_D
があり、TABLE_C
への参照がその定義に含まれている場合、TABLE_D
はTABLE_C
に依存しています。つまり、TABLE_D
をクエリし、そのクエリがLookerのキャッシュにない場合、LookerはTABLE_D
を再構築します。しかし、最初はTABLE_C
を再構築する必要があります。
一時的な派生テーブルのカスケードのシナリオを見ていきましょう。このテーブルでは、TABLE_A
に依存するTABLE_B
に依存するTABLE_C
に、TABLE_D
が依存しています。LookerのキャッシュにTABLE_C
に対する有効なクエリ結果がない場合、Lookerはそのクエリに必要なすべてのテーブルをビルドします。そのため、LookerはTABLE_A
、TABLE_B
、TABLE_C
の順番で構築することになります。
このシナリオでは、TABLE_A
の生成は、LookerがTABLE_B
の生成を開始するまでに完了する、という順番でTABLE_C
までテーブルの生成を完了させると、Lookerからクエリ結果が返されます。(TABLE_D
はこのクエリに応える必要がないため、Lookerはこの時点ではTABLE_D
を再構築しません。)
PDTにも同じ基本のロジックが当てはまります。Lookerは、依存関係チェーンを遡って、クエリの回答に必要なすべてのテーブルをビルドします。ただし、PDTでは、テーブルがすでに存在し、再構築する必要がない場合もあります。カスケードPDTに関する標準のユーザークエリでは、Lookerはデータベース内にそのPDTの有効なバージョンがない場合のみ、カスケード内のPDTを再構築します。カスケード内のすべてのPDTを強制的に再構築する場合は、Exploreから手動によりクエリ用テーブルを再構築することができます。
重要な論理ポイントは、PDTカスケードの場合、基本的に依存PDTがその依存先であるPDTをクエリしていることです。これは特に、persist_for
戦略を使用しているPDTには重要です。通常、persist_for
PDTはユーザーがクエリを実行したときに構築され、persist_for
期間が終了するまでデータベースに残され、その後はユーザーが次にクエリするまで再構築されません。ただし、persist_for
PDTがトリガーベースのPDT(datagroup_trigger
、interval_trigger
、またはsql_trigger_value
を使用するPDT)を使用するカスケードの一部である場合、基本的にpersist_for
PDTは、依存PDTが再構築されるごとにクエリされます。そのため、この場合、persist_for
PDTはその依存PDTのスケジュールに従って再構築されます。つまり、persist_for
PDTは、それに依存する永続性戦略の影響を受けるのです。
手動によるクエリ用の永続的なテーブルの再構築
Exploreのメニューから[派生テーブルを再作成して実行する]オプションを選択すると、永続性設定を上書きし、Exploreの現在のクエリに必要なすべてのPDTと集計テーブルを再構築することができます。
このオプションは、ユーザーにdevelop
権限があり、Exploreクエリが読み込まれた場合のみ表示されます。
[Rebuild Derived Tables & Run]オプションにより、永続性戦略に関係なく、クエリに回答するために必要なすべての永続的なテーブル(すべてのPDTおよび集計テーブル)が再構築されます。これには現在のクエリのすべての集計テーブルとPDT、および現在のクエリの集計テーブルとPDTによって参照されるすべての集計テーブルとPDTも含まれます。
増分PDTの場合、[Rebuild Derived Tables & Run]オプションは新しい増分のビルドをトリガーします。増分PDTでは、increment_key
パラメーターで指定した期間、およびincrement_offset
パラメーターで指定した数の以前の期間(もしあれば)が増分に含まれます。設定に応じた増分PDTのビルド方法のシナリオ例については、増分PDTのドキュメンテーションページを参照してください。
カスケードPDTの場合は、カスケードの中のすべての派生テーブルが上から順に再ビルドされることを意味します。これは、一時的な派生テーブルのカスケードでテーブルをクエリする場合と同じ動作です。
ユーザーが[派生テーブルを再作成して実行する]操作を開始した場合、テーブルが再構築されてからクエリの結果が読み込まれます。他のユーザーのクエリには、引き続き既存のテーブルが使用されます。永続的なテーブルが再構築された後、すべてのユーザーは再構築されたテーブルを使用します。
このプロセスは、テーブルの再構築中に他のユーザーのクエリを中断しないように設計されていますが、データベースへの負荷増大という形で他のユーザーに影響が及ぶ可能性はあります。業務時間内に再構築をトリガーするとデータベースに許容できない負荷が生じうる状況においては、その時間に特定のPDTや集計テーブルを再構築しないようユーザーへの周知が必要となる場合もあります。
開発モードの永続テーブル
Lookerには、開発モードで永続テーブルを管理するための特殊な動作があります。
このような動作は、集計テーブルとPDTを含むあらゆる永続テーブルに該当します。
開発モードで永続テーブルの定義に変更を加えることなくテーブルに対してクエリを実行すると、Lookerはそのテーブルのプロダクションバージョンに対してクエリを実行します。ただし、テーブルのデータまたはテーブルのクエリ方法に影響を及ぼすような変更をテーブルの定義に加えると、次に開発モードでテーブルをクエリしたときにそのテーブルの新しい開発バージョンが作成されます。このような開発テーブルがあれば、エンドユーザーに影響を及ぼすことなく変更内容をテストできます。
Lookerで開発テーブルが作成されるきっかけ
可能な場合、Lookerは、開発モードかどうかに関係なく、既存のプロダクションテーブルを使用してクエリに答えます。ただし、Lookerは開発モードでクエリにプロダクションテーブルを使用できない次のような場合もあります:
- 永続テーブルに、データセットを絞り込んで開発モードでより速く動作するパラメーターが含まれている場合
- テーブル内のデータに影響を与える変更を永続テーブルの定義に加えた場合
開発モードで、if prod
ステートメントとif dev
ステートメントで条件付きWHERE
句を使用して定義されたSQLベースの派生テーブルをクエリすると、Lookerは開発テーブルを構築します(これは、dev_filters
パラメーターを含むネイティブ派生テーブルのケースではありません。dev_filters
を含むネイティブ派生テーブルの場合は、テーブルの定義を変更してから、開発モードでテーブルをクエリしない限り、Lookerはプロダクションテーブルを使用して開発モードでクエリに答えます)。
開発モードでデータセットを絞り込むパラメーターが含まれていない永続テーブルの場合は、テーブルの定義を変更してから、開発モードでテーブルをクエリしない限り、Lookerは、開発モードでテーブルのプロダクションバージョンを使用してクエリに答えます。これによって、テーブル内のデータやテーブルのクエリ方法に影響を及ぼす変更がテーブルに加えられる場合があります。
Lookerで永続テーブルの開発バージョンが作成されるきっかけとなる変更タイプの例を次に示します(Lookerは、これらの変更を加えた後に続けてテーブルがクエリされた場合にのみ、テーブルを作成します)。
- 永続テーブル自体、または必須テーブル(カスケード派生テーブルの場合)にある
explore_source
、sql
、query
、sql_create
、create_process
パラメーターの変更など、永続テーブルがベースとしているクエリの変更 - テーブルの
datagroup_trigger
、sql_trigger_value
、interval_trigger
、またはpersist_for
パラメーターの変更など、テーブルの永続性戦略の変更 - 派生テーブルの
view
の名前の変更 - 増分PDTの
increment_key
またはincrement_offset
の変更 - 関連するモデルで使用される
connection
の変更
テーブルのデータを変更しないあるいはLookerがテーブルをクエリする方法に影響しない変更については、開発テーブルは作成されません。この例として、publish_as_db_view
パラメーターがあります。開発モードでは、派生テーブルのpublish_as_db_view
設定のみを変更しても、Lookerでは派生テーブルを再構築する必要がないため開発テーブルは作成されません。
Lookerが開発テーブルを永続化する期間
テーブルの実際の永続性戦略とは関係なく、Lookerでは開発の永続テーブルを、persist_for: "24 hours"
の永続性戦略があるものとして扱います。Lookerでは、これによって開発テーブルを1日以上永続させないようにしています。これはLooker開発者が開発中、非常に多くのテーブルを反復クエリし、毎回新しい開発テーブルがビルドされることがあるためです。データベースが開発テーブルでいっぱいにならないようにするため、Lookerではpersist_for: "24 hours"
戦略を適用し、データベースからテーブルが頻繁に削除されるようにしています。
そうしない場合、Lookerはプロダクションモードで永続テーブルをビルドする場合と同じ方法で、開発モードでPDTと集計テーブルをビルドします。
PDTまたは集計テーブルに変更をデプロイしたとき、データベース上で開発テーブルが永続化されている場合、Lookerはユーザーがテーブルをクエリするときにテーブルが作成されるのを待つ必要がないようにするために、その開発テーブルをプロダクションテーブルとして扱うことがよくあります。
変更をデプロイする場合、状況によっては、そのテーブルをプロダクションでクエリ対象として引き続き再構築しなければならない場合があります。
- 開発モードでテーブルをクエリしてから24時間経過すると、テーブルの開発バージョンに期限切れとのタグが付けられ、クエリに使用されなくなります。未作成のPDTがあるかどうかは、Looker IDEを使用するかまたは[永続的な派生テーブル]ページの[開発]タブを使用してチェックできます。未作成のPDTがある場合は、変更を加える直前に開発モードで未作成のPDTをクエリすることで、開発テーブルをプロダクションで使用できるようになります。
- 永続テーブルに
dev_filters
パラメーター(ネイティブ派生テーブル用)やif prod
ステートメントおよびif dev
ステートメントを使用している条件付きWHERE
句(SQLベースの派生テーブル用)が含まれている場合、開発バージョンには省略データセットが含まれるため、開発テーブルをプロダクションバージョンとして使用することはできません。この場合、テーブルの開発が終わって変更をデプロイする前に、dev_filters
パラメーターまたは条件付きWHERE
句をコメント化してから開発モードでテーブルをクエリすることができます。こうすることで、Lookerは変更をデプロイした後もプロダクションで使用可能なフルバージョンのテーブルを構築します。
一方、プロダクションテーブルとして使用可能である有効な開発テーブルがないときに変更をデプロイすると、Lookerは次回テーブルがプロダクションモードでクエリされたとき(persist_for
戦略を使用する永続テーブルの場合)、または次回リジェネレータを実行したとき(datagroup_trigger
、interval_trigger
、またはsql_trigger_value
を使用する永続テーブルの場合)にそのテーブルを再構築します。
開発モードでの未作成のPDTの有無の確認
PDTまたは集計テーブルに変更をデプロイしたとき、データベース上で開発テーブルが永続化されている場合、Lookerはユーザーがテーブルをクエリするときにテーブルが作成されるのを待つ必要がないようにするために、その開発テーブルをプロダクションテーブルとして扱うことがよくあります。詳細は、このページのLookerが開発テーブルを永続化する期間およびLookerで開発テーブルが作成されるきっかけを参照してください。
したがって、プロダクションにデプロイする時点ですべてのPDTが作成済みであり、それらのテーブルをプロダクションバージョンとして直ちに使用できるのが最善です。
プロジェクト内で未構築のPDTがないか[プロジェクトの健全性]パネルで確認できます。Looker IDEの[プロジェクトの健全性]アイコンをクリックして、[プロジェクトの健全性]パネルを開きます。その後、[PDTステータスの検証]ボタンをクリックします。
未構築のPDTが検出されると、[プロジェクトの健全性]パネルにリストされます。
see_pdts
権限がある場合は、[PDT管理に移動]ボタンをクリックできます。[永続的な派生テーブル]ページの[開発]タブが開き、結果が特定のLookMLプロジェクトでフィルタリングされます。同じ場所で、どの開発PDTが作成済みで、どの開発PDTが未作成かを確認でき、その他のトラブルシューティング情報にアクセスできます。詳細については、管理者設定 - 永続的な派生テーブルドキュメンテーションページを参照してください。
プロジェクト内の未作成のPDTが特定できたら、そのPDTをクエリするExploreを開き、[Explore]メニューから[Rebuild Derived Tables & Run]オプションを使用して、そのPDTの開発バージョンを構築します。詳細については、このページの手動によるクエリ用の永続的なテーブルの再構築セクションを参照してください。
永続テーブルが、
if prod
ステートメントとif dev
ステートメントで条件付きWHERE
句を使用して定義されたSQLベースの派生テーブルである場合は、そのテーブルの開発バージョンには省略データセットが含まれているため、プロダクションに使用することはできません。詳細は、このページのLookerが開発テーブルを永続化する期間セクションを参照してください。dev_filters
パラメーターを含むネイティブ派生テーブルの場合は、テーブルの定義を変更してから、開発モードでテーブルをクエリしない限り、Lookerは開発モードのクエリにはプロダクションテーブルを使用して答えます(このドキュメンテーションページのLookerで開発テーブルが作成されるきっかけセクションを参照してください)。
テーブルの共有とクリーンアップ
任意のLookerインスタンス内で永続テーブルの定義と永続性メソッド設定が同一である場合、ユーザー間でそのテーブルを共有します。さらに、テーブルの定義が存在しなくなった場合には、Lookerはそのテーブルを期限切れとしてマークします。
これにはいくつかの利点があります。
- 開発モードでテーブルに対して変更を加えていない場合には、既存のプロダクションテーブルがクエリで使用されます。これは、使用しているテーブルが、
if prod
ステートメントとif dev
ステートメントで条件付きWHERE
句を使用して定義されたSQLベースの派生テーブルでない場合に当てはまります。テーブルが条件付きWHERE
句を使用して定義された場合は、開発モードでテーブルをクエリすると、Lookerは開発テーブルを構築します(dev_filters
パラメーターを含むネイティブ派生テーブルの場合は、テーブルの定義を変更してから、開発モードでテーブルをクエリしない限り、Lookerはプロダクションテーブルを使用して開発モードでクエリに答えます)。 - 開発者2人が開発モードでテーブルに同一の変更を加えた場合には、これらの開発者の間で同じ開発テーブルが共有されます。
- 開発モードからプロダクションモードへ変更内容をプッシュすると、古いプロダクションの定義は存在しなくなるため、古いプロダクションテーブルは期限切れとしてマークされ、削除されます。
- 開発モードの変更を廃棄することを決定した場合には、そのテーブルの定義は存在しなくなるため、不要な開発テーブルは期限切れとしてマークされ、削除されます。
開発モードでの作業の効率化
PDTの作成に長時間かかる場合もあります。例えば、開発モードで多数の変更内容をテストしている場合は時間がかかります。このような場合、開発モードを使用していれば、Lookerでよりサイズの小さい派生テーブルが作成されるように指定できます。
ネイティブ派生テーブルの場合は、explore_source
のdev_filters
サブパラメーターを使用して開発バージョンの派生テーブルにのみ適用されるフィルターを指定することができます。
上の例には、過去90日間のデータをフィルタリングして表示するdev_filters
パラメーターと、過去2年間かつYucca Valley Airportのデータをフィルタリングして表示するfilters
パラメーターが含まれています。
dev_filters
パラメーターはfilters
パラメーターと併用できるので、開発バージョンのテーブルにすべてのフィルタが適用されます。dev_filters
とfilters
の両方が同じ列のフィルタを指定している場合、開発バージョンのテーブルにはdev_filters
が優先的に適用されます。この例では、開発バージョンのテーブルは過去90日間のYucca Valley Airportのデータをフィルタリングして表示します。
SQLベースの派生テーブルの場合は、Lookerでプロダクション(if prod
)バージョンのテーブルと開発(if dev
)バージョンのテーブルに異なるオプションを指定する条件付きWHERE句がサポートされています。
この例では、プロダクションモードのクエリでは2000年以降のすべてのデータが含まれますが、開発モードのクエリでは2020年以降のデータのみが含まれます。この機能を戦略的に使用して結果セットを制限し、クエリ速度を向上させることで、開発モードの変更の検証が大幅にしやすくなります。
永続テーブルに
dev_filters
パラメーターや条件付きWHERE
句が含まれている場合、開発バージョンには省略データセットが含まれるため、開発テーブルをプロダクションバージョンとして使用することはできません。この場合、テーブルの開発が終わって変更をデプロイする前に、dev_filters
パラメーターまたは条件付きWHERE
句をコメント化してから開発モードでテーブルをクエリすることができます。こうすることで、Lookerは変更をデプロイした後もプロダクションで使用可能なフルバージョンのテーブルを構築します。プロダクションでの開発テーブルの使用についての詳細は、このページのLookerが開発テーブルを永続化する期間セクションを参照してください。
Lookerリジェネレータ
Lookerリジェネレーターは、トリガー永続テーブルのステータスをチェックし、再構築を開始します。トリガー永続テーブルとは、トリガーを永続性戦略として使用しているPDTのことです。
sql_trigger_value
を使用するテーブルの場合、トリガーはテーブルのsql_trigger_value
パラメーターで指定されるクエリになります。Lookerリジェネレータは、直近のトリガークエリチェックの結果が前回のトリガークエリチェックの結果と異なる場合に、テーブルの再構築をトリガーします。例えば、派生テーブルがSQLクエリSELECT CURDATE()
で永続している場合、Lookerリジェネレータは、日付が変わった後、次にトリガーをチェックするとき、テーブルを再構築します。デフォルトでは、Lookerのリジェネレーターは5分ごとにトリガークエリをチェックし、永続テーブルがトリガーされて、再ビルドする必要があるかどうか見極めます。ただし、テーブルの再構築に必要な時間は、このページの永続テーブルの導入に関する重要な考慮事項のセクションにある通り、他の要素にも影響されることがあります。interval_trigger
を使用するテーブルの場合、トリガーはテーブルのinterval_trigger
パラメーターで指定される期間になります。Lookerリジェネレータは、指定した時間が経過すると、テーブルの再構築をトリガーします。datagroup_trigger
を使用するテーブルの場合、トリガーは関連するデータグループのsql_trigger
パラメーターで指定されるクエリ、またはデータグループのinterval_trigger
パラメーターで指定される期間になります。
Lookerリジェネレータはpersist_for
パラメーターを使用する永続テーブルの再構築も開始しますが、これは、persist_for
テーブルがトリガー永続テーブルの依存カスケードである場合のみです。この場合、Lookerリジェネレーターはpersist_for
テーブルの再ビルドを開始します。カスケード内の他のテーブルを再ビルドするためにこのテーブルが必要になるからです。カスケードの一部でない場合、リジェネレータはpersist_for
戦略を使用する永続テーブルを監視しません。
PDTの構築に失敗した場合、リジェネレータは次のリジェネレータサイクルでテーブルの再構築を試みます。
- データベース接続で[Always Retry Failed PDT Builds]設定が有効になっている場合、テーブルのトリガー条件が満たされない場合でも、Lookerリジェネレータは次のリジェネレータサイクルでテーブルの再構築を試みます。
- [Always Retry Failed PDT Builds]設定が無効の場合、PDTのトリガー条件が満たされるまで、Lookerリジェネレータはテーブルの再構築を試行しません。
ユーザーがビルド中の永続テーブルからのデータをリクエストし、そのクエリ結果がキャッシュされていない場合、Lookerは既存のテーブルがまだ有効であるかどうかをチェックします。(新バージョンのテーブルと互換性がない旧テーブルは無効になる場合があります。これは、新テーブルに異なる定義がある、新テーブルで別のデータベース接続を使用している、または新テーブルがLookerの別のバージョンで作成されている場合に発生する可能性があります)。既存のテーブルがまだ有効な場合、Lookerは新しいテーブルがビルドされるまで既存のテーブルからデータを返します。一方、既存のテーブルが有効でない場合、Lookerは新しいテーブルが再ビルドされてからクエリ結果を返します。
永続テーブルの導入に関する重要な考慮事項
永続テーブル(PDTおよび集計テーブル)の有用性については、Lookerインスタンスでさまざまな考慮事項を簡単に集約できます。Lookerリジェネレーターが同時に多数のテーブルをビルトするために必要なシナリオを作成することができます。特にカスケードテーブルや実行時間の長いテーブルでは、テーブルの再構築までに時間がかかるシナリオや、データベースがテーブルを生成しょうとしている最中にそのテーブルからクエリ結果を得ようとすると遅延が発生するシナリオを作成することができます。
デフォルトでは、LookerのリジェネレータはPDTトリガーを5分おきにチェックして、トリガー永続テーブル(datagroup_trigger
、interval_trigger
、またはsql_trigger_value
永続性戦略を使用するPDTと集計テーブル)を再構築する必要があるか見極めます。
ただし、テーブルの再構築にかかる時間には他の要素が影響することがあります。
- Looker管理者がデータベース接続の[PDT And Datagroup Maintenance Schedule]設定を使用して、リジェネレータのトリガーチェックの間隔を変更しているかもしれません。
- デフォルトでは、リジェネレーターは1つの接続で1回、1つのPDTか集計テーブルの再ビルドを開始できます。Looker管理者は、接続設定の[Max PDT Builder Connections]フィールドを使用して、リジェネレーターが同時に再構築できる回数を調整できます。
- 同一の
datagroup
によりトリガーされるすべてのPDTおよび集計テーブルは、同じ再生成プロセスで再構築されます。この場合、直接または依存関係のカスケードの結果としてデータグループを使用するテーブルの数が多くなると、負荷が大きくなことがあります。
また、前述の考慮事項に加え、以下のように派生テーブルへ永続性を追加すべきでない状況もあります。
- 派生テーブルが拡張される場合 — PDTの拡張ごとにデータベース内にテーブルの新しいコピーが作成されます。
- 派生テーブルでは、テンプレートFilterまたはLiquidパラメーターを使用します。テンプレートFilterまたはLiquidパラメーターを使用する派生テーブルでは、永続性はサポートされていません。
access_filters
、またはsql_always_where
のユーザー属性を使用するネイティブ派生テーブルがExploreから構築される場合 — 指定されたユーザー属性の値ごとに、データベース内にテーブルのコピーが作成されます。
Looker接続での永続テーブルの数と複雑さによっては、サイクルごとにチェックし、再構築する必要のある永続テーブルがキューに多数含まれることになります。このため、Lookerインスタンスに派生テーブルを導入するには、これらの要素を念頭に置くことが重要です。
APIを使用した大規模なPDTの管理
インスタンスで多くのPDTを作成すると、さまざまなスケジュールで更新されるPDTの監視と管理がますます複雑になります。LookerのApache Airflow統合を使用して、PDTスケジュールを他のETLおよびELTプロセスと一緒に管理することを検討してください。
PTDの監視とトラブルシューティング
PDT、特にカスケードPDTを使用する場合は、PDTのステータス確認が役立ちます。Lookerの[Persistent Derived Tables]管理ページを使用すると、PTDのステータスを確認できます。詳細については、管理者設定 - 永続的な派生テーブルドキュメンテーションページを参照してください。
永続的な派生テーブルのトラブルシューティングの際の留意点は次のとおりです。
- PDTイベントログの調査時には、開発テーブルとプロダクションテーブルの対比に特に注意を払う。
- 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ベースの派生テーブルの場合は、共通テーブル式(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のクエリコメントのセクションを参照してください。
PDTのクエリコメント
データベース管理者は、通常のクエリとPDTを生成するクエリを簡単に見分けることができます。Lookerは、対象のPDTのLookMLモデルおよびビュー、さらにLookerインスタンスの一意の識別子(スラッグ)を含むCREATE TABLE ... AS SELECT ...
ステートメントにコメントを追加します。対象のPDTがユーザーに代わり開発モードで生成されている場合には、コメントにはそのユーザーのIDが表示されます。PDT生成コメントは、次のパターンに従います。
Exploreのクエリに代わりLookerがPDTを生成した場合には、PDT生成コメントがExploreのSQLタブに表示されます。コメントはSQLステートメントの上部に表示されます。
最後に、PDT生成コメントは、クエリ管理ページの各クエリのクエリ詳細ポップアップの[情報]タブの[メッセージ]フィールドに表示されます。
障害の後のPDTの再構築
PDTに障害がある場合、そのPDTのクエリ時に次のことが発生します。
- 同じクエリがすでに実行されていた場合は、Lookerがキャッシュ内の結果を使用します。(この仕組みについては、データグループによるクエリのキャッシングとPDTの再構築のドキュメンテーションページを参照してください。)
- 結果がキャッシュにない場合、Lookerはデータベース内に有効なPDTバージョンがあれば、そのPDTから結果を取得します。
- データベース内に有効なPDTがない場合は、PDTの再構築が試行されます。
- PDTを再構築できない場合、Lookerはクエリのエラーを返します。リジェネレーターは次回、PDTがクエリされたとき、または次回、PDTの永続性戦略によって再構築がトリガーされたときにPDTの再構築を試行します。
カスケードPDTにも同じロジックが適用されますが、カスケードPDTに関しては次の点が例外となります。
- 1つのテーブルのビルドに失敗したため、依存関係チェーン下方のPDTを構築できない。
- 依存PDTは基本的に依存先であるPDTをクエリするため、あるテーブルの永続性戦略が、依存関係で上位にあるPDTの再構築をトリガーすることがある。
前述の、TABLE_A
に依存するTABLE_B
に依存するTABLE_C
に、TABLE_D
が依存しているカスケードテーブルの例を振り返りましょう。
TABLE_B
に障害が発生した場合、標準の(カスケードではない)動作がすべてTABLE_B
に適用されます。TABLE_B
がクエリされると、Lookerは最初にキャッシュを使用して結果を返そうとし、次に可能であればテーブルの前のバージョンの使用を試み、次にテーブルを再構築しようとし、TABLE_B
を再構築できなければ最終的にエラーを返します。Lookerは、テーブルが次回クエリされたとき、またはテーブルの永続性戦略が次回再構築をトリガーしたときに、TABLE_B
をもう一度再構築しようとします。
TABLE_B
に依存するものにも同様のことが当てはまります。そのため、TABLE_B
をビルドできず、TABLE_C
へのクエリがある場合は、次のようになります。
- Lookerは、
TABLE_C
のクエリにキャッシュを使用しようとします。 - 結果がキャッシュにない場合、Lookerはデータベース内の
TABLE_C
から結果を取得しようとします。 TABLE_C
の有効なバージョンがない場合、LookerはTABLE_C
を再構築しようとし、これによってTABLE_B
に対するクエリが作成されます。- 次に、Lookerは
TABLE_B
を再構築しようとします(ただし、これはTABLE_B
が修正されていないと失敗します)。 TABLE_B
を再構築できない場合、TABLE_C
も再構築できません。そのためLookerはTABLE_C
に対するクエリにエラーを返します。- 次に、Lookerはその通常の永続性戦略に従って、または次回PDTがクエリされたとき(
TABLE_D
はTABLE_C
に依存するため、これには次回TABLE_D
が再構築を試みる場合も含まれます)に、TABLE_C
を再構築しようとします。
TABLE_B
の問題が解決したら、TABLE_B
とそれに依存する各テーブルが、永続性戦略に従って、または次回クエリされたとき(これには次回、依存PDTが再構築を試みた場合も含む)に、再構築を試みます。あるいは、カスケード内のPDTの開発バージョンが開発モードでビルドされている場合は、開発バージョンが新しいプロダクションPDTとして使用される場合があります。(この仕組みについては、このページの開発モードにおける永続テーブルのセクションを参照してください。)または、Exploreを使用してTABLE_D
に対するクエリを実行し、次に手動によりクエリ用のPDTを再構築すると、依存関係カスケードの上方のPDTすべてが強制的に再構築されます。