So generiert Looker SQL

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 die FROM-Klausel von SQL gefüllt wird
  • Felder: die in die Abfrage aufzunehmenden Parameter dimension und measure, die in der SQL-Klausel SELECT enthalten sind
  • filter: Looker-Filterausdrücke, die auf null oder mehr Felder angewendet werden, die die SQL-Klauseln WHERE und HAVING 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(&#42;) 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});;
}