Utiliser la détection automatique de schéma
Détection automatique de schéma
La détection automatique de schéma permet à BigQuery de déduire le schéma des données CSV, JSON ou Google Sheets. La détection automatique de schéma est disponible lorsque vous chargez des données dans BigQuery et lorsque vous interrogez une source de données externe.
Lorsque la détection automatique est activée, BigQuery déduit le type de données pour chaque colonne. BigQuery sélectionne un fichier aléatoire dans la source de données et analyse jusqu'à 500 premières lignes de données afin de les utiliser comme échantillon représentatif.
BigQuery procède ensuite à l'examen de chaque champ et essaie de leur affecter un type de données en fonction des valeurs de l'exemple. Si toutes les lignes d'une colonne sont vides, la détection automatique utilise par défaut le type de données STRING
pour la colonne.
Si vous n'activez pas la détection automatique de schéma pour les données CSV, JSON ou Google Sheets, vous devez fournir le schéma manuellement lors de la création de la table.
Vous n'avez pas besoin d'activer la détection automatique de schéma pour les fichiers d'exportation Avro, Parquet, ORC, Firestore ou Datastore. Ces formats de fichiers étant autodescriptifs, BigQuery déduit automatiquement le schéma de la table à partir des données sources. Pour les fichiers Parquet, Avro et Orc, vous pouvez éventuellement fournir un schéma explicite pour remplacer le schéma déduit.
Vous pouvez afficher le schéma détecté pour une table de différentes manières :
- Utilisez la console Google Cloud.
- Utilisez la commande
bq show
de l'outil de ligne de commande bq.
Dans de rares cas, lorsque BigQuery détecte des schémas, certains noms de champ peuvent être modifiés pour être compatibles avec la syntaxe GoogleSQL.
Pour en savoir plus sur les conversions de types de données, consultez les pages suivantes :
- Conversion de type de données lors du chargement de données depuis Cloud Datastore
- Conversion de type de données lors du chargement de données depuis Firestore
- Conversions Avro
- Conversions Parquet
- Conversions ORC
Charger des données à l'aide de la détection automatique de schéma
Pour activer la détection automatique du schéma lors du chargement des données, utilisez l'une des approches suivantes :
- Dans la console Google Cloud, dans la section Schéma, pour la Détection automatique, cochez l'option Schéma et paramètres d'entrée.
- Dans l'outil de ligne de commande bq, exécutez la commande
bq load
avec le paramètre--autodetect
.
Lorsque la détection automatique de schéma est activée, BigQuery tente au mieux de déduire automatiquement le schéma des fichiers CSV et JSON.
La logique de détection automatique déduit les types de champs du schéma en lisant les 500 premières lignes de données. Les lignes initiales sont ignorées si l'option --skip_leading_rows
est présente. Les types de champs sont basés sur les lignes qui comptent le plus de champs.
Par conséquent, la détection automatique devrait fonctionner comme prévu tant qu'il existe au moins une ligne de données comportant des valeurs dans chaque colonne/champ.
La détection automatique de schéma n'est pas utilisée avec les fichiers Avro, Parquet ou ORC, et les fichiers d'exportation Firestore ou Datastore. Lorsque vous chargez ces fichiers dans BigQuery, le schéma de la table est automatiquement extrait des données sources auto-descriptives.
Pour utiliser la détection automatique de schéma lorsque vous chargez des données JSON ou CSV, procédez comme suit :
Console
Dans la console Google Cloud, accédez à la page "BigQuery".
Dans le panneau Explorer, développez votre projet et sélectionnez un ensemble de données.
Développez l'option
Actions puis cliquez sur Ouvrir.Dans le panneau de détails, cliquez sur Créer une table
.Dans la section Source de la page Créer une table :
- Dans le champ Create table from (Créer une table à partir de), sélectionnez le type de source souhaité.
Dans le champ source, recherchez le fichier ou le bucket Cloud Storage, ou saisissez l'URI Cloud Storage. Sachez que vous ne pouvez pas inclure plusieurs URI dans la console Google Cloud. En revanche, les caractères génériques sont acceptés. Le bucket Cloud Storage doit se trouver au même emplacement que l'ensemble de données contenant la table que vous créez.
Dans le champ File format (Format de fichier), sélectionnez CSV ou JSON.
Dans la section Destination de la page Créer une table :
Sous Dataset name (Nom de l'ensemble de données), sélectionnez l'ensemble de données approprié.
Dans le champ Nom de la table, saisissez le nom de la table que vous créez.
Vérifiez que Type de table est défini sur Table native.
Cliquez sur Create table (Créer une table).
bq
Exécutez la commande bq load
avec le paramètre --autodetect
.
(Facultatif) Spécifiez l'option --location
et définissez la valeur correspondant à votre emplacement.
La commande suivante charge un fichier à l'aide de la détection automatique de schéma :
bq --location=LOCATION load \ --autodetect \ --source_format=FORMAT \ DATASET.TABLE \ PATH_TO_SOURCE
Remplacez l'élément suivant :
LOCATION
: nom de votre emplacement. L'option--location
est facultative. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, définissez la valeur de l'option surasia-northeast1
. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc.FORMAT
:NEWLINE_DELIMITED_JSON
ouCSV
.DATASET
: ensemble de données contenant la table dans laquelle vous chargez des données.TABLE
: nom de la table dans laquelle vous chargez des données.PATH_TO_SOURCE
: correspond à l'emplacement du fichier de données CSV ou JSON.
Exemples :
Saisissez la commande suivante pour charger myfile.csv
à partir de votre ordinateur local dans une table nommée mytable
stockée dans un ensemble de données nommé mydataset
.
bq load --autodetect --source_format=CSV mydataset.mytable ./myfile.csv
Saisissez la commande suivante pour charger myfile.json
à partir de votre ordinateur local dans une table nommée mytable
stockée dans un ensemble de données nommé mydataset
.
bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable ./myfile.json
API
Créez une tâche
load
qui pointe vers les données sources. Pour en savoir plus sur la création de tâches, consultez la page Exécuter des tâches de manière automatisée. Spécifiez l'emplacement dans la propriétélocation
de la sectionjobReference
.Spécifiez le format de données en définissant la propriété
sourceFormat
. Pour utiliser la détection automatique de schéma, cette valeur doit être définie surNEWLINE_DELIMITED_JSON
ouCSV
.Utilisez la propriété
autodetect
pour définir la détection automatique de schéma surtrue
.
Accéder
Avant d'essayer cet exemple, suivez les instructions de configuration pour Go 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 Go.
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.
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.
PHP
Avant d'essayer cet exemple, suivez les instructions de configuration pour PHP 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 PHP.
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
Pour activer la détection automatique de schéma, définissez la propriété LoadJobConfig.autodetect sur True
.
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.
Ruby
Avant d'essayer cet exemple, suivez les instructions de configuration pour Ruby 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 Ruby.
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.
Détection automatique de schéma pour les sources de données externes
La détection automatique de schéma peut être utilisée avec des sources de données externes CSV, JSON et Google Sheets. Lorsque la détection automatique de schéma est activée, BigQuery tente le mieux possible de déduire automatiquement le schéma à partir des données sources. Si vous n'activez pas la détection automatique de schéma pour ces sources, vous devez fournir un schéma explicite.
Vous n'avez pas besoin d'activer la détection automatique de schéma lorsque vous interrogez des fichiers d'exportation Avro, Parquet, ORC, Firestore ou Datastore externes. Ces formats de fichiers étant autodescriptifs, BigQuery déduit automatiquement le schéma de la table à partir des données sources. Pour les fichiers Parquet, Avro et Orc, vous pouvez éventuellement fournir un schéma explicite pour remplacer le schéma déduit.
À l'aide de la console Google Cloud, vous pouvez activer la détection automatique de schéma en cochant l'option Schéma et paramètres d'entrée pour la Détection automatique.
L'outil de ligne de commande bq vous permet d'activer la détection automatique de schéma lorsque vous créez un fichier de définition de table pour les données CSV, JSON ou Google Sheets. Lorsque vous utilisez l'outil bq pour créer un fichier de définition de table, transmettez l'option --autodetect
à la commande mkdef
pour activer la détection automatique de schéma ou transmettez l'option --noautodetect
pour désactiver la détection automatique.
Lorsque vous utilisez l'option --autodetect
, le paramètre autodetect
est défini sur true
dans le fichier de définition de table. Lorsque vous utilisez l'option --noautodetect
, le paramètre autodetect
est défini sur false
dans le fichier de définition de table. Si vous ne fournissez pas de définition de schéma pour la source de données externe lorsque vous créez une définition de table et que vous n'utilisez pas l'option --noautodetect
ou --autodetect
, le paramètre autodetect
est défini par défaut sur true
.
Lorsque vous créez un fichier de définition de table à l'aide de l'API, définissez la valeur de la propriété autodetect
sur true
ou false
. Si la propriété autodetect
est définie sur true
, la détection automatique est activée. Si la propriété autodetect
est définie sur false
, la détection automatique est désactivée.
Détails de la détection automatique
En plus de détecter les détails du schéma, la détection automatique reconnaît les éléments suivants :
Compression
BigQuery reconnaît la compression de fichier compatible avec gzip lors de l'ouverture d'un fichier.
Valeurs de date et d'heure
BigQuery détecte les valeurs de date et d'heure en fonction de la mise en forme des données sources.
Les valeurs des colonnes DATE
doivent respecter le format suivant : YYYY-MM-DD
.
Les valeurs des colonnes TIME
doivent être au format suivant : HH:MM:SS[.SSSSSS]
(le composant fractionnaire d'une seconde est facultatif).
Pour les colonnes TIMESTAMP
, BigQuery détecte un large éventail de formats d'horodatage, y compris, mais sans s'y limiter :
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSSSSS
YYYY/MM/DD HH:MM
Un horodatage peut également contenir un décalage UTC ou l'indicateur de zone UTC ("Z").
Voici quelques exemples de valeurs que BigQuery détecte automatiquement en tant que valeurs d'horodatage :
- 2018-08-19 12:11
- 2018-08-19 12:11:35.22
- 2018/08/19 12:11
- 2018-08-19 07:11:35.220 -05:00
Si BigQuery ne reconnaît pas le format, il charge la colonne en tant que type de données de chaîne. Dans ce cas, vous devrez peut-être prétraiter les données sources avant de les charger. Par exemple, si vous exportez des données CSV à partir d'une feuille de calcul, définissez le format de date afin qu'il corresponde à l'un des exemples présentés ci-dessous. Vous pouvez également transformer les données après leur chargement dans BigQuery.
Détection automatique de schéma pour les données CSV
Délimiteur de fichier CSV
BigQuery détecte les délimiteurs suivants :
- Virgule ( , )
- Barre verticale ( | )
- Tabulation ( \t )
En-tête CSV
BigQuery déduit les en-têtes en comparant la première ligne du fichier avec ses autres lignes. Si la première ligne ne contient que des chaînes et que les autres lignes contiennent d'autres types de données, BigQuery suppose que la première ligne est une ligne d'en-tête. BigQuery attribue des noms de colonnes en fonction des noms de champs dans la ligne d'en-tête. Les noms peuvent être modifiés pour répondre aux règles de dénomination des colonnes de BigQuery. Par exemple, les espaces seront remplacés par des traits de soulignement.
Sinon, BigQuery considère que la première ligne est une ligne de données et attribue des noms de colonnes génériques tels que string_field_1
. Notez qu'une fois la table créée, les noms de colonne ne peuvent pas être mis à jour dans le schéma, mais vous pouvez les modifier manuellement après la création de la table. Une autre option consiste à fournir un schéma explicite au lieu d'utiliser la détection automatique.
Vous pouvez avoir un fichier CSV avec une ligne d'en-tête, où tous les champs de données sont des chaînes. Dans ce cas, BigQuery ne détecte pas automatiquement que la première ligne est un en-tête. Utilisez l'option --skip_leading_rows
pour ignorer la ligne d'en-tête. Sinon, l'en-tête sera importé en tant que ligne de données. Pensez également à fournir un schéma explicite dans ce cas, afin de pouvoir attribuer des noms de colonne.
Lignes entre guillemets de fichier CSV
BigQuery détecte les nouvelles lignes entre guillemets dans un champ CSV et ne les interprète pas comme une limite de ligne.
Détection automatique de schéma pour les données JSON
Champs JSON imbriqués et répétés
BigQuery infère les champs imbriqués et répétés dans les fichiers JSON. Si une valeur de champ est un objet JSON, BigQuery charge la colonne sous la forme d'un type RECORD
. Si une valeur de champ est un tableau, BigQuery charge la colonne en tant que colonne répétée. Pour obtenir un exemple de données JSON avec des données imbriquées et répétées, consultez la section Charger des données JSON imbriquées et répétées.
Conversion de chaîne
Si vous activez la détection automatique de schéma, BigQuery convertit les chaînes en types booléens, numériques ou de date/heure, lorsque cela est possible. Par exemple, en utilisant les données JSON suivantes, la détection automatique de schéma convertit le champ id
en une colonne INTEGER
:
{ "name":"Alice","id":"12"}
{ "name":"Bob","id":"34"}
{ "name":"Charles","id":"45"}
Pour en savoir plus, consultez la page Charger des données JSON à partir de Cloud Storage.
Détection automatique de schéma pour Google Sheets
Pour Sheets, BigQuery détecte automatiquement si la première ligne est une ligne d'en-tête, comme pour les fichiers CSV. Si la première ligne est identifiée comme étant un en-tête, BigQuery attribue des noms de colonne en fonction des noms de champs dans la ligne d'en-tête et ignore la ligne. Les noms peuvent être modifiés pour répondre aux règles de dénomination des colonnes de BigQuery. Par exemple, les espaces seront remplacés par des traits de soulignement.
Sécurité des tables
Pour savoir comment contrôler l'accès aux tables dans BigQuery, consultez la page Présentation des contrôles d'accès aux tables.