Tutoriel sur la reconnaissance d'agrégats

Pour en savoir plus, consultez la page de documentation sur la sensibilisation globale.

Introduction

Cette page est un guide pour implémenter la connaissance globale dans un scénario pratique. Elle vous aide à identifier les opportunités d'implémentation, la valeur générée par la connaissance globale et un workflow simple pour l'implémenter dans un modèle réel. Cette page n'est pas une explication détaillée de toutes les fonctionnalités de sensibilisation agrégative ni des cas particuliers, et ne constitue pas un catalogue exhaustif de toutes ses fonctionnalités.

Qu'est-ce que la notoriété globale ?

Dans Looker, vous interrogez principalement des tables brutes ou des vues de votre base de données. Il peut s'agir de tables dérivées persistantes (PDT) 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 agrégations.

En général, vous pouvez créer des tables d'agrégation telles qu'une table orders_daily contenant une dimensionnalité limitée. Ils doivent être traités et modélisés séparément dans l'exploration. Ils ne sont pas bien intégrés au modèle. Ces limites nuisent à l'expérience utilisateur lorsque l'utilisateur doit choisir entre plusieurs explorations pour les mêmes données.

Grâce à la prise en compte des agrégations par Looker, vous pouvez prédéfinir des tables agrégées à différents niveaux de granularité, 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 récapitulatives lorsque Looker le jugera approprié, sans aucune intervention de l'utilisateur. Cela réduit la taille des requêtes, les temps d'attente et améliore l'expérience utilisateur.

REMARQUE:Les tables agrégées de Looker sont un type de table dérivée persistante (PDT). Cela signifie que les tables agrégées ont les mêmes exigences en termes de base de données et de connexion que les fichiers PDT.

Pour savoir si votre dialecte de base de données et votre connexion Looker sont compatibles avec les tables PDT, consultez les conditions requises sur la page de documentation Tables dérivées dans Looker.

Pour savoir si votre dialecte de base de données est compatible avec la prise en compte des agrégations, consultez la page de documentation Prise en compte des agrégations.

Valeur de la notoriété globale

Un certain nombre de propositions de valeur importantes permettent d'agréger des offres de sensibilisation pour générer plus de valeur à partir de votre modèle Looker existant:

  • Amélioration des performances:l'implémentation de la reconnaissance d'agrégats accélère les requêtes des utilisateurs. Looker utilise une table plus petite si elle contient les données nécessaires pour répondre à la requête de l'utilisateur.
  • Économies de coûts:certains dialectes facturent en fonction de la taille de la requête sur un modèle de consommation. En demandant à Looker d'interroger des tables plus petites, vous réduisez les coûts par requête utilisateur.
  • Amélioration de l'expérience utilisateur:en plus d'une expérience améliorée qui permet de récupérer des réponses plus rapidement, la consolidation élimine la création d'explorations redondantes.
  • Réduction de l'empreinte LookML:le remplacement des stratégies de sensibilisation agrégatives existantes basées sur Liquid par une implémentation native et flexible permet d'améliorer la résilience et de réduire le nombre d'erreurs.
  • Possibilité d'exploiter le LookML existant:les tables agrégées utilisent l'objet query, qui réutilise la logique modélisée existante plutôt que de dupliquer la logique avec du code SQL personnalisé explicite.

Exemple de base

Voici une implémentation très simple dans un modèle Looker pour montrer à quel point la prise en compte des agrégations peut être légère. Étant donné une table flights hypothétique dans la base de données avec une ligne pour chaque vol enregistré par la FAA, nous pouvons modéliser cette table dans Looker avec sa propre vue et son exploration. Voici le code LookML d'une table agrégée que nous pouvons 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. Looker s'appuiera automatiquement sur la table agrégée définie dans le code LookML pour répondre aux requêtes. L'utilisateur n'a pas besoin d'indiquer à Looker des conditions spéciales: si le tableau convient aux champs sélectionnés par l'utilisateur, Looker l'utilisera.

Les utilisateurs disposant des autorisations see_sql peuvent utiliser les commentaires de l'onglet SQL d'une exploration pour voir quelle table agrégée sera utilisée pour une requête. Voici un exemple d'onglet SQL Looker pour une requête qui utilise la table agrégative flights:flights_by_week_and_carrier in teach_scratch:

Onglet "SQL" d'une exploration qui affiche le code SQL sous-jacent et un commentaire spécifiant le schéma de travail de la table agrégative utilisée.

Consultez la page de documentation Connaissance des agrégations pour savoir comment déterminer si des tables agrégées sont utilisées pour une requête.

Identifier les opportunités

Pour maximiser les avantages de la notoriété globale, vous devez identifier les cas où elle peut jouer un rôle dans l'optimisation ou dans la valeur de la notoriété globale.

Identifier les tableaux de bord dont l'exécution est longue

Une excellente opportunité de sensibilisation aux données agrégées consiste à créer des tables agrégées pour les tableaux de bord très utilisés avec un temps d'exécution très élevé. Vos utilisateurs peuvent vous signaler des tableaux de bord lents. Si vous disposez de see_system_activity, vous pouvez également utiliser l'exploration de l'historique de l'activité système de Looker pour identifier les tableaux de bord dont l'exécution est plus lente que la moyenne. Pour gagner du temps, vous pouvez ouvrir ce lien d'exploration de l'historique de l'activité système dans un navigateur, puis remplacer "hostname" dans l'URL par le nom de votre instance Looker. Une visualisation "Explorer" s'affiche avec des données sur les tableaux de bord de votre instance, y compris Titre, Historique, Nombre d'explorations, Ratio entre le cache et la base de données et Les performances sont inférieures à la moyenne:

Dans cet exemple, plusieurs tableaux de bord présentant une utilisation élevée ont des performances inférieures à la moyenne, comme le tableau de bord Exemples de visualisations. Le tableau de bord Exemples de visualisations utilise deux explorations. Il est donc judicieux de créer des tableaux agrégatifs pour ces deux explorations.

Identifier les explorations lentes et très sollicitées par les utilisateurs

Une autre opportunité de sensibilisation globale est offerte par les explorations qui sont très consultées par les utilisateurs et dont la réponse aux requêtes est inférieure à la moyenne.

Vous pouvez utiliser l'exploration "Historique de l'activité système" comme point de départ pour identifier des opportunités d'optimisation des explorations. Pour un raccourci, vous pouvez ouvrir le lien d'exploration de l'historique de l'activité système dans un navigateur, puis remplacer "hostname" dans l'URL par le nom de votre instance Looker. Une visualisation d'exploration s'affiche avec des données sur les explorations de votre instance, y compris Exploration, Modèle, Nombre d'exécutions de requêtes, Nombre d'utilisateurs et Durée d'exécution moyenne en secondes:

Visualisation de tableau montrant que les explorations "order_items" et "flights" sont les plus fréquemment interrogées sur l'instance.

Dans l'exploration "Historique", vous pouvez identifier les types d'explorations suivants dans votre instance:

  • Explorations interrogées par les utilisateurs (par opposition aux requêtes de l'API ou des diffusions planifiées)
  • Explorations fréquemment interrogées
  • Explorations dont les performances sont faibles (par rapport aux autres explorations)

Dans l'exemple d'exploration de l'historique des activités système précédent, les explorations flights et order_items sont des candidats probables pour l'implémentation de la sensibilisation globale.

Identifier les champs les plus utilisés dans les requêtes

Enfin, vous pouvez identifier d'autres opportunités au niveau des données en comprenant les champs que les utilisateurs incluent généralement dans les requêtes et les filtres.

Utilisez l'exploration "Utilisation des champs d'activité du système" pour identifier les champs couramment sélectionnés dans les explorations que vous avez identifiées comme lents et très utilisés. Pour gagner du temps, vous pouvez ouvrir ce lien vers l'exploration "Utilisation des champs d'activité système" dans un navigateur, puis remplacer "hostname" dans l'URL par le nom de votre instance Looker. Remplacez les filtres en conséquence. Une exploration s'affiche, avec une visualisation sous forme de graphique à barres qui indique le nombre de fois qu'un champ a été utilisé dans une requête:

Graphique à barres montrant que les champs flights.count et flights.depart_week de l'exploration "Vols" du modèle FAA sont les plus utilisés.

Dans l'exemple d'exploration de l'activité système illustré dans l'image, vous pouvez voir que flights.count et flights.depart_week sont les deux champs les plus couramment sélectionnés pour l'exploration. Par conséquent, ces champs sont de bons candidats à inclure dans des tables agrégées.

Des données concrètes comme celles-ci sont utiles, mais des éléments subjectifs guideront vos critères de sélection. Par exemple, en examinant les quatre champs précédents, vous pouvez supposer sans risque que les utilisateurs consultent généralement le nombre de vols programmés et le nombre de vols annulés, et qu'ils souhaitent ventiler ces données par semaine et par compagnie aérienne. Il s'agit d'un exemple de combinaison claire, logique et concrète de champs et de métriques.

Résumé

Les étapes décrites sur cette page de la documentation doivent vous aider à trouver les tableaux de bord, les explorations et les champs à optimiser. Il est également important de comprendre que ces trois éléments peuvent être mutuellement exclusifs: les tableaux de bord problématiques ne sont pas nécessairement alimentés par les explorations problématiques, et la création de tableaux agrégés avec les champs couramment utilisés ne les aide pas nécessairement. Il est possible qu'il s'agisse de trois implémentations distinctes de la reconnaissance d'agrégats.

Concevoir des tableaux cumulés

Une fois que vous avez identifié les opportunités de sensibilisation globale, vous pouvez concevoir des tableaux agrégatifs qui répondent le mieux à ces opportunités. Consultez la page de documentation Connaissances des données agrégées pour en savoir plus sur les champs, les mesures et les périodes pris en charge dans les tables agrégées, ainsi que pour obtenir d'autres conseils de conception.

REMARQUE:Il n'est pas nécessaire que les tables agrégées correspondent exactement à votre requête pour qu'elle soit utilisée. Si votre requête est au niveau de la semaine et que vous disposez d'une table récapitulative quotidienne, Looker utilisera votre table agrégée au lieu de votre table brute au niveau du code temporel. De même, si vous disposez d'une table agrégative agrégée au niveau brand et date, et qu'un utilisateur interroge la table au niveau brand uniquement, cette table peut toujours être utilisée par Looker pour la visibilité globale.

La prise de conscience globale est compatible avec les mesures suivantes:

  • Mesures standards : mesures de type SUM, COUNT, AVERAGE, MIN et MAX
  • Mesures composites : mesures de type NUMBER, STRING, YESNO et DATE
  • Mesures distinctes approximatives : dialectes pouvant utiliser la fonctionnalité HyperLogLog

La visibilité globale n'est pas prise en charge pour les mesures suivantes:

  • Mesures distinctes:comme la distinctivité ne peut être calculée que sur des données atomiques non agrégées, les mesures *_DISTINCT ne sont pas prises en charge en dehors de ces approximations qui utilisent HyperLogLog.
  • Mesures basées sur la cardinalité:comme pour les mesures distinctes, les médianes et les percentiles ne peuvent pas être pré-agrégés et ne sont pas acceptés. 
REMARQUE:Si vous connaissez une requête utilisateur potentielle avec des types de mesures non compatibles avec la connaissance globale, vous pouvez créer un tableau agrégé correspondant exactement à une requête. Un tableau agrégé correspondant exactement à la requête peut être utilisé pour répondre à une requête avec des types de mesures qui ne seraient pas pris en charge pour la connaissance globale.

Précision des tableaux cumulés

Avant de créer des tableaux pour des combinaisons de dimensions et de mesures, vous devez déterminer les tendances d'utilisation et de sélection de champs courantes afin de créer des tableaux agrégatifs qui seront utilisés aussi souvent que possible et qui auront le plus d'impact. Notez que tous les champs utilisés dans la requête (qu'ils soient sélectionnés ou filtrés) doivent figurer dans la table agrégée pour que la table puisse être utilisée pour la requête. Toutefois, comme indiqué précédemment, il n'est pas nécessaire que la table agrégée corresponde exactement à une requête pour qu'elle puisse être utilisée. Vous pouvez répondre à de nombreuses requêtes potentielles des utilisateurs dans une seule table agrégative tout en constatant de grands gains de performances.

Dans l'exemple d'identification des champs très utilisés dans les requêtes, deux dimensions (flights.depart_week et flights.carrier) sont sélectionnées très fréquemment, ainsi que deux mesures (flights.count et flights.cancelled_count). Il est donc logique de créer un tableau agrégé qui utilise ces quatre champs. De plus, créer une seule table agrégative pour flights_by_week_and_carrier entraînera une utilisation plus fréquente de la table agrégative que deux tables agrégatives différentes pour les tables flights_by_week et flights_by_carrier.

Voici un exemple de table agrégée que nous pourrions créer pour les requêtes sur les champs communs:

  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;;
      }
    }
  }

Les utilisateurs de votre entreprise, les preuves anecdotiques et les données de l'activité système de Looker peuvent vous aider à prendre vos décisions.

Équilibrer l'applicabilité et les performances

L'exemple suivant montre une requête d'exploration des champs Flights Depart Week, Flights Details Carrier, Flights Count et Flights Detailed Cancelled Count de la table agrégative flights_by_week_and_carrier:

Tableau de données d'exploration avec quatre champs de la table agrégée flights_by_week_and_carrier.

L'exécution de cette requête à partir de la table de base de données d'origine a pris 15,8 secondes et a analysé 38 millions de lignes sans aucune jointure à l'aide d'Amazon Redshift. L'opération de pivotement de la requête, qui est une opération utilisateur normale, a pris 29,5 secondes.

Après avoir implémenté la table agrégative flights_by_week_and_carrier, la requête suivante a pris 7,2 secondes et a analysé 4 592 lignes. Cela représente une réduction de 99,98% de la taille de la table. Le pivotement de la requête a pris 9,8 secondes.

L'exploration "Utilisation des champs d'activité système" nous permet de voir à quelle fréquence nos utilisateurs incluent ces champs dans leurs requêtes. Dans cet exemple, flights.count a été utilisé 47 848 fois, flights.depart_week 18 169 fois, flights.cancelled_count 16 570 fois et flights.carrier 13 517 fois.

Même si nous estimons très modestement que 25% de ces requêtes utilisaient les quatre champs de la manière la plus simple (sélection simple, pas de pivot), 3 379 x 8, 6 secondes = 8 heures et 4 minutes de temps d'attente cumulé des utilisateurs supprimées.

REMARQUE:L'exemple de modèle utilisé ici est très basique. Ces résultats ne doivent pas être utilisés comme référence ou cadre de référence pour votre modèle.

Après avoir appliqué le même flux exact à notre modèle d'e-commerce order_items (l'exploration la plus utilisée sur l'instance), les résultats sont les suivants:

Source Durée de la requête Lignes analysées
Table de base 13,1 secondes 285 000
Table agrégée 5,1 secondes 138 000
Delta 8 secondes 147 000

Les champs utilisés dans la requête et le tableau agrégé ultérieur étaient brand, created_date, orders_count et total_revenue, à l'aide de deux jointures. Les champs avaient été utilisés 11 000 fois au total. En supposant la même utilisation combinée d'environ 25%, les économies cumulées pour les utilisateurs seraient de 6 heures et 6 minutes (8 s * 2 750 = 22 000 s). La création du tableau cumulé a pris 17,9 secondes.

En examinant ces résultats, il est utile de prendre un moment pour prendre du recul et évaluer les avantages potentiels de:

  • Optimiser des modèles/explorations plus volumineux et plus complexes dont les performances sont "acceptables" et qui peuvent améliorer leurs performances grâce à de meilleures pratiques de modélisation

contre

  • Utilisation de la connaissance globale pour optimiser les modèles plus simples qui sont utilisés plus fréquemment et dont les performances sont médiocres

Les résultats de vos efforts diminueront à mesure que vous tenterez d'obtenir le dernier pic de performances de Looker et de votre base de données. Vous devez toujours tenir compte des attentes de performances de référence, en particulier de la part des utilisateurs professionnels, et des limites imposées par votre base de données (par exemple, la simultanéité, les seuils de requête, les coûts, etc.). Ne vous attendez pas à ce que la notoriété globale surmonte ces limites.

De plus, lorsque vous concevez une table agrégée, n'oubliez pas que plus vous ajoutez de champs, plus la table agrégée sera volumineuse et lente. Les tables plus grandes peuvent optimiser davantage de requêtes et peuvent donc être utilisées dans plus de situations, mais elles ne seront pas aussi rapides que les tables plus petites et plus simples.

Exemple :

  explore: flights {
    aggregate_table: flights_by_week_and_carrier {
      query: {
        dimensions: [carrier, depart_week,flights.distance, flights.arrival_week,flights.cancelled]
        measures: [cancelled_count, count, flights.average_distance, flights.total_distance]
      }

      materialization: {
        sql_trigger_value: SELECT CURRENT-DATE;;
      }
    }
  }

Le tableau agrégé sera alors utilisé pour toute combinaison de dimensions affichées et pour toutes les mesures incluses. Il pourra ainsi répondre à de nombreuses requêtes utilisateur différentes. Toutefois, pour utiliser cette table pour une requête SELECT simple de carrier et count, il faudrait analyser une table de 885 000 lignes. En revanche, la même requête ne nécessiterait qu'une analyse de 4 592 lignes si la table était basée sur deux dimensions. La table de 885 000 lignes représente toujours une réduction de 97% de la taille de la table (contre 38 millions de lignes auparavant). Toutefois, l'ajout d'une dimension augmente la taille de la table à 20 millions de lignes. Par conséquent, les résultats diminuent à mesure que vous incluez davantage de champs dans votre table agrégée pour l'appliquer à davantage de requêtes.

Créer des tables agrégées

Prenons l'exemple de l'exploration Vols que nous avons identifiée comme une opportunité d'optimisation. La meilleure stratégie consiste à créer trois tableaux agrégatifs différents:

  • flights_by_week_and_carrier
  • flights_by_month_and_distance
  • flights_by_year

Le moyen le plus simple de créer ces tables agrégées consiste à obtenir le code LookML de la table agrégée à partir d'une requête d'exploration ou d'un tableau de bord, puis à l'ajouter aux fichiers de votre projet Looker.

Une fois que vous avez ajouté les tables agrégées à votre projet LookML et déployé les mises à jour en production, vos explorations exploiteront les tables agrégées pour les requêtes de vos utilisateurs.

Persistance

Pour être accessibles à la connaissance globale, les tables agrégées doivent être conservées dans votre base de données. Il est recommandé d'aligner la régénération automatique de ces tables agrégées sur votre règle de mise en cache en utilisant des groupes de données. Vous devez utiliser le même groupe de données pour une table agrégée que celui utilisé pour l'exploration associée. Si vous ne pouvez pas utiliser de groupes de données, vous pouvez utiliser le paramètre sql_trigger_value à la place. L'exemple suivant montre une valeur générique basée sur la date pour sql_trigger_value: 

sql_trigger_value: SELECT CURRENT_DATE() ;;

Vos tables agrégées seront ainsi créées automatiquement à minuit chaque jour.

Logique de période

Lorsque Looker crée une table agrégée, elle inclut les données jusqu'au moment de sa création. Toutes les données qui ont été ajoutées ultérieurement à la table de base de la base de données sont normalement exclues des résultats d'une requête utilisant cette table agrégative.

Ce diagramme montre la chronologie des commandes reçues et enregistrées dans la base de données par rapport au moment où la table agrégative Commandes a été créée. Deux commandes reçues aujourd'hui ne figureront pas dans le tableau agrégé Commandes, car elles ont été reçues après la création du tableau agrégé:

Chronologie des commandes reçues aujourd'hui et hier, excluant deux points de données qui se sont produits après la création du tableau agrégé.

Toutefois, Looker peut ajouter des données fraîches à la table agrégée lorsqu'un utilisateur interroge une période qui chevauche la table agrégée, comme illustré dans le même diagramme chronologique:

La requête de l'utilisateur inclut les points de données sur la chronologie qui ont eu lieu après la création de la table agrégée.

Comme Looker peut joindre des données fraîches à une table agrégative, si un utilisateur filtre une période qui chevauche la fin de la table agrégative et de la table de base, les commandes reçues après la création de la table agrégative seront incluses dans les résultats de l'utilisateur. Pour en savoir plus et connaître les conditions à remplir pour joindre des données fraîches aux requêtes de table agrégées, consultez la page de documentation Connaissance globale.

Résumé

Pour résumer, trois étapes fondamentales sont nécessaires pour créer une implémentation de la sensibilisation globale:

  1. Identifiez les cas où l'optimisation à l'aide de tables agrégées est appropriée et efficace.
  2. Concevez des tables agrégées qui couvrent au mieux les requêtes utilisateur courantes, tout en restant suffisamment petites pour réduire suffisamment la taille de ces requêtes.
  3. Créez les tables agrégées dans le modèle Looker, en associant la persistance de la table à celle du cache d'exploration.