Arbeiten mit Joins in LookML

Mit Joins können Sie verschiedene Datenansichten miteinander verbinden und so Daten aus mehreren Datenansichten gleichzeitig untersuchen. Außerdem lässt sich so ermitteln, wie verschiedene Teile Ihrer Daten in Beziehung zueinander stehen.

Ihre Datenbank kann beispielsweise die Tabellen order_items, orders und users enthalten. Mithilfe von Joins können wir Daten aus all diesen Tabellen gleichzeitig analysieren. Auf dieser Seite werden Joins in LookML erläutert. Dies umfasst auch bestimmte Join-Parameter und Verbindungsmuster.

Joins beginnen mit einem Explore

Joins sind in der Modelldatei definiert, um die Beziehung zwischen einer Erkundung und einer Ansicht herzustellen. Joins verknüpfen eine oder mehrere Ansichten in einem einzelnen Explore, entweder direkt oder über eine andere verknüpfte Ansicht.

Sehen wir uns zwei Datenbanktabellen an: order_items und orders. Nachdem Sie Ansichten für beide Tabellen generiert haben, deklarieren Sie eine oder mehrere Tabellen unter dem Parameter explore in der Modelldatei:

explore: order_items { ... }

Wenn Sie eine Abfrage aus dem order_items-Tool ausführen, wird order_items in der FROM-Klausel des generierten SQL-Codes angezeigt:

SELECT ...
FROM order_items

Weitere Informationen findest du in unserer order_items-Erkundung. So können Sie beispielsweise Daten zu order hinzufügen, zu der order_item gehört:

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

Der obige LookML-Code bewirkt zwei Dinge. Erstens sehen Sie Felder aus orders und order_items in der UI:

Zweitens wird in LookML beschrieben, wie orders und order_items verknüpft werden. Dieser LookML-Code entspricht dem folgenden SQL-Code:

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

Diese LookML-Parameter werden in den nachfolgenden Abschnitten detaillierter beschrieben. Weitere Informationen zur Übersetzung dieses LookML in SQL finden Sie auf der joinReferenzseite für Parameter.

Tipp für das Google Chat-Team: Nutzer fragen am häufigsten nach dem Überprüfungsfehler, „Unbekanntes oder nicht zugängliches Feld“, der durch eine fehlende Zusammenführung verursacht werden kann. Weitere Informationen zu diesem Fehler finden Sie in diesem Hilfeartikel.

Join-Parameter

Zum Zusammenführen werden vier Hauptparameter verwendet: joins, join, type, relationship und sql_on.

1. Schritt: Das Explore starten

Erstellen Sie zuerst das order_items-Erkunden:

explore: order_items { ... }

Schritt 2: join

Wenn Sie eine Tabelle verbinden möchten, müssen Sie sie zuerst in einer Ansicht deklarieren. In diesem Beispiel ist orders eine vorhandene Ansicht in unserem Modell.

Verwenden Sie dann den Parameter join, um zu erklären, dass Sie die Ansicht orders mit order_items verknüpfen möchten:

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

Schritt 3: type

Überlegen Sie, welcher type des Joins ausgeführt werden soll. Looker unterstützt LEFT JOIN, INNER JOIN, FULL OUTER JOIN und CROSS JOIN. Diese entsprechen den type-Parameterwerten left_outer, inner, full_outer und cross.

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

Der Standardwert von type ist left_outer und in der Regel der beliebte Join-Typ.

Schritt 4: relationship

Definieren Sie einen Join relationship zwischen order_items und orders. Die korrekte Deklaration der relationship eines Joins ist wichtig, damit Looker genaue Messwerte berechnen kann. Die Beziehung wird von der Funktion order_items Entdecken und von der Ansicht orders definiert. Die möglichen Optionen sind one_to_one, many_to_one, one_to_many und many_to_many.

In diesem Beispiel kann es mehrere order_items für eine einzelne order geben. Die Beziehung von order_items zu orders ist many_to_one:

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

Wenn Sie relationship nicht in Ihren Join aufnehmen, wird von Looker standardmäßig many_to_one verwendet.

Weitere Tipps zum korrekten Definieren des relationship-Parameters für einen Join finden Sie im Hilfeartikel Parameter relationship richtig verstehen.

Schritt 5: sql_on

Deklarieren Sie, wie diese beiden Tabellen mit dem Parameter sql_on oder foreign_key verknüpft werden. Wir empfehlen in der Regel sql_on, weil damit alles möglich ist – foreign_key aber meistens einfacher zu verstehen ist.

sql_on entspricht der ON-Klausel in der generierten SQL-Datei für eine Abfrage. Mit diesem Parameter können wir deklarieren, welche Felder für den Join-Vorgang abgeglichen werden sollten:

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

Wir können auch komplexere Joins formulieren. Sie können z. B. nur an Bestellungen mit id größer als 1.000 teilnehmen:

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

Weitere Informationen zur Syntax von ${ ... } in diesen Beispielen finden Sie unter Substitutionsoperatoren.

6. Schritt: Testen

Testen Sie, ob dieser Join wie erwartet funktioniert. Rufen Sie dazu Erkunden auf. Sie sollten Felder sowohl aus order_items als auch aus orders sehen.

Weitere Informationen zum Testen von LookML-Änderungen finden Sie unter Modellentwicklung.

Durch eine andere Ansicht verbinden

Sie können eine Ansicht über eine andere Ansicht mit einem Explore verbinden. Im Beispiel oben haben Sie orders über das Feld order_id mit order_items verbunden. Es könnte auch sein, dass wir die Daten aus einer Datenansicht mit dem Namen users mit der order_items-Erkundung zusammenführen möchten, obwohl sie kein gemeinsames Feld haben. Das können Sie über orders tun.

Mit sql_on oder foreign_key können Sie users statt order_items orders beitreten. Legen Sie dazu das Feld von orders korrekt als orders.user_id fest.

Hier ein Beispiel mit sql_on:

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

Eine Ansicht mehrmals verbinden

Eine users-Datenansicht enthält Daten für Käufer und Verkäufer. Wenn Sie Daten aus dieser Ansicht in order_items zusammenführen möchten, aber separat für Käufer und Verkäufer, können Sie users zweimal mit unterschiedlichen Namen mithilfe des Parameters from zusammenführen.

Mit dem Parameter from können Sie die Ansicht festlegen, die in einem Join verwendet werden soll, und ihm einen eindeutigen Namen geben. Beispiel:

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} ;;
  }
}

In diesem Fall werden nur Käuferdaten als buyers zusammengeführt, während nur Verkäuferdaten als sellers zusammengeführt werden.

Hinweis: Der Name der Ansicht mit der Alias-ID buyers und sellers muss im Join enthalten sein.

Felder aus einem Join beschränken

Mit dem Parameter fields können Sie festlegen, welche Felder aus einem Join in ein exploratives Analysetool übertragen werden. Standardmäßig werden alle Felder aus einer Ansicht beim Verbinden hinzugefügt. Es ist jedoch denkbar, dass Sie nur einen Teil der Felder aufnehmen möchten.

Wenn beispielsweise orders mit order_items verbunden ist, sollten Sie nur die Felder shipping und tax über den Join nutzen:

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

Sie können auch auf eine Gruppe von Feldern wie [set_a*] verweisen. Jeder Satz wird in einer Ansicht mithilfe des Parameters set definiert. Angenommen, Sie haben den folgenden Satz in der Ansicht orders definiert:

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

Wenn Sie orders mit order_items verknüpfen, können Sie festlegen, dass nur diese drei Felder übernommen werden:

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

Symmetrische Summen

Looker verwendet eine Funktion namens „symmetrische Aggregierungen“, um Aggregationen wie Summen und Durchschnitte korrekt zu berechnen, selbst wenn Joins zu einem Fanout führen. Symmetrische Aggregats werden ausführlicher im Artikel Eine einfache Erläuterung von symmetrischen Aggregattypen in der Hilfe beschrieben. Das von ihnen gelöste Fanout-Problem wird im Artikel SQL-Fanouts beschrieben.

Primärschlüssel erforderlich

Damit Messwerte (Aggregationen) über Joins erfolgen, müssen Sie in allen am Join beteiligten Ansichten Primärschlüssel definieren.

Fügen Sie dazu in jeder Ansicht der Definition des Primärschlüssels den Parameter primary_key hinzu:

dimension: id {
  type: number
  primary_key: yes
}

Für die richtige Handhabung von verbundenen Messwerten setzt Looker voraus, dass Sie einen Primärschlüssel angeben, wenn es sich bei den Werten um absolut eindeutige Nicht-NULL-Werte handelt. Wenn Ihre Daten keinen Primärschlüssel enthalten, sollten Sie überlegen, ob die Verkettung mehrerer Felder zu einem Primärschlüssel mit vollständig eindeutigen Nicht-NULL-Werten führen würde. Wenn Ihr Primärschlüssel nicht eindeutig ist oder NULL-Werte enthält und Ihre Abfrage Daten enthält, die diese Probleme aufdecken, gibt Looker einen Fehler zurück, wie in diesem Hilfeartikel beschrieben.

Unterstützte SQL-Dialekte

Damit Looker symmetrische Summen in Ihrem Looker-Projekt unterstützen kann, müssen diese auch von Ihrem Datenbankdialekt unterstützt werden. In der folgenden Tabelle sehen Sie, welche Dialekte in der neuesten Version von Looker symmetrische Aggregationen unterstützen:

Sollte Ihr Dialekt symmetrische Summen nicht unterstützen, müssen Sie bei der Ausführung von Joins in Looker besonders aufmerksam sein, da einige Arten von Joins zu fehlerhaften Aggregationen (wie Summen und Durchschnittswerte) führen können. Dieses Problem und die Behelfslösungen werden ausführlich im Hilfeartikel Probleme mit SQL-Fanouts beschrieben.

Weitere Informationen zu Joins

Weitere Informationen zu Join-Parametern in LookML finden Sie in der Join-Referenz.