Tutoriel sur la reconnaissance d'agrégats

Pour en savoir plus, consultez la page de documentation Reconnaissance d'agrégation.

Introduction

Cette page est un guide d'implémentation de la notoriété globale dans un scénario pratique. Elle consiste, entre autres, à identifier les opportunités de mise en œuvre, à identifier les bénéfices générés par cette fonctionnalité et à suivre 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 agrégées de notoriété ou des cas limites, ni un catalogue exhaustif de toutes ses fonctionnalités.

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

Dans Looker, vous interrogez principalement des tables ou des vues brutes de votre base de données. Il s'agit parfois 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 propriétés de consolidation.

En règle générale, vous pouvez créer des tables d'agrégation, comme une table orders_daily, dont la dimensionnalité est limitée. Ils doivent être traités et modélisés séparément dans l'exploration et ne se situent pas correctement dans le modèle. Ces limites nuisent à l'expérience utilisateur lorsque l'utilisateur doit choisir entre plusieurs explorations pour les mêmes données.

Désormais, grâce à la reconnaissance de l'agrégation dans Looker, vous pouvez préconstruire des tables agrégées à différents niveaux de précision, de dimensionnalité et d'agrégation, et d'indiquer à Looker comment les utiliser dans des explorations existantes. Les requêtes utiliseront ensuite ces tables de consolidation lorsque Looker le juge approprié, sans aucune entrée utilisateur. Cela réduira la taille des requêtes, réduira les temps d’attente et améliorera 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 tables PDT.

Pour savoir si votre dialecte de base de données et votre connexion Looker sont compatibles avec les tables dérivées persistantes, consultez les conditions requises figurant 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 reconnaissance d'agrégation, consultez la page de documentation Reconnaissance d'agrégation.

L'intérêt de la notoriété globale

Il existe un certain nombre de propositions de valeur clés en vue d'améliorer la valeur ajoutée de votre modèle Looker existant:

• Amélioration des performances:implémenter la détection d'agrégats permet d'accélérer les requêtes des utilisateurs. Looker utilisera une table plus petite si elle contient les données nécessaires à la requête de l'utilisateur.
• Économies:pour certains dialectes, la facturation dépend de la taille de la requête dans un modèle de consommation. En interrogeant des tables plus petites, Looker vous permet de réduire le coût par requête utilisateur.
• Amélioration de l'expérience utilisateur:en plus d'améliorer l'expérience qui récupère les réponses plus rapidement, la consolidation élimine la création d'explorations redondantes.
• Réduction de l'encombrement LookML:le remplacement des stratégies existantes de détection d'agrégations basées sur Liquid par une implémentation flexible et native permet d'augmenter la résilience et de limiter les erreurs.
• Possibilité d'exploiter le code LookML existant:les tables agrégées utilisent l'objet query, qui réutilise la logique modélisée existante au lieu de la dupliquer avec du code SQL personnalisé explicite.

Exemple de base

Voici une implémentation très simple dans un modèle Looker pour démontrer à quel point la prise en compte des agrégats peut être légère. À partir d'une table flights hypothétique dans la base de données avec une ligne pour chaque vol enregistré via la FAA, nous pouvons 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 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 utilisera automatiquement la table agrégée définie ci-dessus et se servira de la table agrégée pour répondre aux requêtes. L'utilisateur n'aura pas à informer Looker de toute condition spéciale. Looker utilisera simplement cette table si elle est adaptée aux champs sélectionnés par l'utilisateur.

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égée flights:flights_by_week_and_carrier in teach_scratch:

Consultez la page de documentation Reconnaissance d'agrégation 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 le rôle que peut jouer cette fonctionnalité dans l'optimisation ou l'amélioration des valeurs mentionnées ci-dessus.

Identifier les tableaux de bord ayant une durée d'exécution élevée

Une excellente occasion de connaître l'agrégat est de créer des tableaux agrégés pour les tableaux de bord très utilisés avec un temps d'exécution très élevé. Vos utilisateurs vous signalent peut-être que les tableaux de bord sont lents, mais si vous utilisez see_system_activity, vous pouvez également utiliser l'exploration de l'historique d'activité du système de Looker pour trouver les tableaux de bord dont l'exécution est plus lente que la moyenne. Pour aller plus vite, vous pouvez ouvrir ce lien d'exploration de l'historique de l'activité du système dans un navigateur, puis remplacer "nom d'hôte" dans l'URL par le nom de votre instance Looker. Une visualisation de l'exploration s'affiche. Elle contient des données sur les tableaux de bord de votre instance, y compris le titre, l'historique, le nombre d'explorations, le nombre d'explorations du cache ou de la base de données et le calcul est moins bien que la moyenne:

Dans cet exemple, un certain nombre de tableaux de bord avec une utilisation élevée sont moins performants que la moyenne. C'est le cas, par exemple, du tableau de bord Sample Visualizations. Le tableau de bord Exemples de visualisation utilise deux explorations. Il peut donc être judicieux de créer des tables agrégées pour ces deux explorations.

Identifier les explorations lentes et fortement interrogées par les utilisateurs

Les explorations, qui sont fortement interrogées par les utilisateurs et dont les réponses aux requêtes sont inférieures à la moyenne, constituent une autre opportunité de renforcer la notoriété de l'agrégat.

Vous pouvez utiliser l'exploration de l'historique d'activité du système comme point de départ pour identifier des opportunités d'optimisation des explorations. Pour aller plus vite, vous pouvez ouvrir le lien Explorer de l'historique d'activité système dans un navigateur, puis remplacer "nom d'hôte" dans l'URL par le nom de votre instance Looker. Une visualisation de l'exploration affiche des données sur les explorations de votre instance, y compris l'exploration, le modèle, le nombre d'exécutions de requêtes, le nombre d'utilisateurs et la durée moyenne d'exécution en secondes:

Dans l'exploration de l'historique, vous pouvez identifier les types d'explorations suivants sur votre instance:

• Explorations interrogées par les utilisateurs (par opposition aux requêtes de l'API ou aux requêtes issues de livraisons planifiées)
• Explorations fréquemment interrogées
• Explorations peu performantes (par rapport aux autres explorations)

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

Identifier les champs très 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 d'utilisation des champs d'activité du système pour comprendre les champs couramment sélectionnés dans les explorations que vous avez identifiées ci-dessus. Pour aller plus vite, vous pouvez ouvrir ce lien d'exploration de l'utilisation des champs de l'activité du système dans un navigateur, puis remplacer "nom d'hôte" dans l'URL par le nom de votre instance Looker. Remplacez les filtres en conséquence. Une exploration avec un graphique à barres indique le nombre de fois qu'un champ a été utilisé dans une requête:

Dans l'exploration de l'activité du système illustrée ci-dessus, vous pouvez voir que flights.count et flights.depart_week sont les deux champs les plus fréquemment sélectionnés pour l'exploration. Il s'agit donc de bons candidats pour les champs à inclure dans des tableaux cumulés.

Des données concrètes de ce type sont utiles, mais il existe des éléments subjectifs qui guideront vos critères de sélection. Par exemple, en examinant les quatre champs précédents, vous pouvez supposer sans problème que les utilisateurs consultent généralement le nombre de vols planifiés et le nombre de vols annulés, et qu'ils veulent répartir ces données par semaine et par transporteur. Il s'agit d'un exemple d'une combinaison claire, logique et concrète de champs et de métriques.

Résumé

Les étapes ci-dessus doivent servir de guide pour trouver des tableaux de bord, des explorations et des champs à prendre en compte pour l'optimisation. Il est également utile de comprendre que ces trois éléments peuvent s'exclure mutuellement: les tableaux de bord problématiques peuvent ne pas être alimentés par les explorations problématiques et la création de tables agrégées avec les champs couramment utilisés peut ne pas aider du tout ces tableaux de bord. Il s'agit peut-être de trois implémentations distinctes de détection d'agrégats.

Concevoir des tables agrégées

Après avoir identifié les opportunités de notoriété globale, vous pouvez créer des tableaux cumulés qui répondront au mieux à ces opportunités. Consultez la page de documentation Reconnaissance d'agrégation pour obtenir des informations sur les champs, les mesures et les périodes acceptés dans les tableaux cumulés, ainsi que pour obtenir d'autres consignes sur la création de tableaux cumulés.

REMARQUE:Les tables agrégées ne doivent pas nécessairement correspondre exactement à votre requête. Si votre requête est effectuée au niveau de la semaine et que vous disposez d'une table de consolidation quotidienne, Looker utilisera votre table agrégée au lieu de votre table brute au niveau de l'horodatage. De même, si une table agrégée est déployée au niveau brand et date et qu'un utilisateur interroge uniquement le niveau brand, cette table peut toujours être utilisée par Looker pour la détection d'agrégations.

La reconnaissance d'agrégats 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 reconnaissance d'agrégats n'est pas compatible avec les mesures suivantes:

• Mesures distinctes:étant donné que la distinction ne peut être calculée que sur des données atomiques et non agrégées, les mesures *_DISTINCT ne sont pas acceptées en dehors de ces approximations qui utilisent HyperLogLog.
• Mesures basées sur la cardinalité:comme les mesures distinctes, les médianes et les centiles ne peuvent pas être pré-agrégés et ne sont pas acceptés. 

REMARQUE:Si vous connaissez une requête d'utilisateur potentielle dont les types de mesures ne sont pas compatibles avec la reconnaissance d'agrégation, vous pouvez créer une table agrégée correspondant exactement à une requête. Un tableau cumulé qui correspond exactement à la requête peut être utilisé pour répondre à une requête avec des types de mesures qui ne seraient autrement pas acceptés pour la reconnaissance d'agrégats.

Précision des tables agrégées

Avant de créer des tableaux pour des combinaisons de dimensions et de mesures, vous devez déterminer des modèles communs d'utilisation et de sélection de champs afin de créer des tableaux cumulés qui seront utilisés aussi souvent que possible avec le plus grand impact possible. Notez que tous les champs utilisés dans la requête (sélectionnés ou filtrés) doivent figurer dans la table agrégée pour que la table soit utilisée pour la requête. Toutefois, comme indiqué précédemment, la table agrégée ne doit pas nécessairement être une correspondance exacte pour une requête. Vous pouvez répondre à de nombreuses requêtes d'utilisateurs potentielles dans un même tableau cumulé, tout en améliorant considérablement les performances.

Dans l'exemple d'identification des champs très utilisés dans les requêtes ci-dessus, deux dimensions sont sélectionnées très fréquemment (flights.depart_week et flights.carrier), ainsi que deux mesures (flights.count et flights.cancelled_count). Il serait donc logique de créer une table agrégée qui utilise ces quatre champs. En outre, la création d'une seule table agrégée pour flights_by_week_and_carrier entraîne une utilisation plus fréquente des tables agrégées que deux tables agrégées 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 portant 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;;
      }
    }
  }

Vos utilisateurs métier et des preuves anecdotiques ainsi que les données de l'activité du système de Looker peuvent guider votre processus de prise de décision.

Équilibrer la portée et les performances

L'exemple suivant illustre une requête d'exploration des champs Semaine de départ, Informations sur les vols, Nombre de vols et Nombre d'annulations détaillées des vols du tableau cumulé 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. Le pivotement de la requête, ce qui serait une opération utilisateur normale, a pris 29,5 secondes.

Après avoir implémenté la table agrégée 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.

Dans l'exploration d'utilisation des champs d'activité du système, nous pouvons voir à quelle fréquence les utilisateurs incluent ces champs dans les requêtes. Dans cet exemple, flights.count a été utilisé 47 848 fois, flights.depart_week a été utilisé 18 169 fois, flights.cancelled_count 16 570 et flights.carrier 13 517 fois.

Même si nous estimons très modestement que 25% de ces requêtes ont utilisé les quatre champs de la façon la plus simple (sélection simple, sans tableau croisé dynamique), 3 379 x 8,6 secondes = 8 heures, 4 minutes de temps d'attente cumulé des utilisateurs ont été éliminé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é exactement le même flux à notre modèle d'e-commerce order_items (l'exploration la plus fréquemment 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 la table agrégée suivante étaient brand, created_date, orders_count et total_revenue, en utilisant deux jointures. Les champs ont été utilisés 11 000 fois au total. Si vous estimez la même utilisation combinée d'environ 25%, l'économie globale réalisée pour les utilisateurs serait de 6 heures, 6 minutes (8 s * 2 750 = 22 000 s). La création de la table agrégée a pris 17,9 secondes.

Au vu de ces résultats, prenez le temps de prendre du recul et d'évaluer les bénéfices que vous pourriez obtenir grâce aux éléments suivants:

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

contre

• Utiliser la reconnaissance d'agrégats pour optimiser des modèles plus simples qui sont utilisés plus souvent et qui ne sont pas performants

Vos efforts seront moins rentables si vous essayez d'obtenir les dernières performances de Looker et de votre base de données. Vous devez toujours être conscient des attentes de base en termes de performances, en particulier des utilisateurs métier, et des limites imposées par votre base de données (par exemple, simultanéité, seuils de requête, coût, etc.). Ne vous attendez pas à ce que la sensibilisation globale dépasse ces limites.

De plus, lors de la conception d'une table agrégée, n'oubliez pas que le fait d'avoir plus de champs se traduira par une table agrégée plus grande et plus lente. Les grands tableaux peuvent optimiser plus de requêtes et être donc utilisés dans plus de situations, mais les grands tableaux ne seront pas aussi rapides que des tableaux plus petits 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;;
      }
    }
  }

Ainsi, le tableau cumulé sera utilisé pour toute combinaison de dimensions affichées et pour n'importe quelle mesure incluse. Ce tableau pourra donc être utilisé pour répondre à de nombreuses requêtes d'utilisateurs différentes. Toutefois, pour utiliser cette table pour une simple requête SELECT de carrier et count, il faudrait analyser une table de 885 000 lignes. En revanche, la même requête ne nécessiterait une analyse que de 4 592 lignes si le tableau était basé sur deux dimensions. Le tableau de 885 000 lignes représente toujours une réduction de 97% de la taille du tableau (par rapport aux 38 millions de lignes précédentes), mais l'ajout d'une dimension supplémentaire augmente la taille du tableau à 20 millions de lignes. Par conséquent, les rendements diminuent à mesure que vous incluez des champs dans votre table agrégée afin d'accroître son applicabilité à un plus grand nombre de requêtes.

Créer des tables agrégées

Prenons l'exemple de l'exploration Vols que nous avons identifié comme une opportunité d'optimisation. La meilleure stratégie serait de créer trois tables agrégées différentes:

• 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 à partir d'un tableau de bord, puis d'ajouter le code LookML à vos fichiers de 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 exploitent les tables agrégées pour les requêtes de vos utilisateurs.

Persistance

Pour qu'elles soient accessibles pour la détection d'agrégation, les tables agrégées doivent être conservées dans votre base de données. Il est recommandé d'aligner la génération automatique de ces tables agrégées sur votre règle de mise en cache à l'aide des groupes de données. Vous devez utiliser le même groupe de données pour une table agrégée que celle utilisée 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. Voici 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 calendrier

Lorsque Looker crée une table agrégée, elle inclut les données remontant au moment où la table agrégée a été créée. Toutes les données qui ont été ajoutées par la suite au tableau de base de la base de données seraient normalement exclues des résultats d'une requête utilisant ce tableau cumulé.

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

Toutefois, Looker peut unION de données actualisées dans le tableau cumulé lorsqu'un utilisateur interroge une période qui chevauche le tableau cumulé, comme illustré dans ce même diagramme chronologique:

Étant donné que Looker peut UNION de nouvelles données dans une table agrégée, si un utilisateur filtre sur une période qui chevauche la fin de la table agrégée et de la table de base, les commandes reçues après la création de la table agrégée seront incluses dans les résultats de l'utilisateur. Consultez la page de documentation Reconnaissance d'agrégats pour en savoir plus et connaître les conditions à remplir pour unifier des données actualisées et agréger les requêtes de table.

Résumé

Pour résumer, voici trois étapes fondamentales à suivre pour mettre en place une campagne de sensibilisation globale:

1. Identifiez les opportunités pour lesquelles l'optimisation à l'aide de tableaux cumulés est appropriée et efficace.
2. Créez des tableaux cumulés qui fourniront la plus grande couverture pour les requêtes courantes des utilisateurs, tout en restant suffisamment petits 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.