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 in 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 anderen gewünschten 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 Zahnradsymbol Explore-Aktionen und wählen Sie LookML abrufen aus.
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:
Rufen Sie im Entwicklungsmodus Ihre Projektdateien auf.
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 einer derived_table-Abfrage eine Tabelle mit mehreren 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 verwenden Sie den Parameter explore_source, um auf ein Explore zu verweisen und die Spalten und anderen Merkmale für die native abgeleitete Tabelle zu definieren.
In der Ansichtsdatei der nativen abgeleiteten Tabelle müssen Sie nicht den Parameter include verwenden, um auf die Datei zu verweisen, die die Definition des Explores 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 vorangehenden Beispiel zu sehen, verwenden Sie column, um die Ausgabespalten der abgeleiteten Tabelle anzugeben.
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 Angabe eines Ausgabenamens, der anders als das Eingabefeld lautet. Mit dem folgenden Code wird Looker beispielsweise angewiesen, „eine Ausgabespalte namens lifetime_value aus dem 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 Explore des Parameters explore_source nicht vorhanden sind. Jeder derived_column-Parameter hat einen sql-Parameter, der angibt, wie der Wert erstellt werden soll.
Für die Berechnung von sql 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 dieselbe abgeleitete Tabelle wie im vorherigen Beispiel erstellt, mit der Ausnahme, dass eine berechnete Spalte average_customer_order 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 sowie anderen nützlichen Berechnungen für mehrere Zeilen. 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 mit den Spalten user_id, order_id und created_time erstellt. 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 verwenden, aber nur Käufe der letzten 90 Tage berücksichtigen.
Dazu fügen Sie der derived_table einfach einen Filter hinzu, der nach Transaktionen der letzten 90 Tage filtert. 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. So können Sie kleinere, gefilterte Versionen der Tabelle erstellen, um sie zu iterieren und zu testen, ohne nach jeder Änderung auf die Erstellung der vollständigen Tabelle warten zu müssen.
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 in der Entwicklungsversion der Tabelle Vorrang.
Weitere Informationen finden Sie unter Schnelles 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 der zugrunde liegenden explore_source sein.
Mit from_field wird das Feld angegeben, aus dem der Filter abgerufen wird, sofern zur Laufzeit ein Filter vorhanden ist.
Im vorherigen Beispiel für bind_filters nimmt Looker jeden Filter, der auf das Feld filtered_lookml_dt.filter_date angewendet wird, 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
Mit dem Unterparameter timezone können Sie die Zeitzone Ihrer nativen abgeleiteten Tabelle festlegen:
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 nativ abgeleiteten Tabelle keine Zeitzone angeben, wird in der nativ abgeleiteten Tabelle keine Zeitzonenkonvertierung für zeitbasierte Daten durchgeführt. Stattdessen wird für zeitbasierte Daten standardmäßig die Zeitzone der Datenbank verwendet.
Wenn die native abgeleitete Tabelle nicht persistent ist, können Sie den Zeitzonenwert auf "query_timezone" festlegen, damit die Zeitzone der aktuell laufenden Abfrage verwendet wird.