Créer un fichier de définition de table pour une source de données externe

Cette page explique comment créer un fichier de définition de table pour une source de données externe. Une source de données externe peut être interrogée directement, même si les données ne sont pas stockées dans BigQuery.

Un fichier de définition de table contient la définition de schéma et les métadonnées d'une table externe (format de données de la table et propriétés associées, par exemple). Lorsque vous créez un fichier de définition de table, vous pouvez utiliser la fonctionnalité de détection automatique des schémas pour définir le schéma d'une source de données externe. Vous avez la possibilité de spécifier le schéma sur la ligne de commande ou de fournir un fichier JSON contenant la définition de schéma.

Les fichiers de définition de table sont utilisés avec l'outil de ligne de commande bq. Les propriétés d'un fichier de définition de table s'appliquent également à la création d'une configuration ExternalDataConfiguration lorsque vous utilisez l'API REST. Vous n'utilisez pas de fichiers de définition de table lorsque vous créez une table externe à l'aide de la console Google Cloud.

Vous pouvez créer des fichiers de définition de table pour décrire une table externe permanente ou temporaire pour les sources de données externes suivantes :

Cloud Storage
- CSV (Comma-Separated Values)
- JSON délimité par des retours à la ligne
- Fichiers Avro
- Fichiers d'exportation Datastore
- Fichiers ORC
- Fichiers Parquet
- Fichiers d'exportation Firestore
Google Drive
- CSV (Comma-Separated Values)
- JSON délimité par des retours à la ligne
- Fichiers Avro
- Google Sheets
Bigtable

Avant de commencer

Pour créer un fichier de définition de table, vous avez besoin de l'URI de votre source de données :

Pour une source de données Drive, vous avez besoin de l'URI Drive.
Pour une source de données Cloud Storage, vous avez besoin de l'URI Cloud Storage.
Pour une source de données Bigtable, vous avez besoin de l'URI Bigtable.

Créer un fichier de définition pour les fichiers CSV, JSON ou Google Sheets

Utilisez l'une des méthodes suivantes pour créer un fichier de définition de table pour des fichiers CSV, JSON ou Google Sheets dans Cloud Storage ou Drive :

Utiliser l'option autodetect
Utiliser un schéma intégré
Utiliser un fichier de schéma JSON

Utilisez l'option `autodetect`.

Si vous spécifiez un fichier CSV, JSON ou Google Sheets sans inclure de description de schéma intégrée ni de fichier de schéma, vous pouvez utiliser l'indicateur --autodetect pour définir l'option "autodetect" sur true dans le fichier de définition de table. Lorsque la détection automatique est activée, BigQuery tente le mieux possible de déduire automatiquement le schéma. Pour en savoir plus, consultez la section Détection automatique de schéma pour les sources de données externes.

Utiliser la détection automatique avec une source de données Cloud Storage

Créez un fichier de définition de table pour une source de données Cloud Storage :

Exécutez la commande bq mkdef avec l'option --autodetect pour créer un fichier de définition de table. La commande mkdef génère un fichier de définition de table au format JSON. L'exemple suivant crée une définition de table et écrit la sortie dans un fichier : /tmp/file_name.
```
bq mkdef \
  --autodetect \
  --source_format=SOURCE_FORMAT \
  "URI" > /tmp/FILE_NAME
```
Remplacez les éléments suivants :
- SOURCE_FORMAT : format de votre fichier
- FILE_NAME : nom de votre fichier de définition de table
- URI : URI Cloud Storage
  
  Par exemple, gs://mybucket/myfile.
(Facultatif) Ouvrez le fichier de définition de table dans un éditeur de texte. Par exemple, la commande nano /tmp/file_name ouvre le fichier dans nano. Le fichier doit se présenter comme illustré ci-dessous pour une source de données externe CSV. Notez que "autodetect" est défini sur true.
```
{
"autodetect": true,
"csvOptions": {
  "allowJaggedRows": false,
  "allowQuotedNewlines": false,
  "encoding": "UTF-8",
  "fieldDelimiter": ",",
  "quote": "\"",
  "skipLeadingRows": 0
},
"sourceFormat": "CSV",
"sourceUris": [
  "URI"
]
}
```
(Facultatif) Modifiez manuellement le fichier de définition de table pour modifier, ajouter ou supprimer des paramètres généraux, tels que maxBadRecords et ignoreUnknownValues. Aucun paramètre de configuration n'est propre aux fichiers source JSON, mais certains paramètres s'appliquent aux fichiers CSV et Google Sheets. Pour en savoir plus, reportez-vous à la propriété ExternalDataConfiguration dans la documentation de référence de l'API.

Utiliser la détection automatique avec une source de données Drive

Créez un fichier de définition de table pour une source de données Drive :

Exécutez la commande bq mkdef avec l'option --autodetect pour créer une définition de table. La commande mkdef génère un fichier de définition de table au format JSON. L'exemple suivant crée une définition de table et écrit la sortie dans un fichier : /tmp/file_name.
```
bq mkdef \
   --autodetect \
   --source_format=SOURCE_FORMAT \
   "URI" > /tmp/FILE_NAME
```
Remplacez les éléments suivants :
- SOURCE_FORMAT : format de votre fichier
- FILE_NAME : nom de votre fichier de définition de table
- URI : URI Drive
  
  Par exemple, https://drive.google.com/open?id=123ABCD123AbcD123Abcd.
Ouvrez le fichier de définition de table dans un éditeur de texte. Par exemple, la commande nano /tmp/file_name ouvre le fichier dans nano. Le fichier doit se présenter comme illustré ci-dessous pour une source de données externe Google Sheets. Notez que "autodetect" est défini sur true.
```
{
"autodetect": true,
"sourceFormat": "GOOGLE_SHEETS",
"sourceUris": [
  "URI"
]
}
```
(Facultatif) Modifiez manuellement le fichier de définition de table pour modifier, ajouter ou supprimer des paramètres généraux, tels que maxBadRecords et ignoreUnknownValues. Aucun paramètre de configuration n'est propre aux fichiers source JSON, mais certains paramètres s'appliquent aux fichiers CSV et Google Sheets. Pour en savoir plus, reportez-vous à la propriété ExternalDataConfiguration dans la documentation de référence de l'API.
Pour spécifier une feuille spécifique ou une plage de cellules dans un fichier Google Sheets, ajoutez la propriété range au fichier de définition de table. Pour interroger une feuille spécifique, indiquez son nom. Pour interroger une plage de cellules, spécifiez-la au format suivant : sheet_name!top_left_cell_id:bottom_right_cell_id, par exemple, "Sheet1!A1:B20". Si le paramètre range n'est pas spécifié, la première feuille du fichier est utilisée.

Utiliser un schéma intégré

Si vous ne souhaitez pas utiliser la fonctionnalité de détection automatique des schémas, vous pouvez créer un fichier de définition de table en spécifiant une définition de schéma sur la ligne de commande. Pour fournir une définition de schéma intégrée, répertoriez les champs et les types de données sur la ligne de commande au format suivant : FIELD:DATA_TYPE,FIELD:DATA_TYPE.

Utiliser un schéma intégré avec une source de données Cloud Storage ou Drive

Créez une définition de table pour une source de données Cloud Storage ou Drive à l'aide d'une définition de schéma intégrée :

Exécutez la commande bq mkdef avec l'option --noautodetect pour créer une définition de table. La commande mkdef génère un fichier de définition de table au format JSON. L'exemple suivant crée une définition de table et écrit la sortie dans un fichier : /tmp/file_name.
```
bq mkdef \
  --noautodetect \
  --source_format=SOURCE_FORMAT \
  "URI" \
  FIELD:DATA_TYPE,FIELD:DATA_TYPE > /tmp/FILE_NAME
```
Remplacez les éléments suivants
- SOURCE_FORMAT : format du fichier source
- URI : URI Cloud Storage ou votre URI Drive
  
  Par exemple, gs://mybucket/myfile pour Cloud Storage ou https://drive.google.com/open?id=123ABCD123AbcD123Abcd pour Drive
- FIELD:DATA_TYPE,FIELD:DATA_TYPE : définition de schéma
  
  Par exemple, Name:STRING,Address:STRING, ....
- FILE_NAME : nom de votre fichier de définition de table
(Facultatif) Ouvrez le fichier de définition de table dans un éditeur de texte. Par exemple, la commande nano /tmp/file_name ouvre le fichier dans nano. Le fichier doit se présenter comme illustré ci-dessous. Notez que l'option "autodetect" n'est pas activée, et que les informations de schéma sont écrites dans le fichier de définition de table.
```
{
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "NEWLINE_DELIMITED_JSON",
"sourceUris": [
  "URI"
]
}
```
(Facultatif) Modifiez manuellement le fichier de définition de table pour modifier, ajouter ou supprimer des paramètres généraux, tels que maxBadRecords et ignoreUnknownValues. Aucun paramètre de configuration n'est propre aux fichiers source JSON, mais certains paramètres s'appliquent aux fichiers CSV et Google Sheets. Pour en savoir plus, reportez-vous à la propriété ExternalDataConfiguration dans la documentation de référence de l'API.

Utiliser un fichier de schéma JSON

Si vous ne souhaitez pas utiliser la fonctionnalité de détection automatique ni spécifier de définition de schéma sur la ligne de commande, vous pouvez créer un fichier de schéma JSON et le référencer lors de la création de votre fichier de définition de table. Créez le fichier de schéma JSON manuellement sur votre ordinateur local. Il est impossible de référencer un fichier de schéma JSON stocké dans Cloud Storage ou Google Drive.

Utiliser un fichier de schéma avec une source de données Cloud Storage

Créez une définition de table pour une source de données Cloud Storage à l'aide d'un fichier de schéma JSON :

Exécutez la commande bq mkdef avec l'option --noautodetect pour créer une définition de table. La commande mkdef génère un fichier de définition de table au format JSON. L'exemple suivant crée une définition de table et écrit la sortie dans un fichier : /tmp/file_name.
```
bq mkdef \
   --noautodetect \
   --source_format=SOURCE_FORMAT \
   "URI" \
  PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME
```
Remplacez les éléments suivants :
- SOURCE_FORMAT : format de votre fichier
- FILE_NAME : nom de votre fichier de définition de table
- URI : URI Cloud Storage
  
  Par exemple, gs://mybucket/myfile.
- PATH_TO_SCHEMA_FILE : emplacement du fichier de schéma JSON sur votre ordinateur local
(Facultatif) Ouvrez le fichier de définition de table dans un éditeur de texte. Par exemple, la commande nano /tmp/file_name ouvre le fichier dans
nano. Le fichier doit se présenter comme illustré ci-dessous. Notez que l'option "autodetect" n'est pas activée, et que les informations de schéma sont écrites dans le fichier de définition de table.
```
{
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "NEWLINE_DELIMITED_JSON",
"sourceUris": [
  "URI"
]
}
```
(Facultatif) Modifiez manuellement le fichier de définition de table pour modifier, ajouter ou supprimer des paramètres généraux, tels que maxBadRecords et ignoreUnknownValues. Aucun paramètre de configuration n'est propre aux fichiers source JSON, mais certains paramètres s'appliquent aux fichiers CSV et Google Sheets. Pour en savoir plus, reportez-vous à la propriété ExternalDataConfiguration dans la documentation de référence de l'API.

Utiliser un fichier de schéma avec une source de données Drive

Créez une définition de table pour une source de données Drive à l'aide d'un fichier de schéma JSON :

Exécutez la commande bq mkdef avec l'option --noautodetect pour créer une définition de table. La commande mkdef génère un fichier de définition de table au format JSON. L'exemple suivant crée une définition de table et écrit la sortie dans un fichier : /tmp/file_name.
```
bq mkdef \
   --noautodetect \
   --source_format=source_format \
   "URI" \
   PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME
```
Remplacez les éléments suivants :
- SOURCE_FORMAT : format du fichier source
- URI : URI Drive
  
  Par exemple, https://drive.google.com/open?id=123ABCD123AbcD123Abcd.
- PATH_TO_SCHEMA_FILE : emplacement du fichier de schéma JSON sur votre ordinateur local
- FILE_NAME : nom de votre fichier de définition de table
Ouvrez le fichier de définition de table dans un éditeur de texte. Par exemple, la commande nano /tmp/file_name ouvre le fichier dans nano. Le fichier doit se présenter comme illustré ci-dessous. Notez que l'option "autodetect" n'est pas activée, et que les informations de schéma sont écrites dans le fichier de définition de table.
```
{
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "GOOGLE_SHEETS",
"sourceUris": [
  "URI"
]
}
```
(Facultatif) Modifiez manuellement le fichier de définition de table pour modifier, ajouter ou supprimer des paramètres généraux, tels que maxBadRecords et ignoreUnknownValues. Aucun paramètre de configuration n'est propre aux fichiers source JSON, mais certains paramètres s'appliquent aux fichiers CSV et Google Sheets. Pour en savoir plus, reportez-vous à la propriété ExternalDataConfiguration dans la documentation de référence de l'API.
Pour spécifier une feuille spécifique ou une plage de cellules dans un fichier Google Sheets, ajoutez la propriété range au fichier de définition de table. Pour interroger une feuille spécifique, indiquez son nom. Pour interroger une plage de cellules, spécifiez-la au format suivant : sheet_name!top_left_cell_id:bottom_right_cell_id, par exemple, "Sheet1!A1:B20". Si le paramètre range n'est pas spécifié, la première feuille du fichier est utilisée.

Créer un fichier de définition pour les formats autodescriptifs

Les formats Avro, Parquet et ORC sont des formats autodescriptif. Les fichiers de données respectant ces formats contiennent leurs propres informations de schéma. Si vous utilisez l'un de ces formats en tant que source de données externe, BigQuery récupère automatiquement le schéma à l'aide des données sources. Lorsque vous créez une définition de table, vous n'avez pas besoin d'utiliser la détection automatique de schéma et vous n'avez pas besoin de fournir une définition de schéma intégrée ou un fichier de schéma.

Vous pouvez créer un fichier de définition de table pour des données Avro, Parquet ou ORC stockées dans Cloud Storage ou Google Drive.

Utilisez la commande bq mkdef pour créer une table permanente.
```
bq mkdef \
    --source_format=FORMAT \
    "URI" > FILE_NAME
```
Remplacez les éléments suivants :
- FORMAT : format source
- URI : URI Cloud Storage ou votre URI Drive
  
  Par exemple, gs://mybucket/myfile pour Cloud Storage ou https://drive.google.com/open?id=123ABCD123AbcD123Abcd pour Drive
- FILE_NAME : nom de votre fichier de définition de table
(Facultatif) Ouvrez le fichier de définition de table dans un éditeur de texte. Le fichier ressemble à ceci :
```
{
   "sourceFormat": "AVRO",
   "sourceUris": [
   "URI"
    ]
}
```
(Facultatif) Modifiez manuellement le fichier de définition de table pour modifier, ajouter ou supprimer des paramètres généraux, tels que maxBadRecords et ignoreUnknownValues. Pour en savoir plus, reportez-vous à la propriété ExternalDataConfiguration dans la documentation de référence de l'API.

Créer un fichier de définition pour les données partitionnées avec Hive

Utilisez la commande bq mkdef avec les le mode hive_partitioning_mode et les options hive_partitioning_source_uri_prefix pour créer un fichier de définition pour les données partitionnées avec Hive qui sont stockées dans Cloud Storage, Amazon Simple Storage Service (Amazon S3) ou Azure Blob Storage.

Créer un fichier de définition pour Datastore et Firestore

Si vous utilisez une exportation Datastore ou Firestore en tant que source de données externe, BigQuery récupère automatiquement le schéma à l'aide des données sources auto-descriptives. Lorsque vous créez une définition de table, vous n'avez pas besoin de fournir une définition de schéma intégrée ou un fichier de schéma.

Vous pouvez créer un fichier de définition de table pour les données d'exportation Datastore et Firestore stockées dans Cloud Storage.

Utilisez la commande bq mkdef pour créer une table permanente. L'option --noautodetect n'est pas nécessaire avec les fichiers de sauvegarde Datastore ou Firestore. La fonctionnalité de détection automatique des schémas est désactivée pour ces types de fichiers. La commande mkdef génère un fichier de définition de table au format JSON. L'exemple suivant crée une définition de table et écrit la sortie dans un fichier : /tmp/file_name.
```
bq mkdef \
--source_format=DATASTORE_BACKUP \
"URI" > /tmp/FILE_NAME
```
Remplacez les éléments suivants :
- URI : URI Cloud Storage
- FILE_NAME : nom de votre fichier de définition de table
Le format source DATASTORE_BACKUP est utilisé aussi bien pour Datastore que pour Firestore.
(Facultatif) Ouvrez le fichier de définition de table dans un éditeur de texte. Par exemple, la commande nano /tmp/file_name ouvre le fichier dans nano. Le fichier doit se présenter comme illustré ci-dessous. Notez que le paramètre "autodetect" n'est pas nécessaire.
```
{
"sourceFormat": "DATASTORE_BACKUP",
"sourceUris": [
  "gs://URI"
]
}
```
(Facultatif) Modifiez manuellement le fichier de définition de table pour modifier, ajouter ou supprimer des paramètres tels que maxBadRecords et ignoreUnknownValues. Il n'existe pas de paramètres de configuration spécifiques aux fichiers d'exportation Datastore et Firestore. Pour en savoir plus, reportez-vous à la propriété ExternalDataConfiguration dans la documentation de référence de l'API.

Créer un fichier de définition pour Bigtable

Lorsque vous créez un fichier de définition de table pour Cloud Bigtable, vous le générez manuellement au format JSON. L'utilisation de la commande mkdef pour créer une définition de table n'est pas compatible avec les sources de données Bigtable pour le moment. La fonctionnalité de détection automatique des schémas n'est par ailleurs pas disponible pour Bigtable. Pour obtenir la liste des options de définition de table Bigtable, reportez-vous à la section BigtableOptions dans la documentation de référence de l'API REST.

Un fichier de définition de table JSON pour Bigtable se présente comme illustré ci-dessous. À l'aide de ce fichier, BigQuery lit les données d'une seule famille de colonnes et interprète les valeurs sous la forme d'entiers codés au format binaire.

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/PROJECT_ID/instances/INSTANCE_ID/tables/TABLE_NAME"
    ],
    "bigtableOptions": {
        "columnFamilies" : [
            {
                "familyId": "FAMILY_ID",
                "type": "INTEGER",
                "encoding": "BINARY"
            }
        ]
    }
}

Remplacez les éléments suivants :

PROJECT_ID : projet contenant votre cluster Bigtable
INSTANCE_ID : ID de l'instance Bigtable
TABLE_NAME : nom de la table que vous interrogez
FAMILY_ID : identifiant de la famille de colonnes

Pour en savoir plus, consultez la section Récupérer l'URI Bigtable.

Utilisation des caractères génériques pour les fichiers de définition de table

Si vos données sont séparées en plusieurs fichiers, vous pouvez utiliser un caractère générique astérisque (*) 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 de fed-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 de fed-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.

Si vous utilisez un caractère générique astérisque, placez le bucket et le nom de fichier entre guillemets. Par exemple, si vous disposez de deux fichiers nommés fed-sample000001.csv et fed-sample000002.csv, et que vous souhaitez utiliser un astérisque pour les sélectionner, l'URI du bucket aura la forme suivante : "gs://mybucket/fed-sample*".

Le caractère générique * n'est pas autorisé lors de la création de fichiers de définition de table pour les sources de données suivantes :

Bigtable. Vous ne pouvez spécifier qu'une seule source de données pour les données Bigtable. La valeur de l'URI doit être une URL HTTPS valide pour une table Bigtable.
Datastore ou Firestore. Exportations Datastore ou Firestore stockées dans Cloud Storage Pour les sauvegardes Datastore, vous ne pouvez spécifier qu'une seule source de données. La valeur de l'URI doit se terminer par .backup_info ou .export_metadata.
Drive. Données stockées dans Drive.

Étapes suivantes

Découvrez comment interroger des données Cloud SQL.
Découvrez comment interroger des données Drive.
Découvrez comment interroger des données Bigtable.