Gérer les recommandations de vues matérialisées

Ce document décrit le fonctionnement de l'outil de recommandation de vues matérialisées, ainsi que la manière de les afficher et de les appliquer

Introduction

L'outil de recommandation de vues matérialisées BigQuery peut vous aider à améliorer les performances des charges de travail et à réduire les coûts d'exécution. Ces recommandations sont basées sur l'historique des caractéristiques d'exécution des requêtes au cours des 30 derniers jours.

Dans BigQuery, les vues matérialisées sont des vues précalculées qui mettent régulièrement en cache les résultats d'une requête pour améliorer les performances et l'efficacité. Les vues matérialisées utilisent le réglage intelligent pour réécrire de manière transparente les requêtes sur les tables sources afin d'utiliser les vues matérialisées existantes pour améliorer les performances et l'efficacité.

Fonctionnement de l'outil de recommandation

L'outil de recommandation génère des recommandations quotidiennement pour chaque projet qui exécute des tâches de requête dans BigQuery. Les recommandations sont basées sur l'analyse de l'exécution des charges de travail au cours des 30 derniers jours. L'outil de recommandation de vues matérialisées recherche des schémas de requête répétitifs et calcule les économies qui pourraient être réalisées si la sous-requête répétitive pouvait être déplacée vers une vue matérialisée incrémentielle. L'outil de recommandation prend en compte les économies réalisées au moment de la requête et les coûts de maintenance liés à la vue matérialisée. Si ces facteurs combinés montrent un résultat positif significatif, l'outil de recommandation formule une recommandation.

Prenons l'exemple de requête suivant :

WITH revenue   AS
(SELECT l_suppkey as supplier_no,
        sum(l_extendedprice * (1 - l_discount)) as total_revenue
  FROM lineitem
  WHERE
    l_shipdate >= date '1996-01-01'
    AND l_shipdate < date_add(date '1996-01-01', interval 3 MONTH)
  GROUP BY l_suppkey)
SELECT s_suppkey,
      s_name,
      s_address,
      s_phone,
      total_revenue
FROM
supplier,
revenue
WHERE s_suppkey = supplier_no
AND total_revenue =
  (SELECT max(total_revenue)
    FROM revenue)
ORDER BY s_suppkey

Cet exemple de requête affiche des informations sur le principal fournisseur. La requête contient une expression de table commune (CTE) nommée revenue, qui représente le revenu total pour chaque fournisseur (l_suppkey). revenue est associé à la table des fournisseurs sous la condition que le paramètre total_revenue du fournisseur corresponde à max(total_revenue) pour tous les fournisseurs. Par conséquent, la requête calcule des informations (l_suppkey, s_name, s_address, s_phone, total_revenue) concernant le fournisseur avec le revenu total maximal.

L'intégralité de la requête est trop complexe pour être placée dans une vue matérialisée incrémentielle. Cependant, la CTE supplier est une agrégation sur une seule table, un schéma de requête compatible avec les vues matérialisées incrémentielles. La CTE supplier est également la partie de la requête la plus coûteuse en calcul. Par conséquent, si l'exemple de requête a été exécuté de manière répétée sur des tables sources en constante évolution, l'outil de recommandation de vue matérialisée peut suggérer de placer la CTE supplier dans une vue matérialisée. La recommandation de vue matérialisée pour l'exemple de requête précédent peut ressembler à ce qui suit :

CREATE MATERIALIZED VIEW mv AS
SELECT l_suppkey as supplier_no,
         sum(l_extendedprice * (1 - l_discount)) as total_revenue
  FROM lineitem
  WHERE
    l_shipdate >= date '1996-01-01'
    AND l_shipdate < date_add(date '1996-01-01', interval 3 MONTH)
  GROUP BY l_suppkey

L'API Recommender renvoie également des informations sur l'exécution des requêtes sous la forme d'insights. Les insights sont des résultats qui vous aident à comprendre la charge de travail de votre projet, en fournissant plus de contexte sur la façon dont une recommandation de vue matérialisée peut améliorer les coûts des charges de travail.

Limites

L'outil de recommandation de vues matérialisées n'est pas compatible avec le processus standard permettant de désactiver le traitement des données. Pour cesser de recevoir des recommandations de vues matérialisées, suivez les instructions du formulaire d'inscription à l'offre de pré-DG Gemini dans BigQuery.
Les recommandations de vue matérialisée ne peuvent pas être exportées vers BigQuery.

Avant de commencer

Pour pouvoir afficher ou appliquer des recommandations de vues matérialisées, vous devez activer l'API Recommender.

Autorisations requises

Pour obtenir les autorisations nécessaires pour accéder aux recommandations de vues matérialisées, demandez à votre administrateur de vous accorder le rôle IAM lecteur de l'outil de recommandation de vues matérialisées BigQuery (roles/recommender.bigqueryMaterializedViewViewer). Pour en savoir plus sur l'attribution de rôles, consultez la page Gérer l'accès aux projets, aux dossiers et aux organisations.

Ce rôle prédéfini contient les autorisations nécessaire pour accéder aux recommandations de vues matérialisées. Pour connaître les autorisations exactes requises, développez la section Autorisations requises :

Autorisations requises

Vous devez disposer des autorisations suivantes pour accéder aux recommandations de vues matérialisées :

recommender.bigqueryMaterializedViewRecommendations.get
recommender.bigqueryMaterializedViewRecommendations.list

Vous pouvez également obtenir ces autorisations avec des rôles personnalisés ou d'autres rôles prédéfinis.

Pour plus d'informations sur les rôles et les autorisations IAM dans BigQuery, consultez la page Présentation d'IAM.

Afficher les recommandations de vues matérialisées

Cette section explique comment afficher les recommandations et les insights sur les vues matérialisées à l'aide de la console Google Cloud, de Google Cloud CLI ou de l'API Recommender.

Sélectionnez l'une des options suivantes :

Console

Dans la console Google Cloud, accédez à la page BigQuery.

Accéder à BigQuery
Cliquez sur Recommandations.
Le volet Recommandations BigQuery s'ouvre. Sous Optimiser le coût de la charge de travail BigQuery, cliquez sur Afficher les détails.
Une liste de recommandations s'affiche. Elle contient toutes les recommandations générées pour le projet en cours. Pour afficher plus d'informations sur une recommandation de vue matérialisée ou un insight de table spécifique, cliquez sur Détails.

Vous pouvez également afficher toutes les recommandations disponibles pour votre projet ou votre organisation en cliquant sur Recommandations dans la barre de navigation latérale.

gcloud

Pour afficher les recommandations de vue matérialisée pour un projet spécifique, utilisez la commande gcloud recommender recommendations list :

gcloud recommender recommendations list \
    --project=PROJECT_NAME \
    --location=REGION_NAME \
    --recommender=google.bigquery.materializedview.Recommender \
    --format=FORMAT_TYPE \

Remplacez les éléments suivants :

PROJECT_NAME : nom du projet qui exécute les tâches de requête
REGION_NAME : région dans laquelle les jobs de requête sont exécutés
FORMAT_TYPE : format de sortie gcloud CLI compatible (par exemple, JSON)

Le tableau suivant décrit les champs importants de la réponse "recommendations" :

Propriété	Pertinent pour le sous-type	Description
`recommenderSubtype`	`CREATE_MATERIALIZED_VIEW`	Type de recommandation.
`content.overview.sql`	`CREATE_MATERIALIZED_VIEW`	Instruction LDD suggérée permettant de créer une vue matérialisée.
`content.overview.slotMsSavedMonthly`	`CREATE_MATERIALIZED_VIEW`	Estimation du nombre de millisecondes d'emplacements à enregistrer chaque mois par la vue suggérée.
`content.overview.bytesSavedMonthly`	`CREATE_MATERIALIZED_VIEW`	Estimation du nombre d'octets analysés pour être enregistrés chaque mois, selon la vue suggérée.
`content.overview.baseTables`	`CREATE_MATERIALIZED_VIEW`	Réservé pour une utilisation ultérieure.

Pour en savoir plus sur les autres champs dans la réponse recommendations, consultez Ressource REST : projects.locations.recommenders.recommendation.
Pour en savoir plus sur l'utilisation de l'API Recommender, consultez la section Utiliser l'API - Recommandations.

Pour afficher les insights qui ont demandé des recommandations de vues matérialisées à l'aide de gcloud CLI, exécutez la commande gcloud recommender insights list :

gcloud recommender insights list \
    --project=PROJECT_NAME \
    --location=REGION_NAME \
    --insight-type=google.bigquery.materializedview.Insight \
    --format=FORMAT_TYPE \

Remplacez les éléments suivants :

PROJECT_NAME : nom du projet qui exécute les tâches de requête
REGION_NAME : région dans laquelle les jobs de requête sont exécutés
FORMAT_TYPE : format de sortie gcloud CLI compatible (par exemple, JSON)

Le tableau suivant décrit les champs importants de la réponse de l'API Insights :

Propriété	Pertinent pour le sous-type	Description
`content.queryCount`	`CREATE_MATERIALIZED_VIEW`	Nombre de requêtes au cours de la période d'observation avec un modèle répétitif, pouvant être optimisées à l'aide de la vue matérialisée.

Pour en savoir plus sur les autres champs dans la réponse des insights, consultez la section Ressource REST : projects.locations.insightTypes.insights.
Pour en savoir plus sur l'utilisation des insights, consultez la section Utiliser l'API - Insights.

API REST

Pour afficher les recommandations de vues matérialisées pour un projet spécifique, utilisez l'API REST. Avec chaque commande, vous devez fournir un jeton d'authentification, que vous pouvez obtenir à l'aide de gcloud CLI. Pour en savoir plus sur l'obtention d'un jeton d'authentification, consultez la section Méthodes d'obtention d'un jeton d'ID.

Vous pouvez utiliser la requête curl list pour afficher toutes les recommandations pour un projet spécifique :

$ curl
-H "Authorization: Bearer $(gcloud auth print-access-token)"
-H "x-goog-user-project: PROJECT_NAME" https://recommender.googleapis.com/v1/projects/PROJECT_NAME/locations/LOCATION/recommenders/google.bigquery.materializedview.Recommender/recommendations

Remplacez les éléments suivants :

PROJECT_NAME : nom du projet contenant votre table BigQuery
LOCATION : emplacement du projet.

Le tableau suivant décrit les champs importants de la réponse "recommendations" :

Propriété	Pertinent pour le sous-type	Description
`recommenderSubtype`	`CREATE_MATERIALIZED_VIEW`	Type de recommandation.
`content.overview.sql`	`CREATE_MATERIALIZED_VIEW`	Instruction LDD suggérée permettant de créer une vue matérialisée.
`content.overview.slotMsSavedMonthly`	`CREATE_MATERIALIZED_VIEW`	Estimation du nombre de millisecondes d'emplacements à enregistrer chaque mois par la vue suggérée.
`content.overview.bytesSavedMonthly`	`CREATE_MATERIALIZED_VIEW`	Estimation du nombre d'octets analysés pour être enregistrés chaque mois, selon la vue suggérée.
`content.overview.baseTables`	`CREATE_MATERIALIZED_VIEW`	Réservé pour une utilisation ultérieure.

Pour en savoir plus sur les autres champs dans la réponse recommendations, consultez Ressource REST : projects.locations.recommenders.recommendation.
Pour en savoir plus sur l'utilisation de l'API Recommender, consultez la section Utiliser l'API - Recommandations.

Pour afficher les insights qui ont demandé des recommandations de vues matérialisées à l'aide de l'API REST, exécutez la commande suivante :

$ curl
-H "Authorization: Bearer $(gcloud auth print-access-token)"
-H "x-goog-user-project: PROJECT_NAME" https://recommender.googleapis.com/v1/projects/PROJECT_NAME/locations/LOCATION/insightTypes/google.bigquery.materializedview.Insight/insights

Remplacez les éléments suivants :

PROJECT_NAME : nom du projet contenant votre table BigQuery
LOCATION : emplacement du projet.

Le tableau suivant décrit les champs importants de la réponse de l'API Insights :

Propriété	Pertinent pour le sous-type	Description
`content.queryCount`	`CREATE_MATERIALIZED_VIEW`	Nombre de requêtes au cours de la période d'observation avec un modèle répétitif, pouvant être optimisées à l'aide de la vue matérialisée.

Pour en savoir plus sur les autres champs dans la réponse des insights, consultez la section Ressource REST : projects.locations.insightTypes.insights.
Pour en savoir plus sur l'utilisation des insights, consultez la section Utiliser l'API - Insights.

Afficher les recommandations avec `INFORMATION_SCHEMA`

Vous pouvez également afficher vos recommandations et vos insights à l'aide des vues INFORMATION_SCHEMA. Par exemple, vous pouvez utiliser la vue INFORMATION_SCHEMA.RECOMMENDATIONS pour afficher vos trois principales recommandations basées sur les économies d'emplacements, comme illustré dans l'exemple suivant :

+---------------------------------------------------+--------------------------------------------------------------------------------------------------+
|                    recommender                    |   target_resources      | est_gb_saved_monthly | slot_hours_saved_monthly |  last_updated_time
+---------------------------------------------------+--------------------------------------------------------------------------------------------------+
| google.bigquery.materializedview.Recommender      | ["project_resource"]    | 140805.38289248943   |        9613.139166666666 |  2024-07-01 13:00:00
| google.bigquery.table.PartitionClusterRecommender | ["table_resource_1"]    | 4393.7416711859405   |        56.61476777777777 |  2024-07-01 13:00:00
| google.bigquery.table.PartitionClusterRecommender | ["table_resource_2"]    |   3934.07264107652   |       10.499466666666667 |  2024-07-01 13:00:00
+---------------------------------------------------+--------------------------------------------------------------------------------------------------+

Pour en savoir plus, consultez les ressources suivantes :

Appliquer des recommandations de vues matérialisées

Vous pouvez appliquer une recommandation pour créer une vue matérialisée en exécutant l'instruction LDD de type CREATE MATERIALIZED VIEW suggérée dans la console Google Cloud.

Remarque : Pour exécuter l'instruction LDD CREATE MATERIALIZED VIEW suggérée, vous devez disposer des autorisations nécessaires dans tous les emplacements suivants :

Projet de requête
Ensemble de données contenant les tables sources
Ensemble de données contenant la vue matérialisée

Dans la console Google Cloud, accédez à la page BigQuery.

Accéder à BigQuery
Cliquez sur Recommandations.
Le volet Recommandations BigQuery s'ouvre. Sous Optimiser le coût de la charge de travail BigQuery, cliquez sur Afficher les détails.
Une liste de recommandations s'affiche. Elle contient toutes les recommandations générées pour le projet ou l'organisation en cours, en fonction du champ d'application sélectionné. Recherchez une recommandation de vue matérialisée, puis cliquez sur Détails.
Cliquez sur Afficher dans BigQuery Studio. Un éditeur SQL s'ouvre. Il contient une instruction LDD CREATE MATERIALIZED VIEW.
Dans l'instruction CREATE MATERIALIZED VIEW fournie, remplacez l'espace réservé MATERIALIZED_VIEW par un nom de vue matérialisée unique.
Exécutez l'instruction LDD CREATE MATERIALIZED VIEW pour créer une vue matérialisée recommandée.

Résoudre les problèmes liés aux recommandations

Problème : aucune recommandation n'apparaît pour une table spécifique.

Les recommandations de vues matérialisées peuvent ne pas apparaître dans les cas suivants :

Il n'existe aucun schéma de requête récurrent parmi les tâches de requête exécutées par un projet.
Les schémas de requête récurrents ne respectent pas les limites des vues matérialisées incrémentielles et ne peuvent pas être placés dans une vue matérialisée adaptée au réglage intelligent.
La vue matérialisée potentielle entraîne des coûts de maintenance élevés. Par exemple, les tables sources sont souvent modifiées par des opérations LMD (langage de manipulation de données). Par conséquent, une vue matérialisée fait l'objet d'une actualisation complète, ce qui entraîne des coûts supplémentaires.
Le nombre de requêtes ayant un modèle récurrent commun est insuffisant.
Les économies mensuelles estimées sont trop faibles (moins d'un emplacement).
Les jobs de requête exécutés par le projet utilisent déjà des vues matérialisées.

Tarifs

L'affichage des recommandations n'entraîne aucuns frais et n'a aucune incidence négative sur les performances de la charge de travail.

Lorsque vous appliquez des recommandations en créant des vues matérialisées, des coûts de stockage, de maintenance et de requêtage peuvent être générés. Pour plus d'informations, consultez la section Tarifs des vues matérialisées.

Gérer les recommandations de vues matérialisées

Introduction

Fonctionnement de l'outil de recommandation

Limites

Avant de commencer

Autorisations requises

Autorisations requises

Afficher les recommandations de vues matérialisées

Console

gcloud

API REST

Afficher les recommandations avec INFORMATION_SCHEMA

Appliquer des recommandations de vues matérialisées

Résoudre les problèmes liés aux recommandations

Tarifs

Afficher les recommandations avec `INFORMATION_SCHEMA`