Cómo consultar datos de Google Drive

En esta página, se explica cómo usar BigQuery para realizar consultas de los datos almacenados en Google Drive.

BigQuery admite consultas en archivos de Google Drive personal y en archivos de unidades de equipo. Si deseas obtener más información acerca de Google Drive, consulta el Centro de aprendizaje de G Suite.

Puedes realizar consultas en archivos de Google Drive con los siguientes formatos:

  • Valores separados por comas (CSV)
  • JSON (delimitado por saltos de línea)
  • Archivos de Avro
  • Hojas de cálculo de Google (solo la primera pestaña)

Para realizar una consulta directamente en una fuente de datos externa de Google Drive, proporciona la ruta de acceso del URI de Google Drive a tus datos y crea una tabla externa que haga referencia a esa fuente de datos. La tabla utilizada para hacer referencia a la fuente de datos de Google Drive puede ser una tabla permanente o una tabla temporal.

Cómo recuperar el URI de Google Drive

A fin de crear una tabla externa que haga referencia a una fuente de datos de Google Drive, debes proporcionar el URI de Google Drive. Para obtener el URI de Google Drive, haz lo siguiente:

  1. Ve a Google Drive.

  2. Haz clic derecho en el archivo y selecciona Obtener vínculo para compartir. El URI debería tener este formato: https://drive.google.com/open?id=[FILE_ID].

    Aquí:

    • [FILE_ID] es el ID alfanumérico que corresponde a tu archivo de Google Drive.

Cómo habilitar el acceso a Google Drive

Para acceder a los datos alojados en Google Drive, se necesita un alcance de OAuth adicional, tanto cuando defines la fuente federada como cuando ejecutas la consulta. Aunque no está habilitado de manera predeterminada, puede incluirse en la IU, CLI o a través de la API por medio de los siguientes mecanismos:

IU web


Sigue los pasos de autenticación basada en la Web cuando crees una tabla permanente en la IU web. Cuando te lo soliciten, haz clic en Permitir a fin de otorgar a las herramientas cliente de BigQuery acceso a Google Drive.

CLI


A fin de permitir el acceso a Google Drive, haz lo siguiente:

  1. Ingresa el siguiente comando para asegurarte de que tengas la versión más reciente de la herramienta de línea de comandos.

    gcloud components update
    
  2. Ingresa el siguiente comando para autenticar con Google Drive.

    gcloud auth login --enable-gdrive-access
    

API


Si usas la API de BigQuery, solicita el alcance de OAuth para Google Drive además del alcance para BigQuery.

Python

Antes de probar esta muestra, sigue las instrucciones de configuración de Python que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Si deseas obtener más información, consulta la Documentación de referencia de la API de Python de BigQuery.

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)

Controles de acceso y alcances

Controles de acceso para tablas externas permanentes

Puedes compartir el acceso a una tabla externa permanente vinculada a una fuente de datos de Google Drive. Puedes compartir el acceso con usuarios (incluidas las cuentas de servicio) o grupos. A fin de realizar consultas en una tabla externa, tus usuarios o grupos necesitan (como mínimo):

  • Acceso de READER o bigquery.dataViewer al conjunto de datos que contiene la tabla externa
  • Acceso de bigquery.user al proyecto que contiene el conjunto de datos (a fin de ejecutar trabajos de consulta)
  • Acceso de Can view al archivo de Google Drive vinculado a la tabla externa

Alcances para instancias de Compute Engine

Cuando creas una instancia de Compute Engine, puedes especificar una lista de alcances para la instancia. Los alcances controlan el acceso de la instancia a los productos de Google Cloud Platform, incluido Google Drive. Las aplicaciones que se ejecutan en la VM utilizan la cuenta de servicio para llamar a las API de Google Cloud.

Si configuras una instancia de Compute Engine de modo que se ejecute como una cuenta de servicio, y esa cuenta de servicio accede a una tabla externa vinculada a una fuente de datos de Google Drive, debes agregar el alcance de OAuth para Google Drive (https://www.googleapis.com/auth/drive) a la instancia.

Si deseas obtener más información acerca de cómo aplicar alcances a una instancia de Compute Engine, consulta Cómo cambiar la cuenta de servicio y los alcances de acceso de una instancia. Si deseas obtener más información sobre las cuentas de servicio de Compute Engine, consulta Cuentas de servicio.

Tablas externas permanentes y temporales

Puedes consultar una fuente de datos externa en BigQuery; para ello, utiliza una tabla permanente o una tabla temporal. Cuando usas una tabla permanente, creas una tabla en un conjunto de datos de BigQuery que está vinculado a la fuente de datos externa. Debido a que la tabla es permanente, puedes utilizar controles de acceso a nivel del conjunto de datos para compartir la tabla con otras personas que también tienen acceso a la fuente de datos externa subyacente, y puedes consultar la tabla en cualquier momento.

Cuando consultas una fuente de datos externa con una tabla temporal, envías un comando que incluye una consulta y crea una tabla no permanente vinculada a la fuente de datos externa. Cuando utilizas una tabla temporal, no creas una tabla en uno de tus conjuntos de datos de BigQuery. Debido a que la tabla no se almacena de forma permanente en un conjunto de datos, no puede compartirse con otras personas. La consulta de una fuente de datos externa con el uso de una tabla temporal es útil para las consultas únicas ad-hoc sobre datos externos, o para procesos de extracción, transformación y carga (ETL).

Cómo consultar datos de Google Drive mediante tablas externas permanentes

Para realizar consultas en una fuente de datos externa por medio de una tabla permanente, debes crear una tabla en un conjunto de datos de BigQuery que está vinculado a tu fuente de datos externa. Los datos no se almacenan en la tabla de BigQuery. Debido a que la tabla es permanente, puedes utilizar controles de acceso a nivel del conjunto de datos a fin de compartir la tabla con otras personas que también tienen acceso a la fuente de datos externa subyacente.

Existen tres maneras de especificar la información del esquema cuando creas una tabla externa permanente en BigQuery:

  • Si estás utilizando la API para crear una tabla externa permanente, en primer lugar, debes crear un archivo de definición de tablas que defina el esquema y los metadatos correspondientes a la fuente de datos externa. Cuando creas un archivo de definición de tablas, puedes habilitar la detección automática de esquemas en las fuentes de datos admitidas.
  • Si estás utilizando la CLI a fin de crear una tabla externa permanente, puedes usar un archivo de definición de tablas, puedes crear y usar tu propio archivo de esquema, o puedes ingresar el esquema intercalado (en la línea de comandos).
  • Si estás utilizando la consola o la IU web de BigQuery clásica con el fin de crear una tabla externa permanente, puedes ingresar el esquema de tabla de forma manual o usar la detección automática de esquemas para las fuentes de datos admitidas.

Para realizar consultas en datos de Google Drive mediante una tabla externa permanente, haz lo siguiente:

  • Crea un archivo de definición de tablas (para la API y, de forma opcional, para la CLI).
  • Crea una tabla en BigQuery vinculada a la fuente de datos externa.
  • Realiza una consulta en la tabla vinculada a la fuente de datos externa.

Cómo crear una tabla externa permanente

Puedes crear una tabla externa permanente vinculada a tu fuente de datos externa mediante la IU web, la CLI o la API.

IU web

  1. Ve a la IU web de BigQuery.

    Ir a la IU web de BigQuery

  2. En el panel de navegación, desplázate sobre un conjunto de datos, haz clic en el ícono de flecha hacia abajo imagen del ícono de flecha hacia abajo y haz clic en Crear tabla nueva.

  3. En la página Crear tabla, en la sección Datos de origen:

    • Para la Ubicación, selecciona Google Drive y en el campo de origen, ingresa el URI de Google Drive. Ten en cuenta que no se admiten los comodines en los URI de Google Drive.
    • En Formato de archivo, selecciona el formato de tus datos. Los formatos válidos para los datos de Google Drive incluyen los siguientes:

      • Valores separados por comas (CSV)
      • JSON (delimitado por saltos de línea)
      • Avro
      • Hojas de cálculo de Google (solo la primera pestaña)
  4. En la página Crear tabla, en la sección Tabla de destino:

    • Para el Nombre de tabla, elige el conjunto de datos apropiado y, en el campo de nombre de tabla, ingresa el nombre de la tabla permanente que estás creando en BigQuery.
    • Verifica que el Tipo de tabla esté configurado como Tabla externa.
  5. En la sección Esquema, ingresa la información del esquema.

    • Para los archivos JSON o CSV, puedes marcar la opción de Detección automática a fin de habilitar la detección automática de esquemas. La detección automática de esquemas en la IU web no se admite actualmente en las Hojas de cálculo de Google (aunque está disponible cuando se usan la CLI y la API). Además, no está disponible la Detección automática para las fuentes de datos de Avro. La información del esquema se obtiene automáticamente a partir de los archivos de Avro.

    • En el caso de los archivos de CSV, JSON o de Hojas de cálculo de Google, puedes ingresar la información del esquema de forma manual como se describe a continuación:

      • Haz clic en Editar como texto y, luego, ingresa el esquema de la tabla en formato JSON.
      • Usa Agregar campo para ingresar manualmente el esquema.
  6. Selecciona los elementos aplicables en la sección Opciones y haz clic en Crear tabla.

  7. Selecciona tu cuenta y, luego, haz clic en Permitir para otorgar a las herramientas cliente de BigQuery acceso a Google Drive.

    Diálogo de acceso de las herramientas cliente

Luego, puedes ejecutar una consulta en la tabla como si fuera una tabla nativa de BigQuery, sujeto a las limitaciones para las fuentes de datos externas.

Una vez que se ha completado tu consulta, puedes descargar los resultados en formato CSV o JSON, guardarlos como una tabla o guardarlos en Hojas de cálculo de Google. Consulta Descarga, guarda y exporta datos para obtener más información.

CLI

A fin de crear una tabla en la herramienta de línea de comandos de BigQuery, debes emplear el comando bq mk. Cuando usas la CLI con el fin de crear una tabla vinculada a una fuente de datos externa, puedes identificar el esquema de la tabla por medio de las siguientes opciones:

  • Un archivo de definición de tablas (almacenado en tu máquina local)
  • Una definición de esquema intercalado
  • Un archivo de esquema JSON (almacenado en tu máquina local)

Para crear una tabla permanente vinculada a tu fuente de datos de Google Drive por medio de un archivo de definición de tablas, ingresa el siguiente comando:

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

Aquí:

  • [DEFINITION_FILE] es la ruta de acceso al archivo de definición de tablas en tu máquina local.
  • [DATASET_ID] es el nombre del conjunto de datos que contiene la tabla.
  • [TABLE_NAME] es el nombre de la tabla que creas.

Por ejemplo, el siguiente comando crea una tabla permanente llamada mytable por medio de un archivo de definición de tablas llamado mytable_def.

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

A fin de crear una tabla permanente vinculada a tu fuente de datos externa por medio de una definición de esquema intercalado, ingresa el siguiente comando.

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

Aquí:

  • [SCHEMA] es la definición del esquema (en el formato [FIELD]:[DATA_TYPE], [FIELD]:[DATA_TYPE]).
  • [SOURCE_FORMAT] es CSV, NEWLINE_DELIMITED_JSON, AVRO o GOOGLE_SHEETS.
  • [DRIVE_URI] es tu URI de Google Drive.
  • [DATASET_ID] es el nombre del conjunto de datos que contiene la tabla.
  • [TABLE_NAME] es el nombre de la tabla que creas.

Por ejemplo, el siguiente comando crea una tabla permanente llamada sales vinculada a un archivo de Hojas de cálculo de Google almacenado en Google Drive con la siguiente definición de esquema: 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

Para crear una tabla permanente vinculada a tu fuente de datos externa por medio de un archivo de esquema JSON, ingresa el siguiente comando.

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

Aquí:

  • [SCHEMA_FILE] es la ruta de acceso al archivo de esquema JSON en tu máquina local.
  • [SOURCE_FORMAT] es CSV, NEWLINE_DELIMITED_JSON, AVRO o GOOGLE_SHEETS.
  • [DRIVE_URI] es tu URI de Google Drive.
  • [DATASET_ID] es el nombre del conjunto de datos que contiene la tabla.
  • [TABLE_NAME] es el nombre de la tabla que creas.

Por ejemplo, el siguiente comando crea una tabla llamada sales vinculada a un archivo CSV almacenado en Google Drive por medio del archivo de esquema /tmp/sales_schema.json.

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

Una vez que se ha creado la tabla permanente, puedes ejecutar una consulta en la tabla como si fuera una tabla nativa de BigQuery, sujeto a las limitaciones para las fuentes de datos externas.

Una vez que se ha completado tu consulta, puedes descargar los resultados en formato CSV o JSON, guardarlos como una tabla o guardarlos en Hojas de cálculo de Google. Consulta Descarga, guarda y exporta datos para obtener más información.

API

Especifica las fuentes de datos externas mediante las propiedades externalDataConfiguration.

Python

Antes de probar esta muestra, sigue las instrucciones de configuración de Python que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Python de BigQuery.

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

Cómo realizar consultas en datos de Google Drive con tablas temporales

Si quieres realizar una consulta en una fuente de datos externa sin crear una tabla permanente, debes ejecutar un comando que combine lo siguiente:

  • Un archivo de definición de tablas con una consulta
  • Una definición de esquema intercalado con una consulta
  • Un archivo de definición de esquema JSON con una consulta

Se emplea el archivo de definición de tablas o el esquema proporcionado a fin de crear la tabla externa temporal, y se ejecuta la consulta en la tabla externa temporal. La consulta de una fuente de datos externa mediante una tabla temporal es compatible con la CLI y la API de BigQuery.

Cuando utilizas una tabla externa temporal, no creas una tabla en uno de tus conjuntos de datos de BigQuery. Debido a que la tabla no se almacena de forma permanente en un conjunto de datos, no puede compartirse con otras personas. La consulta de una fuente de datos externa con el uso de una tabla temporal es útil para las consultas únicas ad-hoc sobre datos externos, o para procesos de extracción, transformación y carga (ETL).

Cómo crear y consultar una tabla temporal

Puedes crear una tabla temporal vinculada a una fuente de datos externa y realizar consultas en ella por medio de la CLI o la API.

CLI

A fin de realizar una consulta en una tabla temporal vinculada a una fuente de datos externa, debes usar el comando bq query con la marca --external_table_definition. Cuando usas la CLI para realizar una consulta en una tabla temporal vinculada a una fuente de datos externa, puedes identificar el esquema de la tabla por medio de las siguientes opciones:

  • Un archivo de definición de tablas (almacenado en tu máquina local)
  • Una definición de esquema intercalado
  • Un archivo de esquema JSON (almacenado en tu máquina local)

Para realizar una consulta en una tabla temporal vinculada a tu fuente de datos externa por medio de un archivo de definición de tablas, ingresa el siguiente comando:

bq --location=[LOCATION] query --external_table_definition=[TABLE_NAME]::[DEFINITION_FILE] '[QUERY]'

Aquí:

  • [LOCATION] es tu ubicación. La marca --location es opcional.
  • [TABLE_NAME] es el nombre de la tabla temporal que creas.
  • [DEFINITION_FILE] es la ruta de acceso al archivo de definición de tablas en tu máquina local.
  • [QUERY] es la consulta que envías a la tabla temporal.

Por ejemplo, el siguiente comando crea y consulta una tabla temporal llamada sales por medio de un archivo de definición de tablas llamado sales_def.

bq --location=US query --external_table_definition=sales::sales_def 'SELECT Region,Total_sales FROM sales;'

A fin de realizar una consulta en una tabla temporal vinculada a tu fuente de datos externa por medio de una definición de esquema intercalado, ingresa el siguiente comando.

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

Aquí:

  • [LOCATION] es tu ubicación. La marca --location es opcional.
  • [TABLE_NAME] es el nombre de la tabla temporal que creas.
  • [SCHEMA] es la definición del esquema intercalado (en el formato [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE]).
  • [SOURCE_FORMAT] es CSV, NEWLINE_DELIMITED_JSON, AVRO o GOOGLE_SHEETS.
  • [DRIVE_URI] es tu URI de Google Drive.
  • [QUERY] es la consulta que envías a la tabla temporal.

Por ejemplo, el siguiente comando crea y consulta una tabla temporal llamada sales vinculada a un archivo CSV almacenado en Google Drive con la siguiente definición de esquema: 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;'

Para realizar una consulta en una tabla temporal vinculada a tu fuente de datos externa por medio de un archivo de esquema JSON, ingresa el siguiente comando:

bq --location=[LOCATION] query --external_table_definition=[SCHEMA_FILE]@[SOURCE_FORMAT]=[DRIVE_URI] '[QUERY]'

Aquí:

  • [LOCATION] es tu ubicación. La marca --location es opcional.
  • [SCHEMA_FILE] es la ruta de acceso al archivo de esquema JSON en tu máquina local.
  • [SOURCE_FORMAT] es CSV, NEWLINE_DELIMITED_JSON, AVRO o GOOGLE_SHEETS.
  • [DRIVE_URI] es tu URI de Google Drive.
  • [QUERY] es la consulta que envías a la tabla temporal.

Por ejemplo, el siguiente comando crea y consulta una tabla temporal llamada sales vinculada a un archivo CSV almacenado en Google Drive por medio del archivo de esquema /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

Antes de probar esta muestra, sigue las instrucciones de configuración de Python que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Si deseas obtener más información, consulta la Documentación de referencia de la API de Python de BigQuery.

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

Seudocolumna _FILE_NAME

Las tablas basadas en fuentes de datos externas proporcionan una seudocolumna llamada _FILE_NAME. Esta columna contiene la ruta de acceso completa al archivo al que pertenece la fila. Esta columna solo está disponible para tablas que hagan referencia a los datos externos almacenados en Cloud Storage y Google Drive.

El nombre de columna _FILE_NAME está reservado, lo que significa que no puedes crear una columna con ese nombre en ninguna de tus tablas. A fin de seleccionar el valor de _FILE_NAME, debes usar un alias. La siguiente consulta de ejemplo muestra la selección de _FILE_NAME mediante la asignación del alias fn a la seudocolumna.

bq query --project_id=[PROJECT_ID] --use_legacy_sql=false 'SELECT name, _FILE_NAME AS fn from [DATASET].[TABLE_NAME] where name contains "Alex";' 

Aquí:

  • [PROJECT_ID] es un ID de proyecto válido (esta marca no es necesaria si usas Cloud Shell o si configuras un proyecto predeterminado en el SDK de Cloud).
  • [DATASET] es el nombre del conjunto de datos que almacena la tabla externa permanente.
  • [TABLE_NAME] es el nombre de la tabla externa permanente.

¿Te ha resultado útil esta página? Enviar comentarios:

Enviar comentarios sobre...

Si necesitas ayuda, visita nuestra página de asistencia.