Spécifier un schéma
BigQuery vous permet de spécifier le schéma d'une table lorsque vous chargez des données dans une table et lorsque vous créez une table vide. Vous pouvez également utiliser la détection automatique de schéma pour les formats de données compatibles.
Lorsque vous chargez des fichiers d'exportation Avro, Parquet, ORC, Firestore ou Datastore, le schéma est automatiquement extrait des données sources auto-descriptives.
Vous pouvez spécifier le schéma d'une table des manières suivantes :
- Spécifiez le schéma manuellement :
- Utiliser la console
- de façon intégrée à l'aide de l'outil de ligne de commande
bq
. - Utilisez l'instruction SQL
CREATE TABLE
.
- Créez un fichier de schéma au format JSON.
- Appelez la méthode
jobs.insert
et configurez la propriétéschema
dans la configuration de la tâcheload
. - Appelez la méthode
tables.insert
et configurez le schéma dans la ressource de table à l'aide de la propriétéschema
.
Une fois les données chargées ou une table vide créée, vous pouvez modifier la définition de schéma de la table.
Composants du schéma
Lorsque vous spécifiez un schéma de table, vous devez fournir le nom et le type de données de chaque colonne. Vous pouvez également fournir la description et le mode d'une colonne.
Noms de colonne
Un nom de colonne ne peut contenir que des lettres (a-z, A-Z), des chiffres (0-9) et des traits de soulignement (_), et doit commencer par une lettre ou un trait de soulignement. Sa longueur maximale est de 300 caractères. Un nom de colonne ne peut utiliser aucun des préfixes suivants :
_TABLE_
_FILE_
_PARTITION
_ROW_TIMESTAMP
__ROOT__
_COLIDENTIFIER
Les noms de colonnes en double ne sont pas autorisés, même si la casse est différente. Par exemple, la colonne Column1
est considérée comme identique à la colonne column1
.
Descriptions de colonne
Chaque colonne peut inclure une description facultative. La description est une chaîne qui ne doit pas contenir plus de 1 024 caractères.
Types de données du langage SQL standard
Le langage SQL standard de BigQuery vous permet de spécifier les types de données suivants dans votre schéma. Le type de données est un champ obligatoire.
Nom | Type de données | Description |
---|---|---|
Entier | INT64 |
Valeurs numériques sans composants fractionnaires |
Virgule flottante | FLOAT64 |
Valeurs numériques approximatives avec composants fractionnaires |
Numérique | NUMERIC |
Valeurs numériques exactes avec composants fractionnaires |
BigNumeric | BIGNUMERIC |
Valeurs numériques exactes avec composants fractionnaires |
Booléen | BOOL |
TRUE ou FALSE (non sensible à la casse) |
Chaîne | STRING |
Données de type caractères de longueur variable (Unicode) |
Octets | BYTES |
Données binaires de longueur variable |
Date | DATE |
Date de calendrier logique |
Date/Heure | DATETIME |
Année, mois, jour, heure, minute, seconde et milliseconde |
Time | TIME |
Heure indépendante d'une date précise |
Temporel | TIMESTAMP |
Point absolu dans le temps avec spécification de la microseconde |
Type "Struct (Record)" | STRUCT |
Conteneur de champs triés ayant chacun un type (obligatoire) et un nom de champ (facultatif) |
Géographie | GEOGRAPHY |
Ensemble de points sur la surface de la Terre (ensemble de points, de lignes et de polygones sur le sphéroïde de référence WGS84 avec des arêtes géodésiques) |
Pour en savoir plus sur les types de données en SQL standard, consultez la page Types de données en SQL standard.
Vous pouvez également spécifier un type de tableau lorsque vous interrogez des données. Pour plus d'informations, consultez la section Utiliser des tableaux.
Modes
BigQuery accepte les modes suivants pour les colonnes. Le mode est un champ facultatif. Si le mode n'est pas spécifié, la valeur par défaut de la colonne est NULLABLE
.
Mode | Description |
---|---|
Nullable | Colonne autorisant les valeurs NULL (paramètre par défaut) |
Obligatoire | Valeurs NULL non autorisées |
Répété | Colonne contenant un tableau de valeurs du type spécifié |
Pour en savoir plus sur les modes, consultez la ligne mode
de la section TableFieldSchema
.
Spécifier les schémas manuellement
Lorsque vous chargez des données ou créez une table vide, vous pouvez spécifier manuellement le schéma de la table à l'aide de la console ou de l'outil de ligne de commande bq
. La spécification manuelle d'un schéma est acceptée pour le chargement des fichiers CSV et JSON (délimité par une nouvelle ligne). Lorsque vous chargez des données d'exportation Avro, Parquet, ORC, Firestore ou Datastore, le schéma est automatiquement extrait des données sources auto-descriptives.
Pour spécifier manuellement un schéma de table, procédez comme suit :
Console
Dans la console, vous pouvez spécifier un schéma à l'aide de l'option Ajouter un champ ou de l'option Modifier sous forme de texte.
Dans la console, ouvrez la page "BigQuery".
Dans le panneau Explorateur, 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, sélectionnez Table vide.
Dans la section Destination de la page Créer une table :
Sous 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 Table type (Type de table) est défini sur Native table (Table native).
Dans la section Schema (Schéma), saisissez la définition du schéma.
- Option 1 : Cliquez sur Ajouter un champ, puis définissez les valeurs Nom, Type et Mode de chaque champ. Dans la console, vous ne pouvez pas ajouter de description de champ lorsque vous utilisez l'option Ajouter un champ. Toutefois, vous pouvez ajouter des descriptions de champ manuellement une fois vos données chargées.
- Option 2 : Cliquez sur Modifier sous forme de texte et collez le schéma sous la forme d'un tableau JSON. Lorsque vous utilisez un tableau JSON, vous générez le schéma en utilisant le même processus que pour la création d'un fichier de schéma JSON.
Cliquez sur Create table.
SQL
Utilisez l'instruction CREATE TABLE
.
Spécifiez le schéma en utilisant l'option column.
L'instruction suivante crée une table nommée newtable
avec des colonnes x, y et z dont les types sont respectivement "integer" (entier), "string" (chaîne) et "boolean" (booléen) :
Dans la console, accédez à la page BigQuery.
Dans l'éditeur de requête, saisissez l'instruction suivante :
CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRING, z BOOL) OPTIONS( description = 'My example table');
Cliquez sur
Exécuter.
Pour en savoir plus sur l'exécution des requêtes, consultez la page Exécuter des requêtes interactives.
bq
Fournissez manuellement le schéma intégré au format field:data_type,field:data_type
en utilisant l'une des commandes suivantes :
- Si vous chargez des données, utilisez la commande
bq load
. - Si vous créez une table vide, utilisez la commande
bq mk
.
Lorsque vous spécifiez le schéma sur la ligne de commande, vous ne pouvez pas inclure de type RECORD
(STRUCT
) ni de description de colonne. Vous ne pouvez pas non plus spécifier le mode de la colonne. Tous les modes par défaut sont définis comme NULLABLE
. Pour inclure des descriptions, des modes et des types RECORD
, fournissez plutôt un fichier de schéma JSON.
Pour charger des données dans une table à l'aide d'une définition de schéma intégrée, entrez la commande load
et spécifiez le format de données à l'aide de l'option --source_format
.
Si vous chargez des données dans une table d'un projet autre que votre projet par défaut, ajoutez l'ID du projet au format suivant : project_id:dataset.table_name
.
(Facultatif) Spécifiez l'option --location
et définissez la valeur correspondant à votre emplacement.
bq --location=location load \ --source_format=format \ project_id:dataset.table_name \ path_to_source \ schema
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, vous pouvez définir 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
.project_id
: ID de votre projet.dataset
: ensemble de données contenant la table dans laquelle vous chargez des données.table_name
: nom de la table dans laquelle vous chargez des données.path_to_source
: emplacement du fichier de données CSV ou JSON sur votre ordinateur local ou dans Cloud Storage.schema
: définition de schéma intégrée.
Exemple :
Saisissez la commande suivante pour charger des données à partir d'un fichier CSV local nommé myfile.csv
dans mydataset.mytable
dans le projet par défaut. Le schéma est spécifié manuellement de façon intégrée :
bq load \
--source_format=CSV \
mydataset.mytable \
./myfile.csv \
qtr:STRING,sales:FLOAT,year:STRING
Pour en savoir plus sur le chargement de données dans BigQuery, consultez la page Présentation du chargement des données.
Pour spécifier une définition de schéma intégrée lorsque vous créez une table vide, saisissez la commande bq mk
avec l'option --table
ou -t
. Si vous créez une table dans un projet autre que votre projet par défaut, ajoutez l'ID du projet à la commande au format suivant : project_id:dataset.table
.
bq mk --table project_id:dataset.table schema
Remplacez les éléments suivants :
project_id
: ID de votre projet.dataset
: un ensemble de données de votre projet.table
: nom de la table que vous créez.schema
: une définition de schéma intégrée.
Par exemple, la commande suivante crée une table vide nommée mytable
dans votre projet par défaut. Le schéma est spécifié manuellement de façon intégrée.
bq mk --table mydataset.mytable qtr:STRING,sales:FLOAT,year:STRING
Pour en savoir plus sur la création d'une table vide, consultez la section Créer une table vide avec une définition de schéma.
C#
Pour spécifier le schéma d'une table lorsque vous chargez des données dans une table :
Pour spécifier un schéma lorsque vous créez une table vide :
Go
Pour spécifier le schéma d'une table lorsque vous chargez des données dans une table :
Pour spécifier un schéma lorsque vous créez une table vide :
Java
Pour spécifier le schéma d'une table lorsque vous chargez des données dans une table :
Pour spécifier un schéma lorsque vous créez une table vide :
Python
Pour spécifier le schéma d'une table lorsque vous chargez des données dans une table, configurez la propriété LoadJobConfig.schema.
Pour spécifier un schéma lorsque vous créez une table vide, configurez la propriété Table.schema.
Spécifier un fichier de schéma JSON
Si vous le souhaitez, vous pouvez spécifier le schéma à l'aide d'un fichier de schéma JSON au lieu d'utiliser une définition de schéma intégrée. Un fichier de schéma JSON consiste en un tableau JSON contenant les éléments suivants :
- (Facultatif) Description de la colonne
- Nom de la colonne
- Type de données de la colonne
- (Facultatif) Mode de la colonne (si aucune valeur n'est spécifiée, le mode par défaut est
NULLABLE
)
Créer un fichier de schéma JSON
Pour créer un fichier de schéma JSON, entrez les informations suivantes à l'aide d'un éditeur de texte approprié :
[ { "description": "[DESCRIPTION]", "name": "[NAME]", "type": "[TYPE]", "mode": "[MODE]" }, { "description": "[DESCRIPTION]", "name": "[NAME]", "type": "[TYPE]", "mode": "[MODE]" } ]
Le tableau JSON est indiqué par les crochets de début et de fin []
. Chaque entrée de colonne doit être séparée par une virgule : },
.
Vous pouvez écrire un schéma de table existant dans un fichier local en saisissant la commande suivante :
bq show \ --schema \ --format=prettyjson \ project_id:dataset.table > path_to_file
Vous pouvez utiliser le fichier de sortie comme point de départ pour votre propre fichier de schéma JSON. Si vous utilisez cette approche, assurez-vous que le fichier ne contient que le tableau JSON qui représente le schéma de la table.
Par exemple, le tableau JSON suivant représente un schéma de table de base. Ce schéma comporte trois colonnes : qtr
(REQUIRED
STRING
), rep
(NULLABLE
STRING
) et sales
(NULLABLE
FLOAT
).
[ { "description": "quarter", "mode": "REQUIRED", "name": "qtr", "type": "STRING" }, { "description": "sales representative", "mode": "NULLABLE", "name": "rep", "type": "STRING" }, { "description": "total sales", "mode": "NULLABLE", "name": "sales", "type": "FLOAT" } ]
Utiliser un fichier de schéma JSON
Une fois le fichier de schéma JSON créé, vous pouvez le spécifier à l'aide de l'outil de ligne de commande bq
.
Vous ne pouvez pas utiliser de fichier de schéma avec la console ou l'API.
Fournissez manuellement le fichier de schéma :
- Si vous chargez des données, utilisez la commande
bq load
. - Si vous créez une table vide, utilisez la commande
bq mk
.
Lorsque vous fournissez un fichier de schéma JSON, il doit être stocké dans un emplacement local accessible en lecture. Vous ne pouvez pas spécifier un fichier de schéma JSON stocké dans Cloud Storage ou Drive.
Spécifier un fichier de schéma lors du chargement de données
La commande suivante charge des données dans une table à l'aide de la définition de schéma d'un fichier JSON :
bq --location=location load \ --source_format=format \ project_id:dataset.table \ path_to_data_file \ path_to_schema_file
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, vous pouvez définir la valeur de l'option surasia-northeast1
. Vous pouvez définir une valeur par défaut correspondant à l'emplacement à l'aide du fichier .bigqueryrc.format
:NEWLINE_DELIMITED_JSON
ouCSV
.project_id
: ID de votre projet.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_data_file
: emplacement du fichier de données CSV ou JSON sur votre ordinateur local ou dans Cloud Storage.path_to_schema_file
: chemin d'accès du fichier de schéma sur votre ordinateur local.
Exemple :
Saisissez la commande suivante pour charger des données à partir d'un fichier CSV local nommé myfile.csv
dans mydataset.mytable
dans le projet par défaut. Le schéma est spécifié dans myschema.json
dans le répertoire actuel.
bq load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json
Spécifier un fichier de schéma lors de la création d'une table
La commande suivante crée une table vide dans un ensemble de données existant en utilisant la définition de schéma d'un fichier JSON :
bq mk --table project_id:dataset.table path_to_schema_file
Remplacez les éléments suivants :
project_id
: ID de votre projet.dataset
: un ensemble de données de votre projet.table
: nom de la table que vous créez.path_to_schema_file
: chemin d'accès du fichier de schéma sur votre ordinateur local.
Par exemple, la commande suivante crée une table nommée mytable
dans mydataset
dans votre projet par défaut. Le schéma est spécifié dans myschema.json
dans le répertoire actuel :
bq mk --table mydataset.mytable ./myschema.json
Spécifier un schéma dans l'API
Spécifiez un schéma de table à l'aide de l'API :
Pour spécifier un schéma lorsque vous chargez des données, appelez la méthode
jobs.insert
et configurez la propriétéschema
dans la ressourceJobConfigurationLoad
.Pour spécifier un schéma lorsque vous créez une table, appelez la méthode
tables.insert
et configurez la propriétéschema
dans la ressourceTable
.
La spécification d'un schéma à l'aide de l'API est similaire au processus de création d'un fichier de schéma JSON.
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.
Étapes suivantes
- Découvrez comment spécifier des colonnes imbriquées et répétées dans une définition de schéma.
- Consultez la section sur la détection automatique de schéma.
- Découvrez comment charger des données dans BigQuery.
- Découvrez comment créer et utiliser des tables.