派生テーブルの SQL コンセプト

通常、LookML ビューはデータベース内の既存のテーブルに基づいていますが、SQL SELECT ステートメントに基づくビューを作成することもできます。Looker では、このタイプのビューを「派生テーブル」と呼びます。派生テーブルは、クエリ結果をデータベース内の実際のテーブルのように使用できるクエリのことです。

このガイドでは、次のトピックについて説明します。

Looker が派生テーブルの SQL を生成する方法

LookML で派生テーブルをクエリすると、Looker は言語に応じてクエリを共通テーブル式（CTE）またはインライン ビューとして SQL ステートメントに変換します。つまり、生成された SQL クエリは次のいずれかの例のようになります。

次の例は、Looker が CTE を使用して派生テーブルをクエリする SQL クエリを生成する方法を示しています。

WITH (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users
SELECT ...
FROM users AS u
INNER JOIN top_100_users ON u.id == top_100_users.id
WHERE ...

次の例は、Looker がインライン ビューを使用して派生テーブルをクエリする SQL クエリを生成する方法を示しています。

SELECT ...
FROM users AS u
INNER JOIN (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users ON u.id == top_100_users.id
WHERE ...

SQLベースの派生テーブル

SQL ベースの派生テーブルを作成するには、ビューの derived_table パラメータ内の sql パラメータを使用して、LookML 内で SQL クエリを直接定義します。これにより、SQL を使用して派生テーブルの列を定義できます。

たとえば、次の LookML の例では、すべての注文の合計支出額が上位 100 人のユーザーを特定する top_100_users という派生テーブルを定義しています。

view: top_100_users {
  derived_table: {
    sql: SELECT o.user_id as id
         FROM orders AS o
         INNER JOIN order_items AS oi ON o.id == oi.order_id
         ORDER BY SUM(oi.total_sale_price) DESC
         GROUP BY o.customer_id
         LIMIT 100 ;;
  }

  dimension: id {
    type: number
    sql: ${TABLE}.id ;;
  }
}

Explore クエリで top_100_users ビューが参照されると、Looker は生成された SQL でこの SQL SELECT ステートメントを、言語に応じて CTE またはインライン ビューとして使用します。

SQL を使用して派生テーブルを定義すると、いくつかの制限が生じる可能性があります。たとえば、SQL ベースの派生テーブルのセクションの例では、次の考慮事項が適用されます。

  • orders テーブルと order_items テーブルの関係は、LookML モデルですでに定義されている可能性があります。データベースの基盤となるテーブル名が変更された場合は、SQL ベースの派生テーブルの定義など、複数の場所でテーブル名を更新する必要があります。
  • 派生テーブル定義の SQL は、基盤となるデータベースの正しい言語で記述する必要があります。データが別のデータベースに移動された場合は、SQL ベースの派生テーブルの定義を変更する必要があります。

これらの理由から、ネイティブ派生テーブルの方が適している場合が多くあります。

ネイティブ派生テーブル

Looker では、ネイティブ派生テーブルは LookML で定義されます。SQL ベースの派生テーブルを使用する場合とは対照的に、ネイティブ派生テーブルを定義する場合は、次の方法で LookML モデルを活用します。

  • データベースでテーブル名が変更された場合は、LookML モデルで 1 回だけ更新する必要があります。ネイティブ派生テーブルは、データベース テーブルが定義されている既存の LookML オブジェクトを参照するため、ネイティブ派生テーブルは適切なテーブルを自動的に参照します。
  • 同様に、別の言語に移行した場合でも、Looker はデータベース接続に適した SQL を生成するため、ネイティブ派生テーブルの LookML は引き続き有効です。

ネイティブ派生テーブルの LookML を手動で定義できます。ただし、ネイティブ派生テーブルを作成する最も簡単な方法は、Looker に Explore クエリから派生テーブルを作成させることです。Explore で、派生テーブルで使用するフィールドを選択し、Explore の歯車メニューから [LookML を取得] > [派生テーブル] オプションを使用して LookML を取得します。Looker は、Explore クエリから派生テーブルを作成するために必要な LookML を生成します。これには、派生テーブルの列を作成するために必要な LookML モデルの関連するフィールド定義が含まれます。LookML をプロジェクトのビューファイルにコピーして、派生テーブルを作成できます。

次の例は、すべての注文の合計支出額が最も多い上位 100 人のユーザーを特定するネイティブ派生テーブルを示しています。

view: top_100_users {
  derived_table: {
    explore_source:  orders {
      column: id {
        field: orders.customer_id
      }
      sorts: [order_items.sum_total_sale_price desc]
      limit: 100
    }
  }
}

Explore でネイティブ派生テーブルをクエリすると、ネイティブ派生テーブルの SQL クエリのすべての詳細が自動的に生成されます。ネイティブ派生テーブルを含む LookML ビューの列の定義は、基盤となる Explore の定義から推測されるため、定義を繰り返す必要はありません。

永続的な派生テーブル（PDT）

Looker では、一時的な派生テーブルと永続的な派生テーブルの両方を作成できます。SQL ベースの派生テーブルまたはネイティブ派生テーブルを作成したら、永続性を追加して、Looker がテーブルをデータベースのスクラッチ スキーマに書き込み、指定したスケジュールで再生成できるようにします。詳しくは、Looker の派生テーブルをご覧ください。