Créer des tables externes Cloud Storage
BigQuery permet d'interroger des données Cloud Storage aux formats suivants :
- CSV (Comma-Separated Values)
- JSON (délimité par un retour à la ligne)
- Avro
- ORC
- Parquet
- Exportations Cloud Datastore
- Exportations Firestore
BigQuery permet d'interroger des données Cloud Storage depuis les classes de stockage suivantes :
- Standard
- Nearline
- Coldline
- Archive
Pour interroger une table externe Cloud Storage, vous devez disposer d'autorisations à la fois sur la table externe et sur les fichiers Cloud Storage. Nous vous recommandons d'utiliser une table BigLake à la place si possible. Les tables BigLake fournissent une délégation d'accès. Vous n'avez donc besoin que d'autorisations sur la table BigLake pour interroger les données Cloud Storage.
Prenez bien en compte l'emplacement de votre ensemble de données et de votre bucket Cloud Storage lorsque vous interrogez des données stockées dans Cloud Storage.
Avant de commencer
Attribuez aux utilisateurs des rôles IAM (Identity and Access Management) incluant les autorisations nécessaires pour effectuer l'ensemble des tâches du présent document. Les autorisations requises pour effectuer une tâche (le cas échéant) sont répertoriées dans la section "Autorisations requises" de la tâche.
Rôles requis
Pour créer une table externe, vous devez disposer de l'autorisation IAM (Identity and Access Management) BigQuery bigquery.tables.create
.
Tous les rôles Identity and Access Management prédéfinis suivants incluent cette autorisation :
- Éditeur de données BigQuery (
roles/bigquery.dataEditor
) - Propriétaire de données BigQuery (
roles/bigquery.dataOwner
) - Administrateur BigQuery (
roles/bigquery.admin
)
Vous devez également disposer des autorisations suivantes pour accéder au bucket Cloud Storage contenant vos données :
storage.buckets.get
storage.objects.get
storage.objects.list
(obligatoire si vous utilisez un caractère générique dans l'URI)
Le rôle IAM prédéfini Administrateur Cloud Storage (roles/storage.admin
) inclut ces autorisations.
Si vous n'êtes pas un compte principal de l'un de ces rôles, demandez à votre administrateur de vous accorder l'accès ou de créer la table externe pour vous.
Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la section Rôles et autorisations prédéfinis.
Champs d'application de l'accès aux instances Compute Engine
Si vous devez interroger une table externe associée à une source Cloud Storage à partir d'une instance Compute Engine, celle-ci doit au moins disposer de l'accès en lecture seule à Cloud Storage (https://www.googleapis.com/auth/devstorage.read_only
).
Les champs d'application contrôlent l'accès de l'instance Compute Engine aux produits Google Cloud, y compris Cloud Storage. Les applications exécutées sur l'instance utilisent le compte de service associé à l'instance pour appeler les API Google Cloud.
Si vous configurez une instance Compute Engine pour qu'elle s'exécute en tant que compte de service Compute Engine par défaut, un certain nombre de champs d'application par défaut lui sont attribués. y compris le champ d'application https://www.googleapis.com/auth/devstorage.read_only
.
Si vous configurez l'instance avec un compte de service personnalisé, assurez-vous d'accorder explicitement le champ d'application https://www.googleapis.com/auth/devstorage.read_only
à l'instance.
Pour en savoir plus sur l'attribution de champs d'application à une instance Compute Engine, consultez la section Modifier le compte de service et les champs d'application d'accès d'une instance. Pour en savoir plus sur les comptes de service Compute Engine, consultez la page Comptes de service.
Créer des tables externes sur des données non partitionnées
Vous pouvez créer une table permanente associée à votre source de données externe :
- Utiliser la console Google Cloud
- Exécuter la commande
bq mk
- en créant une configuration
ExternalDataConfiguration
lorsque vous utilisez la méthode d'APItables.insert
; - En exécutant une instruction LDD (langage de définition de données)
CREATE EXTERNAL TABLE
- En utilisant les bibliothèques clientes
Sélectionnez l'une des options suivantes :
Console
Accédez à la page BigQuery.
Dans le volet Explorateur, développez votre projet et sélectionnez un ensemble de données.
Développez l'option
Actions, puis cliquez sur Créer une table.Dans la section Source, spécifiez les détails suivants :
Pour le champ Créer une table à partir de, sélectionnez Google Cloud Storage.
Dans le champ Sélectionner un fichier du bucket GCS ou utiliser un modèle d'URI, parcourez la liste pour sélectionner un bucket et un fichier à utiliser, ou saisissez le chemin d'accès au format
gs://bucket_name/[folder_name/]file_name
.Vous ne pouvez pas spécifier plusieurs URI dans la console Google Cloud. En revanche, vous pouvez sélectionner plusieurs fichiers en spécifiant un caractère générique astérisque (
*
). Par exemple,gs://mybucket/file_name*
. Pour en savoir plus, consultez la section Gestion des caractères génériques dans les URI Cloud Storage.Le bucket Cloud Storage doit se trouver dans le même emplacement que l'ensemble de données contenant la table que vous créez.
Pour Format de fichier, sélectionnez le format correspondant à votre fichier.
Dans la section Destination, spécifiez les détails suivants :
Pour Projet, choisissez le projet dans lequel créer la table.
Pour Ensemble de données, choisissez l'ensemble de données dans lequel créer la table.
Pour Table, saisissez le nom de la table que vous créez.
Pour Type de table, sélectionnez Table externe.
Dans la section Schéma, vous pouvez activer la détection automatique de schéma ou spécifier manuellement un schéma si vous disposez d'un fichier source. Si vous ne disposez pas d'un fichier source, vous devez spécifier manuellement un schéma.
Pour activer la détection automatique de schéma, sélectionnez l'option Détection automatique.
Pour spécifier manuellement un schéma, ne cochez pas l'option Détection automatique. Activez Modifier sous forme de texte et saisissez le schéma de la table sous forme de tableau JSON.
Pour ignorer les lignes dont les valeurs de colonnes supplémentaires ne correspondent pas au schéma, développez la section Options avancées et sélectionnez Valeurs inconnues.
Cliquez sur Créer une table.
Une fois la table permanente créée, vous pouvez exécuter une requête sur celle-ci comme s'il s'agissait d'une table BigQuery native. Une fois la requête exécutée, vous pouvez exporter les résultats au format CSV ou JSON, ou bien les enregistrer soit sous forme de table, soit dans Google Sheets.
SQL
Vous pouvez créer une table externe permanente en exécutant l'instruction LDD CREATE EXTERNAL TABLE
.
Vous pouvez spécifier explicitement le schéma ou utiliser la détection automatique de schéma pour déduire le schéma à partir des données externes.
Dans la console Google Cloud, accédez à la page BigQuery.
Dans l'éditeur de requête, saisissez l'instruction suivante :
CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME` OPTIONS ( format ="TABLE_FORMAT", uris = ['BUCKET_PATH'[,...]] );
Remplacez les éléments suivants :
PROJECT_ID
: nom du projet dans lequel vous souhaitez créer la table, par exemplemyproject
DATASET
: nom de l'ensemble de données BigQuery dans lequel vous souhaitez créer la table, par exemple,mydataset
.EXTERNAL_TABLE_NAME
: nom de la table que vous souhaitez créer (par exemple,mytable
).TABLE_FORMAT
: format de la table que vous souhaitez créer (par exemple,PARQUET
)BUCKET_PATH
: chemin d'accès au bucket Cloud Storage contenant les données de la table externe, au format['gs://bucket_name/[folder_name/]file_name']
.Vous pouvez sélectionner plusieurs fichiers dans le bucket en spécifiant un caractère générique astérisque (
*
) dans le chemin. Par exemple,['gs://mybucket/file_name*']
. 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/*.csv']
['gs://bucket/path1/*', 'gs://bucket/path2/file00*']
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.
Cliquez sur
Exécuter.
Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.
Exemples
L'exemple suivant utilise la détection automatique de schéma pour créer une table externe nommée sales
, qui est associée à un fichier CSV stocké dans Cloud Storage :
CREATE OR REPLACE EXTERNAL TABLE mydataset.sales OPTIONS ( format = 'CSV', uris = ['gs://mybucket/sales.csv']);
L'exemple suivant spécifie un schéma explicitement et ignore la première ligne du fichier CSV :
CREATE OR REPLACE EXTERNAL TABLE mydataset.sales ( Region STRING, Quarter STRING, Total_Sales INT64 ) OPTIONS ( format = 'CSV', uris = ['gs://mybucket/sales.csv'], skip_leading_rows = 1);
bq
Pour créer une table externe, exécutez la commande bq mk
avec l'option --external_table_definition
. Cette option contient un chemin d'accès à un fichier de définition de table ou une définition de table intégrée.
Option 1 : fichier de définition de table
Exécutez la commande bq mkdef
pour créer un fichier de définition de table, puis transmettez le chemin d'accès au fichier bq mk
comme suit :
bq mkdef --source_format=SOURCE_FORMAT \ BUCKET_PATH > DEFINITION_FILE bq mk --table \ --external_table_definition=DEFINITION_FILE \ DATASET_NAME.TABLE_NAME \ SCHEMA
Remplacez les éléments suivants :
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.
DEFINITION_FILE
: chemin d'accès au fichier de définition de table sur votre ordinateur local.DATASET_NAME
: nom de l'ensemble de données contenant la table.TABLE_NAME
: nom de la table que vous créez.SCHEMA
: spécifie un chemin d'accès à un fichier de schéma JSON, ou spécifie le schéma au formatfield:data_type,field:data_type,...
.
Exemple :
bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
mydataset.mytable \
Region:STRING,Quarter:STRING,Total_sales:INTEGER
Pour utiliser la détection automatique de schéma, définissez l'option --autodetect=true
dans la commande mkdef
et omettez le schéma :
bq mkdef --source_format=CSV --autodetect=true \
gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
mydataset.mytable
Option 2 : Définition de table intégrée
Au lieu de créer un fichier de définition de table, vous pouvez transmettre la définition directement à la commande bq mk
:
bq mk --table \ --external_table_definition=@SOURCE_FORMAT=BUCKET_PATH \ DATASET_NAME.TABLE_NAME \ SCHEMA
Remplacez les éléments suivants :
SOURCE_FORMAT
: format de la source de données externePar exemple,
CSV
.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.
DATASET_NAME
: nom de l'ensemble de données contenant la table.TABLE_NAME
: nom de la table que vous créez.SCHEMA
: spécifie un chemin d'accès à un fichier de schéma JSON, ou spécifie le schéma au formatfield:data_type,field:data_type,...
. Pour utiliser la détection automatique de schéma, omettez cet argument.
Exemple :
bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
mydataset.mytable \
Region:STRING,Quarter:STRING,Total_sales:INTEGER
API
Appelez la méthode API tables.insert
, puis créez un objet ExternalDataConfiguration
dans la ressource Table
que vous transmettez.
Spécifiez la propriété schema
ou définissez la propriété autodetect
sur true
pour activer la détection automatique du schéma pour les sources de données acceptées.
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.
Créer des tables externes sur des données partitionnées
Vous pouvez créer une table externe pour les données partitionnées Hive résidant dans Cloud Storage. Une fois la table partitionnée en externe créée, vous ne pouvez pas modifier la clé de partition. Vous devez recréer la table pour modifier la clé de partition.
Pour créer une table externe pour les données partitionnées Hive, choisissez l'une des options suivantes :
Console
Dans la console Google Cloud, accédez à BigQuery.
- Dans le volet Explorateur, développez votre projet et sélectionnez un ensemble de données.
- Cliquez sur Afficher les actions, puis sur Créer une table. Le volet Créer une table s'ouvre.
- Dans la section Source, spécifiez les détails suivants :
- Pour le champ Créer une table à partir de, sélectionnez Google Cloud Storage.
- Dans le champ Sélectionner un fichier à partir d'un bucket Cloud Storage, saisissez le chemin d'accès au dossier Cloud Storage, en utilisant des caractères génériques.
Exemple :
my_bucket/my_files*
. Le bucket Cloud Storage doit se trouver au même emplacement que l'ensemble de données contenant la table que vous souhaitez créer, modifier ou écraser. - Dans la liste Format de fichier, sélectionnez le type de fichier.
- Cochez la case Partitionnement des données source, puis, pour Sélectionner le préfixe d'URI source, saisissez le préfixe d'URI Cloud Storage. Exemple :
gs://my_bucket/my_files
. - Dans la section Mode d'inférence de la partition, sélectionnez l'une des options suivantes :
- Déduire automatiquement les types pour définir le mode de détection du schéma de partition sur
AUTO
. - Toutes les colonnes sont des chaînes pour définir le mode de détection du schéma de partition sur
STRINGS
. - Fournir ma propre définition pour définir le mode de détection du schéma de partition sur
CUSTOM
et saisir manuellement les informations de schéma pour les clés de partition. Pour plus d'informations, consultez la section Fournir un schéma de clé de partitionnement personnalisé.
- Déduire automatiquement les types pour définir le mode de détection du schéma de partition sur
- (Facultatif) Pour exiger un filtre de partitionnement sur toutes les requêtes de cette table, cochez la case Demander un filtre de partitionnement. Ce type de filtre peut contribuer à réduire les coûts et à améliorer les performances. Pour en savoir plus, consultez la section Exiger des filtres de prédicat sur les clés de partitionnement dans les requêtes.
- Dans la section Destination, spécifiez les détails suivants :
- Pour Projet, sélectionnez le projet dans lequel vous souhaitez créer la table.
- Pour Ensemble de données, sélectionnez l'ensemble de données dans lequel vous souhaitez créer la table.
- Pour le champ Table, saisissez le nom de la table que vous souhaitez créer.
- Pour Type de table, sélectionnez Table externe.
- Dans la section Schéma, saisissez la définition du schéma.
- Pour activer la détection automatique du schéma, sélectionnez Détection automatique.
- Pour ignorer les lignes dont les valeurs de colonnes supplémentaires ne correspondent pas au schéma, développez la section Options avancées et sélectionnez Valeurs inconnues.
- Cliquez sur Créer une table.
SQL
Utilisez l'instruction LDD CREATE EXTERNAL TABLE
:
L'exemple suivant utilise la détection automatique des clés de partitionnement Hive :
CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME` WITH PARTITION COLUMNS OPTIONS ( format = 'SOURCE_FORMAT', uris = ['GCS_URIS'], hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX', require_hive_partition_filter = BOOLEAN);
Remplacez les éléments suivants :
SOURCE_FORMAT
: format de la source de données externe, par exemplePARQUET
.GCS_URIS
: chemin d'accès au dossier Cloud Storage, au format générique.GCS_URI_SHARED_PREFIX
: préfixe de l'URI source sans le caractère générique.BOOLEAN
: spécifie si un filtre de prédicat est requis au moment de la requête. Cette option est facultative. La valeur par défaut estfalse
.
L'exemple suivant utilise des types et des clés de partitionnement Hive personnalisés en les répertoriant dans la clause WITH PARTITION COLUMNS
:
CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME` WITH PARTITION COLUMNS (PARTITION_COLUMN_LIST) OPTIONS ( format = 'SOURCE_FORMAT', uris = ['GCS_URIS'], hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX', require_hive_partition_filter = BOOLEAN);
Remplacez les éléments suivants :
PARTITION_COLUMN_LIST
: liste de colonnes suivant le même ordre dans le chemin d'accès du dossier Cloud Storage, au format :
KEY1 TYPE1, KEY2 TYPE2
L'exemple suivant crée une table partitionnée en externe. Il utilise la détection automatique de schéma pour détecter à la fois le schéma du fichier et la configuration de partitionnement Hive. Si le chemin externe est gs://bucket/path/field_1=first/field_2=1/data.parquet
, les colonnes de partition sont détectées en tant que field_1
(STRING
) et field_2
(INT64
).
CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable WITH PARTITION COLUMNS OPTIONS ( uris = ['gs://bucket/path/*'], format = 'PARQUET', hive_partition_uri_prefix = 'gs://bucket/path', require_hive_partition_filter = false);
L'exemple suivant crée une table partitionnée en externe en spécifiant explicitement les colonnes de partition. Cet exemple suppose que le chemin d'accès au fichier externe est au format gs://bucket/path/field_1=first/field_2=1/data.parquet
.
CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable WITH PARTITION COLUMNS ( field_1 STRING, -- column order must match the external path field_2 INT64) OPTIONS ( uris = ['gs://bucket/path/*'], format = 'PARQUET', hive_partition_uri_prefix = 'gs://bucket/path', require_hive_partition_filter = false);
bq
Tout d'abord, utilisez la commande bq mkdef
pour créer un fichier de définition de table :
bq mkdef \ --source_format=SOURCE_FORMAT \ --hive_partitioning_mode=PARTITIONING_MODE \ --hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \ --require_hive_partition_filter=BOOLEAN \ GCS_URIS > DEFINITION_FILE
Remplacez les éléments suivants :
SOURCE_FORMAT
: format de la source de données externe. Exemple :CSV
.PARTITIONING_MODE
: mode de partitionnement Hive. Utilisez l'une des valeurs suivantes :AUTO
: détecte automatiquement les noms et les types de clés.STRINGS
: convertit automatiquement les noms de clés en chaînes.CUSTOM
: encode le schéma de clé dans le préfixe d'URI source.
GCS_URI_SHARED_PREFIX
: préfixe de l'URI source.BOOLEAN
: spécifie si un filtre de prédicat est requis au moment de la requête. Cette option est facultative. La valeur par défaut estfalse
.GCS_URIS
: chemin d'accès au dossier Cloud Storage, au format générique.DEFINITION_FILE
: chemin d'accès au fichier de définition de table sur votre ordinateur local.
Si la valeur de PARTITIONING_MODE
est CUSTOM
, incluez le schéma de clé de partitionnement dans le préfixe d'URI source, en utilisant le format suivant :
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...
Après avoir créé le fichier de définition de table, créez la table externe à l'aide de la commande bq mk
:
bq mk --external_table_definition=DEFINITION_FILE \ DATASET_NAME.TABLE_NAME \ SCHEMA
Remplacez les éléments suivants :
DEFINITION_FILE
: chemin d'accès au fichier de définition de table.DATASET_NAME
: nom de l'ensemble de données contenant la table.TABLE_NAME
: nom de la table que vous créez.SCHEMA
: spécifie un chemin d'accès à un fichier de schéma JSON, ou spécifie le schéma au formatfield:data_type,field:data_type,...
. Pour utiliser la détection automatique de schéma, omettez cet argument.
Exemples
L'exemple suivant utilise le mode de partitionnement Hive AUTO
:
bq mkdef --source_format=CSV \
--hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
gs://myBucket/myTable/* > mytable_def
bq mk --external_table_definition=mytable_def \
mydataset.mytable \
Region:STRING,Quarter:STRING,Total_sales:INTEGER
L'exemple suivant utilise le mode de partitionnement Hive STRING
:
bq mkdef --source_format=CSV \
--hive_partitioning_mode=STRING \
--hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
gs://myBucket/myTable/* > mytable_def
bq mk --external_table_definition=mytable_def \
mydataset.mytable \
Region:STRING,Quarter:STRING,Total_sales:INTEGER
L'exemple suivant utilise le mode de partitionnement Hive CUSTOM
:
bq mkdef --source_format=CSV \
--hive_partitioning_mode=CUSTOM \
--hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING} \
gs://myBucket/myTable/* > mytable_def
bq mk --external_table_definition=mytable_def \
mydataset.mytable \
Region:STRING,Quarter:STRING,Total_sales:INTEGER
API
Pour définir le partitionnement Hive à l'aide de l'API BigQuery, ajoutez un objet HiverPartitioningOptions dans l'objetExternalDataConfiguration lorsque vous créez le fichier de définition de table.
Si vous définissez le champ hivePartitioningOptions.mode
sur CUSTOM
, vous devez encoder le schéma de clé de partitionnement dans le champ hivePartitioningOptions.sourceUriPrefix
comme suit : gs://BUCKET/PATH_TO_TABLE/{KEY1:TYPE1}/{KEY2:TYPE2}/...
Pour forcer l'application d'un filtre de prédicat au moment de la requête, définissez le champ hivePartitioningOptions.requirePartitionFilter
sur true
.
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.
Interroger des tables externes
Pour en savoir plus, consultez la section Interroger des données Cloud Storage dans des tables externes.
Mettre à niveau des tables externes vers BigLake
Vous pouvez mettre à niveau des tables basées sur Cloud Storage vers des tables BigLake en associant la table externe à une connexion. Si vous souhaitez utiliser la mise en cache des métadonnées avec la table BigLake, vous pouvez spécifier les paramètres en même temps. Pour obtenir des détails sur la table, tels que son format source et son URI source, consultez la page Obtenir des informations sur la table.
Pour mettre à jour une table externe vers une table BigLake, sélectionnez l'une des options suivantes :
SQL
Utilisez l'instruction LDD CREATE OR REPLACE EXTERNAL TABLE
pour mettre à jour une table :
Dans la console Google Cloud, accédez à la page BigQuery.
Dans l'éditeur de requête, saisissez l'instruction suivante :
CREATE OR REPLACE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME` WITH CONNECTION `REGION.CONNECTION_ID` OPTIONS( format ="TABLE_FORMAT", uris = ['BUCKET_PATH'], max_staleness = STALENESS_INTERVAL, metadata_cache_mode = 'CACHE_MODE' );
Remplacez les éléments suivants :
PROJECT_ID
: nom du projet contenant la tableDATASET
: nom de l'ensemble de données contenant la tableEXTERNAL_TABLE_NAME
: nom de la tableREGION
: région qui contient la connexionCONNECTION_ID
: nom de la connexion à utiliserTABLE_FORMAT
: format utilisé par la tableVous ne pouvez pas modifier ce paramètre lors de la mise à jour de la table.
BUCKET_PATH
: chemin d'accès au bucket Cloud Storage contenant les données de la table externe, au format['gs://bucket_name/[folder_name/]file_name']
.Vous pouvez sélectionner plusieurs fichiers dans le bucket en spécifiant un caractère générique astérisque (
*
) dans le chemin. Par exemple,['gs://mybucket/file_name*']
. 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/*.csv']
['gs://bucket/path1/*', 'gs://bucket/path2/file00*']
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.
STALENESS_INTERVAL
: indique si les métadonnées mises en cache sont utilisées par les opérations sur la table et indique le niveau nécessaire de fraîcheur des métadonnées mises en cache pour que l'opération puisse les utiliser.Pour en savoir plus sur les considérations liées à la mise en cache des métadonnées, consultez la section Mise en cache des métadonnées pour améliorer les performances.
Pour désactiver la mise en cache des métadonnées, spécifiez 0. Il s'agit de la valeur par défaut.
Pour activer la mise en cache des métadonnées, spécifiez une valeur de littéral d'intervalle comprise entre 30 minutes et 7 jours. Par exemple, spécifiez
INTERVAL 4 HOUR
pour un intervalle d'obsolescence de quatre heures. Avec cette valeur, les opérations sur la table utilisent les métadonnées mises en cache si elles ont été actualisées au cours des quatre dernières heures. Si les métadonnées mises en cache sont plus anciennes, l'opération extrait les métadonnées de Cloud Storage.CACHE_MODE
: indique si le cache de métadonnées est actualisé automatiquement ou manuellement.Pour en savoir plus sur les considérations liées à la mise en cache des métadonnées, consultez la section Mise en cache des métadonnées pour améliorer les performances.
Définissez cet élément sur
AUTOMATIC
pour que le cache de métadonnées soit actualisé à un intervalle défini par le système, généralement entre 30 et 60 minutes.Définissez la valeur sur
MANUAL
si vous souhaitez actualiser le cache de métadonnées selon une programmation que vous déterminez. Dans ce cas, vous pouvez appeler la procédure systèmeBQ.REFRESH_EXTERNAL_METADATA_CACHE
pour actualiser le cache.Vous devez définir
CACHE_MODE
siSTALENESS_INTERVAL
est défini sur une valeur supérieure à 0.
Cliquez sur
Exécuter.
Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.
bq
Exécutez les commandes bq mkdef
et bq update
pour mettre à jour une table :
Générez une définition de table externe décrivant les aspects de la table à modifier :
bq mkdef --connection_id=PROJECT_ID.REGION.CONNECTION_ID \ --source_format=TABLE_FORMAT \ --metadata_cache_mode=CACHE_MODE \ "BUCKET_PATH" > /tmp/DEFINITION_FILE
Remplacez les éléments suivants :
PROJECT_ID
: nom du projet contenant la connexionREGION
: région qui contient la connexionCONNECTION_ID
: nom de la connexion à utiliser.TABLE_FORMAT
: format utilisé par la table. Vous ne pouvez pas modifier ce paramètre lors de la mise à jour de la table.CACHE_MODE
: indique si le cache de métadonnées est actualisé automatiquement ou manuellement. Pour en savoir plus sur les considérations liées à la mise en cache des métadonnées, consultez la section Mettre en cache les métadonnées pour améliorer les performances.Définissez cet élément sur
AUTOMATIC
pour que le cache de métadonnées soit actualisé selon un intervalle défini par le système, généralement entre 30 et 60 minutes.Définissez la valeur sur
MANUAL
si vous souhaitez actualiser le cache de métadonnées selon une programmation que vous déterminez. Dans ce cas, vous pouvez appeler la procédure systèmeBQ.REFRESH_EXTERNAL_METADATA_CACHE
pour actualiser le cache.Vous devez définir
CACHE_MODE
siSTALENESS_INTERVAL
est défini sur une valeur supérieure à 0.BUCKET_PATH
: chemin d'accès au bucket Cloud Storage contenant les données de la table externe, au formatgs://bucket_name/[folder_name/]file_name
.Vous pouvez limiter les fichiers sélectionnés à partir du bucket en spécifiant un caractère générique astérisque (
*
) dans le chemin. Par exemple,gs://mybucket/file_name*
. 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/*.csv
gs://bucket/path1/*,gs://bucket/path2/file00*
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.
DEFINITION_FILE
: nom du fichier de définition de table que vous créez.
Mettez à jour la table en utilisant la nouvelle définition de table externe :
bq update --max_staleness=STALENESS_INTERVAL \ --external_table_definition=/tmp/DEFINITION_FILE \ PROJECT_ID:DATASET.EXTERNAL_TABLE_NAME
Remplacez les éléments suivants :
STALENESS_INTERVAL
: indique si les métadonnées mises en cache sont utilisées par les opérations sur la table et indique le niveau nécessaire de fraîcheur des métadonnées mises en cache pour que l'opération puisse les utiliser. Pour en savoir plus sur les considérations liées à la mise en cache des métadonnées, consultez la section Mise en cache des métadonnées pour améliorer les performances.Pour désactiver la mise en cache des métadonnées, spécifiez 0. Il s'agit de la valeur par défaut.
Pour activer la mise en cache des métadonnées, spécifiez une valeur d'intervalle entre 30 minutes et 7 jours, à l'aide du format
Y-M D H:M:S
décrit dans la documentation de type de donnéesINTERVAL
. Par exemple, spécifiez0-0 0 4:0:0
pour un intervalle d'obsolescence de quatre heures. Avec cette valeur, les opérations sur la table utilisent les métadonnées mises en cache si elles ont été actualisées au cours des quatre dernières heures. Si les métadonnées mises en cache sont plus anciennes, l'opération extrait les métadonnées de Cloud Storage.DEFINITION_FILE
: nom du fichier de définition de table que vous avez créé ou mis à jour.PROJECT_ID
: nom du projet contenant la table.DATASET
: nom de l'ensemble de données contenant la table.EXTERNAL_TABLE_NAME
: nom de la table
Chemin d'accès à la ressource Cloud Storage
Lorsque vous créez une table externe basée sur une source de données Cloud Storage, vous devez fournir le chemin d'accès aux données.
Le chemin d'accès à la ressource Cloud Storage contient le nom du bucket et l'objet (nom de fichier). Par exemple, si le bucket Cloud Storage est nommé mybucket
et que le fichier de données s'appelle myfile.csv
, le chemin d'accès à la ressource sera gs://mybucket/myfile.csv
.
BigQuery n'accepte pas les chemins de ressource Cloud Storage qui incluent plusieurs barres obliques consécutives après la double barre oblique initiale.
Le nom des objets Cloud Storage peut contenir plusieurs barres obliques ("/") consécutives. Toutefois, BigQuery convertit les barres obliques consécutives en une seule. Par exemple, le chemin d'accès à la ressource suivant, bien qu'il soit valide dans Cloud Storage, ne fonctionne pas dans BigQuery : gs://bucket/my//object//name
.
Pour récupérer le chemin d'accès à la ressource Cloud Storage, procédez comme suit :
Ouvrez la console Cloud Storage.
Accédez à l'emplacement de l'objet (fichier) contenant les données source.
Cliquez sur le nom de l'objet souhaité.
La page Détails de l'objet s'affiche.
Copiez la valeur fournie dans le champ gsutil URI, qui commence par
gs://
.
Gestion des caractères génériques dans les URI Cloud Storage
Si vos données sont séparées en plusieurs fichiers, vous pouvez utiliser un caractère générique (*) pour sélectionner plusieurs fichiers. Le caractère générique astérisque doit respecter les règles suivantes :
- Le caractère générique astérisque peut apparaître à l'intérieur ou à la fin du nom de l'objet.
- Vous ne pouvez pas utiliser plusieurs astérisques. Par exemple, le chemin
gs://mybucket/fed-*/temp/*.csv
n'est pas valide. - Vous ne pouvez pas utiliser d'astérisque avec le nom du bucket.
Exemples :
L'exemple suivant montre comment sélectionner tous les fichiers de tous les dossiers commençant par le préfixe
gs://mybucket/fed-samples/fed-sample
:gs://mybucket/fed-samples/fed-sample*
L'exemple suivant montre comment sélectionner uniquement les fichiers ayant une extension
.csv
dans le dossier nomméfed-samples
et tous les sous-dossiers defed-samples
:gs://mybucket/fed-samples/*.csv
L'exemple suivant montre comment sélectionner des fichiers avec un modèle de dénomination
fed-sample*.csv
dans le dossier nomméfed-samples
. Cet exemple ne sélectionne pas les fichiers dans les sous-dossiers defed-samples
.gs://mybucket/fed-samples/fed-sample*.csv
Lorsque vous utilisez l'outil de ligne de commande bq, vous devrez peut-être échapper l'astérisque sur certaines plates-formes.
Vous ne pouvez pas utiliser de caractère générique astérisque lorsque vous créez des tables externes associées à des exportations Datastore ou Firestore.
Limites
Pour en savoir plus sur les limites applicables aux tables externes, consultez la section Limites des tables externes.
Étapes suivantes
- Documentez-vous sur les tables externes.
- Découvrez les tables BigLake.