Inkrementelle PDTs

In Looker werden persistente abgeleitete Tabellen (PDTs) in das Scratch-Schema Ihrer Datenbank geschrieben. PDTs werden basierend auf ihrer Persistenzstrategie in Looker persistent gemacht und neu erstellt. 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 hervorgehoben sind, um zu zeigen, dass der Tabelle eine kleine Anzahl neuer Zeilen hinzugefügt wird.

Wenn Ihr Dialekt inkrementelle PDTs unterstützt, können Sie die folgenden Typen 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 eingerichtet 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, um als inkrementelle PDT verwendet zu werden. SQL-basierte PDTs, die mit dem Parameter sql_create oder dem Parameter create_process definiert sind, 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 Inkrementenschlüssel verwendet wird, eine Optimierungsstrategie haben, z. B. Partitionierung, Sortierschlüssel, Indexe oder eine andere Optimierungsstrategie, die für Ihren Dialekt unterstützt wird. 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. Wurde die Quelltabelle für diese Abfragen nicht optimiert, kann die Abfrage der neuesten Werte durch Looker langsam und teuer ausfallen.

Inkrementelle PDT definieren

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

  • increment_key (erforderlich, um die PDT in eine inkrementelle PDT umzuwandeln): 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 angibt (in der Granularität des Inkrementschlüssels), die für jeden inkrementellen Aufbau 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 Inkrementen von einem Tag (increment_key: departure_date) neu erstellt, bis drei Tage zurück (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 Zeitrahmen werden beide in der flights-Ansicht definiert, die die 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.
  • Wenn die PDT inkrementiert, bestimmt der PDT-Generator, wann die neuesten Daten zuvor zur Tabelle hinzugefügt wurden, im Bezug auf das aktuellste Inkrement (der vom Parameter increment_key bestimmte 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.
  • Hat die PDT einen increment_offset-Parameter, erstellt der PDT-Generator auch die Anzahl der vorherigen Zeiträume neu, die im increment_offset-Parameter angegeben sind. Die vorherigen Zeiträume reichen zurück beginnend am Anfang des aktuellsten Zeitinkrements (dem Zeitraum, der durch den increment_key-Parameter definiert wird).

Die folgenden Beispielszenarien demonstrieren die Aktualisierung inkrementeller PDTs durch Zeigen der Interaktion von increment_key, increment_offset und 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 basierenden 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. April und bis zum 1. Juni, dem aktuellen Tag.

Diesen SQL-Befehl führt der PDT-Generator am 1. Juni aus, um die Zeilen 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: einmal täglich ausgelöst
  • Inkrementeller Schlüssel: „month“
  • 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 basierenden 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:

  • Inkrementeller Schlüssel: „month“
  • Inkrementeller Offset: 3
  • Persistenzstrategie: 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 basierenden 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-Generator auch die Daten der vorherigen drei Zeiträume (Monate) vor dem Monat Mai neu erstellt. Das Ergebnis ist die Neuerstellung der Daten von Februar, März, April und bis zum aktuellen Tag, dem 1. Juni.

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.
  • Aufgrund des Inkrement-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. So testen Sie eine inkrementelle 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.
    • Erstellen Sie in derselben Modelldatei mit dem Parameter explore ein Explore für die Ansicht der inkrementellen PDT.
     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 den Namen eines Explores 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:

    • Wenn Sie die Berechtigung see_logs haben, 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.
    • Außerdem können Sie in den Kommentaren der SQL-Registerkarte in der Leiste Daten des Explores klicken. Auf dem Tab SQL sehen Sie die Abfrage und die Aktionen, 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 die erste Version der PDT erstellt haben, können Sie eine inkrementelle Erstellung der PDT mit der Option Abgeleitete Tabellen neu erstellen und ausführen im Explore anfordern.

  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 als Abfragestatus „Aus Zwischenspeicher“ angezeigt wird, 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 Avalanche
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 und höher mit nativem Treiber
Nein
Cloudera Impala mit nativem Treiber
Nein
DataVirtuality
Nein
Databricks
Ja
Denodo 7
Nein
Denodo 8
Nein
Dremio
Nein
Dremio 11 und höher
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-Datenbank
Nein
Microsoft Azure Synapse Analytics
Ja
Microsoft SQL Server 2008 und 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+
Nein
Snowflake
Ja
Teradata
Nein
Trino
Nein
Vektor
Nein
Vertica
Ja