Arbeiten mit Joins in LookML

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

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 verknüpfen eine oder mehrere Ansichten in einem einzelnen Explore, entweder direkt oder über eine andere verknüpfte Ansicht.

Betrachten Sie zwei Datenbanktabellen: 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 explore-Parameter 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 order_items-Explore zusätzliche Informationen hinzufügen. Mit der folgenden Beispiel-LookML können Sie beispielsweise die Daten der Ansicht orders mit der explorativen Datenanalyse order_items zusammenführen:

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

Der oben gezeigte LookML-Code bewirkt zwei Dinge. Im Feld-Chooser für explorative Datenanalysen sehen Sie zuerst Felder aus orders und order_items:

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 vom Chatteam:Wir werden am häufigsten für den Validierungsfehler „Unbekanntes oder unzugängliches Feld“ um Unterstützung gebeten. Dieser kann durch einen fehlenden Join verursacht werden. Weitere Informationen finden Sie unter Fehler: Unbekanntes oder nicht zugängliches Feld.

Join-Parameter

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

1. Schritt: Das Explore starten

Erstellen Sie zuerst die explorative Datenanalyse order_items:

explore: order_items { ... }

Schritt 2: join

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

Verwenden Sie dann den Parameter join, um anzugeben, dass Sie die Ansicht orders mit der explorativen Datenanalyse order_items verknüpfen 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 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. 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 Auftragselemente 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 keinen relationship-Parameter in die Verbindung aufnehmen, verwendet Looker standardmäßig many_to_one.

Weitere Tipps zur korrekten Definition des relationship-Parameters für eine Zusammenführung finden Sie unter relationship-Parameter richtig festlegen.

Schritt 5: sql_on

Deklarieren Sie, wie die Tabelle order_items und die Tabelle orders mit dem Parameter sql_on oder dem Parameter foreign_key zusammengeführt werden.

Tipp: Wir empfehlen in der Regel, den Parameter sql_on zu verwenden, da er alle Funktionen des Parameters foreign_key bietet. Der Parameter sql_on ist jedoch in der Regel leichter verständlich.

Der Parameter sql_on entspricht der ON-Klausel in der generierten SQL-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

Ob dieser Join wie erwartet funktioniert, können Sie testen, indem Sie das Explore Bestellpositionen aufrufen. Sie sollten Felder sowohl von order_items als auch von 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 über das Feld order_id mit order_items zusammengeführt. Möglicherweise sollen zudem die Daten aus der Ansicht users mit dem Explore order_items verbunden werden, auch wenn kein gemeinsames Feld vorhanden ist. Dazu wird ein Join durch die Ansicht orders erstellt.

Verwenden Sie den Parameter sql_on oder den Parameter foreign_key, um die users-Ansicht mit der orders-Ansicht zu verknüpfen, anstatt mit der order_items-Explore-Ansicht. Dazu muss das Scoping für das Feld aus orders ordnungsgemäß als orders.user_id erfolgen.

Hier ein Beispiel für die Verwendung des Parameters 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-Ansicht enthält Daten sowohl für Käufer als auch für Verkäufer. Wenn Sie Daten aus dieser Ansicht mit order_items verknüpfen möchten, aber für Käufer und Verkäufer getrennt, können Sie users zweimal mit unterschiedlichen Namen mithilfe des Parameters from verknüpfen.

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 zusammengeführt, während nur Verkäuferdaten als sellers zusammengeführt werden.

Hinweis: Die Ansicht users muss jetzt im Join mit den Aliasnamen buyers und sellers referenziert werden.

Felder aus einem Join beschränken

Mit dem Parameter fields können Sie angeben, welche Felder aus einem Join in ein Explore übernommen 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 orders beispielsweise mit order_items zusammengeführt wird, möchten Sie möglicherweise nur die Felder shipping und tax in die Zusammenführung einbeziehen:

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 einen Feldsatz wie [set_a*] verweisen. Innerhalb einer Ansicht wird jeder Satz mit dem Parameter set definiert. Angenommen, Sie haben in der orders-Ansicht den folgenden Satz definiert:

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

Anschließend können wir wie folgt festlegen, dass nur diese drei Felder übernommen werden, wenn orders mit order_items verbunden wird:

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 Durchschnittswerte) auch dann richtig zu berechnen, wenn Joins zu einem Fanout führen. Symmetrische Summen werden unter Symmetrische Summen ausführlicher beschrieben. Das Fan-out-Problem, das durch symmetrische Summen gelöst wird, wird im Communitybeitrag Das Problem von SQL-Fan-outs 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
}

Hinweis :Für die korrekte Verarbeitung verbundener Messwerte muss in Looker ein Primärschlüssel angegeben werden, dessen Werte absolut eindeutig und keine NULL-Werte sind. Wenn Ihre Daten keinen Primärschlüssel enthalten, überlegen Sie, ob die Konkatenierung mehrerer Felder zu einem Primärschlüssel mit absolut 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 aufzeigen, gibt Looker einen Fehler zurück, wie auf der Seite mit Best Practices unter Fehler: Nicht eindeutiger Wert/Primärschlüssel (oder sql_distinct_key), Wertüberlauf oder Kollision bei der Berechnung 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. In der folgenden Tabelle ist zu sehen, welche Dialekte symmetrische Summen in der aktuellen Looker-Version unterstützen:

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

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 Problemumgehungen werden im Communitybeitrag The problem of SQL fanouts (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.