Fiabilité : interroger des données

Ce document explique comment créer des solutions fiables avec BigQuery et comment interroger des données de manière fiable dans votre environnement BigQuery.

Comprendre les emplacements

Lorsque BigQuery exécute une tâche de requête, il convertit l'instruction SQL déclarative envoyée en un graphe d'exécution divisé en une série de phases de requête elles-mêmes composées d'ensembles d'étapes d'exécution plus précis. BigQuery exploite une architecture parallèle fortement distribuée pour exécuter ces requêtes, et les phases modélisent les unités de travail que de nombreux nœuds de calcul potentiels peuvent exécuter en parallèle. Les ressources de ces nœuds de calcul sont appelés des emplacements.

Pour simplifier, un emplacement BigQuery est un processeur virtuel utilisé par BigQuery pour exécuter des requêtes SQL. Pour en savoir plus sur les emplacements et leur fonctionnement, consultez la page Comprendre les emplacements.

BigQuery est en mesure d'obtenir un contrat de niveau de service garantissant une disponibilité de 99,99 % pour les requêtes, en fournissant une capacité d'emplacements redondante dans deux zones distinctes. Cette distribution protège les clients des défaillances de zones.

Les ressources de calcul utilisées pour exécuter des requêtes sont acquises selon un modèle de tarification à la demande ou forfaitaire. Dans le modèle à la demande, les ressources de calcul sont facturées en fonction du nombre d'octets traités par les requêtes soumises. Dans le cadre du modèle forfaitaire, vous achetez une quantité dédiée de capacité de traitement de requêtes, ce qui permet d'obtenir un modèle de coût stable.

Modèle d'analyse à la demande

Avec le modèle de tarification à la demande de BigQuery, vous êtes facturé uniquement en fonction de la quantité d'octets traités par les requêtes que vous envoyez (on parle également "d'octets lus"). Les projets qui utilisent le modèle de tarification à la demande de BigQuery ont accès à une capacité d'emplacements par défaut généralement plus que suffisante et soumise aux Quotas d'emplacements par projet.

Modèle d'emplacement à tarif forfaitaire

Les clients qui préfèrent un coût fixe pour les requêtes plutôt qu'un modèle de tarification à la demande peuvent envisager d'utiliser le modèle de tarification forfaitaire. Lorsque vous souscrivez à la tarification forfaitaire, vous achetez une capacité dédiée pour le traitement de requêtes et mesurée en emplacements BigQuery. Vos requêtes utilisent cette capacité, mais les octets traités ne vous sont pas facturés.

La structure forfaitaire permet aux clients d'acheter des emplacements à différentes durées d'engagement (annuel, mensuel ou sans engagement), de hiérarchiser les emplacements en attribuant des réservations à des projets ou dossiers spécifiques, et de partager les emplacements inactifs entre plusieurs réservations. Pour en savoir plus, consultez la page Présentation des réservations.

Le modèle forfaitaire définit explicitement un nombre maximal de ressources d'emplacements disponibles pour vos dossiers ou projets. Par conséquent, l'achat d'un trop petit nombre d'emplacements peut avoir un impact sur les performances de vos requêtes, tandis que l'achat d'un trop grand nombre d'emplacements peut entraîner l'inactivité d'une partie de la capacité, ce qui génère une sous-utilisation des ressources et des coûts inutiles.

Considérations relatives aux modèles d'emplacement à tarif forfaitaire

Bien que le coût soit souvent le principal facteur déterminant le choix du modèle à la demande et du modèle forfaitaire, cette décision peut également affecter la fiabilité. Par exemple, l'évolutivité peut différer entre le modèle de tarification à la demande qui fournit une base de 2000 emplacements disponibles par projet lors des pics de charge, et le modèle forfaitaire qui peut être configuré avec jusqu'à plusieurs centaines de milliers d'emplacements. Le nombre d'emplacements consommés par votre projet dépend de la complexité des requêtes, de la quantité de données analysées, de l'utilisation de fonctions spéciales telles que les fonctions définies par l'utilisateur et du nombre de requêtes simultanées envoyées. Toutefois, les 2000 emplacements disponibles pour les clients à la demande sont généralement plus que suffisants pour les utilisateurs qui commencent seulement à utiliser BigQuery. Pour en savoir plus sur le choix entre le modèle à la demande et le modèle forfaitaire pour BigQuery, consultez la page Choisir entre le tarif à la demande et le tarif forfaitaire pour BigQuery.

La disponibilité de BigQuery à 99,99 % dans le cadre du contrat de niveau de service s'applique aussi bien à la tarification à la demande qu'à la tarification forfaitaire. Cependant, le nombre de ressources d'emplacements n'est garanti que pour le modèle forfaitaire car le modèle à la demande utilise un pool partagé de ressources d'emplacements. Notez que BigQuery ne garantit aucune disponibilité en matière d'emplacements réservés tant que les emplacements ne sont pas achetés et provisionnés. Pour cette raison, Google déconseille d'acheter des emplacements réservés au dernier moment pour les charges de travail critiques. Plus la requête d'emplacement est grande, plus il est probable que des délais de livraison soient nécessaires pour provisionner les ressources d'emplacements.

Il est également possible de combiner les modèles à la demande et forfaitaire au sein de votre organisation ou de votre projet afin de répondre au mieux à vos besoins. Cela est possible grâce au concept de BigQuery Reservations dans BigQuery. Par exemple, vous pouvez utiliser une réservation à taux fixe pour un ensemble de données multirégional de production aux États-Unis et une capacité à la demande pour un ensemble de données de développement plus petit basé dans une autre région.

Réservations

Les réservations BigQuery vous permettent de personnaliser les allocations d'emplacements dans votre organisation et de les hiérarchiser en fonction de vos charges de travail. Par exemple, vous pouvez créer une réservation appelée prod pour les charges de travail de production, et une réservation distincte appelée test pour les tests. Ainsi, vos tâches de test ne consomment pas les ressources dont vos charges de travail de production ont besoin. Une réservation spécifie la priorité des ressources d'emplacements sur un projet, une organisation ou un dossier spécifié. Chaque niveau de la hiérarchie des ressources hérite de l'attribution du niveau supérieur, à moins que cette attribution ne soit remplacée. En d'autres termes, un projet hérite de l'attribution de son dossier parent, et un dossier hérite de l'attribution de son organisation.

Par défaut, les réservations BigQuery optimisent l'utilisation des ressources en partageant les ressources d'emplacements inactifs entre d'autres réservations. Cela permet aux charges de travail non prioritaires, telles que les environnements de développement et de test, de bénéficier de meilleures performances de requêtes pendant les périodes où les réservations de production ne sont pas pleinement utilisées. Dans le cas où une charge de travail de développement/test consomme les emplacements inutilisés d'une réservation de production et que la réservation de production devient entièrement utilisée, le planificateur d'emplacements BigQuery récupère intelligemment les ressources d'emplacements des requêtes de développement/test en cours pour les mettre à disposition de la réservation de production. Les requêtes de développement/test continueront de s'exécuter, mais plus lentement car elles disposent de moins de ressources de calcul.

En fonction de la nature de vos besoins d'analyse, il est souvent judicieux de créer plusieurs réservations BigQuery dans votre environnement de manière à séparer les charges de travail en fonction de l'unité commerciale, de la fonction (production ou développement/test) ou du type d'application (tableau de bord d'informatique décisionnelle, requêtes/tâches planifiées ou requêtes ad hoc émises par des utilisateurs).

Notez que les emplacements inactifs ne sont partagés qu'entre les réservations créées dans un même projet d'administration BigQuery.

Dimensionner une réservation

Pour redimensionner de manière précise une réservation, il est préférable de surveiller l'utilisation actuelle et historique des emplacements BigQuery via les graphiques de ressources d'administration BigQuery ou le tableau de bord Cloud Monitoring Les graphiques des ressources d'administration fournissent des données en temps réel ainsi que des données historiques des 14 derniers jours, avec des informations spécifiques sur l'utilisation des emplacements, la simultanéité des tâches et les performances des tâches au niveau de l'organisation, du dossier, du projet, de la réservation, de l'utilisateur ou de la tâche.

Optimiser la fiabilité des tâches

Vous pouvez soumettre deux types de requêtes pour analyser des données :

  • Requêtes interactives : une requête interactive soumise est immédiatement envoyée pour exécution.
  • Requêtes par lot : une requête par lot soumise est placée en file d'attente puis démarrée dès qu'un nombre suffisant de ressources d'emplacements est disponible. Si BigQuery n'a pas lancé la requête dans un délai de 24 heures, il redéfinit la priorité de la tâche sur "interactive" et la distribue immédiatement.

Les requêtes interactives et les requêtes par lot utilisent les mêmes ressources d'emplacements. En fait, après le démarrage d'une requête par lot par le programmeur BigQuery, il n'y a aucune différence de priorité entre une requête interactive ou par lot. Cependant, les requêtes interactives et par lot affectent différemment les limites et les quotas de requêtes. Contrairement aux requêtes interactives, les requêtes par lot ne sont pas comptabilisées.

Si la limite de requêtes simultanées est atteinte, les autres requêtes interactives échouent avec une erreur quotaExceeded. Ce quota est en place pour votre protection. Tant que les emplacements disponibles ne sont pas entièrement utilisés, vous pouvez généralement augmenter ce quota en contactant le Service client ou le Service commercial. Toutefois, un degré élevé de simultanéité des requêtes réduit la disponibilité globale des ressources de calcul pour chaque requête, ce qui peut entraîner un conflit de ressources et dégrader le débit de traitement par requête. Par conséquent, vous ne devez pas augmenter le quota de requêtes simultanées au-delà du point de saturation des ressources d'emplacements. Si des erreurs de requête simultanées se produisent, vous pouvez réessayer en appliquant un intervalle exponentiel entre les tentatives afin d'éviter tout impact négatif sur la latence des tâches de requête.

Les options suivantes permettent de réduire le nombre de requêtes simultanées dans votre environnement :

  • 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.
  • Lorsque vous expérimentez avec ou explorez des 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 des résultats de requête mis 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 accélérées car BigQuery n'a pas besoin de calculer l'ensemble de résultats.
  • Utilisez BI Engine, le service d'analyse en mémoire rapide de BigQuery. BI Engine vous permet de créer des tableaux de bord et des rapports interactifs avec des outils tels que Google Data Studio et l'interface SQL. De plus, il offre une simultanéité améliorée pour les données stockées dans BigQuery. Cette méthode est particulièrement efficace pour les grands nombres de petites requêtes.

Les autres considérations relatives à la simultanéité des requêtes et aux performances des tâches sont les tâches d'isolation et de répartition. Le fait d'isoler les tâches de requête par cas d'utilisation dans plusieurs projets ou réservations permet de réduire les problèmes de voisinage, par exemple lorsqu'une requête consomme une grande quantité de ressources et impacte négativement les performances des autres requêtes. Cela réduit également le risque d'atteindre les limites de requêtes simultanées par projet.

La répartition des tâches ou l'envoi de plusieurs tâches en séquence plutôt que simultanément peut également améliorer les performances globales et réduire les taux d'erreur. Prenons comme exemple un ensemble de cinq tâches complexes. Supposons que l'exécution simultanée de ces cinq requêtes consomme un grand nombre d'emplacements et se termine en une heure. L'envoi des cinq mêmes tâches en séquence prend toujours une heure mais libère des ressources d'emplacements qui peuvent être utilisées par d'autres requêtes, ce qui réduit le risque d'atteindre des limites de requête simultanées.

BigQuery utilise la planification équitable pour allouer les ressources entre les requêtes concurrentes au sein d'un projet ou d'une réservation. Cela signifie que chaque requête dispose d'un accès identique à tous les emplacements disponibles à tout moment, et que la capacité est réaffectée de manière dynamique et automatique aux requêtes actives, au fil de l'évolution de leurs besoins. Pour cette raison, l'exécution de requêtes de production critiques et de requêtes de développement/test dans un même projet ou une même réservation peut ne pas s'avérer idéale, car chaque requête dispose d'un accès identique aux emplacements. Les requêtes non critiques peuvent donc affecter les performances des requêtes critiques en cours. Il est recommandé de hiérarchiser les requêtes avant de les exécuter.

Considérations relatives au LMD pour l'optimisation de la fiabilité

Les instructions LMD (langage de manipulation de données) BigQuery vous permettent de mettre à jour, d'insérer et de supprimer des données dans vos tables BigQuery, et bénéficient de leurs propres considérations de fiabilité.

  • BigQuery étant entièrement atomique, chaque instruction LMD initie une transaction implicite, ce qui signifie que les modifications apportées par l'instruction sont automatiquement validées à la fin de chaque instruction LMD réussie.
  • Les instructions LMD UPDATE, DELETE ou MERGE ne peuvent pas être utilisées sur une table avec un tampon de streaming actif. Toute tentative d'utilisation de ces instructions entraîne l'échec de la requête.
  • Les instructions LMD INSERT, UPDATE, DELETE et MERGE n'ont pas de limites de quota simultanées. Cependant, lorsqu'un certain seuil de tâches simultanées est atteint, les nouvelles tâches sont mises en file d'attente avec un état en attente.
  • En cas d'exécution simultanée d'instructions LMD de mutation sur la même table, BigQuery détermine si les instructions LMD peuvent muter les mêmes fichiers de backend et n'autorise qu'une seule instruction LMD à s'exécuter, après quoi il retente d'exécuter les autres instructions jusqu'à trois fois. Si les trois tentatives d'exécution de l'instruction LMD échouent, cela est dû à des conflits dans les modifications que l'instruction tente d'effectuer.
  • Bien que BigQuery soit entièrement compatible avec les opérations LMD UPDATE, DELETE et MERGE, il n'est pas conçu pour être une base de données transactionnelle de type OLTP.

Une bonne pratique consiste à éviter d'envoyer un trop grand nombre de lignes LMD individuelles pour des mises à jour et des insertions. Au lieu de cela, regroupez les opérations LMD lorsque cela est possible.

Considérations relatives aux fonctions définies par l'utilisateur pour l'optimisation de la fiabilité

Les fonctions définies par l'utilisateur BigQuery sont des fonctions persistantes ou temporaires créées à partir d'une expression SQL ou d'un code JavaScript et qui renvoient des résultats basés sur l'exécution du code fourni. Les fonctions définies par l'utilisateur ont leurs propres considérations de fiabilité.

  • Les fonctions définies par l'utilisateur consomment généralement plus de ressources d'emplacements que les requêtes SQL standards, ce qui peut avoir un impact sur les performances des tâches. Si la fonction peut être exprimée en SQL, il est souvent plus optimal d'exécuter le code en tant que tâche de requête SQL standard.
  • Une fonction JavaScript définie par l'utilisateur peut arriver à expiration et empêcher les requêtes de se terminer. Les délais avant expiration peuvent ne durer que 5 minutes, mais ils varient en fonction de plusieurs facteurs, y compris le temps CPU utilisateur que consomme votre fonction et la taille de vos entrées et sorties par rapport à la fonction JavaScript.
  • Les fonctions définies par l'utilisateur sont soumises à leurs propres quotas et limites de requêtes simultanées. Pour en savoir plus, consultez la section Limites relatives aux fonctions définies par l'utilisateur.

Gérer les quotas et les limites

Comme indiqué précédemment, les tâches de requête sont soumises à un certain nombre de limites et quotas. Ces limites varient en fonction du type de requête ainsi que d'autres facteurs, mais la durée maximale d'exécution d'une requête ou d'un script est de 6 heures. Cette limite ne peut pas être modifiée. Dans certains cas, il est possible de relancer une requête. Celle-ci pourra alors être exécutée pendant six heures supplémentaires et relancée jusqu'à trois fois.

Surveiller les tâches de requête

La surveillance des tâches BigQuery est essentielle pour exécuter des applications fiables. Vous pouvez effectuer la surveillance en utilisant les Graphiques des ressources d'administration BigQuery, les Tableaux de bord Cloud Monitoring ou les Tables Information_Schema de BigQuery.
Les graphiques des ressources d'administration BigQuery et les tableaux de bord Cloud Monitoring permettent de surveiller de manière native les octets interrogés, le nombre de requêtes simultanées exécutées, la consommation d'emplacements, la latence des requêtes, etc… Bien que les tables INFORMATION_SCHEMA fournissent des métadonnées supplémentaires concernant les types de tâches, les messages d'erreur spécifiques, les taux de cache, la complexité des tâches, et autres… l'utilisation de ces tables INFORMATION_SCHEMA fournit des couches de personnalisation supplémentaires, comme avec la requête ci-dessous qui fournit la liste des requêtes actuelles en attente ou en cours d'exécution, classées selon le temps écoulé depuis leur création :

SELECT
    creation_time,
    project_id,
    user_email,
    job_id,
    job_type,
    priority,
    state,
    TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), start_time,second) as running_time_sec
 FROM
   region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
    creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND CURRENT_TIMESTAMP()
    AND state != "DONE"
ORDER BY
    running_time_sec DESC

Cas d'utilisation pour les requêtes de données

Analyse en temps réel

Dans ce cas d'utilisation, les données en streaming sont utilisées pour alimenter un tableau de bord d'informatique décisionnelle en temps réel utilisé par un grand nombre d'utilisateurs qui exécutent des requêtes interactives.

Dans ce cas d'utilisation, vous devez isoler les requêtes du tableau de bord d'informatique décisionnelle des autres requêtes à usage général en utilisant des projets ou des réservations distincts afin d'éviter que le tableau de bord d'informatique décisionnelle n'entraîne des problèmes de voisinage susceptibles d'affecter les performances des autres tâches de requête d'usage général. En outre, vous devez surveiller les requêtes en cours, les requêtes coûteuses et les requêtes SQL présentant des anti-modèles BigQuery en consultant les tables INFORMATION_SCHEMA, puis corriger ces requêtes pour éviter les dépenses inutiles et réduire la latence des requêtes.

Pensez à exploiter BI Engine si un nombre élevé de tâches de requêtes simultanées est envoyé au tableau de bord de veille stratégique ou si la latence des requêtes est problématique.

Envisagez de créer des limites de contrôle des coûts personnalisés pour limiter la quantité de données pouvant être traitées par jour et par utilisateur, ou par table.

Traitement de données par lot

Dans ce cas d'utilisation, des tâches par lot complexes quotidiennes sont traitées afin d'effectuer une analyse approfondie des données de la journée entière et sont jointes à d'autres sources de données afin d'enrichir les données.

De la même manière que pour les cas d'utilisation en temps réel, il est recommandé d'isoler les requêtes complexes par lot des autres requêtes à usage général en utilisant des projets ou des réservations distincts afin d'éviter les problèmes de voisinage susceptibles d'affecter les performances des autres tâches de requête. En outre, les requêtes de longue durée en cours, les requêtes coûteuses et les requêtes SQL présentant des anti-modèles BigQuery doivent être surveillées à l'aide des tables INFORMATION_SCHEMA puis corrigées pour éviter les dépenses inutiles et/ou réduire la latence des requêtes.

Envisagez de créer des alertes dans Cloud Monitoring pour recevoir des alertes en cas de faible disponibilité des emplacements, de temps de requêtes élevés ou de nombre élevé d'octets analysés afin d'être averti des problèmes de performance des tâches.

Étapes suivantes