メトリクスビューでの結合

このページでは、メトリクスビューで結合を使用して、関連テーブルの属性でソースデータをエンリッチする方法について説明します。

メトリックビューでのjoinは、ファクトテーブルからディメンションテーブルへの直接join (スター スキーマ)、正規化されたディメンションテーブル間でのマルチホップjoin (スノーフレーク スキーマ)、および関連テーブルからのファクトを集約する1対多のjoinをサポートします。デフォルトでは、すべての結合は多対一です。これは、各ソース行が結合テーブル内の最大1行に一致することを意味します。

スタースキーマの結合

スタースキーマでは、sourceがファクトテーブルであり、1つ以上のディメンションテーブルとLEFT OUTER JOINを使用して結合します。メトリクスビューは、選択されたフィールドと測定値に基づいて、特定のクエリに必要なファクトテーブルとディメンションテーブルを結合します。

on句（Boolean式）またはusing句（共有カラム名）のいずれかを使用して、結合カラムを指定します。結合は多対一の関係に従う必要があります。多対多の場合、結合されたディメンションテーブルから最初に一致する行が選択されます。

次の例では、 ordersファクトテーブルをcustomerディメンションテーブルにon句（ブール式を受け取る）で結合しています。

YAML

version: 1.1
source: samples.tpch.orders

joins:
  # The on clause supports a Boolean expression
  - name: customer
    source: samples.tpch.customer
    on: source.o_custkey = customer.c_custkey

fields:
  # Field referencing a join column using dot notation
  - name: Customer name
    expr: customer.c_name
  - name: Customer market segment
    expr: customer.c_mktsegment

measures:
  # Measure referencing a join column
  - name: Total revenue
    expr: SUM(o_totalprice)
  - name: Order count
    expr: COUNT(1)

結合列が両方のテーブルで同じ名前である場合、on 句の代わりに using 句を使用します。using句は、ソーステーブルと結合されたテーブルの両方に存在する列名の配列を受け取ります。samples カタログに結合列名が共通するデータセットはありません。そのため、以下の例では、構文を説明するためにプレースホルダーのテーブル名と列名を使用しています。

YAML

joins:
  - name: customer
    source: catalog.schema.customer
    using:
      - customer_id

注記

on句では、sourceはメトリクスビューのソーステーブルを指し、結合nameは結合されたテーブルからの列を指します。例えば、source.o_custkey = customer.c_custkeyはソーステーブルのo_custkey列をcustomerテーブルのc_custkey列に結合します。プレフィックスが提供されない場合、参照は結合テーブルにデフォルトで設定されます。

スノーフレークスキーマの結合

スノーフレークスキーマは、ディメンションテーブルを正規化してサブディメンションに接続することで、スタースキーマを拡張したものです。マルチレベル結合構造を作成します。

スノーフレークスキーマを定義するには：

1. メトリクスビューを作成します。
2. 第1レベル（スタースキーマ）の結合を追加してください。
3. 他のディメンションテーブルと結合。
4. ビューにフィールドを追加することで、ネストされた属性を表示します。

以下の例では、TPC-H データセットを使用して、注文の地理的階層を示すスノーフレークスキーマを説明します。この例では、ordersテーブルを顧客に結合し、次にその国（国）に、最後にその地域（大陸）に結合します。TPC-Hデータセットは、Databricksワークスペースのsamplesカタログで利用可能です。

YAML

source: samples.tpch.orders

joins:
  - name: customer
    source: samples.tpch.customer
    on: source.o_custkey = customer.c_custkey
    joins:
      - name: nation
        source: samples.tpch.nation
        on: customer.c_nationkey = nation.n_nationkey
        joins:
          - name: region
            source: samples.tpch.region
            on: nation.n_regionkey = region.r_regionkey

fields:
  - name: clerk
    expr: o_clerk
  - name: customer
    expr: customer
    comment: returns the full customer row as a struct
  - name: customer_name
    expr: customer.c_name
  - name: nation
    expr: customer.nation
  - name: nation_name
    expr: customer.nation.n_name

カーディナリティに参加する

cardinalityフィールドは、結合におけるソーステーブルと結合されたテーブル間の関係を制御します。このフィールドは、結合テーブルから列を参照するメジャーをエンジンがどのように処理するかを決定します。

次の表は、サポートされている2つのカーディナリティを比較しています。

属性	many_to_one （デフォルト）	one_to_many
ソース行ごとの一致行数	最大1件	0個以上
典型的な使用例	ディメンション ルックアップ	ファクト拡大
許可されています fields	はい	No
許可されています measures	はい	はい

多対一の結合

多対一はデフォルトカーディナリティです。ソースの各行が結合テーブルの最大1行に一致するため、結合テーブルはディメンション参照として機能します。多対1結合では、cardinalityフィールドを省略するか、cardinality: many_to_oneを明示的に指定できます。

フィールドとメジャーはどちらも、ドット表記を使用して多対一の結合からの列を参照できます（例：customer.c_name）。

結合制約を宣言します rely

「rely.at_most_one_match: true」の設定は、結合の「「一」」側がファンアウトしないことを宣言します。

• 多対一結合では、各ソース行は結合テーブルの最大1行に一致します。
• 一対多の結合では、結合された各行は最大1つのソース行と一致します。

この宣言を使用すると、特に結合されたテーブルのフィールドでフィルタリングを行うクエリにおいて、エンジンが不要な結合をスキップし、スキャンされるデータ量を削減できます。Databricks は、制約が有効な場合、両方のカーディナリティで rely を設定することを推奨しています。

警告

関係が実際に成立する場合にのみ、at_most_one_match: trueを設定してください。このプロパティはランタイムでは検証されません。一意性がアサートされた側でファンアウトが発生した場合、メジャーは誤った結果を返します。

次の例では、orders を customer に結合し、rely を有効にします：

YAML

version: 1.1
source: samples.tpch.orders

joins:
  - name: customer
    source: samples.tpch.customer
    on: source.o_custkey = customer.c_custkey
    rely:
      at_most_one_match: true

fields:
  - name: Customer name
    expr: customer.c_name
  - name: Customer market segment
    expr: customer.c_mktsegment

measures:
  - name: Total revenue
    expr: SUM(o_totalprice)
  - name: Order count
    expr: COUNT(1)

「relyを使用して結合を最適化する」の完全なrelyフィールドのリファレンスを参照してください。

1対多の結合

cardinality: one_to_many を設定して、単一のソース行が結合テーブルの複数の行に一致できるようにします。これにより、そのテーブルは、エンジンがソース粒度で個別に集計するファクトソースとなります。

注記

一対多結合には Databricks Runtime 18.1 以降と YAML 仕様バージョン 1.1 が必要です。「メトリクス ビューの機能の可用性」を参照してください。

一対多の結合により、クエリ結果のソース行を重複させることなく、単一のメトリクスビューで、顧客ごとの注文やアカウントごとのイベントなど、異なる粒度で存在する事実を測定することが可能になります。ソースはディメンションの骨格として機能します。結合されたテーブルに一致する行がいくつ存在しようとも、各エンティティは厳密に1回だけ出現します。

一対多結合の例

次の例では、customerをソースとして使用し、ordersとcardinality: one_to_manyを結合します。nation への many_to_one 結合が nation_name フィールドを提供します。各結合条件のソース側を source. で修飾することで、参照がメトリクスビューのソーステーブルに解決されるようにします。両方の結合がrely.at_most_one_match: trueを設定します。nation結合では、各顧客が最大で1つの国を持つことを表明し、orders結合では、各注文が最大で1つの顧客に属することを表明します。rely による結合制約の宣言を参照してください。

YAML

version: 1.1
source: samples.tpch.customer

joins:
  - name: nation
    source: samples.tpch.nation
    on: nation.n_nationkey = source.c_nationkey
    rely:
      at_most_one_match: true
  - name: orders
    source: samples.tpch.orders
    on: orders.o_custkey = source.c_custkey
    cardinality: one_to_many
    rely:
      at_most_one_match: true

fields:
  - name: customer_name
    expr: c_name
  - name: nation_name
    expr: nation.n_name

measures:
  - name: customer_count
    expr: count(*)
  - name: order_count
    expr: count(orders.o_orderkey)
  - name: total_order_revenue
    expr: sum(orders.o_totalprice)

このビューでは、customer_count はソース customer テーブルの行数をカウントするのに対し、order_count と total_order_revenue は orders ブランチから行を集約します。2つの注文を持つ顧客は、order_countが2を返し、customer_countが1のままであるため、ソース行が重複していないことが確認されます。注文のない顧客も、order_countは0、NULL total_order_revenueとして結果に表示されます。

ネストされた1対多の結合

ソースから2レベル以上下にある事実を測定するには、一対多の結合をネストしてください。一対多のサブツリー内のすべての結合は同じカーディナリティを共有する必要があります。そのため、一対多の親は多対一の子を持つことはできません。結合名全体を通る完全なドットパスを使用して、ネストされた結合の列を参照します。

次の例では、lineitem を orders の下にネストして、単一の顧客粒度ビューで注文と明細項目の両方をカウントできるようにします。

YAML

version: 1.1
source: samples.tpch.customer

joins:
  - name: orders
    source: samples.tpch.orders
    on: orders.o_custkey = source.c_custkey
    cardinality: one_to_many
    joins:
      - name: lineitem
        source: samples.tpch.lineitem
        on: lineitem.l_orderkey = orders.o_orderkey
        cardinality: one_to_many

fields:
  - name: customer_name
    expr: c_name

measures:
  - name: order_count
    expr: count(distinct orders.o_orderkey)
  - name: line_item_count
    expr: count(orders.lineitem.l_linenumber)
  - name: total_line_revenue
    expr: sum(orders.lineitem.l_extendedprice)

メジャーは、結合名を介した完全なドットパスでネストされた列を参照します。たとえば、orders.lineitem.l_extendedpriceなど。これは、lineitemがordersを介してのみ到達可能であるためです。注文数をカウントするには、通常のcountではなくcount(distinct orders.o_orderkey)を使用してください。各注文は複数の明細項目に展開されるため、通常のカウントでは注文が明細項目ごとに1回カウントされてしまいます。

関連する一対多の結合

単一のビューから独立したファクトソースを測定するために、同じレベルで複数の一対多の結合を定義できます。兄弟結合は個別に集計され、その後ブレンドされます。そのため、その行が交差乗算されることはありません。トップレベルの兄弟はカーディナリティを自由に混在させることができ、そのため、many_to_oneディメンション結合とone_to_manyファクト結合は同じレベルで共存できます。

以下の例では、nationをソースとして使用し、2つの独立した1対多の分岐であるcustomerとsupplierを追加します：

YAML

version: 1.1
source: samples.tpch.nation

joins:
  - name: customer
    source: samples.tpch.customer
    on: customer.c_nationkey = source.n_nationkey
    cardinality: one_to_many
  - name: supplier
    source: samples.tpch.supplier
    on: supplier.s_nationkey = source.n_nationkey
    cardinality: one_to_many

fields:
  - name: nation_name
    expr: n_name

measures:
  - name: customer_count
    expr: count(customer.c_custkey)
  - name: supplier_count
    expr: count(supplier.s_suppkey)
  - name: customers_per_supplier
    expr: count(customer.c_custkey) / count(supplier.s_suppkey)

customers_per_supplierメジャーは、エンジンがそれぞれをクエリ粒度にブレンドした後、2つの独立した集計を分割します。異なるソースからの測定基準を算術演算を使用して組み合わせることができます。ただし、単一の集計関数は1つのソースからの列のみを参照する必要があります。

複数のファクトテーブルをブリッジテーブルで接続する

メトリクスビューは、ディメンションテーブルに結合された単一のファクトテーブルをモデル化します。異なる粒度にある2つ以上のファクトテーブルから測定値を結合するには、ファクトが共有するディメンションの有効な組み合わせを列挙するブリッジを、メトリクスビューの source で直接定義します。例えば、samples.tpchの出荷ファクトlineitem（粒度：注文明細）および供給ファクトpartsupp（粒度：部品とサプライヤー）の両方が、部品とサプライヤーのディメンションを共有します。

ブリッジにより、有効なディメンションの組み合わせが明確になるため、クエリー結果は予測可能な状態を維持できます。メトリクスビューは、各クエリに対して推論するのではなく、有効であると宣言された組み合わせのみを返します。ファンアウトや重複カウントをせずに、エンジンが共有ブリッジに対して各ファクトを独立して集計できるように、各ファクト結合にcardinality: one_to_manyを設定します。

ブリッジを構築するには、メトリクスビューsource内でそれをSQLクエリとして定義し、各ファクトテーブルを共有列でそれにjoinさせ、次に、共有ディメンション列にフィールドを、各ファクトにメジャーを宣言します。共有ディメンションのすべての組み合わせが有効な場合は、CROSS JOINを使用してください。

YAML

version: 1.1
source: SELECT * FROM samples.tpch.part CROSS JOIN samples.tpch.supplier
filter: s_suppkey IN (11315, 42920) AND p_partkey IN (30419, 80418)

joins:
  - name: lineitem
    source: samples.tpch.lineitem
    on: source.p_partkey = lineitem.l_partkey AND source.s_suppkey = lineitem.l_suppkey
    cardinality: one_to_many
  - name: partsupp
    source: samples.tpch.partsupp
    on: source.p_partkey = partsupp.ps_partkey AND source.s_suppkey = partsupp.ps_suppkey
    cardinality: one_to_many

fields:
  - name: part_name
    expr: p_name
  - name: part_brand
    expr: p_brand
  - name: part_type
    expr: p_type
  - name: part_size
    expr: p_size
  - name: manufacturer
    expr: p_mfgr
  - name: supplier_name
    expr: s_name

measures:
  - name: lineitem_count
    expr: COUNT(lineitem.*)
  - name: total_quantity_sold
    expr: SUM(lineitem.l_quantity)
  - name: gross_revenue
    expr: SUM(lineitem.l_extendedprice)
  - name: net_revenue
    expr: SUM(lineitem.l_extendedprice * (1 - lineitem.l_discount))
  - name: distinct_orders
    expr: COUNT(DISTINCT lineitem.l_orderkey)
  - name: available_quantity
    expr: SUM(partsupp.ps_availqty)
  - name: avg_supply_cost
    expr: AVG(partsupp.ps_supplycost)
  - name: total_supply_value
    expr: SUM(partsupp.ps_availqty * partsupp.ps_supplycost)

ファクトテーブル上のメジャーは、共有ディメンションの値がブリッジに含まれるレコードのみをカウントします。ブリッジに含まれない組み合わせは、結果には反映されません。

実際に発生する組み合わせのみが必要な場合は、各ファクトの異なるペアの source を UNION（または FULL OUTER JOIN）にスワップして、各ファクトが一意のメンバーを提供できるようにします。joins、fields、measuresは同じです：

YAML

source: |
  SELECT DISTINCT l_partkey AS p_partkey, l_suppkey AS s_suppkey FROM samples.tpch.lineitem
  UNION
  SELECT DISTINCT ps_partkey AS p_partkey, ps_suppkey AS s_suppkey FROM samples.tpch.partsupp

一対多結合の制約

• フィールドは一対多結合を参照できません: フィールドはソース行ごとに厳密に1つの値に解決する必要があります。一対多の列は、ソース行ごとに複数の値を持つことができるため、fieldsの定義で使用することはできません。このような列をフィールドとして使用するには、そのテーブルをソースとし、元のソースをmany_to_one結合として結合してください。
• 単一の集計は複数のソースにまたがることはできません：各集計関数は1つのソースから列を参照する必要があります。2つの集計結果間の算術演算は、count(orders.o_orderkey) / count(*)のように許可されていますが、単一の関数では2つのソースからの列を組み合わせることはできません。
• 結合サブツリーではカーディナリティを混在させることはできません ：一対多の結合の子孫はすべて一対多である必要があり、多対一の結合の子孫はすべて多対一である必要があります。トップレベルの兄弟要素のみがカーディナリティを混在させることができます。

次のステップ

• メトリクスビューのモデリング
• メトリクス ビューの高度なテクニック
• メトリクスビューYAML 構文リファレンス
• チュートリアル: 結合で完全なメトリクスビューを構築する