Présentation du chargement des données

Ce document vous offre un aperçu du chargement des données dans BigQuery.

Méthodes de chargement des données

Vous pouvez charger (ou ingérer) des données dans BigQuery de plusieurs manières :

  • Chargez un ensemble d'enregistrements de données par lots.
  • Diffuser des enregistrements individuels ou des lots d'enregistrements.
  • Utilisez des requêtes pour générer de nouvelles données et ajouter ou écraser les résultats dans une table.
  • Utiliser une application ou un service tiers

Chargement par lot

Avec le chargement par lot, vous chargez les données sources dans une table BigQuery en une seule opération par lot. Par exemple, la source de données peut être un fichier CSV, une base de données externe ou un ensemble de fichiers journaux. Les tâches traditionnelles d'extraction, de transformation et de chargement (ETL) entrent dans cette catégorie.

Les options de chargement par lots dans BigQuery sont les suivantes :

  • Tâches de chargement Chargez des données à partir de Cloud Storage ou d'un fichier local en créant une tâche de chargement. Les enregistrements peuvent être au format Avro, CSV, JSON, ORC ou Parquet.
  • SQL. L'instruction SQL LOAD DATA charge les données d'un ou de plusieurs fichiers dans une table nouvelle ou existante. Vous pouvez utiliser l'instruction LOAD DATA pour charger des fichiers Avro, CSV, JSON, ORC ou Parquet.
  • Service de transfert de données BigQuery Utiliser le service de transfert de données BigQuery pour automatiser le chargement de données depuis des applications SaaS (Software as a Service) de Google ou depuis des applications et services tiers.
  • API BigQuery Storage Write L'API Storage Write vous permet de traiter par lot un nombre arbitraire d'enregistrements et de les valider en une seule opération atomique. Si l'opération de commit échoue, vous pouvez retenter l'opération en toute sécurité. Contrairement aux tâches de chargement BigQuery, l'API Storage Write ne nécessite pas la préproduction des données dans un espace de stockage intermédiaire, tel que Cloud Storage.
  • Autres services gérés Utilisez d'autres services gérés pour exporter les données d'un datastore externe et les importer dans BigQuery. Par exemple, vous pouvez charger des données à partir d'exportations Firestore.

Lorsque vous choisissez une méthode de chargement par lot, la plupart des modèles basés sur des fichiers doivent utiliser une tâche de chargement ou une instruction SQL LOAD DATA pour charger des données par lot. Les autres services doivent généralement utiliser le service de transfert de données BigQuery ou exporter des données à partir des services Google.

Le chargement par lots peut être effectué une seule fois ou selon un calendrier récurrent. Par exemple, vous pouvez effectuer les opérations suivantes :

  • Vous pouvez exécuter des transferts du service de transfert de données BigQuery de manière planifiée.
  • Vous pouvez utiliser un service d'orchestration tel que Cloud Composer pour planifier des tâches de chargement.
  • Vous pouvez utiliser une tâche Cron pour charger des données de manière planifiée.

Streaming

Avec le streaming, vous envoyez continuellement de petits lots de données en temps réel afin que les données soient disponibles pour l'interrogation dès qu'elles arrivent. Les options de streaming dans BigQuery sont les suivantes :

  • API Storage Write L'API Storage Write est compatible avec l'ingestion de flux haut débit avec une sémantique de diffusion de type "exactement une fois".
  • Dataflow Utilisez Dataflow avec le SDK Apache Beam pour configurer un pipeline de streaming qui écrit dans BigQuery. Pour en savoir plus, consultez la page Connecteur d'E/S BigQuery dans la documentation Apache Beam et le tutoriel sur la diffusion de données en streaming depuis Pub/Sub vers BigQuery.
  • Datastream. Datastream utilise la fonctionnalité de capture de données modifiées de BigQuery et l'API Storage Write pour répliquer les mises à jour de données et de schémas directement à partir de bases de données opérationnelles dans BigQuery. Suivez ce guide de démarrage rapide pour obtenir un exemple de réplication d'une base de données Cloud SQL pour PostgreSQL dans BigQuery.
  • Connecteur BigQuery pour SAP Le connecteur BigQuery pour SAP permet une réplication en quasi-temps réel des données SAP directement dans BigQuery. Pour en savoir plus, consultez le guide de planification du connecteur BigQuery pour SAP.
  • Pub/Sub. Pub/Sub est un service de messagerie que vous pouvez utiliser pour coordonner les pipelines d'analyse de flux et d'intégration de données. Vous pouvez utiliser les abonnements BigQuery pour écrire des messages directement dans une table BigQuery existante.

Données générées

Vous pouvez utiliser SQL pour générer des données et stocker les résultats dans BigQuery. Les options de génération de données sont les suivantes :

  • Utiliser des instructions LMD (langage de manipulation de données) pour effectuer des insertions groupées dans une table existante ou pour stocker les résultats de requête dans une nouvelle table.

  • Utiliser une instruction CREATE TABLE ... AS pour créer une table à partir d'un résultat de requête.

  • Exécuter une requête et enregistrer les résultats dans une table. Vous pouvez ajouter les résultats à une table existante ou écrire dans une nouvelle table. Pour en savoir plus, consultez la section Écrire des résultats de requête.

Applications tierces

Certains services et applications tiers fournissent des connecteurs permettant d'ingérer des données dans BigQuery. Les informations sur la configuration et la gestion du pipeline d'ingestion dépendent de l'application. Par exemple, pour charger des données provenant de sources externes dans l'espace de stockage BigQuery, vous pouvez utiliser Informatica Data Loader ou Fivetran Data Pipelines. Pour en savoir plus, consultez la page Charger des données à l'aide d'une application tierce.

Choisir une méthode d'ingestion de données

Voici quelques éléments à prendre en compte lorsque vous choisissez une méthode d'ingestion de données.

Source des données La source des données ou le format de données peut déterminer si le chargement par lot ou en flux est plus simple à mettre en œuvre et à gérer. Tenez compte des points suivants :

  • Si le service de transfert de données BigQuery est compatible avec la source de données, le transfert de données directement dans BigQuery peut être la solution la plus simple à mettre en œuvre.

  • Pour les données provenant de sources tierces non compatibles avec le service de transfert de données BigQuery, transformez les données dans un format compatible avec le chargement par lot et utilisez cette méthode à la place.

  • Si vos données proviennent de Spark ou de Hadoop, pensez à utiliser les connecteurs BigQuery pour simplifier l'ingestion de données.

  • Pour les fichiers locaux, envisagez les tâches de chargement par lot, en particulier si BigQuery est compatible avec le format de fichier sans nécessiter une étape de transformation ou de nettoyage des données.

  • Pour les données d'application, telles que les événements d'application ou un flux de journal, il peut être plus facile de diffuser les données en temps réel plutôt que d'implémenter le chargement par lots.

Données à évolution lente et à évolution rapide. Si vous devez ingérer et analyser des données quasiment en temps réel, envisagez de les diffuser en flux continu. Avec le streaming, les données peuvent être interrogées dès que chaque enregistrement est reçu. Évitez d'utiliser des instructions LMD pour envoyer un grand nombre de mises à jour ou d'insertions de lignes individuelles. Pour les données fréquemment mises à jour, il est souvent préférable de diffuser un journal des modifications et d'utiliser une vue pour obtenir les derniers résultats. Une autre option consiste à utiliser Cloud SQL comme base de données de traitement des transactions en ligne (OLTP), et à utiliser des requêtes fédérées pour joindre les données dans BigQuery.

Si vos données sources changent lentement ou si vous n'avez pas besoin de résultats mis à jour en continu, envisagez d'utiliser une tâche de chargement. Par exemple, si vous utilisez les données pour générer un rapport quotidien ou horaire, les tâches de chargement peuvent être moins coûteuses et utiliser moins de ressources système.

Un autre scénario est celui de données qui arrivent de manière peu fréquente ou en réponse à un événement. Dans ce cas, envisagez d'utiliser Dataflow pour diffuser les données ou d'utiliser Cloud Run Functions pour appeler l'API de streaming en réponse à un déclencheur.

Fiabilité de la solution. BigQuery est associé à un contrat de niveau de service. Cependant, vous devez également prendre en compte la fiabilité de la solution que vous mettez en œuvre. Tenez compte des points suivants :

  • Avec des formats moins rigoureux tels que JSON ou CSV, les données incorrectes peuvent faire échouer l'ensemble de la tâche de chargement. Déterminez si vous avez besoin d'une étape de nettoyage des données avant le chargement, et réfléchissez à la résolution des erreurs. Utilisez également un type de format plus rigoureux , tel que Avro, ORC ou Parquet.
  • Les tâches de chargement périodiques nécessitent une planification, à l'aide de Cloud Composer, de Cron ou d'un autre outil. Le composant de planification peut constituer un point de défaillance dans la solution.
  • Avec le streaming, vous pouvez vérifier la réussite de chaque enregistrement et signaler rapidement une erreur. Envisagez d'écrire des messages en échec dans une file d'attente de messages non traités pour une analyse et un traitement ultérieurs. Pour en savoir plus sur les erreurs de streaming BigQuery, consultez la section Dépannage pour les insertions en flux continu.
  • Les tâches de streaming et de chargement sont soumises à des quotas. Pour en savoir plus sur la gestion des erreurs de quota, consultez la page Dépannage des erreurs de quota BigQuery.
  • Les solutions tierces peuvent différer en matière de configurabilité, de fiabilité, de garantie de classement et d'autres facteurs. Nous vous recommandons donc de les prendre en compte avant d'adopter une solution.

Latence. Réfléchissez à la quantité de données que vous chargez et à la date à laquelle elles doivent être disponibles. Le streaming offre la plus faible latence des données disponibles pour l'analyse. Les tâches de chargement périodiques ont une latence plus élevée, car les nouvelles données ne sont disponibles qu'à la fin de chaque tâche de chargement.

Les tâches de chargement utilisent un pool d'emplacements partagé par défaut. Une tâche de chargement peut attendre un état en attente jusqu'à ce que des emplacements soient disponibles, en particulier si vous chargez une très grande quantité de données. Si cela crée des temps d'attente inacceptables, vous pouvez acheter des emplacements dédiés au lieu d'utiliser le pool d'emplacements partagé. Pour plus d'informations, consultez la page Présentation des réservations.

Les performances des requêtes pour des sources de données externes peuvent être inférieures aux performances des requêtes sur les données stockées dans BigQuery. Si la réduction de la latence des requêtes est importante, nous vous recommandons de charger les données dans BigQuery.

Format de l'ingestion de données. Choisissez un format d'ingestion de données en fonction des facteurs suivants :

  • Compatibilité du schéma Les exportations Avro, ORC, Parquet et Firestore sont des formats autodescriptifs. BigQuery crée automatiquement le schéma de la table en fonction des données sources. Pour les données JSON et CSV, vous pouvez fournir un schéma explicite ou utiliser la détection automatique de schéma.

  • Données plates ou champs imbriqués et répétés Les formats Avro, CSV, JSON, ORC et Parquet sont tous compatibles avec les données plates. Les exportations aux formats Avro, JSON, ORC, Parquet et Firestore sont également compatibles avec les données comprenant des champs imbriqués et répétés. Les données imbriquées et répétées sont utiles pour exprimer des données hiérarchiques. Les champs imbriqués et répétés permettent également de réduire la duplication des données lors du chargement des données.

  • Les nouvelles lignes intégrées Lorsque vous chargez des données à partir de fichiers JSON, les lignes doivent être délimitées par un retour à la ligne. BigQuery s'attend à ce que les fichiers JSON délimités par un retour à ligne ne contiennent qu'un seul enregistrement par ligne.

  • Encodage BigQuery est compatible avec l'encodage UTF-8 pour les données imbriquées ou répétées, ainsi que pour les données plates. BigQuery est compatible avec l'encodage ISO-8859-1 pour les données plates de fichiers CSV exclusivement.

Charger des données imbriquées et répétées

Vous pouvez charger des données dans des champs imbriqués et répétés dans les formats de données suivants :

  • Avro
  • JSON (délimité par un retour à la ligne)
  • ORC
  • Parquet
  • Exportations Cloud Datastore
  • Exportations Firestore

Pour savoir comment spécifier des champs imbriqués et répétés dans votre schéma lorsque vous chargez des données, consultez la section Spécifier des champs imbriqués et répétés.

Charger des données depuis d'autres services Google

Certains services Google exportent des données vers BigQuery à l'aide de requêtes, d'exportations ou de transferts planifiés. Pour en savoir plus sur les services compatibles avec les exportations vers BigQuery, consultez la section Charger des données à partir de services Google.

D'autres services Google sont compatibles avec les exportations de données lancées à partir du service de transfert de données BigQuery. Pour plus d'informations sur les services compatibles avec les exportations lancées par le service de transfert de données BigQuery, consultez la page Service de transfert de données BigQuery.

Quotas

Pour en savoir plus sur les quotas, consultez les sections suivantes :

Alternatives au chargement de données

Vous n'avez pas besoin de charger des données avant d'exécuter des requêtes dans les situations suivantes :

Ensembles de données publics
Les ensembles de données publics sont des ensembles de données stockés dans BigQuery et partagés avec le public. Pour plus d'informations, consultez Ensembles de données publics BigQuery.
Ensembles de données partagés
Vous pouvez partager des ensembles de données stockés dans BigQuery. Si quelqu'un a partagé un ensemble de données avec vous, vous pouvez exécuter des requêtes sur cet ensemble sans charger les données.
Sources de données externes
BigQuery peut exécuter des requêtes sur certaines formes de données externes, sans charger les données dans le stockage BigQuery. Cette approche vous permet de bénéficier des fonctionnalités analytiques de BigQuery sans déplacer les données stockées ailleurs. Pour en savoir plus sur les avantages et les limites de cette approche, consultez la section Sources de données externes.
Fichiers journaux Logging
Cloud Logging fournit une option pour exporter les fichiers journaux dans BigQuery. Pour en savoir plus, consultez la page Configurer et gérer les récepteurs.

Surveiller l'utilisation des jobs de chargement

Vous pouvez surveiller l'utilisation des tâches de chargement de deux manières :

  • Utilisez Cloud Monitoring. Pour en savoir plus, consultez la page Métriques BigQuery. Plus spécifiquement, vous pouvez surveiller la quantité de données et le nombre de lignes importées dans une table spécifique. Si vos tâches de chargement importent des données dans une table spécifique, vous pouvez utiliser cette table comme proxy pour surveiller l'utilisation de données des tâches de chargement d'importation.

  • Utilisez INFORMATION_SCHEMA.JOBS_BY_PROJECT. Vous pouvez utiliser la vue INFORMATION_SCHEMA.JOBS_BY_PROJECT pour obtenir le nombre de tâches de chargement par table et par jour.

Exemple d'utilisation

Les exemples suivants expliquent les méthodes à utiliser en fonction de votre cas d'utilisation et comment les utiliser avec d'autres solutions d'analyse de données.

Diffuser des données à l'aide de l'API Storage Write

Supposons qu'un pipeline traite des données d'événement à partir des journaux de points de terminaison. Les événements sont générés en continu et doivent être disponibles dès que possible pour des requêtes dans BigQuery. La fraîcheur des données étant primordiale pour ce cas d'utilisation, l'API Storage Write est la meilleure option pour ingérer les données dans BigQuery. Une architecture recommandée pour ne pas surcharger ces points de terminaison consiste à envoyer les événements vers Pub/Sub afin qu'ils soient consommés par un pipeline Dataflow de traitement par flux qui les diffuse directement vers BigQuery.

La principale préoccupation de fiabilité pour cette architecture est la gestion des échecs d'insertion d'un enregistrement dans BigQuery. Si chaque enregistrement est important et ne peut pas être perdu, les données doivent être mises en mémoire tampon avant d'essayer de les insérer. Dans l'architecture recommandée ci-dessus, Pub/Sub peut jouer le rôle d'un tampon avec ses fonctionnalités de conservation des messages. Le pipeline Dataflow doit être configuré de manière à relancer les insertions par flux BigQuery avec un intervalle exponentiel tronqué entre les tentatives. Une fois la capacité de Pub/Sub en tant que mémoire tampon épuisée, par exemple en cas d'indisponibilité prolongée de BigQuery ou de défaillance du réseau, les données doivent être conservées côté client et le client doit mettre en œuvre un mécanisme de reprise d'insertion des enregistrements une fois la disponibilité restaurée. Pour savoir comment gérer cette situation, consultez l'article de blog Guide de fiabilité Google Pub/Sub.

Les enregistrements nocifs sont un autre type de défaillance à gérer. Un enregistrement nocif est soit un enregistrement refusé par BigQuery parceque son insertion génère une erreur ne permettant aucune autre tentative, soit un enregistrement qu'il n'a pas été possible d'insérer après le nombre maximal de tentatives. Les deux types d'enregistrements doivent être stockés dans une file d'attente de lettres mortes par le pipeline Dataflow pour une analyse plus approfondie.

Si la sémantique "exactement une fois" est requise, créez un flux d'écriture dans le type commit, avec les décalages d'enregistrement fournis par le client. Cela évite les doublons, car l'opération d'écriture n'est appliquée que si la valeur de décalage correspond au prochain décalage d'ajout. Si vous ne fournissez pas de décalage, les enregistrements sont ajoutés à la fin actuelle du flux, ce qui implique un risque de création de doublon de cet enregistrement dans le flux en ca de nouvelle tentative d'insertion.

Si aucune garantie de type "exactement une fois" n'est requise, écrire dans le flux par défaut permet d'obtenir un débit plus élevé et de ne pas consommer la limite de quota applicable à la création de flux d'écriture.

Estimez le débit de votre réseau et assurez-vous à l'avance que vous disposez d'un quota suffisant pour diffuser le débit.

Si votre charge de travail génère ou traite des données à un rythme très inégal, essayez d'atténuer les pics de charge du côté client et d'envoyer les données en streaming à BigQuery avec un débit constant. Cela peut simplifier votre planification de capacité. Si cela n'est pas possible, assurez-vous d'être prêt à gérer les erreurs 429 (ressources épuisées) si et quand votre débit dépasse le quota pendant de courts pics d'activité.

Traitement de données par lot

Supposons qu'un pipeline de traitement par lot quotidien doit être effectué dans un délai fixe. Les données doivent être disponibles dans le délai indiqué afin de bénéficier d'un traitement ultérieur par un autre processus par lot qui génère des rapports pour une autorité réglementaire. Ce cas d'utilisation est courant dans les secteurs réglementés tels que la finance.

Le chargement par lot de données avec des tâches de chargement est l'approche appropriée pour ce cas d'utilisation, car la latence n'est pas une préoccupation tant que le délai est respecté. Assurez-vous que vos buckets Cloud Storage répondent aux exigences d'emplacement pour le chargement de données dans l'ensemble de données BigQuery.

Le résultat d'une tâche de chargement BigQuery est atomique : tous les enregistrements sont insérés ou aucun. Lorsque vous insérez toutes les données dans une seule tâche de chargement, il est recommandé de créer une table à l'aide de la disposition WRITE_TRUNCATE de la ressource JobConfigurationLoad. Cela est important pour les nouvelles tentatives d'exécution d'une tâche de chargement ayant échoué, car le client peut ne pas être en mesure de faire la distinction entre les tâches ayant échoué et l'échec causé par exemple par la communication de la réussite de l'opération au client.

En supposant que les données à ingérer ont déjà été copiées dans Cloud Storage, une nouvelle tentative avec un intervalle exponentiel entre les tentatives est suffisante pour résoudre les problèmes d'ingestion.

Il est recommandé de ne pas atteindre le quota par défaut de 1 500 chargements par table et par jour d'une tâche par lot quotidienne, même en cas de nouvelles tentatives. Lorsque vous chargez des données de manière incrémentielle, le quota par défaut est suffisant pour exécuter une tâche de chargement toutes les cinq minutes tout en conservant du quota disponible suffisant pour au moins une nouvelle tentative par tâche.

Étapes suivantes