Interroger des données Google Drive

Cette page explique comment utiliser BigQuery pour interroger des données stockées dans Google Drive.

BigQuery est compatible avec les requêtes portant sur les fichiers personnels Google Drive et les fichiers Drive d'équipe. Pour en savoir plus sur Google Drive, consultez le Centre de formation G Suite.

Dans Google Drive, vous pouvez interroger des fichiers aux formats suivants :

  • CSV (Comma-Separated Values)
  • JSON (délimité par un retour à la ligne)
  • Avro
  • Google Sheets (premier onglet uniquement)

Pour interroger directement une source de données externe à Google Drive, indiquez le chemin d'accès de l'URI de Google 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.

Récupérer l'URI de Google Drive

Pour créer une table externe destinée à une source de données Google Drive, vous devez fournir l'URI de Google Drive. Pour le récupérer, procédez comme suit :

  1. Accédez à Google Drive.

  2. Cliquez avec le bouton droit sur votre fichier, puis sélectionnez Obtenir le lien partageable. L'URI doit se présenter comme suit : https://drive.google.com/open?id=[FILE_ID].

    Où :

    • [FILE_ID] est l'identifiant alphanumérique de votre fichier Google Drive.

Activer l'accès Google 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 l'UI, dans la CLI ou via l'API en procédant comme suit :

UI Web


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

CLI


Pour activer l'accès à Google Drive, procédez comme suit :

  1. Saisissez la commande suivante pour vous assurer que vous disposez de la dernière version de l'outil de ligne de commande.

    gcloud components update
    
  2. Saisissez la commande suivante pour vous authentifier avec Google Drive.

    gcloud auth login --enable-gdrive-access
    

API


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

Python

Avant d'essayer cet exemple, suivez les instructions de configuration de Python décrites 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.

import google.auth
# from google.cloud import bigquery

# 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',
])
client = bigquery.Client(credentials=credentials, project=project)

Contrôles d'accès et champs d'application

Contrôles de l'accès aux tables externes permanentes

Vous pouvez partager l'accès à une table externe permanente qui est associée à une source de données Google Drive. Vous pouvez partager l'accès avec des utilisateurs (y compris des comptes de service) ou des groupes. Pour interroger la table externe, les utilisateurs ou groupes doivent (au minimum) disposer des droits suivants :

  • Accès READER ou bigquery.dataViewer à l'ensemble de données contenant la table externe
  • Accès bigquery.user au projet contenant l'ensemble de données (afin d'exécuter des tâches de requête)
  • Accès Can view au fichier Google 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 Platform, parmi lesquels Google 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 si ce compte de service accède à une table externe associée à une source de données Google Drive, vous devez ajouter à l'instance le champ d'application OAuth pour Google Drive (https://www.googleapis.com/auth/drive).

Pour en savoir plus sur l'application des champs d'application à une instance Compute Engine, reportez-vous à 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 l'article Comptes de service.

Tables externes permanentes et temporaires

Vous pouvez interroger une source de données externe dans BigQuery en utilisant une table permanente ou temporaire. Lorsque vous vous servez d'une table permanente, vous créez une table dans un ensemble de données BigQuery qui est associée à la 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 envoyez 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, vous ne pouvez pas la partager avec d'autres utilisateurs. L'interrogation d'une source de données externe à l'aide d'une table temporaire est utile pour des 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 Google Drive à l'aide de tables externes permanentes

Pour interroger une source de données externe à l'aide d'une table permanente, vous créez une table dans un ensemble de données BigQuery 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 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.

Il existe trois façons de spécifier des informations de schéma lors de la création d'une table externe permanente dans BigQuery :

  • Si vous utilisez l'API pour créer une table externe permanente, vous devez d'abord créer un fichier de définition de table qui spécifie le schéma et les métadonnées de la source de données externe. Lorsque vous créez un fichier de définition de table, vous pouvez activer la détection automatique de schéma pour les sources de données compatibles.
  • Si vous utilisez la CLI pour créer une table externe permanente, vous pouvez utiliser un fichier de définition de table, créer et utiliser votre propre fichier de schéma, ou saisir le schéma sur la ligne de commande.
  • Si vous utilisez l'UI Web pour créer une table externe permanente, vous pouvez saisir manuellement le schéma de la table ou utiliser la détection automatique de schéma pour les sources de données compatibles.

Pour interroger les données Google Drive à l'aide d'une table externe permanente, vous pouvez :

  • créer un fichier de définition de table (pour l'API et éventuellement pour la CLI) ;
  • créer dans BigQuery une table associée à la source de données externe ;
  • interroger la table associée à la source de données externe.

Créer une table externe permanente

Vous pouvez créer une table permanente associée à votre source de données externe à l'aide de l'UI Web, de la CLI ou de l'API.

UI Web

  1. Accédez à l'interface utilisateur Web de BigQuery.

    Accéder à l'UI Web de BigQuery

  2. Dans le panneau de navigation, passez la souris sur un ensemble de données. Ensuite, cliquez sur l'icône représentant une flèche vers le bas image de la flèche vers le bas, puis sur Créer une table.

  3. Dans la section Données sources de la page Créer une table, procédez comme suit :

    • Pour le paramètre Emplacement, sélectionnez Google Drive. Dans le champ "Source", indiquez l'URI Google Drive. Notez que les caractères génériques ne sont pas acceptés dans les URI Google Drive.
    • Pour le paramètre Format de fichier, sélectionnez le format de vos données. Les formats valides pour les données Google Drive sont les suivants :

      • CSV (Comma-Separated Values)
      • JSON (délimité par un retour à la ligne)
      • Avro
      • Google Sheets (premier onglet uniquement)
  4. Dans la section Table de destination de la page Créer une table, procédez comme suit :

    • Pour le Nom de la table, choisissez l'ensemble de données approprié et, dans le champ correspondant au nom de la table, saisissez le nom de la table à créer dans BigQuery.
    • Vérifiez que le paramètre Type de table est défini sur Table externe.
  5. Dans la section Schéma, saisissez les informations de schéma.

    • Pour les fichiers JSON ou CSV, vous pouvez cocher l'option Détection automatique pour activer la détection automatique de schéma. La détection automatique de schémas dans l'UI Web n'est actuellement pas disponible pour Google Sheets (elle est néanmoins disponible avec la CLI et l'API). Par ailleurs, la détection automatique n'est pas disponible pour les sources de données Avro. Les informations de schéma sont automatiquement extraites des fichiers Avro.

    • Pour les fichiers CSV, JSON ou Google Sheets, vous pouvez saisir manuellement les informations de schéma en utilisant les méthodes suivantes :

      • Cliquez sur Modifier sous forme de texte et saisissez le schéma de la table au format JSON.
      • Utilisez l'option Ajouter un champ pour saisir manuellement le schéma.
  6. Sélectionnez les éléments applicables dans la section Options, puis cliquez sur Créer une table.

  7. Sélectionnez votre compte, puis cliquez sur Autoriser pour permettre aux outils client BigQuery d'accéder à Google Drive.

    Boîte de dialogue d'accès aux outils client

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 que la requête a été 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. Pour en savoir plus, consultez l'article sur le téléchargement, l'enregistrement et l'exportation des données.

CLI

Vous pouvez créer une table dans l'outil de ligne de commande BigQuery à l'aide de la commande bq mk. Lorsque vous utilisez la CLI pour créer une table associée à une source de données externe, vous pouvez identifier le schéma de la table à l'aide des éléments suivants :

  • Un fichier de définition de table (stocké sur votre ordinateur local)
  • Une définition de schéma spécifiée sur la ligne de commande
  • Un fichier de schéma JSON (stocké sur votre ordinateur local)

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

bq mk --external_table_definition=[DEFINITION_FILE] [DATASET_ID].[TABLE_NAME]

Où :

  • [DEFINITION_FILE] correspond au chemin d'accès du fichier de définition de table sur l'ordinateur local.
  • [DATASET_ID] est le nom de l'ensemble de données contenant la table.
  • [TABLE_NAME] est le 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 spécifiée sur la ligne de commande, saisissez la commande suivante :

bq mk --external_table_definition=[SCHEMA]@[SOURCE_FORMAT]=[DRIVE_URI] [DATASET_ID].[TABLE_NAME]

Où :

  • [SCHEMA] correspond à la définition du schéma (au format [FIELD]:[DATA_TYPE], [FIELD]:[DATA_TYPE]).
  • [SOURCE_FORMAT] est le format CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_SHEETS.
  • [DRIVE_URI] correspond à votre URI Google Drive.
  • [DATASET_ID] est le nom de l'ensemble de données contenant la table.
  • [TABLE_NAME] 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 Google Sheets stocké dans Google 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_ID].[TABLE_NAME]

Où :

  • [SCHEMA_FILE] correspond au chemin d'accès du fichier de schéma JSON sur votre ordinateur local.
  • [SOURCE_FORMAT] est le format CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_SHEETS.
  • [DRIVE_URI] correspond à votre URI Google Drive.
  • [DATASET_ID] est le nom de l'ensemble de données contenant la table.
  • [TABLE_NAME] 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 Google 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 que la requête a été 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. Pour en savoir plus, consultez l'article sur le téléchargement, l'enregistrement et l'exportation des données.

API

Spécifiez la source de données externe à l'aide des propriétés externalDataConfiguration.

Python

Avant d'essayer cet exemple, suivez les instructions de configuration de Python décrites 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.

import google.auth
# from google.cloud import bigquery
# dataset_id = 'my_dataset'

# 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',
])
client = bigquery.Client(credentials=credentials, project=project)

# Configure the external data source
dataset_ref = client.dataset(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('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
table.external_data_configuration = external_config

# Create a permanent table linked to the Sheets 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 Google 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 spécifiée sur la ligne de commande 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, et la requête s'exécute sur cette table. Il est possible d'interroger une source de données externe à l'aide d'une table temporaire dans l'API et la CLI BigQuery.

Lorsque vous utilisez 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, vous ne pouvez pas la partager avec d'autres utilisateurs. L'interrogation d'une source de données externe à l'aide d'une table temporaire est utile pour des 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).

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 la CLI ou de l'API.

CLI

Pour interroger une table temporaire associée à une source de données externe, utilisez la commande bq query avec le paramètre --external_table_definition. Lorsque vous utilisez la CLI pour interroger une table temporaire associée à une source de données externe, vous pouvez identifier le schéma de la table à l'aide des éléments suivants :

  • Un fichier de définition de table (stocké sur votre ordinateur local)
  • Une définition de schéma spécifiée sur la ligne de commande
  • Un fichier de schéma JSON (stocké sur votre 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_NAME]::[DEFINITION_FILE] '[QUERY]'

Où :

  • [LOCATION] correspond à votre emplacement. L'indicateur --location est facultatif.
  • [TABLE_NAME] 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 l'ordinateur local.
  • [QUERY] est la requête que vous soumettez à la table temporaire.

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

bq --location=US 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 spécifiée sur la ligne de commande, saisissez la commande suivante :

bq --location=[LOCATION] query --external_table_definition=[TABLE_NAME]::[SCHEMA]@[SOURCE_FORMAT]=[DRIVE_URI] '[QUERY]'

Où :

  • [LOCATION] correspond à votre emplacement. L'indicateur --location est facultatif.
  • [TABLE_NAME] est le nom de la table temporaire que vous créez.
  • [SCHEMA] correspond à la définition du schéma spécifiée sur la ligne de commande (au format [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE]).
  • [SOURCE_FORMAT] est le format CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_SHEETS.
  • [DRIVE_URI] correspond à votre URI Google 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 Google 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_FORMAT]=[DRIVE_URI] '[QUERY]'

Où :

  • [LOCATION] correspond à votre emplacement. L'indicateur --location est facultatif.
  • [SCHEMA_FILE] correspond au chemin d'accès du fichier de schéma JSON sur votre ordinateur local.
  • [SOURCE_FORMAT] est le format CSV, NEWLINE_DELIMITED_JSON, AVRO ou GOOGLE_SHEETS.
  • [DRIVE_URI] correspond à votre URI Google 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 Google Drive à l'aide du fichier de schéma /tmp/sales_schema.json :

bq --location=US 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 de Python décrites 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.

import google.auth
# from google.cloud import bigquery

# 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',
])
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
table_id = 'us_states'
job_config = bigquery.QueryJobConfig()
job_config.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)  # 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)))

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 est disponible uniquement pour les tables qui renvoient à des données externes stockées dans Google 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. L'exemple de requête suivant illustre la sélection de _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] est un ID de projet valide (ce paramètre n'est pas requis si vous utilisez Cloud Shell ou si vous définissez un projet par défaut dans le SDK Cloud).
  • [DATASET] est le nom de l'ensemble de données où est stockée la table externe temporaire.
  • [TABLE_NAME] est le nom de la table externe permanente.

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.