Interroger des données 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
  • Archiver

Pour interroger une source de données externe Cloud Storage, indiquez le chemin d'accès à vos données sous forme d'URI Cloud Storage et créez une table externe qui renvoie à cette source de données. La table utilisée pour renvoyer à la source de données Cloud Storage peut être une table permanente ou une table temporaire.

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écupérer l'URI Cloud Storage

Pour créer une table externe à l'aide d'une source de données Cloud Storage, vous devez indiquer l'URI Cloud Storage.

L'URI Cloud Storage comprend 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, l'URI du bucket sera gs://mybucket/myfile.csv. Si vos données sont séparées en plusieurs fichiers, vous pouvez utiliser un caractère générique dans l'URI. Pour en savoir plus, consultez les URI de requête Cloud Storage.

BigQuery ne prend pas en charge les URI sources qui comprennent 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, l'URI source suivant, bien qu'il soit valide dans Cloud Storage, ne fonctionne pas dans BigQuery : gs://bucket/my//object//name.

Pour récupérer l'URI Cloud Storage :

  1. Ouvrez la console Cloud Storage.

    Console Cloud Storage

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

  3. En haut de la console Cloud Storage, notez le chemin d'accès à l'objet. Pour composer l'URI, remplacez gs://bucket/file par le chemin d'accès approprié, par exemple, gs://mybucket/myfile.json. bucket correspond au nom du bucket Cloud Storage et file au nom de l'objet (fichier) contenant les 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 les 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).

Interroger des données Cloud Storage à l'aide de tables externes permanentes

Autorisations requises

Pour interroger des données externes hébergées dans Cloud Storage à l'aide d'une table permanente, vous devez disposer des autorisations permettant d'effectuer les tâches suivantes :

  • Exécuter une tâche de requête au niveau du projet ou à un niveau supérieur.
  • Créer une table qui pointe vers les données externes.
  • Accéder à la table.

Si vos données externes sont stockées dans Cloud Storage, vous devez également disposer d'es autorisations requises pour accéder au bucket contenant vos données.

Autorisations permettant de créer et d'interroger une table externe dans BigQuery

Pour créer et interroger une table externe dans BigQuery, vous devez disposer des autorisations IAM suivantes :

  • bigquery.tables.create
  • bigquery.tables.getData
  • bigquery.jobs.create

Chacun des rôles IAM prédéfinis suivants inclut les autorisations dont vous avez besoin pour créer et interroger une table externe dans BigQuery :

  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.admin (inclut l'autorisation bigquery.jobs.create)
  • roles/bigquery.user (inclut l'autorisation bigquery.jobs.create)
  • roles/bigquery.jobUser (inclut l'autorisation bigquery.jobs.create)

En outre, si vous disposez de l'autorisation bigquery.datasets.create, vous pouvez créer des tables externes dans les ensembles de données que vous créez et y accéder. Cependant, vous avez toujours besoin de l'autorisation bigquery.jobs.create pour pouvoir interroger les données.

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la page Rôles prédéfinis et autorisations.

Autorisations permettant d'interroger des données externes dans un bucket Cloud Storage

Pour interroger des données externes dans un bucket Cloud Storage, vous devez disposer des autorisations IAM suivantes :

  • storage.objects.get
  • storage.objects.list (obligatoire si vous utilisez un caractère générique dans l'URI)

Le rôle Cloud IAM prédéfini roles/storage.objectViewer inclut toutes les autorisations dont vous avez besoin pour interroger des données externes dans un bucket Cloud Storage.

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 et interroger une table externe permanente

Vous pouvez créer une table permanente associée à votre source de données externe :

Pour interroger une source de données externe à l'aide d'une table permanente, vous créez dans un ensemble de données BigQuery une table qui est associée à votre source de données externe. Les données ne sont pas stockées dans la table BigQuery. La table étant permanente, vous pouvez utiliser des contrôles d'accès pour la partager avec d'autres utilisateurs ayant également accès à la source de données externe sous-jacente.

Lorsque vous créez une table externe permanente, vous pouvez spécifier le schéma comme suit :

Pour créer une table externe, procédez comme suit :

Console

  1. Dans Cloud Console, ouvrez la page "BigQuery".

Accéder à BigQuery

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

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

  3. Dans la section Source de la page Créer une table :

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

    • Dans le champ Select file from GSC bucket (sélectionner un fichier à partir d'un bucket GSC), recherchez le fichier ou le bucket Cloud Storage, ou saisissez l'URI Cloud Storage. Sachez que vous ne pouvez pas inclure plusieurs URI dans Cloud Console. En revanche, les caractères génériques sont acceptés. Le bucket Cloud Storage doit se trouver au même emplacement que l'ensemble de données contenant la table que vous créez.

    • Pour Format de fichier, sélectionnez le format des données.

  4. Dans la section Destination de la page Créer une table :

    • Pour Ensemble de données, sélectionnez l'ensemble de données approprié.
    • Vérifiez que le paramètre Type de table est défini sur Table externe.
    • Dans le champ Table, saisissez le nom de la table que vous créez dans BigQuery.

  5. Dans la section Schéma, vous pouvez activer la détection automatique de schéma ou spécifier un schéma manuellement.

    • Pour activer la détection automatique de schéma, sélectionnez l'option Détection automatique.

    • Pour spécifier manuellement un schéma, conservez l'option Détection automatique, puis effectuez l'une des opérations suivantes :

      • Activez Modifier sous forme de texte et saisissez le schéma de la table sous forme de tableau JSON.

  6. 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, puis les enregistrer sous forme de table ou 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. Si vous ne spécifiez pas de schéma, BigQuery utilise la détection automatique de schéma pour déduire le schéma à partir des données externes.

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 :

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

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

    CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
  OPTIONS (
  format = 'CSV',
  uris = ['gs://mybucket/sales.csv']);

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez la page Exécuter des requêtes interactives.

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_URL > DEFINITION_FILE

bq mk --table \
  --external_table_definition=DEFINITION_FILE \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

Où :

  • SOURCE_FORMAT est le format de la source de données externe, par exemple CSV.
  • BUCKET_URI correspond à l'URI Cloud Storage ;
  • DEFINITION_FILE correspond au chemin d'accès du fichier de définition de table sur votre ordinateur local.
  • DATASET_NAME est le nom de l'ensemble de données contenant la table.
  • TABLE_NAME est le 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_URI \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

Où :

  • SOURCE_FORMAT est le format de la source de données externe, par exemple CSV.
  • BUCKET_URI correspond à l'URI Cloud Storage ;
  • DATASET_NAME correspond au nom de l'ensemble de données contenant la table ;
  • TABLE_NAME est le 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

Créez une configuration ExternalDataConfiguration lorsque vous utilisez la méthode d'API tables.insert. 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 l'exemple ci-dessous, suivez la procédure de configuration pour Java décrite dans le 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 Java. 

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 l'exemple ci-dessous, suivez la procédure de configuration pour Node.js décrite dans le 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 Node.js. 

// 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 l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le 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 Python. 

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'

# Configure the external data source
dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset_ref.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
]
external_config.options.skip_leading_rows = 1  # optionally skip header row
table.external_data_configuration = external_config

# Create a permanent table linked to the GCS file
table = client.create_table(table)  # API request

# Example query to find states starting with 'W'
sql = 'SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(dataset_id, table_id)

query_job = client.query(sql)  # API request

w_states = list(query_job)  # Waits for query to finish
print("There are {} states with names starting with W.".format(len(w_states)))

Interroger des données Cloud Storage à l'aide de tables temporaires

Pour interroger une source de données externe sans créer de table permanente, vous exécutez une commande permettant de combiner les éléments suivants :

  • Un fichier de définition de table et une requête
  • Une définition de schéma intégrée et une requête
  • Un fichier de définition de schéma JSON et une requête

Le fichier de définition de table ou le schéma fourni est utilisé pour créer la table externe temporaire, sur laquelle la requête s'exécute. L'outil de ligne de commande bq et l'API permettent d'interroger une source de données externe à l'aide d'une table temporaire.

En cas d'utilisation d'une table externe 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 les 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).

Autorisations requises

Pour interroger des données externes hébergées dans Cloud Storage à l'aide d'une table temporaire, vous avez besoin d'autorisations pour exécuter une tâche de requête au niveau du projet ou à un niveau supérieur. Vous avez également besoin d'un accès à l'ensemble de données contenant la table qui pointe vers les données externes. Pour interroger des données dans Cloud Storage, vous devez également disposer des autorisations requises pour accéder au bucket contenant vos données.

Autorisations permettant d'interroger une table externe dans BigQuery

Pour interroger une table externe dans BigQuery à l'aide d'une table temporaire, vous avez besoin des autorisations IAM suivantes :

  • bigquery.tables.getData
  • bigquery.jobs.create

Chacun des rôles IAM prédéfinis suivants inclut les autorisations dont vous avez besoin pour interroger une table externe dans BigQuery à l'aide d'une table temporaire :

  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.admin (inclut l'autorisation bigquery.jobs.create)
  • roles/bigquery.user (inclut l'autorisation bigquery.jobs.create)
  • roles/bigquery.jobUser (inclut l'autorisation bigquery.jobs.create)

En outre, si vous disposez de l'autorisation bigquery.datasets.create, vous pouvez créer des tables externes dans les ensembles de données que vous créez et y accéder. Cependant, vous avez toujours besoin de l'autorisation bigquery.jobs.create pour pouvoir interroger les données.

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la page Rôles prédéfinis et autorisations.

Autorisations permettant d'interroger des données externes dans un bucket Cloud Storage

Pour interroger des données externes dans un bucket Cloud Storage, vous devez disposer des autorisations IAM suivantes :

  • storage.objects.get
  • storage.objects.list (obligatoire si vous utilisez un caractère générique dans l'URI)

Le rôle Cloud IAM prédéfini roles/storage.objectViewer inclut toutes les autorisations dont vous avez besoin pour interroger des données externes dans un bucket Cloud Storage.

Créer et interroger une table temporaire

Vous pouvez créer et interroger une table temporaire associée à une source de données externe à l'aide de l'outil de ligne de commande bq, de l'API ou des bibliothèques clientes.

bq

L'option bq query permet d'interroger une table temporaire associée à une source de données externe à l'aide de la commande --external_table_definition. Lorsque vous utilisez l'outil de ligne de commande bq pour interroger une table temporaire associée à une source de données externe, vous pouvez spécifier le schéma de la table à l'aide de l'une des méthodes suivantes :

  • Un fichier de définition de table (stocké sur l'ordinateur local)
  • Une définition de schéma intégrée
  • Un fichier de schéma JSON (stocké sur l'ordinateur local)

(Facultatif) Spécifiez l'option --location et définissez la valeur correspondant à votre emplacement.

Pour interroger une table temporaire associée à votre source de données externe à l'aide d'un fichier de définition de table, saisissez la commande suivante :

bq --location=location query \
--external_table_definition=table::definition_file \
'query'

Où :

  • location est le nom de l'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 cette option sur asia-northeast1. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc ;
  • table est le nom de la table temporaire que vous créez.
  • definition_file correspond au chemin d'accès du fichier de définition de table sur votre machine locale.
  • query correspond à la requête que vous soumettez à la table temporaire.

Par exemple, la commande suivante permet de créer et d'interroger une table temporaire nommée sales à l'aide du fichier de définition de table sales_def.

bq query \
--external_table_definition=sales::sales_def \
'SELECT
  Region,
  Total_sales
FROM
  sales'

Pour interroger une table temporaire associée à votre source de données externe à l'aide d'une définition de schéma intégrée, saisissez la commande suivante :

bq --location=location query \
--external_table_definition=table::schema@source_format=Cloud Storage URI \
'query'

Où :

  • location est le nom de l'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 cette option sur asia-northeast1. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc ;
  • table correspond au nom de la table temporaire que vous créez ;
  • schema correspond à la définition de schéma spécifiée sur la ligne de commande au format field:data_type,field:data_type.
  • source_format est le format de la source de données externe, par exemple CSV.
  • Cloud Storage URI correspond à l'URI Cloud Storage ;
  • query correspond à la requête que vous soumettez à la table temporaire.

Par exemple, la commande suivante crée et interroge une table temporaire appelée sales qui est associée à un fichier CSV stocké dans Cloud Storage avec la définition de schéma suivante : Region:STRING,Quarter:STRING,Total_sales:INTEGER.

bq query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
'SELECT
  Region,
  Total_sales
FROM
  sales'

Pour interroger une table temporaire associée à votre source de données externe à l'aide d'un fichier de schéma JSON, saisissez la commande suivante :

bq --location=location query \
--external_table_definition=schema_file@source_format=Cloud Storage URI \
'query'

Où :

  • location est le nom de l'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 cette option sur asia-northeast1. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc ;
  • schema_file correspond au chemin d'accès vers le fichier de schéma JSON sur votre ordinateur local.
  • source_format est le format de la source de données externe, par exemple CSV.
  • Cloud Storage URI correspond à l'URI Cloud Storage ;
  • query correspond à la requête que vous soumettez à la table temporaire.

Par exemple, la commande suivante crée et interroge une table temporaire appelée sales qui est associée à un fichier CSV stocké dans Cloud Storage à l'aide du fichier de schéma /tmp/sales_schema.json.

  bq query \
  --external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv \
  'SELECT
      Region,
      Total_sales
    FROM
      sales'

API

Java

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Java décrite dans le 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 Java. 

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

// Sample to queries an external data source using a temporary table
public class QueryExternalGCSTemp {

  public static void runQueryExternalGCSTemp() {
    // TODO(developer): Replace these variables before running the sample.
    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 WHERE name LIKE 'W%%'", tableName);
    queryExternalGCSTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSTemp(
      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();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addTableDefinition(tableName, externalTable)
              .build();

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

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

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

Node.js

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Node.js décrite dans le 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 Node.js. 

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

async function queryExternalGCSTemp() {
  // Queries an external data source using a temporary table.

  const tableId = 'us_states';

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

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

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    query,
    tableDefinitions: {[tableId]: externalDataConfig},
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  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 l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le 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 Python. 

from google.cloud import bigquery

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

# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1
table_id = "us_states"
job_config = bigquery.QueryJobConfig(table_definitions={table_id: external_config})

# Example query to find states starting with 'W'.
sql = 'SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)

query_job = client.query(sql, job_config=job_config)  # Make an API request.

w_states = list(query_job)  # Wait for the job to complete.
print("There are {} states with names starting with W.".format(len(w_states)))

Interroger des données partitionnées en externe

Consultez la page Interroger des données partitionnées en externe.

Gestion des caractères génériques dans les URI Cloud Storage

Si vos données Google 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 pouvez également utiliser un caractère générique lorsque vous créez une table externe sans fichier de définition de table.

Pour insérer un caractère générique dans l'URI Cloud Storage, il vous suffit d'ajouter un astérisque (*) au nom de base.

Exemples :

  • L'URI générique suivant sélectionne 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'URI générique suivant ne sélectionne que les fichiers ayant une extension .csv dans le dossier nommé fed-samples et tous les sous-dossiers de fed-samples :

    gs://mybucket/fed-samples/fed-sample/*.csv

  • L'URI générique suivant sélectionne les 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 utiliser qu'un seul caractère générique pour les objets (noms de fichiers) contenus dans votre bucket. Le caractère générique peut apparaître à l'intérieur ou à la fin du nom de l'objet. Vous ne pouvez pas ajouter un caractère générique au nom du bucket. L'emploi de plusieurs caractères génériques dans l'URI source n'est pas accepté. Par exemple, le chemin gs://mybucket/fed-*/temp/*.csv n'est pas valide.

Pour les exportations Google Datastore, vous ne pouvez spécifier qu'un seul URI, qui doit se terminer par .backup_info ou .export_metadata.

Le caractère générique astérisque n'est pas autorisé lors des opérations suivantes :

  • Créer des tables externes associées à des exportations Datastore ou Firestore
  • Charger des données d'exportation Datastore ou Firestore à partir de Cloud Storage

Pseudo-colonne _FILE_NAME

Les tables basées sur des sources de données externes donnent accès à une pseudo-colonne intitulée _FILE_NAME. Cette colonne contient le chemin d'accès complet du fichier auquel appartient la ligne. Elle n'est disponible que pour les tables qui renvoient à des données externes stockées dans Cloud Storage et Google Drive.

Le nom de colonne _FILE_NAME est réservé. Vous ne pouvez donc pas créer de colonne portant ce nom dans vos tables. Pour sélectionner la valeur de _FILE_NAME, vous devez utiliser un alias. Par exemple, la requête suivante sélectionne _FILE_NAME en attribuant l'alias fn à la pseudo-colonne.

bq query \
--project_id=project_id \
--use_legacy_sql=false \
'SELECT
   name,
   _FILE_NAME AS fn
 FROM
   `dataset.table_name`
 WHERE
   name contains "Alex"'

Où :

  • project_id correspond à un ID de projet valide (cette option n'est pas obligatoire si vous utilisez Cloud Shell ou si vous définissez un projet par défaut dans Google Cloud CLI).
  • dataset correspond au nom de l'ensemble de données où est stockée la table externe permanente.
  • table_name est le nom de la table externe permanente.

Lorsque la requête comporte un prédicat de filtre sur la pseudo-colonne _FILE_NAME, BigQuery tente d'ignorer les fichiers qui ne correspondent pas au filtre. Des recommandations similaires pour interroger des tables partitionnées par date d'ingestion à l'aide de pseudo-colonnes s'appliquent lors de la construction de prédicats de requête avec la pseudo-colonne _FILE_NAME.