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 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 derived_table Parameter auf Ansichtsebene definiert. Bei nativen abgeleiteten Tabellen müssen Sie jedoch keine SQL-Abfrage erstellen. Stattdessen geben Sie mit dem explore_source Parameter das Explore an, auf dem die abgeleitete Tabelle, die gewünschten Spalten und andere erwünschte Merkmale basieren sollen.
Sie können Looker auch 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. Führen Sie dann die folgenden Schritte aus, um die LookML für die native abgeleitete Tabelle zu generieren:
Wählen Sie das Zahnradmenü Explore-Aktionen und dann LookML abrufen aus.
Klicken Sie auf die Registerkarte 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 Entwicklermodus 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 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 eine Measure vom Typ
type: countverwenden, werden die Ergebniswerte mit dem Ansichtsnamen und nicht mit dem Wort Anzahl bezeichnet. Um Verwechslungen zu vermeiden, verwenden Sie den Plural Ihres Ansichtsnamens. Sie können den Ansichtsnamen ändern, indem Sie in den Visualisierungseinstellungen unter Reihen die Option Vollständigen Feldnamen anzeigen auswählen oder den Parameterview_labelmit einer Pluralversion Ihres Ansichtsnamens verwenden.
Native abgeleitete Tabelle in LookML definieren
Unabhängig davon, ob Sie in SQL oder nativem LookML deklarierte abgeleitete Tabellen verwenden, ist die Ausgabe einer derived_table's-Abfrage stets 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. Es ist nicht nötig, die Abfrage manuell in SQL zu schreiben. Stattdessen erstellt Looker die Abfrage für Sie, und zwar mithilfe des angegebenen Explores order_items und einigen Feldern 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
}
}
include-Anweisungen verwenden, um Verweise auf Felder zu ermöglichen
In der Ansichtsdatei der nativen abgeleiteten Tabelle verwenden Sie den explore_source Parameter, um auf ein 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 den include Parameter nicht verwenden, um auf die Datei zu verweisen, die die Definition des Explores enthält. autosuggest Stattdessen können Sie den LookML-Validator verwenden, um die Felder zu überprüfen, auf die Sie in Ihrer nativen abgeleiteten Tabelle verweisen.
Wenn Sie jedoch die automatische Vorschlagsfunktion 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 potenziellen Zirkelbezügen haben, können Sie mit dem Parameter include auf den Speicherort der Definition des Explores verweisen.
Explores werden häufig in einer Modelldatei definiert. Bei nativen abgeleiteten Tabellen ist es jedoch übersichtlicher, eine separate Datei für das Explore zu erstellen. LookML-Explore-Dateien haben die .explore.lkml Dateiendung, wie in der Dokumentation zum Erstellen von Explore-Dateien 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 in der Ansichtsdatei Ihrer nativen abgeleiteten Tabelle auf die Explore-Datei 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 gezeigt, verwenden Sie column, um die Ausgabespalten der abgeleiteten Tabelle anzugeben.
Spaltennamen angeben
Für die Spalte user_id stimmt der Spaltenname mit dem Namen des angegebenen Felds im ursprünglichen Explore überein.
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 tatsächlich dem lifetime_customer_value eines Kunden.
Die Deklaration column unterstützt die Angabe eines Ausgabenamens, der anders als das Eingabefeld lautet. Der folgende Code weist Looker beispielsweise an, „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
Wird der field Parameter nicht in eine Spaltendeklaration aufgenommen, wird angenommen, dass er <explore_name>.<field_name> ist. Wenn Sie beispielsweise explore_source: order_items angegeben haben, dann
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.
Ihre sql-Berechnung kann alle Spalten verwenden, 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.
Das Beispiel unten erzeugt dieselbe abgeleitete Tabelle wie das vorherige Beispiel, jedoch mit dem Unterschied, dass die berechnete Spalte average_customer_order hinzugefügt wird. Diese wird anhand der Spalten lifetime_customer_value und lifetime_number_of_orders in der nativen abgeleiteten Tabelle berechnet.
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 Berechnungen für mehrere Zeilen. Nach Ausführung der Primärabfrage 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 durchführen, aber nur Käufe aus den letzten 90 Tagen berücksichtigen.
Fügen Sie einfach einen Filter zur derived_table hinzu, der nach Transaktionen in den letzten 90 Tagen filtert. Für den filters Parameter für eine abgeleitete Tabelle wird dieselbe Syntax verwendet wie zum Erstellen einer gefilterten Measure.
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 dev_filters Unterparameter von explore_source mit einer nativen abgeleiteten Tabelle verwenden. Mit dem Parameter dev_filters können Sie Filter festlegen, die Looker nur auf Entwicklungsversionen der abgeleiteten Tabelle anwendet. So können Sie kleinere, gefilterte Versionen einer Tabelle erstellen, um Wiederholungen und Tests durchzuführen, ohne zu warten, bis nach einer Änderung die vollständige Tabelle erstellt wurde.
Der dev_filters Parameter funktioniert in Verbindung mit dem filters Parameter, 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
Sie können bind_filters verwenden, um Filtervorlagen einzufügen:
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
Das ist im Wesentlichen dasselbe wie die 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.
from_field gibt das Feld an, aus dem der Filter abgerufen werden soll, wenn zur Laufzeit ein Filter vorhanden ist.
Im vorherigen bind_filters-Beispiel übernimmt Looker alle Filter, die auf das Feld filtered_lookml_dt.filter_date angewendet werden, und wendet sie 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 explore_source Parameter.
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 zu den timezone Werten.
Wenn Sie in der Definition der nativen abgeleiteten Tabelle keine Zeitzone festlegen, führt die native abgeleitete Tabelle keine Zeitzonenumwandlung für zeitbasierte Daten durch. Stattdessen wird für zeitbasierte Daten standardmäßig Ihre Datenbank-Zeitzone 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.