Tables dérivées dans Looker

Dans Looker, une table dérivée est une requête dont les résultats sont utilisés comme si la requête était une table réelle dans la base de données.

Par exemple, vous pourriez avoir une table de base de données appelée orders qui comporte 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. En utilisant soit une table dérivée native soit une table dérivée basée sur SQL , vous pouvez créer une nouvelle table de base de données nommée customer_order_summary qui inclut ces métriques.

Vous pouvez ensuite travailler avec 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 exemples d'utilisation populaires de tables dérivées, consultez Looker cookbooks : Getting the most out of derived tables in Looker.

Tables dérivées natives et tables dérivées SQL

Pour créer un tableau dérivé 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 définissez la table dérivée avec une requête basée sur LookML.
• Pour une table dérivée basée sur SQL, vous définissez la table dérivée avec une requête SQL.

Par exemple, les fichiers de vue suivants montrent comment vous pouvez utiliser LookML pour créer une vue à partir d'une table dérivée 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 vue orders existante, qui est définie dans un fichier séparé qui n'est pas affiché dans cet exemple. La requête explore_source dans la table dérivée native fait apparaître les champs customer_id, first_order et total_amount du fichier de vue orders.
• La table dérivée basée sur SQL définit la requête en utilisant SQL dans le paramètre sql. Dans cet exemple, la requête SQL est une requête directe sur la table orders 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 qui est basée sur la table orders, avec les colonnes customer_id, first_order, et total_amount.

Hormis le paramètre derived_table et 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 avec 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 utilisez le paramètre explore_source à l'intérieur du paramètre derived_table d'un paramètre view. 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. Voir 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éation de tables dérivées natives pour plus de détails 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. Voir le fichier de vue Tableau dérivée basé sur SQL dans l'exemple précédent.

Le plus souvent, vous définissez la requête SQL en utilisant le paramètre sql à l'intérieur du paramètre derived_table d'un paramètre view.

Un raccourci utile pour créer des requêtes SQL dans Looker consiste à utiliser SQL Runner pour créer la requête SQL et la transformer en une définition de table dérivée.

Certains cas particuliers ne permettront pas l'utilisation du paramètre sql. Dans de tels cas, Looker prend en charge les paramètres suivants pour définir une requête SQL pourtables dérivées persistantes (PDT) :

• create_process: Lorsque vous utilisez le paramètre sql pour un PDT, Looker enveloppe en arrière-plan l'instruction CREATE TABLE Data Definition Language (LDD) du dialecte autour de votre requête pour créer le PDT à partir de votre requête SQL. Certains dialectes ne prennent pas en charge une instruction SQL CREATE TABLE en une seule étape. Pour ces dialectes, vous ne pouvez pas créer un PDT avec le paramètre sql. Vous pouvez en revanche utiliser le paramètre create_process pour créer un PDT en plusieurs étapes. Consultez la page de documentation du paramètre create_process pour obtenir des informations et des exemples.
• sql_create: Si votre cas d'utilisation nécessite des commandes DDL personnalisées et que votre dialecte prend en charge LDD (par exemple, le BigQuery ML prédictif de Google), vous pouvez utiliser le paramètre sql_create pour créer un PDT au lieu d'utiliser le paramètre sql. Consultez la page de documentation sql_create pour obtenir des informations et des exemples.

Que vous utilisiez le paramètre sql, create_process ou sql_create, dans tous ces cas, vous définissez la table dérivée avec une requête SQL, donc ce sont toutes des tables dérivées basées sur SQL.

Lorsque vous définissez une table dérivée basée sur SQL, veillez à donner à chaque colonne un alias propre en utilisant AS. En effet, vous devrez faire référence aux noms de colonnes de votre ensemble de résultats dans vos dimensions, comme ${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

Outre la distinction entre les tables dérivées natives et les tables dérivées basées sur SQL, il existe également une distinction 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) — qui est é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 tableaux dérivés présentés précédemment sont des exemples de tableaux dérivés temporaires. Elles sont temporaires car il n'y a pas de stratégie de persistance 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 Explore impliquant une ou plusieurs tables dérivées, Looker construit une requête SQL en utilisant une combinaison spécifique au dialecte du SQL pour la ou les tables dérivées, ainsi que 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. Consultez la page de documentation Caching queries pour plus d'informations sur la mise en cache des requêtes dans Looker.

Sinon, si Looker ne peut pas utiliser les résultats mis en cache, Looker 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. De ce fait, vous devez vous assurer que vos tables dérivées temporaires sont performantes et ne surchargent pas votre base de données. Dans les cas où la requête prendra un certain temps à s'exécuter, un PDT est souvent une meilleure option.

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 quels dialectes prennent en charge les tables dérivées dans la dernière version de Looker :

Tables dérivées persistantes

Une table dérivée persistante (PDT) est une table dérivée qui est écrite dans un schéma temporaire sur votre base de données et régénérée 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.

Exigences 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 Dialogues de base de données pris en charge pour les PDT plus loin sur cette page pour obtenir les listes des dialectes qui prennent 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 le commutateur Activer les PDT activé. Ce paramètre Enable PDTs est généralement configuré lors de la configuration initiale de votre connexion Looker (voir la page de documentation Looker dialects pour obtenir des instructions concernant 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 PDT dans votre projet, votre dialecte de base de données doit également les prendre en charge.

Pour prendre en charge tout type de PDT (basé sur LookML ou sur SQL), le dialecte doit prendre en charge les écritures dans la base de données, entre autres exigences. Certaines configurations de bases de données en lecture seule ne permettent pas la persistance (le plus souvent les bases de données de réplicas à échange à chaud de Postgres). Dans ces cas, vous pouvez utiliser à la place des tables dérivées temporaires .

Le tableau suivant présente les dialectes qui prennent en charge la persistanceTables dérivées basées sur SQL dans la dernière version de Looker :

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 la liste des dialectes qui prennent en charge les tables dérivées persistantes natives (basées sur LookML) dans la dernière version de Looker :

Augmentation de la génération de tables PDT

UnPDT incrémentale est untable dérivée persistante que Looker construit en ajoutant de nouvelles données à la table au lieu de la reconstruire entièrement.

Si votre dialecte prend en charge les PDT incrémentiels, et que votre PDT utilise une stratégie de persistance basée sur un déclencheur (datagroup_trigger, sql_trigger_value ou interval_trigger), vous pouvez définir le PDT comme un PDT incrémentiel.

Consultez la page de documentation Incremental PDT pour plus d'informations.

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 quels dialectes prennent en charge les PDT incrémentiels dans la dernière version de Looker :

Création de PDT

Pour transformer une table dérivée en une table dérivée persistante (PDT), vous définissez une stratégie de persistance pour la table. Pour optimiser les performances, vous devriez également 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, pourdialectes qui prennent en charge les vues matérialisées, par votre base de données en utilisantvues 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 :
  • datagroup_trigger
  • sql_trigger_value
  • interval_trigger
  • persist_for
• Paramètres de persistance gérés par la base de données :
  • materialized_view: yes

Avec les stratégies de persistance basées sur des déclencheurs (datagroup_trigger, sql_trigger_value et interval_trigger), Looker conserve le PDT dans la base de données jusqu'à ce que le PDT soit déclenché pour une reconstruction. Une fois la PDT déclenchée, Looker la régénère pour remplacer la version précédente. Cela signifie qu'avec les PDT basés sur des déclencheurs, vos utilisateurs n'auront pas à attendre que le PDT soit construit pour obtenir des réponses aux requêtes Explore du PDT.

datagroup_trigger

Datagroups sont la méthode la plus flexible pour créer de la persistance. Si vous avez défini un datagroup avec sql_trigger ou interval_trigger, vous pouvez utiliser le paramètre datagroup_trigger pour lancer la reconstruction 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. Cela signifie que, dans la plupart des cas, vos utilisateurs n'auront pas à attendre la création du PDT. Si un utilisateur demande des données au PDT pendant sa construction et que les résultats de la requête ne sont pas dans le cache, Looker renverra les données du PDT existant jusqu'à ce que le nouveau PDT soit construit. Voir Cache des requêtes pour un aperçu des groupes de données.

Consultez la section sur Le régénérateur Looker pour plus d'informations sur la façon dont le régénérateur construit les PDT.

sql_trigger_value

Le paramètre sql_trigger_value déclenche la régénération d'une table dérivée persistante (PDT) basée sur une 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. Cela signifie que, dans la plupart des cas, vos utilisateurs n'auront pas à attendre la création du PDT. Si un utilisateur demande des données au PDT pendant sa construction et que les résultats de la requête ne sont pas dans le cache, Looker renverra les données du PDT existant jusqu'à ce que le nouveau PDT soit construit.

Consultez la section sur Le régénérateur Looker pour plus d'informations sur la façon dont le régénérateur construit les PDT.

interval_trigger

Le paramètre interval_trigger déclenche la régénération d'une table dérivée persistante (PDT) en fonction d'un intervalle de temps que vous fournissez, tel que "24 hours" ou "60 minutes". Semblable au paramètre sql_trigger, cela signifie que généralement le PDT sera préconstruit lorsque vos utilisateurs le consulteront. Si un utilisateur demande des données au PDT pendant sa construction et que les résultats de la requête ne sont pas dans le cache, Looker renverra les données du PDT existant jusqu'à ce que le nouveau PDT soit construit.

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 exécute pour la première fois une requête sur celle-ci. Looker conserve ensuite le PDT dans la base de données pendant la durée spécifiée dans le paramètre persist_for du PDT. Si un utilisateur interroge le PDT dans le délai de persist_for, Looker utilise les résultats mis en cache si possible ou exécute la requête sur le PDT.

Après le délai de persist_for, Looker efface le PDT de votre base de données, et le PDT sera reconstruit la prochaine fois qu'un utilisateur l'interrogera, ce qui signifie que la requête devra attendre la reconstruction.

Les PDT qui utilisent persist_for ne sont pas automatiquement reconstruits par le Looker regenerator, sauf dans le cas d'une dépendance cascade de PDT. Lorsqu'une table persist_for fait partie d'une cascade de dépendances avec des PDT basés 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 surveillera et reconstruira la table persist_for afin de reconstruire d'autres tables dans la cascade. Consultez la section Comment Looker construit des tables dérivées en cascade sur cette page.

materialized_view: yes

Les vues matérialisées vous permettent d'utiliser les fonctionnalités de votre base de données pour persister 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 prises en charge pour les deuxtables dérivées natives etTables dérivées basées sur SQL.

Similaire à untable 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 temporaire 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 obtenir des informations sur la prise en charge des dialectes, les exigences et les considérations importantes.

Stratégies d'optimisation

Étant donné que les tables dérivées persistantes (PDT) sont stockées dans votre base de données, vous devez optimiser vos PDT en utilisant les stratégies suivantes, prises en charge par votre dialecte :

• cluster_keys
• distribution
• distribution_style
• indexes
• partition_keys
• sortkeys

Par exemple, pour ajouter de la persistance à la table dérivée example, vous pouvez la configurer pour qu'elle se reconstruise lorsque le groupe de données orders_datagroup se déclenche, et ajouter des index 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 (ou un équivalent pour votre dialecte), Looker vous avertira que vous devriez 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.

De manière générale, il est recommandé aux développeurs de modéliser les données sans utiliser de PDT, sauf en cas d'absolue nécessité.

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 un PDT. Assurez-vous d'analyser les plans d'exécution des requêtes lentes à l'aide de l'outil Explain from SQL Runner.

Outre la réduction du temps d'exécution des requêtes et de la charge de la base de données sur les requêtes fréquemment exécutées, les PDT présentent plusieurs autres cas d'utilisation, notamment :

• Optimisations de test
• Données pré-jointes ou agrégées

Vous pouvez également utiliser un PDT pour définir une clé primaire dans les cas où il n'existe aucun moyen raisonnable d'identifier une ligne unique dans une table comme clé primaire.

Utilisation des PDT pour tester les optimisations

Vous pouvez utiliser les PDT pour tester différentes options d'indexation, de distribution et d'optimisation sans avoir besoin d'un soutien important de la part de votre administrateur de base de données ou de vos développeurs ETL.

Prenons le cas où vous avez une table mais que vous souhaitez tester différents index. Votre LookML initial pour la vue peut ressembler à ceci :

view: customer {
  sql_table_name: warehouse.customer ;;
}

Pour tester les stratégies d'optimisation, vous pouvez utiliser le paramètre indexes pour ajouter des index au LookML comme ceci :

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 seule fois pour générer le PDT. Exécutez ensuite vos requêtes de test et comparez vos résultats. Si vos résultats sont favorables, vous pouvez demander à votre administrateur de base de données ou à votre équipe ETL d'ajouter les index à la table d'origine.

N'oubliez pas de rétablir votre code d'affichage initial pour supprimer le PDT.

Utilisation des PDT pour pré-regrouper ou agréger des données

Il peut être utile de pré-joindre ou de pré-agréger les données afin d'optimiser les requêtes pour des volumes importants ou plusieurs types de données.

Par exemple, supposons que vous souhaitiez créer une requête pour les clients par cohorte en fonction de la date de leur première commande. Cette requête peut s'avérer coûteuse à exécuter plusieurs fois lorsque les données sont nécessaires en temps réel ; toutefois, vous pouvez la calculer une seule fois et réutiliser ensuite les résultats avec un 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 faire référence à une table dérivée dans la définition d'une autre, créant ainsi une chaîne de tables dérivées en cascade, ou de tables dérivées persistantes en cascade (PDT), selon le cas. Un exemple de tables dérivées en cascade serait une table, TABLE_D, qui dépend d'une autre table, TABLE_C, tandis que TABLE_C dépend de TABLE_B et TABLE_B dépend 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 faire référence à la table dérivée clean_events avec cette syntaxe :

`${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, le PDT clean_events est créé à partir de la table events dans la base de données. Le PDT clean_events exclut les lignes indésirables de la table de base de données events. Ensuite, un deuxième PDT est affiché ; le PDT event_summary est un résumé du PDT clean_events. La table event_summary se régénère chaque fois que de nouvelles lignes sont ajoutées à clean_events.

Le PDT event_summary et le PDT clean_events sont des PDT en cascade, où event_summary dépend de clean_events (puisque event_summary est défini à l'aide du PDT clean_events). Cet exemple précis pourrait être réalisé plus efficacement dans un seul PDT, mais il est utile pour illustrer les références de 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 nécessaire, 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 ce format :

${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 (surtout avec les clauses ON), il est possible d'oublier qu'il faut utiliser la syntaxe ${derived_table_or_view_name.SQL_TABLE_NAME} pour récupérer ce nom long. 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 en cascade temporary, si les résultats de la requête d'un utilisateur ne sont pas dans le cache, Looker construira 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, alors TABLE_D est dépendant de TABLE_C. Cela signifie que si vous interrogez TABLE_D et que la requête n'est pas dans le cache de Looker, Looker reconstruira TABLE_D. Mais d'abord, il faut reconstruire TABLE_C.

Considérons un scénario avec des tables dérivées temporaires en cascade, où 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 pour une requête sur TABLE_C dans le cache, Looker construira toutes les tables dont il a besoin pour la requête. Looker va donc construire TABLE_A, puis TABLE_B, puis TABLE_C :

Dans ce scénario, TABLE_A doit terminer sa génération avant que Looker puisse commencer à générer TABLE_B, et TABLE_B doit terminer sa génération avant que Looker puisse commencer à générer TABLE_C. Lorsque TABLE_C aura terminé, Looker fournira les résultats de la requête. (Comme TABLE_D n'est pas nécessaire pour répondre à cette requête, Looker ne reconstruira pas TABLE_D pour le moment.)

Consultez la page de documentation du paramètre datagroup pour un exemple de scénario de PDT en cascade qui utilisent le même groupe de données.

La même logique de base s'applique aux PDT : Looker construira toute table nécessaire pour répondre à une requête, et ce, jusqu'au sommet de la chaîne de dépendances. Mais avec les PDT, il arrive souvent que les tables existent déjà et n'aient pas besoin d'être reconstruites. Lors de requêtes utilisateur standard sur des PDT en cascade, Looker reconstruit les PDT en cascade uniquement s'il n'existe aucune version valide des PDT dans la base de données. Si vous souhaitez forcer une reconstruction pour tous les PDT en cascade, vous pouvez reconstruire manuellement les tables pour une requête via une exploration.

Un point logique important à comprendre est que, dans le cas d'une cascade PDT, une PDT dépendante est essentiellementinterrogation Cela dépend du PDT. Ceci est particulièrement important pour les PDT qui utilisent la stratégie persist_for. En règle générale, les PDT persist_for sont construits lorsqu'un utilisateur les interroge, restent dans la base de données jusqu'à ce que leur intervalle persist_for soit écoulé, puis ne sont pas reconstruits jusqu'à ce qu'ils soient interrogés à nouveau par un utilisateur. Toutefois, si un PDT persist_for fait partie d'une cascade avec des PDT basés sur des déclencheurs (PDT qui utilisent la stratégie de persistance datagroup_trigger, interval_trigger ou sql_trigger_value), le PDT persist_for est essentiellement interrogé chaque fois que ses PDT dépendants sont reconstruits. Dans ce cas, le PDT persist_for sera reconstruit selon le calendrier de ses PDT dépendants. Cela signifie que les PDT persist_for peuvent être affectés par la stratégie de persistance de leurs dépendants.

Lorsque vous configurez la persistance pour des structures PDT profondément imbriquées (chaînes de PDT en cascade avec plusieurs niveaux de dépendances), assurez-vous que vos périodes de rétention du cache et vos intervalles de groupe de données laissent suffisamment de temps pour que l'ensemble de la cascade puisse se construire. Des périodes de rétention du cache trop courtes peuvent provoquer une condition de concurrence qui entraîne une erreur 409 Conflict lors de l'actualisation. Pour plus d'informations et de bonnes pratiques recommandées, consultez la section Dépannage des erreurs de conflit 409 dans les PDT profondément imbriqués sur cette page.

Régénération manuelle de tables persistantes pour une requête

Les utilisateurs peuvent sélectionner l'option Reconstruire les tables dérivées et exécuter dans le menu d'Explore pour remplacer les paramètres de persistance et reconstruire toutes les tables dérivées persistantes (PDT) et tables d'agrégation nécessaires à la requête actuelle dans Explore :

Cette option n'est visible que pour les utilisateurs disposant de l'autorisation develop, et seulement après le chargement de la requête Explore.

L'option Reconstruire les tables dérivées et exécuter reconstruit toutes les tables persistantes (toutes les 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 de la requête actuelle, ainsi que toutes les tables agrégées et les PDT référencés par les tables agrégées et les PDT de la requête actuelle.

Dans le cas des PDT incrémentiels , l'option Reconstruire les tables dérivées et exécuter déclenche la construction d'un nouvel incrément. Avec les PDT incrémentaux, un incrément comprend la période de temps spécifiée dans le paramètre increment_key, ainsi que le nombre de périodes de temps précédentes spécifiées dans le paramètre increment_offset, le cas échéant. Consultez la page de documentation Incremental PDT pour quelques exemples de scénarios qui montrent comment les PDT incrémentaux sont construits, en fonction de leur configuration.

Dans le cas de PDT en cascade, cela signifie reconstruire toutes les tables dérivées dans la cascade, en commençant par le haut. Ce comportement est identique à celui observé lors de l'interrogation d'une table dans une cascade de tables dérivées temporaires :

Notez ce qui suit concernant la reconstruction manuelle des tables dérivées :

• Pour l'utilisateur qui lance l'opération Reconstruire les tables dérivées et exécuter, la requête attendra que les tables soient reconstruites 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 reconstruction des tables, ces utilisateurs pourraient tout de même être affectés par la charge supplémentaire 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 de développement et que l'Exploration est basée sur une table de développement, l'opération Reconstruire les tables dérivées et exécuter reconstruira la table de développement, et non la table de production, pour l'Exploration. Mais si l'explorateur en mode développement utilise la version de production d'une table dérivée, la table de production sera reconstruite. Voir Tables persistantes en mode de développement pour plus d'informations sur les tables de développement et les tables de production.

• Pour les instances hébergées par Looker, si la reconstruction de la table dérivée prend plus d'une heure, la reconstruction échouera et la session du navigateur expirera. Consultez la section Délais d'attente et mise en file d'attente des requêtes sur la page de documentation Paramètres d'administration - Requêtes pour plus d'informations sur les délais d'attente qui peuvent affecter les processus Looker.

Tables persistantes en mode Développement

Looker possède des comportements spéciaux pour la gestion des tables persistantes en mode de développement.

Si vous interrogez une table persistante en mode de développement sans apporter de modifications à sa définition, Looker interrogera la version de production de cette table. Si vous do apportez une modification à la définition de la table qui affecte les données de la table ou la façon dont la table 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. Disposer d'un tel tableau de développement permet de tester les modifications sans perturber les utilisateurs.

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 possède un paramètre qui restreint son ensemble de données à , travaillez 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 construira 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 conditionnelle WHERE 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, 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 repose la table persistante, par exemple en modifiant le paramètre explore_source, sql, query, sql_create ou create_process dans la table persistante elle-même ou dans 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 les paramètres datagroup_trigger, sql_trigger_value, interval_trigger ou persist_for de la table
• Modifier le nom de la view d'une table dérivée
• Modifier le increment_key ou le increment_offset d'une PDT incrémentale
• Modifier le connection utilisé par le modèle associé

Looker ne créera pas de table de développement en réponse aux modifications qui ne changent pas les données de la table et n'affectent pas la façon dont Looker interroge la table. Le paramètre publish_as_db_view est un bon exemple : en mode Développement, si vous modifiez uniquement le paramètre publish_as_db_view d'une table dérivée, Looker n'a pas besoin de régénérer 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 elles avaient une stratégie de persistance de persist_for: "24 hours". Looker le fait pour s'assurer que les tables de développement ne sont pas persistantes plus d'une journée, car un développeur Looker peut interroger plusieurs itérations d'une table lors du développement, ce qui entraîne à chaque fois la génération d'une table de développement. Pour empêcher les tables de développement d'encombrer la base de données, Looker applique la stratégie persist_for: "24 hours" pour s'assurer que les tables sont fréquemment nettoyées par rapport à 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 façon qu'il génère des tables persistantes en mode Production.

Si une table de développement est persistante sur votre base de données lorsque vous déployez des modifications sur une table PDT ou un tableau cumulé, Looker peut souvent utiliser la table de développement comme table de production afin que les utilisateurs n'aient 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 votre interrogation de la table en mode Développement remonte à plus de 24 heures, la version du développement de la table sera marquée comme étant expirée et ne sera pas utilisée pour les requêtes. Vous pouvez rechercher les tables PDT non créées à l'aide de l'IDE Looker ou de 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 possède le paramètre dev_filters (pour les tables dérivées natives) ou la clause conditionnelle WHERE ​​qui utilise les instructions if prod et if dev (pour les tables dérivées basées sur SQL), la table de développement ne peut pas être utilisée comme version de production, car la version de développement possède un jeu de données abrégé. Si tel est le cas, une fois que vous avez terminé le développement de la table et avant de déployer vos modifications, vous pouvez commenter le paramètre dev_filters ou la clause conditionnelle WHERE 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'il n'existe aucune table de développement valide pouvant être utilisée comme table de production, Looker reconstruira la table lors de la prochaine requête de la table en mode Production (pour les tables persistantes utilisant la stratégie persist_for), ou lors de la prochaine exécution du regenerator (pour les tables persistantes utilisant 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 afin que vos utilisateurs n'aient pas à attendre que la table soit construite lorsqu'ils interrogent la table. Consultez les sections Combien de temps Looker conserve-t-il les tables de développement et Qu'est-ce qui incite Looker à créer une table de développement sur cette page pour plus de détails.

Il est donc optimal que tous vos PDT soient construits lors du déploiement en production afin que les tables puissent être utilisées immédiatement comme versions de production.

Vous pouvez vérifier si votre projet contient des PDT non compilés dans le panneau Santé du projet. Cliquez sur l'icône Santé du projet dans l'IDE Looker pour ouvrir le panneau Santé du projet. Cliquez ensuite sur le bouton Valider le statut PDT.

S'il existe des PDT non compilés, le panneau Santé du projet les listera :

Si vous disposez de l'autorisation see_pdts, vous pouvez cliquer sur le bouton Accéder à la gestion PDT. Looker ouvrira l'onglet Développement de la page Tables dérivées persistantes et filtrera les résultats selon votre projet LookML spécifique. À partir de là, vous pouvez voir quels PDT de développement sont compilés et non compilés, ainsi qu'accéder à d'autres informations de dépannage. Consultez la page de documentation Paramètres d'administration - Tables dérivées persistantes pour plus d'informations.

Une fois que vous avez identifié un PDT non construit dans votre projet, vous pouvez en construire une version de développement en ouvrant une Explore qui interroge la table, puis en utilisant l'option Reconstruire les tables dérivées et exécuter du menu Explore. Consultez la section Reconstruction manuelle des tables persistantes pour une requête sur 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. De plus, si la définition d'une table cesse d'exister, Looker marque la table comme expirée.

Cela présente 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 qui est définie à l’aide d’une clause conditionnelle WHERE avec des instructions if prod et if dev. Si la table est définie avec une clause conditionnelle WHERE, Looker construira une table de développement si vous interrogez la table en mode développement. (Pourtables dérivées natives avec ledev_filters En mode développement, Looker utilise la table de production pour répondre aux requêtes, sauf si vous modifiez la définition de la table avant de l'interroger.
• 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

Il existe des situations où la génération de la table dérivée persistante (PDT) que vous créez peut prendre beaucoup de temps, ce qui peut s'avérer fastidieux si vous testez de nombreuses modifications en mode développement. Dans ces cas-là, vous pouvez demander à Looker de créer des versions plus petites d'une table dérivée lorsque vous êtes 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 s'appliquent 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 comprend un paramètre dev_filters qui filtre les données aux 90 derniers jours et un paramètre filters qui filtre les données aux 2 dernières années et à l'aéroport de Yucca Valley.

Le paramètre dev_filters agit conjointement avec le paramètre filters afin que tous les filtres soient appliqués à la version de développement du tableau. Si dev_filters et filters spécifient tous deux des filtres pour la même colonne, dev_filters a la priorité pour la version de développement du tableau. 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 prend en charge une clause conditionnelle WHERE 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 construit les PDT

Une fois qu'une table dérivée persistante (PDT) a été définie et qu'elle est soit exécutée pour la première fois, soit déclenchée par le regenerator pour être reconstruite selon sa stratégie de persistance, Looker suivra les étapes suivantes :

1. Utilisez la requête SQL de la table dérivée pour créer une instruction CREATE TABLE AS SELECT (ou CTAS) et exécutez-la. Par exemple, pour reconstruire un PDT appelé customer_orders_facts: CREATE TABLE tmp.customer_orders_facts AS SELECT ... FROM ... WHERE ...
2. Exécutez les instructions pour créer les index lors de la création de la table.
3. Renommez la table de LC$.. (« Looker Create ») à LR$.. (« Looker Read ») pour indiquer qu'elle est prête à être utilisée.
4. Supprimez toute ancienne version du tableau qui ne devrait plus être utilisée.

Il y a quelques implications importantes :

• Le code SQL qui constitue la table dérivée doit être valide à l'intérieur d'une instruction CTAS.
• Les alias de colonnes dans le jeu de résultats de l'instruction SELECT doivent être des noms de colonnes valides.
• Les noms utilisés pour spécifier la distribution, les clés de tri et les index doivent être les noms de colonnes qui figurent dans la définition SQL de la table dérivée, et non les noms de champs définis dans le 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 par déclencheur 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ètre sql_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 conservée avec la requête SQL SELECT CURDATE(), le régénérateur Looker reconstruira la table la prochaine fois qu'il vérifiera le déclencheur après les changements de date.
• Pour les tables qui utilisent interval_trigger, le déclencheur est une durée de temps spécifiée dans le paramètre interval_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ètre sql_trigger du groupe de données associé, ou le déclencheur peut être une durée de temps spécifiée dans le paramètre interval_trigger du groupe de données.

Le régénérateur Looker lance également des reconstructions pour les tables persistantes qui utilisent le paramètre persist_for, mais seulement lorsque la table persist_for est une dépendance cascade d'une table persistante déclenchée. Dans ce cas, le régénérateur Looker lancera des reconstructions pour une table persist_for, car la table est nécessaire pour reconstruire 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.

De plus, le régénérateur Looker crée modèles analytiques dans votre base de données, si vous avez défini le modèle analytique en utilisant le paramètre derived_analytic_model. Le régénérateur Looker traite des modèles analytiques dérivés de manière similaire aux PDT qui sontvues matérialisées. Les vues matérialisées et les modèles analytiques dérivés ne sont créés qu'une seule fois et ne prennent pas en charge les déclencheurs, tels que déclencheurs de groupe de données, déclencheurs SQL ou déclencheurs d'intervalle. Le régénérateur Looker recrée les modèles analytiques dans votre base de données uniquement si leur définition LookML change ou si l'une des vues LookML dont ils dépendent change.

Le cycle de régénération Looker commence à un intervalle régulier configuré par votre administrateur Looker dans le paramètre Planification de maintenance de votre connexion à la base de données (l'intervalle par défaut est de cinq minutes). Cependant, le régénérateur Looker ne démarre pas un nouveau cycle tant qu'il n'a pas terminé toutes les vérifications et les reconstructions PDT du cycle précédent. Cela signifie que si vous avez des builds PDT de longue durée, le cycle de régénération Looker peut ne pas s'exécuter aussi souvent que défini dans le paramètre Planification de maintenance. D'autres facteurs peuvent affecter le temps nécessaire à la reconstruction de vos tables, comme décrit dans la section Considérations importantes pour la mise en œuvre de tables persistantes de cette page.

Dans les cas où la création d'une table de données persistantes (PDT) échoue, le régénérateur peut tenter de la reconstruire lors du cycle de régénération suivant :

• Si le paramètre Retry Failed PDT Builds est activé sur votre connexion de base de données, le régénérateur Looker tentera de reconstruire la table lors du prochain cycle de régénération, même si la condition de déclenchement de la table n'est pas remplie.
• Si le paramètre Retry Failed PDT Builds est désactivé, le régénérateur Looker ne tentera pas de reconstruire la table tant que la condition de déclenchement du PDT n'est pas remplie.

Si un utilisateur demande des données à la table persistante pendant sa construction et que les résultats de la requête ne sont pas dans le 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 possible 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 construire plusieurs tables en même temps. Surtout avectables en cascade, ou des tables à exécution longue, vous pouvez créer un scénario où les tables subissent un long délai avant d'être reconstruites, ou où les utilisateurs subissent un délai dans l'obtention des résultats de requête d'une table pendant que la base de données travaille dur pour générer la table.

Le Looker regenerator vérifie les déclencheurs PDT pour voir s'il doit reconstruire les tables persistantes des déclencheurs. Le cycle de régénération est défini à un intervalle régulier configuré par votre administrateur Looker dans le paramètre Planification de maintenance de votre connexion à la base de données (l'intervalle par défaut est de cinq minutes).

Plusieurs facteurs peuvent influencer le temps nécessaire à la reconstruction de vos tables :

• Votre administrateur Looker a peut-être modifié l'intervalle des vérifications du déclencheur de régénération en utilisant le paramètre Planification de maintenance sur votre connexion à la base de données.
• Le régénérateur Looker ne démarre pas un nouveau cycle tant qu'il n'a pas terminé tous les contrôles et les reconstructions PDT du cycle précédent. Ainsi, si vous avez des builds PDT de longue durée, le cycle de régénération Looker peut ne pas être aussi fréquent que le paramètre Planification de maintenance.
• 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 de reconstructions simultanées autorisées du régénérateur en utilisant le champ Nombre maximal de connexions du générateur PDT dans les paramètres d'une connexion.
• Tous les PDT et les tables agrégées déclenchés par le même datagroup seront reconstruits au cours du même processus de régénération. Cela peut représenter une charge importante si vous avez de nombreuses tables utilisant le groupe de données, soit directement, soit à la suite de dépendances en cascade.

Outre les considérations précédentes, il existe également des situations dans lesquelles il est préférable d'éviter d'ajouter de la persistance à une table dérivée :

• Lorsque les tables dérivées seront étendues — Chaque extension d'une PDT créera une nouvelle copie de la table dans votre base de données.
• Lorsque les tables dérivées utilisentfiltres à modèles ou paramètres Liquid — La persistance n'est pas prise en charge pour les tables dérivées qui utilisent des filtres à modèles ou des paramètres Liquid.
• Quandtables dérivées natives sont construits à partir d'Explores qui utilisentattributs de l'utilisateur avecaccess_filters, ou avecsql_always_where — Des copies du tableau seront créées dans votre base de données pour chaque valeur possible d'attribut utilisateur spécifiée.
• Lorsque les données sous-jacentes changent fréquemment et que votre dialecte de base de données ne le prend pas en chargePDT incrémentaux.
• Lorsque le coût et le temps nécessaires à la création de PDT sont trop élevés.

En fonction du nombre et de la complexité des tables persistantes sur votre connexion Looker, la file d'attente peut contenir de nombreuses tables persistantes qui doivent être vérifiées et reconstruites à 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 une API

La surveillance et la gestion des tables dérivées persistantes (PDT) qui s'actualisent selon des calendriers variables deviennent de plus en plus complexes à mesure que vous créez davantage de PDT sur votre instance. Envisagez d'utiliser l'intégration Looker Apache Airflow pour gérer vos calendriers PDT parallèlement à vos autres processus ETL et ELT.

Surveillance et correction de tables PDT

Si vous utilisez des tables dérivées persistantes (PDT), et en particulieren cascade Pour les PDT, il est utile de consulter leur statut. Vous pouvez utiliser la page d'administration Looker Persistent Derived Tables pour voir l'état de vos PDT. Vous pouvez également consulter l'arbre de dépannage PDT pour un débogage étape par étape.

Lors du dépannage des PDT :

• Portez une attention particulière à la distinction entre les tables de développement et les tables de production lors de l'examen du journal des événements PDT.
• Vérifiez que le paramètre Base de données temporaire de votre connexion Looker correspond à votre base de données ou schéma temporaire. Si le paramètre Base de données temporaire de la connexion ne correspond pas au schéma temporaire de votre base de données, mettez à jour le paramètre Base de données temporaire afin que Looker puisse stocker les tables dérivées persistantes dans votre base de données.
• 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 avec succès 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'un LIMIT protège contre les requêtes excessives.) Pour en savoir plus sur l'utilisation de l'Exécuteur SQL pour déboguer les tables dérivées, consultez l'article 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. (Veillez à appliquer un LIMIT dans l'exécuteur SQL pour que les temps de requête restent raisonnables.)
• Pour les tables dérivées basées sur SQL, évitez d'utiliser des expressions de table courantes (CTE). La combinaison de CTE et de tables dérivées crée des instructions WITH imbriquées pouvant provoquer des erreurs inattendues au niveau des PDT. Utilisez plutôt le code SQL de votre CTE pour créer une DT secondaire et référencez cette DT à partir de votre première DT à l'aide de la syntaxe ${derived_table_or_view_name.SQL_TABLE_NAME}.
• Assurez-vous que la table PDT posant problème n'est liée à aucune table existante (qu'il s'agisse d'une table normale ou d'une autre table PDT) susceptible d'être interrogée.
• 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 les verrous exclusifs associés à la table à partir de laquelle Looker crée une table PDT. Si cette table comporte un verrou exclusif, Looker ne peut pas acquérir de verrou partagé pour exécuter les requêtes tant que tous les verrous exclusifs ne sont pas supprimés.
• Utilisez le bouton 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.

• Lorsque des fonctions de date spécifiques à la base de données (telles que current_date()) sont utilisées dans la requête SQL d'une table dérivée, il existe un risque d'incompatibilité de fuseau horaire entre la session Looker de l'utilisateur et la base de données sous-jacente. Étant donné que les fonctions de base de données sont exécutées directement dans la base de données et ne subissent pas la conversion de fuseau horaire des requêtes Looker, cette différence peut entraîner des résultats inattendus dans les filtres de date (par exemple, un filtre de date pour « Hier » pourrait correspondre à il y a deux jours, aux alentours de minuit).

  Pour résoudre ce problème, assurez-vous de la bonne synchronisation des fuseaux horaires entre votre base de données et l'instance Looker, ce qui peut nécessiter une coordination avec votre équipe d'ingénierie des données.

• Si vous rencontrez une erreur 409 Conflict lors d'une actualisation PDT dans des structures PDT profondément imbriquées (chaînes de PDT en cascade avec plusieurs niveaux de dépendances), consultez la section Dépannage des erreurs de conflit 409 dans les PDT profondément imbriqués sur cette page.

Commentaires de requête pour les tables PDT

Les administrateurs de bases de données peuvent 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 inclut le modèle et la vue LookML du PDT, ainsi qu'un identifiant unique (slug) pour l'instance Looker. Si le PDT est généré pour le compte d'un utilisateur en mode développement, les commentaires indiqueront l'identifiant de l'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 PDT apparaîtra dans l'onglet SQL d'une exploration si Looker a dû générer un 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 d'une table 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 (PDT) rencontre une défaillance, voici ce qui se passe lorsque cette 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. (Consultez la page de documentation Caching queries pour une explication de son fonctionnement.)
• Si les résultats ne sont pas dans le cache, Looker les extraira du PDT dans la base de données, si une version valide du 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 le PDT ne peut pas être reconstruit, Looker renverra une erreur pour la requête. Le régénérateur Looker tentera de reconstruire le PDT la prochaine fois que le PDT sera interrogé ou la prochaine fois que la stratégie de persistance du PDT déclenchera une reconstruction.

Avec les PDT en cascade , 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 envoie essentiellement une requête à la PDT sur laquelle elle repose. La stratégie de persistance d'une table peut donc déclencher des régénérations des PDT tout au long de 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 échoue, tout le comportement standard (non en cascade) s'applique à TABLE_B :

1. Si TABLE_B est interrogé, Looker essaie d'abord d'utiliser le cache pour renvoyer les résultats.
2. Si cette tentative échoue, Looker essaie ensuite d'utiliser une version précédente de la table, si possible.
3. Si cette tentative échoue également, Looker essaie ensuite de régénérer la table.
4. Enfin, si TABLE_B ne peut pas être régénérée, Looker renvoie une erreur.

Looker essaie à nouveau de régénérer TABLE_B lorsque la table est interrogée à nouveau ou lorsque la stratégie de persistance de la table déclenche à nouveau une régénération.

Il en va de même pour les dépendances de TABLE_B. Par conséquent, si TABLE_B ne peut pas être créé et qu'il existe une requête sur TABLE_C, la séquence suivante se produit :

1. Looker essaiera d'utiliser le cache pour la requête sur TABLE_C.
2. Si les résultats ne sont pas dans le cache, Looker essaie d'extraire les résultats de TABLE_C dans la base de données.
3. S'il n'existe aucune version valide de TABLE_C, Looker tente de régénérer TABLE_C, ce qui crée une requête sur TABLE_B.
4. Looker essaie ensuite de régénérer TABLE_B (qui échoue si TABLE_B n'a pas été corrigé).
5. Si TABLE_B ne peut pas être régénérée, TABLE_C ne peut pas l'être non plus. Looker renvoie donc une erreur pour la requête sur TABLE_C.
6. Looker tentera ensuite de régénérer TABLE_C selon sa stratégie de persistance habituelle ou lors de la prochaine interrogation de la table PDT (y compris la prochaine tentative de compilation de TABLE_D, car TABLE_D dépend de TABLE_C).

Une fois le problème résolu avec TABLE_B, TABLE_B et chacune des tables dépendantes tenteront de se régénérer en fonction de leurs stratégies de persistance ou lors de leur prochaine interrogation (y compris la prochaine fois qu'une PDT dépendante tentera de se régénérer). 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 en savoir plus, consultez la section Tables persistantes en mode Développement de cette page.) Vous pouvez également utiliser un Explore pour exécuter une requête sur TABLE_D, puis régénérer manuellement les PDT pour la requête. Cela forcera la régénération de toutes les PDT dans la cascade de dépendances.

Résoudre les erreurs de conflit 409 dans les PDT profondément imbriquées

Lorsque vous travaillez avec des structures PDT profondément imbriquées (chaînes de PDT en cascade avec plusieurs niveaux de dépendances), la configuration de périodes de conservation du cache courtes (par exemple, 15 minutes) peut entraîner une condition de concurrence qui génère une erreur 409 Conflict lors de l'actualisation.

Cette condition de concurrence se produit, car le cache des tables PDT imbriquées de niveau inférieur peut expirer alors que les tables PDT de niveau supérieur sont toujours en cours de création. Dans ce cas, Looker déclenche une nouvelle demande de génération en double pour les PDT de niveau inférieur, tandis que le job initial est toujours en cours de traitement dans l'entrepôt de données, ce qui entraîne le conflit.

Pour résoudre ou éviter cette erreur, suivez les bonnes pratiques suivantes :

• Augmentez la durée de conservation du cache : définissez la durée de conservation du cache (max_cache_age ou persist_for) pour les PDT sur au moins deux à trois fois la durée maximale nécessaire pour générer complètement toutes les PDT imbriquées.
• Augmentez l'intervalle d'actualisation du groupe de données : accordez suffisamment de temps pour que les PDT profondément imbriquées soient créées, ce qui réduit le risque de chevauchement des processus de création.

Amélioration des performances PDT

Lorsque vous créez des tables dérivées persistantes (PDT), les performances peuvent être un problème. Surtout lorsque la table est très volumineuse, l'interrogation de cette table peut être lente, comme cela peut être le cas pour n'importe quelle table volumineuse de votre base de données.

Vous pouvez améliorer les performances en filtrant les données ou en contrôlant la façon dont les données dans le PDT sont triées et indexées.

Ajout de filtres pour limiter l'ensemble de données

Avec des ensembles de données particulièrement volumineux, le fait d'avoir de nombreuses lignes ralentira les requêtes sur une table dérivée persistante (PDT). Si vous n'interrogez généralement que les données récentes, envisagez d'ajouter un filtre à la clause WHERE de votre PDT qui limite la table aux données des 90 derniers jours ou moins. De cette façon, seules les données pertinentes seront ajoutées à la table à chaque reconstruction, ce qui permettra d'exécuter les requêtes beaucoup plus rapidement. Ensuite, vous pouvez créer un PDT distinct et plus volumineux pour l'analyse historique, permettant à la fois des requêtes rapides sur les données récentes et la possibilité d'interroger des données anciennes.

En utilisantindexes ousortkeys etdistribution

Lorsque vous créez une grande table dérivée persistante (PDT), l'indexation de la table (pour des dialectes tels que MySQL ou Postgres) ou l'ajout de clés de tri et de distribution (pour Redshift) peuvent contribuer à améliorer les performances.

Il est généralement préférable d'ajouter le paramètre indexes aux champs ID ou date.

Pour Redshift, il est généralement préférable d'ajouter le paramètre sortkeys sur les champs ID ou date et le paramètre distribution sur 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. Ces paramètres sont facultatifs, mais fortement 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 sur un cluster. Lorsque deux tables sont jointes par la colonne spécifiée dans le paramètre distribution, la base de données peut trouver les données de jointure sur le même nœud, de sorte que les E/S inter-nœuds sont minimisées.
• Pour Redshift, définissez le paramètre distribution_style sur all pour indiquer à la base de données de conserver une copie complète des données sur chaque nœud. Cette technique est souvent utilisée pour minimiser les E/S entre les nœuds lors de la jointure de tables relativement petites. Définissez cette valeur sur even pour indiquer à la base de données de répartir les données uniformément dans le cluster sans utiliser de colonne de distribution. Cette valeur ne peut être spécifiée que lorsque distribution n'est pas spécifié.
• Pour Redshift, utilisez le paramètre sortkeys. Ces valeurs spécifient les colonnes du PDT utilisées pour trier les données sur le disque afin de faciliter la recherche. Sur Redshift, vous pouvez utiliser soit sortkeys soit indexes, mais pas les deux.
• Sur la plupart des bases de données, utilisez le paramètre indexes. Les valeurs spécifient quelles colonnes du PDT sont indexées. (Sur Redshift, les index sont utilisés pour générer des clés de tri entrelacées.)