Crea tablas externas de Google Drive

En este documento, se describe cómo crear una tabla externa sobre los datos almacenados en Google Drive.

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

Puedes crear tablas externas en archivos de Drive que tengan los siguientes formatos:

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

Antes de comenzar

Antes de crear una tabla externa, recopila información y asegúrate de tener permiso para crear la tabla.

Recupera los URI de Drive

A fin de crear una tabla externa que haga referencia a una fuente de datos de Google Drive, debes proporcionar el URI de Drive. Puedes recuperar el URI de Drive directamente desde la URL de tus datos de Drive:

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 permiso de OAuth adicional. Aunque no está habilitado de forma predeterminada, puede incluirse en la consola de Google Cloud, la herramienta de línea de comandos de bq o la API a través de los siguientes mecanismos:

Consola

Sigue los pasos de autenticación basada en la Web cuando crees una tabla permanente en la consola de Google Cloud. 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:

Ingresa el siguiente comando para asegurarte de que tengas la versión más reciente de Google Cloud CLI.
```
gcloud components update
```
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 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.
#
# If you are using credentials from gcloud, you must authorize the
# application first with the following command:
#
# gcloud auth application-default login \
#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/cloud-platform",
    ]
)

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

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.

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

Funciones obligatorias

Para crear una tabla externa, necesitas el permiso bigquery.tables.create de BigQuery Identity and Access Management (IAM).

Cada una de los siguientes roles predefinidos de Identity and Access Management incluye este permiso:

Editor de datos de BigQuery (roles/bigquery.dataEditor)
Propietario de datos de BigQuery (roles/bigquery.dataOwner)
BigQuery Admin (roles/bigquery.admin)

Si no eres principal en estos roles, pídele a tu administrador que te otorgue acceso o que cree la tabla externa por ti.

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

Crea tablas externas

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

Usa la consola de Google Cloud
Usa 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 crear una tabla externa, sigue estos pasos:

Consola

En la consola de Google Cloud, abre la página de BigQuery.

Ir a BigQuery

En el panel Explorador, expande tu proyecto y elige un conjunto de datos.
Expande la opción Acciones y haz clic en Abrir.
En el panel de detalles, haz clic en Crear tabla (Create table).
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
  - Sheets
  Nota: En la consola de Google Cloud, la opción de copia de seguridad de Datastore o Avro oculta la opción de Detección automática porque estos tipos de archivo son autodescriptivos.
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.
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.
- Verifica que el Tipo de tabla esté configurado como Tabla externa.
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.
Haz clic en Crear tabla.
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 estándar 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 con 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 deseas crear.

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

Aquí:

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 deseas crear.

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

Aquí:

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 deseas crear.

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 estándar de BigQuery, sujeta a las limitaciones para las fuentes de datos externas.

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

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.
#
# If you are using credentials from gcloud, you must authorize the
# application first with the following command:
#
# gcloud auth application-default login \
#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
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]
options = external_config.google_sheets_options
assert options is not None
options.skip_leading_rows = 1  # Optionally skip header row.
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)

results = client.query_and_wait(sql)  # Make an API request.

# Wait for the query to complete.
w_states = list(results)
print(
    "There are {} states with names starting with W in the selected range.".format(
        len(w_states)
    )
)

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.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 tablas externas

Para obtener más información, visita Consulta Datos de Drive.

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.