Wenn Sie aus einem SQL-Hintergrund zu Looker kommen, sind Sie wahrscheinlich neugierig, wie Looker SQL generiert. Im Grunde ist Looker ein Tool, das SQL-Abfragen generiert und an eine Datenbankverbindung sendet. Looker formuliert SQL-Abfragen auf der Grundlage eines LookML-Projekts, das die Beziehung zwischen Tabellen und Spalten in der Datenbank beschreibt. Wenn Sie wissen, wie Looker Abfragen generiert, werden Sie auch besser verstehen, wie Ihr LookML-Code in effiziente SQL-Abfragen umgewandelt wird.
Jeder LookML-Parameter steuert einen Aspekt, wie Looker SQL generiert, indem er die Struktur, den Inhalt oder das Verhalten der Abfrage ändert. Auf dieser Seite wird beschrieben, wie SQL in Looker generiert wird. Es werden jedoch nicht alle LookML-Elemente im Detail behandelt. Die LookML-Kurzanleitung ist ein guter Ausgangspunkt, um Informationen zu LookML-Parametern zu erhalten.
Abfrage aufrufen
In einem gespeicherten Look oder einem Explore können Sie im Bereich Daten auf dem Tab SQL sehen, was Looker an die Datenbank sendet, um die Daten abzurufen. Sie können auch die Links In SQL Runner öffnen und In SQL Runner erklären unten auf dem Tab SQL verwenden, um Ihre Abfrage in SQL Runner anzuzeigen oder den Erläuterungsplan der Datenbank für die Abfrage anzuzeigen.
Weitere Informationen zum SQL Runner finden Sie auf der Dokumentationsseite Grundlagen des SQL Runners. Weitere Informationen zum Optimieren einer Abfrage mit SQL Runner finden Sie im Communitybeitrag How to Optimize SQL with EXPLAIN.
Kanonische Form einer Looker-Abfrage
Die SQL-Abfragen von Looker haben immer die folgende Form.
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>
Das LookML-Projekt definiert alle Dimensionen, Messwerte, Explores und Ansichten, auf die in der SQL-Abfrage verwiesen wird. Filterausdrücke werden in Looker vom Benutzer angegeben, um Ad-hoc-Abfragen zu gestalten. Filterausdrücke können auch direkt in LookML deklariert und auf alle Abfragen angewendet werden.
Grundlegende Komponenten einer Looker-Abfrage
Alle Looker-Abfragen werden durch diese grundlegenden Parameter dargestellt, die auf ein LookML-Projekt angewendet werden, wie in der vorherigen Beispielabfrage dargestellt.
Looker verwendet die folgenden Parameter, um eine vollständige SQL-Abfrage zu generieren:
model
: der Name des zu verwendenden LookML-Modells, der die Zieldatenbank angibtexplore
: Der Name des abzufragenden Explores, das die SQL-KlauselFROM
füllt.- Felder: die Parameter
dimension
undmeasure
, die in die Abfrage aufgenommen werden sollen und mit denen die SQL-SELECT
-Klausel ausgefüllt wird filter
: Looker-Filterausdrücke, die auf null oder mehr Felder angewendet werden sollen, mit denen die SQL-KlauselWHERE
undHAVING
ausgefüllt werden- Sortierreihenfolge: das Feld, nach dem sortiert werden soll, sowie die Sortierfolge, mit der die SQL-
ORDER BY
-Klausel ausgefüllt wird
Diese Parameter sind genau die Elemente, die ein Nutzer beim Erstellen einer Abfrage auf der Looker-Seite Explore angibt. Dieselben Elemente werden in allen Modi zum Ausführen von Abfragen mit Looker angezeigt, z. B. im generierten SQL, in der URL, die die Abfrage darstellt, und in der Looker-API.
Was ist mit den Ansichten, die durch die LEFT JOIN
-Klauseln angegeben werden? JOIN
-Klauseln werden basierend auf der Struktur des LookML-Modells ausgefüllt, das angibt, wie Ansichten mit Explores verknüpft werden. Beim Erstellen von SQL-Abfragen fügt Looker JOIN
-Klauseln nur bei Bedarf ein. Wenn Benutzer eine Abfrage in Looker erstellen, müssen sie nicht angeben, wie Tabellen zusammengeführt werden, da diese Informationen im Modell codiert sind – einer der größten Vorteile von Looker für Geschäftsanwender.
Eine Beispielabfrage und das resultierende SQL
Lassen Sie uns eine Abfrage in Looker erstellen, um zu zeigen, wie die Abfrage nach dem vorherigen Muster generiert wird. Nehmen wir als Beispiel einen E-Commerce-Shop mit einer Datenbank mit zwei Tabellen, orders und users, um Nutzer und Bestellungen zu verfolgen.
orders |
---|
id INT |
created_at DATETIME |
users_id INT |
status VARCHAR(255) |
traffic_source VARCHAR(15) |
users |
---|
id INT |
email VARCHAR(255) |
first_name VARCHAR(255) |
last_name VARCHAR(255) |
created_at DATETIME |
zip INT |
country VARCHAR(255) |
state VARCHAR(255) |
city VARCHAR(255) |
age INT |
traffic_source VARCHAR(15) |
Lassen Sie uns die Anzahl der Aufträge (ORDERS-Anzahl) in einem Looker-Explore nach Bundesstaat (NUTZER Bundesstaat) gruppiert und nach dem Erstellungsdatum des Auftrags (ORDERS Created Date) gefiltert finden.
Wenn Sie sich die von Looker generierte und ausgeführte SQL-Abfrage ansehen möchten, klicken Sie im Bereich Daten auf den Tab SQL.
SELECT COALESCE(users.state, ' ') AS "_g1",
users.state AS 'users.state',
COUNT(DISTINCT orders.id) AS 'orders.count'
FROM orders
LEFT JOIN users ON orders.user_id = users.id
WHERE
orders.created_at BETWEEN (CONVERT_TZ(DATE_ADD(CURDATE(), INTERVAL -29 day), 'America/Los_Angeles', 'UTC',)) AND (CONVERT_TZ(DATE_ADD(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL -29 day), INTERVAL 30 day), INTERVAL -1 second), 'America/Los_Angeles', 'UTC'))
GROUP BY 1
ORDER BY COUNT(DISTINCT orders.id) DESC
LIMIT 500
Beachten Sie die Ähnlichkeit zur kanonischen Abfrageformel. Looker SQL weist einige Merkmale von maschinengeneriertem Code auf (z. B. COALESCE(users.state,'') AS "_g1"
), entspricht aber immer der Formel.
Experimentieren Sie mit mehr Abfragen in Looker, um sich selbst zu beweisen, dass die Abfragestruktur immer gleich ist.
Raw-SQL im SQL-Runner von Looker ausführen
Looker enthält die Funktion SQL Runner, mit der Sie beliebige SQL-Abfragen für die in Looker eingerichteten Datenbankverbindungen ausführen können.
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 experimentieren.
SQL-Rohabfragen, die in SQL Runner ausgeführt werden, erzeugen die gleichen Ergebnisse. Wenn die SQL Fehler enthält, hebt SQL Runner die Position des ersten Fehlers im SQL-Befehl hervor und gibt die Position des Fehlers in die Fehlermeldung an.
Suchanfragenkomponenten in der erweiterten URL untersuchen
Nachdem Sie eine Abfrage in Looker ausgeführt haben, können Sie sich die erweiterte URL ansehen, um die grundlegenden Komponenten einer Looker-Abfrage zu sehen. Wählen Sie zuerst Freigeben im Zahnradmenü des Explores aus, um das Menü URLs teilen zu öffnen.
Die erweiterte URL bietet genügend Informationen, um die Anfrage neu zu erstellen. Dieses Beispiel für eine erweiterte URL enthält die folgenden Informationen:
https://<Looker instance URL>.cloud.looker.com/explore/e_thelook/events?fields=users.state,users.count
&f[users.created_year]=2020&sorts=users.count+desc&limit=500
model | e_thelook |
analysieren | events |
Felder, die abgefragt und angezeigt werden sollen | fields=users.state,users.count |
Feld und Reihenfolge sortieren | sorts=users.count+desc |
Filterfelder und -werte | f[users.created_year]=2020 |
Wie Looker JOINs strukturiert
In der vorherigen Beispielabfrage sehen Sie, dass das Explore orders
im FROM
-Hauptteil und die verknüpften Ansichten in den LEFT JOIN
-Klauseln angezeigt werden. Looker-Joins können auf viele verschiedene Arten geschrieben werden. Dies wird auf der Seite Mit Joins in LookML arbeiten ausführlicher erläutert.
SQL-Blöcke geben benutzerdefinierte SQL-Klauseln an
Nicht alle Elemente einer Looker-Abfrage sind maschinengeneriert. Irgendwann muss das Datenmodell bestimmte Details bereitstellen, damit Looker auf die zugrunde liegenden Tabellen zugreifen und abgeleitete Werte berechnen kann. In LookML sind SQL-Blöcke von SQL-Code-Snippets, die vom Datenmodellierer bereitgestellt werden und von Looker verwendet werden, um vollständige SQL-Ausdrücke zu synthetisieren.
Der gängigste SQL-Blockparameter ist sql
. Er wird in Dimensions- und Messwertdefinitionen verwendet. 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
. Weitere Informationen zu den einzelnen Parametern finden Sie in der LookML-Referenz.
Beispiele für SQL-Blöcke für Dimensionen und Messwerte
Das folgende Codebeispiel enthält einige Beispiele für SQL-Blöcke für Dimensionen und Messwerte. Durch den LookML-Substitutionsoperator ($) wirken diese sql
-Deklarationen täuschend anders als SQL. Nach der Substitution ist der resultierende String jedoch reines SQL, das von Looker in die SELECT
-Klausel der Abfrage eingeschleust wird.
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 in diesem Beispiel gezeigt, können SQL-Blöcke Funktionen verwenden, die von der zugrunde liegenden Datenbank unterstützt werden (wie 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, mit der die Tabelle abgeleitet wird. Dies ist ein Beispiel für eine SQL-basierte abgeleitete Tabelle:
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 für einen SQL-Block zum Filtern eines Explores
Mit den LookML-Parametern sql_always_where
und sql_always_having
können Sie die für eine Abfrage verfügbaren Daten einschränken, indem Sie einen SQL-Block in die SQL-WHERE- oder HAVING-Klauseln einfügen. In diesem Beispiel wird der LookML-Substitutionsoperator ${view_name.SQL_TABLE_NAME}
verwendet, um auf eine abgeleitete Tabelle zu verweisen:
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});;
}