Consulta 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 personales y en archivos compartidos. 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
  • Avro
  • Hojas de cálculo de Google

Para realizar una consulta 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 que se usa para hacer referencia a la fuente de datos de Google Drive puede ser permanente o temporal.

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.

    En este comando, ocurre lo siguiente:

    • file_id es el ID alfanumérico que corresponde a tu archivo de Google Drive

Si no, puedes abrir el archivo para recuperar el URI. Por ejemplo, si quieres recuperar el URI para un archivo de Hojas de cálculo de Google, sigue estos pasos:

  1. Dirígete a Hojas de cálculo de Google.

  2. Abre la hoja de cálculo y copia el URI en la barra de direcciones del navegador. El URI debería tener el siguiente aspecto: https://docs.google.com/spreadsheets/d/file_id.

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

Console

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

IU clásica

Sigue los pasos de autenticación basada en la Web cuando crees una tabla permanente en la IU web de BigQuery. 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 realizar la autenticación 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 este ejemplo, sigue las instrucciones de configuración de Python incluidas en la Guía de inicio rápido de BigQuery con bibliotecas cliente. A fin de 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)

Tablas externas permanentes y temporales

Puedes consultar una fuente de datos externa en BigQuery; para ello, usa una tabla permanente o una temporal. Una tabla permanente es una tabla que se crea en un conjunto de datos y está vinculada a tu fuente de datos externa. Debido a que la tabla es permanente, puedes usar controles de acceso a nivel de conjunto de datos para compartir la tabla con otras personas que también tengan acceso a la fuente de datos externa subyacente y consultarla 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 usas una tabla temporal, no se crea 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).

Consulta datos de Google Drive con tablas externas permanentes

Permisos y alcances obligatorios

Cuando consultas datos externos en Google Drive mediante una tabla permanente, necesitas permisos para ejecutar un trabajo de consulta a nivel de proyecto o superior, crear una tabla que apunte a los datos externos y acceder a los datos de la tabla. Cuando tus datos externos se almacenan en Google Drive, también necesitas permisos para acceder al archivo de Google Drive vinculado a la tabla externa.

Permisos de BigQuery

Como mínimo, se requieren los siguientes permisos para crear y consultar una tabla externa en BigQuery.

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

Las siguientes funciones de Cloud IAM predefinidas incluyen los permisos bigquery.tables.create y bigquery.tables.getData:

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

Las siguientes funciones de Cloud IAM predefinidas incluyen los permisos bigquery.jobs.create:

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

Además, si un usuario tiene permisos bigquery.datasets.create, cuando crea un conjunto de datos, se le otorga el acceso bigquery.dataOwner. El acceso bigquery.dataOwner proporciona al usuario la capacidad de crear tablas externas en el conjunto de datos, pero aún se requieren los permisos bigquery.jobs.create para consultar los datos.

Si deseas obtener más información sobre las funciones de Cloud IAM y los permisos en BigQuery, consulta Control de acceso.

Permisos de Google Drive

Como mínimo, para consultar datos externos en Google Drive, necesitas tener acceso View en el 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 usan la cuenta de servicio para llamar a las API de Google Cloud Platform.

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.

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

Crea y consulta una tabla externa permanente

Puedes crear una tabla permanente vinculada a tu fuente de datos externa mediante las siguientes opciones:

  • Usando GCP Console o la IU web de BigQuery clásica
  • Mediante el comando mk de la herramienta de línea de comandos
  • Mediante la creación de ExternalDataConfiguration cuando usas el método de API tables.insert
  • Mediante las bibliotecas cliente

Para consultar una fuente de datos externa mediante una tabla permanente, debes crear una tabla en un conjunto de datos de BigQuery 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 usar controles de acceso a nivel de conjunto de datos para compartir la tabla con otras personas que también tengan 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 usas el método de API tables.insert para crear una tabla externa permanente, debes crear un recurso de tabla que incluya una definición de esquema y ExternalDataConfiguration. Configura el parámetro autodetect como true para habilitar la detección automática de esquema en las fuentes de datos compatibles.
  • Si usas la CLI para crear una tabla externa permanente, puedes usar un archivo de definición de tablas, crear y usar tu propio archivo de esquema o ingresar el esquema de forma intercalada (en la línea de comandos). 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 usas la consola o la IU web clásica de BigQuery para crear una tabla externa permanente, puedes ingresar el esquema de tabla de forma manual o usar la detección automática de esquemas en las fuentes de datos admitidas.

Para crear una tabla externa, sigue estos pasos:

Console

  1. Abre la IU web de BigQuery en GCP Console.
    Ir a GCP Console

  2. En el panel de navegación, en la sección Recursos, expande tu proyecto y selecciona un conjunto de datos. Haz clic en Crear tabla (Create table) en el lado derecho de la ventana.

    Crear tabla

  3. En la página Crear tabla, en la sección Fuente, realiza lo siguiente:

    • En Crear tabla desde, selecciona Drive.

    • En el campo Seleccionar URI de Drive, ingresa el URI de Google Drive. Ten en cuenta que los comodines no son compatibles con 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
  4. (Opcional) Si eliges Hojas de cálculo de Google, en el cuadro Rango de hojas (Opcional), especifica la hoja y el rango de celdas que se consultarán. Puedes especificar un nombre de hoja, o puedes especificar sheet_name!top_left_cell_id:bottom_right_cell_id para un rango de celdas; por ejemplo, “Sheet1!A1:B20”. Si no se especifica rango de hoja, se usa la primera hoja del archivo.

  5. En la página Crear tabla en la sección Destino (Destination), haz lo siguiente: * En Nombre del conjunto de datos (Dataset name), selecciona el conjunto de datos apropiado y, en el campo Nombre de tabla, escribe el nombre de la tabla que quieres crear en BigQuery.

    Seleccionar conjunto de datos

    • Verifica que el Tipo de tabla (Table type) esté configurado como Tabla externa.
  6. En la sección Esquema, ingresa la definició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 no está disponible para exportaciones de Cloud Datastore o Cloud Firestore y archivos Avro. La información del esquema de estos tipos de archivos se recupera de forma automática desde los datos de origen autodescriptivos.
    • Ingresa la información del esquema de forma manual de la siguiente manera:
      • Habilita Editar como texto y, luego, ingresa el esquema de la tabla como un arreglo JSON. Nota: Puedes ver el esquema de una tabla existente en formato JSON si ingresas el siguiente comando en la CLI: bq show --format=prettyjson dataset.table.
      • Usa Agregar campo para ingresar el esquema de forma manual.
  7. Haz clic en Crear tabla.

  8. Si es necesario, selecciona tu cuenta y haz clic en Permitir para otorgar a las herramientas cliente de BigQuery acceso a Google Drive.

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

Una vez que se completó 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.

IU clásica

  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, luego, en Crear tabla nueva.

  3. En la página Crear tabla, en la sección Datos fuente, haz lo siguiente:

    • 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
  4. En la página Crear tabla, en la sección Tabla de destino, realiza lo siguiente:

    • 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 por el momento 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 de manera automática a partir de los archivos de Avro.

    • En el caso de los archivos 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 de forma manual 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 (Allow) 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, sujeta a las limitaciones para las fuentes de datos externas.

Una vez que se completó 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

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

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

En este comando, ocurre lo siguiente:

  • definition_file es la ruta de acceso al archivo de definición de tablas en tu máquina local.
  • dataset es el nombre del conjunto de datos que contiene la tabla.
  • table es el nombre de la tabla que quieres crear.

Por ejemplo, con el comando siguiente, se 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

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

bq mk \
--external_table_definition=schema@source_format=drive_uri \
dataset.table

En este comando, ocurre lo siguiente:

  • schema es la definición de 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 es el nombre del conjunto de datos que contiene la tabla.
  • table es el nombre de la tabla que quieres crear.

Por ejemplo, el siguiente comando crea una tabla permanente denominada 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 comando siguiente.

bq mk \
--external_table_definition=schema_file@source_format=drive_uri \
dataset.table

En este comando, ocurre lo siguiente:

  • 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 es el nombre del conjunto de datos que contiene la tabla.
  • table es el nombre de la tabla que quieres crear.

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 creó la tabla permanente, puedes ejecutar una consulta en la tabla como si fuera una tabla nativa de BigQuery, sujeta a las limitaciones para las fuentes de datos externas.

Una vez que se completó 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

Crea una ExternalDataConfiguration cuando uses el método de API tables.insert. Especifica la propiedad schema o configura la propiedad autodetect como true para habilitar la detección automática de esquemas en las fuentes de datos compatibles.

Python

Antes de probar este ejemplo, sigue las instrucciones de configuración de Python incluidas en la Guía de inicio rápido de BigQuery con bibliotecas cliente. A fin de 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)))

Realiza consultas en datos de Google Drive con tablas temporales

Si quieres realizar una consulta sobre 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 esquema o el esquema proporcionado para crear la tabla externa temporal y se ejecuta la consulta en la tabla externa temporal. La CLI y la API de BigQuery admiten las consultas de fuentes de datos externas con una tabla temporal.

Cuando usas una tabla externa temporal, no se crea una tabla en uno de los 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. Consultar una fuente de datos externa con una tabla temporal es útil para las consultas únicas ad-hoc sobre datos externos o procesos de extracción, transformación y carga (ETL).

Permisos necesarios

Cuando consultas datos externos en Google Drive con una tabla temporal, necesitas permisos para ejecutar un trabajo de consulta a nivel de proyecto o superior, además de acceso al conjunto de datos que contiene la tabla que apunta a los datos externos. Cuando consultas datos en Google Drive, también necesitas permisos para acceder al archivo de Google Drive que los contiene.

Permisos de BigQuery

Como mínimo, se requieren los siguientes permisos para consultar una tabla externa en BigQuery mediante una tabla temporal.

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

Las siguientes funciones predefinidas de Cloud IAM incluyen los permisos bigquery.tables.getData:

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

Las siguientes funciones de Cloud IAM predefinidas incluyen los permisos bigquery.jobs.create:

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

Además, si un usuario tiene permisos bigquery.datasets.create, cuando crea un conjunto de datos, se le otorga el acceso bigquery.dataOwner. El acceso bigquery.dataOwner permite al usuario crear y acceder a tablas externas en el conjunto de datos, pero aún se requieren los permisos bigquery.jobs.create para consultar los datos.

Si deseas obtener más información sobre las funciones de Cloud IAM y los permisos en BigQuery, consulta Control de acceso.

Permisos de Google Drive

Como mínimo, para consultar datos externos en Google Drive, necesitas tener acceso View en el archivo de Google Drive vinculado a la tabla externa.

Crea y consulta una tabla temporal

Puedes crear y consultar una tabla temporal vinculada a una fuente de datos externa mediante la CLI, la API o las bibliotecas cliente.

CLI

Usa el comando bq query con la marca --external_table_definition para consultar una tabla temporal vinculada a una fuente de datos externa. Cuando usas la CLI para consultar 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::definition_file \
'query'

En este comando, ocurre lo siguiente:

  • location es tu ubicación. La marca --location es opcional.
  • table es el nombre de la tabla temporal que quieres crear.
  • definition_file es la ruta de acceso al archivo de definición de tablas en tu máquina local.
  • query es la consulta que quieres enviar a la tabla temporal.

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

bq query \
--external_table_definition=sales::sales_def \
'SELECT
   Region,Total_sales
 FROM
   sales'

Para 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 comando siguiente.

bq --location=location query \
--external_table_definition=table::schema@source_format=drive_uri \
'query'

En este comando, ocurre lo siguiente:

  • location es tu ubicación. La marca --location es opcional.
  • table es el nombre de la tabla temporal que quieres crear.
  • 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 quieres enviar 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'

En este comando, ocurre lo siguiente:

  • 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 quieres enviar a la tabla temporal.

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

Antes de probar este ejemplo, sigue las instrucciones de configuración de Python incluidas en la Guía de inicio rápido de BigQuery con bibliotecas cliente. A fin de 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 pseudocolumna llamada _FILE_NAME. Esta columna contiene la ruta de acceso calificada por completo al archivo al que pertenece la fila. Esta columna solo está disponible para tablas que hagan referencia a 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. Para seleccionar el valor de _FILE_NAME, debes usar un alias. La consulta de ejemplo siguiente muestra la selección de _FILE_NAME mediante la asignación del alias fn a la pseudocolumna.

bq query \
--project_id=project_id \
--use_legacy_sql=false \
'SELECT
   name,
   _FILE_NAME AS fn
 FROM
   `dataset.table_name`
 WHERE
   name contains "Alex"' 

En este comando, ocurre lo siguiente:

  • project_id es un ID del 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.