Charger des données avec des opérations multicloud

En tant qu'administrateur ou analyste BigQuery, vous pouvez charger des données depuis un bucket Amazon S3 (Amazon Simple Storage Service) ou depuis Azure Blob Storage dans des tables BigQuery. Vous pouvez associer les données transférées aux données présentes dans les régions Google Cloud ou exploiter les fonctionnalités de BigQuery, telles que BigQuery ML.

Vous pouvez transférer des données vers BigQuery de différentes manières :

  • Pour transférer des données à partir de fichiers hébergés dans Amazon S3 et Azure Blob Storage vers des tables BigQuery, utilisez l'instruction LOAD DATA.

  • Pour filtrer les données provenant de fichiers hébergés dans Amazon S3 ou Blob Storage avant de transférer les résultats dans des tables BigQuery, utilisez l'instruction CREATE TABLE AS SELECT. Pour ajouter des données à la table de destination, utilisez l'instruction INSERT INTO SELECT.

    La manipulation des données est appliquée aux tables externes qui font référence aux données d'Amazon S3 ou de Blob Storage.

Quotas et limites

Pour en savoir plus sur les quotas et les limites, consultez la page Quotas et limites des jobs de requête.

Tarifs

Les octets transférés d'un cloud à l'autre vous sont facturés à l'aide de l'instruction LOAD. Pour connaître les tarifs, consultez la section sur le transfert de données Cross Cloud pour Omni dans Tarifs de BigQuery Omni.

Les octets transférés d'un cloud à l'autre à l'aide de l'instruction CREATE TABLE AS SELECT ou de l'instruction INSERT INTO SELECT vous sont facturés, ainsi que la capacité de calcul.

Les instructions LOAD et CREATE TABLE AS SELECT nécessitent des emplacements dans les régions BigQuery Omni afin d'analyser les fichiers Amazon S3 et Blob Storage en vue de leur chargement. Pour en savoir plus, consultez les Tarifs de BigQuery Omni.

Avant de commencer

Pour fournir à Google Cloud un accès en lecture aux fichiers dans d'autres clouds, demandez à votre administrateur de créer une connexion et de la partager avec vous. Pour en savoir plus sur la création de connexions, consultez la page Se connecter à Amazon S3 ou Blob Storage.

Rôle requis

Afin d'obtenir les autorisations nécessaires pour charger des données à l'aide de transferts entre clouds, demandez à votre administrateur de vous accorder le rôle IAM Éditeur de données BigQuery (roles/bigquery.dataEditor) sur l'ensemble de données. Pour en savoir plus sur l'attribution de rôles, consultez la section Gérer les accès.

Ce rôle prédéfini contient les autorisations requises pour charger des données à l'aide de transferts entre clouds. Pour afficher les autorisations exactes requises, développez la section Autorisations requises :

Autorisations requises

Vous devez disposer des autorisations suivantes pour charger des données à l'aide de transferts entre cloud :

  • bigquery.tables.create
  • bigquery.tables.get
  • bigquery.tables.updateData
  • bigquery.tables.update
  • bigquery.jobs.create
  • bigquery.connections.use

Vous pouvez également obtenir ces autorisations avec des rôles personnalisés ou d'autres rôles prédéfinis.

Pour en savoir plus sur les rôles IAM dans BigQuery, consultez la page Rôles prédéfinis et autorisations.

Charger les données

Vous pouvez charger des données dans BigQuery à l'aide de l'instruction LOAD DATA [INTO|OVERWRITE].

Limites

  • Le chargement de données dans une table de destination avec une partition de temps d'ingestion n'est pas accepté.
  • Les tâches LOAD DATA ne sont pas exécutées sur des réservations. Les tâches utilisent des emplacements à la demande gérés par Google Cloud.
  • La connexion et l'ensemble de données de destination doivent appartenir au même projet. Le chargement de données entre projets n'est pas accepté.
  • LOAD DATA n'est compatible que lorsque vous transférez des données depuis Amazon Simple Storage Service (Amazon S3) ou Azure Blob Storage vers une région BigQuery colocalisée. Pour plus d'informations, consultez la section Emplacements.
    • Vous pouvez transférer des données de n'importe quelle région US vers un emplacement multirégional US. Vous pouvez également effectuer des transferts depuis n'importe quelle région EU vers un emplacement multirégional EU.

Exemple

Exemple 1

L'exemple suivant charge un fichier Parquet nommé sample.parquet à partir d'un bucket Amazon S3 dans la table test_parquet avec un schéma de détection automatique :

LOAD DATA INTO mydataset.testparquet
  FROM FILES (
    uris = ['s3://test-bucket/sample.parquet'],
    format = 'PARQUET'
  )
  WITH CONNECTION `aws-us-east-1.test-connection`

Exemple 2

L'exemple suivant charge un fichier CSV ayant le préfixe sampled* depuis Blob Storage dans la table test_csv avec le partitionnement de colonne prédéfini par heure :

LOAD DATA INTO mydataset.test_csv (Number INT64, Name STRING, Time DATE)
  PARTITION BY Time
  FROM FILES (
    format = 'CSV', uris = ['azure://test.blob.core.windows.net/container/sampled*'],
    skip_leading_rows=1
  )
  WITH CONNECTION `azure-eastus2.test-connection`

Exemple 3

L'exemple suivant remplace la table test_parquet existante par des données d'un fichier nommé sample.parquet avec un schéma de détection automatique :

LOAD DATA OVERWRITE mydataset.testparquet
  FROM FILES (
    uris = ['s3://test-bucket/sample.parquet'],
    format = 'PARQUET'
  )
  WITH CONNECTION `aws-us-east-1.test-connection`

Filtrer les données

Vous pouvez filtrer les données avant de les transférer dans BigQuery à l'aide de l'instruction CREATE TABLE AS SELECT et de l'instruction INSERT INTO SELECT.

Limites

  • Si le résultat de la requête SELECT dépasse 20 Gio en octets logiques, la requête échoue. La table n'est pas créée et les données ne sont pas transférées. Pour savoir comment réduire le volume des données analysées, consultez la section Réduire la quantité de données traitée dans les requêtes.

  • Les tables temporaires ne sont pas acceptées.

  • Le transfert du format de données géospatiales Binaire connu (WKB) n'est pas accepté.

  • L'instruction INSERT INTO SELECT ne permet pas de transférer des données vers une table en cluster.

  • Dans l'instruction INSERT INTO SELECT, si la table de destination est identique à la table source de la requête SELECT, l'instruction INSERT INTO SELECT ne modifie aucune ligne de la table de destination. La table de destination n'est pas modifiée, car BigQuery ne peut pas lire les données entre les régions.

  • CREATE TABLE AS SELECT et INSERT INTO SELECT ne sont compatibles que si vous transférez des données depuis Amazon S3 ou Blob Storage vers une région BigQuery colocalisée. Pour plus d'informations, consultez la section Emplacements.

    • Vous pouvez transférer des données de n'importe quelle région US vers un emplacement multirégional US. Vous pouvez également effectuer des transferts depuis n'importe quelle région EU vers un emplacement multirégional EU.

Exemple

Exemple 1

Supposons que vous disposiez d'une table BigLake nommée myawsdataset.orders qui référence les données d'Amazon S3. Vous souhaitez transférer les données de cette table vers une table BigQuery myotherdataset.shipments dans l'emplacement multirégional US.

Commencez par afficher des informations sur la table myawsdataset.orders :

    bq show myawsdataset.orders;

Le résultat ressemble à ce qui suit :

  Last modified             Schema              Type     Total URIs   Expiration
----------------- -------------------------- ---------- ------------ -----------
  31 Oct 17:40:28   |- l_orderkey: integer     EXTERNAL   1
                    |- l_partkey: integer
                    |- l_suppkey: integer
                    |- l_linenumber: integer
                    |- l_returnflag: string
                    |- l_linestatus: string
                    |- l_commitdate: date

Ensuite, affichez les informations sur la table myotherdataset.shipments :

  bq show myotherdataset.shipments

Le résultat renvoyé ressemble à ceci : Certaines colonnes sont omises pour simplifier la sortie.

  Last modified             Schema             Total Rows   Total Bytes   Expiration   Time Partitioning   Clustered Fields   Total Logical
 ----------------- --------------------------- ------------ ------------- ------------ ------------------- ------------------ ---------------
  31 Oct 17:34:31   |- l_orderkey: integer      3086653      210767042                                                         210767042
                    |- l_partkey: integer
                    |- l_suppkey: integer
                    |- l_commitdate: date
                    |- l_shipdate: date
                    |- l_receiptdate: date
                    |- l_shipinstruct: string
                    |- l_shipmode: string

Désormais, à l'aide de l'instruction CREATE TABLE AS SELECT, vous pouvez charger des données de manière sélective dans la table myotherdataset.orders de l'emplacement multirégional US :

CREATE OR REPLACE TABLE
  myotherdataset.orders
  PARTITION BY DATE_TRUNC(l_commitdate, YEAR) AS
SELECT
  *
FROM
  myawsdataset.orders
WHERE
  EXTRACT(YEAR FROM l_commitdate) = 1992;

Vous pouvez ensuite effectuer une opération de jointure avec la table nouvellement créée :

SELECT
  orders.l_orderkey,
  orders.l_orderkey,
  orders.l_suppkey,
  orders.l_commitdate,
  orders.l_returnflag,
  shipments.l_shipmode,
  shipments.l_shipinstruct
FROM
  myotherdataset.shipments
JOIN
  `myotherdataset.orders` as orders
ON
  orders.l_orderkey = shipments.l_orderkey
AND orders.l_partkey = shipments.l_partkey
AND orders.l_suppkey = shipments.l_suppkey
WHERE orders.l_returnflag = 'R'; -- 'R' means refunded.

Lorsque de nouvelles données sont disponibles, ajoutez les données de l'année 1993 à la table de destination à l'aide de l'instruction INSERT INTO SELECT :

INSERT INTO
   myotherdataset.orders
 SELECT
   *
 FROM
   myawsdataset.orders
 WHERE
   EXTRACT(YEAR FROM l_commitdate) = 1993;

Exemple 2

L'exemple suivant insère des données dans une table partitionnée par date d'ingestion :

CREATE TABLE
 mydataset.orders(id String, numeric_id INT)
PARTITION BY _PARTITIONDATE;

Après avoir créé une table partitionnée, vous pouvez insérer des données dans la table partitionnée par date d'ingestion :

INSERT INTO
 mydataset.orders(
   _PARTITIONTIME,
   id,
   numeric_id)
SELECT
 TIMESTAMP("2023-01-01"),
 id,
 numeric_id,
FROM
 mydataset.ordersof23
WHERE
 numeric_id > 4000000;

Bonnes pratiques

  • Évitez de charger plusieurs fichiers de taille inférieure à 5 Mo. Créez plutôt une table externe pour votre fichier, et exportez le résultat de la requête vers Amazon S3 ou Blob Storage pour créer un fichier plus volumineux. Cette méthode permet d'améliorer le temps de transfert de vos données.
    • Pour en savoir plus sur la limite du nombre maximal de résultats de requête, consultez la section Taille maximale des résultats de requête BigQuery Omni.
  • Si vos données source se trouvent dans un fichier compressé au format gzip, définissez l'option external_table_options.compression sur GZIP lors de la création des tables externes.

Étapes suivantes