Interroger des données Cloud Storage dans des tables externes
Ce document explique comment interroger des données stockées dans une table externe Cloud Storage.
Avant de commencer
Assurez-vous de disposer d'une table externe Cloud Storage.
Rôles requis
Pour interroger des tables externes Cloud Storage, assurez-vous de disposer des rôles suivants :
- Lecteur de données BigQuery (
roles/bigquery.dataViewer
) - Utilisateur BigQuery (
roles/bigquery.user
) - Lecteur des objets de l'espace de stockage (
roles/storage.objectViewer
)
Selon vos autorisations, vous pouvez vous attribuer ces rôles ou demander à votre administrateur de vous les accorder. Pour en savoir plus sur l'attribution de rôles, consultez la page Afficher les rôles pouvant être attribués sur des ressources.
Pour afficher les autorisations BigQuery exactes requises pour interroger des tables externes, développez la section Autorisations requises :
Autorisations requises
bigquery.jobs.create
bigquery.readsessions.create
(obligatoire seulement si vous lisez des données avec l'API BigQuery Storage Read)bigquery.tables.get
bigquery.tables.getData
Vous pouvez également obtenir ces autorisations avec des rôles personnalisés ou d'autres rôles prédéfinis.
Interroger des tables externes permanentes
Après avoir créé une table externe Cloud Storage, vous pouvez l'interroger à l'aide de la syntaxe GoogleSQL, comme s'il s'agissait d'une table BigQuery standard. Exemple :SELECT field1, field2
FROM mydataset.my_cloud_storage_table;
Interroger des tables externes temporaires
L'interrogation d'une source de données externe à l'aide d'une table temporaire est utile pour les requêtes ad hoc ponctuelles qui sont exécutées sur des données externes ou pour les processus d'extraction, de transformation et de chargement (ETL, Extract-Transform-Load).
Pour interroger une source de données externe sans créer de table permanente, vous devez fournir une définition de table pour la table temporaire, puis l'utiliser dans une commande ou un appel pour interroger la table temporaire. Vous pouvez fournir la définition de la table de l'une des manières suivantes :
- Un fichier de définition de table
- Une définition de schéma intégrée
- Un fichier de schéma JSON
Le fichier de définition de table ou le schéma fourni est utilisé pour créer la table externe temporaire, sur laquelle la requête s'exécute.
En cas d'utilisation d'une table externe temporaire, vous ne créez pas de table dans l'un de vos ensembles de données BigQuery. La table n'étant pas stockée de manière permanente dans un ensemble de données, elle ne peut pas être partagée avec d'autres utilisateurs.
Vous pouvez créer et interroger une table temporaire associée à une source de données externe à l'aide de l'outil de ligne de commande bq, de l'API ou des bibliothèques clientes.
bq
La commande bq query
permet d'interroger une table temporaire associée à une source de données externe à l'aide de l'option --external_table_definition
.
Lorsque vous utilisez l'outil de ligne de commande bq pour interroger une table temporaire associée à une source de données externe, vous pouvez spécifier le schéma de la table à l'aide de l'une des méthodes suivantes :
- Un fichier de définition de table (stocké sur l'ordinateur local)
- Une définition de schéma intégrée
- Un fichier de schéma JSON (stocké sur l'ordinateur local)
(Facultatif) Spécifiez l'option --location
et définissez la valeur correspondant à votre emplacement.
Pour interroger une table temporaire associée à votre source de données externe à l'aide d'un fichier de définition de table, saisissez la commande suivante :
bq --location=LOCATION query \ --external_table_definition=TABLE::DEFINITION_FILE \ 'QUERY'
Remplacez les éléments suivants :
LOCATION
: nom de votre emplacement. L'option--location
est facultative ; Par exemple, si vous utilisez BigQuery dans la région de Tokyo, vous pouvez définir la valeur de cette option surasia-northeast1
. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc ;TABLE
: nom de la table temporaire que vous créez.DEFINITION_FILE
: chemin d'accès au fichier de définition de table sur votre ordinateur local.QUERY
: requête que vous soumettez à la table temporaire.
Par exemple, la commande suivante permet de créer et d'interroger une table temporaire nommée sales
à l'aide du fichier de définition de table sales_def
.
bq query \
--external_table_definition=sales::sales_def \
'SELECT
Region,
Total_sales
FROM
sales'
Pour interroger une table temporaire associée à votre source de données externe à l'aide d'une définition de schéma intégrée, saisissez la commande suivante :
bq --location=LOCATION query \ --external_table_definition=TABLE::SCHEMA@SOURCE_FORMAT=BUCKET_PATH \ 'QUERY'
Remplacez les éléments suivants :
LOCATION
: nom de votre emplacement. L'option--location
est facultative ; Par exemple, si vous utilisez BigQuery dans la région de Tokyo, vous pouvez définir la valeur de cette option surasia-northeast1
. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc ;TABLE
: nom de la table temporaire que vous créez.SCHEMA
: définition de schéma en ligne au formatfield:data_type,field:data_type
.SOURCE_FORMAT
: format de la source de données externe, par exempleCSV
.BUCKET_PATH
: chemin d'accès au bucket Cloud Storage contenant les données de la table, au formatgs://bucket_name/[folder_name/]file_pattern
.Vous pouvez sélectionner plusieurs fichiers dans le bucket en spécifiant un caractère générique astérisque (
*
) dans lefile_pattern
. Exemple :gs://mybucket/file00*.parquet
. Pour en savoir plus, consultez la section Gestion des caractères génériques dans les URI Cloud Storage.Vous pouvez spécifier plusieurs buckets pour l'option
uris
en fournissant plusieurs chemins d'accès.Les exemples suivants montrent des valeurs
uris
valides :gs://bucket/path1/myfile.csv
gs://bucket/path1/*.parquet
gs://bucket/path1/file1*
,gs://bucket1/path1/*
Lorsque vous spécifiez des valeurs
uris
qui ciblent plusieurs fichiers, tous ces fichiers doivent partager un schéma compatible.Pour en savoir plus sur l'utilisation des URI Cloud Storage dans BigQuery, consultez la page Chemin d'accès aux ressources Cloud Storage.
QUERY
: requête que vous soumettez à la table temporaire.
Par exemple, la commande suivante crée et interroge une table temporaire appelée sales
qui est associée à un fichier CSV stocké dans Cloud Storage avec la définition de schéma suivante : Region:STRING,Quarter:STRING,Total_sales:INTEGER
.
bq query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
'SELECT
Region,
Total_sales
FROM
sales'
Pour interroger une table temporaire associée à votre source de données externe à l'aide d'un fichier de schéma JSON, saisissez la commande suivante :
bq --location=LOCATION query \ --external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=BUCKET_PATH \ 'QUERY'
Remplacez les éléments suivants :
LOCATION
: nom de votre emplacement. L'option--location
est facultative ; Par exemple, si vous utilisez BigQuery dans la région de Tokyo, vous pouvez définir la valeur de cette option surasia-northeast1
. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc ;SCHEMA_FILE
: chemin d'accès au fichier de schéma JSON sur votre ordinateur local.SOURCE_FORMAT
: format de la source de données externe, par exempleCSV
.BUCKET_PATH
: chemin d'accès au bucket Cloud Storage contenant les données de la table, au formatgs://bucket_name/[folder_name/]file_pattern
.Vous pouvez sélectionner plusieurs fichiers dans le bucket en spécifiant un caractère générique astérisque (
*
) dans lefile_pattern
. Exemple :gs://mybucket/file00*.parquet
. Pour en savoir plus, consultez la section Gestion des caractères génériques dans les URI Cloud Storage.Vous pouvez spécifier plusieurs buckets pour l'option
uris
en fournissant plusieurs chemins d'accès.Les exemples suivants montrent des valeurs
uris
valides :gs://bucket/path1/myfile.csv
gs://bucket/path1/*.parquet
gs://bucket/path1/file1*
,gs://bucket1/path1/*
Lorsque vous spécifiez des valeurs
uris
qui ciblent plusieurs fichiers, tous ces fichiers doivent partager un schéma compatible.Pour en savoir plus sur l'utilisation des URI Cloud Storage dans BigQuery, consultez la page Chemin d'accès aux ressources Cloud Storage.
QUERY
: requête que vous soumettez à la table temporaire.
Par exemple, la commande suivante crée et interroge une table temporaire appelée sales
qui est associée à un fichier CSV stocké dans Cloud Storage à l'aide du fichier de schéma /tmp/sales_schema.json
.
bq query \ --external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv \ 'SELECT Region, Total_sales FROM sales'
API
Pour exécuter une requête à l'aide de l'API, procédez comme suit:
- Créez un objet
Job
. - Renseignez la section
configuration
de l'objetJob
avec un objetJobConfiguration
. - Renseignez la section
query
de l'objetJobConfiguration
avec un objetJobConfigurationQuery
. - Renseignez la section
tableDefinitions
de l'objetJobConfigurationQuery
avec un objetExternalDataConfiguration
. - Appeler la méthode
jobs.insert
pour exécuter la requête de manière asynchrone ou la méthodejobs.query
pour exécuter la requête de manière synchrone, en transmettant l'objetJob
.
Java
Avant d'essayer cet exemple, suivez les instructions de configuration pour Java du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Java.
Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.
Node.js
Avant d'essayer cet exemple, suivez les instructions de configuration pour Node.js du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Node.js.
Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.
Python
Avant d'essayer cet exemple, suivez les instructions de configuration pour Python du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Python.
Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.
Interroger la pseudo-colonne _FILE_NAME
Les tables basées sur des sources de données externes donnent accès à une pseudo-colonne intitulée _FILE_NAME
. Cette colonne contient le chemin d'accès complet du fichier auquel appartient la ligne. Elle n'est disponible que pour les tables qui font référence à des données externes stockées dans Cloud Storage, Google Drive, Amazon S3 et Azure Blob Storage.
Le nom de colonne _FILE_NAME
est réservé. Vous ne pouvez donc pas créer de colonne portant ce nom dans vos tables. Pour sélectionner la valeur de _FILE_NAME
, vous devez utiliser un alias. Par exemple, la requête suivante sélectionne _FILE_NAME
en attribuant l'alias fn
à la pseudo-colonne.
bq query \
--project_id=PROJECT_ID \
--use_legacy_sql=false \
'SELECT
name,
_FILE_NAME AS fn
FROM
`DATASET.TABLE_NAME`
WHERE
name contains "Alex"'
Remplacez les éléments suivants :
-
PROJECT_ID
correspond à un ID de projet valide (cette option n'est pas obligatoire si vous utilisez Cloud Shell ou si vous définissez un projet par défaut dans la Google Cloud CLI). -
DATASET
correspond au nom de l'ensemble de données où est stockée la table externe permanente. -
TABLE_NAME
est le nom de la table externe permanente.
Lorsque la requête comporte un prédicat de filtre sur la pseudo-colonne _FILE_NAME
, BigQuery tente d'ignorer les fichiers qui ne correspondent pas au filtre. Des recommandations similaires pour interroger des tables partitionnées par date d'ingestion à l'aide de pseudo-colonnes s'appliquent lors de la construction de prédicats de requête avec la pseudo-colonne _FILE_NAME
.
Étape suivante
- Découvrez comment utiliser SQL dans BigQuery.
- Documentez-vous sur les tables externes.
- Apprenez-en plus sur les quotas BigQuery.