Une table dérivée est une requête dont les résultats sont utilisés comme si la table dérivée était une table physique dans la base de données. Une table dérivée native est basée sur une requête que vous définissez à l'aide de termes LookML. Elle est différente d'une table dérivée basée sur SQL, qui est basée sur une requête que vous définissez avec des termes SQL. 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. Pour en savoir plus, consultez la section Tables dérivées natives et tables dérivées basées sur SQL de la page de documentation Tables dérivées dans Looker.
Comme celles basées sur SQL, les tables dérivées natives sont définies dans LookML à l'aide du derived_table paramètre, au niveau de la vue. En revanche, avec les tables dérivées natives, il est inutile de créer une requête SQL. Vous utilisez plutôt le paramètre explore_source pour spécifier l'exploration sur laquelle baser la table dérivée, les colonnes souhaitées et d'autres caractéristiques souhaitées.
Vous pouvez également demander à Looker de créer le code LookML de la table dérivée à partir d'une requête SQL Runner, comme décrit sur la page de documentation Utiliser SQL Runner pour créer des tables dérivées.
Définition de vos tables dérivées natives à partir d'une exploration
À partir d'une exploration, Looker peut générer un code LookML pour tout ou partie d'une table dérivée. Il suffit de créer une exploration et de sélectionner tous les champs souhaités dans la table. Ensuite, pour générer le code LookML de la table dérivée native, procédez comme suit :
Sélectionnez le menu en forme d'engrenage Actions d'exploration , puis Obtenir le code LookML.
Cliquez sur l'onglet Table dérivée pour afficher le LookML et créer une table dérivée native pour l'exploration.
Copiez le code LookML.
Après avoir copié le code LookML généré, collez-le dans un fichier de vue :
En mode Développement, accédez aux fichiers de votre projet.
Cliquez sur le bouton + en haut de la liste des fichiers de projet dans l'IDE Looker, puis sélectionnez Créer une vue. Vous pouvez également cliquer sur le menu d'un dossier, puis sélectionner Créer une vue pour créer le fichier dans le dossier.
Donnez un nom représentatif à la vue.
Au besoin, changez le nom des colonnes, désignez des colonnes dérivées et ajoutez des filtres.
Lorsque vous utilisez une mesure de
type: countdans une exploration, la visualisation libelle les valeurs résultantes avec le nom de la vue plutôt qu'avec le mot Nombre. Pour éviter toute confusion, mettez le nom de votre vue au pluriel. Vous pouvez modifier le nom de la vue en sélectionnant Afficher le nom complet du champ sous Séries dans les paramètres de visualisation ou en utilisant le paramètreview_labelavec une version au pluriel du nom de votre vue.
Définition d'une table dérivée native dans LookML
Que vous utilisiez des tables dérivées déclarées en SQL ou en LookML natif, le résultat d'une derived_table's requête est une table contenant un ensemble de colonnes. Si la table dérivée est exprimée en SQL, les noms de ces colonnes de sortie sont implicitement fournis par la requête SQL. Par exemple, la requête SQL suivante aura les colonnes de sortie user_id, lifetime_number_of_orders et lifetime_customer_value :
SELECT
user_id
, COUNT(DISTINCT order_id) as lifetime_number_of_orders
, SUM(sale_price) as lifetime_customer_value
FROM order_items
GROUP BY 1
Dans Looker, les requêtes sont basées sur une exploration, incluent des champs de mesure et de dimension, ajoutent si nécessaire des filtres, et peuvent même indiquer un ordre de tri. Une table dérivée native contient tous ces éléments, ainsi que les noms de sortie des colonnes.
L'exemple simple suivant produit une table dérivée avec trois colonnes : user_id, lifetime_customer_value et lifetime_number_of_orders. Il n'est pas nécessaire d'écrire manuellement la requête en SQL : Looker la crée automatiquement en utilisant l'exploration désignée order_items et certains de ses champs (order_items.user_id, order_items.total_revenue et order_items.order_count).
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
}
Utilisation d'instructions include pour référencer des champs
Dans le fichier de vue de la table dérivée native, vous utilisez le explore_source paramètre pour pointer vers une exploration et définir les colonnes et autres caractéristiques de la table dérivée native.
Dans le fichier de vue de la table dérivée native, vous n'êtes pas obligé d'utiliser le include paramètre pour pointer vers le fichier contenant la définition de l'exploration. Si vous n'avez pas l'instruction include, l'IDE Looker ne autosuggest les noms de champs ni ne valide vos références de champs lorsque vous créez la table dérivée native. Vous pouvez plutôt utiliser le validateur LookML pour vérifier les champs auxquels vous faites référence dans votre table dérivée native.
Toutefois, si vous souhaitez activer la suggestion automatique et la validation immédiate des champs dans l'IDE Looker, ou si vous avez un projet LookML complexe comportant plusieurs explorations du même nom ou un risque de références circulaires, vous pouvez utiliser le paramètre include pour pointer vers l'emplacement de la définition de l'exploration.
Les explorations sont souvent définies dans un fichier de modèle, mais dans le cas des tables dérivées natives, il est plus propre de créer un fichier distinct pour l'exploration. Les fichiers d'exploration LookML ont l'extension de fichier .explore.lkml, comme décrit dans la documentation Créer des fichiers d'exploration. De cette façon, vous pouvez inclure un unique fichier d'exploration dans le fichier de vue de votre table dérivée native, plutôt que l'intégralité du fichier de modèle.
Si vous souhaitez créer un fichier d'exploration distinct et utiliser le paramètre include pour pointer vers le fichier d'exploration dans le fichier de vue de votre table dérivée native, assurez-vous que vos fichiers LookML répondent aux exigences suivantes :
- Le fichier de vue de la table dérivée native doit inclure le fichier de l'exploration. Exemple :
include: "/explores/order_items.explore.lkml"
- Le fichier de l'exploration doit inclure les fichiers de vue dont il a besoin. Exemple :
include: "/views/order_items.view.lkml"include: "/views/users.view.lkml"
- Le modèle doit inclure le fichier de l'exploration. Exemple :
include: "/explores/order_items.explore.lkml"
Définition de colonnes d'une table dérivée native
Comme illustré dans l'exemple précédent, vous utilisez column pour spécifier les colonnes de sortie de la table dérivée.
Spécification des noms de colonnes
Pour la colonne user_id, le nom de la colonne correspond au nom du champ spécifié dans l'exploration d'origine.
En général, vous souhaitez que les colonnes de la table de sortie portent un nom différent de celui des champs de l'exploration d'origine. L'exemple précédent a produit un calcul de la valeur vie client par utilisateur à l'aide de l'exploration order_items. Dans la table de sortie, total_revenue correspond en réalité à la lifetime_customer_value d'un client.
La déclaration column permet de déclarer un nom de sortie différent du champ d'entrée. Par exemple, le code suivant demande à Looker de "créer une colonne de sortie nommée lifetime_value à partir du champ order_items.total_revenue" :
column: lifetime_value {
field: order_items.total_revenue
}
Noms de colonnes implicites
Si le paramètre field est exclu d'une déclaration de colonne, il est supposé être <explore_name>.<field_name>. Par exemple, si vous avez spécifié explore_source: order_items, alors
column: user_id {
field: order_items.user_id
}
équivaut à
column: user_id {}
Création de colonnes dérivées pour des valeurs calculées
Vous pouvez ajouter des paramètres derived_column pour spécifier des colonnes qui n'existent pas dans l'exploration du paramètre explore_source. Chaque paramètre derived_column comporte un paramètre sql spécifiant comment construire la valeur.
Votre calcul sql peut utiliser toutes les colonnes que vous avez spécifiées à l'aide de paramètres column. Les colonnes dérivées ne peuvent pas inclure de fonctions d'agrégation, mais peuvent en revanche contenir des calculs réalisables sur une seule ligne de la table.
L'exemple suivant produit la même table dérivée que le précédent, à ceci près qu'il ajoute une colonne average_customer_order calculée à partir des colonnes lifetime_customer_value et lifetime_number_of_orders de la table dérivée native.
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
Utilisation des fonctions de fenêtrage SQL
Certains dialectes de base de données prennent en charge les fonctions de fenêtrage, en particulier pour créer des numéros de séquence, des clés primaires, des totaux cumulés et d'autres calculs utiles sur plusieurs lignes. Une fois la requête primaire exécutée, les éventuelles déclarations derived_column sont exécutées lors d'une autre passe.
Si votre dialecte de base de données prend en charge les fonctions de fenêtrage, vous pouvez les utiliser dans votre table dérivée native. Créez un paramètre derived_column avec un paramètre sql contenant la fonction de fenêtrage souhaitée. Pour référencer ces valeurs, vous devez utiliser le nom de colonne défini dans la table dérivée native.
L'exemple suivant crée une table dérivée native qui inclut les colonnes user_id, order_id et created_time. Ensuite, en utilisant une colonne dérivée avec une fonction de fenêtrage SQL ROW_NUMBER(), il calcule une colonne contenant le numéro de séquence d'une commande client.
view: user_order_sequences {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: order_id {
field: order_items.order_id
}
column: created_time {
field: order_items.created_time
}
derived_column: user_sequence {
sql: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_time) ;;
}
}
}
dimension: order_id {
hidden: yes
}
dimension: user_sequence {
type: number
}
}
Ajout de filtres à une table dérivée native
Supposons que vous vouliez créer une table dérivée de la valeur d'un client au cours des 90 jours précédents. Vous souhaitez effectuer les mêmes calculs que dans l'exemple précédent, mais vous ne voulez inclure que les achats effectués au cours des 90 derniers jours.
Il vous suffit d'ajouter un filtre au derived_table qui filtre les transactions des 90 derniers jours. Le paramètre filters d'une table dérivée utilise la même syntaxe que celle que vous utilisez pour créer une mesure filtrée.
view: user_90_day_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: number_of_orders_90_day {
field: order_items.order_count
}
column: customer_value_90_day {
field: order_items.total_revenue
}
filters: [order_items.created_date: "90 days"]
}
}
# Add define view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: number_of_orders_90_day {
type: number
}
dimension: customer_value_90_day {
type: number
}
}
Les filtres seront ajoutés à la clause WHERE lorsque Looker écrira l'instruction SQL de la table dérivée.
De plus, vous pouvez utiliser le dev_filters sous-paramètre de explore_source avec une table dérivée native. Le paramètre dev_filters vous permet d'indiquer les filtres appliqués par Looker uniquement aux versions de développement de la table dérivée. Ainsi, vous pouvez créer des versions filtrées plus petites de la table à des fins d'itération et de test, sans attendre la création de la table complète après chaque modification.
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 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.
Pour en savoir plus, consultez la section Gains d'efficacité en mode Développement.
Utilisation de filtres basés sur un modèle
Vous pouvez utiliser bind_filters pour inclure des filtres basés sur un modèle :
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
Cela revient essentiellement à utiliser le code suivant dans un bloc sql :
{% condition filtered_lookml_dt.filter_date %} users.created_date {% endcondition %}
to_field est le champ auquel le filtre est appliqué. to_field doit être un champ de l'explore_source sous-jacent.
from_field spécifie le champ à partir duquel obtenir le filtre, s'il existe un filtre lors de l'exécution.
Dans l'exemple bind_filters précédent, Looker prendra n'importe quel filtre appliqué au champ filtered_lookml_dt.filter_date et l'appliquera au champ users.created_date.
Vous pouvez également utiliser le bind_all_filters sous-paramètre de explore_source pour transmettre tous les filtres d'exécution d'une exploration à une sous-requête de table dérivée native. Pour en savoir plus, consultez la page de documentation du paramètre explore_source.
Tri et limitation des tables dérivées natives
Vous pouvez également trier et limiter les tables dérivées :
sorts: [order_items.count: desc]
limit: 10
N'oubliez pas qu'une exploration peut présenter les lignes dans un ordre différent de l'ordre de tri sous-jacent.
Conversion de tables NDT dans différents fuseaux horaires
Vous pouvez définir le fuseau horaire d'une table dérivée native à l'aide du sous-paramètre timezone :
timezone: "America/Los_Angeles"
Lorsque vous utilisez le sous-paramètre timezone, toutes les données temporelles dans la table dérivée native sont converties dans le fuseau horaire indiqué. Pour obtenir la liste des fuseaux horaires acceptés, consultez la page de documentation des valeurs timezone.
Si vous ne précisez aucun fuseau horaire dans la définition d'une table dérivée native, celle-ci n'effectuera aucune conversion de fuseau horaire sur les données temporelles, lesquelles utiliseront alors par défaut le fuseau horaire de votre base de données.
Si la table dérivée native n'est pas persistante, vous pouvez définir la valeur du fuseau horaire sur "query_timezone" pour utiliser automatiquement le fuseau horaire de la requête en cours d'exécution.