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

Crear tablas externas de Google Drive

En este documento se describe cómo crear una tabla externa a partir de datos almacenados en Google Drive.

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

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

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

Antes de empezar

Antes de crear una tabla externa, reúne información y asegúrate de que tienes permiso para crearla.

Recuperar URIs de Drive

Para crear una tabla externa de una fuente de datos de Google Drive, debe proporcionar el URI de Drive. Puedes obtener el URI de Drive directamente de la URL de tus datos de Drive:

Formato de URI

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

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

donde FILE_ID es el ID alfanumérico de tu archivo de Drive.

Autenticar y habilitar el acceso a Drive

Para acceder a los datos alojados en Drive, se necesita un ámbito de OAuth adicional. Para autenticarte en BigQuery y habilitar el acceso a Drive, haz lo siguiente:

Consola

Sigue los pasos de autenticación basados en web cuando crees una tabla externa en la consola de Google Cloud . Cuando se te pida, haz clic en Permitir para dar acceso a las herramientas de cliente de BigQuery a Drive.

gcloud

In the Google Cloud console, activate Cloud Shell.

Activate Cloud Shell

At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
Introduce el siguiente comando para asegurarte de que tienes la versión más reciente de la CLI de Google Cloud.
```
gcloud components update
```
Introduce el siguiente comando para autenticarte en Drive.
```
gcloud auth login --enable-gdrive-access
```

API

Solicita el permiso de OAuth adecuado para Drive, además del permiso de BigQuery:

Inicia sesión ejecutando el comando gcloud auth login --enable-gdrive-access.
Ejecuta el comando gcloud auth print-access-token para obtener el token de acceso de OAuth con el permiso de Drive que se usa en tu API.

Python

Crea un ID de cliente de OAuth.
Configura las credenciales predeterminadas de la aplicación (ADC) en tu entorno local con los ámbitos necesarios. Para ello, haz lo siguiente:
1. Instala la CLI de Google Cloud y, a continuación, inicialízala ejecutando el siguiente comando:
```
gcloud init
```
2. Crea credenciales de autenticación local para tu cuenta de Google:
```
gcloud auth application-default login \
    --client-id-file=CLIENT_ID_FILE \
    --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
```
  Sustituye CLIENT_ID_FILE por el archivo que contiene tu ID de cliente de OAuth.
  
  Para obtener más información, consulta Credenciales de usuario proporcionadas mediante gcloud CLI.

Java

Crea un ID de cliente de OAuth.
Configura las credenciales predeterminadas de la aplicación (ADC) en tu entorno local con los ámbitos necesarios. Para ello, haz lo siguiente:
1. Instala la CLI de Google Cloud y, a continuación, inicialízala ejecutando el siguiente comando:
```
gcloud init
```
2. Crea credenciales de autenticación local para tu cuenta de Google:
```
gcloud auth application-default login \
    --client-id-file=CLIENT_ID_FILE \
    --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
```
  Sustituye CLIENT_ID_FILE por el archivo que contiene tu ID de cliente de OAuth.
  
  Para obtener más información, consulta Credenciales de usuario proporcionadas mediante gcloud CLI.

Roles obligatorios

Para crear una tabla externa, necesitas el permiso de bigquery.tables.creategestión de identidades y accesos (IAM) de BigQuery.

Cada uno de los siguientes roles predefinidos de gestión de identidades y accesos incluye este permiso:

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

Si no eres un principal de ninguno de estos roles, pide a tu administrador que te dé acceso o que cree la tabla externa por ti.

Para obtener más información sobre los roles y permisos de gestión de identidades y accesos en BigQuery, consulta el artículo sobre roles y permisos predefinidos.

Crear tablas externas

Para crear una tabla permanente vinculada a tu fuente de datos externa, sigue estos pasos:

Usar la Google Cloud consola
Usar el comando mk de la herramienta de línea de comandos bq
Crear un ExternalDataConfiguration cuando se usa el método de la API tables.insert
Usar las bibliotecas de cliente

Para crear una tabla externa, sigue estos pasos:

Consola

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

Ir a BigQuery

En el panel Explorador, expande tu proyecto y selecciona un conjunto de datos.
Abre la opción Acciones y haz clic en Abrir.
En el panel de detalles, haz clic en Crear tabla .
En la página Crear tabla, ve a la sección Fuente:
- En Crear tabla a partir de, selecciona Drive.
- En el campo Seleccionar URI de Drive, introduce el URI de Drive. Ten en cuenta que no se admiten comodines en los URIs de Drive.
- En Formato de archivo, seleccione el formato de los datos. Los formatos válidos para los datos de Drive son los siguientes:
  - Valores separados por comas (CSV)
  - JSON delimitado por líneas nuevas
  - Avro
  - Hojas de cálculo
  Nota: En la consola Google Cloud , si elige Avro o Datastore como copia de seguridad, se oculta la opción Detectar automáticamente porque estos tipos de archivo se describen automáticamente.
(Opcional) Si elige Hojas de cálculo, en el cuadro Intervalo de la hoja (opcional), especifique la hoja y el intervalo de celdas que quiera consultar. Puedes especificar un nombre de hoja o sheet_name!top_left_cell_id:bottom_right_cell_id para un intervalo de celdas, como "Hoja1!A1:B20". Si no se especifica ningún Intervalo de la hoja, se usará la primera hoja del archivo.
En la página Crear tabla, ve a la sección Destino:
- En Nombre del conjunto de datos, elige el conjunto de datos adecuado y, en el campo Nombre de la tabla, introduce el nombre de la tabla que vas a crear en BigQuery.
- Verifica que el Tipo de tabla sea Tabla externa.
En la sección Schema (Esquema), introduce la definición de schema.
- En el caso de los archivos JSON o CSV, puedes marcar la opción Detección automática para habilitar la detección automática del esquema. La opción Detección automática no está disponible para las exportaciones de Datastore, las exportaciones de Firestore ni los archivos Avro. La información del esquema de estos tipos de archivo se obtiene automáticamente de los datos de origen autodescriptivos.
- Para introducir la información del esquema manualmente, siga estos pasos:
  - Habilitando la opción Editar como texto e introduciendo el esquema de la tabla como una matriz JSON. Nota: Para ver el esquema de una tabla en formato JSON, introduce el siguiente comando en la herramienta de línea de comandos bq: bq show --format=prettyjson DATASET.TABLE.
  - Usando Añadir campo para introducir el esquema manualmente.
Haz clic en Crear tabla.
Si es necesario, selecciona tu cuenta y, a continuación, haz clic en Permitir para dar acceso a Drive a las herramientas de cliente de BigQuery.

Después, puede ejecutar una consulta en la tabla como si fuera una tabla estándar de BigQuery, sujeta a las limitaciones de las fuentes de datos externas.

Una vez que se haya completado la consulta, puedes descargar los resultados en formato CSV o JSON, guardarlos como tabla o guardarlos en Hojas de cálculo. Para obtener más información, consulta el artículo Descargar, guardar y exportar datos.

bq

Para crear una tabla en la herramienta de línea de comandos bq, usa el comando bq mk. Cuando usas la herramienta de línea de comandos bq para crear una tabla 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 crear una tabla permanente vinculada a tu fuente de datos de Drive mediante un archivo de definición de tabla, introduce el siguiente comando.

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

Donde:

DEFINITION_FILE es la ruta del archivo de definición de tabla 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 vas a crear.

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

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

Donde:

SCHEMA es la definición del 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 vas a crear.

Por ejemplo, el siguiente comando 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 mediante un archivo de esquema JSON, introduce el siguiente comando.

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

Donde:

SCHEMA_FILE es la ruta al archivo de esquema JSON de 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 vas a crear.

Si tu archivo de definición de tabla contiene configuración específica de Hojas de cálculo, puedes omitir las filas iniciales y especificar un intervalo de hojas definido.

En el siguiente ejemplo 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 creada la tabla permanente, puede ejecutar una consulta en ella como si fuera una tabla estándar de BigQuery, sujeta a las limitaciones de las fuentes de datos externas.

API

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

Python

from google.cloud import bigquery
import google.auth

credentials, project = google.auth.default()

# 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

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 {

      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault();

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

Consultar tablas externas

Para obtener más información, consulta Consultar datos de Drive.

La pseudocolumna `_FILE_NAME`

Las tablas basadas en fuentes de datos externas proporcionan una pseudocolumna llamada _FILE_NAME. Esta columna contiene la ruta completa del archivo al que pertenece la fila. Esta columna solo está disponible en las tablas que hacen 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.

Crear tablas externas de Google Drive

Antes de empezar

Recuperar URIs de Drive

Autenticar y habilitar el acceso a Drive

Consola

gcloud

API

Python

Java

Roles obligatorios

Crear tablas externas

Consola

bq

API

Python

Java

Consultar tablas externas

La pseudocolumna _FILE_NAME

La pseudocolumna `_FILE_NAME`