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

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

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

• Looker が派生テーブルの SQL を生成する仕組み
• SQLベースの派生テーブル
• ネイティブ派生テーブル
• 永続的な派生テーブル（PDT）

Looker が派生テーブルの SQL を生成する仕組み

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

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

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 モデルで一度だけ更新する必要があります。ネイティブ派生テーブルは、データベース テーブルが定義されている既存の 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 の派生テーブルをご覧ください。

関連リソース

• Lookerの派生テーブル
• derived_table
• ネイティブ派生テーブルの作成
• How Looker generates SQL（LookerのSQL生成の仕組み）