Arbeiten mit Joins in LookML

Mit Joins können Sie verschiedene Datenansichten verknüpfen, um Daten aus mehreren Datenansichten gleichzeitig zu analysieren und zu sehen, wie verschiedene Teile Ihrer Daten zueinander in Beziehung stehen.

Ihre Datenbank könnte 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 werden in der Modelldatei definiert, um die Beziehung zwischen einem Explore und einer Ansicht herzustellen. Joins verbinden eine oder mehrere Ansichten mit einem einzelnen Explore, entweder direkt oder über eine andere verbundene Ansicht.

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

explore: order_items { ... }

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

SELECT ...
FROM order_items

Sie können zusätzliche Informationen mit dem order_items-Explore verbinden. Sie können beispielsweise das folgende LookML-Beispiel verwenden, um die Ansicht orders mit dem Explore order_items zu verknüpfen:

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

Mit dem dargestellten LookML-Code wurden zuvor zwei Dinge erreicht. Erstens können Sie im Field Picker für Explore die Felder von orders und order_items sehen:

Zweitens beschreibt der LookML-Code, wie orders und order_items miteinander 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.

Tipp für das Chat-Team:Nutzer fragen am häufigsten nach dem Validierungsfehler „Unbekanntes oder nicht zugängliches Feld“, der durch einen fehlenden Join verursacht werden kann. Weitere Informationen finden Sie unter Fehler: Unbekanntes oder nicht zugängliches Feld.

Join-Parameter

Für den Join 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 { ... }

Schritt 2: join

Um eine Tabelle zu verknüpfen, müssen Sie die Tabelle 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 { ... }
}

Schritt 3: 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 type-Parameterwerten von left_outer, inner, full_outer und cross.

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

Der Standardwert von type ist left_outer.

Schritt 4: relationship

Definieren Sie eine Join-Beziehung zwischen dem order_items-Explore und der orders-Ansicht. Damit Looker genaue Messwerte berechnen kann, ist es wichtig, die Beziehung eines Joins richtig zu deklarieren. Die Beziehung wird vom order_items-Explore zu der orders-Ansicht 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 Bestellartikel für eine einzelne Bestellung geben. Die Beziehung zwischen dem Explore order_items und der Ansicht orders ist many_to_one:

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

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

Weitere Tipps zur korrekten Definition des relationship-Parameters für einen Join finden Sie unter Richtigen relationship-Parameters.

Schritt 5: sql_on

Geben Sie entweder mit dem Parameter sql_on oder dem Parameter foreign_key an, wie die Tabelle order_items und die Tabelle orders zusammengeführt werden sollen.

Tipp :Wir empfehlen in der Regel, den Parameter sql_on zu verwenden, da er alles kann, was der Parameter foreign_key kann. Der Parameter sql_on ist aber in der Regel leichter zu verstehen.

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

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. Beispielsweise können Sie nur Aufträge mit einer id größer als 1.000 zusammenführen:

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 Order Items (Bestellelemente) aufrufen. Sie sollten Felder von order_items und orders sehen.

Weitere Informationen zum Testen von LookML-Änderungen in einem Explore finden Sie unter LookML bearbeiten und validieren.

Durch eine andere Ansicht verbinden

Sie können eine Ansicht über eine andere Ansicht mit einem Explore verbinden. Im Beispiel mit Join-Parametern haben Sie orders über das Feld order_id mit order_items verknüpft. Vielleicht möchten wir auch die Daten aus einer Ansicht namens users mit dem order_items-Explore zusammenführen, auch wenn sie kein gemeinsames Feld haben. Dazu können Sie sie über die orders-Ansicht zusammenführen.

Verwenden Sie den Parameter sql_on oder den Parameter foreign_key, um die Ansicht users mit der Ansicht orders und nicht mit dem Explore order_items zu verknüpfen. Legen Sie dazu das Feld aus orders korrekt als orders.user_id fest.

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 users-Datenansicht enthält Daten für Käufer und Verkäufer. Wenn Sie Daten aus dieser Datenansicht mit order_items zusammenführen möchten, für Käufer und Verkäufer jedoch getrennt, können Sie users mithilfe des Parameters from zweimal mit unterschiedlichen Namen zusammenführen.

Mit dem Parameter from können Sie angeben, welche Ansicht in einem Join verwendet werden soll, und dem Join 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 Daten des Käufers mit buyers zusammengeführt, während nur Daten des Verkäufers mit sellers zusammengeführt werden.

Hinweis: Auf die Ansicht users muss jetzt im Join mit ihren Aliasnamen buyers und sellers verwiesen werden.

Felder aus einem Join beschränken

Mit dem Parameter fields können Sie angeben, welche Felder aus einem Join in ein Explore ü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 verknüpft wird, können Sie nur die Felder shipping und tax über den Join importieren:

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 innerhalb einer Ansicht mithilfe des Parameters set definiert. Angenommen, Sie haben in der Ansicht orders Folgendes definiert:

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

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

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 Summen“, um Aggregationen (wie Summen und Durchschnitte) korrekt zu berechnen, auch wenn Joins zu einem Fanout führen. Symmetrische Aggregatfunktionen werden unter symmetrische Aggregatfunktionen ausführlicher beschrieben. Das Fanout-Problem, das mit symmetrischen aggregierten Daten gelöst wird, wird im Communitybeitrag 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.

Fügen Sie dazu den Parameter primary_key in die Definition des Primärschlüsselfelds in jeder Ansicht ein:

dimension: id {
  type: number
  primary_key: yes
}

Hinweis: Damit verbundene Messwerte korrekt verarbeitet werden können, müssen Sie für Looker einen Primärschlüssel angeben, bei dem die Werte vollständig eindeutig sind und keine NULL-Werte sind. Wenn Ihre Daten keinen Primärschlüssel enthalten, überlegen Sie, ob die Verkettung mehrerer Felder zu einem Primärschlüssel mit völlig eindeutigen Werten führen würde, die nicht NULL sind. Wenn Ihr Primärschlüssel nicht eindeutig ist oder NULL-Werte enthält und Ihre Abfrage Daten enthält, die diese Probleme offenlegen, gibt Looker einen Fehler zurück, wie auf der Best Practices-Seite Fehler: Nicht eindeutiger Wert/Primärschlüssel (oder sql_distinct_key), Wertüberlauf oder Kollision beim Berechnen der Summe 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. Die folgende Tabelle zeigt, welche Dialekte symmetrische Summen in der neuesten Version von Looker unterstützen:

Dialekt	Unterstützt?
Actian Lawine	Yes
Amazon Athena	Yes
Amazon Aurora MySQL	Yes
Amazon Redshift	Yes
Druid	Nein
Apache Druid 0.13+	Nein
Apache Druid 0.18+	Nein
Apache Hive 2.3 und höher	Nein
Apache Hive 3.1.2 und höher	Nein
Apache Spark 3 und höher	Yes
ClickHouse	Nein
Cloudera Impala 3.1 und höher	Yes
Cloudera Impala 3.1+ mit nativem Treiber	Yes
Cloudera Impala mit nativem Treiber	Nein
DataVirtuality	Yes
Databricks	Yes
Denodo 7	Yes
Denodo 8	Yes
Dremio	Nein
Dremio 11+	Yes
Exasol	Yes
Firebolt	Yes
Google BigQuery Legacy-SQL	Yes
Google BigQuery-Standard-SQL	Yes
Google Cloud PostgreSQL	Yes
Google Cloud SQL	Yes
Google Spanner	Yes
Greenplum	Yes
HyperSQL	Nein
IBM Netezza	Yes
MariaDB	Yes
Microsoft Azure PostgreSQL	Yes
Microsoft Azure SQL-Datenbank	Yes
Microsoft Azure Synapse-Analyse	Yes
Microsoft SQL Server 2008 oder höher	Yes
Microsoft SQL Server 2012 und höher	Yes
Microsoft SQL Server 2016	Yes
Microsoft SQL Server 2017 und höher	Yes
MongoBI	Nein
MySQL	Yes
MySQL 8.0.12 und höher	Yes
Oracle	Yes
Oracle ADWC	Yes
PostgreSQL 9.5+	Yes
PostgreSQL vor 9.5	Yes
PrestoDB	Yes
PrestoSQL	Yes
SAP HANA 2 und höher	Yes
SingleStore	Yes
SingleStore 7 und höher	Yes
Snowflake	Yes
Teradata	Yes
Trino	Yes
Vektor	Yes
Vertica	Yes

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 entsprechenden Behelfslösungen werden im Communitybeitrag Das Problem von SQL-Fanouts ausführlich beschrieben.

Weitere Informationen zu Joins

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