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 aufgebaut 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 nur wenige neue Zeilen hinzugefügt werden.

Wenn Ihr Dialekt inkrementelle PDTs unterstützt, können Sie die folgenden Typen von PDT in eine inkrementelle PDT 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 zu inkrementellen PDTs:

  • Inkrementelle PDTs werden nur für PDTs unterstützt, die eine triggerbasierte Persistenzstrategie (datagroup_trigger, sql_trigger_value oder interval_trigger) verwenden. Inkrementelle PDTs 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. Die zeitbasierte Spalte, die für den Inkrementschlüssel verwendet wird, muss 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 dringend 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, damit die PDT eine inkrementelle PDT ist): 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 anwendbar auf LookML-basierte PDTs): 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 increment_offset-Parameter 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 Dokumentationsseite zum Parameter increment_key finden Sie Beispiele, in denen gezeigt wird, wie Sie inkrementelle PDTs aus persistenten nativen abgeleiteten Tabellen, persistenten SQL-basierten abgeleiteten Tabellen und aggregierten Tabellen erstellen.

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 Steigerungsschlüssel basiert auf der Dimension departure_date, die eigentlich der Zeitrahmen date aus der Dimensionsgruppe departure ist. Eine Übersicht über die Funktionsweise von Dimensionsgruppen finden Sie auf der Dokumentationsseite zum Parameter dimension_group. Die Dimensionsgruppe und der Zeitrahmen werden beide in der flights-Ansicht definiert, welche 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 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. Die inkrementelle PDT wird vom PDT-Builder nur geändert, wenn die Persistenzstrategie der Tabelle ausgelöst wird oder wenn die PDT manuell mit der Option Abgeleitete Tabellen neu erstellen und ausführen in einem Explore ausgelöst wird.
  • Wenn die PDT inkrementiert wird, 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 veranschaulichen, wie inkrementelle PDTs aktualisiert werden. Dabei wird die Interaktion von increment_key, increment_offset und der Persistenzstrategie gezeigt.

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: ein Mal am Tag ausgelöst
  • Inkrementeller Schlü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:

  • Inkrementeller Schlüssel: Monat
  • Inkrementeller Offset: 3
  • Persistenzstrategie: ein Mal am Tag 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-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.
  • 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 zugehörigen Modelldatei den Parameter include, um die Ansichtsdatei der PDT in die Modelldatei einzufügen.
    • 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 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:

    • Wenn Sie die Berechtigung see_logs haben, können Sie im PDT-Ereignisprotokoll nachsehen, 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, um aktuellere Informationen zu erhalten.
    • Außerdem können Sie in den Kommentaren der SQL-Registerkarte in der Leiste Daten des Explores nachsehen. 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 Version der PDT erstellen lassen, indem Sie im Explore die Option Abgeleitete Tabellen neu erstellen und ausführen verwenden.

  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 dieses Ereignis im PDT-Ereignisprotokoll nicht angezeigt wird 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 erstellt wird und die Werte richtig erhöht werden, können Sie die explore- und include-Parameter der PDT aus Ihrer Modelldatei entfernen oder auskommentieren, wenn Sie die spezielle Explore-Funktion für die PDT nicht beibehalten möchten.

Nach der Erstellung der PDT im Entwicklungsmodus wird dieselbe Tabelle für die Produktion genutzt, sobald Sie Ihre Änderungen bereitstellen, es sei denn, Sie führen weitere Änderungen an der Definition der Tabelle durch. Weitere Informationen finden Sie im Abschnitt Persistente Tabellen im Entwicklermodus auf der Dokumentationsseite Abgeleitete Tabellen in Looker.

Fehlerbehebung bei inkrementellen PDTs

In diesem Abschnitt werden einige häufige Probleme beschrieben, die bei der Verwendung inkrementeller PDTs auftreten können, sowie Schritte zur Fehlerbehebung und Behebung dieser Probleme.

Inkrementelle PDT kann nach Schemaänderung nicht erstellt werden

Wenn Ihre inkrementelle abgeleitete Tabelle auf SQL basiert und der Parameter sql ein Platzhalterzeichen wie SELECT * enthält, können Änderungen an Ihrem zugrunde liegenden Datenbankschema (z. B. Hinzufügen, Entfernen oder Ändern des Datentyps von Spalten) dazu führen, dass die abgeleitete Tabelle mit dem folgenden Fehler fehlschlägt:

SQL Error in incremental PDT: Query execution failed

Bearbeiten Sie die SELECT-Anweisung im Parameter sql, um dieses Problem zu beheben. Wählen Sie stattdessen einzelne Spalten aus. Wenn Ihre SELECT-Anweisung beispielsweise SELECT * lautet, ändern Sie sie in SELECT column1, column2, ....

Wenn sich Ihr Schema ändert und Sie Ihren inkrementellen PAT von Grund auf neu erstellen möchten, verwenden Sie den API-Aufruf start_pdt_build und fügen Sie den Parameter full_force_incremental ein.

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, Datendefinitionssprache) unterstützen, mit denen Zeilen gelöscht und eingefügt werden können.

In der folgenden Tabelle ist zu sehen, welche Dialekte inkrementelle PDTs in der aktuellen Version von Looker 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
Amazon Redshift 2.1+
Ja
Amazon Redshift Serverless 2.1+
Ja
Apache Druid
Nein
Apache Druid 0.13+
Nein
Apache Druid 0.18+
Nein
Apache Hive 2.3+
Nein
Apache Hive 3.1.2+
Nein
Apache Spark 3+
Nein
ClickHouse
Nein
Cloudera Impala 3.1+
Nein
Cloudera Impala 3.1+ with Native Driver
Nein
Cloudera Impala with Native Driver
Nein
DataVirtuality
Nein
Databricks
Ja
Denodo 7
Nein
Denodo 8 & 9
Nein
Dremio
Nein
Dremio 11+
Nein
Exasol
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+
Nein
Microsoft SQL Server 2012+
Nein
Microsoft SQL Server 2016
Nein
Microsoft SQL Server 2017+
Nein
MongoBI
Nein
MySQL
Ja
MySQL 8.0.12+
Ja
Oracle
Nein
Oracle ADWC
Nein
PostgreSQL 9.5+
Ja
PostgreSQL pre-9.5
Ja
PrestoDB
Nein
PrestoSQL
Nein
SAP HANA
Nein
SAP HANA 2+
Nein
SingleStore
Nein
SingleStore 7+
Nein
Snowflake
Ja
Teradata
Nein
Trino
Nein
Vector
Nein
Vertica
Ja