So generiert Looker SQL

Wenn Sie SQL-Kenntnisse haben und neu in Looker sind, fragen Sie sich wahrscheinlich, 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 basierend auf einem LookML-Projekt, das die Beziehung zwischen Tabellen und Spalten in der Datenbank beschreibt. Wenn Sie wissen, wie Looker Abfragen generiert, können Sie besser nachvollziehen, wie Ihr LookML-Code in effiziente SQL-Abfragen übersetzt wird.

Jeder LookML-Parameter steuert einen Aspekt der SQL-Generierung durch Looker, indem er die Struktur, den Inhalt oder das Verhalten der Abfrage ändert. Auf dieser Seite werden die Grundlagen der SQL-Generierung in Looker beschrieben. Es werden jedoch nicht alle LookML-Elemente im Detail behandelt. Die Dokumentationsseite LookML-Kurzübersicht ist ein guter Ausgangspunkt für Informationen zu LookML-Parametern.

Abfrage ansehen

In einem gespeicherten Look oder in einem Explore können Sie auf dem Tab SQL im Bereich Daten 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 erläutern unten auf dem Tab SQL verwenden, um Ihre Abfrage in SQL Runner aufzurufen oder den Explain-Plan der Datenbank für die Abfrage aufzurufen.

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 Community-Beitrag SQL mit EXPLAIN optimieren.

Kanonische Form einer Looker-Abfrage

Die 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, Explores und Ansichten definiert, auf die in der SQL-Abfrage verwiesen wird. Filterausdrücke werden in Looker vom Nutzer angegeben, um Ad-hoc-Abfragen zu gestalten. Filterausdrücke können auch direkt im LookML-Code deklariert werden, um auf alle Abfragen angewendet zu 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 zu sehen ist.

Looker verwendet die folgenden Parameter, um eine vollständige SQL-Abfrage zu generieren:

• model: Der Name des LookML-Modells, auf das abgezielt werden soll. Damit wird die Zieldatenbank angegeben.
• explore: Der Name des zu abfragenden Explores, der die SQL-FROM-Klausel enthält.
• Felder: Die Parameter dimension und measure, die in die Abfrage aufgenommen werden und die SQL-Klausel SELECT füllen.
• filter: Looker-Filterausdrücke, die auf null oder mehr Felder angewendet werden sollen und die SQL-Klauseln WHERE und HAVING enthalten
• Sortierreihenfolge: Das Feld, nach dem sortiert werden soll, und die Sortierreihenfolge, die die SQL-Klausel ORDER BY enthält.

Diese Parameter sind genau die Elemente, die ein Nutzer beim Erstellen einer Abfrage auf der Looker-Seite Explore angibt. Diese Elemente sind in allen Modi zum Ausführen von Abfragen mit Looker vorhanden, z. B. im generierten SQL, in der URL, die die Abfrage darstellt, und in der Looker API.

Was ist mit den Ansichten, die in den LEFT JOIN-Klauseln angegeben sind? JOIN-Klauseln werden basierend auf der Struktur des LookML-Modells ausgefüllt, in dem angegeben ist, wie Ansichten mit Explores verknüpft werden. Beim Erstellen von SQL-Abfragen fügt Looker JOIN-Klauseln nur bei Bedarf ein. Wenn Nutzer eine Abfrage in Looker erstellen, müssen sie nicht angeben, wie Tabellen verknüpft werden, da diese Informationen im Modell codiert sind. Das ist einer der größten Vorteile von Looker für Geschäftsnutzer.

Beispielabfrage und resultierendes SQL

Wir erstellen nun eine Abfrage in Looker, um zu zeigen, wie die Abfrage gemäß dem vorherigen Muster generiert wird. Angenommen, Sie haben einen Onlineshop mit einer Datenbank mit zwei Tabellen, orders und users, um Nutzer und Bestellungen zu erfassen.

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)

Wir möchten die Anzahl der Bestellungen (ORDERS Count) nach Bundesstaat (USERS State) gruppiert und nach dem Erstellungsdatum der Bestellung (ORDERS Created Date) gefiltert in einem Looker-Explore abrufen.

Wenn Sie die SQL-Abfrage sehen möchten, die von Looker generiert und ausgeführt wird, 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 zum kanonischen Abfrageformular. Der Looker-SQL-Code weist einige Merkmale von maschinell generiertem Code auf (z. B. COALESCE(users.state,'') AS "_g1"), entspricht aber immer der Formel.

Probieren Sie weitere Abfragen in Looker aus, um sich davon zu überzeugen, dass die Abfragestruktur immer dieselbe ist.

Roh-SQL in SQL Runner von Looker ausführen

Looker enthält die Funktion SQL Runner, mit der Sie beliebigen SQL-Code für die Datenbankverbindungen ausführen können, die Sie in Looker eingerichtet haben.

Da jede von Looker generierte Abfrage einen vollständigen, funktionalen SQL-Befehl ergibt, können Sie den SQL Runner verwenden, um die Abfrage zu untersuchen oder damit zu arbeiten.

Roh-SQL-Abfragen, die in SQL Runner ausgeführt werden, liefern dieselbe Ergebnismenge. Wenn der SQL-Code Fehler enthält, wird in SQL Runner die Position des ersten Fehlers im SQL-Befehl hervorgehoben und die Position des Fehlers wird in die Fehlermeldung aufgenommen.

Hinweis :Wenn in Ihren Verbindungseinstellungen eine Datenbankzeitzone angegeben ist, gibt Looker bei der Verbindung einen SET TIMEZONE aus, was sich auf die Ergebnisse auswirken kann.

Abfragekomponenten in der erweiterten URL untersuchen

Nachdem Sie eine Abfrage in Looker ausgeführt haben, können Sie die erweiterte URL aufrufen, um die grundlegenden Komponenten einer Looker-Abfrage zu sehen. Wählen Sie zuerst im Zahnradmenü von „Entdecken“ die Option Teilen aus, um das Menü URLs teilen zu öffnen.

Hinweis :Wenn Sie von einem Look ausgehen, klicken Sie auf den Link Von hier aus explorieren des Looks, um die Abfrage in einem Explore zu öffnen.

Die erweiterte URL enthält genügend Informationen, um die Anfrage zu rekonstruieren. Dieses Beispiel für eine erweiterte URL enthält beispielsweise 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

Modell	e_thelook
analysieren	events
Felder für Abfrage und Anzeige	fields=users.state,users.count
Sortierfeld und ‑reihenfolge	sorts=users.count+desc
Filterfelder und ‑werte	f[users.created_year]=2020

So strukturiert Looker JOINs

In der vorherigen Beispielabfrage sehen Sie, dass das Explore orders in der Hauptklausel FROM und die verknüpften Ansichten in den Klauseln LEFT JOIN enthalten sind. Looker-Joins können auf viele verschiedene Arten geschrieben werden. Das wird auf der Seite Arbeiten mit Joins in LookML genauer erläutert.

SQL-Blöcke geben benutzerdefinierte SQL-Klauseln an

Nicht alle Elemente einer Looker-Abfrage werden maschinell generiert. Irgendwann muss das Datenmodell spezifische Details enthalten, 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 die Looker verwendet, um vollständige SQL-Ausdrücke zu erstellen.

Der gängigste SQL-Blockparameter ist sql. Er wird in Dimensions- und Messwertdefinitionen verwendet. Mit dem Parameter sql wird eine SQL-Klausel angegeben, mit der auf eine zugrunde liegende Spalte verwiesen oder eine Aggregatfunktion ausgeführt wird. Im Allgemeinen erwarten alle LookML-Parameter, die mit sql_ beginnen, einen SQL-Ausdruck in irgendeiner Form. Beispiele: 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 Codebeispiel finden Sie einige Beispiele für SQL-Blöcke für Dimensionen und Messwerte. Der LookML-Substitutionsoperator ($) kann bewirken, dass diese sql-Deklarationen fälschlicherweise nicht wie SQL wirken. Doch nach einer Substitution ist die daraus resultierende Zeichenfolge reines SQL, das 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 in diesem Beispiel zu sehen, 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 Code, den Sie in SQL-Blöcken verwenden, muss dem von der Datenbank verwendeten SQL-Dialekt entsprechen.

Beispiel für einen SQL-Block für abgeleitete Tabellen

Abgeleitete Tabellen verwenden ebenfalls einen SQL-Block, um die Abfrage zur Ableitung der Tabelle anzugeben. Hier sehen Sie 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-Ersetzungsoperator ${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});;
}