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 :

Sélectionnez l'une des options suivantes :

Console

  1. Accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans le volet Explorateur, développez votre projet et sélectionnez un ensemble de données.

  3. Développez l'option Actions, puis cliquez sur Créer une table.

  4. Dans la section Source, spécifiez les détails suivants :

    1. Pour le champ Créer une table à partir de, sélectionnez Google Cloud Storage.

    2. 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.

    3. Pour Format de fichier, sélectionnez le format correspondant à votre fichier.

  5. Dans la section Destination, spécifiez les détails suivants :

    1. Pour Projet, choisissez le projet dans lequel créer la table.

    2. Pour Ensemble de données, choisissez l'ensemble de données dans lequel créer la table.

    3. Pour Table, saisissez le nom de la table que vous créez.

    4. Pour Type de table, sélectionnez Table externe.

  6. 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.

  7. 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.

  8. 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.

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. 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 exemple myproject
    • 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.

  3. 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 exemple CSV.

  • BUCKET_PATH : chemin d'accès au bucket Cloud Storage contenant les données de la table, au format gs://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 le file_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 format field: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 externe

    Par exemple, CSV.

  • BUCKET_PATH : chemin d'accès au bucket Cloud Storage contenant les données de la table, au format gs://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 le file_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 format field: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. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a permanent table
public class QueryExternalGCSPerm {

  public static void runQueryExternalGCSPerm() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query =
        String.format("SELECT * FROM %s.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalGCSPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the GCS file
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      bigquery.create(TableInfo.of(tableId, externalTable));

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(QueryJobConfiguration.of(query));

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external permanent table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

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. 

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSPerm() {
  // Queries an external data source using a permanent table

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  // Configure the external data source
  const dataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row
    csvOptions: {skipLeadingRows: 1},
  };

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    externalDataConfiguration: dataConfig,
  };

  // Create an external table linked to the GCS file
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${datasetId}.${tableId}\`
  WHERE name LIKE 'W%'`;

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(query);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

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. 

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
table_id = "your-project.your_dataset.your_table_name"

# TODO(developer): Set the external source format of your table.
# Note that the set of allowed values for external data sources is
# different than the set used for loading data (see :class:`~google.cloud.bigquery.job.SourceFormat`).
external_source_format = "AVRO"

# TODO(developer): Set the source_uris to point to your data in Google Cloud
source_uris = [
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/a-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/c-twitter.avro",
]

# Create ExternalConfig object with external source format
external_config = bigquery.ExternalConfig(external_source_format)
# Set source_uris that point to your data in Google Cloud
external_config.source_uris = source_uris

# TODO(developer) You have the option to set a reference_file_schema_uri, which points to
# a reference file for the table schema
reference_file_schema_uri = "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro"

external_config.reference_file_schema_uri = reference_file_schema_uri

table = bigquery.Table(table_id)
# Set the external data configuration of the table
table.external_data_configuration = external_config
table = client.create_table(table)  # Make an API request.

print(
    f"Created table with external source format {table.external_data_configuration.source_format}"
)

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

  1. Dans la console Google Cloud, accédez à BigQuery.

    Accéder à BigQuery

  2. Dans le volet Explorateur, développez votre projet et sélectionnez un ensemble de données.
  3. Cliquez sur Afficher les actions, puis sur Créer une table. Le volet Créer une table s'ouvre.
  4. Dans la section Source, spécifiez les détails suivants :
    1. Pour le champ Créer une table à partir de, sélectionnez Google Cloud Storage.
    2. 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.
    3. Dans la liste Format de fichier, sélectionnez le type de fichier.
    4. 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.
    5. 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é.
    6. (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.
  5. Dans la section Destination, spécifiez les détails suivants :
    1. Pour Projet, sélectionnez le projet dans lequel vous souhaitez créer la table.
    2. Pour Ensemble de données, sélectionnez l'ensemble de données dans lequel vous souhaitez créer la table.
    3. Pour le champ Table, saisissez le nom de la table que vous souhaitez créer.
    4. Pour Type de table, sélectionnez Table externe.
  6. Dans la section Schéma, saisissez la définition du schéma.
  7. Pour activer la détection automatique du schéma, sélectionnez Détection automatique.
  8. 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.
  9. 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 exemple PARQUET.
  • 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 est false.

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 est false.
  • 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 format field: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. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.HivePartitioningOptions;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create external table using hive partitioning
public class SetHivePartitioningOptions {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/*";
    String sourceUriPrefix =
        "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/{pkey:STRING}/";
    setHivePartitioningOptions(datasetName, tableName, sourceUriPrefix, sourceUri);
  }

  public static void setHivePartitioningOptions(
      String datasetName, String tableName, String sourceUriPrefix, String sourceUri) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Configuring partitioning options
      HivePartitioningOptions hivePartitioningOptions =
          HivePartitioningOptions.newBuilder()
              .setMode("CUSTOM")
              .setRequirePartitionFilter(true)
              .setSourceUriPrefix(sourceUriPrefix)
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      ExternalTableDefinition customTable =
          ExternalTableDefinition.newBuilder(sourceUri, FormatOptions.parquet())
              .setAutodetect(true)
              .setHivePartitioningOptions(hivePartitioningOptions)
              .build();
      bigquery.create(TableInfo.of(tableId, customTable));
      System.out.println("External table created using hivepartitioningoptions");
    } catch (BigQueryException e) {
      System.out.println("External table was not created" + e.toString());
    }
  }
}

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 :

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. 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 table
    • DATASET : nom de l'ensemble de données contenant la table
    • EXTERNAL_TABLE_NAME : nom de la table
    • REGION : région qui contient la connexion
    • CONNECTION_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.

    • 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ème BQ.REFRESH_EXTERNAL_METADATA_CACHE pour actualiser le cache.

      Vous devez définir CACHE_MODE si STALENESS_INTERVAL est défini sur une valeur supérieure à 0.

  3. 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 :

  1. 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 connexion
    • REGION : région qui contient la connexion
    • CONNECTION_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ème BQ.REFRESH_EXTERNAL_METADATA_CACHE pour actualiser le cache.

      Vous devez définir CACHE_MODE si STALENESS_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 format gs://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.

  2. 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ées INTERVAL. Par exemple, spécifiez 0-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 :

  1. Ouvrez la console Cloud Storage.

    Console Cloud Storage

  2. Accédez à l'emplacement de l'objet (fichier) contenant les données source.

  3. Cliquez sur le nom de l'objet souhaité.

    La page Détails de l'objet s'affiche.

  4. 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 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.

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