Dans Looker, une table dérivée est une requête dont les résultats sont utilisés comme s'il s'agissait d'une table réelle de la base de données.
Par exemple, vous pouvez avoir une table de base de données appelée orders
comportant de nombreuses colonnes. Vous souhaitez calculer certaines métriques agrégées au niveau du client, comme le nombre de commandes passées par chaque client ou le moment auquel chaque client a passé sa première commande. À l'aide d'une table dérivée native ou d'une table dérivée basée sur SQL, vous pouvez créer une table de base de données nommée customer_order_summary
qui inclut ces métriques.
Vous pouvez ensuite utiliser la table dérivée customer_order_summary
comme s'il s'agissait de n'importe quelle autre table de la base de données.
Pour des cas d'utilisation courants de tables dérivées, consultez Looker Looker: Exploitez tout le potentiel des tables dérivées dans Looker.
Tables dérivées natives et tables dérivées SQL
Pour créer une table dérivée dans votre projet Looker, utilisez le paramètre derived_table
sous un paramètre view. Dans le paramètre derived_table
, vous pouvez définir la requête pour la table dérivée de deux manières:
- Pour une table dérivée native, vous devez la définir à l'aide d'une requête basée sur LookML.
- Pour une table dérivée basée sur SQL, vous devez la définir à l'aide d'une requête SQL.
Par exemple, les fichiers de vue suivants montrent comment utiliser LookML pour créer une vue à partir d'une table dérivée de customer_order_summary
. Les deux versions de LookML illustrent comment créer des tables dérivées équivalentes en utilisant LookML ou SQL pour définir la requête pour la table dérivée :
- La table dérivée native définit la requête avec LookML dans le paramètre
explore_source
. Dans cet exemple, la requête est basée sur une vueorders
existante, définie dans un fichier distinct non présenté dans cet exemple. La requêteexplore_source
de la table dérivée native ajoute les champscustomer_id
,first_order
ettotal_amount
du fichier de vueorders
. - La table dérivée basée sur SQL définit la requête en utilisant le langage SQL dans le paramètre
sql
. Dans cet exemple, la requête SQL est une requête directe de la tableorders
de la base de données.
view: customer_order_summary { derived_table: { explore_source: orders { column: customer_id { field: orders.customer_id } column: first_order { field: orders.first_order } column: total_amount { field: orders.total_amount } } } dimension: customer_id { type: number primary_key: yes sql: ${TABLE}.customer_id ;; } dimension_group: first_order { type: time timeframes: [date, week, month] sql: ${TABLE}.first_order ;; } dimension: total_amount { type: number value_format: "0.00" sql: ${TABLE}.total_amount ;; } }
view: customer_order_summary { derived_table: { sql: SELECT customer_id, MIN(DATE(time)) AS first_order, SUM(amount) AS total_amount FROM orders GROUP BY customer_id ;; } dimension: customer_id { type: number primary_key: yes sql: ${TABLE}.customer_id ;; } dimension_group: first_order { type: time timeframes: [date, week, month] sql: ${TABLE}.first_order ;; } dimension: total_amount { type: number value_format: "0.00" sql: ${TABLE}.total_amount ;; } }
Les deux versions créent une vue appelée customer_order_summary
basée sur la table orders
, avec les colonnes customer_id
, first_order,
et total_amount
.
À l'exception du paramètre derived_table
et de ses sous-paramètres, cette vue customer_order_summary
fonctionne comme n'importe quel autre fichier de vue. Que vous définissiez la requête de la table dérivée avec LookML ou SQL, vous pouvez créer des mesures et des dimensions LookML basées sur les colonnes de la table dérivée.
Une fois que vous avez défini votre table dérivée, vous pouvez l'utiliser comme n'importe quelle autre table dans votre base de données.
Tables dérivées natives
Les tables dérivées natives sont basées sur des requêtes que vous définissez en utilisant les termes LookML. Pour créer une table dérivée native, vous devez utiliser le paramètre explore_source
dans le paramètre derived_table
d'un paramètre de vue. Vous créez les colonnes de votre table dérivée native en vous référant aux dimensions et mesures LookML dans votre modèle. Consultez le fichier de vue Tableau dérivée native dans l'exemple précédent.
Comparées aux tables dérivées basées sur SQL, les tables dérivées natives sont beaucoup plus faciles à lire et à comprendre lors de la modélisation des données.
Consultez la page de documentation Créer des tables dérivées natives pour en savoir plus sur la création de tables dérivées natives.
Tables dérivées basées sur SQL
Pour créer une table dérivée basée sur SQL, vous devez définir une requête SQL, en créant des colonnes dans la table à l'aide d'une requête SQL. Vous ne pouvez pas faire référence à des dimensions et mesures LookML dans une table dérivée basée sur SQL. Consultez le fichier de vue Tableau dérivée basée sur SQL dans l'exemple précédent.
La plupart du temps, vous définissez la requête SQL à l'aide du paramètre sql
dans le paramètre derived_table
d'un paramètre view.
Pour créer des requêtes basées sur SQL dans Looker, vous pouvez utiliser SQL Runner pour créer la requête SQL et la transformer en définition de table dérivée.
Certains cas limites ne permettent pas l'utilisation du paramètre sql
. Dans de tels cas, Looker accepte les paramètres suivants pour définir une requête SQL pour des tables dérivées persistantes (PDT):
create_process
: lorsque vous utilisez le paramètresql
pour une table PDT, Looker encapsule en arrière-plan l'instruction LDD (langage de définition de données) du dialecteCREATE TABLE
autour de votre requête afin de créer la table PDT à partir de votre requête SQL. Certains dialectes ne prennent pas en charge une instruction SQLCREATE TABLE
en une seule étape. Pour ces dialectes, vous ne pouvez pas créer de PDT avec le paramètresql
. À la place, vous pouvez utiliser le paramètrecreate_process
pour créer une PDT en plusieurs étapes. Consultez la page de documentation du paramètrecreate_process
pour obtenir des informations et des exemples.sql_create
: si votre cas d'utilisation nécessite des commandes LDD personnalisées et que votre dialecte prend en charge le LDD (par exemple, la prédiction Google BigQuery ML), vous pouvez utiliser le paramètresql_create
au lieu du paramètresql
pour créer une table PDT. Pour obtenir des informations et des exemples, consultez la page de documentation desql_create
.
Que vous utilisiez le paramètre sql
, create_process
ou sql_create
, vous définissez dans tous les cas la table dérivée à l'aide d'une requête SQL. Elles sont donc toutes considérées comme des tables dérivées basées sur SQL.
Lorsque vous définissez une table dérivée basée sur SQL, veillez à attribuer un alias propre à chaque colonne en utilisant AS
. En effet, vous devrez faire référence aux noms de colonnes de votre ensemble de résultats dans vos dimensions (par exemple, ${TABLE}.first_order
). C'est pourquoi l'exemple précédent utilise MIN(DATE(time)) AS first_order
au lieu de simplement MIN(DATE(time))
.
Tables dérivées temporaires et persistantes
En plus de la distinction entre les tables dérivées natives et les tables dérivées basées sur SQL, une distinction existe également entre une table dérivée temporaire (qui n'est pas écrite dans la base de données) et une table dérivée persistante (PDT) ; écrite dans un schéma de votre base de données.
Les tables dérivées natives et tables dérivées basées sur SQL peuvent être temporaires ou persistantes.
Tables dérivées temporaires
Les tables dérivées présentées précédemment sont des exemples de tables dérivées temporaires. Ils sont temporaires, car aucune stratégie de persistance n'est définie dans le paramètre derived_table
.
Les tables dérivées temporaires ne sont pas écrites sur la base de données. Lorsqu'un utilisateur exécute une requête d'exploration impliquant une ou plusieurs tables dérivées, Looker construit une requête SQL en utilisant une combinaison spécifique au dialecte du code SQL de la ou des tables dérivées, plus les champs, jointures et valeurs de filtre demandés. Si la combinaison a été exécutée auparavant et que les résultats se trouvent toujours dans le cache, Looker utilise les résultats mis en cache. Pour en savoir plus sur la mise en cache des requêtes dans Looker, consultez la page de documentation Mettre en cache des requêtes.
Sinon, si Looker ne peut pas utiliser les résultats mis en cache, il doit exécuter une nouvelle requête sur votre base de données chaque fois qu'un utilisateur demande des données à partir d'une table dérivée temporaire. C'est pourquoi vous devez vous assurer que vos tables dérivées temporaires sont performantes et ne soumettent pas la base de données à une charge excessive. Si l'exécution de la requête prend un certain temps, il est souvent préférable d'utiliser un PDT.
Dialectes de base de données pris en charge pour les tables dérivées temporaires
Pour que Looker prenne en charge les tables dérivées dans votre projet, votre dialecte de base de données doit également les prendre en charge. Le tableau suivant indique les dialectes prenant en charge les tables dérivées dans la dernière version de Looker:
Dialecte | Compatible ? |
---|---|
Actian Avalanche | Oui |
Amazon Athena | Oui |
Amazon Aurora MySQL | Oui |
Amazon Redshift | Oui |
Apache Druid | Oui |
Apache Druid 0.13 et versions ultérieures | Oui |
Apache Druid 0.18 et versions ultérieures | Oui |
Apache Hive 2.3 et versions ultérieures | Oui |
Apache Hive 3.1.2+ | Oui |
Apache Spark 3 ou version ultérieure | Oui |
ClickHouse | Oui |
Cloudera Impala 3.1 ou version ultérieure | Oui |
Cloudera Impala 3.1 ou version ultérieure avec pilote natif | Oui |
Cloudera Impala avec pilote natif | Oui |
DataVirtuality | Oui |
Databricks | Oui |
Denodo 7 | Oui |
Denodo 8 | Oui |
Dremio | Oui |
Dremio 11 et versions ultérieures | Oui |
Exasol | Oui |
Boulon | Oui |
Ancien SQL de Google BigQuery | Oui |
SQL standard Google BigQuery | Oui |
Google Cloud PostgreSQL | Oui |
Google Cloud SQL | Oui |
Google Spanner | Oui |
Prune | Oui |
HyperSQL | Oui |
IBM Netezza | Oui |
MariaDB | Oui |
Microsoft Azure PostgreSQL | Oui |
Base de données Microsoft Azure SQL | Oui |
Microsoft Azure Synapse Analytics | Oui |
Microsoft SQL Server 2008 ou version ultérieure | Oui |
Microsoft SQL Server 2012 ou version ultérieure | Oui |
Microsoft SQL Server 2016 | Oui |
Microsoft SQL Server 2017 ou version ultérieure | Oui |
MongoBI | Oui |
MySQL | Oui |
MySQL 8.0.12 et versions ultérieures | Oui |
Oracle | Oui |
Oracle ADWC | Oui |
PostgreSQL 9.5 ou version ultérieure | Oui |
PostgreSQL antérieur à la version 9.5 | Oui |
PrestoDB | Oui |
PrestoSQL | Oui |
SAP HANA | Oui |
SAP HANA 2+ | Oui |
SingleStore | Oui |
SingleStore 7+ | Oui |
Snowflake | Oui |
Teradata | Oui |
Trino | Oui |
Vecteur | Oui |
Vertica | Oui |
Tables dérivées persistantes
Une table dérivée persistante (PDT) est écrite dans un schéma entièrement nouveau de votre base de données, puis régénéré selon la planification que vous spécifiez avec une stratégie de persistance.
Une PDT peut être soit une table dérivée native, soit une table dérivée basée sur SQL.
Conditions requises pour les PDT
Pour utiliser des tables dérivées persistantes (PDT) dans votre projet Looker, vous avez besoin des éléments suivants:
- Un dialecte de base de données qui prend en charge les PDT. Consultez la section Dialectes de base de données compatibles pour les PDT plus loin sur cette page pour obtenir la liste des dialectes prenant en charge les tables dérivées persistantes basées sur SQL et les tables dérivées natives persistantes.
- Un schéma entièrement nouveau sur votre base de données. Il peut s'agir de n'importe quel schéma sur votre base de données mais nous vous recommandons de créer un nouveau schéma qui sera utilisé uniquement à cette fin. Votre administrateur de base de données doit configurer le schéma avec une autorisation d'écriture pour l'utilisateur de la base de données Looker.
- Une connexion Looker configurée avec l'option Activer les PDT activée Ce paramètre est généralement défini lors de la configuration initiale de votre connexion Looker (consultez la page de documentation sur les dialectes Looker pour obtenir des instructions pour votre dialecte de base de données), mais vous pouvez également activer les PDT pour votre connexion après la configuration initiale.
Dialectes de base de données pris en charge pour les tables PDT
Pour que Looker prenne en charge les tables dérivées persistantes (PDT) dans votre projet Looker, votre dialecte de base de données doit également les prendre en charge.
Pour prendre en charge tout type de PDT (basée sur LookML ou SQL), le dialecte doit prendre en charge les écritures dans la base de données, entre autres exigences. Certaines configurations de base de données en lecture seule n'autorisent pas la persistance (il s'agit le plus souvent des bases de données d'instances répliquées à chaud Postgres). Vous pouvez alors utiliser des tables dérivées temporaires.
Le tableau suivant présente les dialectes compatibles avec les tables dérivées basées sur SQL persistantes dans la dernière version de Looker:
Dialecte | Compatible ? |
---|---|
Actian Avalanche | Oui |
Amazon Athena | Oui |
Amazon Aurora MySQL | Oui |
Amazon Redshift | Oui |
Apache Druid | Non |
Apache Druid 0.13 et versions ultérieures | Non |
Apache Druid 0.18 et versions ultérieures | Non |
Apache Hive 2.3 et versions ultérieures | Oui |
Apache Hive 3.1.2+ | Oui |
Apache Spark 3 ou version ultérieure | Oui |
ClickHouse | Non |
Cloudera Impala 3.1 ou version ultérieure | Oui |
Cloudera Impala 3.1 ou version ultérieure avec pilote natif | Oui |
Cloudera Impala avec pilote natif | Oui |
DataVirtuality | Non |
Databricks | Oui |
Denodo 7 | Non |
Denodo 8 | Non |
Dremio | Non |
Dremio 11 et versions ultérieures | Non |
Exasol | Oui |
Boulon | Non |
Ancien SQL de Google BigQuery | Oui |
SQL standard Google BigQuery | Oui |
Google Cloud PostgreSQL | Oui |
Google Cloud SQL | Oui |
Google Spanner | Non |
Prune | Oui |
HyperSQL | Non |
IBM Netezza | Oui |
MariaDB | Oui |
Microsoft Azure PostgreSQL | Oui |
Base de données Microsoft Azure SQL | Oui |
Microsoft Azure Synapse Analytics | Oui |
Microsoft SQL Server 2008 ou version ultérieure | Oui |
Microsoft SQL Server 2012 ou version ultérieure | Oui |
Microsoft SQL Server 2016 | Oui |
Microsoft SQL Server 2017 ou version ultérieure | Oui |
MongoBI | Non |
MySQL | Oui |
MySQL 8.0.12 et versions ultérieures | Oui |
Oracle | Oui |
Oracle ADWC | Oui |
PostgreSQL 9.5 ou version ultérieure | Oui |
PostgreSQL antérieur à la version 9.5 | Oui |
PrestoDB | Oui |
PrestoSQL | Oui |
SAP HANA | Oui |
SAP HANA 2+ | Oui |
SingleStore | Oui |
SingleStore 7+ | Oui |
Snowflake | Oui |
Teradata | Oui |
Trino | Oui |
Vecteur | Oui |
Vertica | Oui |
Pour prendre en charge les tables dérivées natives persistantes (qui ont des requêtes basées sur LookML), le dialecte doit également prendre en charge une fonction LDD CREATE TABLE
. Voici une liste des dialectes compatibles avec les tables dérivées natives (basées sur LookML) persistantes dans la dernière version de Looker:
Dialecte | Compatible ? |
---|---|
Actian Avalanche | Oui |
Amazon Athena | Oui |
Amazon Aurora MySQL | Oui |
Amazon Redshift | Oui |
Apache Druid | Non |
Apache Druid 0.13 et versions ultérieures | Non |
Apache Druid 0.18 et versions ultérieures | Non |
Apache Hive 2.3 et versions ultérieures | Oui |
Apache Hive 3.1.2+ | Oui |
Apache Spark 3 ou version ultérieure | Oui |
ClickHouse | Non |
Cloudera Impala 3.1 ou version ultérieure | Oui |
Cloudera Impala 3.1 ou version ultérieure avec pilote natif | Oui |
Cloudera Impala avec pilote natif | Oui |
DataVirtuality | Non |
Databricks | Oui |
Denodo 7 | Non |
Denodo 8 | Non |
Dremio | Non |
Dremio 11 et versions ultérieures | Non |
Exasol | Oui |
Boulon | Non |
Ancien SQL de Google BigQuery | Oui |
SQL standard Google BigQuery | Oui |
Google Cloud PostgreSQL | Oui |
Google Cloud SQL | Non |
Google Spanner | Non |
Prune | Oui |
HyperSQL | Non |
IBM Netezza | Oui |
MariaDB | Oui |
Microsoft Azure PostgreSQL | Oui |
Base de données Microsoft Azure SQL | Oui |
Microsoft Azure Synapse Analytics | Oui |
Microsoft SQL Server 2008 ou version ultérieure | Oui |
Microsoft SQL Server 2012 ou version ultérieure | Oui |
Microsoft SQL Server 2016 | Oui |
Microsoft SQL Server 2017 ou version ultérieure | Oui |
MongoBI | Non |
MySQL | Oui |
MySQL 8.0.12 et versions ultérieures | Oui |
Oracle | Oui |
Oracle ADWC | Oui |
PostgreSQL 9.5 ou version ultérieure | Oui |
PostgreSQL antérieur à la version 9.5 | Oui |
PrestoDB | Oui |
PrestoSQL | Oui |
SAP HANA | Oui |
SAP HANA 2+ | Oui |
SingleStore | Oui |
SingleStore 7+ | Oui |
Snowflake | Oui |
Teradata | Oui |
Trino | Oui |
Vecteur | Oui |
Vertica | Oui |
Augmentation de la génération de tables PDT
Une PDT incrémentielle est une table dérivée persistante (PDT) que Looker crée en ajoutant de nouvelles données à la table au lieu de la reconstruire dans son intégralité.
Si votre dialecte accepte les PDT incrémentielles et que votre PDT utilise une stratégie de persistance basée sur des déclencheurs (datagroup_trigger
, sql_trigger_value
ou interval_trigger
), vous pouvez définir la PDT en tant que PDT incrémentielle.
Pour en savoir plus, consultez la page de documentation sur les PDT incrémentielles.
Dialectes de base de données pris en charge pour les augmentations de tables PDT
Pour que Looker prenne en charge les augmentations de tables PDT dans votre projet, votre dialecte de base de données doit également les prendre en charge. Le tableau suivant indique les dialectes prenant en charge les augmentations de tables PDT dans la dernière version de Looker:
Dialecte | Compatible ? |
---|---|
Actian Avalanche | Non |
Amazon Athena | Non |
Amazon Aurora MySQL | Non |
Amazon Redshift | Oui |
Apache Druid | Non |
Apache Druid 0.13 et versions ultérieures | Non |
Apache Druid 0.18 et versions ultérieures | Non |
Apache Hive 2.3 et versions ultérieures | Non |
Apache Hive 3.1.2+ | Non |
Apache Spark 3 ou version ultérieure | Non |
ClickHouse | Non |
Cloudera Impala 3.1 ou version ultérieure | Non |
Cloudera Impala 3.1 ou version ultérieure avec pilote natif | Non |
Cloudera Impala avec pilote natif | Non |
DataVirtuality | Non |
Databricks | Oui |
Denodo 7 | Non |
Denodo 8 | Non |
Dremio | Non |
Dremio 11 et versions ultérieures | Non |
Exasol | Non |
Boulon | Non |
Ancien SQL de Google BigQuery | Non |
SQL standard Google BigQuery | Oui |
Google Cloud PostgreSQL | Oui |
Google Cloud SQL | Non |
Google Spanner | Non |
Prune | Oui |
HyperSQL | Non |
IBM Netezza | Non |
MariaDB | Non |
Microsoft Azure PostgreSQL | Oui |
Base de données Microsoft Azure SQL | Non |
Microsoft Azure Synapse Analytics | Oui |
Microsoft SQL Server 2008 ou version ultérieure | Non |
Microsoft SQL Server 2012 ou version ultérieure | Non |
Microsoft SQL Server 2016 | Non |
Microsoft SQL Server 2017 ou version ultérieure | Non |
MongoBI | Non |
MySQL | Oui |
MySQL 8.0.12 et versions ultérieures | Oui |
Oracle | Non |
Oracle ADWC | Non |
PostgreSQL 9.5 ou version ultérieure | Oui |
PostgreSQL antérieur à la version 9.5 | Oui |
PrestoDB | Non |
PrestoSQL | Non |
SAP HANA | Non |
SAP HANA 2+ | Non |
SingleStore | Non |
SingleStore 7+ | Non |
Snowflake | Oui |
Teradata | Non |
Trino | Non |
Vecteur | Non |
Vertica | Oui |
Création de PDT
Pour transformer une table dérivée en table dérivée persistante (PDT), vous devez définir une stratégie de persistance pour la table. Pour optimiser les performances, nous vous conseillons également d'ajouter une stratégie d'optimisation.
Stratégies de persistance
La persistance d'une table dérivée peut être gérée par Looker ou, pour les dialectes compatibles avec les vues matérialisées, par votre base de données à l'aide de vues matérialisées.
Pour rendre une table dérivée persistante, ajoutez l'un des paramètres suivants à la définition derived_table
:
- Paramètres de persistance gérés par Looker :
- Paramètres de persistance gérés par la base de données :
Avec les stratégies de persistance basées sur des déclencheurs (datagroup_trigger
, sql_trigger_value
et interval_trigger
), Looker conserve la PDT dans la base de données jusqu'à ce que la PDT soit déclenchée pour une recompilation. Une fois la PDT déclenchée, Looker la régénère pour remplacer la version précédente. Ainsi, avec les PDT basées sur des éléments déclencheurs, vos utilisateurs n'auront pas à attendre que la PDT soit générée pour obtenir des réponses pour les requêtes d'exploration issues de la PDT.
datagroup_trigger
Les groupes de données constituent la méthode la plus flexible pour créer de la persistance. Si vous avez défini un groupe de données avec sql_trigger
ou interval_trigger
, vous pouvez utiliser le paramètre datagroup_trigger
pour lancer la recompilation de vos tables dérivées persistantes (PDT).
Looker maintient la table PDT dans la base de données jusqu'au déclenchement de son groupe de données. Une fois le groupe de données déclenché, Looker régénère la table PDT pour remplacer la version précédente. Ainsi, dans la plupart des cas, les utilisateurs n'auront pas à attendre que la table PDT soit générée. Si un utilisateur demande des données à partir de la PDT en cours de création et que les résultats de la requête ne sont pas mis en cache, Looker renvoie les données de la PDT existante jusqu'à ce que la nouvelle PDT soit générée. Pour obtenir un aperçu des groupes de données, consultez la section Mettre en cache des requêtes.
Consultez la section sur le régénérateur Looker pour en savoir plus sur son fonctionnement.
sql_trigger_value
Le paramètre sql_trigger_value
déclenche la regénération d'une table dérivée persistante (PDT) en fonction de l'instruction SQL que vous fournissez. Si le résultat de l'instruction SQL est différent de la valeur précédente, la table PDT est régénérée. Sinon, la table PDT existante est maintenue dans la base de données. Ainsi, dans la plupart des cas, les utilisateurs n'auront pas à attendre que la table PDT soit générée. Si un utilisateur demande des données à partir de la PDT en cours de création et que les résultats de la requête ne sont pas mis en cache, Looker renvoie les données de la PDT existante jusqu'à ce que la nouvelle PDT soit générée.
Consultez la section sur le régénérateur Looker pour en savoir plus sur son fonctionnement.
interval_trigger
Le paramètre interval_trigger
déclenche la regénération d'une table dérivée persistante (PDT) en fonction d'un intervalle de temps que vous indiquez, par exemple "24 hours"
ou "60 minutes"
. Comme pour le paramètre sql_trigger
, cela signifie que la PDT est généralement prédéfinie lorsque les utilisateurs l'interrogent. Si un utilisateur demande des données à partir de la PDT en cours de création et que les résultats de la requête ne sont pas mis en cache, Looker renvoie les données de la PDT existante jusqu'à ce que la nouvelle PDT soit générée.
persist_for
Une autre option consiste à utiliser le paramètre persist_for
pour définir la durée pendant laquelle la table dérivée doit être stockée avant d'être marquée comme expirée, afin qu'elle ne soit plus utilisée pour les requêtes et soit supprimée de la base de données.
Une table dérivée persistante (PDT) persist_for
est créée lorsqu'un utilisateur y exécute pour la première fois une requête. Looker conserve ensuite la PDT dans la base de données pendant la durée spécifiée dans le paramètre persist_for
de la PDT. Si un utilisateur interroge la PDT dans le délai persist_for
, Looker utilise les résultats mis en cache si possible, ou exécute la requête sur la PDT.
Passé ce délai de persist_for
, Looker efface la PDT de votre base de données, et la PDT sera recréée la prochaine fois qu'un utilisateur l'interrogera, ce qui signifie que cette requête devra attendre la recompilation.
Les PDT qui utilisent persist_for
ne sont pas automatiquement recréées par le régénérateur de Looker, sauf dans le cas d'une cascade de dépendances de PDT. Lorsqu'une table persist_for
fait partie d'une cascade de dépendances avec des PDT basées sur des déclencheurs (PDT qui utilisent la stratégie de persistance datagroup_trigger
, interval_trigger
ou sql_trigger_value
), le régénérateur surveille et recompile la table persist_for
afin de recréer les autres tables dans la cascade. Consultez la section Comment Looker crée des tables dérivées en cascade sur cette page.
materialized_view: yes
Les vues matérialisées vous permettent d'exploiter les fonctionnalités de votre base de données pour conserver les tables dérivées dans votre projet Looker. Si votre dialecte de base de données prend en charge les vues matérialisées et que votre connexion Looker est configurée avec l'option Activer les PDT activée, vous pouvez créer une vue matérialisée en spécifiant materialized_view: yes
pour une table dérivée. Les vues matérialisées sont compatibles avec les tables dérivées natives et les tables dérivées basées sur SQL.
À l'instar d'une table dérivée persistante (PDT), une vue matérialisée est un résultat de requête stocké sous forme de table dans le schéma entièrement nouveau de votre base de données. La principale différence entre une PDT et une vue matérialisée se situe au niveau de l'actualisation des tables :
- Pour les PDT, la stratégie de persistance est définie dans Looker, et la persistance est gérée par Looker.
- Pour les vues matérialisées, la base de données est responsable de l'entretien et de l'actualisation des données de la table.
Pour cette raison, la fonctionnalité vue matérialisée nécessite une connaissance avancée de votre dialecte et de ses caractéristiques. Dans la plupart des cas, votre base de données actualisera la vue matérialisée à chaque fois qu'elle détectera de nouvelles données dans les tables interrogées par la vue matérialisée. Les vues matérialisées sont optimales pour les scénarios requérant des données en temps réel.
Consultez la page de documentation du paramètre materialized_view
pour en savoir plus sur la compatibilité des dialectes, les exigences et les points importants à prendre en compte.
Stratégies d'optimisation
Les tables dérivées persistantes (PDT) étant stockées dans votre base de données, vous devez optimiser vos PDT à l'aide des stratégies suivantes, compatibles avec votre dialecte:
Par exemple, pour rendre l'exemple de table dérivée persistante, vous pouvez configurer la table de sorte qu'elle soit recréée lorsque le groupe de données orders_datagroup
se déclenche, et ajouter des index à la fois sur customer_id
et first_order
, comme ceci:
view: customer_order_summary {
derived_table: {
explore_source: orders {
...
}
datagroup_trigger: orders_datagroup
indexes: ["customer_id", "first_order"]
}
}
Si vous n'ajoutez pas d'index (ni d'équivalent pour votre dialecte), Looker vous invite à le faire pour améliorer les performances des requêtes.
Cas d'utilisation des PDT
Les tables dérivées persistantes (PDT) sont utiles car elles peuvent améliorer les performances d'une requête en conservant les résultats de la requête dans une table.
Il est recommandé aux développeurs d'essayer de modéliser les données sans utiliser de PDT, jusqu'à ce que cela soit absolument nécessaire.
Dans certains cas, les données peuvent être optimisées par d'autres moyens. Par exemple, l'ajout d'un index ou la modification du type de données d'une colonne peut résoudre un problème sans qu'il soit nécessaire de créer une PDT. Veillez à analyser les plans d'exécution des requêtes lentes à l'aide de l'outil Explain from SQL Runner.
En plus de réduire la durée des requêtes et la charge de la base de données pour les requêtes fréquemment exécutées, il existe plusieurs autres cas d'utilisation des tables PDT:
Vous pouvez également utiliser une table PDT pour définir une clé primaire s'il n'existe aucun moyen raisonnable d'identifier une ligne unique dans une table en tant que clé primaire.
Utiliser des PDT pour tester les optimisations
Vous pouvez utiliser des PDT pour tester différentes options d'indexation, de distribution et d'autres options d'optimisation sans avoir besoin d'une assistance importante de la part de vos développeurs DBA ou ETL.
Imaginons que vous avez une table, mais que vous souhaitez tester différents index. Le code LookML initial pour la vue peut se présenter comme suit:
view: customer {
sql_table_name: warehouse.customer ;;
}
Pour tester les stratégies d'optimisation, vous pouvez utiliser le paramètre indexes
afin d'ajouter des index au code LookML comme suit:
view: customer {
# sql_table_name: warehouse.customer
derived_table: {
sql: SELECT * FROM warehouse.customer ;;
persist_for: "8 hours"
indexes: [customer_id, customer_name, salesperson_id]
}
}
Interrogez la vue une fois pour générer la PDT. Exécutez ensuite vos requêtes de test et comparez vos résultats. Si vos résultats sont favorables, vous pouvez demander à votre équipe DBA ou ETL d'ajouter les index à la table d'origine.
N'oubliez pas de rétablir le code de vue pour supprimer la PDT.
Utiliser des PDT pour pré-joindre ou agréger des données
Il peut être utile de pré-joindre ou de pré-agréger des données afin d'ajuster l'optimisation des requêtes pour des volumes importants ou plusieurs types de données.
Par exemple, supposons que vous souhaitiez créer un rapport sur les clients par cohorte en fonction de la date à laquelle ils ont passé leur première commande. Cette requête peut être coûteuse à exécuter plusieurs fois chaque fois que les données sont nécessaires en temps réel ; cependant, vous pouvez la calculer une seule fois, puis réutiliser les résultats avec une PDT:
view: customer_order_facts {
derived_table: {
sql: SELECT
c.customer_id,
MIN(o.order_date) OVER (PARTITION BY c.customer_id) AS first_order_date,
MAX(o.order_date) OVER (PARTITION BY c.customer_id) AS most_recent_order_date,
COUNT(o.order_id) OVER (PARTITION BY c.customer_id) AS lifetime_orders,
SUM(o.order_value) OVER (PARTITION BY c.customer_id) AS lifetime_value,
RANK() OVER (PARTITION BY c.customer_id ORDER BY o.order_date ASC) AS order_sequence,
o.order_id
FROM warehouse.customer c LEFT JOIN warehouse.order o ON c.customer_id = o.customer_id
;;
sql_trigger_value: SELECT CURRENT_DATE ;;
indexes: [customer_id, order_id, order_sequence, first_order_date]
}
}
Tables dérivées en cascade
Il est possible de référencer une table dérivée dans la définition d'une autre en créant une chaîne de tables dérivées en cascade ou de tables dérivées persistantes en cascade (PDT), selon le cas. Voici un exemple de tables dérivées en cascade : la table TABLE_D
, qui dépend d'une autre table, TABLE_C
, tandis que TABLE_C
dépend de TABLE_B
et TABLE_B
de TABLE_A
.
Syntaxe pour référencer une table dérivée
Pour référencer une table dérivée dans une autre table dérivée, utilisez cette syntaxe :
`${derived_table_or_view_name.SQL_TABLE_NAME}`
Dans ce format, SQL_TABLE_NAME
est une chaîne littérale. Par exemple, vous pouvez référencer la table dérivée clean_events
avec la syntaxe suivante:
`${clean_events.SQL_TABLE_NAME}`
Vous pouvez utiliser la même syntaxe pour désigner une vue LookML. Là encore, dans ce cas, SQL_TABLE_NAME
est une chaîne littérale.
Dans l'exemple suivant, la PDT clean_events
est créée à partir de la table events
de la base de données. La PDT clean_events
exclut les lignes indésirables de la table de base de données events
. Une deuxième PDT est affichée. La PDT event_summary
est un résumé de la PDT clean_events
. La table event_summary
est régénérée chaque fois que de nouvelles lignes sont ajoutées à clean_events
.
La PDT event_summary
et la PDT clean_events
sont des PDT en cascade, où event_summary
dépend de clean_events
(puisque event_summary
est défini à l'aide de la PDT clean_events
). Cet exemple particulier pourrait être fait plus efficacement dans une seule PDT, mais il est utile pour démontrer les références aux tables dérivées.
view: clean_events {
derived_table: {
sql:
SELECT *
FROM events
WHERE type NOT IN ('test', 'staff') ;;
datagroup_trigger: events_datagroup
}
}
view: events_summary {
derived_table: {
sql:
SELECT
type,
date,
COUNT(*) AS num_events
FROM
${clean_events.SQL_TABLE_NAME} AS clean_events
GROUP BY
type,
date ;;
datagroup_trigger: events_datagroup
}
}
Bien que cela ne soit pas toujours obligatoire, lorsque vous faites référence à une table dérivée de cette manière, il est souvent utile de créer un alias pour la table en utilisant le format suivant:
${derived_table_or_view_name.SQL_TABLE_NAME} AS derived_table_or_view_name
L'exemple précédent donne le résultat suivant :
${clean_events.SQL_TABLE_NAME} AS clean_events
Il est judicieux d'utiliser un alias, car en arrière-plan, les tables PDT sont nommées dans la base de données avec de longs codes. Dans certains cas (en particulier avec les clauses ON
), il est facile d'oublier que vous devez utiliser la syntaxe ${derived_table_or_view_name.SQL_TABLE_NAME}
pour récupérer ce long nom. Un alias permet d'éviter ce type d'erreur.
Comment Looker génère des tables dérivées en cascade
Dans le cas de tables dérivées temporaires en cascade, si les résultats de la requête d'un utilisateur ne sont pas dans le cache, Looker génère toutes les tables dérivées nécessaires à la requête. Si vous avez un TABLE_D
dont la définition contient une référence à TABLE_C
, TABLE_D
dépend de TABLE_C
. Cela signifie que si vous interrogez TABLE_D
et que la requête ne se trouve pas dans le cache de Looker, Looker recompile TABLE_D
. Mais d'abord, il doit recompiler TABLE_C
.
Prenons maintenant un scénario de tables dérivées temporaires en cascade dans lequel TABLE_D
dépend de TABLE_C
, qui dépend de TABLE_B
, qui dépend de TABLE_A
. Si Looker ne dispose pas de résultats valides dans le cache pour une requête sur TABLE_C
, il créera toutes les tables dont il a besoin pour la requête. Looker va donc créer TABLE_A
, puis TABLE_B
, puis TABLE_C
:
Dans ce scénario, TABLE_A
doit terminer la génération avant que Looker ne puisse commencer à générer TABLE_B
, et ainsi de suite, jusqu'à ce que TABLE_C
soit terminé et que Looker puisse fournir les résultats de la requête. (Comme TABLE_D
n'est pas nécessaire pour répondre à cette requête, Looker ne recompile pas TABLE_D
pour le moment.)
Consultez la page de documentation du paramètre datagroup
pour obtenir un exemple de scénario de tables PDT en cascade qui utilisent le même groupe de données.
La même logique de base s'applique aux tables PDT: Looker crée toutes les tables requises pour répondre à une requête, tout au long de la chaîne de dépendances. Mais avec les PDT, il est souvent le cas que les tables existent déjà et n'ont pas besoin d'être recréées. Avec les requêtes des utilisateurs standards sur les PDT en cascade, Looker régénère les PDT dans la cascade uniquement s'il n'existe aucune version valide des PDT dans la base de données. Si vous souhaitez forcer la recompilation de toutes les PDT dans une cascade, vous pouvez recréer manuellement les tables pour une requête par le biais d'une exploration.
Il est essentiel de comprendre que, dans le cas d'une cascade de PDT, une PDT dépendante interroge essentiellement la PDT dont elle dépend. Ce point est important, en particulier pour les tables PDT qui utilisent la stratégie persist_for
. En règle générale, les PDT persist_for
sont créées lorsqu'un utilisateur les interroge, restent dans la base de données jusqu'à la fin de l'intervalle persist_for
, puis ne sont pas recréées tant qu'elles ne sont pas interrogées par un utilisateur. Toutefois, si une PDT persist_for
fait partie d'une cascade avec des PDT basées sur des déclencheurs (PDT qui utilisent la stratégie de persistance datagroup_trigger
, interval_trigger
ou sql_trigger_value
), la PDT persist_for
est essentiellement interrogée chaque fois que ses PDT dépendantes sont régénérées. La PDT persist_for
sera donc recréée selon la programmation de ses PDT dépendantes. Cela signifie que la stratégie de persistance des tables PDT persist_for
peut être affectée par la stratégie de persistance de leurs dépendances.
Régénération manuelle de tables persistantes pour une requête
Les utilisateurs peuvent sélectionner l'option Recréer les tables dérivées et exécuter dans le menu d'une exploration pour remplacer les paramètres de persistance et recompiler toutes les tables dérivées persistantes (PDT) et les tables agrégées requises pour la requête actuelle dans l'exploration:
Cette option n'est visible que par les utilisateurs disposant de l'autorisation develop
, et uniquement après le chargement de la requête d'exploration.
L'option Recréer les tables dérivées et exécuter permet de recréer toutes les tables persistantes (toutes les tables PDT et tables agrégées) nécessaires pour répondre à la requête, quelle que soit leur stratégie de persistance. Cela inclut toutes les tables agrégées et les PDT dans la requête actuelle, ainsi que les tables agrégées et les PDT référencées par les tables agrégées et les PDT dans la requête actuelle.
Dans le cas de PDT incrémentales, l'option Rebuild Derived Tables & Run (Recompiler les tables dérivées et exécuter) déclenche la compilation d'un nouvel incrément. Avec les PDT incrémentielles, l'incrément inclut la période spécifiée dans le paramètre increment_key
, ainsi que le nombre de périodes précédentes spécifiées dans le paramètre increment_offset
, le cas échéant. Consultez la page de documentation Augmentations de tables PDT pour obtenir des exemples de création de PDT incrémentielles en fonction de leur configuration.
Dans le cas de PDT en cascade, cela implique de recréer toutes les tables dérivées dans la cascade, en commençant par la partie supérieure. Ce comportement est le même que lorsque vous interrogez une table dans une cascade de tables dérivées temporaires:
Notez les points suivants à propos de la recréation manuelle des tables dérivées:
- Pour l'utilisateur qui lance l'opération Rebuild Derived Tables & Run (Recompiler les tables dérivées et exécuter), la requête attend que les tables soient régénérées avant de charger les résultats. Les requêtes des autres utilisateurs continueront d'utiliser les tables existantes. Une fois les tables persistantes régénérées, tous les utilisateurs s'en servent. Bien que ce processus soit conçu pour éviter d'interrompre les requêtes des autres utilisateurs pendant la recompilation des tables, ceux-ci peuvent être affectés par la charge supplémentaire pesant sur votre base de données. Si le lancement d'une régénération pendant les heures de bureau risque de soumettre votre base de données à une pression inacceptable, vous devrez peut-être demander aux utilisateurs de ne pas régénérer certaines tables PDT ni agréger de tables pendant ces heures.
Si un utilisateur est en mode Développement et que l'exploration est basée sur une table de développement, l'opération Recompiler les tables dérivées et exécuter régénère la table de développement pour l'exploration, et non la table de production. Toutefois, si l'exploration en mode Développement utilise la version de production d'une table dérivée, la table de production sera recompilée. Pour en savoir plus sur les tables de développement et de production, consultez la section Tables persistantes en mode Développement.
Pour les instances hébergées par Looker, si la recréation de la table dérivée prend plus d'une heure, la table ne sera pas régénérée correctement et la session de navigateur expire. Consultez la section Expirations de délai pour les requêtes et mise en file d'attente sur la page de documentation Paramètres d'administration : requêtes pour en savoir plus sur les délais d'inactivité pouvant affecter les processus Looker.
Tables persistantes en mode Développement
Looker présente des comportements spéciaux pour la gestion des tables persistantes en mode Développement.
Si vous interrogez une table persistante en mode Développement sans modifier sa définition, Looker interroge la version de production de cette table. Si vous apportez à la définition de la table une modification qui affecte ses données ou la façon dont elle est interrogée, une nouvelle version de développement de la table sera créée la prochaine fois que vous interrogerez la table en mode Développement. Une table de développement de la sorte vous permet de tester les modifications sans déranger l'utilisateur final.
Quelles commandes entraînent la création d'une table de développement par Looker ?
Dans la mesure du possible, Looker utilise la table de production existante pour répondre aux requêtes, que vous soyez ou non en mode Développement. Cependant, dans certains cas, Looker ne peut pas utiliser de table de production pour les requêtes en mode Développement :
- Si votre table persistante comporte un paramètre qui restreint son ensemble de données pour fonctionner plus rapidement en mode Développement
- Si vous avez apporté des modifications à la définition de votre table persistante qui affectent les données de la table
Looker créera une table de développement si vous êtes en mode Développement et que vous interrogez une table dérivée basée sur SQL définie à l'aide d'une clause WHERE
conditionnelle avec des instructions if prod
et if dev
.
Pour les tables persistantes qui ne comportent pas de paramètre permettant de limiter l'ensemble de données en mode Développement, Looker utilise la version de production de la table pour répondre aux requêtes en mode Développement, sauf si vous modifiez la définition de la table et puis interrogez la table en mode Développement. Cela s'applique à toutes les modifications affectant les données dans la table ou la façon dont elle est interrogée.
Voici quelques exemples de modifications entraînant la création d'une version de développement d'une table persistante par Looker (Looker créera la table uniquement si vous interrogez la table après avoir apporté ces modifications) :
- Modifier la requête sur laquelle est basée la table persistante, par exemple le paramètre
explore_source
,sql
,query
,sql_create
oucreate_process
de la table persistante elle-même ou de toute table requise (dans le cas de tables dérivées en cascade) - Modifier la stratégie de persistance de la table, par exemple en modifiant son paramètre
datagroup_trigger
,sql_trigger_value
,interval_trigger
oupersist_for
- Modifier le nom d'une table dérivée
view
- Modifier la
increment_key
ou laincrement_offset
d'une PDT incrémentielle - Modifier la
connection
utilisée par le modèle associé
Pour les modifications qui ne modifient pas les données de la table ou qui n'affectent pas la façon dont Looker l'interroge, Looker ne crée pas de table de développement. Le paramètre publish_as_db_view
en est un bon exemple. En mode Développement, si vous ne modifiez que le paramètre publish_as_db_view
d'une table dérivée, Looker n'a pas besoin de recréer la table dérivée et ne créera donc pas de table de développement.
Durée de persistance des tables de développement
Quelle que soit la stratégie de persistance réelle de la table, Looker traite les tables persistantes de développement comme si leur stratégie de persistance était persist_for: "24 hours"
. Looker fait cela pour s'assurer que les tables de développement ne sont pas persistantes pendant plus d'une journée, car un développeur Looker peut interroger de nombreuses itérations d'une table pendant le développement, et chaque fois qu'une nouvelle table de développement est créée. Pour éviter que les tables de développement n'encombrent la base de données, Looker applique la stratégie persist_for: "24 hours"
pour s'assurer que les tables sont régulièrement nettoyées de la base de données.
Sinon, Looker génère des tables dérivées persistantes (PDT) et des tables agrégées en mode Développement de la même manière qu'il crée des tables persistantes en mode Production.
Si une table de développement est conservée dans votre base de données lorsque vous déployez des modifications sur une table PDT ou une table agrégée, Looker peut souvent utiliser la table de développement comme table de production. Ainsi, vos utilisateurs n'ont pas à attendre la création de la table lorsqu'ils l'interrogent.
Notez que, lorsque vous déployez vos modifications, vous devez peut-être régénérer la table pour qu'elle soit interrogée en production, en fonction de la situation :
- Si plus de 24 heures se sont écoulées depuis l'interrogation de la table en mode Développement, la version de développement de la table sera marquée comme expirée et ne sera pas utilisée pour les requêtes. Vous pouvez rechercher des PDT non compilées à l'aide de l'IDE Looker ou dans l'onglet Développement de la page Tables dérivées persistantes. Si vous disposez de tables PDT déconstruites, vous pouvez les interroger en mode Développement avant d'apporter vos modifications afin que la table de développement puisse être utilisée en production.
- Si une table persistante comporte le paramètre
dev_filters
(pour les tables dérivées natives) ou la clauseWHERE
conditionnelle qui utilise les instructionsif prod
etif dev
(pour les tables dérivées basées sur SQL), la table de développement ne peut pas être utilisée en tant que version de production, car celle-ci dispose d'un ensemble de données abrégé. Dans ce cas, une fois que vous avez fini de développer la table et avant de déployer vos modifications, vous pouvez mettre en commentaire le paramètredev_filters
ou la clause conditionnelleWHERE
, puis interroger la table en mode Développement. Looker générera alors une version complète de la table qui pourra être utilisée pour la production lorsque vous déploierez vos modifications.
Sinon, si vous déployez vos modifications alors qu'aucune table de développement valide ne peut être utilisée en tant que table de production, Looker la recréera la prochaine fois qu'elle sera interrogée en mode production (pour les tables persistantes utilisant la stratégie persist_for
) ou la prochaine fois que le régénérateur s'exécutera (pour les tables persistantes qui utilisent datagroup_trigger
, interval_trigger
ou sql_trigger_value
).
Recherche de tables PDT déconstruites en mode Développement
Si une table de développement est conservée dans votre base de données lorsque vous déployez des modifications sur une table dérivée persistante (PDT) ou une table agrégée, Looker peut souvent utiliser la table de développement comme table de production. Ainsi, vos utilisateurs n'ont pas à attendre la création de la table lorsqu'ils l'interrogent. Pour en savoir plus, consultez les sections Durée de conservation des tables de développement par Looker et Éléments qui invitent Looker à créer une table de développement sur cette page.
Par conséquent, il est préférable que toutes vos tables PDT soient créées lors du déploiement en production afin que ces tables puissent être utilisées immédiatement en tant que versions de production.
Vous pouvez rechercher des PDT non compilées dans votre projet dans le panneau Project Health (État du projet). Cliquez sur l'icône État du projet dans l'IDE Looker pour ouvrir le panneau État du projet. Cliquez ensuite sur le bouton Validate PDT Status (Valider l'état des PDT.
Si des PDT non compilées, le panneau Project Health (État du projet) les affiche:
Si vous disposez de l'autorisation see_pdts
, vous pouvez cliquer sur le bouton Accéder à la gestion des PDT. Looker ouvre l'onglet Développement de la page Tables dérivées persistantes et filtre les résultats en fonction de votre projet LookML spécifique. Vous pouvez consulter les tables PDT construites et déconstruites, et accéder à d'autres informations de résolution. Pour en savoir plus, consultez la page de documentation Paramètres d'administration – Tables dérivées persistantes.
Une fois que vous avez identifié une PDT non compilée dans votre projet, vous pouvez créer une version de développement en ouvrant une exploration qui interroge la table, puis en utilisant l'option Recréer les tables dérivées et exécuter dans le menu "Explorer". Consultez la section Recréer manuellement des tables persistantes pour une requête de cette page.
Partage et nettoyage de tables
Au sein d'une instance Looker donnée, Looker partage entre les utilisateurs les tables persistantes ayant la même définition et faisant appel à la même méthode de persistance. En outre, si la définition d'une table cesse d'exister, Looker marque la table comme expirée.
Ces mesures offrent plusieurs avantages :
- Si vous n'avez apporté aucune modification à une table en mode Développement, vos requêtes utiliseront les tables de production existantes. C'est le cas, sauf si votre table est une table dérivée basée sur SQL définie à l'aide d'une clause
WHERE
conditionnelle avec des instructionsif prod
etif dev
. Si la table est définie avec une clauseWHERE
conditionnelle, Looker crée une table de développement si vous interrogez la table en mode Développement. (Pour les tables dérivées natives avec le paramètredev_filters
, Looker a la logique d'utiliser la table de production pour répondre aux requêtes en mode Développement, sauf si vous modifiez la définition de la table, puis interrogez la table en mode Développement.) - Si deux développeurs apportent la même modification à une table en mode Développement, ils partageront la même table de développement.
- Après la mise en production des changements apportés en mode Développement, l'ancienne définition de production n'existe plus, de sorte que l'ancienne table de production est marquée comme étant expirée et est supprimée.
- Si vous décidez d'annuler les modifications effectuées en mode Développement, cette définition de la table n'existe plus, et les tables de développement devenues inutiles sont marquées comme expirées et supprimées.
Gains d'efficacité en mode Développement
Dans certains cas, la table dérivée persistante (PDT) que vous créez prend beaucoup de temps à générer, ce qui peut s'avérer fastidieux si vous testez de nombreuses modifications en mode Développement. Dans ce cas, vous pouvez demander à Looker de créer des versions plus petites d'une table dérivée en mode Développement.
Pour les tables dérivées natives, vous pouvez utiliser le sous-paramètre dev_filters
de explore_source
pour spécifier des filtres qui ne sont appliqués qu'aux versions de développement de la table dérivée:
view: e_faa_pdt {
derived_table: {
...
datagroup_trigger: e_faa_shared_datagroup
explore_source: flights {
dev_filters: [flights.event_date: "90 days"]
filters: [flights.event_date: "2 years", flights.airport_name: "Yucca Valley Airport"]
column: id {}
column: airport_name {}
column: event_date {}
}
}
...
}
Cet exemple inclut un paramètre dev_filters
qui filtre les données pour n'afficher que les 90 derniers jours, et un paramètre filters
qui filtre les données pour les deux dernières années et pour l'aéroport de Yucca Valley.
Le paramètre dev_filters
fonctionne conjointement avec le paramètre filters
afin que tous les filtres soient appliqués à la version de développement de la table. Si dev_filters
et filters
spécifient des filtres pour la même colonne, dev_filters
est prioritaire pour la version de développement de la table. Dans cet exemple, la version de développement de la table filtrera les données des 90 derniers jours pour l'aéroport de Yucca Valley.
Pour les tables dérivées basées sur SQL, Looker accepte une clause WHERE conditionnelle avec différentes options pour les versions de production (if prod
) et de développement (if dev
) de la table:
view: my_view {
derived_table: {
sql:
SELECT
columns
FROM
my_table
WHERE
-- if prod -- date > '2000-01-01'
-- if dev -- date > '2020-01-01'
;;
}
}
Dans cet exemple, la requête inclut toutes les données datées à partir de l'an 2000 en mode Production, mais seulement à partir de 2020 en mode Développement. Utilisée à bon escient pour limiter le jeu de résultats obtenu et accélérer les requêtes, cette fonctionnalité peut simplifier considérablement la validation des modifications apportées en mode Développement.
Comment Looker crée des PDT
Lorsqu'une table dérivée persistante (PDT) a été définie et qu'elle est exécutée pour la première fois ou qu'elle est déclenchée par le régénérateur pour la recompiler conformément à sa stratégie de persistance, Looker procède comme suit:
- Utilisez le code SQL de la table dérivée pour façonner une instruction CREATE TABLE AS SELECT (ou CTAS) et l'exécuter. Par exemple, pour recompiler une PDT appelée
customer_orders_facts
:CREATE TABLE tmp.customer_orders_facts AS SELECT ... FROM ... WHERE ...
- Émettre les instructions permettant de créer les index lors de la création de la table
- Renommez la table LC$.. ("Looker Create") en LR$.. ("Looker Read"), pour indiquer que la table est prête à l'emploi.
- Supprimer toute ancienne version de la table qui ne devrait plus être utilisée
Cela a plusieurs conséquences importantes:
- Le code SQL qui forme la table dérivée doit être valide dans une instruction CTAS.
- Les alias de colonne de l'ensemble de résultats de l'instruction SELECT doivent être des noms de colonne valides.
- Les noms utilisés lorsque vous spécifiez la distribution, les clés de tri et les index doivent correspondre aux noms de colonne répertoriés dans la définition SQL de la table dérivée, et non aux noms de champs définis dans le code LookML.
Le régénérateur Looker
Le régénérateur Looker vérifie le statut et lance les régénérations des tables persistantes à déclenchement. Une table persistante à déclenchement est une table dérivée persistante (PDT) ou une table agrégée qui utilise un déclencheur comme stratégie de persistance:
- Pour les tables qui utilisent
sql_trigger_value
, le déclencheur est une requête spécifiée dans le paramètresql_trigger_value
de la table. Le régénérateur Looker déclenche une régénération de la table lorsque le résultat du dernier contrôle de requête de déclenchement diffère du précédent. Par exemple, si votre table dérivée est persistante avec la requête SQLSELECT CURDATE()
, le régénérateur Looker régénérera la table la prochaine fois qu'il vérifiera le déclencheur après la modification de la date. - Pour les tables qui utilisent
interval_trigger
, le déclencheur est une durée spécifiée dans le paramètreinterval_trigger
de la table. Le régénérateur Looker déclenche une régénération de la table lorsque la durée spécifiée est passée. - Pour les tables qui utilisent
datagroup_trigger
, le déclencheur peut être une requête spécifiée dans le paramètresql_trigger
du groupe de données associé, ou le déclencheur peut être une durée spécifiée dans le paramètreinterval_trigger
du groupe de données.
Le régénérateur Looker lance également des recompilations pour les tables persistantes qui utilisent le paramètre persist_for
, mais uniquement lorsque la table persist_for
est une cascade de dépendances d'une table persistante à déclencheur. Dans ce cas, le régénérateur Looker lance les régénérations d'une table persist_for
, car la table est nécessaire pour recréer les autres tables dans la cascade. Sinon, le régénérateur ne surveille pas les tables persistantes qui utilisent la stratégie persist_for
.
Le cycle de régénérateur Looker commence à un intervalle régulier configuré par votre administrateur Looker dans le paramètre Planification de maintenance des groupes de données et des tables PDT de votre connexion à la base de données (la valeur par défaut est un intervalle de cinq minutes). Toutefois, le régénérateur Looker ne démarre pas de nouveau cycle tant qu'il n'a pas terminé toutes les vérifications et reconstitué la PDT à partir du dernier cycle. Par conséquent, si vous avez des compilations de tables PDT de longue durée, le cycle de régénérateur Looker risque de ne pas s'exécuter aussi souvent que défini dans le paramètre Programmation de maintenance des groupes de données et des tables PDT. D'autres facteurs peuvent affecter le temps nécessaire à la recompilation de vos tables, comme indiqué dans la section Remarques importantes concernant la mise en œuvre des tables persistantes sur cette page.
Si la création d'une table PDT échoue, le régénérateur peut tenter de régénérer la table lors du prochain cycle de régénérateur:
- Si le paramètre Réessayer les générations de tables PDT ayant échoué est activé sur votre connexion à la base de données, le régénérateur Looker tentera de régénérer la table lors du prochain cycle de régénérateur, même si la condition de déclenchement de la table n'est pas remplie.
- Si le paramètre Relancer les générations de tables PDT ayant échoué est désactivé, le régénérateur Looker ne tentera pas de régénérer la table tant que la condition de déclenchement de la table PDT ne sera pas remplie.
Si un utilisateur demande des données à partir de la table persistante en cours de création et que les résultats de la requête ne sont pas mis en cache, Looker vérifie si la table existante est toujours valide. (La table précédente peut ne pas être valide si elle n'est pas compatible avec la nouvelle version de la table, ce qui peut se produire à cause d'une définition différente ou d'une connexion de base de données différente de la nouvelle table ou parce que la nouvelle table a été créée avec une version différente de Looker.) Si la table existante est toujours valide, Looker renvoie les données issues de la table existante jusqu'à la génération de la nouvelle table. Autrement, si la table existante n'est pas valide, Looker renvoie les résultats de requête une fois que la nouvelle table est régénérée.
Éléments importants à prendre en compte lors de la mise en œuvre de tables persistantes
Compte tenu de l'utilité des tables persistantes (PDT et tables agrégées), il est facile d'en accumuler un grand nombre sur votre instance Looker. Il est possible de créer un scénario dans lequel le régénérateur Looker doit créer plusieurs tables en même temps. Vous pouvez créer un scénario dans lequel les tables mettent beaucoup de temps à être recompilées, en particulier avec les tables en cascade ou les tables de longue durée. Il est également possible que les utilisateurs mettent du temps à obtenir les résultats de requête d'une table pendant que la base de données est en train de générer une table.
Le régénérateur de Looker vérifie les déclencheurs des tables PDT pour voir s'il doit régénérer les tables persistantes à déclenchement. Le cycle du régénérateur est défini à un intervalle régulier configuré par votre administrateur Looker dans le paramètre Calendrier de maintenance des groupes de données et des tables PDT de votre connexion à la base de données (la valeur par défaut est un intervalle de cinq minutes).
Plusieurs facteurs peuvent affecter le temps nécessaire à la recréation de vos tables:
- Votre administrateur Looker a peut-être modifié l'intervalle des vérifications du déclencheur des régénérateurs à l'aide du paramètre Planification de maintenance des groupes de données et des tables PDT sur votre connexion à la base de données.
- Le régénérateur Looker ne démarre pas de nouveau cycle tant qu'il n'a pas terminé toutes les vérifications et reconstitué la PDT à partir du dernier cycle. Ainsi, si vous avez des compilations de tables PDT de longue durée, le cycle de régénérateur Looker peut ne pas être aussi fréquent que le paramètre Programmation de maintenance des groupes de données et des tables PDT.
- Par défaut, le régénérateur peut lancer la régénération d'une table PDT ou agrégée à un temps donnée sur une connexion. Un administrateur Looker peut ajuster le nombre autorisé de reconstructions simultanées autorisées pour le régénérateur à l'aide du champ Nombre maximal de connexions du générateur de tables PDT dans les paramètres d'une connexion.
- Toutes les PDT et les tables agrégées déclenchées par le même
datagroup
seront recréées au cours du même processus de régénération. Cela peut représenter une charge importante si de nombreuses tables utilisent le groupe de données, soit directement, soit en raison de dépendances en cascade.
Outre les considérations précédentes, il existe également des situations dans lesquelles vous devez éviter d'ajouter de la persistance à une table dérivée:
- Lorsque les tables dérivées sont extended : chaque extension d'une table PDT crée une copie de la table dans votre base de données.
- Lorsque les tables dérivées utilisent des filtres modélisés ou des paramètres Liquid : la persistance n'est pas compatible avec les tables dérivées qui utilisent des filtres basés sur un modèle ou des paramètres Liquid.
- Lorsque des tables dérivées natives sont créées à partir d'explorations utilisant des attributs utilisateur avec
access_filters
ousql_always_where
, des copies de la table sont créées dans votre base de données pour chaque valeur d'attribut utilisateur possible spécifiée. - Lorsque les données sous-jacentes changent fréquemment et que votre dialecte de base de données ne prend pas en charge les PDT incrémentielles.
- Lorsque le coût et le temps liés à la création de tables PDT sont trop élevés.
Selon le nombre et la complexité des tables persistantes sur votre connexion Looker, la file d'attente peut contenir de nombreuses tables persistantes en attente de contrôle et de régénération à chaque cycle. Il est donc important de garder ces facteurs à l'esprit lors de la mise en œuvre de tables dérivées sur votre instance Looker.
Gestion des PDT à grande échelle via l'API
Plus vous créez de PDT sur votre instance, plus la surveillance et la gestion des tables dérivées persistantes (PDT) qui s'actualisent selon des planifications variables deviennent de plus en plus complexes. Envisagez d'utiliser l'intégration Apache Airflow de Looker pour gérer vos planifications de PDT en même temps que vos autres processus ETL et ELT.
Surveillance et correction de tables PDT
Si vous utilisez des tables dérivées persistantes (PDT), et en particulier des PDT en cascade, il est utile de connaître leur état. Vous pouvez accéder à la page d'administration Tables dérivées persistantes de Looker pour consulter l'état de vos PDT. Pour en savoir plus, consultez la page de documentation Paramètres d'administration – Tables dérivées persistantes.
Lorsque vous tentez de résoudre les problèmes liés aux PDT:
- Portez une attention particulière à la distinction entre les tables de développement et les tables de production lorsque vous examinez le journal des événements de PDT.
- Assurez-vous qu'aucune modification n'a été apportée au schéma créé de toutes pièces dans lequel Looker stocke les tables dérivées persistantes. Si des modifications ont été apportées, vous devrez peut-être mettre à jour les paramètres de connexion dans la section Administration de Looker, puis redémarrer Looker pour rétablir le fonctionnement normal des tables PDT.
- Déterminez si toutes les tables PDT posent problème, ou une seule d'entre elles. Dans ce dernier cas, le problème est probablement dû à une erreur LookML ou SQL.
- Déterminez si les problèmes liés à la table PDT coïncident avec les régénérations planifiées.
- Assurez-vous que toutes les requêtes
sql_trigger_value
sont évaluées correctement, et qu'elles ne renvoient qu'une seule ligne et une seule colonne. Pour les tables PDT basées sur SQL, vous pouvez le faire en les exécutant dans SQL Runner. (L'application d'unLIMIT
vous protège contre les requêtes intempestives.) Pour en savoir plus sur l'utilisation de SQL Runner pour déboguer des tables dérivées, consultez le post de la communauté Utiliser l'exécuteur SQL pour tester les tables dérivées . - Pour les tables PDT basées sur SQL, utilisez SQL Runner pour vérifier si le code SQL d'une table s'exécute sans erreur. (N'oubliez pas d'appliquer un
LIMIT
dans l'exécuteur SQL pour que les délais des requêtes soient raisonnables.) - Pour les tables dérivées basées sur SQL, évitez d'utiliser des expressions de table courantes (CTE). L'utilisation de CTE avec des DT crée des instructions
WITH
imbriquées qui peuvent entraîner l'échec des PDT sans avertissement. Utilisez plutôt le code SQL de votre CTE pour créer un DT secondaire et référencez-le à partir de votre premier DT à l'aide de la syntaxe${derived_table_or_view_name.SQL_TABLE_NAME}
. - Vérifiez que toutes les tables dont dépend la PDT problématique (qu'il s'agisse de tables normales ou de tables PDT elles-mêmes) existent et peuvent être interrogées.
- Assurez-vous que les tables dont la PDT posant problème dépend ne possèdent pas de verrous partagés ou exclusifs. Pour créer une table PDT, Looker doit acquérir un verrou exclusif sur la table à mettre à jour. Cela crée un conflit avec les autres verrous partagés ou exclusifs associés à la table. Looker ne peut pas mettre à jour la table PDT tant que les autres verrous ne sont pas supprimés. Il en va de même pour tous les verrous exclusifs sur la table à partir de laquelle Looker crée une table PDT. Si un verrou exclusif s'applique à une table, Looker ne pourra pas acquérir un verrou partagé pour exécuter des requêtes tant que le verrou exclusif n'aura pas été effacé.
- Utilisez le bouton Show Processes (Afficher les processus) dans SQL Runner. Si de nombreux processus sont actifs, l'exécution des requêtes pourrait être ralentie.
- Surveillez les commentaires figurant dans la requête. Consultez la section Commentaires de requête pour les PDT sur cette page.
Commentaires de requête pour les tables PDT
Les administrateurs de bases de données peuvent facilement différencier les requêtes normales de celles qui génèrent des tables dérivées persistantes (PDT). Looker ajoute des commentaires à l'instruction CREATE TABLE ... AS SELECT ...
, qui incluent le modèle et la vue LookML de la PDT, ainsi qu'un identifiant unique (slug) pour l'instance Looker. Si la PDT est générée au nom d'un utilisateur en mode Développement, les commentaires indiquent l'ID de cet utilisateur. Les commentaires de génération d'une table PDT suivent le schéma ci-dessous :
-- Building `<view_name>` in dev mode for user `<user_id>` on instance `<instance_slug>`
CREATE TABLE `<table_name>` SELECT ...
-- finished `<view_name>` => `<table_name>`
Le commentaire de génération d'une PDT apparaît dans l'onglet SQL d'une exploration si Looker a dû générer une PDT pour la requête de l'exploration. Dans ce cas, le commentaire apparaît en haut de l'instruction SQL.
Enfin, le commentaire de génération des tables PDT apparaît dans le champ Message de l'onglet Infos du pop-up Détails de la requête pour chaque requête sur la page d'administration Requêtes.
Régénération d'une table PDT après échec
Lorsqu'une table dérivée persistante est défaillante, voici ce qui se passe lorsque cette table PDT est interrogée:
- Looker utilise les résultats dans le cache si la même requête a été exécutée précédemment. Pour comprendre comment cela fonctionne, consultez la page de documentation Mettre en cache les requêtes.
- Si les résultats ne sont pas dans le cache, Looker extrait les résultats de la PDT dans la base de données, si une version valide de la PDT existe.
- S'il n'existe aucune table PDT valide dans la base de données, Looker tente de régénérer la table PDT.
- Si la PDT ne peut pas être recréée, Looker renvoie une erreur pour une requête. Le régénérateur Looker tentera de régénérer la PDT la prochaine fois que la PDT sera interrogée ou la prochaine fois que la stratégie de persistance de la PDT déclenche une régénération.
Pour les PDT cascading, la même logique s'applique, sauf qu'avec les PDT en cascade:
- Un échec de création de table empêche la création de PDT tout le long de la chaîne de dépendance.
- Une PDT dépendante interroge essentiellement la PDT sur laquelle elle repose. Ainsi, la stratégie de persistance d'une table peut déclencher des régénérations des PDT en amont dans la chaîne.
Reprenons l'exemple précédent de tables en cascade, où TABLE_D
dépend de TABLE_C
, qui dépend de TABLE_B
, qui dépend de TABLE_A
:
Si TABLE_B
rencontre un échec, tout le comportement standard (hors cascade) s'applique à TABLE_B
: si TABLE_B
est interrogé, Looker essaie d'abord d'utiliser le cache pour renvoyer les résultats, puis essaie d'utiliser une version précédente de la table si possible, puis tente de recréer la table, puis renvoie une erreur si TABLE_B
ne peut pas la recompiler. Looker essaiera à nouveau de recréer TABLE_B
la prochaine fois que la table sera interrogée ou que la stratégie de persistance de la table déclenche une recompilation.
Il en va de même pour les dépendants de TABLE_B
. Ainsi, si TABLE_B
ne peut pas être compilé et qu'il existe une requête sur TABLE_C
:
- Looker tentera d'utiliser le cache pour la requête sur
TABLE_C
. - Si les résultats ne sont pas dans le cache, Looker tente d'extraire les résultats de
TABLE_C
dans la base de données. - S'il n'existe pas de version valide de
TABLE_C
, Looker tente de recompilerTABLE_C
, ce qui crée une requête surTABLE_B
. - Looker essaie ensuite de recompiler
TABLE_B
(ce qui échouera siTABLE_B
n'a pas été corrigé). - Si
TABLE_B
ne peut pas être recompilé,TABLE_C
ne peut pas être recompilé. Looker renvoie donc une erreur pour la requête surTABLE_C
. - Looker tentera ensuite de recréer
TABLE_C
selon sa stratégie de persistance habituelle ou la prochaine fois que la PDT sera interrogée (ce qui inclut la prochaine tentative de compilation deTABLE_D
, carTABLE_D
dépend deTABLE_C
).
Une fois le problème résolu avec TABLE_B
, TABLE_B
et chacune des tables dépendantes tenteront de se recompiler conformément à leurs stratégies de persistance ou la prochaine fois qu'elles seront interrogées (ce qui inclut la prochaine fois qu'une table PDT dépendante tentera de se recompiler). Ou, si une version de développement des tables PDT dans la cascade a été générée en mode Développement, les versions de développement peuvent être utilisées comme les nouvelles tables PDT de production. (Pour savoir comment procéder, consultez la section Tables persistantes en mode Développement de cette page.) Vous pouvez également utiliser une exploration pour exécuter une requête sur TABLE_D
, puis recréer manuellement les PDT pour la requête, ce qui forcera la recompilation de toutes les PDT qui montent dans la cascade de dépendances.
Améliorer les performances des PDT
Lorsque vous créez des tables dérivées persistantes (PDT), les performances peuvent poser problème. L'interrogation peut être lente, en particulier lorsque la table est très volumineuse, tout comme n'importe quelle table volumineuse de votre base de données.
Pour améliorer les performances, vous pouvez filtrer les données ou contrôler la façon dont les données de la table PDT sont triées et indexées.
Ajouter des filtres pour limiter l'ensemble de données
Avec les ensembles de données particulièrement volumineux, le fait d'avoir de nombreuses lignes ralentit les requêtes sur une table dérivée persistante (PDT). Si vous n'interrogez généralement que des données récentes, envisagez d'ajouter un filtre à la clause WHERE
de votre PDT qui limite la table à 90 jours de données ou moins. Ainsi, seules les données pertinentes seront ajoutées à la table chaque fois qu'elle est recréée, ce qui accélère l'exécution des requêtes. Ensuite, vous pouvez créer une PDT distincte plus grande pour l'analyse de l'historique, afin de permettre à la fois des requêtes rapides pour les données récentes et la possibilité d'interroger les anciennes données.
Utiliser indexes
ou sortkeys
et distribution
Lorsque vous créez une grande table dérivée persistante (PDT), l'indexation de la table (pour les dialectes tels que MySQL ou Postgres) ou l'ajout de clés de tri et de distribution (pour Redshift) peuvent aider à améliorer les performances.
Il est généralement préférable d'ajouter le paramètre indexes
dans les champs d'ID ou de date.
Pour Redshift, il est généralement préférable d'ajouter le paramètre sortkeys
dans les champs d'ID ou de date et le paramètre distribution
dans le champ utilisé pour la jointure.
Paramètres recommandés pour améliorer les performances
Les paramètres suivants contrôlent la manière dont les données de la table dérivée persistante (PDT) sont triées et indexées. Les paramètres suivants sont facultatifs, mais vivement recommandés:
- Pour Redshift et Aster, utilisez le paramètre
distribution
pour spécifier le nom de la colonne dont la valeur est utilisée pour répartir les données autour d'un cluster. Lorsque deux tables sont jointes par la colonne spécifiée dans le paramètredistribution
, la base de données peut trouver les données de jointure sur le même nœud, ce qui réduit les E/S entre les nœuds. - Pour Redshift, définissez le paramètre
distribution_style
surall
pour indiquer à la base de données de conserver une copie complète des données sur chaque nœud. Elle est souvent utilisée pour minimiser les E/S entre les nœuds lorsque des tables relativement petites sont jointes. Définissez cette valeur sureven
pour indiquer à la base de données de répartir uniformément les données dans le cluster sans utiliser de colonne de distribution. Cette valeur ne peut être spécifiée que sidistribution
n'est pas spécifié. - Pour Redshift, utilisez le paramètre
sortkeys
. Les valeurs spécifient les colonnes de la table PDT utilisées pour trier les données sur le disque afin de faciliter la recherche. Sur Redshift, vous pouvez utilisersortkeys
ouindexes
, mais pas les deux. - Dans la plupart des bases de données, utilisez le paramètre
indexes
. Les valeurs spécifient les colonnes de la table PDT qui sont indexées. (Sur Redshift, les index sont utilisés pour générer des clés de tri entrelacées.)