Concepts SQL pour les tables dérivées

Bien que les vues LookML soient généralement basées sur des tables existantes de votre base de données, vous pouvez également créer des vues basées sur des instructions SELECT SQL. Dans Looker, ce type de vue est appelé table dérivée. Les tables dérivées sont des requêtes dont les résultats sont utilisés comme s'il s'agissait de tables réelles de la base de données.

Ce guide aborde les sujets suivants:

Comment Looker génère le code SQL pour les tables dérivées

Lorsque vous interrogez des tables dérivées dans LookML, Looker traduit vos requêtes en instructions SQL sous forme d'expressions de table communes (CTE, Common Table Expressions) ou de vues intégrées, selon le dialecte. En d'autres termes, les requêtes SQL générées peuvent ressembler à l'un des exemples suivants.

L'exemple suivant montre comment Looker peut générer une requête SQL utilisant un CTE pour interroger une table dérivée:

WITH (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users
SELECT ...
FROM users AS u
INNER JOIN top_100_users ON u.id == top_100_users.id
WHERE ...

L'exemple suivant montre comment Looker peut générer une requête SQL qui utilise une vue intégrée pour interroger une table dérivée :

SELECT ...
FROM users AS u
INNER JOIN (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users ON u.id == top_100_users.id
WHERE ...

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 directement dans le langage LookML à l'aide du paramètre sql dans le paramètre derived_table d'une vue. Vous pouvez ainsi définir les colonnes de la table dérivée à l'aide de SQL.

Par exemple, l'exemple LookML suivant définit une table dérivée appelée top_100_users qui identifie les 100 premiers utilisateurs ayant dépensé le plus sur l'ensemble de leurs commandes :

view: top_100_users {
  derived_table: {
    sql: SELECT o.user_id as id
         FROM orders AS o
         INNER JOIN order_items AS oi ON o.id == oi.order_id
         ORDER BY SUM(oi.total_sale_price) DESC
         GROUP BY o.customer_id
         LIMIT 100 ;;
  }

  dimension: id {
    type: number
    sql: ${TABLE}.id ;;
  }
}

Lorsque la vue top_100_users est référencée dans une requête d'exploration, Looker utilise cette instruction SQL SELECT dans le code SQL généré en tant que CTE ou vue intégrée, en fonction de votre dialecte.

L'utilisation de SQL pour définir des tables dérivées peut présenter certaines limites. Dans l'exemple de la section sur les tables dérivées basées sur SQL, les considérations suivantes s'appliquent:

  • La relation entre les tables orders et order_items est probablement déjà définie dans le modèle LookML. Si les noms des tables sous-jacentes de votre base de données changent, les noms des tables doivent être mis à jour à plusieurs endroits, y compris dans la définition de votre table dérivée basée sur SQL.
  • Le code SQL de la définition de votre table dérivée doit être dans le dialecte approprié pour la base de données sous-jacente. Si les données sont déplacées vers une autre base de données, vous devez modifier la définition de votre table dérivée basée sur SQL.

C'est pourquoi les tables dérivées natives constituent souvent un meilleur choix.

Tables dérivées natives

Dans Looker, les tables dérivées natives sont définies avec LookML. Contrairement aux tables dérivées basées sur SQL, lorsque vous définissez une table dérivée native, vous exploitez votre modèle LookML de différentes manières:

  • Si le nom de la table change dans la base de données, vous n'aurez besoin de la mettre à jour qu'une seule fois dans le modèle LookML. Étant donné que votre table dérivée native pointe vers l'objet LookML existant où la table de base de données est définie, votre table dérivée native fait automatiquement référence à la table appropriée.
  • De même, si vous passez à un autre dialecte, le code LookML de la table dérivée native restera valide, car Looker génère le code SQL approprié à votre connexion de base de données.

Vous pouvez définir manuellement le code LookML de la table dérivée native. Toutefois, le moyen le plus simple de créer une table dérivée native consiste à demander à Looker de créer la table dérivée à partir d'une requête d'exploration. Dans votre exploration, sélectionnez les champs que vous souhaitez utiliser dans votre table dérivée, puis utilisez l'option Obtenir le code LookML > Table dérivée dans le menu en forme de roue dentée de l'exploration pour obtenir le code LookML. Looker génère le code LookML nécessaire à la création d'une table dérivée de votre requête d'exploration, y compris les définitions de champs pertinentes de votre modèle LookML nécessaires à la création des colonnes de votre table dérivée. Vous pouvez copier le code LookML dans un fichier de vue de votre projet pour créer la table dérivée.

L'exemple suivant montre un tableau dérivé natif qui identifie les 100 utilisateurs ayant dépensé le plus sur l'ensemble de leurs commandes :

view: top_100_users {
  derived_table: {
    explore_source:  orders {
      column: id {
        field: orders.customer_id
      }
      sorts: [order_items.sum_total_sale_price desc]
      limit: 100
    }
  }
}

Lorsque vous interrogez une table dérivée native dans une exploration, tous les détails de la requête SQL de cette table sont générés automatiquement. Les définitions des colonnes d'une vue LookML avec une table dérivée native sont déduites des définitions de l'exploration sous-jacente, ce qui évite de répéter des définitions.

Tables dérivées persistantes (PDT)

Dans Looker, vous pouvez créer à la fois des tables dérivées temporaires et des tables dérivées persistantes. Une fois que vous avez créé une table dérivée basée sur SQL ou une table dérivée native, vous pouvez ajouter une persistance afin que Looker écrive la table dans un schéma scratch de votre base de données et la régénére selon la fréquence que vous spécifiez. Pour en savoir plus, consultez la page Tables dérivées dans Looker.