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. Ce type de source de données (également appelé source de données fédérée) peut être interrogé directement, même si les données ne sont pas stockées dans BigQuery.

Fichiers de définition de table

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.

Vous utilisez les fichiers de définition de table avec l'interface de ligne de commande (CLI) BigQuery. 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. Les fichiers de destination de table sont inutiles lorsque vous créez une table externe à l'aide de Cloud Console ou de l'interface utilisateur Web classique de BigQuery.

Vous pouvez créer des fichiers de définition de table pour les sources de données externes suivantes :

  • Cloud Storage

    • Valeurs séparées par une virgule (CSV)
    • JSON délimité par des retours à la ligne
    • Fichiers Avro
    • Fichiers d'exportation Datastore
    • Fichiers ORC (version bêta)
    • Fichiers Parquet (version bêta)
    • Fichiers d'exportation Cloud Firestore
  • Google Drive

    • Valeurs séparées par une virgule (CSV)
    • JSON délimité par des retours à la ligne
    • Fichiers Avro
    • Google Sheets
  • Cloud 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 :

Tables externes permanentes et temporaires

Vous pouvez interroger une source de données externe dans BigQuery à l'aide d'une table permanente ou d'une table temporaire. Une table permanente est une table créée dans un ensemble de données et liée à votre source de données externe. La table étant permanente, vous pouvez utiliser des contrôles d'accès au niveau de l'ensemble de données pour la partager avec d'autres utilisateurs ayant également accès à la source de données externe sous-jacente. Vous avez par ailleurs la possibilité d'interroger la table à tout moment.

Lorsque vous interrogez une source de données externe à l'aide d'une table temporaire, vous exécutez une commande qui inclut une requête et crée une table non permanente associée à la source de données externe. En cas d'utilisation d'une table 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. L'interrogation d'une source de données externe à l'aide d'une table temporaire est utile pour des 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).

Vous pouvez utiliser un fichier de définition de table pour décrire une table externe permanente ou temporaire.

Créer une définition de table à l'aide de la fonctionnalité de détection automatique des schémas

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 des schémas dans BigQuery.

Vous pouvez utiliser la fonctionnalité de détection automatique des schémas lorsque vous créez des définitions de table pour les éléments suivants :

  • Fichiers JSON stockés dans Cloud Storage ou Google Drive
  • Fichiers CSV stockés dans Cloud Storage ou Google Drive
  • Fichiers Google Sheets stockés dans Google Drive

Cloud Storage

Pour créer une définition de table pour une source de données Cloud Storage à l'aide de la CLI, procédez comme suit :

  1. Utilisez la commande mkdef de l'outil de ligne de commande avec l'indicateur --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.

    Dans la commande ci-dessous, remplacez :

    • source_format par le format de votre fichier : NEWLINE_DELIMITED_JSON, CSV ou GOOGLE_SHEETS.
    • file_name par le nom de votre fichier de définition de table.
    • bucket_uri par votre URI Cloud Storage, par exemple, gs://mybucket/myfile.
    bq mkdef \
    --autodetect \
    --source_format=source_format \
    "bucket_uri" > /tmp/file_name
    
  2. (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": [
      "bucket_uri"
    ]
    }
    
  3. (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.

Données partitionnées en externe

Pour configurer un fichier de définition de table pour des données partitionnées en externe dans Cloud Storage, reportez-vous à la section Interroger les données partitionnées en externe.

Google Drive

Pour créer une définition de table pour une source de données Google Drive à l'aide de la CLI, procédez comme suit :

  1. Utilisez la commande mkdef de l'outil de ligne de commande avec l'indicateur --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.

    Dans la commande ci-dessous, remplacez :

    • source_format par le format de votre fichier : NEWLINE_DELIMITED_JSON, CSV ou GOOGLE_SHEETS.
    • file_name par le nom de votre fichier de définition de table.
    • drive_uri par votre URI Google Drive, par exemple, https://drive.google.com/open?id=123ABCD123AbcD123Abcd.
    bq mkdef \
    --autodetect \
    --source_format=source_format \
    "drive_uri" > /tmp/file_name
    
  2. 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": [
      "drive_uri"
    ]
    }
    
  3. (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.

  4. 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 une définition de table à l'aide d'un schéma spécifié sur la ligne de commande

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.

Vous pouvez utiliser une définition de schéma spécifiée sur la ligne de commande lorsque vous créez des fichiers de définition de table pour les éléments suivants :

  • Fichiers JSON stockés dans Cloud Storage ou Google Drive
  • Fichiers CSV stockés dans Cloud Storage ou Google Drive
  • Fichiers Google Sheets stockés dans Google Drive

Pour créer une définition de table pour une source de données Cloud Storage à l'aide d'une définition de schéma dans la CLI, procédez comme suit :

  1. Utilisez la commande mkdef de l'outil de ligne de commande avec l'indicateur --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.

    Dans la commande ci-dessous, remplacez :

    • source_format par le format de votre fichier : NEWLINE_DELIMITED_JSON, CSV ou GOOGLE_SHEETS.
    • uri par votre URI Cloud Storage ou votre URI Google Drive. Par exemple, https://drive.google.com/open?id=123ABCD123AbcD123Abcd pour Google Drive ou gs://mybucket/myfile pour Cloud Storage.
    • field:data_type,field:data_type par votre définition de schéma, par exemple, Name:STRING,Address:STRING, ....
    • file_name par le nom de votre fichier de définition de table.
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "uri" \
    field:data_type,field:data_type > /tmp/file_name
    
  2. (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"
    ]
    }
    
  3. (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.

Créer une définition de table à l'aide d'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. Vous devez créer le fichier de schéma JSON manuellement, et celui-ci doit se trouver sur votre ordinateur local. Il est impossible de référencer un fichier de schéma JSON stocké dans Cloud Storage ou Google Drive.

Vous pouvez utiliser un fichier de schéma JSON lorsque vous créez des définitions de table pour les éléments suivants :

  • Fichiers JSON stockés dans Cloud Storage ou Google Drive
  • Fichiers CSV stockés dans Cloud Storage ou Google Drive
  • Fichiers Google Sheets stockés dans Google Drive

Cloud Storage

Pour créer une définition de table pour une source de données Cloud Storage à l'aide d'un fichier de schéma JSON dans la CLI, procédez comme suit :

  1. Utilisez la commande mkdef de l'outil de ligne de commande avec l'indicateur --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.

    Dans la commande ci-dessous, remplacez :

    • source_format par le format de votre fichier : NEWLINE_DELIMITED_JSON, CSV ou GOOGLE_SHEETS.
    • file_name par le nom de votre fichier de définition de table.
    • bucket_uri par votre URI Cloud Storage, par exemple, gs://mybucket/myfile.
    • path_to_schema_file par l'emplacement du fichier de schéma JSON sur votre ordinateur local.
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "bucket_uri" \
    path_to_schema_file > /tmp/file_name
    
  2. (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": [
      "bucket_uri"
    ]
    }
    
  3. (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.

Google Drive

Pour créer une définition de table pour une source de données Google Drive à l'aide d'un fichier de schéma JSON dans la CLI, procédez comme suit :

  1. Utilisez la commande mkdef de l'outil de ligne de commande avec l'indicateur --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.

    Dans la commande ci-dessous, remplacez :

    • source_format par le format de votre fichier : NEWLINE_DELIMITED_JSON, CSV ou GOOGLE_SHEETS.
    • drive_uri par votre URI Google Drive, par exemple, https://drive.google.com/open?id=123ABCD123AbcD123Abcd.
    • path_to_schema_file par l'emplacement du fichier de schéma JSON sur votre ordinateur local.
    • file_name par le nom de votre fichier de définition de table.
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "drive_uri" \
    path_to_schema_file > /tmp/file_name
    
  2. 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": [
      "drive_uri"
    ]
    }
    
  3. (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.

  4. 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 des définitions de table Avro

Si vous utilisez un fichier Avro 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 pour des fichiers Avro, vous n'avez pas besoin d'utiliser la fonctionnalité de détection automatique des schémas. Il est également inutile de spécifier une définition de schéma sur la ligne de commande ou un fichier de schéma.

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

Pour créer un fichier de définition de table pour des données Avro à l'aide de la CLI, procédez comme suit :

  1. Utilisez la commande mkdef de l'outil de ligne de commande pour créer une définition de table. L'indicateur --noautodetect n'est pas nécessaire avec les fichiers Avro. La fonctionnalité de détection automatique des schémas est désactivée pour ces 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.

    Dans la commande ci-dessous, remplacez :

    • uri par votre URI Cloud Storage ou votre URI Google Drive. Par exemple, https://drive.google.com/open?id=123ABCD123AbcD123Abcd pour Google Drive ou gs://mybucket/myfile pour Cloud Storage.
    • file_name par le nom de votre fichier de définition de table.
    bq mkdef \
    --source_format=AVRO \
    "uri" > /tmp/file_name
    
  2. (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": "AVRO",
    "sourceUris": [
      "uri"
    ]
    }
    
  3. (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 Avro, mais certains paramètres s'appliquent aux fichiers CSV et Google  Sheet. Pour en savoir plus, reportez-vous à la propriété ExternalDataConfiguration dans la documentation de référence de l'API.

Créer des définitions de tables d'exportation Datastore et Cloud Firestore

Si vous utilisez une exportation Datastore ou Cloud 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 pour des fichiers de sauvegarde Datastore et Cloud Firestore, vous n'avez pas besoin d'utiliser la fonctionnalité de détection automatique des schémas. Il est également inutile de spécifier une définition de schéma ou un fichier de schéma.

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

Pour utiliser la CLI afin de créer un fichier de définition de table pour les données d'exportation Datastore ou Cloud Firestore, procédez comme suit :

  1. Utilisez la commande mkdef de l'outil de ligne de commande pour créer une définition de table. L'indicateur --noautodetect n'est pas nécessaire avec les fichiers de sauvegarde Datastore ou Cloud 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.

    Dans la commande ci-dessous, remplacez :

    • bucket_uri par votre URI Cloud Storage.
    • file_name par le nom de votre fichier de définition de table.

    Notez que le format source DATASTORE_BACKUP est utilisé pour Datastore et pour Cloud Firestore.

    bq mkdef \
    --source_format=DATASTORE_BACKUP \
    "uri" > /tmp/file_name
    
  2. (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://bucket_uri"
    ]
    }
    
  3. (Facultatif) Modifiez manuellement le fichier de définition de table pour modifier, ajouter ou supprimer des paramètres tels que maxBadRecords et ignoreUnknownValues. Aucun paramètre de configuration n'est spécifique aux fichiers d'exportation Datastore et Cloud Firestore, 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.

Créer des définitions de table Cloud 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 Cloud Bigtable pour le moment. La fonctionnalité de détection automatique des schémas n'est par ailleurs pas disponible pour Cloud Bigtable. Pour obtenir la liste des options de définition de table Cloud 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 Cloud 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_int",
                "type": "INTEGER",
                "encoding": "BINARY"
            }
        ],
    }
}

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

Si vos données Cloud Storage sont réparties dans plusieurs fichiers partageant un nom de base commun, vous pouvez utiliser un caractère générique dans l'URI du fichier de définition de la table. Vous devez ajouter un astérisque (*) au nom de base, et placer le bucket et le nom de fichier entre guillemets. Par exemple, si vous utilisez deux fichiers nommés fed- sample000001.csv et fed-sample000002.csv, l'URI du bucket sera "gs://mybucket/fed-sample*".

Vous ne pouvez utiliser qu'un seul caractère générique pour les objets (noms de fichiers) de votre bucket. Le caractère générique peut figurer dans le nom de l'objet ou à la fin de celui-ci. Vous ne pouvez pas ajouter un caractère générique au nom du bucket.

Pour les données Cloud Bigtable, un seul URI peut être spécifié. Il doit s'agir d'une URL HTTPS complète et valide pour une table Cloud Bigtable. De même, un seul URI peut être spécifié pour les sauvegardes Cloud Datastore. Cet URI doit se terminer par .backup_info.

Le caractère générique * n'est pas autorisé lors de la création de fichiers de définition de table pour les éléments suivants :

  • Sources de données Cloud Bigtable
  • Exportations Datastore stockées dans Cloud Storage
  • Exportations Cloud Firestore stockées dans Cloud Storage
  • Données stockées dans Google Drive