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

Pour résoudre les problèmes de lenteur des requêtes, tenez compte des causes courantes suivantes:

  1. Consultez la page État des services Google Cloud pour identifier les pannes 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 le service client Cloud 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 de slots de requête ou un quota de mélange 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 actuelle à un autre job de requête qui s'est terminé dans un délai acceptable. Si le nombre d'octets traités entre les deux requêtes est très différent, la requête a peut-être été lente en raison d'un grand volume de données. Pour en savoir plus sur l'optimisation de vos requêtes afin de gérer de grands volumes de données, 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 parvenez toujours pas à trouver la raison expliquant les performances de requêtes plus lentes que prévu, contactez le service client Cloud 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, procédez comme suit :

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).

Position introuvable

Chaîne d'erreur : Dataset [project_id]:[dataset_id] was not found in location [region]

Cette erreur apparaît lorsque vous faites référence à une ressource d'ensemble de données qui n'existe pas ou lorsque l'emplacement dans la requête ne correspond pas à l'emplacement de l'ensemble de données.

Pour résoudre ce problème, spécifiez l'emplacement de l'ensemble de données dans la requête ou vérifiez qu'il est disponible au même endroit.

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. Par exemple, en regroupant les mises à jour et les insertions. Lorsque vous regroupez des jobs plus petites en plus grandes, le coût d'exécution des jobs les plus importants 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 jobs 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 Éviter les instructions LMD qui mettent à jour ou insèrent des lignes simples.

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 la limite de complexité à l'aide du champ query_info.resource_warning dans 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.

Résoudre les problèmes de connectivité

Les sections suivantes expliquent comment résoudre les problèmes de connectivité lorsque vous essayez d'interagir avec BigQuery:

Ajouter le DNS Google à la liste d'autorisation

Utilisez l 'outil Google IP Dig pour résoudre le point de terminaison DNS BigQuery bigquery.googleapis.com en une seule adresse IP d'enregistrement A. Assurez-vous que cette adresse IP n'est pas bloquée dans les paramètres de votre pare-feu.

En général, nous vous recommandons d'ajouter les noms DNS Google à la liste d'autorisation. Les plages d'adresses IP partagées dans les fichiers https://www.gstatic.com/ipranges/goog.json et https://www.gstatic.com/ipranges/cloud.json changent souvent. Nous vous recommandons donc d'ajouter à la liste d'autorisation les noms DNS de Google. Voici une liste de noms DNS courants que nous vous recommandons d'ajouter à la liste d'autorisation:

  • *.1e100.net
  • *.google.com
  • *.gstatic.com
  • *.googleapis.com
  • *.googleusercontent.com
  • *.appspot.com
  • *.gvt1.com

Identifier le proxy ou le pare-feu qui abandonne des paquets

Pour identifier tous les sauts de paquets entre le client et le Google Front End (GFE), exécutez une commande traceroute sur votre machine cliente. Elle peut mettre en évidence le serveur qui abandonne les paquets destinés au GFE. Voici un exemple de commande traceroute:

traceroute -T -p 443 bigquery.googleapis.com

Il est également possible d'identifier les sauts de paquets pour des adresses IP GFE spécifiques si le problème est lié à une adresse IP particulière:

traceroute -T -p 443 142.250.178.138

En cas de problème de délai avant expiration côté Google, la requête parvient jusqu'au GFE.

Si vous constatez que les paquets n'atteignent jamais le GFE, contactez votre administrateur réseau pour résoudre ce problème.

Générer un fichier PCAP et analyser votre pare-feu ou votre proxy

Générez un fichier de capture de paquets (PCAP) et analysez-le pour vous assurer que le pare-feu ou le proxy ne filtre pas les paquets destinés aux adresses IP de Google et qu'il permet aux paquets d'atteindre le GFE.

Voici un exemple de commande pouvant être exécutée avec l'outil tcpdump:

tcpdump -s 0 -w debug.pcap -K -n host bigquery.googleapis.com

Configurer des tentatives pour les problèmes de connectivité intermittents

Dans certains cas, les équilibreurs de charge GFE peuvent interrompre les connexions d'une adresse IP client, par exemple s'ils détectent des modèles de trafic DDoS ou si l'instance de l'équilibreur de charge est réduite, ce qui peut entraîner le recyclage de l'adresse IP du point de terminaison. Si les équilibreurs de charge GFE interrompent la connexion, le client doit intercepter la requête arrivée à expiration et la renvoyer au point de terminaison DNS. Assurez-vous de ne pas utiliser la même adresse IP tant que la requête n'a pas abouti, car elle peut avoir changé.

Si vous avez identifié un problème de délai avant expiration constant côté Google et que les nouvelles tentatives ne fonctionnent pas, contactez le service client Cloud et veillez à inclure un nouveau fichier PCAP généré en exécutant un outil de capture de paquets tel que tcpdump.

Étape suivante