Esta página se ha traducido con Cloud Translation API.

Consultar datos de Drive

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

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

Puedes consultar datos de Drive desde una tabla externa permanente o desde una tabla externa temporal que creas al ejecutar la consulta.

Limitaciones

Para obtener información sobre las limitaciones relacionadas con las tablas externas, consulta Limitaciones de las tablas externas.

Roles obligatorios

Para consultar tablas externas de Drive, asegúrate de que tienes los siguientes roles:

Lector de datos de BigQuery (roles/bigquery.dataViewer)
Usuario de BigQuery (roles/bigquery.user)

En función de tus permisos, puedes asignarte estos roles o pedirle a tu administrador que te los asigne. Para obtener más información sobre cómo conceder roles, consulta el artículo Ver los roles que se pueden conceder en los recursos.

Para ver los permisos exactos de BigQuery que se necesitan para consultar tablas externas, despliega la sección Permisos necesarios:

Permisos obligatorios

bigquery.jobs.create
bigquery.readsessions.create (Solo es necesario si lees datos con la API de lectura de almacenamiento de BigQuery).
bigquery.tables.get
bigquery.tables.getData

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

Ámbitos de las instancias de Compute Engine

Cuando creas una instancia de Compute Engine, puedes especificar una lista de ámbitos para la instancia. Los ámbitos 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 APIs de Google Cloud .

Si configuras una instancia de Compute Engine para 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 Drive, debes añadir el ámbito de OAuth de Drive (https://www.googleapis.com/auth/drive.readonly) a la instancia.

Para obtener información sobre cómo aplicar ámbitos a una instancia de Compute Engine, consulta el artículo Cambiar la cuenta de servicio y los ámbitos de acceso de una instancia. Para obtener más información sobre las cuentas de servicio de Compute Engine, consulta el artículo Cuentas de servicio.

Consultar datos de Drive con tablas externas permanentes

Una vez que hayas creado una tabla externa de Drive, podrás consultarla con la sintaxis de GoogleSQL, como si fuera una tabla estándar de BigQuery. Por ejemplo, SELECT field1, field2 FROM mydataset.my_drive_table;.

Consultar datos de Drive con tablas temporales

Consultar una fuente de datos externa mediante una tabla temporal es útil para realizar consultas puntuales y ad hoc sobre datos externos, o para 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, a continuación, usar esa definición de tabla en un comando o una llamada para consultar la tabla temporal. Puedes proporcionar la definición de la tabla de cualquiera de las siguientes formas:

Un archivo de definición de tabla
Una definición de esquema insertada
Un archivo de esquema JSON

El archivo de definición de tabla o el esquema proporcionado se usan para crear la tabla externa temporal y la consulta se ejecuta en la tabla externa temporal.

Cuando usas una tabla externa temporal, no creas una tabla en uno de tus conjuntos de datos de BigQuery. Como la tabla no se almacena de forma permanente en un conjunto de datos, no se puede compartir con otros usuarios.

Crear y consultar tablas temporales

Puedes crear y consultar una tabla temporal vinculada a una fuente de datos externa con la herramienta de línea de comandos bq, la API o las bibliotecas de cliente.

bq

Para consultar 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 bq para consultar una tabla temporal vinculada a una fuente de datos externa, puedes identificar el esquema de la tabla de las siguientes formas:

Un archivo de definición de tabla (almacenado en tu máquina local)
Una definición de esquema insertada
Un archivo de esquema JSON (almacenado en tu máquina local)

Para consultar una tabla temporal vinculada a tu fuente de datos externa mediante un archivo de definición de tabla, introduce el siguiente comando.

bq --location=LOCATION query \
--external_table_definition=TABLE::DEFINITION_FILE \
'QUERY'

Donde:

LOCATION es tu ubicación. La marca --location es opcional.
TABLE es el nombre de la tabla temporal que vas a crear.
DEFINITION_FILE es la ruta del archivo de definición de tabla 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 mediante un archivo de definición de tabla llamado sales_def.

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

Para consultar una tabla temporal vinculada a tu fuente de datos externa mediante una definición de esquema insertada, introduce el siguiente comando.

bq --location=LOCATION query \
--external_table_definition=TABLE::SCHEMA@SOURCE_FORMAT=DRIVE_URI \
'QUERY'

Donde:

LOCATION es tu ubicación. La marca --location es opcional.
TABLE es el nombre de la tabla temporal que vas a crear.
SCHEMA es la definición de esquema insertada 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, el siguiente comando crea una tabla temporal llamada sales y consulta su contenido. Esta tabla está 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 consultar una tabla temporal vinculada a tu fuente de datos externa mediante un archivo de esquema JSON, introduce el siguiente comando.

bq --location=LOCATION query \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMT=DRIVE_URI \
'QUERY'

Donde:

LOCATION es tu ubicación. La marca --location es opcional.
SCHEMA_FILE es la ruta al archivo de esquema JSON de 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, el siguiente comando 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

Crea una configuración de tarea de consulta. Consulta Consultar datos para obtener información sobre cómo llamar a jobs.query y jobs.insert.
Especifique la fuente de datos externa creando un ExternalDataConfiguration.

Python

Antes de probar este ejemplo, sigue las Pythoninstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Python de BigQuery.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta el artículo Configurar la autenticación para bibliotecas de 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 Javainstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Java de BigQuery.

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

Solución de problemas

Error string: Resources exceeded during query execution: Google Sheets service overloaded.

Puede tratarse de un error temporal que se puede solucionar volviendo a ejecutar la consulta. Si el error persiste después de volver a ejecutar una consulta, prueba a simplificar la hoja de cálculo. Por ejemplo, puedes minimizar el uso de fórmulas. Para obtener más información, consulta las limitaciones de las tablas externas.

Siguientes pasos

Consulta información sobre cómo usar SQL en BigQuery.
Consulta información sobre las tablas externas.
Consulta las cuotas de BigQuery.