Eine abgeleitete Tabelle ist eine Abfrage, deren Ergebnisse so verwendet werden, als wäre die abgeleitete Tabelle eine physische Tabelle in der Datenbank. Eine native abgeleitete Tabelle basiert auf einer Abfrage, die Sie mit LookML-Begriffen definieren. Dies unterscheidet sich von einer SQL-basierten abgeleiteten Tabelle, die auf einer Abfrage basiert, die Sie mit SQL-Begriffen definieren. Im Vergleich zu SQL-basierten abgeleiteten Tabellen sind native abgeleitete Tabellen bei der Modellierung Ihrer Daten wesentlich leichter zu lesen und zu verstehen. Weitere Informationen finden Sie auf der Seite Abgeleitete Tabellen in Looker im Abschnitt Native abgeleitete Tabellen und SQL-basierte abgeleitete Tabellen.
Sowohl native als auch SQL-basierte abgeleitete Tabellen werden in LookML mit dem Parameter derived_table auf Ansichtsebene definiert. Bei nativen abgeleiteten Tabellen müssen Sie jedoch keine SQL-Abfrage erstellen. Stattdessen verwenden Sie den Parameter explore_source, um das Explore anzugeben, auf dem die abgeleitete Tabelle basieren soll, sowie die gewünschten Spalten und andere gewünschte Merkmale.
Sie können auch Looker die LookML für die abgeleitete Tabelle aus einer SQL Runner-Abfrage erstellen lassen, wie auf der Dokumentationsseite Mit SQL Runner abgeleitete Tabellen erstellen beschrieben.
Native abgeleitete Tabellen auf der Grundlage eines Explores definieren
Ausgehend von einem Explore kann Looker LookML für die gesamte abgeleitete Tabelle oder einen großen Teil davon generieren. Erstellen Sie einfach ein Explore, und wählen Sie alle Felder aus, die in der abgeleiteten Tabelle enthalten sein sollen. Anschließend generieren Sie den LookML-Code für die native abgeleitete Tabelle wie folgt:
Klicken Sie auf das Zahnrad-Menü Explore Actions (Aktionen ansehen) und wählen Sie Get LookML (LookML abrufen).
Klicken Sie auf den Tab Abgeleitete Tabelle, um die LookML zum Erstellen einer nativen abgeleiteten Tabelle für das Explore aufzurufen.
Kopieren Sie den LookML-Code.
Fügen Sie den kopierten LookML-Code in eine Ansichtsdatei ein:
Gehen Sie im Entwicklermodus zu Ihren Projektdateien.
Klicken Sie oben in der Projektdateiliste in der Looker-IDE auf das Pluszeichen + und wählen Sie Ansicht erstellen aus. Alternativ können Sie auf das Menü eines Ordners klicken und Ansicht erstellen auswählen, um die Datei im Ordner zu erstellen.
Geben Sie der Ansicht einen aussagekräftigen Namen.
Optional können Sie Spaltennamen ändern, abgeleitete Spalten festlegen und Filter hinzufügen.
Wenn Sie in einem Explore ein Ergebnis von
type: countverwenden, werden die resultierenden Werte in der Visualisierung nicht mit dem Wort Anzahl, sondern mit dem Namen der Datenansicht beschriftet. Um Verwechslungen zu vermeiden, sollten Sie den Namen der Ansicht im Plural angeben. Sie können den Namen der Ansicht ändern, indem Sie in den Visualisierungseinstellungen unter Reihe die Option Vollständigen Feldnamen anzeigen auswählen oder den Parameterview_labelmit einer Pluralform des Namens der Ansicht verwenden.
Native abgeleitete Tabelle in LookML definieren
Unabhängig davon, ob Sie in SQL deklarierte abgeleitete Tabellen oder native LookML verwenden, ist die Ausgabe der Abfrage von derived_table eine Tabelle mit einer Reihe von Spalten. Wenn die abgeleitete Tabelle in SQL ausgedrückt wird, werden die Spaltennamen der Ausgabe durch die SQL-Abfrage impliziert. Die folgende SQL-Abfrage hat beispielsweise die Ausgabespalten user_id, lifetime_number_of_orders und lifetime_customer_value:
SELECT
user_id
, COUNT(DISTINCT order_id) as lifetime_number_of_orders
, SUM(sale_price) as lifetime_customer_value
FROM order_items
GROUP BY 1
In Looker beruht eine Abfrage auf einem Explore, enthält Felder für Messwerte und Dimensionen, fügt ggf. Filter hinzu und kann auch eine Sortierreihenfolge vorgeben. Eine native abgeleitete Tabelle enthält all diese Elemente plus die Ausgabenamen für die Spalten.
Im folgenden einfachen Beispiel wird eine abgeleitete Tabelle mit drei Spalten erstellt: user_id, lifetime_customer_value und lifetime_number_of_orders. Sie müssen die Abfrage nicht manuell in SQL schreiben. Stattdessen erstellt Looker die Abfrage für Sie anhand des angegebenen Explores order_items und einiger seiner Felder (order_items.user_id, order_items.total_revenue und order_items.order_count).
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
}
include-Anweisungen verwenden, um Verweise auf Felder zu ermöglichen
In der Ansichtsdatei der nativen abgeleiteten Tabelle verweisen Sie mit dem Parameter explore_source auf ein Explore und definieren die Spalten und andere Eigenschaften für die native abgeleitete Tabelle.
In der Ansichtsdatei der nativen abgeleiteten Tabelle müssen Sie den Parameter include nicht verwenden, um auf die Datei zu verweisen, die die Explore-Definition enthält. Wenn Sie die include-Anweisung nicht haben, werden in der Looker IDE keine Feldnamen automatisch vorgeschlagen und Ihre Feldreferenzen werden beim Erstellen der nativen abgeleiteten Tabelle nicht überprüft. Stattdessen können Sie mit dem LookML-Validator die Felder prüfen, auf die Sie in Ihrer nativen abgeleiteten Tabelle verweisen.
Wenn Sie jedoch die automatische Vervollständigung und die sofortige Feldüberprüfung in der Looker IDE aktivieren möchten oder ein komplexes LookML-Projekt mit mehreren Explores mit demselben Namen oder mit dem Potenzial für Schleifenverweise haben, können Sie mit dem Parameter include auf die Definition des Explores verweisen.
Explores werden oft in einer Modelldatei definiert, aber im Fall von nativ abgeleiteten Tabellen ist es übersichtlicher, eine separate Datei für das Explore zu erstellen. LookML-Explore-Dateien haben die Dateiendung .explore.lkml, wie in der Dokumentation unter Explore-Dateien erstellen beschrieben. Auf diese Weise können Sie in der Ansichtsdatei der nativen abgeleiteten Tabelle eine einzelne Explore-Datei hinzufügen und nicht die gesamte Modelldatei.
Wenn Sie eine separate Explore-Datei erstellen und den Parameter include verwenden möchten, um in der Ansichtsdatei Ihrer nativen abgeleiteten Tabelle auf die Explore-Datei zu verweisen, müssen Ihre LookML-Dateien die folgenden Anforderungen erfüllen:
- Die Ansichtsdatei der nativen abgeleiteten Tabelle sollte die Explore-Datei enthalten. Beispiel:
include: "/explores/order_items.explore.lkml"
- Die Explore-Datei sollte die benötigten Ansichtsdateien enthalten. Beispiel:
include: "/views/order_items.view.lkml"include: "/views/users.view.lkml"
- Das Modell sollte die Explore-Datei enthalten. Beispiel:
include: "/explores/order_items.explore.lkml"
Spalten nativer abgeleiteter Tabellen definieren
Wie im vorherigen Beispiel gezeigt, geben Sie die Ausgabespalten der abgeleiteten Tabelle mit column an.
Spaltennamen angeben
Bei der Spalte user_id entspricht der Spaltenname dem Namen des festgelegten Felds im ursprünglichen Explore.
Es wird häufiger vorkommen, dass der Spaltenname in der Ausgabetabelle anders lauten soll als der Name der Felder im ursprünglichen Explore. Im vorherigen Beispiel wurde mit dem Explore order_items eine Berechnung des Lifetime-Werts nach Nutzer erstellt. In der Ausgabetabelle entspricht total_revenue dem lifetime_customer_value eines Kunden.
Die Deklaration column unterstützt die Deklaration eines Ausgabenamens, der sich vom Eingabefeld unterscheidet. Der folgende Code weist Looker beispielsweise an, eine Ausgabespalte mit dem Namen lifetime_value aus Feld order_items.total_revenue zu erstellen:
column: lifetime_value {
field: order_items.total_revenue
}
Implizierte Spaltennamen
Wird der Parameter field in einer Spaltendeklaration nicht angegeben, wird davon ausgegangen, dass er <explore_name>.<field_name> ist. Wenn Sie beispielsweise explore_source: order_items angegeben haben,
column: user_id {
field: order_items.user_id
}
entspricht
column: user_id {}
Abgeleitete Spalten für berechnete Werte erstellen
Sie können derived_column-Parameter hinzufügen, um Spalten anzugeben, die im explorativen Analysetool des Parameters explore_source nicht vorhanden sind. Jeder derived_column-Parameter hat einen sql-Parameter, der angibt, wie der Wert konstruiert wird.
Für die sql-Berechnung können alle Spalten verwendet werden, die Sie mit column-Parametern angegeben haben. Abgeleitete Spalten können zwar keine Summenfunktionen enthalten, aber Berechnungen, die an einer einzelnen Tabellenzeile durchgeführt werden können.
Im folgenden Beispiel wird die gleiche abgeleitete Tabelle wie im vorherigen Beispiel erzeugt, mit der Ausnahme, dass eine berechnete average_customer_order-Spalte hinzugefügt wird, die aus den Spalten lifetime_customer_value und lifetime_number_of_orders in der nativen abgeleiteten Tabelle berechnet wird.
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
SQL-Fensterfunktionen verwenden
Einige Datenbankdialekte unterstützen Fensterfunktionen, insbesondere zum Erstellen von Sequenznummern, Primärschlüsseln, laufenden und kumulativen Summen und anderen nützlichen mehrzeiligen Berechnungen. Nach Ausführung der primären Abfrage werden alle derived_column-Deklarationen gesondert ausgeführt.
Sofern Ihr Datenbankdialekt Fensterfunktionen unterstützt, können Sie diese in Ihrer nativen abgeleiteten Tabelle nutzen. Erstellen Sie einen derived_column-Parameter mit einem sql-Parameter, der die gewünschte Fensterfunktion enthält. Beim Verweisen auf Werte sollten Sie den Spaltennamen verwenden, der in Ihrer nativen abgeleiteten Tabelle definiert wurde.
Im folgenden Beispiel wird eine native abgeleitete Tabelle erstellt, die die Spalten user_id, order_id und created_time enthält. Anschließend wird anhand einer abgeleiteten Spalte mit einer SQL ROW_NUMBER()-Fensterfunktion eine Spalte berechnet, die die Sequenznummer eines Kundenauftrags enthält.
view: user_order_sequences {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: order_id {
field: order_items.order_id
}
column: created_time {
field: order_items.created_time
}
derived_column: user_sequence {
sql: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_time) ;;
}
}
}
dimension: order_id {
hidden: yes
}
dimension: user_sequence {
type: number
}
}
Filter zu einer nativen abgeleiteten Tabelle hinzufügen
Angenommen, Sie möchten eine abgeleitete Tabelle für den Wert eines Kunden in den letzten 90 Tagen erstellen. Sie möchten dieselben Berechnungen wie im vorherigen Beispiel ausführen, aber nur Käufe der letzten 90 Tage berücksichtigen.
Hierzu fügen Sie dem derived_table einfach einen Filter hinzu, mit dem nach Transaktionen der letzten 90 Tage gefiltert wird. Der Parameter filters für eine abgeleitete Tabelle verwendet dieselbe Syntax wie beim Erstellen eines gefilterten Messwerts.
view: user_90_day_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: number_of_orders_90_day {
field: order_items.order_count
}
column: customer_value_90_day {
field: order_items.total_revenue
}
filters: [order_items.created_date: "90 days"]
}
}
# Add define view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: number_of_orders_90_day {
type: number
}
dimension: customer_value_90_day {
type: number
}
}
Die Filter werden der WHERE-Klausel hinzugefügt, wenn Looker den SQL-Code für die abgeleitete Tabelle schreibt.
Außerdem können Sie den Unterparameter dev_filters von explore_source mit einer nativ abgeleiteten Tabelle verwenden. Mit dem Parameter dev_filters können Sie Filter angeben, die Looker nur auf Entwicklungsversionen der abgeleiteten Tabelle anwendet. Das bedeutet, dass Sie kleinere, gefilterte Versionen der Tabelle erstellen können, um sie zu iterieren und zu testen, ohne nach jeder Änderung darauf warten zu müssen, dass die vollständige Tabelle erstellt wurde.
Der Parameter dev_filters agiert in Kombination mit dem Parameter filters, sodass alle Filter auf die Entwicklungsversion der Tabelle angewendet werden. Wenn sowohl dev_filters als auch filters Filter für dieselbe Spalte angeben, hat dev_filters für die Entwicklungsversion der Tabelle Vorrang.
Weitere Informationen finden Sie unter Schnelleres Arbeiten im Entwicklungsmodus.
Filtervorlagen verwenden
Mit bind_filters können Sie Filtervorlagen einschließen:
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
Dies entspricht im Grunde der Verwendung des folgenden Codes in einem sql-Block:
{% condition filtered_lookml_dt.filter_date %} users.created_date {% endcondition %}
to_field ist das Feld, auf das der Filter angewendet wird. to_field muss ein Feld aus dem zugrunde liegenden explore_source sein.
Mit from_field wird das Feld angegeben, aus dem der Filter abgerufen werden soll, wenn zur Laufzeit ein Filter vorhanden ist.
Im vorherigen bind_filters-Beispiel übernimmt Looker jeden Filter, der auf das Feld filtered_lookml_dt.filter_date angewendet wurde, und wendet ihn auf das Feld users.created_date an.
Sie können auch den Unterparameter bind_all_filters von explore_source verwenden, um alle Laufzeitfilter aus einer explorativen Datenanalyse an eine Unterabfrage der nativen abgeleiteten Tabelle zu übergeben. Weitere Informationen finden Sie auf der Seite mit der Parameterdokumentation zu explore_source.
Native abgeleitete Tabellen sortieren und begrenzen
Abgeleitete Tabellen können bei Bedarf auch sortiert und eingeschränkt werden:
sorts: [order_items.count: desc]
limit: 10
Denken Sie daran, dass ein Explore die Zeilen möglicherweise in einer anderen Reihenfolge als die zugrunde liegende Sortierung anzeigt.
Native abgeleitete Tabellen in andere Zeitzonen konvertieren
Sie können die Zeitzone für die native abgeleitete Tabelle mit dem Unterparameter timezone angeben:
timezone: "America/Los_Angeles"
Wenn Sie den Unterparameter timezone verwenden, werden alle zeitbasierten Daten in der nativen abgeleiteten Tabelle entsprechend der angegebenen Zeitzone umgewandelt. Eine Liste der unterstützten Zeitzonen finden Sie auf der Dokumentationsseite zu timezone-Werten.
Wenn Sie in der Definition der nativen abgeleiteten Tabelle keine Zeitzone angeben, führt die native abgeleitete Tabelle keine Zeitzonenkonvertierung für zeitbasierte Daten durch. Stattdessen wird für zeitbasierte Daten standardmäßig die Datenbankzeitzone verwendet.
Wenn die native abgeleitete Tabelle nicht persistent ist, können Sie den Zeitzonenwert auf "query_timezone" setzen, um automatisch die Zeitzone der aktuell ausgeführten Abfrage zu verwenden.