このページは、Looker で LookML を使用して Explore を構築しようとしているユーザーを対象としています。SQL に精通していると(特に内部結合と外部結合の違いを理解している場合)、ページを理解しやすくなります。内部結合と外部結合の違いについては、SQL 結合にある w3schools の記事をご覧ください。
Looker は貴社にとって強力な SQL エンジンになります。LookML の抽象的なモデリングにより、データチームと IT チームは常に正しい一般的なルールを構築することができ、ビジネス アナリストは、データチームがその必要性を予測しなかった場合でも、常に正しいクエリを作成することができます。この機能の中核となるのは、対称集計アルゴリズムです。このアルゴリズムは、SQL 結合に関する業界全体の問題を解決します。ただし、このアルゴリズムを活用するには、2 つのことを正しく行う必要があります。主キーは、メジャーを含むすべてのビュー(通常はすべて)で正確であり、relationship
パラメータはすべての結合で正しい必要があります。
主キー
多くの点で、テーブルの主キーを理解することは、テーブルの内容やテーブルでできることを理解することです。唯一の注意点は、主キーとして選択した列(または連結列のセット)に繰り返し値が含まれないことです。
relationship
パラメータ
主キーを検証したら、結合の relationship
パラメータの正しい値を決定できます。relationship
パラメータの目的は、結合が SQL クエリに書き込まれたときに、対称集計を呼び出すかどうかを Looker に伝えることです。この場合、Looker に呼び出しを常に実行すれば、常に正確な結果が得られます。ただし、これにはパフォーマンスの費用がかかるため、対称集計は慎重に使用することをおすすめします。
正しい値を決定するプロセスは、内部結合と外部結合とで多少異なります。
内部結合
たとえば、主キーが order_id
の注文テーブルがあるとします。
order_id | amount | customer_id |
---|---|---|
1 | $25.00 | 1 |
2 | $50.00 | 1 |
3 | $75.00 | 2 |
4 | $35.00 | 3 |
主キーが customer_id
の顧客テーブルがあるとします。
customer_id | first_name | last_name | visits |
---|---|---|---|
1 | Amelia | Earhart | 2 |
2 | Bessie | Coleman | 2 |
3 | Wilbur | Wright | 4 |
これらのテーブルは、両方のテーブルに存在する customer_id
フィールドで結合できます。この結合は LookML で次のように表現されます。
explore: orders { join: customers { type: inner sql_on: ${orders.customer_id} = ${customers.customer_id} ;; relationship: many_to_one } }
この LookML の結合の結果は、次のように単一の結合テーブルとして表すことができます。
order_id | amount | customer_id | customer_id | first_name | last_name | visits |
---|---|---|---|---|---|---|
1 | $25.00 | 1 | 1 | Amelia | Earhart | 2 |
2 | $50.00 | 1 | 1 | Amelia | Earhart | 2 |
3 | $75.00 | 2 | 2 | Bessie | Coleman | 2 |
4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
many_to_one
関係は、結合フィールド(customer_id
)の 1 つの値が各テーブルに表現される回数を指します。orders
テーブル(左側のテーブル)では、1 つのお客様 ID が何度も表されています(この場合は、ID が 1
のお客様が複数の行にあります)。
customers
テーブル(右側のテーブル)では、customer_id
がそのテーブルの主キーであるため、すべてのお客様 ID が 1 回だけ表されます。したがって、orders
テーブル内のレコードは、customers
テーブル内の 1 つの値に対して多数の一致を持つ可能性があります。customer_id
が customers
テーブルのすべての行で一意でない場合、関係は many_to_many
になります。
主キーを確認して、プログラムによって正しい関係値を決定するには、次の手順を行います。
- 最初に、関係として
many_to_many
を記述します。Looker によって対称集計アルゴリズムがトリガーされ、精度が強制されるため、主キーが正しい限り、常に正確な結果が生成されます。ただし、このアルゴリズムではクエリが複雑になり、実行時間が長くなるため、一方または両方のクエリをmany
ではなくone
に変更することをおすすめします。 - 左側の表の
sql_on
句にあるフィールドを確認します。左側のテーブルの主キーを構成するフィールドがある場合は、relationship
パラメータの左側をone
に変更できます。それ以外の場合は、通常はmany
のままにする必要があります。(特殊なケースについては、このページで後述の考慮事項をご覧ください)。 - 次に、
sql_on
句で右側のテーブルを表すフィールドを確認します。いずれかのフィールドが右側のテーブルの主キーを形成している場合は、右側をone
に変更できます。
sql_on
フレーズを記述するときは、等号の左側に表示される左側のテーブルと、右側にある右側のテーブルから始めることをおすすめします。順序がデータベースの SQL 言語に関連していない限り、sql_on
パラメータの条件の順序は重要ではありません。sql_on
パラメータでは、このようにフィールドを並べる必要はありませんが、等号の左右が relationship
パラメータの左から右への読み方と一致するように sql_on
条件を並べると、関係を判断するのに役立ちます。また、フィールドを並べ替えることで、新しいテーブルを結合する Explore の既存のテーブルが一目で簡単に識別できます。
外部結合
外部結合の場合、結合中に null レコードが追加されたときにファンアウトが発生する可能性があることも考慮する必要があります。Looker では左側外部結合がデフォルトであるため、これは特に重要です。null レコードは合計や平均には影響しませんが、Looker が type: count
の測定を実行する方法に影響します。正しく行われないと、null レコードがカウントされます(これは望ましい動作ではありません)。
完全外部結合では、結合キーに他方のテーブルに存在する値がない場合、どちらかのテーブルに null レコードが追加される可能性があります。次の例では、orders
テーブルが関係しています。
order_id | amount | customer_id |
---|---|---|
1 | $25.00 | 1 |
2 | $50.00 | 1 |
3 | $75.00 | 2 |
4 | $35.00 | 3 |
たとえば、次の customers
テーブルがあるとします。
customer_id | first_name | last_name | visits |
---|---|---|---|
1 | Amelia | Earhart | 2 |
2 | Bessie | Coleman | 2 |
3 | Wilbur | Wright | 4 |
4 | Charles | Yeager | 3 |
これらのテーブルが結合されると、結合されたテーブルは次のように表示されます。
order_id | amount | customer_id | customer_id | first_name | last_name | visits |
---|---|---|---|---|---|---|
1 | $25.00 | 1 | 1 | Amelia | Earhart | 2 |
2 | $50.00 | 1 | 1 | Amelia | Earhart | 2 |
3 | $75.00 | 2 | 2 | Bessie | Coleman | 2 |
4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
null | null | null | 4 | Charles | Yeager | 3 |
内部結合と同様に、テーブルの主キー間の関係は many_to_one
です。ただし、null レコードが追加されたことにより、左側のテーブルでも対称集計が必要になります。この結合を実行すると、左側のテーブルのカウントが中断されるので、relationship
パラメータを many_to_many
に変更する必要があります。
この例が左外部結合だった場合、null 行は追加されず、余分な顧客レコードが削除されます。その場合、関係は引き続き many_to_one
になります。ベーステーブルが分析を定義することを前提としているため、Looker のデフォルトとなります。この場合は、顧客ではなく注文を分析します。顧客テーブルが左側にある場合、状況は異なります。
マルチレベル結合
一部の Explore では、ベーステーブルが 1 つ以上のビューに結合され、さらに 1 つ以上の他のビューに結合する必要があります。この例では、テーブルが顧客テーブルに結合されます。このような場合は、relationship
パラメータを評価する際に、書き込まれる個々の結合のみを確認することをおすすめします。ダウンストリーム ファンアウトがクエリに影響するタイミングは、影響を受けるビューが実際にファンアウトを作成した結合に存在しない場合でも、Looker が把握します。
Looker のメリット
Looker には、関係値が正しいことを確認するメカニズムがあります。1 つは、主キーの一意性の確認です。メジャーを計算するためにファンアウトがあり、対称集計が必要な場合、Looker は使用された主キーの一意性をチェックします。一意でない場合は、クエリの実行時にエラーが表示されます(ただし、LookML 検証ツールのエラーはありません)。
また、Looker でファンアウトを処理する方法がない場合(通常は主キーが指定されていないため)、そのビューでは Explore にメジャーが表示されません。この問題を解決するには、メジャーを Explore に取り込めるように、フィールドを主キーとして指定します。
注意点
対称集計の言語 サポート
Looker では、対称集計をサポートしていない一部の言語と接続できます。言語とその対称集計のサポート状況のリストについては、symmetric_aggregates
のドキュメント ページをご覧ください。
特殊なケース
このページの前の内部結合の項では、正しい関係値を決定するために、左側のテーブルから sql_on
句にある 1 つまたは複数のフィールドを見る必要があると述べています。"1 つまたは複数のフィールドが左のテーブルの主キーを形成している場合、relationship
パラメータの左側を one
に変更することができます。そうでない場合は、通常は many
のままにする必要があります。これは、テーブルにレコードが繰り返されていない複数の列が含まれている場合を除いて当てはまります。この場合、primary_key: yes
で指定された列でなくても、関係を構築するときにそのような列を主キーとして扱うことができます。
前の段落の文が常に指定した列に対して当てはまるよう、なんらかのソフトウェア ルールが設定されていることを確認してください。そうであれば、そのまま実行し、後でそれを参照できるように、ビューファイルにその特別なプロパティをメモします(SQL Runner リンクを使用して証明します)。ただし、フィールドが主キーとして指定されている場合、Looker が暗黙の一意性の真正性を確認しますが、他のフィールドについては同じではありません。対称集計アルゴリズムは呼び出されません。