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'instructionINSERT 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 entre les Cloud pour Omni dans les 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 page Gérer l'accès aux projets, aux dossiers et aux organisations.
Ce rôle prédéfini contient les autorisations requises pour charger des données à l'aide de transferts entre clouds. Pour connaître 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
- 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égionalUS
. Vous pouvez également effectuer des transferts depuis n'importe quelle régionEU
vers un emplacement multirégionalEU
.
- Vous pouvez transférer des données de n'importe quelle région
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 60 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êteSELECT
, l'instructionINSERT 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
etINSERT 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égionalUS
. Vous pouvez également effectuer des transferts depuis n'importe quelle régionEU
vers un emplacement multirégionalEU
.
- Vous pouvez transférer des données de n'importe quelle région
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
surGZIP
lors de la création des tables externes.
Étapes suivantes
- Apprenez-en plus sur BigQuery ML.
- Découvrez BigQuery Omni.
- Découvrez comment exécuter des requêtes.
- Découvrez comment configurer VPC Service Controls pour BigQuery Omni.
- Découvrez comment programmer et gérer des tâches de chargement récurrentes à partir d'Amazon S3 dans BigQuery et à partir de Blob Storage dans BigQuery.