Optimisation des Performances des Requêtes

Comprendre les vues matérialisées dans BigQuery, une fonctionnalité puissante pour optimiser les performances des requêtes et maîtriser les coûts. Les vues matérialisées stockent les résultats de requêtes pré-calculées, ce qui les rend idéales pour les tableaux de bord et rapports exécutés fréquemment mais ne nécessitant pas de mises à jour en temps réel.

Qu'est-ce qu'une vue matérialisée

Une vue matérialisée stocke le résultat d'une requête sous forme de table physique :

• Elle se rafraîchit automatiquement en fonction des modifications des données sous-jacentes ;
• Les intervalles de rafraîchissement peuvent être configurés, par exemple toutes les 60 minutes ;
• Les requêtes sur une vue matérialisée sont plus rapides et moins coûteuses que celles sur la table de base.

Pourquoi utiliser les vues matérialisées

Les vues matérialisées offrent plusieurs avantages clés :

• Amélioration des performances en évitant les calculs répétés ;
• Réduction des coûts de requête en diminuant la quantité de données scannées ;
• Optimisation automatique des requêtes, où BigQuery peut utiliser de manière transparente la vue matérialisée à la place de la table de base lorsque cela est possible.

Création d'une vue matérialisée

Les vues matérialisées sont créées à l'aide de CREATE MATERIALIZED VIEW et d'une instruction SELECT standard. Le comportement de rafraîchissement est contrôlé via des options de configuration.

-- 1. Create a materialized view for sales analysis by user loyalty status
CREATE MATERIALIZED VIEW `codefinity-442016.codeFinity_test.mv_sales_by_loyalty` 
OPTIONS(
  enable_refresh = true,
  refresh_interval_minutes = 60
) AS
SELECT 
  up.loyalty_status,
  COUNT(DISTINCT ps.user_id) AS user_count,
  COUNT(ps.sale_id) AS transaction_count,
  SUM(ps.quantity) AS total_items_sold,
  SUM(ps.quantity * ps.sale_price) AS total_revenue,
  AVG(ps.sale_price) AS avg_price_per_unit,
  MAX(PARSE_DATE('%Y-%m-%d', ps.sale_date)) AS last_sale_date
FROM 
  `codefinity-442016.codeFinity_test.product_sales_section4` ps
JOIN 
  `codefinity-442016.codeFinity_test.user_profile` up
  ON ps.user_id = up.user_id
GROUP BY 
  up.loyalty_status;

-- 2. Query the materialized view for much faster results
SELECT 
  loyalty_status,
  user_count,
  transaction_count,
  total_revenue,
  total_revenue / transaction_count AS avg_transaction_value,
  total_revenue / user_count AS revenue_per_user
FROM 
  `codefinity-442016.codeFinity_test.mv_sales_by_loyalty`
ORDER BY 
  total_revenue DESC;

Limitations à prendre en compte

Les vues matérialisées présentent des contraintes importantes :

• Certaines fonctions à état (comme CURRENT_TIMESTAMP) ne sont pas autorisées ;
• Les jointures et agrégations prises en charge sont limitées à des schémas déterministes ;
• Les tables de base doivent se trouver dans le même dataset que la vue matérialisée.

Ces restrictions sont importantes à considérer lors de la conception afin de garantir l'éligibilité de la vue au rafraîchissement automatique.

Exemple

Un tableau de bord qui suit les indicateurs de ventes hebdomadaires peut interroger une vue matérialisée au lieu de la table de transactions brute. Cela améliore considérablement les temps de chargement tout en maintenant une fraîcheur raisonnable des données.

Point clé à retenir

Les vues matérialisées constituent un moyen efficace de faire évoluer les charges de travail BigQuery et de gérer les coûts. Elles sont particulièrement adaptées aux requêtes récurrentes, aux tableaux de bord et aux scénarios de reporting où des mises à jour quasi temps réel sont suffisantes.