Inkrementelle PDTs

In Looker werden persistente abgeleitete Tabellen (PDTs) in das Scratch-Schema Ihrer Datenbank geschrieben. Looker bleibt bestehen und erstellt eine PDT basierend auf ihrer Persistenzstrategie neu. Wenn die Neuerstellung einer PDT ausgelöst wird, erstellt Looker standardmäßig die ganze Tabelle neu.

Eine inkrementelle PDT ist eine PDT, die von Looker erstellt wird. Dabei werden neue Daten an die Tabelle angehängt, anstatt dass die ganze Tabelle neu erstellt wird:

Eine große Tabelle, in der die drei unteren Zeilen markiert sind, um zu sehen, dass eine kleine Anzahl neuer Zeilen zur Tabelle hinzugefügt wird.

Wenn Ihr Dialekt inkrementelle PDTs unterstützt, können Sie die folgenden Arten von PDTs in inkrementelle PDTs umwandeln:

Wenn Sie zum ersten Mal eine Abfrage mit einer inkrementellen PDT ausführen, erstellt Looker die ganze PDT, um die ersten Daten abzurufen. Bei einer großen Tabelle kann diese erste Erstellung sehr viel Zeit in Anspruch nehmen, wie im Falle jeder großen Tabelle. Nach dem Erstellen der ersten Tabelle sind weitere Erstellungen inkrementell und nehmen weniger Zeit in Anspruch, wenn die inkrementelle PDT strategisch eingestellt wurde.

Beachten Sie Folgendes für inkrementelle PDTs:

  • Inkrementelle PDTs werden nur für PDTs unterstützt, die eine triggerbasierte Persistenzstrategie verwenden (datagroup_trigger, sql_trigger_value oder interval_trigger). Sie werden nicht für PDTs unterstützt, die die Persistenzstrategie persist_for verwenden.
  • Bei SQL-basierten PDTs muss die Tabellenabfrage mit dem Parameter sql definiert werden, damit sie als inkrementelle PDT verwendet werden kann. SQL-basierte PDTs, die mit dem Parameter sql_create oder dem Parameter create_process definiert werden, können nicht inkrementell erstellt werden. Wie Sie in Beispiel 1 auf dieser Seite sehen, nutzt Looker einen INSERT- oder MERGE-Befehl, um die Inkremente für eine inkrementelle PDT zu erstellen. Die abgeleitete Tabelle kann nicht über benutzerdefinierte DDL-Anweisungen (Data Definition Language) definiert werden, da Looker nicht in der Lage wäre zu bestimmen, welche DDL-Anweisungen für das Erstellen eines akkuraten Inkrements erforderlich wären.
  • Die Quelltabelle der inkrementellen PDT muss für zeitbasierte Abfragen optimiert sein. Insbesondere muss die zeitbasierte Spalte, die für den Inkrementschlüssel verwendet wird, eine Optimierungsstrategie haben, z. B. Partitionierung, Sortierschlüssel, Indexe oder eine andere für Ihren Dialekt unterstützte Optimierungsstrategie. Eine Optimierung der Quelltabelle wird stark empfohlen. Mit jeder Aktualisierung der inkrementellen Tabelle fragt Looker die Quelltabelle ab, um die neuesten Werte der zeitbasierten Spalte zu bestimmen, die für den Inkrementschlüssel verwendet wird. Wenn die Quelltabelle nicht für diese Abfragen optimiert ist, kann die Abfrage der neuesten Werte durch Looker langsam und teuer sein.

Inkrementelle PDT definieren

Mit den folgenden Parametern können Sie eine PDT in eine inkrementelle PDT umwandeln:

  • increment_key (erforderlich, um die PDT als inkrementelle PDT zu definieren): Definiert den Zeitraum, für den neue Datensätze abgefragt werden sollen.
  • {% incrementcondition %} Liquid-Filter (erforderlich, um eine SQL-basierte PDT in eine inkrementelle PDT umzuwandeln; nicht für LookML-basierte PDTs geeignet): Verbindet den inkrementellen Schlüssel mit der Datenbankzeitspalte, auf der der inkrementelle Schlüssel basiert. Weitere Informationen finden Sie auf der Dokumentationsseite zu increment_key.
  • increment_offset (optional): Eine Ganzzahl, die die Anzahl vorheriger Zeiträume definiert (in der Granularität des Inkrementschlüssels), die für jeden inkrementellen Build neu erstellt werden. Der Parameter increment_offset eignet sich im Fall spät eintreffender Daten, wenn vorherige Zeiträume neue Daten enthalten könnten, die beim ursprünglichen Erstellen und Anhängen an die PDT im entsprechenden Inkrement noch nicht enthalten waren.

Auf der Seite mit der Parameterdokumentation für increment_key finden Sie Beispiele zum Erstellen inkrementeller PDTs aus persistenten nativen abgeleiteten Tabellen, persistenten SQL-basierten abgeleiteten Tabellen und aggregierten Tabellen.

Hier ist ein einfaches Beispiel einer inkrementellen LookML-basierten PDT:

view: flights_lookml_incremental_pdt {
  derived_table: {
    indexes: ["id"]
    increment_key: "departure_date"
    increment_offset: 3
    datagroup_trigger: flights_default_datagroup
    distribution_style: all
    explore_source: flights {
      column: id {}
      column: carrier {}
      column: departure_date {}
    }
  }

  dimension: id {
    type: number
  }
  dimension: carrier {
    type: string
  }
   dimension: departure_date {
    type: date
  }
}

Diese Tabelle wird vollständig erstellt, wenn zum ersten Mal eine Abfrage darin erfolgt. Danach wird die PDT in Schritten von einem Tag (increment_key: departure_date) über drei Tage hinweg neu erstellt (increment_offset: 3).

Der Inkrementenschlüssel basiert auf der Dimension departure_date, also dem date-Zeitraum aus der Dimensionsgruppe departure. Eine Übersicht über die Funktionsweise von Dimensionsgruppen finden Sie auf der Seite mit der Parameterdokumentation für dimension_group. Die Dimensionsgruppe und der Zeitraum werden in der Ansicht flights definiert, die der explore_source für diese PDT ist. So wird die Dimensionsgruppe departure in der Ansichtsdatei flights definiert:

...
  dimension_group: departure {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      year
    ]
    sql: ${TABLE}.dep_time ;;
  }
...

Interaktion von Inkrementparametern und Persistenzstrategie

Die Einstellungen für increment_key und increment_offset einer PDT sind unabhängig von der Persistenzstrategie der PDT:

  • Die Persistenzstrategie einer inkrementellen PDT bestimmt nur, wann die PDT inkrementiert. Der PDT-Builder ändert die inkrementelle PDT nur, wenn die Persistenzstrategie der Tabelle ausgelöst wird oder die PDT manuell mit der Option Abgeleitete Tabellen neu erstellen und ausführen in einem Explore ausgelöst wird.
  • Bei der Erhöhung der PDT ermittelt der PDT-Builder, wann die neuesten Daten zuvor zur Tabelle hinzugefügt wurden, und zwar in Bezug auf das aktuelle Zeitinkrement (der durch den Parameter increment_key definierte Zeitraum). Basierend darauf kürzt der PDT-Generator die Daten zum Beginn des jüngsten Zeitinkrements in der Tabelle und erstellt dann das neueste Inkrement von dort.
  • Wenn die PDT einen increment_offset-Parameter hat, erstellt der PDT-Builder auch die Anzahl der vorherigen Zeiträume neu, die im Parameter increment_offset angegeben wurden. Die vorherigen Zeiträume gehen zurück, beginnend mit dem Beginn des aktuellen Zeitinkrements, also des Zeitraums, der durch den Parameter increment_key definiert wird.

Die folgenden Beispielszenarien veranschaulichen, wie inkrementelle PDTs aktualisiert werden. Sie zeigen die Interaktion von increment_key, increment_offset und die Persistenzstrategie.

Beispiel 1

Dieses Beispiel verwendet eine PDT mit diesen Eigenschaften:

  • Inkrementeller Schlüssel: „Datum“
  • Inkrementeller Offset: 3
  • Persistenzstrategie: wird einmal im Monat am ersten Tag des Monats ausgelöst.

So wird diese Tabelle aktualisiert:

  • Bei einer monatlichen Persistenzstrategie wird die Tabelle automatisch ein Mal im Monat erstellt. Das bedeutet, dass etwa am 1. Juni die letzte Zeile der Tabelle am 1. Mai hinzugefügt worden ist.
  • Da diese Tabelle einen auf dem Datum basierten Inkrementschlüssel verwendet, kürzt der PDT-Generator den 1. Mai zurück zum Beginn des Tages und erstellt die Daten für den 1. Mai neu bis zum aktuellen Tag, dem 1. Juni.
  • Außerdem hat diese PDT einen inkrementellen Offset von 3. Der PDT-Generator erstellt also auch die Daten der vorherigen drei Zeiträume (Tage) vor dem 1. Mai neu. Es werden also Daten neu erstellt für den 28., 29. und 30. Mai und bis zum 1. Juni, dem aktuellen Tag.

Diesen SQL-Befehl führt der PDT-Generator am 1. Juni aus, um die Reihen der vorhandenen PDT zu bestimmen, die neu erstellt werden müssen:

## Example SQL for BigQuery:
SELECT FORMAT_TIMESTAMP('%F %T',TIMESTAMP_ADD(MAX(pdt_name),INTERVAL -3 DAY))

## Example SQL for other dialects:
SELECT CAST(DATE_ADD(MAX(pdt_name),INTERVAL -3 DAY) AS CHAR)

Und diesen SQL-Befehl führt der PDT-Generator am 1. Juni aus, um das neueste Inkrement zu erstellen:

## Example SQL for BigQuery:

MERGE INTO [pdt_name] USING (SELECT [columns]
   WHERE created_at >= TIMESTAMP('4/28/21 12:00:00 AM'))
   AS tmp_name ON FALSE
WHEN NOT MATCHED BY SOURCE AND created_date >= TIMESTAMP('4/28/21 12:00:00 AM')
   THEN DELETE
WHEN NOT MATCHED THEN INSERT [columns]

## Example SQL for other dialects:

START TRANSACTION;
DELETE FROM [pdt_name]
   WHERE created_date >= TIMESTAMP('4/28/21 12:00:00 AM');
INSERT INTO [pdt_name]
   SELECT [columns]
   FROM [source_table]
   WHERE created_at >= TIMESTAMP('4/28/21 12:00:00 AM');
COMMIT;

Beispiel 2

Dieses Beispiel verwendet eine PDT mit diesen Eigenschaften:

  • Persistenzstrategie: wird einmal täglich ausgelöst.
  • Anstiegsschlüssel: Monat
  • Inkrementeller Offset: 0

So wird diese Tabelle am 1. Juni aktualisiert:

  • Bei einer täglichen Persistenzstrategie wird die Tabelle automatisch ein Mal im Tag erstellt. Am 1. Juni wird die letzte Zeile der Tabelle am 31. Mai hinzugefügt worden sein.
  • Da diese Tabelle einen auf dem Monat basierten Inkrementschlüssel verwendet, kürzt der PDT-Generator den 31. Mai zurück zum Beginn des Monats und erstellt die Daten für den gesamten Mai neu bis zum aktuellen Tag, einschließlich des 1. Junis.
  • Da diese PDT keinen inkrementellen Offset hat, werden keine vorherigen Zeiträume neu erstellt.

So wird diese Tabelle am 2. Juni aktualisiert:

  • Am 2. Juni wird die letzte Zeile der Tabelle am 1. Juni hinzugefügt worden sein.
  • Da der PDT-Generator bis zum Anfang des Monats Juni zurück kürzt und dann die Daten beginnend mit dem 1. Juni bis zum aktuellen Tag neu erstellt, werden nur die Daten für den 1. und 2. Juni neu erstellt.
  • Da diese PDT keinen inkrementellen Offset hat, werden keine vorherigen Zeiträume neu erstellt.

Beispiel 3

Dieses Beispiel verwendet eine PDT mit diesen Eigenschaften:

  • Anstiegsschlüssel: Monat
  • Inkrementeller Offset: 3
  • Persistenzstrategie: wird einmal täglich ausgelöst.

Dieses Szenario zeigt eine schlechte Einstellung für eine inkrementelle PDT, da es sich um eine täglich ausgelöste PDT mit einem Offset von drei Monaten handelt. Jeden Tag werden die Daten von mindestens drei Monaten neu erstellt, was eine sehr ineffiziente Nutzung einer inkrementellen PDT darstellt. Die Untersuchung dieses Szenarios ist jedoch interessant, um die Funktionsweise einer inkrementellen PDT zu verstehen.

So wird diese Tabelle am 1. Juni aktualisiert:

  • Bei einer täglichen Persistenzstrategie wird die Tabelle automatisch ein Mal im Tag erstellt. Am 1. Juni etwa wird die letzte Zeile der Tabelle am 31. Mai hinzugefügt worden sein.
  • Da diese Tabelle einen auf dem Monat basierten Inkrementschlüssel verwendet, kürzt der PDT-Generator den 31. Mai zurück zum Beginn des Monats und erstellt die Daten für den gesamten Mai neu bis zum aktuellen Tag, einschließlich des 1. Junis.
  • Außerdem hat diese PDT einen inkrementellen Offset von 3. Das bedeutet, dass der PDT-Builder auch die Daten der vorherigen drei Zeiträume (Monate) vor Mai neu erstellt. Das Ergebnis ist, dass die Daten von Februar, März, April und bis zum aktuellen Tag, dem 1. Juni, neu erstellt werden.

So wird diese Tabelle am 2. Juni aktualisiert:

  • Am 2. Juni wird die letzte Zeile der Tabelle am 1. Juni hinzugefügt worden sein.
  • Der PDT-Generator kürzt den Monat zurück zum 1. Juni und erstellt die Daten für den Monat Juni neu, einschließlich des 2. Junis.
  • Durch den inkrementellen Offset werden außerdem die Daten der drei Monate vor Juni neu erstellt. Das Ergebnis ist die Neuerstellung der Daten von März, April, Mai und bis zum aktuellen Tag, dem 2. Juni.

Test einer inkrementellen PDT im Entwicklungsmodus

Bevor Sie eine neue inkrementelle PDT für Ihre Produktionsumgebung entwickeln, können Sie die PDT testen und sicherstellen, dass sie erstellt und inkrementiert. Für den Test einer inkrementellen PDT im Entwicklungsmodus:

  1. Erstellen Sie ein Explore für die PDT:

    • Verwenden Sie in einer verknüpften Modelldatei den Parameter include, um die Ansichtsdatei der PDT in die Modelldatei aufzunehmen.
    • Verwenden Sie in derselben Modelldatei den Parameter explore, um ein Explore für die Ansicht der inkrementellen PDT zu erstellen.
     include: "/views/e_faa_pdt.view"
 explore: e_faa_pdt {}

  2. Öffnen Sie die Explore der PDT. Klicken Sie dazu auf die Schaltfläche Dateiaktionen ansehen und wählen Sie dann einen Explore-Namen aus.

  1. Wählen Sie im Explore einige Dimensionen oder Messwerte aus und klicken Sie auf Ausführen. Looker erstellt dann die gesamte PDT. Ist dies die erste Abfrage mit einer inkrementellen PDT, erstellt der PDT-Generator die gesamte PDT, um die ersten Daten abzurufen. Bei einer großen Tabelle kann diese erste Erstellung sehr viel Zeit in Anspruch nehmen, wie im Falle jeder großen Tabelle.

  2. Mit den folgenden Möglichkeiten können Sie die Erstellung der ersten PDT bestätigen:

    • Mit der Berechtigung see_logs können Sie im PDT-Ereignisprotokoll prüfen, ob die Tabelle erstellt wurde. Wenn Sie im PDT-Ereignisprotokoll keine PDT-Erstellungsereignisse sehen, prüfen Sie die Statusinformationen oben im Explore des PDT-Ereignisprotokolls. Wird dort „Aus Zwischenspeicher“ angezeigt, wählen Sie Cache leeren und aktualisieren aus, um aktuellere Informationen zu erhalten.
    • Andernfalls können Sie sich die Kommentare auf dem Tab SQL in der Leiste Daten des Explores ansehen. Auf dem Tab SQL werden die Abfrage und die Aktionen angezeigt, die ausgeführt werden, wenn Sie die Abfrage im Explore ausführen. Wenn in den Kommentaren auf dem Tab SQL beispielsweise -- generate derived table e_incremental_pdt steht, wird diese Aktion ausgeführt, wenn Sie auf Ausführen klicken.

  3. Nachdem Sie den ersten Build der PDT erstellt haben, fordern Sie mit der Funktion Abgeleitete Tabellen neu erstellen und im explorativen Analysetool die Option „Ausführen“.

  4. Sie können mit denselben Methoden wie zuvor die inkrementelle Erstellung der PDT bestätigen:

    • Wenn Sie die Berechtigung see_logs haben, können Sie im PDT-Ereignisprotokoll create increment complete-Ereignisse für die inkrementelle PDT aufrufen. Wenn Sie dieses Ereignis im PDT-Ereignisprotokoll nicht sehen und der Abfragestatus „Aus Zwischenspeicher“ lautet, wählen Sie Zwischenspeicher leeren und aktualisieren aus, um aktuellere Informationen zu erhalten.
    • Sehen Sie sich die Kommentare auf dem Tab SQL in der Leiste Daten des Explores an. In diesem Fall zeigen die Kommentare, dass die PDT inkrementiert wurde. Beispiel: -- increment persistent derived table e_incremental_pdt to generation 2

  5. Wenn Sie überprüft haben, dass die PDT korrekt erstellt und inkrementiert wird, und Sie das spezielle Explore für die PDT nicht behalten möchten, können Sie die Parameter explore und include der PDT aus Ihrer Modelldatei entfernen oder kommentieren.

Nachdem die PDT im Entwicklungsmodus erstellt wurde, wird dieselbe Tabelle für die Produktion verwendet, sobald Sie Ihre Änderungen bereitstellen, es sei denn, Sie führen weitere Änderungen an der Definition der Tabelle durch. Weitere Informationen finden Sie auf der Seite Abgeleitete Tabellen in Looker im Abschnitt Persistierte Tabellen im Entwicklungsmodus.

Unterstützte Datenbankdialekte für inkrementelle PDTs

Damit Looker inkrementelle PDTs in Ihrem Looker-Projekt unterstützen kann, muss Ihr Datenbankdialekt DDL-Befehle (Data Definition Language) unterstützen, mit denen Zeilen gelöscht und eingefügt werden können.

In der folgenden Tabelle ist zu sehen, welche Dialekte in der neuesten Version von Looker inkrementelle PDTs unterstützen. Für Databricks werden inkrementelle PDTs nur in Databricks-Version 12.1 und höher unterstützt:

Dialekt Unterstützt?
Actian Lawine
Nein
Amazon Athena
Nein
Amazon Aurora MySQL
Nein
Amazon Redshift
Ja
Apache Druid
Nein
Apache Druid 0.13 oder höher
Nein
Apache Druid 0.18 und höher
Nein
Apache Hive 2.3 und höher
Nein
Apache Hive 3.1.2 und höher
Nein
Apache Spark 3 und höher
Nein
ClickHouse
Nein
Cloudera Impala 3.1 und höher
Nein
Cloudera Impala 3.1+ mit nativem Treiber
Nein
Cloudera Impala mit nativem Treiber
Nein
DataVirtuality
Nein
Databricks
Ja
Denodo 7
Nein
Denodo 8
Nein
Dremio
Nein
Dremio 11+
Nein
Exasol
Nein
Firebolt
Nein
Google BigQuery Legacy SQL
Nein
Google BigQuery Standard SQL
Ja
Google Cloud PostgreSQL
Ja
Google Cloud SQL
Nein
Google Spanner
Nein
Greenplum
Ja
HyperSQL
Nein
IBM Netezza
Nein
MariaDB
Nein
Microsoft Azure PostgreSQL
Ja
Microsoft Azure SQL Database
Nein
Microsoft Azure Synapse Analytics
Ja
Microsoft SQL Server 2008 oder höher
Nein
Microsoft SQL Server 2012 und höher
Nein
Microsoft SQL Server 2016
Nein
Microsoft SQL Server 2017 und höher
Nein
MongoBI
Nein
MySQL
Ja
MySQL 8.0.12 und höher
Ja
Oracle
Nein
Oracle ADWC
Nein
PostgreSQL 9.5 und höher
Ja
PostgreSQL vor Version 9.5
Ja
PrestoDB
Nein
PrestoSQL
Nein
SAP HANA 2 und höher
Nein
SingleStore
Nein
SingleStore 7 und höher
Nein
Snowflake
Ja
Teradata
Nein
Trino
Nein
Vektor
Nein
Vertica
Ja