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 Dokumentationsseite 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 mithilfe des Parameters 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 den LookML-Code der abgeleiteten Tabelle auch über eine SQL Runner-Abfrage erstellen lassen, wie auf der Dokumentationsseite SQL Runner zum Erstellen abgeleiteter Tabellen verwenden 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. Führen Sie dann die folgenden Schritte aus, um den LookML-Code für die native abgeleitete Tabelle zu generieren:
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 den LookML-Code zum Erstellen einer nativen abgeleiteten Tabelle für das Explore anzusehen.
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 in der Looker IDE oben in der Liste der Projektdateien auf das + und wählen Sie Create View (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 einen Messwert von
type: countin einem Explore verwenden, werden die resultierenden Werte in der Visualisierung mit dem Ansichtsnamen und nicht mit dem Wort Count (Anzahl) gekennzeichnet. Verwenden Sie den Ansichtsnamen im Plural, um Verwechslungen zu vermeiden. Sie können den Ansichtsnamen ändern, indem Sie entweder in den Visualisierungseinstellungen unter Series die Option Show Full Field Name (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 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 mithilfe des angegebenen Explore-order_items und einiger der Felder dieses Explores (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
}
}
Mit include-Anweisungen verweisende Felder aktivieren
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 muss der Parameter include nicht verwendet werden, um auf die Datei zu verweisen, die die Explore-Definition enthält. Wenn die include-Anweisung nicht vorhanden ist, werden Feldnamen von der Looker-IDE beim Erstellen der nativen abgeleiteten Tabelle nicht automatisch vorgeschlagen und Ihre Feldverweise werden 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 automatischen Vorschläge 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 Zirkelbezügen haben, können Sie den Parameter include verwenden, um auf den Speicherort der Explore-Definition zu verweisen.
Explores werden häufig in einer Modelldatei definiert. Im Fall von nativen abgeleiteten Tabellen ist es jedoch sauberer, 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 auf die Explore-Datei in der Ansichtsdatei Ihrer nativen abgeleiteten Tabelle zu verweisen, achten Sie darauf, dass 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 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 vorherigen Beispiel wurde mit dem order_items-Explore eine Berechnung des Lifetime-Werts durch einen Nutzer erstellt. In der Ausgabetabelle ist total_revenue im Grunde die lifetime_customer_value eines Kunden.
Die Deklaration column unterstützt die Deklaration eines Ausgabenamens, der sich vom Eingabefeld unterscheidet. Mit dem folgenden Code wird Looker beispielsweise angewiesen, eine Ausgabespalte mit dem Namen lifetime_value aus dem Feld order_items.total_revenue zu erstellen:
column: lifetime_value {
field: order_items.total_revenue
}
Implizierte Spaltennamen
Wenn der Parameter field in einer Spaltendeklaration weggelassen wird, wird davon ausgegangen, dass er <explore_name>.<field_name> ist. Wenn Sie beispielsweise explore_source: order_items angegeben haben, dann gilt:
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 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. Nachdem die primäre Abfrage ausgeführt wurde, werden alle derived_column-Deklarationen in einem separaten Durchlauf 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 mithilfe einer abgeleiteten Spalte mit einer SQL ROW_NUMBER()-Fensterfunktion eine Spalte berechnet, die die Sequenznummer einer Kundenbestellung 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 vornehmen, aber nur Käufe der letzten 90 Tage berücksichtigen.
Dazu fügen Sie dem derived_table einfach einen Filter hinzu, mit dem nach Transaktionen der letzten 90 Tage gefiltert wird. Für den Parameter filters für eine abgeleitete Tabelle wird die gleiche Syntax verwendet, mit der Sie einen gefilterten Messwert erstellen.
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
}
}
Der WHERE-Klausel werden Filter hinzugefügt, wenn Looker den SQL-Code für die abgeleitete Tabelle schreibt.
Darüber hinaus können Sie den dev_filters-Unterparameter 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. 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 fungiert in Verbindung 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 einbinden:
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
Dies entspricht im Wesentlichen 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 bind_all_filters-Unterparameter von explore_source verwenden, um alle Laufzeitfilter aus einem Explore an eine Unterabfrage der nativen abgeleiteten Tabelle zu übergeben. Weitere Informationen finden Sie auf der Dokumentationsseite zum Parameter explore_source.
Native abgeleitete Tabellen sortieren und begrenzen
Sie können die abgeleiteten Tabellen bei Bedarf auch sortieren und einschränken:
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 in die von Ihnen angegebene Zeitzone konvertiert. Auf der Dokumentationsseite zu timezone-Werten finden Sie eine Liste der unterstützten Zeitzonen.
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.