Résoudre les problèmes liés aux vues matérialisées

Ce document vous aide à résoudre les problèmes courants liés aux vues matérialisées dans BigQuery, y compris les erreurs lors de la création de vues matérialisées, les échecs d'actualisation et les performances de requête inattendues.

Workflow de diagnostic

Lorsque vous examinez un problème lié à une vue matérialisée, suivez ces étapes de diagnostic pour identifier la cause première :

1. Vérifiez le type de table et les métadonnées. Vérifiez que la table cible est une vue matérialisée et examinez ses options de configuration :

   SELECT
    table_name,
    table_type
   FROM
    `PROJECT_ID.DATASET`.INFORMATION_SCHEMA.TABLES
   WHERE
    table_name = 'MATERIALIZED_VIEW';

   Remplacez les éléments suivants :

   • PROJECT_ID : projet contenant la vue matérialisée.
   • DATASET : ensemble de données contenant la vue matérialisée.
   • MATERIALIZED_VIEW : nom de la vue matérialisée.

   Pour inspecter les options de configuration telles que enable_refresh, refresh_interval_minutes et max_staleness, interrogez la vue INFORMATION_SCHEMA.TABLE_OPTIONS :

   SELECT
    table_name,
    option_name,
    option_value
   FROM
    `PROJECT_ID.DATASET`.INFORMATION_SCHEMA.TABLE_OPTIONS
   WHERE
    table_name = 'MATERIALIZED_VIEW';

2. Vérifiez l'état de la dernière actualisation. Interrogez la vue INFORMATION_SCHEMA.MATERIALIZED_VIEWS pour vérifier la date de la dernière actualisation de la vue et si la dernière actualisation automatique a rencontré des erreurs :

   SELECT
    table_name,
    last_refresh_time,
    refresh_watermark,
    last_refresh_status
   FROM
    `PROJECT_ID.DATASET`.INFORMATION_SCHEMA.MATERIALIZED_VIEWS
   WHERE
    table_name = 'MATERIALIZED_VIEW';

   Si last_refresh_status n'est pas NULL, le dernier job d'actualisation automatique a échoué. Si last_refresh_time est NULL ou ancien, cela signifie que l'actualisation de la vue matérialisée n'a jamais abouti ou qu'elle échoue.

3. Inspectez l'historique et les erreurs des jobs d'actualisation. Interrogez la vue INFORMATION_SCHEMA.JOBS_BY_PROJECT pour inspecter les jobs d'actualisation automatique récents :

   SELECT
    job_id,
    creation_time,
    end_time,
    state,
    error_result.reason AS error_reason,
    error_result.message AS error_message,
    total_slot_ms,
    total_bytes_processed
   FROM
    `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
   WHERE
    job_id LIKE '%materialized_view_refresh_%'
    AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
   ORDER BY
    creation_time DESC
   LIMIT 50;

   Remplacez REGION par la région de votre ensemble de données (par exemple, us ou europe-west3).

4. Examiner les statistiques d'exécution des requêtes et de réglage intelligent Si une requête s'exécute plus lentement que prévu, examinez le champ materialized_view_statistics dans les statistiques du job pour vérifier si l'optimiseur de requête a utilisé la vue matérialisée :

   SELECT
    job_id,
    total_slot_ms,
    total_bytes_billed,
    materialized_view_statistics
   FROM
    `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
   WHERE
    job_id = 'JOB_ID';

   Remplacez JOB_ID par l'ID du job de requête.

Résoudre les erreurs de création de vues matérialisées

Cette section décrit les erreurs que vous pouvez rencontrer lors de la création de vues matérialisées, ainsi que leurs causes et les étapes à suivre pour les résoudre.

Opérateur ou syntaxe SQL non acceptés

Message d'erreur :

Unsupported operator in materialized view: KEYWORD

ou

Materialized view queries do not support FEATURE

Cause :

Les vues matérialisées incrémentielles sont compatibles avec un sous-ensemble limité de la syntaxe SQL pour permettre la maintenance incrémentielle et le réglage intelligent. Cette erreur peut se produire si la requête définissant votre vue matérialisée inclut des fonctionnalités non compatibles, telles que les suivantes :

• Fonctions non déterministes (par exemple, CURRENT_TIMESTAMP(), RAND() ou SESSION_USER())
• Fonctions de fenêtrage analytique avec OVER()
• Clauses ORDER BY ou LIMIT
• DISTINCT sans agrégation
• Sous-requêtes dans les clauses WHERE ou SELECT
• Fonctions définies par l'utilisateur

Solution :

• Consultez la liste des fonctionnalités SQL non compatibles.

• Si votre requête nécessite des fonctionnalités SQL plus larges, envisagez de créer une vue matérialisée non incrémentielle en définissant allow_non_incremental_definition = true et en définissant un intervalle max_staleness :

  CREATE MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
  OPTIONS (
  enable_refresh = true,
  refresh_interval_minutes = 60,
  max_staleness = INTERVAL "4" HOUR,
  allow_non_incremental_definition = true
  ) AS
  SELECT
  ...

  Remplacez les éléments suivants :

  • PROJECT_ID : projet contenant la vue matérialisée.
  • DATASET : ensemble de données contenant la vue matérialisée.
  • MATERIALIZED_VIEW : nom de la vue matérialisée.

  Les vues matérialisées non incrémentielles sont compatibles avec un ensemble plus large de requêtes SQL, mais elles effectuent toujours des actualisations complètes et ne sont pas compatibles avec le réglage intelligent.

• Si la syntaxe SQL requise n'est pas compatible avec les vues matérialisées non incrémentielles, utilisez une vue logique ou une requête planifiée pour écrire les résultats dans une table de destination.

max_staleness non valide avec la table de base CDC

Message d'erreur :

Materialized view PROJECT_ID:DATASET.MATERIALIZED_VIEW has a CDC table as base table PROJECT_ID:DATASET.TABLE but does not have valid max_staleness. Materialized views over CDC tables must have max_staleness set at least 2 times the base table's max_staleness: 0-0 0 0:0:0

Cause :

Lorsque vous créez une vue matérialisée sur une table de base de capture des données modifiées (CDC), l'option max_staleness de la vue matérialisée doit être configurée sur une valeur au moins deux fois supérieure à celle de l'option max_staleness de la table de base.

Solution :

1. Vérifiez la valeur max_staleness de la table CDC de base en interrogeant la vue INFORMATION_SCHEMA.TABLE_OPTIONS.
2. Définissez l'option max_staleness de la vue matérialisée sur une valeur au moins deux fois supérieure à la valeur max_staleness de la table de base. Par exemple, si la table CDC de base a une valeur max_staleness de 15 minutes, définissez la valeur max_staleness de la vue matérialisée sur au moins 30 minutes. Pour en savoir plus, consultez la section "Instruction ALTER MATERIALIZED VIEW SET OPTIONS" dans Instructions de langage de définition de données (LDD) en GoogleSQL.

Vue matérialisée partitionnée sur une table de base non partitionnée

Message d'erreur :

Partitioned incremental materialized view must be created on top of partitioned managed storage base table.

Cause :

Pour créer une vue matérialisée incrémentielle partitionnée, la table de base sous-jacente doit également être partitionnée, et la colonne de partitionnement de la vue matérialisée doit correspondre à celle de la table de base.

Solution :

• Si vous souhaitez que la vue matérialisée soit partitionnée, assurez-vous que la table de base l'est et configurez la vue matérialisée pour qu'elle utilise la même colonne de partitionnement. Pour en savoir plus, consultez la section Alignement des partitions.
• Si la table de base n'est pas partitionnée, créez la vue matérialisée sans clause PARTITION BY.
• Si vous avez besoin d'une vue partitionnée sur une table non partitionnée, créez une vue matérialisée non incrémentielle avec allow_non_incremental_definition = true et max_staleness. Les vues matérialisées non incrémentielles ne nécessitent pas d'alignement des partitions avec les tables de base.

L'ensemble de données répliqué interrégional est en lecture seule

Message d'erreur :

The dataset replica of the cross region dataset 'PROJECT_ID:DATASET' in region 'REGION' is read-only because it's not the primary replica.

Cause :

Lorsque vous utilisez la réplication d'ensembles de données interrégionaux, les instances répliquées secondaires sont en lecture seule. Vous ne pouvez pas créer de vue matérialisée dans une région de réplique secondaire.

Solution :

Créez la vue matérialisée dans la région principale de l'ensemble de données répliqué. Si vous avez besoin de la vue matérialisée dans la région de l'instance répliquée, créez une instance répliquée de vue matérialisée dans cette région. Pour en savoir plus, consultez Gérer les instances répliquées de vues matérialisées.

Dépassement de la limite de la table de base

Message d'erreur :

Materialized views support at most 10 source tables, query has NUMBER_OF_SOURCE_TABLES

Cause :

Les vues matérialisées BigQuery sont compatibles avec les jointures sur un maximum de 10 tables de base.

Solution :

Refactorisez la requête définissant la vue matérialisée pour qu'elle référence 10 tables de base ou moins. Si votre architecture nécessite de joindre plus de 10 tables, envisagez de préjoindre les tables statiques ou de dimensions dans des tables intermédiaires, ou d'utiliser une requête planifiée ou un pipeline Dataform.

Ressources dépassées lors de la création d'une vue matérialisée

Message d'erreur :

Resources exceeded during query execution: The data accessed in this query is too large; consider accessing fewer tables, or for partitioned tables, fewer partitions.

Cause :

Lorsque vous créez une vue matérialisée, BigQuery effectue une actualisation complète initiale pour la remplir. Si la table de base sous-jacente contient de grandes quantités de données non partitionnées ou si la vue produit des agrégations intermédiaires à cardinalité élevée, l'actualisation initiale peut dépasser les limites de mémoire ou de requête des emplacements.

Solution :

• Ajoutez des conditions de filtre dans la clause WHERE de la vue matérialisée pour limiter le champ d'application des données analysées au sous-ensemble requis.
• Alignez le partitionnement de la vue matérialisée sur celui de la table de base pour supprimer les partitions lors des actualisations.
• Si vous utilisez le calcul à la demande, envisagez d'utiliser les éditions BigQuery avec des réservations d'emplacements dédiées pour fournir une capacité de calcul suffisante pour les actualisations volumineuses.

Problèmes liés aux tables BigLake et à la mise en cache des métadonnées

Symptômes :

Les vues matérialisées sur les tables externes BigLake échouent lors de la création ou de l'actualisation.

Cause :

Les vues matérialisées sur des tables externes sont soumises à des exigences architecturales spécifiques :

• Les vues matérialisées ne sont acceptées que pour les tables BigLake avec la mise en cache des métadonnées activée.
• La valeur max_staleness de la vue matérialisée doit être supérieure à la valeur max_staleness de la table de base BigLake sous-jacente.
• Une vue matérialisée peut faire référence à des tables externes BigLake ou à des tables de stockage géré BigQuery, mais ne peut pas mélanger les types dans une même vue matérialisée.

Solution :

1. Assurez-vous que la mise en cache des métadonnées est activée sur toutes les tables de base BigLake sous-jacentes.
2. Configurez max_staleness sur la vue matérialisée avec une valeur supérieure à l'intervalle de mise en cache des métadonnées des tables de base. Par exemple, si l'intervalle de cache de la table de base est de 30 minutes, définissez max_staleness de la vue matérialisée sur au moins 45 minutes pour prévoir une marge pour l'exécution de l'actualisation.
3. Ne mélangez pas les tables externes et les tables gérées dans une définition de vue matérialisée.

Résoudre les problèmes d'actualisation

Cette section décrit les causes courantes d'échec de l'actualisation et de ralentissement des performances pour les vues matérialisées.

Modifications du schéma de la table de base (invalidQuery)

Symptômes :

La colonne last_refresh_status de INFORMATION_SCHEMA.MATERIALIZED_VIEWS affiche une erreur invalidQuery et les actualisations automatiques cessent de s'exécuter.

Cause :

Si le schéma d'une table de base change (par exemple, si une colonne référencée par la vue matérialisée est supprimée, renommée ou si son type de données est modifié), la requête sous-jacente définissant la vue matérialisée devient non valide.

Solution :

BigQuery ne permet pas de modifier le schéma de colonne d'une vue matérialisée existante. Pour résoudre l'invalidation du schéma, procédez comme suit :

1. Recréez la vue matérialisée à l'aide de l'instruction CREATE OR REPLACE MATERIALIZED VIEW :

   CREATE OR REPLACE MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
   OPTIONS (
    enable_refresh = true,
    refresh_interval_minutes = 30
   ) AS
   SELECT
    ...

   Remplacez les éléments suivants :

   • PROJECT_ID : projet contenant la vue matérialisée.
   • DATASET : ensemble de données contenant la vue matérialisée.
   • MATERIALIZED_VIEW : nom de la vue matérialisée.

2. Vérifiez que la nouvelle définition correspond au schéma de la table de base mise à jour.

Expiration, troncature ou modifications LMD des partitions de la table de base

Symptômes :

L'actualisation des vues matérialisées échoue, ou les requêtes sur les vues matérialisées reviennent à la table de base et s'exécutent lentement.

Cause :

Les opérations suivantes sur les tables de base invalident les données de vue matérialisée existantes :

• Tronquer une table de base ou une partition de table de base (TRUNCATE TABLE)
• Expiration des partitions dans une table de base
• Instructions LMD (langage de manipulation de données) DELETE ou MERGE sur des tables non partitionnées ou des tables de base jointes secondaires

Lorsque ces opérations se produisent, les partitions concernées (ou la vue matérialisée entière pour les tables non partitionnées) sont marquées comme non valides.

Solution :

1. Déclenchez manuellement une actualisation pour rétablir un état valide de la vue matérialisée :

   CALL BQ.REFRESH_MATERIALIZED_VIEW('PROJECT_ID.DATASET.MATERIALIZED_VIEW');

2. Si vous exécutez régulièrement des pipelines ETL par lot qui exécutent des instructions LMD ou tronquent des données, désactivez l'actualisation automatique et appelez BQ.REFRESH_MATERIALIZED_VIEW à la fin de votre pipeline ETL. Pour en savoir plus, consultez Actualisation automatique.

Délai d'expiration des tâches d'actualisation

Symptômes :

Les jobs d'actualisation échouent avec une erreur de délai d'attente après plusieurs heures d'exécution (jusqu'à 12 heures).

Cause :

À mesure que les tables de base augmentent, le volume de données traitées lors d'une actualisation augmente. Si la requête de vue matérialisée ne filtre pas les lignes ou si la vue ne peut pas effectuer de mises à jour incrémentielles en raison d'une invalidation complète, chaque actualisation nécessite une analyse complète des tables de base, ce qui peut épuiser le temps de slot.

Solution :

• Ajoutez des critères de filtre dans la clause WHERE de la vue matérialisée pour limiter les données historiques inutiles.
• Assurez-vous que la vue matérialisée est alignée sur les partitions de la table de base afin que seules les partitions modifiées soient actualisées de manière incrémentielle.
• Allouez une réservation d'emplacements avec une capacité suffisante pour la charge de travail d'actualisation.

Message d'actualisation en double

Message :

Materialized view is already being refreshed.

Cause :

Si les tables de base d'une vue matérialisée JOIN sont mises à jour simultanément, ou si une actualisation manuelle est déclenchée alors qu'une actualisation automatique est déjà en cours, BigQuery détecte l'actualisation simultanée et annule le job en double.

Solution :

Ce comportement est normal et temporaire. Le job en double est arrêté pour éviter un traitement redondant. Vous n'êtes pas facturé pour la tentative d'actualisation en double. Aucune action n'est requise.

Retards d'actualisation des données de streaming (stockage optimisé en écriture)

Symptômes :

Les requêtes sur les tables de base avec des données de streaming à haute vélocité n'apparaissent pas immédiatement dans la vue matérialisée, ou les requêtes reviennent à la table de base.

Cause :

Les données diffusées en flux continu dans BigQuery à l'aide de l'API Storage Write sont initialement stockées dans le stockage optimisé pour l'écriture (tampon de flux continu). Les jobs d'actualisation de la vue matérialisée traitent les données une fois qu'elles ont été validées et converties de la mémoire tampon du flux en stockage par colonnes optimisé.

Pour maintenir la cohérence en temps réel, les requêtes qui lisent des données à partir de la vue matérialisée lisent les données validées à partir de la vue matérialisée et lisent simultanément le delta directement à partir de la mémoire tampon de flux de la table de base.

Solution :

• Si une cohérence de lecture en temps réel est requise pour les données de flux, le planificateur de requêtes combine automatiquement les données de la vue matérialisée avec les deltas de la table de base.
• Si la cohérence en temps réel n'est pas requise et que vous souhaitez éviter d'analyser le tampon de flux pour chaque requête, définissez max_staleness sur la vue matérialisée (par exemple, max_staleness = INTERVAL "15" MINUTE). Les requêtes peuvent alors lire directement à partir de la vue matérialisée précalculée sans traitement delta.

Résoudre les problèmes de performances des requêtes et d'optimisation intelligente

Cette section explique comment résoudre les problèmes liés aux requêtes qui s'exécutent plus lentement que prévu ou qui ne tirent pas parti du réglage intelligent.

Vérifier l'utilisation du réglage intelligent

Lorsque vous interrogez une table de base, BigQuery utilise le réglage intelligent pour réécrire automatiquement la requête afin d'utiliser une vue matérialisée disponible si cela améliore les performances et réduit les coûts.

Pour vérifier si une requête a utilisé une vue matérialisée, inspectez le champ materialized_view_statistics dans les détails du job de requête ou interrogez la vue INFORMATION_SCHEMA.JOBS_BY_PROJECT :

SELECT
  job_id,
  total_slot_ms,
  total_bytes_billed,
  mv.table_reference.dataset_id,
  mv.table_reference.table_id,
  mv.chosen,
  mv.rejected_reason
FROM
  `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
  UNNEST(materialized_view_statistics.materialized_view) AS mv
WHERE
  job_id = 'JOB_ID';

Remplacez les éléments suivants :

• REGION : région de votre ensemble de données (par exemple, us ou europe-west3).
• JOB_ID : ID du job de requête.

Dans l'objet materialized_view_statistics, chaque entrée du tableau materialized_view contient les champs suivants :

• table_reference : identifie le candidat à la vue matérialisée.
• chosen : valeur booléenne indiquant si l'optimiseur de requêtes a sélectionné la vue matérialisée pour l'exécution (true) ou l'a refusée (false).
• estimated_bytes_saved : nombre d'octets estimés que la requête a évité d'analyser en utilisant la vue matérialisée.
• rejected_reason : si chosen est défini sur false, spécifie la raison pour laquelle l'optimiseur a refusé la vue matérialisée.

Pour en savoir plus sur les motifs de refus et l'énumération rejected_reason, consultez Comprendre pourquoi des vues matérialisées ont été refusées.

Motifs courants de refus d'une vue matérialisée

Lorsque chosen est défini sur false, examinez la valeur de rejected_reason pour identifier la cause :

Vue matérialisée non prise en compte (structure de requête non conforme)

Si une vue matérialisée n'est pas listée dans materialized_view_statistics, cela signifie que l'optimiseur de requête a déterminé lors de l'analyse syntaxique que le schéma de requête ne correspondait pas à la définition de la vue matérialisée.

Voici quelques causes courantes :

1. Incohérence entre l'agrégation et le filtre. La requête utilise des fonctions d'agrégation, des colonnes de regroupement ou des prédicats de filtre qui ne peuvent pas être calculés à partir des agrégations précalculées dans la vue matérialisée.
   • Résolution : alignez les fonctions d'agrégation et les regroupements entre vos requêtes et la définition de la vue matérialisée.
2. Vues matérialisées non incrémentielles Les vues créées avec allow_non_incremental_definition = true ne sont pas compatibles avec l'optimisation intelligente.
   • Résolution : Interrogez directement les vues matérialisées non incrémentielles en spécifiant le nom de la vue dans la clause FROM.
3. Requête directe sur une vue obsolète. Si vous interrogez directement une vue matérialisée pour laquelle max_staleness est défini, la requête renvoie des résultats précalculés obsolètes jusqu'à max_staleness sans traitement delta à partir des tables de base.

Erreur de sketch HyperLogLog incompatible

Message d'erreur :

Invalid or incompatible sketch in HLL_COUNT.MERGE_PARTIAL

Cause :

Lorsque vous utilisez des fonctions d'agrégation approximative telles que HLL_COUNT.INIT et HLL_COUNT.MERGE_PARTIAL, BigQuery utilise des croquis HyperLogLog. Si le paramètre de précision spécifié dans la requête ne correspond pas à celui défini dans la vue matérialisée, l'opération de fusion des sketches échoue.

Solution :

Assurez-vous que le paramètre de précision (par exemple, HLL_COUNT.INIT(x, 12)) est identique dans la définition de la vue matérialisée et dans les requêtes référençant ou réécrivant la vue.

Résoudre les problèmes de modification de vues et de schémas

Cette section décrit les problèmes que vous pouvez rencontrer lorsque vous modifiez le schéma ou les options d'une vue matérialisée.

Modifier le schéma de vue matérialisée

Problème :

Toute tentative d'ajout ou de modification de colonnes dans une vue matérialisée à l'aide de ALTER TABLE ou de la console Cloud de Confiance génère une erreur, ou l'option Modifier le schéma n'est pas disponible.

Cause :

BigQuery ne permet pas de modifier directement le schéma de colonne d'une vue matérialisée.

Solution :

• Vous pouvez modifier les options de vue matérialisée (telles que enable_refresh, refresh_interval_minutes et max_staleness) à l'aide de l'instruction ALTER MATERIALIZED VIEW SET OPTIONS :

  ALTER MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
  SET OPTIONS (
  enable_refresh = true,
  refresh_interval_minutes = 20
  );

  Remplacez les éléments suivants :

  • PROJECT_ID : projet contenant la vue matérialisée.
  • DATASET : ensemble de données contenant la vue matérialisée.
  • MATERIALIZED_VIEW : nom de la vue matérialisée.

• Pour modifier la définition de la requête SQL, ajouter des colonnes ou modifier les types de données des colonnes, recréez la vue à l'aide de CREATE OR REPLACE MATERIALIZED VIEW :

  CREATE OR REPLACE MATERIALIZED VIEW `PROJECT_ID.DATASET.MATERIALIZED_VIEW`
  AS SELECT
  ...

Étapes suivantes

• Découvrez comment créer des vues matérialisées.
• Découvrez comment utiliser les vues matérialisées et le réglage intelligent.
• Découvrez comment gérer et actualiser les vues matérialisées.
• Découvrez comment surveiller les actualisations et l'utilisation des vues matérialisées.
• Découvrez comment résoudre les problèmes généraux de performances des requêtes.