LookMLでの結合の使用

結合を使用すると、異なるビューに接続できるため、複数のビューから同時にデータを探索し、データのさまざまな部分が互いにどのように関連しているかを確認できます。

たとえば、データベースにテーブル order_items、orders、users が含まれている場合があります。すべてのテーブルから同時にデータを探索するために、結合を使用できます。このページでは、特定の結合パラメータや結合のパターンなど、LookMLでの結合について紹介します。

結合はExploreから開始

結合はモデルファイルで定義され、Explore とビューの関係を確立します。結合は1つ以上のビューを単一のExploreに、直接、または他の結合されたビューを介して連結します。

2 つのデータベース テーブル order_items と orders について検討します。両方のテーブルのビューを生成してから、それらの 1 つ以上を、モデルファイルの explore パラメータで次のように宣言します。

explore: order_items { ... }

クエリを order_items Explore から実行すると、生成された SQL の FROM 句に order_items が表示されます。

SELECT ...
FROM order_items

order_items Explore に追加情報を結合できます。たとえば、次のサンプル LookML を使用して、orders ビューを order_items Explore に結合できます。

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
}

前述の LookML は 2 つのことを達成します。まず、Explore フィールド ピッカーに orders と order_items の両方のフィールドを表示できます。

注文された商品アイテム Explore には、注文された商品アイテムビューのフィールドと、結合された注文ビューのフィールドが含まれます。

次に、LookML で orders と order_items を結合する方法を説明します。このLookMLが、次のようなSQLに変換されます。

SELECT ...
FROM order_items
LEFT JOIN orders
ON order_items.order_id = orders.id

これらのLookMLパラメーターについては、以下のセクションで詳しく説明します。

結合パラメータ

結合には、4 つの主要なパラメータ（join、type、relationship、sql_on）が使用されます。

ステップ1：Exploreの開始

まず、order_items Explore を作成します。

explore: order_items { ... }

ステップ 2: join

テーブルを結合する場合は、まずビュー内のテーブルを宣言する必要があります。この例では、orders がモデル内の既存のビューであるとします。

次に、join パラメータを使用して、orders ビューを order_items Explore に結合することを宣言します。

explore: order_items {
  join: orders { ... }
}

ステップ 3: type

どのタイプの結合を行うか検討します。Looker は LEFT JOIN、INNER JOIN、FULL OUTER JOIN、CROSS JOIN をサポートしています。これらは、type パラメータの値 left_outer、inner、full_outer、cross に対応します。

explore: order_items {
  join: orders {
    type: left_outer
  }
}

type のデフォルト値は left_outer です。

ステップ 4: relationship

order_items Explore と orders ビューの間の結合関係を定義します。Looker が正確なメジャー計算するには、結合の関連性を適切に宣言することが重要です。関係は、order_items Explore から orders ビューへ定義されます。指定できるオプションは one_to_one、many_to_one、one_to_many、many_to_many です。

この例では、単一の注文に対して多くの注文された商品アイテムが存在する可能性があります。order_items Explore から orders ビューへの関係は many_to_one です。

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
  }
}

結合に relationship パラメータを含めない場合、Looker のデフォルトは many_to_one です。

結合に対して relationship パラメータを正しく定義する追加のヒントについては、relationship パラメータを正しく取得するをご覧ください。

ステップ 5: sql_on

sql_on パラメータと foreign_key パラメータのいずれかを使用して、order_items テーブルと orders テーブルを結合する方法を宣言します。

sql_on パラメータは、クエリで生成された SQL の ON 句と同等です。このパラメータを使用して、どのフィールドを組み合わせて結合を行うかを宣言できます。

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
}

もっと複雑な結合を記述することもできます。例えば、id が1,000より大きいオーダーだけを結合する必要があるかもしれません。

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} AND ${orders.id} > 1000 ;;
  }
}

これらの例の ${ ... } 構文の詳細については、置換演算子のドキュメントをご覧ください。

ステップ6：テスト

[Order Items] に移動して、この結合が期待通り機能するかテストします。order_items と orders の両方のフィールドが表示されるはずです。

Explore で LookML の変更をテストする方法については、Explore のフィールドのテストをご覧ください。

別のビューを介した結合

別のビューを介してビューをExploreに結合できます。結合パラメータの例では、order_id フィールドを介して orders を order_items に結合しました。また、共通のフィールドがなくても、users というビューのデータを order_items Explore に結合することもできます。これは、orders ビューを介して結合することで実現できます。

order_items Explore ではなく、sql_on パラメータまたは foreign_key パラメータを使用して users ビューを orders ビューに結合します。これは、orders のフィールドを orders.user_id と正しくスコーピングすることによって行います。

sql_on パラメータを使用した例を次に示します。

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
  join: users {
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.user_id} = ${users.id} ;;
  }
}

ビューの複数回結合

users ビューには、購入者と販売者の両方のデータが含まれます。このビューのデータを order_items に結合するが、購入者と販売者で別々にそれを行うには、from パラメータを使用して異なる名前で users を 2 回結合します。

from パラメータを使用すると、結合に固有の名前を付けて、その結合でどのビューを使用するかを指定できます。次に例を示します。

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
  join: buyers {
    from: users
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.buyer_id} = ${buyers.id} ;;
  }
  join: sellers {
    from: users
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.seller_id} = ${sellers.id} ;;
  }
}

この場合、buyers として結合されるのは購入者データのみであり、sellers として結合されるのは販売者データのみです。

注: users ビューは、結合でそのエイリアス名 buyers と sellers で参照する必要があります。

結合からのフィールドの制限

fields パラメータを使用すると、結合から Explore に取り出すフィールドを指定できます。デフォルトでは、ビューのすべてのフィールドが結合時に取り出されます。しかし、一部のフィールドだけを取り出したい場合もあります。

たとえば、orders が order_items に結合されている場合は、結合で shipping フィールドと tax フィールドのみを取り出すことができます。

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
    fields: [shipping, tax]
  }
}

[set_a*] など、一連のフィールドを参照することもできます。各セットは、set パラメータを使用してビュー内で定義されます。orders ビューに次のセットが定義されているとします。

set: orders_set {
  fields: [created_date, shipping, tax]
}

orders を order_items に結合するときに、次の 3 つのフィールドのみを取り出すように選択できます。

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
    fields: [orders_set*]
  }
}

対称集計

Looker は、結合によってファンアウトが起こっても、集計（合計や平均など）を正しく計算するために「対称集計」と呼ばれる機能を使用しています。対称集計については、対称集計についてで詳しく説明されています。対称集計によって解決されるファンアウトの問題については、SQL ファンアウトの問題のコミュニティ投稿で説明されています。

必要なプライマリキー

結合からメジャー（集計）を得るには、その結合に関係しているすべてのビューでプライマリキーを定義する必要があります。

これを行うには、各ビューの主キーフィールドの定義に primary_key パラメータを追加します。

dimension: id {
  type: number
  primary_key: yes
}

サポートされている SQL 言語

LookerがあなたのLookerプロジェクトの対称集計をサポートできるようにするには、あなたのデータベース言語もそれをサポートしていなければなりません。次の表に、Looker の最新リリースで対称集計をサポートする言語を示します。

方言 サポート対象
Actian Avalanche
○
Amazon Athena
○
Amazon Aurora MySQL
○
Amazon Redshift
○
Apache Druid
×
Apache Druid 0.13+
×
Apache Druid 0.18+
×
Apache Hive 2.3+
×
Apache Hive 3.1.2+
×
Apache Spark 3 以降
○
ClickHouse
×
Cloudera Impala 3.1+
○
ネイティブ ドライバを使用した Cloudera Impala 3.1+
○
ネイティブ ドライバを使用した Cloudera Impala
×
DataVirtuality
○
Databricks
○
Denodo 7
○
Denodo 8
○
Dremio
×
Dremio 11+
○
Exasol
○
Firebolt
○
Google BigQuery Legacy SQL
○
Google BigQuery Standard SQL
○
Google Cloud PostgreSQL
○
Google Cloud SQL
○
Google Spanner
○
Greenplum
○
HyperSQL
×
IBM Netezza
○
MariaDB
○
Microsoft Azure PostgreSQL
○
Microsoft Azure SQL Database
○
Microsoft Azure Synapse Analytics
○
Microsoft SQL Server 2008+
○
Microsoft SQL Server 2012+
○
Microsoft SQL Server 2016
○
Microsoft SQL Server 2017+
○
MongoBI
×
MySQL
○
MySQL 8.0.12+
○
Oracle
○
Oracle ADWC
○
PostgreSQL 9.5+
○
PostgreSQL 9.5 より前
○
PrestoDB
○
PrestoSQL
○
SAP HANA 2+
○
SingleStore
○
SingleStore 7+
○
Snowflake
○
Teradata
○
Trino
○
Vector
○
Vertica
○

お使いの言語が対称集計をサポートしていない場合は、Lookerで結合を実行するときに注意してください。結合のタイプによっては集計が不正確になることがあります（合計や平均など）。この問題とその回避策については、SQL ファンアウトの問題のコミュニティ投稿で詳しく説明しています。

結合についての詳細

LookML の結合パラメータの詳細については、結合リファレンス ドキュメントをご覧ください。