So erstellt Looker SQL

Wenn Sie von einem SQL-Hintergrund zu Looker kommen, möchten Sie wahrscheinlich wissen, wie Looker SQL generiert. Looker ist ein Tool, mit dem Sie SQL-Abfragen erstellen und an eine Datenbankverbindung senden können. Looker formuliert SQL-Abfragen auf der Grundlage eines LookML-Projekts, das die Beziehung zwischen Tabellen und Spalten in der Datenbank beschreibt. Wenn Sie verstehen, wie Looker Abfragen generiert, können Sie besser nachvollziehen, wie sich der LookML-Code auf effiziente SQL-Abfragen auswirkt.

Jeder LookML-Parameter steuert einen Aspekt der SQL-Generierung durch Looker. Dazu wird die Struktur, der Inhalt oder das Verhalten der Abfrage geändert. Auf dieser Seite werden die Prinzipien der SQL-Erstellung beschrieben, allerdings werden nicht alle LookML-Elemente im Detail behandelt. Die Dokumentation zu LookML-Kurzanleitungen ist ein guter Ausgangspunkt für Informationen zu LookML-Parametern.

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. Sie können auch die Links unten verwenden, um Ihre Abfrage in SQL Runner anzusehen oder den Erklärplan der Datenbank für die Abfrage zu sehen. Weitere Informationen zu SQL Runner finden Sie auf der Dokumentationsseite SQL Runner-Grundlagen. Weitere Informationen zum Optimieren einer Abfrage mit SQL Runner finden Sie im Communitybeitrag How to optimize SQL with EXPLAIN (Mit SQL optimieren).

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>

Im LookML-Projekt sind 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 formen. Filterausdrücke können auch direkt in LookML deklariert werden, um sie auf alle Abfragen anzuwenden.

Grundlegende Komponenten einer Looker-Abfrage

Alle grundlegenden 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: Name des Ziel-ML-Modells, auf das die Zieldatenbank festgelegt werden soll
  • explore: Name der abzufragenden Abfrage, mit der die SQL-FROM-Klausel gefüllt wird
  • Felder: Die Parameter, die in die Abfrage einbezogen werden sollen und in die SELECT-Klausel für SQL eingetragen werden: dimension- und measure-Parameter
  • filter: Looker-Filterausdrücke, die auf null oder mehr Felder angewendet werden, mit denen die SQL-Klauseln WHERE und HAVING gefüllt werden
  • 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. Diese Elemente werden in allen Modi der Ausführung von Abfragen mit Looker angezeigt: im generierten SQL, in der URL, die die Abfrage darstellt, in der Looker API usw.

Was ist mit den Ansichten, die durch die LEFT JOIN-Klauseln angegeben werden? JOIN-Klauseln werden auf der Grundlage der Struktur des LookML-Modells ausgefüllt, das angibt, wie Datenansichten mit Explores zusammengeführt werden. Bei der Erstellung 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ührt werden. Denn diese Informationen sind im Modell codiert – einer der wichtigsten Vorteile von Looker für geschäftliche Nutzer.

Eine Beispielabfrage und die resultierende SQL-Abfrage

Wir erstellen eine Abfrage in Looker, um zu zeigen, wie die Abfrage gemäß dem obigen Muster generiert wird. Nehmen wir als Beispiel einen E-Commerce-Shop mit Tabellen, in dem Sie Nutzer und Bestellungen verfolgen. Die Felder und Tabellenbeziehungen sind unten dargestellt.

Die Anzahl der Bestellungen (ORDERS Count) wird nach Bundesstaat (USERS State) gruppiert und nach Erstellungsdatum des Auftrags gefiltert (Erstellungsdatum: ORDERS).

Unten finden Sie das Abfrageergebnis auf der Seite „Looker untersuchen“.

Klicken Sie auf den Tab SQL, um den von Looker generierten und ausgeführten SQL-Code aufzurufen.

Ähnlichkeit mit der oben genannten kanonischen Formel Die SQL-Elemente von Looker weisen einige Eigenschaften des maschinell erstellten Codes auf (z.B. COALESCE(users.state,'') AS "_g1"), passen jedoch immer zur 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>

Testen Sie in Looker weitere Abfragen, um sich selbst zu beweisen, dass die Abfragestruktur immer gleich ist.

Roh-SQL im Looker-SQL-Runner ausführen

Looker enthält ein Feature namens SQL Runner, mit dem Sie den gewünschten SQL-Code für die in Looker eingerichteten Datenbankverbindungen ausführen können.

Da jede von Looker generierte Abfrage zu einem vollständigen, funktionsfähigen SQL-Befehl führt, können Sie mit dem SQL Runner die Abfrage untersuchen oder mit ihr spielen.

Roh-SQL-Abfragen, die in SQL Runner ausgeführt werden, generieren denselben Ergebnissatz.

Wenn der SQL-Code Fehler enthält, hebt SQL Runner die Position des ersten Fehlers im SQL-Befehl hervor und gibt die Fehlerposition in der Fehlermeldung an.

Suchanfragenkomponenten in der URL prüfen

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ählen Sie im Zahnrad-Menü unter „Erkunden“ die Option Teilen aus:

Wenn Sie bei einem Look beginnen, klicken Sie auf den Link Erkunden von hier, um die Abfrage in einem explorativen Analysetool zu öffnen.

Nun wird das Fenster URLs teilen mit der erweiterten URL angezeigt:

Die URL enthält genügend Informationen, um die Abfrage neu zu erstellen. Hier ein Beispiel für eine erweiterte Freigabe-URL:

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

Die URL enthält folgende Informationen:

model e_thelook
analysieren events
Felder zum Abfragen und Anzeigen fields=users.state,users.count
Feld sortieren und sortieren sorts=users.count+desc
Felder und Werte filtern f[users.created_year]=2020

So funktioniert Looker bei JOINs

Beachten Sie in der SQL-Abfrage oben, dass das orders-explorative Analysetool in der Haupt-FROM-Klausel und die zusammengeführten Ansichten in den LEFT JOIN-Klauseln enthalten sind. Looker-Joins können auf verschiedene Arten geschrieben werden. Dies wird auf der Seite Mit Joins in LookML ausführlicher erläutert.

SQL-Blöcke geben benutzerdefinierte SQL-Klauseln an

Nicht alle Elemente einer Looker-Abfrage werden maschinell generiert. 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 Snippets von SQL-Code, die vom Datenmodellierer bereitgestellt werden und mit denen Looker vollständige SQL-Ausdrücke synthetisiert.

Der gängigste SQL-Blockparameter ist sql. Er wird in Definitionen für Dimensionen und Messwerte verwendet. Der Parameter sql gibt eine SQL-Klausel an, um auf eine zugrunde liegende Spalte zu verweisen oder eine Aggregatfunktion auszuführen. Generell 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

Im Folgenden finden Sie einige Beispiele für SQL-Blöcke für Dimensionen und Messwerte. Mit dem LookML-Substitutionsoperator ($) erscheinen diese sql-Deklarationen irreführend im Gegensatz zu SQL. Der Ersatzstring ist nach dem Ersetzen 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). 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
  }
}

SQL-Beispielblock zum Filtern einer explorativen Datenanalyse

Mit den LookML-Parametern sql_always_where und sql_always_having können Sie die für eine Abfrage verfügbaren Daten einschränken. Dazu fügen Sie einen SQL-Block in die SQL-WHERE- oder HAVING-Klauseln ein. In diesem Beispiel wird mit dem LookML-Substitutionsoperator ${view_name.SQL_TABLE_NAME} eine abgeleitete Tabelle referenziert:

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});;
}