Consulta los datos de Drive

Consulta los datos de Drive

En este documento, se describe cómo usar BigQuery para consultar datos almacenados en Drive.

BigQuery admite consultas en archivos de Drive personales y en archivos compartidos. Para obtener más información sobre Drive, consulta el Centro de aprendizaje de G Suite.

Puedes consultar archivos en Drive con los siguientes formatos:

  • Valores separados por comas (CSV)
  • JSON delimitado por saltos de línea
  • Avro
  • Hojas de cálculo

Para realizar una consulta en una fuente de datos externa de Google Drive, proporciona la ruta de acceso del URI de 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.

Limitación

Una consulta de BigQuery puede sobrecargar las Hojas de cálculo, lo que genera un error como Resources exceeded during query execution: Google Sheets service overloaded.. Considera simplificar tu hoja de cálculo. por ejemplo, mediante la minimización del uso de fórmulas.

Recupera el URI de Drive

A fin de crear una tabla externa que haga referencia a una fuente de datos de Drive, debes proporcionar el URI de Drive. Para recuperar el URI de Drive, consulta Comparte un vínculo al archivo.

Formato del URI

  • https://docs.google.com/spreadsheets/d/FILE_ID

    o

  • https://drive.google.com/open?id=FILE_ID

En el ejemplo anterior, FILE_ID es el ID alfanumérico para tu archivo de Drive.

Habilita el acceso a Drive

Para acceder a los datos alojados en Google, se necesita un alcance de OAuth adicional, tanto cuando defines la fuente federada como cuando ejecutas la consulta. Aunque no está habilitado de forma predeterminada, puede incluirse en Cloud Console, la herramienta de línea de comandos de bq o la API a través de los siguientes mecanismos:

Console

Sigue los pasos de autenticación basada en la Web cuando crees una tabla permanente en Cloud Console. Cuando se te solicite, haz clic en Permitir para otorgar a las herramientas cliente de BigQuery acceso a Drive.

gcloud

Para habilitar el acceso a Drive, sigue estos pasos:

  1. Ingresa el siguiente comando para asegurarte de que tengas la versión más reciente de Google Cloud CLI.

    gcloud components update

  2. Ingresa el siguiente comando para autenticar con Drive.

    gcloud auth login --enable-gdrive-access

API

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

Python

Antes de probar esta muestra, sigue las instrucciones de configuración para Python incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Python de BigQuery. 

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

Antes de probar este ejemplo, sigue las instrucciones de configuración para Java incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Java. 

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

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 para compartir la tabla con otras personas que también tengan acceso a la fuente de datos externa subyacente y también puedes hacer consultas 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 mediante 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 Drive mediante tablas externas permanentes

Permisos y alcances obligatorios

Cuando consultas datos externos en 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 Drive, también necesitas permisos para acceder al archivo de 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 predefinidas de IAM incluyen los permisos bigquery.tables.create y bigquery.tables.getData:

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

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

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

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

Para obtener más información sobre las funciones y los permisos de IAM en BigQuery, consulta Funciones y permisos predefinidos.

Permisos de Drive

Como mínimo, para consultar datos externos en Drive, debes tener acceso View al archivo de 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 permisos controlan el acceso de la instancia a los productos de Google Cloud, incluido Drive. Las aplicaciones que se ejecutan en la VM usan la cuenta de servicio para llamar a las API de Google Cloud.

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

Para obtener más información acerca de cómo aplicar alcances a una instancia de Compute Engine, consulta Cambia 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:

  • Usa Cloud Console.
  • Usar el comando mk de la herramienta de línea de comandos de bq
  • Mediante la creación de un ExternalDataConfiguration cuando uses el método de API tables.insert
  • Usa 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 para compartir la tabla con otras personas que también tengan acceso a la fuente de datos externa subyacente.

Cuando creas una tabla externa permanente, puedes especificar el esquema de las siguientes maneras:

Para crear una tabla externa, sigue estos pasos:

Console

  1. En Cloud Console, abre la página de BigQuery.

Ir a BigQuery

  1. En el panel Explorador, expande tu proyecto y selecciona un conjunto de datos.

  2. Expande la opción Acciones y haz clic en Abrir.

  3. En el panel de detalles, haz clic en Crear tabla  (Create table).

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

    • En Crear tabla desde, selecciona Drive.

    • En el campo Seleccionar URI de Drive, ingresa el URI de Drive. Ten en cuenta que los comodines no son compatibles con los URI de Drive.

    • En Formato de archivo, selecciona el formato de tus datos. Entre los formatos válidos para datos de Drive, se incluyen los siguientes:

      • Valores separados por comas (CSV)
      • JSON delimitado por saltos de línea
      • Avro
      • Hojas de cálculo

  5. Si eliges Hojas de cálculo, en el cuadro Rango de hojas (opcional):, especifica la hoja y el rango de celdas que se consultarán (opcional). Puedes especificar un nombre de hoja o especificar sheet_name!top_left_cell_id:bottom_right_cell_id para un rango de celdas; por ejemplo, “Hoja1!A1:B20” (“Sheet1!A1:B20”). Si no se especifica un Rango de hoja, se usa la primera hoja del archivo.

  6. En la sección Destination (Destino) de la página Create table (Crear tabla), haz lo siguiente:

    • Para Nombre del conjunto de datos (Dataset name), elige el conjunto de datos apropiado y, en el campo Nombre de la tabla (Table name), ingresa el nombre de la tabla que estás creando en BigQuery.

      Selecciona un conjunto de datos

    • Verifica que el Tipo de tabla esté configurado como Tabla externa.

  7. En la sección Esquema, ingresa la definición de 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 las exportaciones de Datastore, Firestore ni 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: Para ver el esquema de una tabla existente en formato JSON, ingresa el siguiente comando en la herramienta de línea de comandos de bq:bq show --format=prettyjson DATASET.TABLE.
      • Usa Agregar campo para ingresar el esquema de forma manual.

  8. Haz clic en Crear tabla.

  9. Si es necesario, selecciona tu cuenta y haz clic en Permitir para otorgar a las herramientas cliente de BigQuery acceso a 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. Consulta Descarga, guarda y exporta datos para obtener más información.

bq

Debes crear una tabla en la herramienta de línea de comandos de bq mediante el comando bq mk. Cuando usas la herramienta de línea de comandos de bq para 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 Drive por medio de un archivo de definición de tablas, ingresa el siguiente comando:

bq mk \
--external_table_definition=DEFINITION_FILE \
DATASET.TABLE

Aquí:

  • DEFINITION_FILE es la ruta de acceso al archivo de definición de tabla de tu máquina local.
  • DATASET es el nombre del conjunto de datos que contiene la tabla.
  • TABLE es el nombre de la tabla que crearás.

Por ejemplo, con el siguiente comando, se crea una tabla permanente llamada mytable mediante un archivo de definición de tabla denominado 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 siguiente comando:

bq mk \
--external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

En el ejemplo anterior, se ilustra 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 Drive.
  • DATASET es el nombre del conjunto de datos que contiene la tabla.
  • TABLE es el nombre de la tabla que creas.

Por ejemplo, con el siguiente comando, se crea una tabla permanente llamada sales vinculada a un archivo de Hojas de cálculo almacenado en 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 el ejemplo anterior, se ilustra lo siguiente:

  • SCHEMA_FILE es la ruta 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 Drive.
  • DATASET es el nombre del conjunto de datos que contiene la tabla.
  • TABLE es el nombre de la tabla que creas.

Por ejemplo, con el siguiente comando, se crea una tabla llamada sales vinculada a un archivo CSV almacenado en Drive mediante el 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. Consulta Descarga, guarda y exporta datos para obtener más información.

API

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

Python

Antes de probar esta muestra, sigue las instrucciones de configuración para Python incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Python de BigQuery. 

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

Antes de probar este ejemplo, sigue las instrucciones de configuración para Java incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Java. 

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

Consulta datos de Drive mediante 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 tabla o el esquema proporcionado para crear la tabla externa temporal, y es en esta donde se ejecuta la consulta. La herramienta de línea de comandos de bq y la API admiten las consultas a una fuente de datos externa mediante el uso de 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 Drive con una tabla temporal, necesitas permisos para ejecutar trabajos 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 Drive, también necesitas permisos para acceder al archivo de 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 IAM incluyen los permisos bigquery.tables.getData:

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

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

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

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

Para obtener más información sobre las funciones y los permisos de IAM en BigQuery, consulta Funciones y permisos predefinidos.

Permisos de Drive

Como mínimo, para consultar datos externos en Drive, debes tener acceso View al archivo de 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 herramienta de línea de comandos de bq, la API o las bibliotecas cliente.

bq

Para consulta una tabla temporal vinculada a una fuente de datos externa, usa el comando bq query con la marca --external_table_definition. Cuando usas la herramienta de línea de comandos de bq 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'

Aquí:

  • LOCATION es tu ubicación. La marca --location es opcional.
  • TABLE es el nombre de la tabla temporal que creas.
  • DEFINITION_FILE es la ruta de acceso al archivo de definición de tabla de tu máquina local.
  • QUERY es la consulta que enviarás a la tabla temporal.

Por ejemplo, con el siguiente comando, se crea y consulta una tabla temporal llamada sales mediante un archivo de definición de tablas denominado 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'

Aquí:

  • LOCATION es tu ubicación. La marca --location es opcional.
  • TABLE es el nombre de la tabla temporal que crearás.
  • SCHEMA es la definición de esquema intercalada 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 Drive.
  • QUERY es la consulta que envías a la tabla temporal.

Por ejemplo, con el siguiente comando, se crea y consulta una tabla temporal llamada sales vinculada a un archivo CSV almacenado en 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_FORMT=DRIVE_URI \
'QUERY'

Aquí:

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

Por ejemplo, con el siguiente comando, se crea y consulta una tabla temporal llamada sales vinculada a un archivo CSV almacenado en Drive mediante el 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 esta muestra, sigue las instrucciones de configuración para Python incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Python de BigQuery. 

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

Antes de probar este ejemplo, sigue las instrucciones de configuración para Java incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Java. 

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

Seudocolumna _FILE_NAME

Las tablas basadas en fuentes de datos externas proporcionan una seudocolumna llamada _FILE_NAME. Esta columna contiene la ruta completamente calificada 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. En la consulta de ejemplo siguiente, se 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 Google Cloud CLI).
  • dataset es el nombre del conjunto de datos que almacena la tabla externa permanente.
  • table_name es el nombre de la tabla externa permanente.

Cuando la consulta tiene un predicado de filtro en la seudocolumna _FILE_NAME, BigQuery intentará omitir los archivos de lectura que no cumplen con el filtro. Se aplican recomendaciones similares a las consultas a tablas particionadas por tiempo de transferencia mediante seudocolumnas cuando se construyen predicados de consulta con la seudocolumna _FILE_NAME.