Concepts SQL pour les tables dérivées

Bien que les vues LookML soient généralement basées sur des tables existantes dans votre base de données, vous pouvez également créer des vues basées sur des instructions SQL SELECT. 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 dans la base de données.

Dans ce guide, vous allez aborder 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) 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 qui utilise une 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 LookML à l'aide du paramètre sql dans le paramètre derived_table d'une vue. Cela vous permet de définir les colonnes de la table dérivée à l'aide de SQL.

Par exemple, l'exemple de code LookML suivant définit une table dérivée appelée top_100_users qui identifie les 100 utilisateurs ayant le total de dépenses le plus élevé pour toutes 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 Explorer, Looker utilise cette instruction SQL SELECT dans le code SQL généré en tant que CTE ou vue intégrée, selon 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, vous devez les modifier à 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.

Pour ces raisons, les tables dérivées natives sont 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 la manière suivante :

  • Si le nom de la table change dans la base de données, vous n'avez besoin de le modifier qu'une seule fois dans le modèle LookML. Étant donné que votre table dérivée native pointe vers l'objet LookML existant dans lequel la table de base de données est définie, elle fera 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 adapté à 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 la créer à partir d'une requête Explorer. Dans votre exploration, sélectionnez les champs que vous souhaitez utiliser dans votre table dérivée, puis utilisez l'option Obtenir le LookML > Table dérivée du menu en forme de roue dentée de l'exploration pour obtenir le LookML. Looker génère le code LookML dont vous avez besoin pour créer une table dérivée à partir de votre requête d'exploration. Il inclut les définitions de champ pertinentes de votre modèle LookML, qui sont nécessaires pour créer les 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 une table dérivée native qui identifie les 100 utilisateurs ayant dépensé le plus d'argent au total pour toutes 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 pour la table dérivée native 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 les définitions.

Tables dérivées persistantes (PDT)

Dans Looker, vous pouvez créer 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 de la persistance pour que Looker écrive la table dans un schéma temporaire 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 Tables dérivées dans Looker.