Résoudre les problèmes liés aux requêtes

Ce document est destiné à vous aider à résoudre les problèmes courants liés à l'exécution de requêtes, tels que l'identification des raisons de lenteur des requêtes ou la résolution des erreurs courantes renvoyées par les requêtes ayant échoué.

Résoudre les problèmes de requêtes lentes

Lorsque vous dépannez des requêtes lentes, tenez compte des causes courantes suivantes:

  1. Consultez la page Google Cloud Service Health pour connaître les interruptions de service BigQuery connues susceptibles d'affecter les performances des requêtes.

  2. Examinez la chronologie de la tâche de votre requête sur la page des informations sur la tâche pour voir le temps nécessaire à l'exécution de chaque étape de la requête.

    • Si la majeure partie du temps écoulé est due à des délais de création longs, contactez Cloud Customer Care pour obtenir de l'aide.

    • Si la majeure partie du temps écoulé est due à des temps d'exécution longs, consultez vos informations sur les performances des requêtes. Les informations sur les performances des requêtes peuvent vous informer si l'exécution de votre requête a dépassé la durée d'exécution moyenne et suggérer les causes possibles. Les causes possibles peuvent inclure un conflit d'emplacements de requête ou un quota de brassage insuffisant. Pour en savoir plus sur chaque problème de performances des requêtes et les solutions possibles, consultez la page Interpréter les informations sur les performances des requêtes.

  3. Examinez la quantité d'octets traités sur la page des informations sur la tâche de requête pour voir si elle est plus élevée que prévu. Pour ce faire, comparez le nombre d'octets traités par la requête en cours avec une autre tâche de requête terminée dans un délai acceptable. S'il existe un écart important du nombre d'octets traités entre les deux requêtes, il est possible que la requête soit lente en raison d'un volume de données important. Pour en savoir plus sur l'optimisation de vos requêtes afin de gérer des volumes de données volumineux, consultez la page Optimiser le calcul des requêtes.

    Vous pouvez également identifier les requêtes de votre projet qui traitent une grande quantité de données en recherchant les requêtes les plus coûteuses à l'aide de la vue INFORMATION_SCHEMA.JOBS.

Si vous ne trouvez toujours pas la raison pour laquelle les performances des requêtes sont plus lentes que prévu, contactez l'assistance Cloud Customer Care pour obtenir de l'aide.

Résolution de schéma Avro

Chaîne d'erreur : Cannot skip stream

Cette erreur peut se produire lors du chargement de plusieurs fichiers Avro avec différents schémas, ce qui entraîne un problème de résolution du schéma et l'échec de la tâche d'importation dans un fichier aléatoire.

Pour résoudre cette erreur, assurez-vous que le dernier fichier alphabétique du job de chargement contient le sur-ensemble (union) des différents schémas. Il s'agit d'une exigence basée sur la manière dont Avro gère la résolution de schéma.

Requêtes simultanées en conflit

Chaîne d'erreur : Concurrent jobs in the same session are not allowed

Cette erreur peut se produire lorsque plusieurs requêtes s'exécutent simultanément dans une session, ce qui n'est pas accepté. Référez-vous aux limites de session.

Instructions LMD en conflit

Chaîne d'erreur : Could not serialize access to table due to concurrent update

Cette erreur peut se produire en cas de conflit lors de l'exécution simultanée de plusieurs instructions LMD (langage de manipulation de données) sur une même table ou lorsque la table est tronquée par une instruction LMD impliquant une mutation. Pour en savoir plus, consultez la section Conflits d'instructions LMD.

Pour résoudre cette erreur, exécutez des opérations LMD qui affectent une seule table afin d'éviter les chevauchements.

Sous-requêtes corrélées

Chaîne d'erreur : Correlated subqueries that reference other tables are not supported unless they can be de-correlated

Cela peut se produire lorsque votre requête contient une sous-requête qui référence une colonne extérieure à cette sous-requête, appelée colonne de corrélation. La sous-requête corrélée est évaluée à l'aide d'une stratégie d'exécution imbriquée inefficace, dans laquelle la sous-requête est évaluée pour chaque ligne de la requête externe générant les colonnes de corrélation. Parfois, BigQuery peut réécrire en interne les requêtes avec des sous-requêtes corrélées afin qu'elles s'exécutent plus efficacement. L'erreur de sous-requêtes corrélée se produit lorsque BigQuery ne peut pas optimiser suffisamment la requête.

Pour résoudre cette erreur, essayez les options suivantes :

  • Supprimez toutes les clauses ORDER BY, LIMIT, EXISTS, NOT EXISTS ou IN de votre sous-requête.
  • Utilisez une requête à plusieurs instructions pour créer une table temporaire à référencer dans votre sous-requête.
  • Réécrivez votre requête pour utiliser une CROSS JOIN à la place.

Autorisations de contrôle des accès insuffisantes au niveau des colonnes

Chaîne d'erreur : Requires raw access permissions on the read columns to execute the DML statements

Cette erreur se produit lorsque vous tentez d'effectuer une instruction LMD DELETE, UPDATE ou MERGE, sans l'autorisation "Lecteur détaillé" sur les colonnes analysées qui utilisent le contrôle des accès au niveau des colonnes. pour restreindre l'accès au niveau des colonnes. Pour en savoir plus, consultez la page Impact sur les opérations d'écriture avec le contrôle des accès au niveau des colonnes.

Identifiants non valides pour les requêtes programmées

Chaînes d'erreur :

  • Error code: INVALID_USERID
  • Error code 5: Authentication failure: User Id not found
  • PERMISSION_DENIED: BigQuery: Permission denied while getting Drive credentials

Cette erreur peut se produire lorsqu'une requête programmée échoue en raison d'identifiants obsolètes, en particulier lors de l'interrogation de données Google Drive.

Pour résoudre cette erreur, mettez à jour les identifiants de la requête programmée.

Identifiants du compte de service non valides

Chaîne d'erreur : HttpError 403 when requesting returned: The caller does not have permission

Cette erreur peut se produire lorsque vous tentez de configurer une requête programmée avec un compte de service. Pour résoudre cette erreur, consultez la procédure de dépannage dans Problèmes d'autorisation.

Heure d'instantané non valide

Chaîne d'erreur : Invalid snapshot time

Cette erreur peut se produire lorsque vous essayez d'interroger des données historiques en dehors de la fenêtre de fonctionnalité temporelle de l'ensemble de données. Pour résoudre cette erreur, modifiez la requête d'accès aux données historiques afin de l'inclure dans la fenêtre de fonctionnalité temporelle de l'ensemble de données.

Cette erreur peut également se produire si l'une des tables utilisées dans la requête est supprimée puis recréée après le démarrage de la requête. Vérifiez si une requête planifiée ou une application effectuent cette opération en même temps que la requête ayant échoué. Le cas échéant, essayez de déplacer le processus qui effectue l'opération de suppression et de recréation à une heure qui n'entre pas en conflit avec les requêtes qui lisent cette table.

La tâche existe déjà

Chaîne d'erreur : Already Exists: Job <job name>

Cette erreur peut se produire pour les tâches de requête qui doivent évaluer des tableaux volumineux, de telle sorte que la création de la tâche de requête prend plus de temps que la moyenne. Par exemple, une requête avec une clause WHERE telle que WHERE column IN (<2000+ elements array>).

Pour résoudre cette erreur, procédez comme suit :

Job introuvable

Chaîne d'erreur : Job not found

Cette erreur peut se produire en réponse à un appel getQueryResults dans lequel aucune valeur n'est spécifiée pour le champ location. Dans ce cas, réessayez l'appel et fournissez une valeur location.

Pour en savoir plus, consultez la page Évitez plusieurs évaluations des mêmes expressions de table courantes (CTE).

La requête dépasse le délai d'exécution

Chaîne d'erreur : Query fails due to reaching the execution time limit

Si votre requête atteint le Délai d'exécution de la requête, vérifiez la durée des précédentes exécutions de la requête en interrogeant la vue INFORMATION_SCHEMA.JOBS avec une requête semblable à l'exemple suivant :

SELECT TIMESTAMP_DIFF(end_time, start_time, SECOND) AS runtime_in_seconds
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE statement_type = 'QUERY'
AND query = "my query string";

Si les exécutions précédentes de la requête ont pris beaucoup moins de temps, utilisez les insights sur les performances des requêtes pour déterminer et résoudre le problème sous-jacent.

La réponse à la requête est trop volumineuse

Chaîne d'erreur : responseTooLarge

Cette erreur se produit lorsque les résultats de la requête dépassent la taille de réponse maximale.

Pour résoudre cette erreur, suivez les instructions fournies pour le message d'erreur responseTooLarge.

Trop d'instructions LMD

Chaîne d'erreur : Too many DML statements outstanding against <table-name>, limit is 20

Cette erreur se produit lorsque vous dépassez la limite de 20 instructions LMD à l'état PENDING dans une file d'attente pour une seule table. Cette erreur se produit généralement lorsque vous envoyez des jobs LMD sur une seule table plus rapidement que ce que BigQuery peut traiter.

Une solution possible consiste à regrouper plusieurs opérations LMD plus petites dans des jobs plus volumineux, mais moins nombreux. Lorsque vous regroupez des tâches plus petites en plus grandes, le coût d'exécution des tâches les plus importantes est amorti et l'exécution est plus rapide. Le regroupement des instructions LMD qui affectent les mêmes données améliore généralement l'efficacité des tâches LMD et est moins susceptible de dépasser la limite de quota de la file d'attente. Pour plus d'informations sur l'optimisation de vos opérations LMD, consultez la section Instructions LMD qui mettent à jour ou insèrent des lignes uniques.

D'autres solutions permettant d'améliorer l'efficacité de vos instructions LMD peuvent consister à partitionner ou à mettre en cluster vos tables. Pour en savoir plus, consultez la section Bonnes pratiques.

L'utilisateur ne dispose pas de l'autorisation requise

Chaînes d'erreur :

  • Access Denied: Project [project_id]: User does not have bigquery.jobs.create permission in project [project_id].
  • User does not have permission to query table project-id:dataset.table.

Cette erreur se produit lorsque vous exécutez une requête sans l'autorisation bigquery.jobs.create sur le projet à partir duquel vous exécutez la requête, quelles que soient vos autorisations sur le projet contenant les données. Vous devez également disposer de l'autorisation bigquery.tables.getData sur toutes les tables et vues auxquelles votre requête fait référence.

Cette erreur peut également se produire si la table n'existe pas dans la région interrogée, par exemple asia-south1. Pour interroger des vues, vous devez également disposer de cette autorisation sur toutes les tables et vues sous-jacentes. Pour en savoir plus sur les autorisations requises, consultez Exécuter une requête.

Lorsque vous corrigez cette erreur, tenez compte des points suivants :

  • Comptes de service : les comptes de service doivent disposer de l'autorisation bigquery.jobs.create sur le projet à partir duquel ils s'exécutent.

  • Rôles personnalisés : l'autorisation bigquery.jobs.create doit être explicitement incluse dans le rôle concerné pour les rôles IAM personnalisés.

  • Ensembles de données partagés : lorsque vous travaillez avec des ensembles de données partagés dans un projet distinct, il se peut que vous ayez tout de même besoin de l'autorisation bigquery.jobs.create dans le projet pour exécuter des requêtes ou des jobs dans cet ensemble de données.

Autoriser l'accès à la table

Pour autoriser un compte principal à accéder à une table, procédez comme suit :

  1. Accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans Explorateur, accédez à la table à laquelle vous devez accéder, sélectionnez  Afficher les actions > Partager, puis cliquez sur Gérer les autorisations.

  3. Dans Ajouter des comptes principaux, saisissez le nom des utilisateurs, groupes, domaines ou comptes de service que vous souhaitez ajouter.

  4. Dans Attribuer des rôles, sélectionnez l'autorisation bigquery.jobs.create. Vous pouvez également attribuer le rôle roles/bigquery.jobUser dans le projet à partir duquel la requête est effectuée, ce qui a pour effet de fournir les autorisations nécessaires.

  5. Cliquez sur Enregistrer.

Problèmes liés à un dépassement de ressources

Les problèmes suivants se produisent lorsque BigQuery ne dispose pas de ressources suffisantes pour traiter votre requête.

La requête dépasse les ressources du processeur

Chaîne d'erreur : Query exceeded resource limits

Cela erreur se produit lorsque les requêtes à la demande utilisent trop de ressources de processeur par rapport à la quantité de données analysée. Pour en savoir plus sur la résolution de ces problèmes, consultez la page Résoudre les problèmes liés au dépassement de ressources.

La requête dépasse les ressources mémoire

Chaîne d'erreur : Resources exceeded during query execution: The query could not be executed in the allotted memory

Pour les instructions SELECT, cette erreur se produit lorsque la requête utilise trop de ressources. Pour résoudre cette erreur, consultez la section Résoudre les problèmes liés au dépassement de ressources.

La requête dépasse les ressources de brassage

Chaîne d'erreur : Resources exceeded during query execution: Your project or organization exceeded the maximum disk and memory limit available for shuffle operations

Cette erreur se produit lorsqu'une requête ne peut pas accéder à suffisamment de ressources de brassage.

Pour résoudre cette erreur, provisionnez plus d'emplacements ou réduisez la quantité de données traitée par la requête. Pour en savoir plus, consultez la section Quota de brassage insuffisant.

Pour en savoir plus sur la résolution de ces problèmes, consultez la section Résoudre les problèmes liés au dépassement de ressources.

Requête trop complexe

Chaîne d'erreur : Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex

Cette erreur se produit lorsqu'une requête est trop complexe. Les principales causes de la complexité sont les suivantes :

  • Clauses WITH profondément imbriquées ou utilisées à plusieurs reprises.
  • Vues profondément imbriquées ou utilisées à plusieurs reprises.
  • Utilisation répétée de l'opérateur UNION ALL.

Pour résoudre cette erreur, essayez les options suivantes :

  • Divisez la requête en plusieurs requêtes, puis utilisez un langage procédural pour exécuter ces requêtes en séquence, avec un état partagé.
  • Utilisez des tables temporaires au lieu des clauses WITH.
  • Réécrivez votre requête pour réduire le nombre d'objets référencés et de comparaisons.

Vous pouvez surveiller de manière proactive les requêtes qui approchent de la limite de complexité à l'aide du champ query_info.resource_warning de la vue INFORMATION_SCHEMA.JOBS. L'exemple suivant renvoie les requêtes ayant entraîné une utilisation élevée des ressources au cours des trois derniers jours :

SELECT
  ANY_VALUE(query) AS query,
  MAX(query_info.resource_warning) AS resource_warning
FROM
  <your_project_id>.`region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  AND query_info.resource_warning IS NOT NULL
GROUP BY
  query_info.query_hashes.normalized_literals
LIMIT
  1000

Pour en savoir plus sur la résolution de ces problèmes, consultez la section Résoudre les problèmes liés au dépassement de ressources.

Résoudre les problèmes liés au dépassement de ressources

Pour les tâches de requête :

Pour optimiser vos requêtes, procédez comme suit :

  • Essayez de retirer une clause ORDER BY.
  • Si la requête utilise JOIN, assurez-vous que la plus grande table se trouve à gauche de la clause.
  • Si la requête utilise FLATTEN, déterminez si elle est nécessaire pour ce cas d'utilisation. Pour en savoir plus, consultez la section sur les données imbriquées et répétées.
  • Si la requête utilise EXACT_COUNT_DISTINCT, pensez à utiliser plutôt COUNT(DISTINCT).
  • Si votre requête utilise COUNT(DISTINCT <value>, <n>) avec une valeur <n> élevée, pensez à utiliser GROUP BY à la place. Pour en savoir plus, consultez la section consacrée à COUNT(DISTINCT).
  • Si votre requête utilise UNIQUE, utilisez plutôt GROUP BY ou une fonction de fenêtre dans une instruction subselect.
  • Si votre requête matérialise plusieurs lignes à l'aide d'une clause LIMIT, envisagez de filtrer une autre colonne telle que ROW_NUMBER(), ou de supprimer complètement la clause LIMIT pour autoriser le chargement en parallèle des écritures.
  • Si votre requête utilise des vues profondément imbriquées et une clause WITH, cela peut entraîner une croissance exponentielle de complexité, atteignant ainsi les limites.
  • Ne remplacez pas les tables temporaires par des clauses WITH. La clause peut devoir être recalculée plusieurs fois, ce qui peut compliquer la requête et la ralentir. La persistance des résultats intermédiaires dans les tables temporaires facilite la complexité.
  • Évitez d'utiliser des requêtes UNION ALL.

Pour en savoir plus, consultez les ressources suivantes :

Pour les tâches de chargement :

Si vous chargez des fichiers Avro ou Parquet, réduisez la taille des lignes dans les fichiers. Recherchez les restrictions de taille spécifiques au format de fichier que vous chargez :

Si cette erreur se produit lors du chargement des fichiers ORC, contactez l'assistance.

Pour l'API Storage :

Chaîne d'erreur : Stream memory usage exceeded

Lors d'un appel ReadRows à l'API Storage Read, certains flux avec une utilisation élevée de mémoire peuvent obtenir une erreur RESOURCE_EXHAUSTED avec ce message. Cela peut se produire lors de la lecture de tables volumineuses ou de tables avec un schéma complexe. Pour résoudre le problème, réduisez la taille des lignes de résultat en sélectionnant moins de colonnes à lire (à l'aide du paramètre selected_fields), ou en simplifiant le schéma de la table.

Étapes suivantes