Interroger des données Drive

Interroger des données Drive

Cette page explique comment utiliser BigQuery pour interroger des données stockées dans 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, accédez au Centre de formation G Suite.

Vous pouvez interroger des fichiers Drive dans les formats suivants :

• CSV (Comma-Separated Values)
• JSON délimité par des retours à la ligne
• Avro
• Sheets

Pour interroger une source de données externe à Google Drive, indiquez le chemin d'accès de l'URI Drive vers vos données et créez une table externe qui renvoie à cette source de données. La table utilisée pour référencer la source de données Google Drive peut être une table permanente ou une table temporaire.

Limite

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.

Récupérer l'URI Drive

Pour créer une table externe destinée à une source de données Drive, vous devez fournir l'URI Drive. Pour récupérer l'URI Drive, consultez la section Partager un lien vers le fichier.

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, lors de la définition de la source fédérée et lors de l'exécution de la requête. Bien qu'il ne soit pas activé par défaut, il peut être inclus dans Cloud Console, 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 Cloud Console. 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 :

1. Saisissez la commande suivante pour vous assurer que vous disposez de la dernière version de Google Cloud CLI.

   gcloud components update
   

2. Saisissez la commande suivante pour vous authentifier sur Drive.

   gcloud auth login --enable-gdrive-access
   

Remarque : Si vous utilisez Cloud Shell pour accéder à vos données Drive, vous n'avez pas besoin de mettre à jour Google Cloud CLI ni de vous authentifier auprès de Drive.

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

Afficher sur GitHub Commentaires

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)

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.

Afficher sur GitHub Commentaires

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();

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 Drive à l'aide de tables externes permanentes

Autorisations et champs d'application requis

Lorsque vous interrogez des données externes dans Drive à l'aide d'une table permanente, 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'autorisations vous permettant de créer une table qui pointe vers les données externes, et d'autorisations pour accéder aux données de la table. Lorsque vos données externes sont stockées dans Drive, vous devez également disposer d'autorisations pour accéder au fichier Drive lié à votre table externe.

Autorisations BigQuery

Vous devez au minimum disposer des autorisations suivantes pour créer et interroger une table externe dans BigQuery.

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

Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.tables.create et bigquery.tables.getData :

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.jobs.create :

• bigquery.user
• bigquery.jobUser
• bigquery.admin

En outre, si un utilisateur possède les autorisations bigquery.datasets.create, il obtient également un accès bigquery.dataOwner à l'ensemble de données qu'il crée. L'accès correspondant au rôle bigquery.dataOwner permet à l'utilisateur de créer des tables externes dans l'ensemble de données, mais les autorisations bigquery.jobs.create restent nécessaires pour 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 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.

Créer et interroger une table externe permanente

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

• Utiliser Cloud Console
• 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 ;
• Utiliser les bibliothèques clientes

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 :

• Fournir un schéma de table explicite.
• Utiliser la détection automatique de schéma.

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

3. Dans le panneau de détails, cliquez sur Créer une table.

4. 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 Cloud Console, si vous sélectionnez la sauvegarde Avro ou Datastore, l'option Automatically detect (Détection automatique) est masquée, car ces fichiers sont autodescriptifs.

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

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

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

8. Cliquez sur Create table.

9. 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 native, 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

La commande bq mk permet de créer une table dans l'outil de ligne de commande bq. 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 native, 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.

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

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.

Afficher sur GitHub Commentaires

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)

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

query_job = client.query(sql)  # 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 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.

Afficher sur GitHub Commentaires

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 données Drive à l'aide de tables temporaires

Pour interroger une source de données externe sans créer de table permanente, 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

Lorsque vous interrogez des données externes dans Drive à 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. Lorsque vous interrogez des données dans Google Drive, vous devez également disposer d'autorisations pour accéder au fichier Google Drive contenant vos données.

Autorisations BigQuery

Vous devez au minimum disposer des autorisations suivantes pour interroger une table externe dans BigQuery à l'aide d'une table temporaire.

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

Les rôles IAM prédéfinis suivants incluent des autorisations bigquery.tables.getData :

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.jobs.create :

• bigquery.user
• bigquery.jobUser
• bigquery.admin

En outre, si un utilisateur possède les autorisations bigquery.datasets.create, il obtient également un accès bigquery.dataOwner à l'ensemble de données qu'il crée. L'accès correspondant au rôle bigquery.dataOwner permet à l'utilisateur de créer des tables externes dans l'ensemble de données et d'y accéder, mais les autorisations bigquery.jobs.create restent nécessaires pour 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 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.

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

• Créez une configuration de tâche de requête. Pour savoir comment appeler jobs.query et jobs.insert, consultez la page Interroger des données.

• Spécifiez la source de données externe en créant une configuration ExternalDataConfiguration.

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.

Afficher sur GitHub Commentaires

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

Afficher sur GitHub Commentaires

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());
    }
  }
}

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.