Beachten Sie, dass Sie sich die Looker-Dokumentation ansehen. Dokumentation zu Looker Studio finden Sie unter https://support.google.com/looker-studio.

Diese Seite wurde von der Cloud Translation API übersetzt.

Wie Looker SQL generiert

Wenn Sie von einem SQL-Umfeld zu Looker kommen, sind Sie wahrscheinlich neugierig darauf, wie Looker SQL generiert. Im Grunde ist Looker ein Tool, das SQL-Abfragen generiert und über 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 besser verstehen, wie Ihr LookML-Code in effiziente SQL-Abfragen übersetzt wird.

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

Abfrage anzeigen

In einem gespeicherten Look oder in 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 oder den Erklärplan der Datenbank für die Abfrage anzusehen.

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 optiming 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 formen. 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 (siehe vorherige Beispielabfrage).

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

model: der Name des LookML-Modells, das als Ziel verwendet werden soll, das die Zieldatenbank angibt
explore: der Name des abzufragenden Explores, mit dem die SQL-FROM-Klausel ausgefüllt wird
Felder: Die Parameter dimension und measure, 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, die die SQL-Klauseln WHERE und HAVING ausfüllen
Sortierreihenfolge: das Feld, nach dem sortiert werden soll, und die Sortierreihenfolge, 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 Erkunden angibt. In allen Modi der Ausführung von Abfragen mit Looker werden dieselben Elemente angezeigt: im generierten SQL-Code, 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 basierend auf der Struktur des LookML-Modells ausgefüllt, das angibt, wie Ansichten mit Explores verbunden 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 gemäß 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 in einem Looker-Explore die Anzahl der Aufträge (ORDERS Count) nach Status (NUTZERstatus) gruppiert und nach dem Erstellungsdatum des Auftrags (Erstellungsdatum der BESTELLUNGEN) filtern.

In einer Explore-Datentabelle wird die Anzahl der Bestellungen angezeigt, die in den letzten 30 Tagen aufgegeben wurden, gruppiert nach Nutzerstatus.

Klicken Sie im Bereich Daten auf den Tab SQL, um die von Looker generierte und ausgeführte SQL-Abfrage anzuzeigen.

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 mit der 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 weiteren Abfragen in Looker, um sich selbst zu beweisen, dass die Abfragestruktur immer gleich ist.

Raw-SQL im SQL-Runner von Looker ausführen

Looker beinhaltet eine Funktion namens SQL-Runner, mit der Sie einen beliebigen SQL-Code 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 die Abfrage mit dem SQL Runner untersuchen oder ausprobieren.

Raw-SQL-Abfragen, die in SQL Runner ausgeführt werden, erzeugen denselben Ergebnissatz. Enthält die SQL-Abfrage Fehler, hebt SQL Runner die Position des ersten Fehlers im SQL-Befehl hervor und fügt die Position des Fehlers in die Fehlermeldung auf.

Die Komponenten der Suchanfrage in der erweiterten URL werden untersucht

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

Die erweiterte URL stellt genügend Informationen bereit, um die Abfrage neu zu erstellen. Diese 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

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`

So strukturiert Looker JOINs

Beachten Sie in der vorherigen Beispielabfrage, dass das Explore orders in der FROM-Hauptklausel und die verknüpften Ansichten in den LEFT JOIN-Klauseln angezeigt werden. Looker-Verknüpfungen 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 enthalten benutzerdefinierte SQL-Klauseln

Nicht alle Elemente einer Looker-Abfrage werden 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 Snippets aus SQL-Code, die vom Datenmodellierer bereitgestellt werden und den Looker zum Synthetisieren vollständiger SQL-Ausdrücke verwendet.

Der gängigste SQL-Blockparameter ist sql und 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

Im Folgenden finden Sie einige Beispiele für SQL-Blöcke für Dimensionen und Messwerte. Der LookML-Substitutionsoperator ($) lässt diese sql-Deklarationen scheinbar anders als SQL erscheinen. Nach einer Ersetzung 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, z. B. die MySQL-Funktionen CONCAT und DATEDIFF. Der in SQL-Blöcken verwendete Code muss dem von der Datenbank verwendeten SQL-Dialekt entsprechen.

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

Abgeleitete Tabellen verwenden auch einen SQL-Block, um die Abfrage anzugeben, mit 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 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 einschleusen. 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});;
}