Abgeleitete Tabellen eröffnen eine Welt erweiterter analytischer Möglichkeiten, können aber eine Herausforderung bei der Herangehensweise, Implementierung und Fehlerbehebung darstellen. Dieses Rezeptbuch enthält die beliebtesten Anwendungsfälle für abgeleitete Tabellen in Looker.
Auf dieser Seite finden Sie die folgenden Beispiele:
- Täglich um 3 Uhr morgens eine Tabelle erstellen
- Neuen Daten an eine große Tabelle anhängen
- SQL-Fensterfunktionen verwenden
- Abgeleitete Spalten für berechnete Werte erstellen
- Optimierungsstrategien
- Optimierungen mithilfe von dynamischen Preis- und Verfügbarkeitsinformationen testen
UNION
zwei Tabellen- Summe einer Summe bilden (Messwert dimensionieren)
- Zusammenfassungstabellen mit aggregierter Markenbekanntheit
Ressourcen für abgeleitete Tabellen
In diesen Rezeptbüchern wird davon ausgegangen, dass Sie ein grundlegendes Verständnis von LookML und abgeleiteten Tabellen haben. Sie sollten mit dem Erstellen von Ansichten und dem Bearbeiten der Modelldatei vertraut sein. In den folgenden Ressourcen können Sie Ihr Wissen zu diesen Themen auffrischen:
- Abgeleitete Tabellen
- LookML – Begriffe und Konzepte
- Native abgeleitete Tabellen erstellen
derived_table
-Parameterreferenz- Abfragen im Cache speichern und PDTs mit Datengruppen neu erstellen
Täglich um 3 Uhr eine Tabelle erstellen
In diesem Beispiel werden die Daten jeden Tag um 2:00 Uhr erfasst. Die Ergebnisse einer Abfrage für diese Daten sind unabhängig davon identisch, ob sie um 3 Uhr morgens oder um 21 Uhr ausgeführt wird. Daher ist es sinnvoll, die Tabelle einmal täglich zu erstellen und die Nutzer die Ergebnisse aus einem Cache abrufen zu lassen.
Wenn Sie die Datengruppe in die Modelldatei aufnehmen, können Sie sie für mehrere Tabellen und Explores wiederverwenden. Diese Datengruppe enthält einen sql_trigger_value
-Parameter, der angibt, wann die abgeleitete Tabelle ausgelöst und neu erstellt werden soll.
Weitere Beispiele für Triggerausdrücke finden Sie in der Dokumentation zu sql_trigger_value
.
## in the model file
datagroup: standard_data_load {
sql_trigger_value: SELECT FLOOR(((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),'1970-01-01 00:00:00',SECOND)) - 60*60*3)/(60*60*24)) ;;
max_cache_age: "24 hours"
}
explore: orders {
…
Fügen Sie der derived_table
-Definition in der Ansichtsdatei den Parameter datagroup_trigger
hinzu und geben Sie den Namen der Datengruppe an, die Sie verwenden möchten. In diesem Beispiel lautet die Datengruppe standard_data_load
.
view: orders {
derived_table: {
indexes: ["id"]
datagroup_trigger: standard_data_load
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
…
}
Neue Daten an eine große Tabelle anhängen
Eine inkrementelle PDT ist eine persistente abgeleitete Tabelle, die von Looker erstellt wird. Dabei werden neue Daten an die Tabelle angehängt, anstatt dass die ganze Tabelle neu erstellt wird.
Im nächsten Beispiel bauen wir auf dem Beispiel für die Tabelle orders
auf, um zu zeigen, wie die Tabelle nach und nach erstellt wird. Täglich kommen neue Bestelldaten hinzu, die der vorhandenen Tabelle angefügt werden können, wenn Sie den Parameter increment_key
und den Parameter increment_offset
hinzufügen.
view: orders {
derived_table: {
indexes: ["id"]
increment_key: "created_at"
increment_offset: 3
datagroup_trigger: standard_data_load
distribution_style: all
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
dimension: id {
primary_key: yes
type: number
sql: ${TABLE}.id ;; }
…
}
Der Wert für increment_key
ist auf created_at
festgelegt. Das ist das Zeitinkrement, in dem in diesem Beispiel neue Daten abgefragt und an die PDT angehängt werden sollen.
Der Wert „increment_offset
“ ist auf „3
“ festgelegt, um die Anzahl der vorherigen Zeiträume anzugeben (in der Granularität des Inkrementschlüssels), die neu erstellt werden, um spät eintreffende Daten zu berücksichtigen.
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.
Wenn 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 Fensterfunktion enthält. Beim Verweisen auf Werte sollten Sie den Spaltennamen verwenden, der in Ihrer nativen abgeleiteten Tabelle definiert wurde.
Im folgenden Beispiel wird gezeigt, wie Sie eine native abgeleitete Tabelle mit den Spalten user_id
, order_id
und created_time
erstellen. Anschließend berechnen Sie mit einer abgeleiteten Spalte mit einer SQL ROW_NUMBER()
-Fensterfunktion eine Spalte, 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
}
}
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 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.
In diesem Beispiel wird eine Spalte vom Typ average_customer_order
erstellt, 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: users.id
}
column: lifetime_number_of_orders {
field: order_items.count
}
column: lifetime_customer_value {
field: order_items.total_profit
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
Optimierungsstrategien
Da PDTs in Ihrer Datenbank gespeichert werden, sollten Sie die PDTs mit den folgenden Strategien optimieren, je nach Unterstützung durch Ihren Dialekt:
Wenn Sie beispielsweise die Persistenz hinzufügen möchten, können Sie die PDT so einstellen, dass sie neu erstellt wird, wenn die Datengruppe orders_datagroup
ausgelöst wird. Anschließend können Sie sowohl für customer_id
als auch für first_order
Indizes hinzufügen, wie unten dargestellt:
view: customer_order_summary {
derived_table: {
explore_source: orders {
...
}
datagroup_trigger: orders_datagroup
indexes: ["customer_id", "first_order"]
}
}
Wenn Sie keinen Index (oder eine Entsprechung für Ihren Dialekt) hinzufügen, werden Sie von Looker gewarnt, dass Sie dies zur Steigerung der Abfrageleistung nachholen sollten.
PDTs zum Testen von Optimierungen verwenden
Mithilfe von PDTs können Sie verschiedene Indexierungs-, Verteilungs- und andere Optimierungsoptionen testen, ohne dass Sie viel Unterstützung von Ihren DBAs oder ETL-Entwicklern benötigen.
Angenommen, Sie haben eine Tabelle, möchten aber verschiedene Indizes testen. Die erste LookML-Datei für die Ansicht könnte so aussehen:
view: customer {
sql_table_name: warehouse.customer ;;
}
Wenn Sie Optimierungsstrategien testen möchten, können Sie mit dem Parameter indexes
Indizes zur LookML hinzufügen, wie unten gezeigt:
view: customer {
# sql_table_name: warehouse.customer
derived_table: {
sql: SELECT * FROM warehouse.customer ;;
persist_for: "8 hours"
indexes: [customer_id, customer_name, salesperson_id]
}
}
Stellen Sie eine einmalige Abfrage an die Ansicht, um die PDT zu generieren. Führen Sie dann Ihre Testabfragen aus und vergleichen Sie die Ergebnisse. Wenn die Ergebnisse positiv sind, können Sie Ihr DBA- oder ETL-Team bitten, die Indizes der ursprünglichen Tabelle hinzuzufügen.
UNION
zwei Tabellen
Sie können einen SQL-UNION
- oder UNION ALL
-Operator in beiden abgeleiteten Tabellen ausführen, sofern Ihr SQL-Dialekt dies unterstützt. Die Operatoren UNION
und UNION ALL
kombinieren die Ergebnismengen von zwei Abfragen.
In diesem Beispiel wird gezeigt, wie eine SQL-basierte abgeleitete Tabelle mit einer UNION
aussieht:
view: first_and_second_quarter_sales {
derived_table: {
sql:
SELECT * AS sales_records
FROM sales_records_first_quarter
UNION
SELECT * AS sales_records
FROM sales_records_second_quarter ;;
}
}
Die UNION
-Anweisung im sql
-Parameter erzeugt eine abgeleitete Tabelle, in der die Ergebnisse beider Abfragen kombiniert werden.
Der Unterschied zwischen UNION
und UNION ALL
besteht darin, dass bei UNION ALL
keine doppelten Zeilen entfernt werden. Bei der Verwendung von UNION
im Vergleich zu UNION ALL
sind Leistungsaspekte zu beachten, da der Datenbankserver zusätzliche Arbeit leisten muss, um die doppelten Zeilen zu entfernen.
Summe einer Summe berechnen (Messwert dimensionieren)
In SQL und damit auch in Looker können Sie eine Abfrage in der Regel nicht nach den Ergebnissen einer Aggregatfunktion gruppieren (in Looker als Messwerte dargestellt). Sie können nur nach nicht aggregierten Feldern gruppieren, die in Looker als Dimensionen dargestellt werden.
Wenn Sie nach einem Aggregat gruppieren möchten (z. B. die Summe einer Summe ermitteln), müssen Sie einen Messwert dimensionieren. Eine Möglichkeit dazu ist die Verwendung einer abgeleiteten Tabelle, die eine untergeordnete Abfrage des Aggregats erstellt.
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 (oder SQL-basierte) abgeleitete Tabelle wie folgt:
Klicken Sie auf das Zahnradmenü des Explores 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. Wenn Sie die Datei im Ordner erstellen möchten, klicken Sie auf das Menü eines Ordners und wählen Sie Ansicht erstellen aus.
Geben Sie der Ansicht einen aussagekräftigen Namen.
Optional können Sie Spaltennamen ändern, abgeleitete Spalten festlegen und Filter hinzufügen.
Aufschlüsselungstabellen mit Aggregatfunktion
In Looker stoßen Sie häufig auf sehr große Datensätze oder Tabellen, die für eine gute Leistung Aggregationstabellen oder Aggregationen erfordern.
Mit der Aggregationsfunktion von Looker können Sie aggregierte Tabellen mit verschiedenen Detaillierungsgraden, Dimensionen und Aggregationen vorab erstellen und Looker mitteilen, wie sie in vorhandenen Explores verwendet werden sollen. Bei Abfragen werden dann diese Zusammenfassungstabellen verwendet, sofern Looker dies für angebracht hält, ohne dass der Nutzer etwas tun muss. Dadurch wird die Abfragegröße reduziert, die Wartezeiten verkürzt und die Nutzerfreundlichkeit verbessert.
Im Folgenden sehen Sie eine sehr einfache Implementierung in einem Looker-Modell, um zu veranschaulichen, wie einfach die Aggregationserkennung sein kann. Angenommen, Sie haben eine hypothetische Flugtabelle in der Datenbank mit einer Zeile für jeden Flug, der über die FAA erfasst wird. Sie können diese Tabelle in Looker mit einer eigenen Ansicht und einem eigenen Explore modellieren. Im Folgenden finden Sie die LookML für eine zusammengefasste Tabelle, die Sie für das Explore definieren können:
explore: flights {
aggregate_table: flights_by_week_and_carrier {
query: {
dimensions: [carrier, depart_week]
measures: [cancelled_count, count]
}
materialization: {
sql_trigger_value: SELECT CURRENT-DATE;;
}
}
}
Mit dieser Aggregierungstabelle kann ein Nutzer das flights
-Explore abfragen. Looker verwendet dann automatisch die Aggregierungstabelle, um Abfragen zu beantworten. Eine ausführlichere Anleitung zu aggregierten Markenbekanntheitswerten finden Sie im Hilfeartikel Aggregierte Markenbekanntheit.