Augmentation de tables PDT

Dans Looker, les tables dérivées persistantes (PDT) sont écrites dans le schéma brouillon de votre base de données. Looker régénère et rend une table PDT persistante en fonction de sa stratégie de persistance. Lorsqu'une table PDT est déclenchée pour se régénérer, par défaut, Looker régénère la totalité de la table.

Une PDT incrémentale est une PDT que Looker crée en ajoutant de nouvelles données à la table au lieu de la recompiler entièrement :

Un grand tableau dont les trois dernières lignes sont mises en évidence pour montrer qu'un petit nombre de nouvelles lignes sont ajoutées au tableau.

Si votre dialecte prend en charge les PDT incrémentales, vous pouvez transformer les types de PDT suivants en PDT incrémentales :

Lors de la première exécution d'une requête sur une augmentation de table PDT, Looker génère intégralement la PDT pour extraire les données initiales. Selon la taille de la table, la génération initiale peut prendre beaucoup de temps, comme pour toute génération d'une table imposante. Une fois la table initiale générée, les générations suivantes se feront par augmentation et prendront moins de temps si l'augmentation de table PDT est configurée de façon stratégique.

Notez les points suivants concernant les PDT incrémentales :

  • Les PDT incrémentales ne sont compatibles qu'avec les PDT qui utilisent une stratégie de persistance basée sur un déclencheur (datagroup_trigger, sql_trigger_value ou interval_trigger). Les PDT incrémentales ne sont pas compatibles avec les PDT qui utilisent la stratégie de persistance persist_for.
  • Pour les PDT basées sur SQL, la requête de table doit être définie à l'aide du paramètre sql pour être utilisée comme PDT incrémentale. Les TPD basées sur SQL qui sont définies avec le paramètre sql_create ou le paramètre create_process ne peuvent pas être créées de manière incrémentielle. Comme vous pouvez le voir dans l'Exemple 1 de cette page, Looker utilise une commande INSERT ou MERGE pour créer les incréments d'une augmentation de tables PDT. La table dérivée ne peut pas être définie à l'aide d'instructions LDD (langage de définition de données) personnalisées, car Looker ne pourrait pas déterminer quelles instructions LDD sont nécessaires pour créer un incrément précis.
  • La table source de la PDT incrémentielle doit être optimisée pour les requêtes temporelles. Plus précisément, la colonne temporelle utilisée pour la clé d'incrément doit avoir une stratégie d'optimisation, telle que le partitionnement, les clés de tri, les index ou toute autre stratégie d'optimisation compatible avec votre dialecte. L'optimisation de la table source est fortement recommandée, car à chaque mise à jour de la table incrémentielle, Looker interroge la table source pour déterminer les dernières valeurs de la colonne temporelle utilisée pour la clé d'incrémentation. Si la table source n'est pas optimisée pour ces requêtes, la requête des dernières valeurs par Looker peut être lente et coûteuse.
  • Les fonctions de fenêtrage SQL ne sont pas directement compatibles avec les PDT incrémentielles. Pour utiliser une fonction de fenêtrage avec une PDT incrémentale, créez la PDT incrémentale en tant que table de base ou intermédiaire sans la fonction de fenêtrage, puis référencez cette PDT incrémentale dans une table dérivée ou une PDT distincte qui applique la fonction de fenêtrage.

Définition d'une augmentation de tables PDT

Vous pouvez utiliser les paramètres suivants pour transformer une table PDT dans une augmentation de PDT :

  • increment_key (obligatoire pour rendre la PDT incrémentale) : définit la période pour laquelle les nouveaux enregistrements doivent être interrogés.
  • Filtre Liquid {% incrementcondition %} (obligatoire pour transformer une PDT basée sur SQL en PDT incrémentielle ; non applicable aux PDT basées sur LookML) : connecte la clé d'incrément à la colonne horaire de la base de données sur laquelle la clé d'incrément est basée. Pour en savoir plus, consultez la page de documentation sur increment_key.
  • increment_offset (facultatif) : un nombre entier qui définit le nombre de périodes passées (selon la granularité de la clé d'incrémentation) qui sont régénérées pour chaque génération d'augmentation. Le paramètre increment_offset est utile en cas de données tardives, lorsque les périodes précédentes disposent de nouvelles données non incluses lors de la génération et de l'ajout à la PDT de l'augmentation correspondante.

Consultez la page de documentation sur le paramètre increment_key pour obtenir des exemples de création de PDT incrémentielles à partir de tables dérivées natives persistantes, de tables dérivées persistantes basées sur SQL et de tables agrégées.

Voici un exemple simple de fichier de vue définissant une augmentation de tables PDT basées sur LookML :

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
  }
}

Cette table sera générée intégralement lorsque la première requête sera exécutée sur elle. Après cela, la PDT sera régénérée en augmentations d'une journée (increment_key: departure_date), en remontant trois jours en arrière (increment_offset: 3).

La clé d'incrément est basée sur la dimension departure_date, qui est en fait la période date du groupe de dimensions departure. (Consultez la page de documentation sur le paramètre dimension_group pour découvrir le fonctionnement des groupes de dimensions.) Le groupe de dimensions et la période sont tous deux définis par la vue flights, qui est la explore_source de cette PDT. Voici comment le groupe de dimensions departure est défini dans le fichier de vue flights :

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

Interaction des paramètres d'augmentation et stratégie de persistance

Les paramètres increment_key et increment_offset d'une PDT sont indépendants de sa stratégie de persistance :

  • La stratégie de persistance d'une PDT incrémentielle détermine uniquement à quel moment la PDT augmente. Le générateur de PDT ne modifie pas la PDT incrémentielle, sauf si la stratégie de persistance de la table est déclenchée ou si la PDT est déclenchée manuellement avec l'option Reconstruire les tables dérivées et exécuter dans une exploration.
  • Lorsque la PDT augmente, le générateur de PDT détermine la date du dernier ajout de données à la table en termes d'incrément de temps le plus courant (période définie par le paramètre increment_key). De ce fait, le générateur de PDT tronque les données au début de l'incrément de temps le plus courant de la table, puis génère l'incrément le plus récent à partir de là.
  • Si la PDT dispose d'un paramètre increment_offset, le générateur de PDT régénère également le nombre de périodes précédentes indiqué dans le paramètre increment_offset. Les périodes précédentes débutent au point de départ de l'incrément de temps le plus courant (période définie par le paramètre increment_key).

Les exemples de scénarios ci-dessous illustrent de quelle façon les tables PDT incrémentielles sont mises à jour en montrant l'interaction de increment_key, increment_offset et la stratégie de persistance.

Exemple 1

Cet exemple utilise une PDT avec les propriétés suivantes :

  • Clé d'incrément : date
  • Décalage de l'incrément : 3
  • Stratégie de persistance : déclenchée une fois par mois au premier jour du mois

Voici comment cette table sera mise à jour :

  • Une stratégie de persistance mensuelle signifie que la table sera générée automatiquement une fois par mois. Cela signifie que le 1er juin, par exemple, la dernière ligne de la table aura été ajoutée le 1er mai.
  • Comme la PDT a une clé d'incrémentation basée sur la date, le générateur de PDT tronque le 1er mai jusqu'au début de la journée et régénère les données du 1er mai jusqu'à la date actuelle du 1er juin.
  • En outre, cette PDT a un décalage d'incrémentation de 3. Le générateur de PDT régénère donc également les données des trois périodes précédentes (jours) avant le 1er mai. Les données sont donc régénérées pour les 28, 29 et 30 avril, et jusqu'au jour actuel, le 1er juin.

En termes SQL, voici la commande qu'exécutera le générateur de PDT le 1er juin pour déterminer les lignes de la PDT existante à régénérer :

## 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)

Et voici la commande SQL qu'exécutera le générateur de PDT le 1er juin pour générer l'incrément le plus récent :

## 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;

Exemple 2

Cet exemple utilise une PDT avec les propriétés suivantes :

  • Stratégie de persistance : déclenchée une fois par jour
  • Clé d'incrément : mois
  • Décalage de l'incrément : 0

Voici comment cette table sera mise à jour le 1er juin :

  • La stratégie de persistance quotidienne signifie que la table sera générée automatiquement une fois par jour. Le 1er juin, la dernière ligne de la table aura été ajoutée le 31 mai.
  • Comme la clé d'incrémentation est basée sur le mois, le générateur de PDT tronque à partir du 31 mai jusqu'au début du mois et régénère les données de tout le mois de mai jusqu'à la date actuelle, y compris le 1er juin.
  • Comme cette PDT n'a pas de décalage d'incrémentation, aucune période précédente n'est régénérée.

Voici comment cette table sera mise à jour le 2 juin :

  • Le 2 juin, la dernière ligne de la table aura été ajoutée le 1er juin.
  • Comme le générateur de PDT tronque jusqu'au début du mois de juin et régénère les données comprises entre le 1er juin et la date actuelle, les données ne sont régénérées que pour le 1er et le 2 juin.
  • Comme cette PDT n'a pas de décalage d'incrémentation, aucune période précédente n'est régénérée.

Exemple 3

Cet exemple utilise une PDT avec les propriétés suivantes :

  • Clé d'incrément : mois
  • Décalage de l'incrément : 3
  • Stratégie de persistance : déclenchée une fois par jour

Ce scénario illustre une mauvaise configuration d'augmentation de tables PDT car il s'agit d'une PDT à déclenchement quotidien avec un décalage de trois mois. Cela signifie qu'au moins trois mois de données seront régénérées chaque jour, ce qui constitue une utilisation particulièrement inefficace d'une augmentation de tables PDT. Cependant, c'est un scénario intéressant pour comprendre comment les augmentations de tables PDT fonctionnent.

Voici comment cette table sera mise à jour le 1er juin :

  • La stratégie de persistance quotidienne signifie que la table sera générée automatiquement une fois par jour. Le 1er juin, par exemple, la dernière ligne de la table aura été ajoutée le 31 mai.
  • Comme la clé d'incrémentation est basée sur le mois, le générateur de PDT tronque à partir du 31 mai jusqu'au début du mois et régénère les données de tout le mois de mai jusqu'à la date actuelle, y compris le 1er juin.
  • En outre, cette PDT a un décalage d'incrémentation de 3. Cela signifie que le générateur de PDT régénère donc également les données des trois périodes précédentes (mois) avant mai. Les données sont donc régénérées à partir des mois de février, mars et avril, et jusqu'au jour actuel, le 1er juin.

Voici comment cette table sera mise à jour le 2 juin :

  • Le 2 juin, la dernière ligne de la table aura été ajoutée le 1er juin.
  • Le générateur de PDT tronque le mois jusqu'au 1er juin et régénère les données pour le mois de juin, en incluant le 2 juin.
  • En outre, en raison du décalage d'incrémentation, le générateur de PDT régénère les données des trois mois précédant le mois de juin. Les données sont donc régénérées à partir des mois de mars, avril et mai, et jusqu'au jour actuel, le 2 juin.

Test d'une augmentation de tables PDT en mode Développement

Avant de déployer une nouvelle augmentation de tables PDT dans votre environnement de production, vous pouvez tester la PDT pour vérifier qu'elle génère et incrémente correctement. Pour tester une augmentation de tables PDT en mode Développement :

  1. Créer une exploration pour la PDT :

    • Dans un fichier de modèle associé, utilisez le paramètre include pour inclure le fichier de vue de la table PDT dans le fichier de modèle.
    • Dans le même fichier de modèle, utilisez le paramètre explore pour créer une exploration de la vue de la PDT incrémentale.
     include: "/views/e_faa_pdt.view"
     explore: e_faa_pdt {}
    
  2. Ouvrez l'exploration pour la PDT. Pour ce faire, sélectionnez le bouton Voir les actions sur les fichiers, puis sélectionnez le nom d'une exploration.

  1. Dans l'exploration, sélectionnez des dimensions ou des mesures, puis cliquez sur Exécuter. Looker régénère ensuite intégralement la PDT. S'il s'agit de la première exécution d'une requête sur l'augmentation de tables PDT, le générateur de PDT génère intégralement la PDT pour extraire les données initiales. Selon la taille de la table, la génération initiale peut prendre beaucoup de temps, comme pour toute génération d'une table imposante.

  2. Vous pouvez vérifier que la PDT initiale a été générée comme suit :

    • Si vous disposez de l'autorisation see_logs, vous pouvez vérifier que la table a été créée en consultant le journal des événements de tables PDT. Si vous ne voyez pas d'événements créés par la PDT dans le journal des événements de tables PDT, vérifiez les informations d'état en haut de l'exploration du journal des événements de tables PDT. S'il indique "depuis le cache ", vous pouvez sélectionner Vider le cache et actualiser pour obtenir des informations plus récentes.
    • Vous pouvez également regarder les commentaires dans l'onglet SQL de la barre Données de l'exploration. L'onglet SQL affiche la requête et les actions qui seront entreprises lorsque vous exécuterez la requête dans l'exploration. Par exemple, si les commentaires de l'onglet SQL indiquent -- generate derived table e_incremental_pdt, c'est l'action qui sera effectuée lorsque vous cliquerez sur Exécuter.
  3. Une fois la compilation initiale de la PDT créée, lancez une compilation incrémentielle de la PDT à l'aide de l'option Rebuild Derived Tables & Run (Recréer les tables dérivées et exécuter) de l'exploration.

  4. Vous pouvez utiliser les mêmes méthodes qu'auparavant pour vérifier que la PDT se génère par augmentation :

    • Si vous disposez de l'autorisation see_logs, vous pouvez utiliser le journal des événements de tables PDT pour afficher les événements create increment complete de la table PDT incrémentielle. Si vous ne voyez pas cet événement dans le journal des événements de tables PDT et si l'état de la requête indique "depuis le cache", vous pouvez sélectionner Vider le cache et rafraîchir pour obtenir des informations plus récentes.
    • Regardez les commentaires dans l'onglet SQL de la barre Données de l'exploration. Dans ce cas, les commentaires indiquent que la PDT a été augmentée. Par exemple : -- increment persistent derived table e_incremental_pdt to generation 2
  5. Une fois que vous avez vérifié que la PDT est créée et s'incrémente correctement, si vous ne souhaitez pas conserver l'exploration dédiée à la PDT, vous pouvez supprimer ou mettre en commentaire les paramètres explore et include de la PDT dans votre fichier de modèle.

Une fois la PDT générée en mode Développement, la même table est utilisée pour la production lorsque vous déployez vos modifications, à moins que vous n'apportiez d'autres modifications à la définition de la table. Pour en savoir plus, consultez la section Tables persistantes en mode Développement de la page de documentation Tables dérivées dans Looker.

Résoudre les problèmes liés aux PDT incrémentales

Cette section décrit certains problèmes courants que vous pouvez rencontrer lors de l'utilisation de TPD incrémentielles, ainsi que les étapes à suivre pour les résoudre.

Échec de la création d'une PDT incrémentielle après une modification de schéma

Si votre PDT incrémentielle est une table dérivée basée sur SQL et que le paramètre sql inclut un caractère générique tel que SELECT *, les modifications apportées au schéma de votre base de données sous-jacente (comme l'ajout ou la suppression d'une colonne, ou la modification du type de données d'une colonne) peuvent entraîner l'échec de la PDT avec l'erreur suivante :

SQL Error in incremental PDT: Query execution failed

Pour résoudre ce problème, modifiez l'instruction SELECT dans le paramètre sql pour sélectionner des colonnes individuelles. Par exemple, si votre clause SELECT est SELECT *, remplacez-la par SELECT column1, column2, ....

Si votre schéma change et que vous souhaitez recréer votre PDT incrémentielle à partir de zéro, utilisez l'appel d'API start_pdt_build et incluez le paramètre full_force_incremental.

Les fonctions de fenêtrage renvoient des résultats inattendus dans une PDT incrémentale

L'utilisation directe de fonctions de fenêtrage SQL dans une PDT incrémentielle n'est pas prise en charge. Étant donné que les compilations incrémentielles ne traitent que les lignes du dernier incrément de temps et de tout increment_offset configuré, définir une fonction de fenêtrage directement dans une PDT incrémentielle (par exemple, dans un paramètre derived_column ou sql) peut entraîner des valeurs manquantes ou des calculs inattendus au-delà des limites de partition lorsque la table s'incrémente.

Pour utiliser une fonction de fenêtrage avec une PDT incrémentale, définissez la PDT incrémentale comme une PDT de base ou intermédiaire sans la fonction de fenêtrage, puis référencez cette PDT incrémentale dans une table dérivée distincte ou une PDT standard qui applique la fonction de fenêtrage.

Dialectes de base de données pris en charge pour les augmentations de tables PDT

Pour que Looker prenne en charge les tables PDT incrémentielles dans votre projet Looker, votre dialecte de base de données doit accepter les commandes du langage de définition de données (LDD) qui permettent de supprimer et d'insérer des lignes.

Le tableau suivant répertorie les dialectes prenant en charge les PDT incrémentielles dans la dernière version de Looker (pour Databricks, les PDT incrémentielles ne sont compatibles qu'avec Databricks 12.1 et versions ultérieures) :

Dialecte Compatibilité
Actian Avalanche
Amazon Athena
Amazon Aurora MySQL
Amazon Redshift
Amazon Redshift 2.1+
Amazon Redshift Serverless 2.1+
Apache Druid
Apache Druid 0.13.x - 0.17.x
Apache Druid 0.18+
Apache Hive 2.3+
Apache Hive 3.1.2+
Apache Spark 3+
ClickHouse
ClickHouse 26+
Cloudera Impala 3.1+
Cloudera Impala 3.1+ with Native Driver
Cloudera Impala with Native Driver
DataVirtuality
Databricks
Denodo 7
Denodo 8 & 9
Dremio
Dremio 11+
Dremio 26+ with Arrow Flight SQL
Exasol
Google BigQuery Legacy SQL
Google BigQuery Standard SQL
Google Cloud AlloyDB for PostgreSQL
Google Cloud PostgreSQL
Google Cloud SQL
Google Spanner
Greenplum
HyperSQL
IBM Netezza
MariaDB
Microsoft Azure PostgreSQL
Microsoft Azure SQL Database
Microsoft Azure Synapse Analytics
Microsoft SQL Server 2008+
Microsoft SQL Server 2012+
Microsoft SQL Server 2016
Microsoft SQL Server 2017+
MongoBI
MongoSQL
MySQL
MySQL 8.0.12+
Oracle
Oracle ADWC
PostgreSQL 9.5+
PostgreSQL pre-9.5
PrestoDB
PrestoSQL
SAP HANA
SAP HANA 2+
SingleStore
SingleStore 7+
Snowflake
Teradata
Trino
Vector
Vertica