Arbeiten mit Joins in LookML

Mit Joins können Sie verschiedene Ansichten verbinden, um Daten aus mehreren Ansichten gleichzeitig zu analysieren und zu sehen, wie verschiedene Teile Ihrer Daten miteinander zusammenhängen.

Ihre Datenbank kann beispielsweise die Tabellen order_items, orders und users enthalten. Mithilfe von Joins können Sie 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 werden in der Modelldatei definiert, um die Beziehung zwischen einem Explore 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 Sie sich zwei Datenbanktabellen an: order_items und orders. Nachdem Sie für diese beiden Tabellen Ansichten erstellt haben, können Sie eine davon oder mehrere in der Modelldatei mit dem Parameter explore deklarieren:

explore: order_items { ... }

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

SELECT ...
FROM order_items

Sie können dem Explore order_items zusätzliche Informationen hinzufügen. Mit dem folgenden LookML-Beispiel können Sie beispielsweise die Ansicht orders mit dem Explore order_items verbinden:

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

Das zuvor gezeigte LookML erreicht zwei Dinge. Erst können Sie Felder aus orders und order_items in der Feldauswahl des Explores sehen:

Zweitens wird im LookML beschrieben, wie orders und order_items miteinander verbunden 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.

Join-Parameter

Für Joins werden vier Hauptparameter verwendet: join, type, relationship und sql_on.

1. Schritt: Das Explore starten

Erstellen Sie zuerst das Explore order_items:

explore: order_items { ... }

2. Schritt: join

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

Verwenden Sie dann den Parameter join, um zu deklarieren, dass Sie die Ansicht orders mit dem Explore order_items verbinden möchten:

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

3. Schritt: type

Überlegen Sie, welche Art von Join ausgeführt werden soll. Looker unterstützt LEFT JOIN, INNER JOIN, FULL OUTER JOIN und CROSS JOIN. Diese entsprechen den Werten des Parameters type: left_outer, inner, full_outer und cross.

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

Der Standardwert von type ist left_outer.

4. Schritt: relationship

Definieren Sie eine Join-Beziehung zwischen dem Explore order_items und der Ansicht orders. Die ordnungsgemäße Deklaration der Beziehung eines Joins ist für die Berechnung korrekter Messwerte durch Looker erforderlich. Die Beziehung wird vom Explore order_items zur 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 viele Bestellpositionen für eine einzelne Bestellung geben. Die Beziehung vom Explore order_items zur Ansicht orders ist many_to_one:

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

Wenn Sie keinen Parameter relationship in Ihren Join einfügen, verwendet Looker standardmäßig many_to_one.

Weitere Tipps zum korrekten Definieren des Parameters relationship für einen Join finden Sie unter Parameter relationship richtig definieren.

5. Schritt: sql_on

Deklarieren Sie, wie die order_items Tabelle und die orders Tabelle mit dem sql_on Parameter oder dem foreign_key Parameter verbunden werden sollen.

Der Parameter sql_on entspricht der Klausel ON im generierten SQL-Code für eine Abfrage. Mit diesem Parameter können Sie 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} ;;
  }
}

Wir können auch komplexere Joins formulieren. Beispiel: Sie möchten nur Aufträge verbinden, deren id größer als 1.000 ist:

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

Weitere Informationen zur Syntax ${ ... } in diesen Beispielen finden Sie in der Dokumentation zu Substitutionsoperatoren.

6. Schritt: Testen

Testen Sie, ob dieser Join wie erwartet funktioniert, indem Sie das Explore Bestellpositionen aufrufen. Sie sollten Felder aus order_items und orders sehen.

Weitere Informationen zum Testen von LookML-Änderungen in einem Explore finden Sie unter Felder im Explore testen.

Durch eine andere Ansicht verbinden

Sie können eine Ansicht über eine andere Ansicht mit einem Explore verbinden. Im Beispiel für Join-Parameter haben Sie orders mit order_items über das Feld order_id verbunden. Möglicherweise sollen zudem die Daten aus der Ansicht users mit dem Explore order_items verbunden werden, auch wenn kein gemeinsames Feld vorhanden ist. Dies kann durch eine Verbindung über die Ansicht orders erfolgen.

Verwenden Sie den sql_on Parameter oder den foreign_key Parameter, um die users Ansicht mit der orders Ansicht zu verbinden, anstatt mit dem order_items Explore. Dazu müssen Sie den Bereich des Felds aus orders korrekt als orders.user_id festlegen.

Hier ein Beispiel mit dem Parameter 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} ;;
  }
}

Eine Ansicht mehrmals verbinden

Eine Ansicht users enthält Daten für Käufer und Verkäufer. Wenn Sie Daten aus dieser Ansicht in order_items einfügen möchten, aber getrennt für Käufer und Verkäufer, können Sie users mit dem Parameter from zweimal mit unterschiedlichen Namen verbinden.

Mit dem Parameter from können Sie festlegen, welche Ansicht bei einem Join verwendet werden soll. Gleichzeitig erhält der Join einen eindeutigen Namen. 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 und nur Verkäuferdaten als sellers verbunden.

Hinweis: In der Ansicht users muss jetzt in der Join-Definition auf die Aliasnamen buyers und sellers verwiesen werden.

Felder aus einem Join beschränken

Mit dem fields Parameter können Sie angeben, welche Felder aus einem Join in ein Explore übernommen werden sollen. 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 wird, möchten Sie möglicherweise nur die Felder shipping und tax über den Join übernehmen:

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 verweisen, z. B. [set_a*]. Jede Gruppe wird in einer Ansicht mit dem set Parameter definiert. Angenommen, Sie haben die folgende Gruppe in der Ansicht orders definiert:

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

Sie können festlegen, dass nur diese drei Felder übernommen werden, wenn Sie orders mit order_items verbinden:

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

Symmetrische Aggregate

Looker verwendet eine Funktion namens „symmetrische Aggregate“, um Aggregationen (wie Summen und Durchschnittswerte) auch dann richtig zu berechnen, wenn Joins zu einem Fanout führen. Symmetrische Aggregate werden unter Symmetrische Aggregate ausführlicher beschrieben. Das Fanout-Problem, das durch symmetrische Summen gelöst wird, wird im Community-Beitrag Das Problem von SQL-Fanouts erläutert.

Primärschlüssel erforderlich

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

Dazu fügen Sie in jeder Ansicht den Parameter primary_key zur Primärschlüssel-Felddefinition hinzu:

dimension: id {
  type: number
  primary_key: yes
}

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 ist zu sehen, welche Dialekte symmetrische Aggregate in der aktuellen Looker-Version unterstützen:

Sollte Ihr Dialekt symmetrische Aggregate 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 sowie die entsprechenden Behelfslösungen werden ausführlich im Community-Beitrag Das Problem von SQL-Fanouts beschrieben.

Weitere Informationen zu Joins

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