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 régénérer entièrement :
Si votre dialecte prend en charge les augmentations de tables PDT, vous pouvez transformer les types de tables PDT suivants en augmentations de tables PDT :
- Tables agrégées
- Tables dérivées persistantes basées sur LookML (natives)
- Tables PDT basées sur SQL
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.
Remarques à propos des PDT incrémentielles :
- 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_valueouinterval_trigger). Elles ne sont pas compatibles avec les PDT qui utilisent la stratégie de persistancepersist_for. - Pour les PDT basées sur SQL, la requête de table doit être définie à l'aide du paramètre
sqlpour être utilisée comme PDT incrémentielle. Les PDT basées sur SQL définies avec le paramètresql_createoucreate_processne peuvent pas être créées de manière incrémentielle. Comme vous pouvez le voir dans Exemple 1 sur cette page, Looker utilise une commande INSERT ou MERGE pour créer les incréments d'un PDT incrémentiel. La table dérivée ne peut pas être définie au moyen d'instructions personnalisées de langage de définition de données (LDD) car Looker ne pourrait déterminer quelles instructions de LDD définition de données sont nécessaires pour créer un incrément précis. - La table source de l'augmentation de table PDT 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 disposer d'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émentale, Looker interroge la table source pour déterminer les dernières valeurs de la colonne temporelle utilisée comme 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.
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 de nouveaux enregistrements devraient être réclamés.- Filtre Liquid
{% incrementcondition %}(obligatoire pour qu'une table PDT basée sur SQL soit une table PDT incrémentielle ; non applicable aux tables 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. Consultez la page de documentationincrement_keypour plus d'informations. increment_offset(facultatif) : nombre entier qui définit le nombre de périodes précédentes (selon la granularité de la clé d'incrémentation) qui sont régénérées pour chaque génération incrémentielle. Le paramètreincrement_offsetest utile dans le cas de données arrivant tardivement, où les périodes précédentes peuvent contenir de nouvelles données qui n'ont pas été incluses lorsque l'incrément correspondant a été initialement construit et ajouté au PDT.
Consultez la page de documentation sur le paramètre increment_key pour obtenir des exemples montrant comment créer des 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 correspond en fait à la période de la dimension date du groupe de dimensions departure. (Pour en savoir plus sur le fonctionnement des groupes de dimensions, consultez la page de documentation sur le paramètre dimension_group.) Le groupe de dimensions et la période sont tous deux définis dans la vue flights, qui est la explore_source pour ce 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'un PDT sont indépendants de sa stratégie de persistance :
- La stratégie de persistance d'une augmentation de tables PDT 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 un Explore.
- Lorsque le PDT est incrémenté, le générateur de PDT déterminera quand les dernières données ont été ajoutées précédemment à la table, en fonction de l'incrément de temps le plus récent (la période de temps 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 comporte un paramètre
increment_offset, le générateur de PDT régénère également le nombre de périodes précédentes spécifié dans le paramètreincrement_offset. Les périodes précédentes remontent à partir du début de l'incrément de temps le plus récent (la période de temps définie par le paramètreincrement_key).
Les scénarios d'exemple suivants illustrent comment les PDT incrémentaux sont mis à jour, en montrant l'interaction de increment_key, increment_offset et de 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é une fois par mois, le 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 :
Créer une exploration pour la PDT :
- Dans un fichier de modèle associé, utilisez le paramètre
includepour inclure le fichier d'affichage de la table PDT dans le fichier de modèle. - Dans le même fichier de modèle, utilisez le paramètre
explorepour créer une exploration pour la vue du PDT incrémental.
include: "/views/e_faa_pdt.view" explore: e_faa_pdt {}- Dans un fichier de modèle associé, utilisez le paramètre
Ouvrez l'exploration pour la PDT. Pour ce faire, sélectionnez le bouton Voir les actions du fichier puis sélectionnez un nom Explorer.
Dans l'Explorer, sélectionnez des dimensions ou des mesures et 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.
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 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 de statut en haut de l'exploration du journal des événements de tables PDT. Si le message indique « à partir du 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 effectuées lorsque vous l'exécuterez dans l'exploration. Par exemple, si les commentaires dans l'onglet SQL indiquent
-- generate derived table e_incremental_pdt,
- Si vous disposez de l'autorisation
Une fois que vous avez créé la version initiale du PDT, lancez une version incrémentale du PDT en utilisant l'option Reconstruire les tables dérivées et exécuter de l'Explorer.
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 PDT pour voir les événementscreate increment completedu PDT incrémentiel. Si vous ne voyez pas cet événement dans le journal des événements PDT et que l'état de la requête indique « à partir du cache », sélectionnez Effacer le cache et actualiser 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
- Si vous disposez de l'autorisation
Une fois que vous avez vérifié que la PDT est créée et incrémentée correctement, si vous ne souhaitez pas conserver l'exploration dédiée à la PDT, vous pouvez supprimer ou commenter les paramètres
exploreetincludede la PDT dérivée permanente dans votre fichier de modèle.
Une fois le PDT créé en mode développement, la même table sera utilisée en production après le déploiement de vos modifications, sauf si vous apportez 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 tables PDT incrémentales
Cette section décrit certains problèmes courants que vous pourriez rencontrer lors de l'utilisation de PDT incrémentiels, ainsi que les étapes pour dépanner et résoudre ces problèmes.
Échec de la création de la PDT incrémentale après une modification du 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 (ajout, suppression ou modification du type de données d'une colonne, par exemple) 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 afin de 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 reconstruire votre PDT incrémentielle à partir de zéro, utilisez l'appel d'API start_pdt_build et incluez le paramètre full_force_incremental.
Dialectes de base de données pris en charge pour les augmentations de tables PDT
Pour que Looker prenne en charge les PDT incrémentiels dans votre projet Looker, votre dialecte de base de données doit prendre en charge les commandes langage de définition de données (LDD) qui permettent la suppression et l'insertion de lignes.
Le tableau suivant indique les dialectes qui prennent en charge les tables PDT incrémentielles dans la dernière version de Looker (pour Databricks, les tables PDT incrémentielles ne sont compatibles qu'avec la version 12.1 et les versions ultérieures de Databricks) :
| 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 | |
| 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+ | |
| 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 |