Wenn Sie Looker über einen SQL-Hintergrund aufrufen, möchten Sie wahrscheinlich wissen, wie Looker SQL generiert. Im Grunde ist Looker ein Tool, mit dem SQL-Abfragen generiert und über eine Datenbankverbindung gesendet werden. Looker formuliert SQL-Abfragen anhand eines LookML-Projekts, das die Beziehung zwischen Tabellen und Spalten in der Datenbank beschreibt. Wenn Sie wissen, wie Looker Abfragen generiert, können Sie besser verstehen, wie Ihr LookML-Code in effiziente SQL-Abfragen umgewandelt wird.
Jeder LookML-Parameter steuert einen Teil der Art und Weise, wie Looker SQL generiert, indem er die Struktur, den Inhalt oder das Verhalten der Abfrage ändert. Auf dieser Seite werden die Prinzipien beschrieben, wie Looker SQL generiert. Dabei werden jedoch nicht alle LookML-Elemente im Detail behandelt. Ausführliche Informationen finden Sie auf der Dokumentationsseite zu LookML.
Abfrage ansehen
In einem gespeicherten Look oder einer explorativen Datenanalyse können Sie auf dem Tab SQL im Abschnitt Daten sehen, was Looker an die Datenbank sendet, um die Daten abzurufen. Über die Links unten können Sie die Abfrage in SQL Runner aufrufen oder den Datenbankplan für die Abfrage aufrufen. Weitere Informationen zum SQL Runner finden Sie auf der Seite Grundlagen des SQL Runners. Weitere Informationen zum Optimieren einer Abfrage mit dem SQL Runner finden Sie im Hilfeartikel SQL mit EXPLAIN optimieren.
Kanonische Form einer Looker-Abfrage
SQL-Abfragen von Looker haben immer das folgende Format.
SELECT
<dimension>, <dimension>, ...
<measure>, <measure>, ...
FROM <explore>
LEFT JOIN <view> ON ...
LEFT JOIN <view> ON ...
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND ...
GROUP BY <dimension>, <dimension>, <dimension>, ...
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
ORDER BY <dimension> | <measure>
LIMIT <limit>
Im LookML-Projekt werden alle Dimensionen, Messwerte, explorativen Datenanalysen und Datenansichten definiert, auf die in der Formel oben verwiesen wird. Filterausdrücke werden in Looker vom Nutzer angegeben, um Ad-hoc-Abfragen zu gestalten. Filterausdrücke können auch direkt in LookML deklariert werden, um sie auf alle Abfragen anzuwenden.
Grundlegende Komponenten einer Looker-Abfrage
Alle Looker-Abfragen werden durch diese grundlegenden Parameter dargestellt, die auf ein LookML-Projekt angewendet werden, wie in der Formel oben dargestellt.
Looker verwendet die folgenden Parameter, um eine vollständige SQL-Abfrage zu generieren:
model
: der Name des Ziel-ML-Modells, auf das ausgerichtet werden soll. Es gibt die Zieldatenbank an.explore
: Der Name der abzufragenden Abfrage, mit der dieFROM
-Klausel von SQL gefüllt wird- Felder: die in die Abfrage aufzunehmenden Parameter
dimension
undmeasure
, die in der SQL-KlauselSELECT
enthalten sind filter
: Looker-Filterausdrücke, die auf null oder mehr Felder angewendet werden, die die SQL-KlauselnWHERE
undHAVING
ausfüllen- Sortierreihenfolge: das Feld, nach dem sortiert werden soll, und die Sortierreihenfolge, mit der die SQL-Klausel
ORDER BY
ausgefüllt wird
Diese Parameter sind genau die Elemente, die ein Nutzer beim Erstellen einer Abfrage auf der Looker-Seite Erkunden angibt. Dieselben Elemente werden in allen Modi der Ausführung von Abfragen mit Looker angezeigt: im generierten SQL-Code, in der URL, die die Abfrage darstellt, in der Looker API usw.
Was ist mit den Ansichten, die in den LEFT JOIN
-Klauseln angegeben sind? JOIN
-Klauseln werden entsprechend der Struktur des LookML-Modells ausgefüllt, das angibt, wie Datenansichten mit dem Tab „Entdecken“ verknüpft werden. Beim Erstellen von SQL-Abfragen schließt Looker nur bei Bedarf JOIN
-Klauseln ein. Wenn Nutzer eine Abfrage in Looker erstellen, müssen sie nicht angeben, wie Tabellen zusammengefügt werden, da diese Informationen im Modell codiert sind – einer der größten Vorteile von Looker für Geschäftskunden.
Eine Beispielabfrage und das resultierende SQL
Lassen Sie uns eine Abfrage in Looker erstellen, um zu zeigen, wie die Abfrage gemäß dem obigen Muster generiert wird. Nehmen wir als Beispiel einen E-Commerce-Shop mit Tabellen, um Nutzer und Bestellungen zu verfolgen. Die Felder und Tabellenbeziehungen sind unten dargestellt.
Suchen Sie nach der Anzahl der Aufträge (Anzahl der Aufträge), gruppiert nach Bundesstaat (Status NUTZER) und gefiltert nach Erstellungsdatum des Auftrags (Erstellungsdatum der Bestellung).
Im Folgenden finden Sie das Abfrageergebnis auf der Seite „Looker Explore“.
Wenn Sie auf den Tab SQL klicken, wird der von Looker generierte und ausgeführte SQL angezeigt.
Beachten Sie die Ähnlichkeit mit der oben genannten kanonischen Formel. SQL von Looker weist einige Merkmale von maschinengeneriertem Code auf (z.B. COALESCE(users.state,'') AS "_g1"
), entspricht jedoch immer der Formel.
SELECT
<dimension>,<dimension>,...
<measure>,<measure>,...
FROM <explore>
LEFT JOIN <view> ON ...
LEFT JOIN <view> ON ...
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND ...
GROUP BY <dimension>,<dimension>,<dimension>,...
ORDER BY <dimension> | <measure>
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
LIMIT <limit>
Experimentieren Sie mit weiteren Abfragen in Looker, um zu beweisen, dass die Abfragestruktur immer gleich ist.
Unbearbeitetes SQL im SQL-Runner von Looker ausführen
Looker bietet eine Funktion namens SQL Runner, mit der Sie beliebige SQL-Verbindungen für die Datenbankverbindungen ausführen können, die Sie in Looker eingerichtet haben.
Da jede von Looker generierte Abfrage zu einem vollständigen, funktionalen SQL-Befehl führt, können Sie den SQL-Runner verwenden, um die Abfrage zu untersuchen oder mit ihr zu spielen.
In SQL Runner ausgeführte Roh-SQL-Abfragen erzeugen denselben Ergebnissatz.
Wenn der SQL-Code Fehler enthält, markiert der SQL Runner den Speicherort des ersten Fehlers im SQL-Befehl und fügt die Fehlerposition in die Fehlermeldung ein.
Abfragekomponenten in der URL untersuchen
Nachdem Sie eine Abfrage in Looker ausgeführt haben, können Sie die erweiterte Freigabe-URL prüfen, um die grundlegenden Komponenten einer Looker-Abfrage zu sehen. Wähle dazu im Zahnrad-Menü „Erkunden“ die Option Teilen aus:
Wenn Sie bei einem Lookbeginnen, klicken Sie auf den LinkVon hier entdecken, um die Abfrage in einem Explore zu öffnen.
Daraufhin wird das Fenster URLs teilen mit der maximierten URL angezeigt:
Die obige Abfrage erzeugt beispielsweise die folgende erweiterte Freigabe-URL:
https://docsexamples.dev.looker.com/explore/e_thelook/events?fields=users.state,users.count
&f[users.created_year]=2020&sorts=users.count+desc&limit=500
Die URL enthält genügend Informationen, um die Abfrage neu zu erstellen:
Modell | e_thelook |
Entdecken | events |
Felder, die abgefragt und angezeigt werden sollen | fields=users.state,users.count |
Feld und Reihenfolge sortieren | sorts=users.count+desc |
Felder und Werte filtern | f[users.created_year]=2020 |
So verknüpft Looker JOINs
In der SQL-Abfrage oben ist zu sehen, dass die Funktion orders
Entdecken in der Hauptklausel FROM
und die zusammengeführten Ansichten in den LEFT JOIN
-Klauseln enthalten sind. Looker-Joins können auf viele verschiedene Arten geschrieben werden, was auf der Seite Mit Joins in LookML arbeiten ausführlicher erläutert wird.
SQL-Blöcke geben benutzerdefinierte SQL-Klauseln an
Nicht alle Elemente einer Looker-Abfrage sind maschinengeneriert. Das Datenmodell muss zu bestimmten Zeiten spezifische Details angeben, damit Looker auf die zugrunde liegenden Tabellen zugreifen und abgeleitete Werte berechnen kann. In LookML sind SQL-Blöcke Snippets von SQL-Code, die vom Datenmodellierer bereitgestellt werden, mit dem Looker vollständige SQL-Ausdrücke synthetisiert.
Der gängigste SQL-Blockparameter ist sql
, der in Definitionen für Dimensionen und Messwerte verwendet wird. Der Parameter sql
gibt eine SQL-Klausel an, die auf eine zugrunde liegende Spalte verweist oder eine Aggregatfunktion ausführt. Im Allgemeinen erwarten alle LookML-Parameter, die mit sql_
beginnen, einen SQL-Ausdruck in irgendeiner Form. Beispiel: sql_always_where
, sql_on
und sql_table_name
. Die LookML-Referenz enthält Details zu jedem Parameter.
Beispiele für SQL-Blöcke für Dimensionen und Messwerte
Im Folgenden finden Sie einige Beispiele für SQL-Blöcke für Dimensionen und Messwerte. Mit dem LookML-Ersetzungsoperator ($) erscheinen diese sql
-Deklarationen trügerisch im Gegensatz zu SQL. Nach dem Ersetzen ist der resultierende String jedoch reiner SQL-Code, den Looker in die SELECT
-Klausel der Abfrage einfügt.
dimension: id {
primary_key: yes
sql: ${TABLE}.id ;; # Specify the primary key, id
}
measure: average_cost {
type: average
value_format: "0.00"
sql: ${cost} ;; # Specify the field that you want to average
# The field 'cost' is declared elsewhere
}
dimension: name {
sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
type: number
sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}
Wie in den letzten beiden Dimensionen oben gezeigt, können SQL-Blöcke Funktionen verwenden, die von der zugrunde liegenden Datenbank unterstützt werden (z. B. die MySQL-Funktionen CONCAT
und DATEDIFF
in diesem Fall). Der in SQL-Blöcken verwendete Code muss mit dem von der Datenbank verwendeten SQL-Dialekt übereinstimmen.
Beispiel für einen SQL-Block für abgeleitete Tabellen
Abgeleitete Tabellen verwenden außerdem einen SQL-Block, um die Abfrage anzugeben, von der die Tabelle abgeleitet wird. Ein Beispiel ist unten aufgeführt:
view: user_order_facts {
derived_table: {
sql:
SELECT
user_id
, COUNT(*) as lifetime_orders
FROM orders
GROUP BY 1 ;;
}
# later, dimension declarations reference the derived column(s)…
dimension: lifetime_orders {
type: number
}
}
Beispiel-SQL-Block zum Filtern eines Explore
Mit den LookML-Parametern sql_always_where
und sql_always_having
können Sie die für eine Abfrage verfügbaren Daten einschränken. Fügen Sie dazu einen SQL-Block in die SQL-WHERE- oder HAVING-Klauseln ein. In diesem Beispiel wird mit dem LookML-Ersatzoperator ${view_name.SQL_TABLE_NAME}
auf eine abgeleitete Tabelle verwiesen:
explore: trips {
view_label: "Long Trips"
# This will ensure that we only see trips that are longer than average!
sql_always_where: ${trips.trip_duration}>=(SELECT tripduration FROM ${average_trip_duration.SQL_TABLE_NAME});;
}