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. Das 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 im Abschnitt Native abgeleitete Tabellen und SQL-basierte abgeleitete Tabellen auf der Dokumentationsseite Abgeleitete Tabellen in Looker.
Sowohl native als auch SQL-basierte abgeleitete Tabellen werden in LookML mit dem Parameter derived_table auf der Ebene view 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 Abgeleitete Tabellen mit SQL Runner 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:
Wählen Sie das Zahnradmenü Aktionen untersuchen und dann LookML abrufen aus.
Klicken Sie auf den Tab Abgeleitete Tabelle, um die LookML zum Erstellen einer nativen abgeleiteten Tabelle für das Explore anzuzeigen.
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 dann Ansicht erstellen aus. Alternativ können Sie auf das Menü eines Ordners klicken und im Menü 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 den Messwert
type: countverwenden, werden die resultierenden Werte in der Visualisierung mit dem Ansichtsnamen und nicht mit dem Wort Anzahl gekennzeichnet. Um Verwechslungen zu vermeiden, sollten Sie den Namen Ihrer Ansicht im Plural angeben. Sie können den Ansichtsnamen ändern, indem Sie in den Visualisierungseinstellungen unter Serie die Option Vollständigen Feldnamen anzeigen auswählen oder den Parameterview_labelmit einer Pluralversion des Ansichtsnamens verwenden.
Native abgeleitete Tabelle in LookML definieren
Unabhängig davon, ob Sie abgeleitete Tabellen verwenden, die in SQL oder nativem LookML deklariert sind, ist die Ausgabe einer derived_table-Abfrage 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 wird sie in Looker anhand des angegebenen Explores order_items und einiger Felder dieses Explores (order_items.user_id, order_items.total_revenue und order_items.order_count) erstellt.
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
Im Ansichtsdateien der nativen abgeleiteten Tabelle verwenden Sie den Parameter explore_source, um auf einen Explore zu verweisen und die Spalten und andere 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 autosuggest und Ihre Feldreferenzen werden beim Erstellen der nativen abgeleiteten Tabelle nicht überprüft. Stattdessen können Sie mit dem LookML-Validator die Felder überprüfen, auf die Sie in Ihrer nativen abgeleiteten Tabelle verweisen.
Wenn Sie jedoch die automatische Vervollständigung und sofortige Feldüberprüfung in der Looker-IDE aktivieren möchten oder ein komplexes LookML-Projekt mit mehreren Explores mit demselben Namen oder potenziellen zirkulären Verweisen haben, können Sie mit dem Parameter include auf den Speicherort der Explore-Definition verweisen.
Explores werden häufig in einer Modelldatei definiert, aber im Fall von nativen 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 mit dem Parameter include auf die Explore-Datei in der Ansichtsdatei Ihrer nativen abgeleiteten Tabelle verweisen möchten, 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 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 angegebenen 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 Beispiel oben wurde die Berechnung eines Werts für die gesamte Lebensdauer (lifetime value) nach Benutzer mit dem Explore order_items bewirkt. In der Ausgabetabelle entspricht total_revenue tatsächlich 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 nicht in eine Spaltendeklaration aufgenommen, wird angenommen, dass er <explore_name>.<field_name> ist. Wenn Sie beispielsweise explore_source: order_items angegeben haben, gilt Folgendes:
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 nicht im Explore des Parameters explore_source vorhanden sind. Jeder derived_column-Parameter hat einen sql-Parameter, der angibt, wie der Wert erstellt wird.
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. Es wird jedoch eine berechnete Spalte average_customer_order hinzugefügt, 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 über mehrere Zeilen hinweg. 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 verwenden, jedoch 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. Für den Parameter filters einer abgeleiteten Tabelle wird dieselbe Syntax verwendet 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 nativen 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 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 Schnelleres Arbeiten im Entwicklungsmodus.
Filtervorlagen verwenden
Mit bind_filters können Sie Filtervorlagen einfügen:
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.
Der Parameter from_field gibt das Feld an, aus dem der Filter abgerufen wird, sofern zur Laufzeit ein Filter vorhanden ist.
Im vorherigen bind_filters-Beispiel wendet Looker jeden Filter, der auf das Feld filtered_lookml_dt.filter_date angewendet wird, 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 Explore-Abfrage an eine Unterabfrage der nativen abgeleiteten Tabelle zu übergeben. Weitere Informationen finden Sie auf der Dokumentationsseite zu explore_source-Parametern.
Native abgeleitete Tabellen sortieren und begrenzen
Abgeleitete Tabellen können bei Bedarf auch sortiert und beschrä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 timezone-Werte.
Wenn Sie in der Definition der nativen abgeleiteten Tabelle keine Zeitzone angeben, wird für zeitbasierte Daten keine Zeitzonenkonvertierung durchgeführt. Stattdessen wird 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.