Consulta los datos de Drive

En este documento, se describe cómo consultar datos almacenados en una tabla externa de Google Drive.

BigQuery admite consultas en archivos de Drive personales y en archivos compartidos. Para obtener más información sobre Drive, consulta Capacitación y ayuda de Google Drive.

Puedes consultar los datos de Drive desde una tabla externa permanente o una tabla externa temporal que crees cuando ejecutes la consulta.

Roles obligatorios

Para consultar las tablas externas de Drive, asegúrate de tener los siguientes roles:

  • Visualizador de datos de BigQuery (roles/bigquery.dataViewer)
  • BigQuery User (roles/bigquery.user)

Según los permisos que tengas, puedes otorgarte estos roles o pedirle a tu administrador que te los otorgue. Para obtener más información sobre cómo otorgar roles, consulta Visualiza los roles que se pueden otorgar en los recursos.

A fin de ver los permisos exactos de BigQuery que se requieren para consultar tablas externas, expande la sección Permisos necesarios:

Permisos necesarios

También puedes obtener estos permisos con roles personalizados o con otros roles 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.

Consulta datos de Drive mediante tablas externas permanentes

Después de crear una tabla externa de Drive, puedes consultarla con la sintaxis de Google SQL, de la misma manera que si fuera una tabla de BigQuery estándar. Por ejemplo, SELECT field1, field2 FROM mydataset.my_drive_table;

Consulta datos de Drive mediante tablas temporales

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

Para consultar una fuente de datos externa sin crear una tabla permanente, debes proporcionar una definición de tabla para la tabla temporal y, luego, usar esa definición de tabla en un comando o llamada para consultar la tabla temporal. Puedes proporcionar la definición de la tabla de cualquiera de las siguientes maneras:

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.

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.

Crea y consulta tablas temporales

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 deseas crear.
  • DEFINITION_FILE es la ruta de acceso al archivo de definición de tabla de tu máquina local.
  • QUERY es la consulta que deseas enviar 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 deseas crear.
  • 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 deseas enviar 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 deseas enviar 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 este ejemplo, 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. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Python.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas cliente. 

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.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas cliente. 

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

Limitaciones

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. Para obtener más información, consulta Limitaciones de la tabla externa.

¿Qué sigue?