Créer des tables externes Google Drive

Ce document explique comment créer une table externe sur des données stockées dans Google Drive.

BigQuery accepte les tables externes sur les fichiers Drive personnels et partagés. Pour en savoir plus sur Drive, consultez les supports de formation et articles d'aide Google Drive.

Vous pouvez créer des tables externes sur des fichiers dans Drive qui ont les formats suivants :

Valeurs séparées par une virgule (CSV)
JSON délimité par des retours à la ligne
Avro
Google Sheets

Avant de commencer

Avant de créer une table externe, rassemblez certaines informations et assurez-vous de disposer des autorisations nécessaires pour créer la table.

Récupérer les URI Drive

Pour créer une table externe destinée à une source de données Google Drive, vous devez fournir l'URI Drive. Vous pouvez récupérer l'URI Drive directement à partir de l'URL de vos données Drive :

Format de l'URI

https://docs.google.com/spreadsheets/d/FILE_ID

ou
https://drive.google.com/open?id=FILE_ID

où FILE_ID correspond à l'ID alphanumérique de votre fichier Drive.

Activer l'accès à Drive

L'accès aux données hébergées dans Google Drive nécessite un champ d'application OAuth supplémentaire. Bien qu'il ne soit pas activé par défaut, il peut être inclus dans la console Google Cloud, dans l'outil de ligne de commande bq ou via l'API grâce aux mécanismes suivants :

Console

Suivez la procédure d'authentification Web pour créer une table permanente dans la console Google Cloud. Lorsque vous y êtes invité, cliquez sur Autoriser pour permettre aux outils clients BigQuery d'accéder à Google Drive.

gcloud

Pour activer l'accès à Drive :

Saisissez la commande suivante pour vous assurer que vous disposez de la dernière version de Google Cloud CLI.
```
gcloud components update
```
Saisissez la commande suivante pour vous authentifier sur Drive.
```
gcloud auth login --enable-gdrive-access
```

API

Si vous utilisez l'API BigQuery, demandez le champ d'application OAuth de Drive en plus de celui de BigQuery.

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
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
#
# If you are using credentials from gcloud, you must authorize the
# application first with the following command:
#
# gcloud auth application-default login \
#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/cloud-platform",
    ]
)

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

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.

GoogleCredentials credentials =
    ServiceAccountCredentials.getApplicationDefault()
        .createScoped(
            ImmutableSet.of(
                "https://www.googleapis.com/auth/bigquery",
                "https://www.googleapis.com/auth/drive"));

// 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.newBuilder().setCredentials(credentials).build().getService();

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)

Si vous ne disposez pas de compte principal pour l'un de ces rôles, demandez à votre administrateur de vous accorder l'accès ou de créer la table externe à votre place.

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

Créer des tables externes

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

En utilisant la console Google Cloud
En exécutant la commande mk de l'outil de ligne de commande bq
En créant une configuration ExternalDataConfiguration lorsque vous utilisez la méthode d'API tables.insert ;
En utilisant les bibliothèques clientes

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

Console

Dans la console Google Cloud, ouvrez la page "BigQuery".

Accéder à BigQuery

Dans le panneau Explorateur, développez votre projet et sélectionnez un ensemble de données.
Développez l'option Actions puis cliquez sur Ouvrir.
Dans le panneau de détails, cliquez sur Créer une table.
Dans la section Source de la page Créer une table :
- Dans Créer une table à partir de, sélectionnez Drive.
- Dans le champ Sélectionnez l'URI du fichier Drive, saisissez l'URI Google Drive. Notez que les caractères génériques ne sont pas acceptés dans les URI Google Drive.
- Pour Format de fichier, sélectionnez le format des données. Les formats valides pour les données Drive sont les suivants :
  - CSV (Comma-Separated Values)
  - JSON délimité par des retours à la ligne
  - Avro
  - Sheets
  Remarque : Dans la console Google Cloud, si vous sélectionnez la sauvegarde Avro ou Datastore, l'option Automatically detect (Détection automatique) est masquée, car ces fichiers sont autodescriptifs.
(Facultatif) Si vous choisissez Google Sheets, dans la zone Plage de la feuille de calcul (facultatif), spécifiez la feuille et la plage de cellules à interroger. Vous pouvez indiquer un nom de feuille ou sheet_name!top_left_cell_id:bottom_right_cell_id pour une plage de cellules, par exemple "Sheet1!A1:B20". Si la plage de la feuille n'est pas spécifiée, la première feuille du fichier est utilisée.
Dans la section Destination de la page Créer une table :
- Pour Dataset name (Nom de l'ensemble de données), sélectionnez l'ensemble de données approprié, puis dans le champ Table name (Nom de la table), saisissez le nom de la table que vous créez dans BigQuery.
- Vérifiez que le paramètre Type de table est défini sur Table externe.
Dans la section Schéma, saisissez la définition du schéma.
- Pour les fichiers JSON ou CSV, vous pouvez cocher l'option Détection automatique pour permettre la détection automatique du schéma. La détection automatique n'est pas disponible pour les exportations Datastore, les exportations Firestore et les fichiers Avro. Les informations de schéma pour ces types de fichiers sont automatiquement extraites des données sources autodescriptives.
- Indiquez manuellement les informations de schéma de l'une des manières suivantes :
  - Activez l'option Modifier sous forme de texte et saisissez le schéma de la table sous forme de tableau JSON. Remarque : Vous pouvez afficher le schéma d'une table existante au format JSON en saisissant la commande suivante dans l'outil de ligne de commande bq : bq show --format=prettyjson DATASET.TABLE.
  - Utilisez l'option Ajouter un champ pour saisir manuellement le schéma.
Cliquez sur Create table.
Si nécessaire, sélectionnez votre compte, puis cliquez sur Autoriser pour permettre aux outils clients BigQuery d'accéder à Google Drive.

Vous pouvez ensuite exécuter une requête sur la table comme s'il s'agissait d'une table BigQuery standard, en respectant les limites applicables aux sources de données externes.

Une fois la requête exécutée, vous pouvez télécharger les résultats au format CSV ou JSON, puis les enregistrer sous forme de table ou dans Google Sheets. Consultez Télécharger, enregistrer et exporter des données pour en savoir plus.

bq

Vous créez une table dans l'outil de ligne de commande bq à l'aide de la commande bq mk. Lorsque vous utilisez l'outil de ligne de commande bq pour créer une table 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)

Pour créer une table permanente associée à votre source de données Drive à l'aide d'un fichier de définition de table, saisissez la commande suivante :

bq mk \
--external_table_definition=DEFINITION_FILE \
DATASET.TABLE

Où :

DEFINITION_FILE correspond au chemin d'accès du fichier de définition de table sur votre machine locale.
DATASET correspond au nom de l'ensemble de données contenant la table ;
TABLE correspond au nom de la table que vous créez.

Par exemple, la commande suivante crée une table permanente nommée mytable à l'aide d'un fichier de définition de table nommé mytable_def.

bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable

Pour créer une table permanente 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 mk \
--external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

Où :

SCHEMA correspond à la définition de schéma au format FIELD:DATA_TYPE,FIELD:DATA_TYPE.
SOURCE_FORMAT est CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_SHEETS.
DRIVE_URI est votre URI Drive.
DATASET est le nom de l'ensemble de données contenant la table.
TABLE est le nom de la table que vous créez.

Par exemple, la commande suivante crée une table permanente nommée sales qui est associée à un fichier Sheets stocké dans Drive avec la définition de schéma suivante : Region:STRING,Quarter:STRING,Total_sales:INTEGER.

bq mk \
--external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@GOOGLE_SHEETS=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

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

bq mk \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

Où :

SCHEMA_FILE correspond au chemin d'accès vers le fichier de schéma JSON sur votre ordinateur local.
SOURCE_FORMAT est CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_SHEETS.
DRIVE_URI est votre URI Drive.
DATASET est le nom de l'ensemble de données contenant la table.
TABLE est le nom de la table que vous créez.

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

bq mk \
--external_table_definition=/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

Une fois que la table permanente a été créée, vous pouvez exécuter une requête sur celle-ci comme s'il s'agissait d'une table BigQuery standard, en respectant les limites applicables aux sources de données externes.

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.

Python

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
#
# If you are using credentials from gcloud, you must authorize the
# application first with the following command:
#
# gcloud auth application-default login \
#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

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

# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = "your-project.your_dataset"

# Configure the external data source.
dataset = client.get_dataset(dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")
# Use a shareable link or grant viewing access to the email address you
# used to authenticate with BigQuery (this example Sheet is public).
sheet_url = (
    "https://docs.google.com/spreadsheets"
    "/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing"
)
external_config.source_uris = [sheet_url]
options = external_config.google_sheets_options
assert options is not None
options.skip_leading_rows = 1  # Optionally skip header row.
options.range = (
    "us-states!A20:B49"  # Optionally set range of the sheet to query from.
)
table.external_data_configuration = external_config

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

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

results = client.query_and_wait(sql)  # Make an API request.

# Wait for the query to complete.
w_states = list(results)
print(
    "There are {} states with names starting with W in the selected range.".format(
        len(w_states)
    )
)

Java

import com.google.auth.oauth2.GoogleCredentials;
import com.google.auth.oauth2.ServiceAccountCredentials;
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.Field;
import com.google.cloud.bigquery.GoogleSheetsOptions;
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;
import com.google.common.collect.ImmutableSet;
import java.io.IOException;

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

  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 =
        "https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";
    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);
    queryExternalSheetsPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalSheetsPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {

      // Create credentials with Drive & BigQuery API scopes.
      // Both APIs must be enabled for your project before running this code.
      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault()
              .createScoped(
                  ImmutableSet.of(
                      "https://www.googleapis.com/auth/bigquery",
                      "https://www.googleapis.com/auth/drive"));

      // 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.newBuilder().setCredentials(credentials).build().getService();

      // Skip header row in the file.
      GoogleSheetsOptions sheetsOptions =
          GoogleSheetsOptions.newBuilder()
              .setSkipLeadingRows(1) // Optionally skip header row.
              .setRange("us-states!A20:B49") // Optionally set range of the sheet to query from.
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the Sheets file.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).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 | IOException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Interroger des tables externes

Pour en savoir plus, consultez la section Interroger des données Drive.

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.