Les tables dérivées ouvrent un monde de possibilités d'analyse avancées, mais leur approche, leur implémentation et leur dépannage peuvent être complexes. Ce livre de recettes contient les cas d'utilisation les plus courants des tables dérivées dans Looker.
Cette page contient les exemples suivants:
- Je crée une table tous les jours à 3h
- Ajouter des données à une grande table
- Utiliser les fenêtrages SQL
- Créer des colonnes dérivées pour les valeurs calculées
- Stratégies d'optimisation
- Tester les optimisations à l'aide de PDT
UNIONdeux tables- Faire la somme d'une somme (dimensionner une mesure)
- Tableaux de consolidation avec agrégation de données
Ressources de la table dérivée
Dans ces livres de cuisine, nous partons du principe que vous avez une première connaissance de LookML et des tables dérivées. Vous devez savoir 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 de 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 recréer des PDT avec des groupes de données
Je crée une table tous les jours à 3h
Dans cet exemple, les données arrivent tous les jours à 2h. Les résultats d'une requête sur ces données seront les mêmes, qu'elle soit exécutée à 3h ou à 21h. Il est donc logique de créer la table une fois par jour et de laisser les utilisateurs extraire les résultats d'un cache.
L'inclusion d'un 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 lui indique quand déclencher et recréer la table dérivée.
Pour plus d'exemples d'expressions de déclencheur, consultez la documentation de 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 et 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 que Looker compile en ajoutant de nouvelles données à la table, au lieu de la reconstruire 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 arrivent chaque jour et peuvent être ajoutées au tableau existant lorsque vous ajoutez 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 PDT dans cet exemple.
La valeur increment_offset est définie sur 3 pour spécifier le nombre de périodes précédentes (selon la précision de la clé d'incrémentation) qui sont recompilées pour prendre en compte les données tardives.
Utilisation des fonctions de fenêtrage SQL
Certains dialectes de base de données prennent en charge les fonctions de fenêtrage, notamment pour créer des numéros de séquence, des clés primaires, des totaux cumulés et cumulés, ainsi que d'autres calculs à plusieurs lignes utiles. Une fois la requête principale exécutée, toutes les 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 incluant les colonnes user_id, order_id et created_time. Vous devez 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 de la commande d'un 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 comporte un paramètre sql qui spécifie comment construire la valeur.
Le calcul de sql peut utiliser n'importe quelle colonne que vous avez spécifiée à l'aide des 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
Les PDT sont stockées dans votre base de données, vous devez donc les optimiser en utilisant les stratégies suivantes, selon votre dialecte:
Par exemple, pour renforcer la persistance, vous pouvez configurer la PDT pour qu'elle soit recréée lorsque le groupe de données orders_datagroup se déclenche, puis ajouter des index à la fois 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 pas d'index (ni d'équivalent pour votre dialecte), Looker vous invite à le faire pour améliorer les performances des requêtes.
Utiliser des PDT pour tester les optimisations
Vous pouvez utiliser des PDT pour tester différentes options d'indexation, de distribution et d'autres options d'optimisation sans avoir besoin d'une assistance importante de la part de vos développeurs DBA ou ETL.
Imaginons que vous avez une table, mais que vous souhaitez tester différents index. Le code LookML initial pour la vue peut se présenter comme suit:
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 code 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 PDT. Exécutez ensuite vos requêtes de test et comparez vos résultats. Si vos résultats sont favorables, vous pouvez demander à votre équipe DBA ou ETL d'ajouter les index à 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 élément 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 sont à prendre en compte lorsque vous utilisez UNION plutôt que UNION ALL, car le serveur de base de données doit effectuer un travail supplémentaire pour supprimer les lignes en double.
Prendre la somme d'une somme (dimensionner une mesure)
En règle générale, dans 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 forme de mesures). Vous ne pouvez regrouper que par champs non agrégés (représentés dans Looker par des dimensions).
Pour effectuer un regroupement par agrégat (pour prendre la somme d'une somme, par exemple), vous devez "dimensionner" une mesure. Pour ce faire, vous pouvez utiliser un tableau dérivé, qui crée effectivement une sous-requête de l'agrégat.
À 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 que vous voulez 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 consulter 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 aux fichiers de votre projet.
Cliquez sur le signe + en haut de la liste des fichiers du projet dans l'IDE Looker, puis sélectionnez Créer une vue. Pour créer le fichier dans le dossier, vous pouvez également cliquer sur le menu d'un dossier et sélectionner 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 détection d'agrégation
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 fonction Aggregate Awareness 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 utilisent ensuite ces tables de cumul lorsque Looker le juge approprié, sans aucune entrée utilisateur. Cela permet de réduire la taille des requêtes, de réduire les temps d'attente et d'améliorer l'expérience utilisateur.
Voici une implémentation très simple dans un modèle Looker pour démontrer à quel point la reconnaissance d'agrégats peut être légère. À partir d'un tableau hypothétique "flights" de la base de données contenant une ligne pour chaque vol enregistré via la FAA, vous pouvez modéliser cette table dans Looker avec sa propre vue et sa 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 une présentation plus détaillée de la détection d'agrégats, consultez le tutoriel sur la détection d'agrégats.