Résoudre les problèmes de quota et de limite

BigQuery comprend différents quotas et limites qui limitent le taux et le volume des différentes requêtes et opérations. Ils permettent à la fois de protéger l'infrastructure et d'éviter toute utilisation inattendue du client. Ce document explique comment diagnostiquer et limiter des erreurs spécifiques résultant de quotas et de limites.

Certains messages d'erreur indiquent des quotas ou des limites que vous pouvez augmenter, tandis que d'autres indiquent des quotas ou des limites que vous ne pouvez pas augmenter. Si vous atteignez une limite stricte, vous devez mettre en œuvre des solutions de contournement ou des bonnes pratiques temporaires ou permanentes pour votre charge de travail. Il s'agit d'une bonne pratique, même pour les quotas ou les limites qui peuvent être augmentés.

Ce document organise les messages d'erreur et leurs solutions selon ces catégories. La section "Présentation" plus loin dans ce document explique comment lire un message d'erreur et appliquer la solution appropriée à votre problème.

Si votre message d'erreur n'est pas listé dans ce document, reportez-vous à la liste des messages d'erreur, qui contient des informations plus génériques sur les erreurs.

Présentation

Si une opération BigQuery échoue en raison d'un dépassement de quota, l'API renvoie le code d'état HTTP 403 Forbidden. Le corps de la réponse contient plus d'informations sur le quota qui a été atteinte. Le corps de la réponse ressemble à ce qui suit :

{
  "code" : 403,
  "errors" : [ {
    "domain" : "global",
    "message" : "Quota exceeded: ...",
    "reason" : "quotaExceeded"
  } ],
  "message" : "Quota exceeded: ..."
}

Le champ message de la charge utile décrit la limite qui a été dépassée. Par exemple, le champ message peut indiquer Exceeded rate limits: too many table update operations for this table.

En général, les limites de quota appartiennent à deux catégories, indiquées par le champ reason dans la charge utile de la réponse.

  • rateLimitExceeded : cette valeur indique une limite à court terme. Pour résoudre ces problèmes de limite, réessayez l'opération après quelques secondes. Utilisez un intervalle exponentiel entre les tentatives. Autrement dit, augmentez le délai entre chaque tentative, de manière exponentielle.

  • quotaExceeded : cette valeur indique une limite à plus long terme. Si vous atteignez une limite de quota à plus long terme, vous devez attendre au moins 10 minutes avant de réessayer l'opération. Si vous atteignez régulièrement l'une de ces limites de quota à plus long terme, vous devez analyser votre charge de travail pour identifier et limiter le problème. Vous pouvez par exemple optimiser votre charge de travail ou demander une augmentation du quota.

Pour les erreurs quotaExceeded, examinez le message d'erreur pour comprendre quelle limite de quota a été dépassée. Analysez ensuite votre charge de travail pour voir si vous pouvez éviter de l'atteindre.

Dans certains cas, le quota peut être augmenté en contactant l'assistance BigQuery ou en contactant l'équipe commerciale Google Cloud . Toutefois, nous vous recommandons d'essayer d'abord les suggestions de ce document.

Diagnostic

Pour diagnostiquer les problèmes, procédez comme suit :

  • Utilisez les vues INFORMATION_SCHEMA avec un qualificatif de région pour analyser le problème sous-jacent. Ces vues contiennent des métadonnées sur vos ressources BigQuery, y compris les tâches, les réservations et les insertions en flux continu.

    Par exemple, la requête suivante utilise la vue INFORMATION_SCHEMA.JOBS pour répertorier toutes les erreurs liées aux quotas au cours des dernières 24 heures :

    SELECT
job_id,
creation_time,
error_result
FROM  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY) AND
    error_result.reason IN ('rateLimitExceeded', 'quotaExceeded')

    Remplacez REGION_NAME par la région du projet. Il doit être précédé de region-. Par exemple, pour la zone multirégionale US, utilisez region-us.

  • Affichez les erreurs dans Cloud Audit Logs

    Par exemple, en utilisant l'Explorateur de journaux, la requête suivante renvoie des erreurs avec Quota exceeded ou limit dans la chaîne du message :

    resource.type = ("bigquery_project" OR "bigquery_dataset")
protoPayload.status.code ="7"
protoPayload.status.message: ("Quota exceeded" OR "limit")

    Dans cet exemple, le code d'état 7 indique PERMISSION_DENIED, ce qui correspond au code d'état HTTP 403.

    Pour obtenir d'autres exemples de requêtes Cloud Audit Logs, consultez la page Requêtes BigQuery.

Résoudre les problèmes de quotas ou de limites qui peuvent être augmentés

Vous pouvez augmenter les quotas et limites suivants. Toutefois, il est préférable d'essayer d'abord les solutions de contournement ou les bonnes pratiques suggérées.

Votre projet a dépassé le quota gratuit d'octets de requêtes analysés

BigQuery renvoie cette erreur lorsque vous exécutez une requête au niveau d'utilisation gratuite et que le compte atteint la limite mensuelle de volume de données pouvant être interrogé. Pour en savoir plus sur la tarification des requêtes, consultez la section Niveau d'utilisation gratuite.

Message d'erreur 

Your project exceeded quota for free query bytes scanned

Solution

Pour continuer à utiliser BigQuery, vous devez mettre à niveau le compte vers un compte de facturation Cloud payant.

Erreurs de quota d'insertion en flux continu

Cette section donne des conseils pour résoudre les erreurs de quota liées à l'insertion de données en flux continu dans BigQuery.

Dans certaines régions, les insertions en flux continu ont un quota plus élevé si vous ne remplissez pas le champ insertId pour chaque ligne. Pour en savoir plus sur les quotas d'insertion en flux continu, consultez la section Insertions en flux continu. Les erreurs liées aux quotas d'insertion en flux continu dans BigQuery dépendent de la présence ou de l'absence de insertId.

Message d'erreur

Si le champ insertId est vide, l'erreur de quota suivante est possible :

Limite de quota Message d'erreur
Octets par seconde et par projet Votre entité avec gaia_id : GAIA_ID, projet : PROJECT_ID dans la région : REGION a dépassé le quota d'insertion d'octets par seconde.

Si le champ insertId est renseigné, les erreurs de quota suivantes sont possibles :

Limite de quota Message d'erreur
Lignes par seconde et par projet Votre projet PROJECT_ID dans REGION a dépassé le quota d'insertion en flux continu de lignes par seconde.
Lignes par seconde et par table Votre table TABLE_ID a dépassé le quota d'insertion en flux continu de lignes par seconde.
Octets par seconde et par table Votre table TABLE_ID a dépassé le quota d'insertion en flux continu d'octets par seconde.

Le champ insertId permet de dédupliquer les lignes insérées. Si plusieurs insertions avec le même insertId arrivent dans un intervalle de quelques minutes, BigQuery écrit une seule version de l'enregistrement. Cependant, cette déduplication automatique n'est pas garantie. Pour un débit en flux continu maximal, nous vous recommandons de ne pas inclure insertId et d'utiliser plutôt la déduplication manuelle. Pour en savoir plus, consultez la section Assurer la cohérence des données.

Lorsque vous rencontrez cette erreur, diagnostiquez le problème, puis suivez les étapes recommandées pour le résoudre.

Diagnostic

Utilisez les vues STREAMING_TIMELINE_BY_* pour analyser le trafic en flux continu. Ces vues cumulent des statistiques de flux continu sur des intervalles d'une minute, regroupés par code d'erreur. Les erreurs de quota apparaissent dans les résultats, avec la valeur de error_code égale à RATE_LIMIT_EXCEEDED ou QUOTA_EXCEEDED.

En fonction de la limite de quota spécifique qui a été atteinte, consultez total_rows ou total_input_bytes. Si l'erreur est un quota au niveau de la table, filtrez par table_id.

Par exemple, la requête suivante affiche le nombre total d'octets ingérés par minute et le nombre total d'erreurs de quota :

SELECT
 start_timestamp,
 error_code,
 SUM(total_input_bytes) as sum_input_bytes,
 SUM(IF(error_code IN ('QUOTA_EXCEEDED', 'RATE_LIMIT_EXCEEDED'),
     total_requests, 0)) AS quota_error
FROM
 `region-REGION_NAME`.INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_PROJECT
WHERE
  start_timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY)
GROUP BY
 start_timestamp,
 error_code
ORDER BY 1 DESC

Solution

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

  • Si vous utilisez le champ insertId pour la déduplication et que votre projet se trouve dans une région compatible avec le quota d'insertion en flux continu le plus élevé, nous vous recommandons de supprimer le champ insertId. Cette solution peut nécessiter des étapes supplémentaires pour dédupliquer manuellement les données. Pour en savoir plus, consultez la section Supprimer manuellement les doublons.

  • Si vous n'utilisez pas insertId ou si vous ne pouvez pas le supprimer, surveillez le trafic en flux continu sur une période de 24 heures et analysez les erreurs de quota :

    • Si vous constatez principalement des erreurs RATE_LIMIT_EXCEEDED plutôt que des erreurs QUOTA_EXCEEDED et que votre trafic global est inférieur à 80 % du quota, les erreurs indiquent probablement des pics temporaires. Vous pouvez retenter l'opération en utilisant un intervalle exponentiel entre les tentatives pour résoudre ces erreurs.

    • Si vous utilisez une tâche Dataflow pour insérer des données, pensez à utiliser des tâches de chargement plutôt que des insertions en flux continu. Pour en savoir plus, consultez la section Définir la méthode d'insertion. Si vous utilisez Dataflow avec un connecteur d'E/S personnalisé, envisagez plutôt d'utiliser un connecteur d'E/S intégré. Pour en savoir plus, consultez la section Modèles d'E/S personnalisés.

    • Si vous voyez des erreurs QUOTA_EXCEEDED ou si le trafic global dépasse systématiquement 80 % du quota, envoyez une requête d'augmentation du quota. Pour en savoir plus, consultez Demander un ajustement de quota.

    • Vous pouvez également envisager de remplacer les insertions en flux continu par la nouvelle API Storage Write, qui bénéficie d'un débit plus élevé, un prix plus faible et de nombreuses fonctionnalités utiles.

Nombre maximal de requêtes simultanées contenant des fonctions distantes

BigQuery renvoie cette erreur lorsque le nombre de requêtes simultanées contenant des fonctions distantes dépasse la limite. Toutefois, cette limite peut être augmentée. Essayez d'abord les solutions de contournement et les bonnes pratiques.

Pour en savoir plus sur les limites des fonctions distantes, consultez Fonctions distantes.

Message d'erreur

Exceeded rate limits: too many concurrent queries with remote functions for
this project

Diagnostic

Pour connaître les limites applicables aux requêtes simultanées contenant des fonctions distantes, consultez la section Limites des fonctions distantes.

Solution

Nombre maximal d'instructions CREATE MODEL

Cette erreur signifie que vous avez dépassé le quota pour les instructions CREATE MODEL.

Message d'erreur

Quota exceeded: Your project exceeded quota for CREATE MODEL queries per project.

Solution

Si vous dépassez le quota pour les instructions CREATE MODEL, envoyez un e-mail à bqml-feedback@google.com et demandez une augmentation de quota.

Nombre maximal de tâches de copie par jour et par erreur de quota de projet

BigQuery renvoie cette erreur lorsque le nombre de tâches de copie exécutées dans un projet a dépassé la limite quotidienne. Pour en savoir plus sur la limite applicable aux tâches de copie par jour, consultez la page Tâches de copie.

Message d'erreur

Your project exceeded quota for copies per project

Diagnostic

Si vous souhaitez collecter plus de données sur l'origine des tâches de copie, vous pouvez essayer les solutions suivantes :

  • Si vos tâches de copie se trouvent dans une ou plusieurs régions, vous pouvez essayer d'interroger la table INFORMATION_SCHEMA.JOBS pour ces régions spécifiques. Exemple :

    SELECT
creation_time, job_id, user_email, destination_table.project_id, destination_table.dataset_id, destination_table.table_id
FROM `PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
WHERE
creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY) AND CURRENT_TIMESTAMP()
AND job_type = "COPY"
order by creation_time DESC

    Vous pouvez également ajuster l'intervalle de temps en fonction de la période qui vous intéresse.

  • Pour afficher toutes les tâches de copie dans toutes les régions, vous pouvez utiliser le filtre suivant dans Cloud Logging : 

    resource.type="bigquery_resource"
protoPayload.methodName="jobservice.insert"
protoPayload.serviceData.jobInsertRequest.resource.jobConfiguration.tableCopy:*

Solution

  • Si l'objectif des opérations de copie fréquentes est de créer un instantané de données, envisagez plutôt d'utiliser des instantanés de table. Les instantanés de table sont une alternative moins chère et plus rapide à la copie de tables complètes.
  • Vous pouvez demander une augmentation de quota en contactant l'assistance ou le service commercial. L'examen et le traitement de la demande peuvent prendre plusieurs jours. Nous vous recommandons d'indiquer la priorité, le cas d'utilisation et l'ID de projet dans la requête.

Erreur "Quota d'octets d'extraction par jour dépassé"

BigQuery renvoie cette erreur lorsque l'extraction dépasse la limite quotidienne par défaut de 50 Tio dans un projet. Pour en savoir plus sur les limites des tâches d'extraction, consultez Tâches d'extraction.

Message d'erreur

Your usage exceeded quota for ExtractBytesPerDay

Diagnostic

Si vous exportez une table de plus de 50 Tio, l'exportation échoue, car elle dépasse la limite d'extraction. Pour contourner ce problème, consultez la résolution. Si vous souhaitez exporter des données de table pour des partitions de table spécifiques, vous pouvez utiliser un décorateur de partition pour identifier les partitions à exporter.

Si vous souhaitez collecter des données sur l'utilisation des exportations au cours des derniers jours, vous pouvez essayer ce qui suit :

  • Affichez les quotas de votre projet avec des critères de filtrage tels que Name: Extract bytes per day ou Metric: bigquery.googleapis.com/quota/extract/bytes, ainsi que le graphique "Afficher l'utilisation" pour voir votre tendance d'utilisation sur quelques jours.

  • Vous pouvez également interroger INFORMATION_SCHEMA.JOBS_BY_PROJECT pour afficher le nombre total d'octets extraits sur plusieurs jours. Par exemple, la requête suivante renvoie le nombre total d'octets traités quotidiennement par les jobs EXTRACT au cours des sept derniers jours.

    SELECT
TIMESTAMP_TRUNC(creation_time, DAY) AS day,
SUM ( total_bytes_processed ) / POW(1024, 3) AS total_gigabytes_processed
FROM
`region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()
AND job_type = "EXTRACT"
GROUP BY 1
ORDER BY 2 DESC

  • Vous pouvez ensuite affiner davantage les résultats en identifiant les jobs spécifiques qui consomment plus d'octets que prévu. L'exemple suivant renvoie les 100 principaux jobs EXTRACT qui consomment plus de 100 Go de données traitées au cours des sept derniers jours.

    SELECT
creation_time,
job_id,
total_bytes_processed/POW(1024, 3) AS total_gigabytes_processed
FROM
`region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()
AND job_type="EXTRACT"
AND total_bytes_processed > (POW(1024, 3) * 100)
ORDER BY
total_bytes_processed DESC
LIMIT 100

Vous pouvez également utiliser l'explorateur de jobs avec des filtres tels que Bytes processed more than pour filtrer les jobs à traitement élevé pour une période donnée.

Solution

Pour résoudre cette erreur de quota, vous pouvez créer une réservation d'emplacement et attribuer votre projet à la réservation avec le type de tâche PIPELINE. Cette méthode peut contourner la vérification des limites, car elle utilise vos réservations dédiées plutôt qu'un pool d'emplacements partagés gratuit. Si nécessaire, vous pouvez supprimer la réservation si vous souhaitez utiliser un pool d'emplacements partagés ultérieurement.

Pour découvrir d'autres approches permettant d'exporter plus de 50 Tio, consultez la section des notes dans Tâches d'extraction.

Nombre maximal d'octets tabledata.list par seconde et par erreur de quota de projet

BigQuery renvoie cette erreur lorsque le numéro de projet mentionné dans le message d'erreur atteint la taille maximale des données pouvant être lues par seconde via l'appel d'API tabledata.list dans un projet. Pour en savoir plus, consultez la section Nombre maximal d'octets tabledata.list par minute.

Message d'erreur

Your project:[project number] exceeded quota for tabledata.list bytes per second per project

Solution

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

  • En règle générale, nous vous recommandons de respecter cette limite. Par exemple, en séparant les requêtes sur une période plus longue avec des délais. Si l'erreur ne se produit pas fréquemment, la mise en œuvre de nouvelles tentatives avec un intervalle exponentiel entre les tentatives peut résoudre ce problème.
  • Si le cas d'utilisation nécessite la lecture rapide et fréquente d'une grande quantité de données à partir d'une table, nous vous recommandons d'utiliser l'API BigQuery Storage Read plutôt que l'API tabledata.list.

  • Si les suggestions précédentes ne fonctionnent pas, vous pouvez demander une augmentation du quota à partir du tableau de bord des API de la consoleGoogle Cloud en procédant comme suit :

    1. Accédez au tableau de bord des API de la consoleGoogle Cloud .
    2. Dans le tableau de bord, utilisez les filtres pour trouver le quota : Tabledata list bytes per minute (default quota).
    3. Sélectionnez le quota, puis suivez les instructions de la section Demander un ajustement de quota.

    L'examen et le traitement de la demande peuvent prendre plusieurs jours.

Erreurs de limite du nombre maximal de requêtes API

BigQuery renvoie cette erreur lorsque vous atteignez la limite de débit pour le nombre de requêtes API adressées à une API BigQuery par utilisateur et par méthode (par exemple, la méthode tables.get appelle à partir d'un compte de service, ou la méthode jobs.insert appelle à partir d'une adresse e-mail d'utilisateur différente). Pour en savoir plus, consultez la limite de débit Nombre maximal de requêtes API par seconde, par utilisateur et par méthode dans API BigQuery.

Message d'erreur 

Quota exceeded: Your user_method exceeded quota for concurrent api requests
per user per method.

Lorsque vous rencontrez cette erreur, diagnostiquez le problème, puis suivez les étapes recommandées pour le résoudre.

Diagnostic

Si vous n'avez pas identifié la méthode ayant atteint cette limite de débit, procédez comme suit :

Pour le compte de service

  1. Accédez au projet qui héberge le compte de service.

  2. Dans la console Google Cloud , accédez au tableau de bord des API.

    Pour savoir comment afficher des informations détaillées concernant l'utilisation d'une API, consultez la section Utiliser le tableau de bord des API.

  3. Dans le tableau de bord des API, sélectionnez API BigQuery.

  4. Pour afficher des informations d'utilisation plus détaillées, sélectionnez Métriques, puis procédez comme suit :

    1. Pour Sélectionner des graphiques, sélectionnez Trafic par méthode API.

    2. Filtrez le graphique en fonction des identifiants du compte de service. Vous pouvez constater la présence de pics pour une méthode dans la période où vous avez observé l'erreur.

Pour les appels d'API

Certains appels d'API consignent des erreurs dans les journaux d'audit BigQuery dans Cloud Logging. Pour identifier la méthode ayant atteint la limite, procédez comme suit :

  1. Dans la console Google Cloud , accédez au menu de navigation Google Cloud  , puis sélectionnez Journalisation > Explorateur de journaux pour votre projet :

    Accéder à l'explorateur de journaux

  2. Filtrez les journaux en exécutant la requête suivante :

     resource.type="bigquery_resource"
 protoPayload.authenticationInfo.principalEmail="<user email or service account>"
 "Too many API requests per user per method for this user_method"
 In the log entry, you can find the method name under the property protoPayload.method_name.

    Pour en savoir plus, consultez la présentation des journaux d'audit BigQuery.

Solution

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

  • Réduisez le nombre de requêtes API ou ajoutez un délai entre plusieurs requêtes API, de sorte que le nombre de requêtes reste inférieur à cette limite.

  • Si la limite n'est dépassée qu'occasionnellement, vous pouvez implémenter de nouvelles tentatives sur cette erreur spécifique avec un intervalle exponentiel entre les tentatives.

  • Si vous insérez fréquemment des données, envisagez d'utiliser des insertions en flux continu, car celles-ci ne sont pas affectées par le quota de l'API BigQuery. Cependant, l'API d'insertion en flux continu induit des coûts et dispose de son propre ensemble de limites et quotas.

    Pour en savoir plus sur le coût des insertions en flux continu, consultez la section Tarifs de BigQuery.

  • Lorsque vous chargez des données dans BigQuery à l'aide de Dataflow avec le connecteur d'E/S BigQuery, vous pouvez rencontrer cette erreur pour la méthode tables.get. Pour résoudre ce problème, procédez comme suit :

    • Définissez la disposition de création de la table de destination sur CREATE_NEVER. Pour en savoir plus, consultez la section Disposition de création.

    • Utilisez le SDK Apache Beam version 2.24.0 ou ultérieure. Dans les versions précédentes du SDK, la disposition CREATE_IF_NEEDED appelle la méthode tables.get pour vérifier si la table existe.

  • Vous pouvez demander une augmentation de quota en contactant l'assistance ou le service commercial. Pour obtenir davantage de quota, consultez la section Demander une augmentation de quota. Le traitement d'une demande d'augmentation de quota peut prendre plusieurs jours. Afin de fournir plus d'informations pour votre requête, nous vous recommandons d'inclure dans votre requête la priorité de la tâche, l'utilisateur qui exécute la requête et la méthode concernée.

Résoudre les problèmes liés aux quotas ou aux limites qui ne peuvent pas être augmentés

Vous ne pouvez pas augmenter les quotas ni les limites suivants, mais vous pouvez appliquer les solutions de contournement ou les bonnes pratiques suggérées pour les atténuer.

Erreurs liées aux limites de file d'attente des requêtes

Si un projet tente de mettre en file d'attente plus de requêtes interactives ou par lot que sa limite de file d'attente le permet, vous pouvez rencontrer cette erreur.

Message d'erreur

Quota exceeded: Your project and region exceeded quota for
max number of jobs that can be queued per project.

Solution

Cette limite ne peut pas être augmentée. Pour résoudre cette erreur de quota, procédez comme suit :

  • Suspendez la tâche. Si vous identifiez un processus ou un pipeline responsable d'une augmentation des requêtes, mettez ce processus ou ce pipeline en pause.

  • Utilisez des tâches avec priorité par lot. Vous pouvez mettre en file d'attente plus de requêtes par lot que de requêtes interactives.

  • Distribuez les requêtes. Organisez et répartissez la charge sur différents projets en fonction de la nature de vos requêtes et des besoins de votre entreprise.

  • Distribuez les temps d'exécution. Répartissez la charge sur une période plus longue. Si votre solution de création de rapports doit exécuter de nombreuses requêtes, essayez d'introduire un caractère aléatoire au démarrage des requêtes. Par exemple, ne démarrez pas tous les rapports en même temps.

  • Utilisez BigQuery BI Engine. Si vous avez rencontré cette erreur lors de l'utilisation d'un outil d'informatique décisionnelle pour créer des tableaux de bord qui interrogent des données dans BigQuery, nous vous recommandons d'utiliser BigQuery BI Engine. L'utilisation de BigQuery BI Engine est optimale pour ce cas d'utilisation.

  • Optimisez les requêtes et le modèle de données. Une requête peut souvent être réécrite afin d'être exécutée plus efficacement. Par exemple, si votre requête contient une expression de table courante (CTE) (clause WITH) référencée à plusieurs endroits de la requête, ce calcul est effectué plusieurs fois. Il est préférable de conserver les calculs effectués par le CTE dans une table temporaire, puis de la référencer dans la requête.

    Les jointures multiples peuvent également être la source d'un manque d'efficacité. Dans ce cas, vous pouvez envisager d'utiliser des colonnes imbriquées et répétées. Bien souvent, cette méthode améliore la localité des données, élimine le besoin de certaines jointures et réduit la consommation des ressources ainsi que le temps d'exécution des requêtes.

    L'optimisation des requêtes les rend moins coûteuses. Ainsi, lorsque vous utilisez la tarification basée sur la capacité, vous pouvez exécuter davantage de requêtes avec vos emplacements. Pour en savoir plus, consultez la page Présentation de l'optimisation des performances des requêtes.

  • Optimisez le modèle de requête. BigQuery n'est pas une base de données relationnelle. Il n'est pas optimisé pour un nombre infini de petites requêtes. L'exécution d'un grand nombre de petites requêtes épuise rapidement vos quotas. Ces requêtes ne s'exécutent pas aussi efficacement qu'avec les produits de base de données plus petits. BigQuery est un grand entrepôt de données et il s'agit de son principal cas d'utilisation. Il fonctionne mieux avec des requêtes analytiques sur de grandes quantités de données.

  • Conservez les données (tables enregistrées). Prétraitez les données dans BigQuery et stockez-les dans des tables supplémentaires. Par exemple, si vous exécutez de nombreuses requêtes similaires qui utilisent beaucoup de ressources de calcul avec différentes conditions WHERE, leurs résultats ne sont pas mis en cache. Ces requêtes consomment également des ressources à chaque exécution. Vous pouvez améliorer les performances de ces requêtes et réduire leur temps de traitement en précalculant les données et en les stockant dans une table. Ces données précalculées dans la table peuvent être interrogées par des requêtes SELECT. Cette opération peut souvent être effectuée lors de l'ingestion dans le processus ETL, ou en utilisant des requêtes programmées ou des vues matérialisées.

  • Utilisez le mode de simulation. Exécutez les requêtes en mode de simulation afin d'estimer le nombre d'octets lus sans avoir à traiter réellement la requête.

  • Prévisualiser les données de la table Pour tester ou explorer les données plutôt que d'exécuter des requêtes, prévisualisez les données de table avec la fonctionnalité d'aperçu de table de BigQuery.

  • Utiliser les résultats d'une requête en cache. Tous les résultats de requête, y compris ceux des requêtes interactives et par lots, sont mis en cache dans des tables temporaires pendant environ 24 heures, à quelques exceptions près. Bien que l'exécution d'une requête mise en cache soit toujours comptabilisée dans votre limite de requêtes simultanées, les requêtes qui utilisent des résultats mis en cache sont considérablement plus rapides que celles qui n'en utilisent pas, car BigQuery n'a pas besoin de calculer l'ensemble de résultats.

Erreurs liées aux limites de taille des opérations de brassage

BigQuery renvoie cette erreur lorsque votre projet dépasse la limite de taille maximale du disque et de la mémoire applicable pour les opérations de brassage.

Ce quota est calculé par réservation et divisé entre les projets pour les réservations. Le quota ne peut pas être modifié par Cloud Customer Care. Pour en savoir plus sur votre utilisation, interrogez la vue INFORMATION_SCHEMA.JOBS_TIMELINE.

Message d'erreur

Un des messages d'erreur suivants s'affiche :

  • Quota exceeded: Your project exceeded quota for total shuffle size limit.
  • Resources exceeded: Your project or organization exceeded the maximum
disk and memory limit available for shuffle operations. Consider provisioning
more slots, reducing query concurrency, or using more efficient logic in this
job.

Solution

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

Erreurs de quota du nombre de modifications de partitions pour les tables partitionnées en colonnes

BigQuery renvoie cette erreur lorsque votre table partitionnée en colonnes atteint le quota du nombre de modifications de partition autorisées par jour. Les modifications de partition incluent le total de toutes les tâches de chargement, des tâches de copie et des tâches de requête qui s'ajoutent à ou écrasent une partition de destination.

Pour afficher la valeur limite du Nombre de modifications de partition par table partitionnée en colonnes par jour, consultez la section Tables partitionnées.

Message d'erreur

Quota exceeded: Your table exceeded quota for
Number of partition modifications to a column partitioned table

Solution

Ce quota ne peut pas être augmenté. Pour résoudre cette erreur de quota, procédez comme suit :

  • Modifiez le partitionnement de la table afin d'obtenir plus de données dans chaque partition et de réduire le nombre total de partitions. Par exemple, passez du partitionnement par jour au partitionnement par mois, ou modifiez le mode de partitionnement de la table.
  • Utilisez le clustering au lieu du partitionnement.
  • Si vous chargez fréquemment des données à partir de plusieurs petits fichiers stockés dans Cloud Storage qui utilisent un job par fichier, combinez plusieurs jobs de chargement en un seul job. Le chargement peut être effectué à partir de plusieurs URI Cloud Storage avec une liste d'éléments séparés par une virgule (par exemple, gs://my_path/file_1,gs://my_path/file_2) ou en utilisant des caractères génériques (par exemple, gs://my_path/*).

    Pour en savoir plus, consultez la section Charger des données par lot.

  • Si vous utilisez des jobs de chargement, de sélection ou de copie pour ajouter des lignes de données individuelles à une table, par exemple, envisagez de regrouper plusieurs jobs en un seul. BigQuery n'est pas très performant lorsqu'il est utilisé comme base de données relationnelle. Nous vous recommandons d'éviter d'exécuter des actions d'ajout fréquentes à une seule ligne.
  • Pour ajouter des données à un taux élevé, envisagez d'utiliser l'API BigQuery Storage Write. Cette solution est recommandée pour une ingestion de données hautes performances. L'API BigQuery Storage Write dispose de fonctionnalités robustes, y compris la sémantique de diffusion de type "exactement une fois". Consultez la section API Storage Write afin d'en savoir plus sur les limites et les quotas, et la section Tarifs d'ingestion de données BigQuery pour connaître les coûts d'utilisation de cette API.
  • Pour surveiller le nombre de partitions modifiées sur une table, utilisez la vue INFORMATION_SCHEMA.

Erreurs de limite du taux maximal d'opérations de mise à jour des métadonnées de table

BigQuery renvoie cette erreur lorsque votre table atteint la limite de taux maximal d'opérations de mise à jour des métadonnées de table par table pour les tables standards. Les opérations de table incluent le total cumulé de toutes les tâches de chargement, tâches de copie et tâches de requête qui s'ajoutent à une table de destination ou l'écrasent, ou qui utilisent une instruction LMD DELETE, INSERT, MERGE, TRUNCATE TABLE ou UPDATE pour écrire des données dans une table.

Pour afficher la valeur limite du Taux maximal d'opérations de mise à jour des métadonnées de table par table, consultez la section Tables standards.

Message d'erreur

Exceeded rate limits: too many table update operations for this table

Lorsque vous rencontrez cette erreur, diagnostiquez le problème, puis suivez les étapes recommandées pour le résoudre.

Diagnostic

Les mises à jour de table de métadonnées peuvent provenir d'appels d'API qui modifient les métadonnées d'une table ou de tâches qui modifient le contenu d'une table. Si vous n'avez pas identifié la source à l'origine de la plupart des opérations de mise à jour des métadonnées d'une table, procédez comme suit :

Identifier les appels d'API

  1. Accédez au menu de navigation Google Cloud , puis sélectionnez Journalisation > Explorateur de journaux :

    Accéder à l'explorateur de journaux

  2. Pour filtrer les journaux afin d'afficher les opérations de la table, exécutez la requête suivante :

    resource.type="bigquery_dataset"
protoPayload.resourceName="projects/my-project-id/datasets/my_dataset/tables/my_table"
(protoPayload.methodName="google.cloud.bigquery.v2.TableService.PatchTable" OR
protoPayload.methodName="google.cloud.bigquery.v2.TableService.UpdateTable" OR
protoPayload.methodName="google.cloud.bigquery.v2.TableService.InsertTable")
Identifier les tâches

La requête suivante renvoie la liste des tâches qui modifient la table concernée dans le projet au cours de la dernière journée. Si vous prévoyez que plusieurs projets dans une organisation écrivent dans la table, remplacez JOBS_BY_PROJECT par JOBS_BY_ORGANIZATION.

SELECT
 job_id,
 user_email,
 query
FROM  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY)
AND destination_table.project_id = "my-project-id"
AND destination_table.dataset_id = "my_dataset"
AND destination_table.table_id = "my_table"

Pour en savoir plus, consultez la présentation des journaux d'audit BigQuery.

Solution

Ce quota ne peut pas être augmenté. Pour résoudre cette erreur de quota, procédez comme suit :

  • Réduisez la fréquence de mise à jour des métadonnées de la table.
  • Ajoutez un délai entre les tâches ou les opérations de table pour vous assurer que le taux de mise à jour est inférieur à la limite.

  • Pour les insertions ou les modifications de données, envisagez d'utiliser des opérations LMD. Les opérations LMD ne sont pas affectées par la limite de taux maximal d'opérations de mise à jour des métadonnées de table par table.

    Les opérations LMD sont soumises à d'autres limites et quotas. Pour en savoir plus, consultez la section Utiliser le langage de manipulation de données (LMD).

  • Si vous chargez fréquemment des données à partir de plusieurs petits fichiers stockés dans Cloud Storage qui utilisent un job par fichier, combinez plusieurs jobs de chargement en un seul job. Le chargement peut être effectué à partir de plusieurs URI Cloud Storage avec une liste d'éléments séparés par une virgule (par exemple, gs://my_path/file_1,gs://my_path/file_2) ou en utilisant des caractères génériques (par exemple, gs://my_path/*).

    Pour en savoir plus, consultez la section Charger des données par lot.

  • Si vous utilisez des jobs de chargement, de sélection ou de copie pour ajouter des lignes de données individuelles à une table, par exemple, envisagez de regrouper plusieurs jobs en un seul. BigQuery n'est pas très performant lorsqu'il est utilisé comme base de données relationnelle. Nous vous recommandons d'éviter d'exécuter des actions d'ajout fréquentes à une seule ligne.
  • Pour ajouter des données à un taux élevé, envisagez d'utiliser l'API BigQuery Storage Write. Cette solution est recommandée pour une ingestion de données hautes performances. L'API BigQuery Storage Write dispose de fonctionnalités robustes, y compris la sémantique de diffusion de type "exactement une fois". Consultez la section API Storage Write afin d'en savoir plus sur les limites et les quotas, et la section Tarifs d'ingestion de données BigQuery pour connaître les coûts d'utilisation de cette API.
  • Pour surveiller le nombre de partitions modifiées sur une table, utilisez la vue INFORMATION_SCHEMA.

Erreurs de quota d'importations de tables ou d'ajouts de requêtes

BigQuery renvoie ce message d'erreur lorsque votre table atteint la limite d'opérations de table par jour pour les tables standards. Les opérations de table incluent le total combiné de toutes les tâches de chargement, tâches de copie et tâches de requête qui ajoutent ou écrasent une table de destination.

Pour afficher la valeur limite du nombre d'Opérations de table par jour, consultez la section Tables standards.

Message d'erreur

Your table exceeded quota for imports or query appends per table

Lorsque vous rencontrez cette erreur, diagnostiquez le problème, puis suivez les étapes recommandées pour le résoudre.

Diagnostic

Si vous n'avez pas identifié la source à l'origine de la plupart des opérations de table, procédez comme suit :

  1. Notez le projet, l'ensemble de données et la table sur lesquels la requête, la charge ou la tâche de copie ayant échoué est en train d'écrire.

  2. Utilisez les tables INFORMATION_SCHEMA.JOBS_BY_* pour en savoir plus sur les tâches qui modifient la table.

    L'exemple suivant recherche le nombre de tâches par heure regroupées par type de tâche sur les dernières 24 heures à l'aide de JOBS_BY_PROJECT. Si vous prévoyez que plusieurs projets écrivent dans la table, remplacez JOBS_BY_PROJECT par JOBS_BY_ORGANIZATION.

    SELECT
TIMESTAMP_TRUNC(creation_time, HOUR),
job_type,
count(1)
FROM `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY)
AND destination_table.project_id = "my-project-id"
AND destination_table.dataset_id = "my_dataset"
AND destination_table.table_id = "my_table"
GROUP BY 1, 2
ORDER BY 1 DESC

Solution

Ce quota ne peut pas être augmenté. Pour résoudre cette erreur de quota, procédez comme suit :

  • Si vous chargez fréquemment des données à partir de plusieurs petits fichiers stockés dans Cloud Storage qui utilisent un job par fichier, combinez plusieurs jobs de chargement en un seul job. Le chargement peut être effectué à partir de plusieurs URI Cloud Storage avec une liste d'éléments séparés par une virgule (par exemple, gs://my_path/file_1,gs://my_path/file_2) ou en utilisant des caractères génériques (par exemple, gs://my_path/*).

    Pour en savoir plus, consultez la section Charger des données par lot.

  • Si vous utilisez des jobs de chargement, de sélection ou de copie pour ajouter des lignes de données individuelles à une table, par exemple, envisagez de regrouper plusieurs jobs en un seul. BigQuery n'est pas très performant lorsqu'il est utilisé comme base de données relationnelle. Nous vous recommandons d'éviter d'exécuter des actions d'ajout fréquentes à une seule ligne.
  • Pour ajouter des données à un taux élevé, envisagez d'utiliser l'API BigQuery Storage Write. Cette solution est recommandée pour une ingestion de données hautes performances. L'API BigQuery Storage Write dispose de fonctionnalités robustes, y compris la sémantique de diffusion de type "exactement une fois". Consultez la section API Storage Write afin d'en savoir plus sur les limites et les quotas, et la section Tarifs d'ingestion de données BigQuery pour connaître les coûts d'utilisation de cette API.
  • Pour surveiller le nombre de partitions modifiées sur une table, utilisez la vue INFORMATION_SCHEMA.

Trop d'instructions LMD en attente sur la table

Cette erreur signifie que le nombre d'instructions LMD en mutation simultanées (UPDATE, DELETE, MERGE) exécutées sur la même table a dépassé. la limite de quota du langage de manipulation de données (LMD). Cette limite de quota s'applique à chaque table et uniquement aux instructions LMD de mutation, qui n'incluent pas INSERT.

Solution

Combinez les jobs LMD en suivant les bonnes pratiques pour les instructions LMD.

Erreurs de quota lors du chargement des fichiers CSV

Si vous chargez un fichier CSV volumineux à l'aide de la commande bq load avec l'option --allow_quoted_newlines, cette erreur peut se produire.

Message d'erreur

Input CSV files are not splittable and at least one of the files is larger than
the maximum allowed size. Size is: ...

Solution

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

  • Définissez l'indicateur --allow_quoted_newlines sur false.
  • Scindez le fichier CSV en fragments plus petits de moins de 4 Go chacun.

Pour en savoir plus sur les limites qui s'appliquent lorsque vous chargez des données dans BigQuery, consultez la section Tâches de chargement.

Votre utilisateur a dépassé le quota de requêtes project.lists simultanées

Cette erreur se produit lorsque des tâches Microsoft Power BI qui communiquent avec BigQuery via un pilote Simba ODBC ou DataHub échouent, car elles dépassent la limite de l'API project.list. Pour résoudre ce problème, utilisez les solutions à court ou long terme décrites dans cette section.

Message d'erreur

Your user exceeded quota for concurrent project.lists requests

Diagnostic

Cette erreur se produit lors de la phase de connexion et de découverte pour Power BI lorsqu'un rapport Power BI est actualisé et que le pilote Simba établit une connexion à un projet BigQuery spécifique.

Résolution à court terme

Pour résoudre ce problème à court terme, utilisez les solutions suivantes, classées de la plus à la moins efficace. Implémentez la correction 3 ou 4, selon que vous vous connectez à BigQuery à l'aide du pilote Simba ou de DataHub.

Pour des solutions à long terme, consultez Solution à long terme.

  1. Échelonner l'actualisation des rapports Si vous ne pouvez pas modifier le DSN, atténuez le problème de quota en réduisant le nombre de requêtes simultanées. Au lieu de faire actualiser tous les rapports en même temps (par exemple, à 9h), échelonnez leurs programmations de quelques minutes (par exemple, à 9h01, 9h03 et 9h05). Cette pratique permet de répartir les appels d'API dans le temps, ce qui réduit le risque d'atteindre la limite simultanée.

  2. Implémentez des nouvelles tentatives dans Power BI. Cette stratégie réactive permet à un rapport de se rétablir après un échec temporaire. Power BI dispose d'une logique de réessai intégrée en cas d'échec de l'actualisation des données. Bien que cette pratique n'empêche pas l'erreur de quota, elle rend votre pipeline plus résilient en permettant à un rapport de réussir lors d'une tentative ultérieure une fois que le pic initial d'appels d'API s'est atténué. Pour appliquer ce correctif :

    1. Dans le service Power BI, accédez aux paramètres de votre ensemble de données.
    2. Développez la section Actualisation programmée. Sous Nouvelle tentative, configurez Power BI pour qu'il réexécute automatiquement une actualisation ayant échoué.

  3. Pour les versions antérieures du pilote Simba, spécifiez l'ID du projet dans la connexion ODBC. Cette action empêche le chauffeur d'effectuer l'appel de découverte projects.list. Au lieu de cela, le pilote se connecte directement au projet spécifié, ce qui évite les appels d'API inutiles et résout le problème de quota.

    Les pilotes plus récents échouent immédiatement si le projet n'est pas spécifié avec un message semblable à Unable to establish connection with data source. Missing settings: {[Catalog]}.

    Pour résoudre ce problème, procédez comme suit :

    1. Sur la machine exécutant la passerelle Power BI ou Power BI Desktop, ouvrez l'application Sources de données ODBC (64 bits).
    2. Sur l'écran de configuration principal du pilote Simba ODBC pour BigQuery, saisissez l'ID de votre projet Google Cloud dans le champ Catalogue (projet) (par exemple, my-gcp-project-id).

  4. Pour les versions antérieures de DataHub, spécifiez l'ID du projet dans la configuration d'ingestion DataHub. Effectuez cette correction si vous utilisez DataHub au lieu du pilote Simba. Comme Simba, les versions ultérieures de DataHub vous obligent à spécifier l'ID du projet pour pouvoir vous connecter à BigQuery.

    Pour éviter de dépasser les limites de DataHub, modifiez votre configuration d'ingestion DataHub afin de fournir une liste explicite des ID de projet à analyser. Cela empêche la configuration DataHub de trouver tous les projets que le compte de service peut voir.

    Dans le fichier de recette de la source BigQuery (généralement un fichier YAML), utilisez la configuration project_ids pour énumérer les projets que vous souhaitez ingérer. Ensuite, redéployez la recette d'ingestion DataHub avec la nouvelle configuration. Consultez l'exemple suivant et cet exemple plus long fourni par DataHub.

    Voici un exemple d'extrait de configuration DataHub :

  source:
  type: "bigquery"
  config:
    # Instead of relying on discovery, explicitly list the projects.
    # This avoids the problematic projects.list() API call.
  project_ids:
    -   "YOUR_PRODUCTION_PROJECT_ID"
    -   "YOUR_ANALYTICS_PROJECT_ID"
    -   "ANOTHER_BQ_PROJECT"

Résolution à long terme

La meilleure solution à long terme pour ce message d'erreur consiste à créer des comptes de serviceGoogle Cloud dédiés et distincts pour chaque fonction. Par exemple, créez un compte de service pour tous les rapports Power BI et un compte de service pour l'ingestion DataHub.

Cette bonne pratique isole l'utilisation de l'API dans des buckets de quota distincts et empêche une tâche à forte charge dans DataHub d'entraîner l'échec des rapports commerciaux critiques dans Power BI.

Utilisez le plan d'action des sections suivantes pour résoudre les erreurs de quota à long terme dans Power BI et DataHub.

Phase 1 : Préparation
  1. Informez les propriétaires des passerelles Power BI et de la configuration DataHub que vous allez apporter des modifications coordonnées pour résoudre les échecs de tâches en cours.
  2. Dans la console Google Cloud , créez deux comptes de service, par exemple sa-powerbi-gateway@... et sa-datahub-ingestion@....
  3. Créez des clés de compte de service pour les comptes de service Power BI et DataHub.
  4. Accordez à chaque nouveau compte de service les autorisations minimales en lui attribuant les rôles Identity and Access Management (IAM) suivants, qui lui permettent d'effectuer ses tâches dans IAM. Évitez d'attribuer des rôles trop larges, par exemple ProjectEditor.
Rôles requis

Le compte de service pour Power BI exécute des requêtes et lit les données des tables. Attribuez les rôles suivants aux comptes de service de chaque projet Google Cloud contenant des données auxquelles Power BI doit accéder. Pour en savoir plus sur ces rôles, consultez Rôles BigQuery.

  • Lecteur de données BigQuery : permet d'accéder en lecture seule aux ensembles de données, aux tables et aux vues.
  • Utilisateur de tâche BigQuery : fournit des autorisations pour exécuter des tâches, y compris des requêtes, ce qui est essentiel pour que Power BI puisse exécuter ses requêtes.

Le compte de service pour l'ingestion DataHub n'a besoin que de lire les métadonnées, telles que les noms de tables, les schémas et les descriptions, et non les données des tables. Attribuez le rôle suivant au niveau du projet pour chaque projet analysé par DataHub. Pour en savoir plus sur ces rôles, consultez Rôles IAM pour BigQuery.

Lecteur de métadonnées BigQuery : ce rôle est spécifiquement conçu pour lire les métadonnées. Il accorde les autorisations nécessaires pour lister les ensembles de données et les tables, et afficher leurs métadonnées sans donner accès aux données sous-jacentes.

Phase 2 : Déploiement coordonné

Pendant une période de faible utilisation, l'administrateur Power BI met à jour les configurations ODBC DSN sur les machines de la passerelle en procédant comme suit :

  1. Modifie la méthode d'authentification pour utiliser la nouvelle clé de compte de service sa-powerbi-gateway@... créée à une étape précédente.
  2. Si vous ne l'avez pas déjà fait en tant que solution à court terme, saisissez l'ID du projet Google Clouddans le champ Catalogue (projet) du pilote ODBC.
  3. Demandez au propriétaire du Data Hub de mettre à jour le fichier YAML de configuration de la source BigQuery.
  4. Indique la nouvelle clé de compte de service sa-datahub-ingestion@... créée à une étape précédente.
  5. Si vous ne l'avez pas déjà fait comme solution à court terme, utilisez le paramètre project_ids pour lister explicitement les projets à analyser.
  6. Redéploie la recette d'ingestion DataHub avec la nouvelle configuration.
Phase 3 : Validation et surveillance

Pour vérifier et surveiller les effets des corrections, les administrateurs Power BI et DataHub procèdent comme suit :

  1. Déclenchez manuellement une actualisation pour quelques rapports Power BI clés et une nouvelle exécution d'ingestion dans DataHub. Vérifiez que ces jobs se terminent correctement sans générer d'erreurs de quota.
  2. Dans la console Google Cloud , accédez à la page IAM et administration> Quotas.
  3. Filtrez le service API BigQuery.
  4. Recherchez le quota Requêtes project.lists simultanées, puis cliquez sur l'icône du graphique pour afficher l'utilisation au fil du temps.

Les administrateurs devraient constater une baisse spectaculaire et permanente de l'utilisation de cet appel d'API spécifique, ce qui confirmerait que la correction a fonctionné.