Les tables dérivées ouvrent un monde de possibilités d'analyse avancées, mais elles peuvent être difficiles à approcher, à implémenter et à résoudre. Ce guide contient les cas d'utilisation les plus courants des tables dérivées dans Looker.
Cette page contient les exemples suivants:
- Créer une table à 3h tous les jours
- Ajouter de nouvelles données à une grande table
- Utiliser les fonctions de fenêtrage SQL
- Créer des colonnes dérivées pour les valeurs calculées
- Stratégies d'optimisation
- Tester les optimisations à l'aide de tables PDT
UNIONdeux tables- Additionner une somme (dimensionnaliser une mesure)
- Récapitulatif des tables avec connaissance globale
Ressources de table dérivées
Ces livres de recettes supposent que vous avez des connaissances de base sur LookML et les tables dérivées. Vous devez savoir comment créer des vues et modifier le fichier de modèle. Si vous souhaitez un rappel sur l’un de ces sujets, consultez les ressources suivantes:
- Tables dérivées
- Termes et concepts LookML
- Créer des tables dérivées natives
- Documentation de référence sur le paramètre
derived_table - Mettre des requêtes en cache et régénérer des tables PDT à l'aide de groupes de données
Construire une table à 3h tous les jours
Les données de cet exemple arrivent à 2h tous les jours. Les résultats d'une requête sur ces données seront les mêmes, qu'elle soit exécutée à 3 h du matin ou à 21 h. Il est donc logique de créer la table une fois par jour et de permettre aux utilisateurs d'extraire les résultats d'un cache.
Inclure votre groupe de données dans le fichier de modèle vous permet de le réutiliser avec plusieurs tables et explorations. Ce groupe de données contient un paramètre sql_trigger_value qui indique au groupe de données quand déclencher et recréer la table dérivée.
Pour plus d'exemples d'expressions de déclencheur, consultez la documentation sql_trigger_value.
## in the model file
datagroup: standard_data_load {
sql_trigger_value: SELECT FLOOR(((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),'1970-01-01 00:00:00',SECOND)) - 60*60*3)/(60*60*24)) ;;
max_cache_age: "24 hours"
}
explore: orders {
…
Ajoutez le paramètre datagroup_trigger à la définition derived_table dans le fichier de vue, puis spécifiez le nom du groupe de données que vous souhaitez utiliser. Dans cet exemple, le groupe de données est standard_data_load.
view: orders {
derived_table: {
indexes: ["id"]
datagroup_trigger: standard_data_load
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
…
}
Ajouter de nouvelles données à une grande table
Une PDT incrémentielle est une table dérivée persistante créée par Looker en ajoutant de nouvelles données à la table, au lieu de régénérer la table dans son intégralité.
L'exemple suivant s'appuie sur l'exemple de table orders pour montrer comment la table est compilée de manière incrémentielle. De nouvelles données de commande sont transmises chaque jour. Elles peuvent être ajoutées au tableau existant en ajoutant les paramètres increment_key et increment_offset.
view: orders {
derived_table: {
indexes: ["id"]
increment_key: "created_at"
increment_offset: 3
datagroup_trigger: standard_data_load
distribution_style: all
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
dimension: id {
primary_key: yes
type: number
sql: ${TABLE}.id ;; }
…
}
La valeur increment_key est définie sur created_at, qui correspond à l'incrément de temps pour lequel de nouvelles données doivent être interrogées et ajoutées à la table PDT dans cet exemple.
La valeur increment_offset est définie sur 3 pour spécifier le nombre de périodes passées (selon la granularité de la clé d'incrémentation) qui sont regénérées pour représenter des données tardives.
Utilisation des fonctions de fenêtrage SQL
Certains dialectes de base de données acceptent les fonctions de fenêtre, en particulier pour créer des numéros de séquence, des clés primaires, des totaux cumulés et en cours, et d'autres calculs utiles sur plusieurs lignes. Une fois la requête principale 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 votre fonction de fenêtrage. 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 montre comment créer une table dérivée native qui inclut les colonnes user_id, order_id et created_time. Vous pouvez ensuite utiliser une colonne dérivée avec une fonction de fenêtrage SQL ROW_NUMBER() pour calculer 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
}
}
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 est associé à un paramètre sql qui spécifie 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.
Cet exemple crée 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: users.id
}
column: lifetime_number_of_orders {
field: order_items.count
}
column: lifetime_customer_value {
field: order_items.total_profit
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
Stratégies d'optimisation
Étant donné que des PDT sont stockées dans votre base de données, vous devriez optimiser vos PDT en suivant les stratégies suivantes, selon ce que prend en charge votre dialecte :
Par exemple, pour ajouter la persistance, vous pouvez configurer la table PDT pour qu'elle soit regénérée lorsque le groupe de données orders_datagroup se déclenche, puis ajouter des index sur customer_id et first_order, comme indiqué ci-dessous :
view: customer_order_summary {
derived_table: {
explore_source: orders {
...
}
datagroup_trigger: orders_datagroup
indexes: ["customer_id", "first_order"]
}
}
Si vous n'ajoutez aucun index (ni équivalent pour votre dialecte), Looker vous invite à le faire pour améliorer les performances des requêtes.
Utiliser des tables PDT pour tester les optimisations
Vous pouvez utiliser des PDT pour tester différents indexages, distributions et autres options d'optimisation sans avoir besoin d'une assistance importante de la part de vos administrateurs de base de données ou développeurs ETL.
Imaginons que vous disposiez d'une table, mais que vous souhaitiez tester différents index. Votre code 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 afin d'ajouter des index au LookML, comme indiqué ci-dessous:
view: customer {
# sql_table_name: warehouse.customer
derived_table: {
sql: SELECT * FROM warehouse.customer ;;
persist_for: "8 hours"
indexes: [customer_id, customer_name, salesperson_id]
}
}
Interrogez la vue une fois pour générer la table PDT. Exécutez ensuite vos requêtes de test et comparez les résultats. Si vos résultats sont favorables, vous pouvez demander à votre équipe DBA ou ETL d'ajouter les indices à la table d'origine.
UNION deux tables
Vous pouvez exécuter un opérateur SQL UNION ou UNION ALL dans les deux tables dérivées si votre dialecte SQL le permet. Les opérateurs UNION et UNION ALL combinent les ensembles de résultats de deux requêtes.
Cet exemple montre à quoi ressemble une table dérivée basée sur SQL avec un UNION :
view: first_and_second_quarter_sales {
derived_table: {
sql:
SELECT * AS sales_records
FROM sales_records_first_quarter
UNION
SELECT * AS sales_records
FROM sales_records_second_quarter ;;
}
}
L'instruction UNION du paramètre sql génère une table dérivée qui combine les résultats des deux requêtes.
La différence entre UNION et UNION ALL est que UNION ALL ne supprime pas les lignes en double. Des considérations de performances doivent être prises en compte lorsque vous utilisez UNION par rapport à UNION ALL, car le serveur de base de données doit effectuer des tâches supplémentaires pour supprimer les lignes en double.
Prendre la somme d'une somme (dimensionner une mesure)
En règle générale, en SQL (et par extension, dans Looker), vous ne pouvez pas regrouper une requête en fonction des résultats d'une fonction d'agrégation (représentée dans Looker sous la forme de mesures). Vous ne pouvez regrouper les données que par champs non agrégés (représentés dans Looker sous forme de dimensions).
Pour effectuer un regroupement selon un agrégat (pour prendre la somme d'une somme, par exemple), vous devez "dimensionner" une mesure. Pour ce faire, vous pouvez utiliser une table dérivée, qui crée en fait une sous-requête de l'agrégation.
À partir d'une exploration, Looker peut générer un code LookML pour tout ou partie d'une table dérivée. Il vous suffit de créer une exploration et de sélectionner tous les champs que vous souhaitez inclure dans votre table dérivée. Ensuite, pour générer le code LookML de la table dérivée native (ou basée sur SQL), procédez comme suit :
Cliquez sur le menu Outils de l'exploration et sélectionnez Obtenir LookML.
Pour afficher le code LookML permettant de créer une table dérivée native pour l'exploration, cliquez sur l'onglet Table dérivée.
Copiez le code LookML.
Maintenant que vous avez copié le code LookML généré, collez-le dans un fichier de vue en procédant comme suit:
En mode développement, accédez à vos fichiers de projet.
Cliquez sur + en haut de la liste des fichiers du projet dans l'IDE Looker, puis sélectionnez Créer une vue. Sinon, pour créer le fichier dans le dossier, cliquez sur le menu d'un dossier et sélectionnez Créer une vue.
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.
Tables de consolidation avec reconnaissance d'agrégats
Dans Looker, vous pouvez souvent rencontrer des ensembles de données ou des tables très volumineux qui, pour être performants, nécessitent des tables d'agrégation ou des cumuls.
Grâce à la prise en compte des agrégations de Looker, vous pouvez pré-construire des tables agrégées à différents niveaux de précision, de dimensionnalité et d'agrégation. Vous pouvez également indiquer à Looker comment les utiliser dans les explorations existantes. Les requêtes utiliseront ensuite ces tables de cumul lorsque Looker le juge approprié, sans aucune entrée utilisateur. Cela réduit la taille des requêtes, les temps d'attente et améliore l'expérience utilisateur.
Vous trouverez ci-dessous une implémentation très simple d'un modèle Looker pour démontrer à quel point la reconnaissance des agrégations peut être légère. Imaginons qu'une table de vols hypothétique figure dans la base de données, avec une ligne pour chaque vol enregistré par la FAA. Vous pouvez modéliser cette table dans Looker avec sa propre vue et son propre exploration. Voici le code LookML d'une table agrégée que vous pouvez définir pour l'exploration:
explore: flights {
aggregate_table: flights_by_week_and_carrier {
query: {
dimensions: [carrier, depart_week]
measures: [cancelled_count, count]
}
materialization: {
sql_trigger_value: SELECT CURRENT-DATE;;
}
}
}
Avec cette table agrégée, un utilisateur peut interroger l'exploration flights, et Looker utilisera automatiquement la table agrégée pour répondre aux requêtes. Pour obtenir un aperçu plus détaillé de la connaissance globale, consultez le tutoriel sur la connaissance globale.