Interroger des données Drive

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

BigQuery est compatible avec les requêtes portant sur les fichiers Drive personnels et les fichiers partagés. Pour en savoir plus sur Drive, consultez les supports de formation et articles d'aide Google Drive.

Vous pouvez interroger les données Drive à partir d'une table externe permanente ou d'une table externe temporaire que vous créez lors de l'exécution de la requête.

Rôles requis

Pour interroger des tables externes Drive, assurez-vous de disposer des rôles suivants :

  • Lecteur de données BigQuery (roles/bigquery.dataViewer)
  • Utilisateur BigQuery (roles/bigquery.user)

Selon vos autorisations, vous pouvez vous attribuer ces rôles ou demander à votre administrateur de vous les accorder. Pour en savoir plus sur l'attribution de rôles, consultez la page Afficher les rôles pouvant être attribués sur des ressources.

Pour afficher les autorisations BigQuery exactes requises pour interroger des tables externes, développez la section Autorisations requises :

Autorisations requises

Vous pouvez également obtenir ces autorisations avec des rôles personnalisés ou d'autres rôles prédéfinis.

Autorisations Drive

Au minimum, pour interroger des données externes dans Drive, vous devez disposer de l'accès View au fichier Drive associé à la table externe.

Champs d'application des instances Compute Engine

Lorsque vous créez une instance Compute Engine, vous pouvez spécifier une liste de champs d'application pour celle-ci. Les champs d'application contrôlent l'accès de l'instance aux produits Google Cloud, y compris Drive. Les applications exécutées sur la VM utilisent le compte de service 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 et que ce compte accède à une table externe associée à une source de données Drive, vous devez ajouter à l'instance le champ d'application OAuth pour Drive (https://www.googleapis.com/auth/drive.readonly).

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.

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

Après avoir créé une table externe Drive, vous pouvez l'interroger à l'aide de la syntaxe GoogleSQL, comme s'il s'agissait d'une table BigQuery standard. Par exemple, SELECT field1, field2 FROM mydataset.my_drive_table;.

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

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

Pour interroger une source de données externe sans créer de table permanente, vous devez fournir une définition de table pour la table temporaire, puis l'utiliser dans une commande ou un appel pour interroger la table temporaire. Vous pouvez fournir la définition de la table de l'une des manières suivantes :

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.

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.

Créer et interroger des tables temporaires

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 --external_table_definition permet d'interroger une table temporaire associée à une source de données externe à l'aide de la commande bq query. 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)

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 correspond à votre emplacement. L'option --location est facultative.
  • 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=DRIVE_URI \
'QUERY'

Où :

  • LOCATION correspond à votre emplacement. L'option --location est facultative.
  • TABLE est le 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 CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_SHEETS.
  • DRIVE_URI est votre URI Drive.
  • QUERY est 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 Drive avec la définition de schéma suivante : Region:STRING,Quarter:STRING,Total_sales:INTEGER.

bq --location=US query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'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_FORMT=DRIVE_URI \
'QUERY'

Où :

  • LOCATION correspond à votre emplacement. L'option --location est facultative.
  • SCHEMA_FILE correspond au chemin d'accès vers le fichier de schéma JSON sur votre ordinateur local.
  • SOURCE_FILE est CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_SHEETS.
  • DRIVE_URI est votre URI Drive.
  • QUERY est la requête que vous soumettez à la table temporaire.

Par exemple, la commande suivante crée et interroge une table temporaire 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 query \
--external_table_definition=sales::/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'SELECT
   Total_sales
 FROM
   sales'

API

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

# Configure the external data source and query job.
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]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1  # Optionally skip header row.
external_config.options.range = (
    "us-states!A20:B49"  # Optionally set range of the sheet to query from.
)
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.

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

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.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.TableResult;
import com.google.common.collect.ImmutableSet;
import java.io.IOException;

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

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

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

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).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 | IOException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Limites

Une requête BigQuery peut surcharger Sheets, ce qui entraîne une erreur telle que Resources exceeded during query execution: Google Sheets service overloaded.. Envisagez de simplifier votre feuille de calcul, par exemple, en minimisant l'utilisation de formules. Pour en savoir plus, consultez la section Limites des tables externes.

Étapes suivantes