Crea tablas externas de Cloud Storage

BigQuery admite la consulta de datos de Cloud Storage en los siguientes formatos:

  • Valores separados por comas (CSV)
  • JSON (delimitado por saltos de línea)
  • Avro
  • ORC
  • Parquet
  • Exportaciones de Datastore
  • Exportaciones de Firestore

BigQuery admite la consulta de datos de Cloud Storage de estas clases de almacenamiento:

  • Estándar
  • Nearline
  • Coldline
  • Archive

Para consultar una tabla externa de Cloud Storage, debes tener permisos en la tabla externa y en los archivos de Cloud Storage. En su lugar, recomendamos usar una tabla de BigLake, si es posible. Las tablas de BigLake proporcionan delegación de acceso, por lo que solo necesitas permisos en la tabla de BigLake para consultar los datos de Cloud Storage.

Asegúrate de tener en cuenta la ubicación de tu conjunto de datos y del depósito de Cloud Storage cuando consultas datos almacenados en Cloud Storage.

Antes de empezar

Otorga roles de Identity and Access Management (IAM) que les brindan a los usuarios los permisos necesarios para hacer cada tarea de este documento. Los permisos necesarios para hacer una tarea (si existen) se enumeran en la sección “Permisos necesarios” de la tarea.

Roles obligatorios

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)

También necesitas los siguientes permisos para acceder al bucket de Cloud Storage que contiene tus datos:

  • storage.buckets.get
  • storage.objects.get
  • storage.objects.list (obligatorio si usas un comodín de URI)

El rol predefinido de Identity and Access Management, Administrador de almacenamiento de Cloud Storage (roles/storage.admin) incluye estos permisos.

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.

Permisos de acceso para instancias de Compute Engine

Si, desde una instancia de Compute Engine necesitas consultar una tabla externa vinculada a una fuente de Cloud Storage, la instancia debe tener, al menos, el permiso de acceso de solo lectura de Cloud Storage (https://www.googleapis.com/auth/devstorage.read_only).

Los permisos controlan el acceso de la instancia de Compute Engine a los productos de Google Cloud, incluido Cloud Storage. Las aplicaciones que se ejecutan en la instancia usan la cuenta de servicio adjuntada a la instancia para llamar a las API de Google Cloud.

Si configuras una instancia de Compute Engine de modo que se ejecute como la cuenta de servicio predeterminada de Compute Engine, de forma predeterminada, se le otorga una cantidad de permisos predeterminados, incluido el permiso https://www.googleapis.com/auth/devstorage.read_only.

Si, en cambio, configuras la instancia con una cuenta de servicio personalizada, asegúrate de otorgar de forma explícita el permiso https://www.googleapis.com/auth/devstorage.read_only a la instancia.

Para obtener más información acerca de cómo aplicar permisos a una instancia de Compute Engine, consulta Cambia la cuenta de servicio y los permisos de acceso de una instancia. Para obtener más información sobre las cuentas de servicio de Compute Engine, consulta Cuentas de servicio.

Crea tablas externas en datos sin particiones

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

Elegir una de las opciones siguientes:

Console

  1. Ve a la página de BigQuery.

    Ir a BigQuery

  2. En el panel Explorador, expande tu proyecto y elige un conjunto de datos.

  3. Expande la opción Acciones y haz clic en Crear table.

  4. En la sección Fuente, especifica los siguientes detalles:

    1. En Crear tabla desde, elige Google Cloud Storage.

    2. En Elegir un archivo del bucket de GCS o usa un patrón de URI, elige un bucket y un archivo para usar o escribe la ruta con el formato gs://bucket_name/[folder_name/]file_name.

      No puedes especificar varios URI en la consola de Google Cloud, pero puedes elegir varios archivos si especificas un carácter comodín de asterisco (*). Por ejemplo, gs://mybucket/file_name* Si deseas obtener más información, consulta Compatibilidad de comodines para los URI de Cloud Storage.

      El bucket de Cloud Storage debe estar en la misma ubicación que el conjunto de datos que contiene la tabla que quieres crear.

    3. En Formato de archivo, elige el formato que coincida con tu archivo.

  5. En la sección Destino, especifica los siguientes detalles:

    1. En Proyecto, elige el proyecto en el que deseas crear la tabla.

    2. En Conjunto de datos, elige el conjunto de datos en el que deseas crear la tabla.

    3. En Tabla, escribe el nombre de la tabla que crearás.

    4. En Tipo de tabla, elige Tabla externa.

  6. En la sección Esquema, puedes habilitar la detección automática de esquemas o especificar un esquema de forma manual si tienes un archivo de origen. Si no tienes un archivo de origen, debes especificar un esquema de forma manual.

    • Para habilitar la detección automática de esquemas, elige la opción Detección automática.

    • Para especificar un esquema de forma manual, deja sin marcar la opción Detección automática. Habilita Cambiar como texto e escribe el esquema de la tabla como un array JSON.

  7. Para omitir las filas con valores de columna adicionales que no coinciden con el esquema, expande la sección Opciones avanzadas y elige Valores desconocidos.

  8. Haga clic en Crear tabla.

Después de crear la tabla permanente, puedes ejecutar una consulta en ella como si fuera una tabla de BigQuery nativa. Cuando se completa tu consulta, puedes exportar los resultados como archivos CSV o JSON, guardarlos como una tabla o en Hojas de cálculo de Google.

SQL

Puedes crear una tabla externa permanente si ejecutas la instrucción de DDL CREATE EXTERNAL TABLE. Puedes especificar el esquema de forma explícita o usar la detección automática de esquemas para inferir el esquema a partir de los datos externos.

  1. En la consola de Google Cloud, ve a la página de BigQuery.

    Ir a BigQuery

  2. En el editor de consultas, escribe la siguiente oración:

    CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
  OPTIONS (
    format ="TABLE_FORMAT",
    uris = ['BUCKET_PATH'[,...]]
    );

    Reemplaza lo siguiente:

    • PROJECT_ID: el nombre del proyecto en el que deseas crear la tabla, por ejemplo, myproject.
    • DATASET: el nombre del conjunto de datos de BigQuery en el que deseas crear una tabla, por ejemplo, mydataset
    • EXTERNAL_TABLE_NAME: el nombre de la tabla que deseas crear, por ejemplo, mytable.
    • TABLE_FORMAT: el formato de la tabla que deseas crear, por ejemplo, PARQUET
    • BUCKET_PATH: la ruta al bucket de Cloud Storage que contiene los datos de la tabla externa, en el formato ['gs://bucket_name/[folder_name/]file_name'].

      Puedes elegir varios archivos del bucket si especificas un carácter comodín de asterisco (*) en la ruta de acceso. Por ejemplo, ['gs://mybucket/file_name*'] Si deseas obtener más información, consulta Compatibilidad de comodines para los URI de Cloud Storage.

      Puedes especificar varios buckets para la opción uris si proporcionas varias rutas de acceso.

      En los siguientes ejemplos, se muestran valores uris válidos:

      • ['gs://bucket/path1/myfile.csv']
      • ['gs://bucket/path1/*.csv']
      • ['gs://bucket/path1/*', 'gs://bucket/path2/file00*']

      Cuando especificas valores uris que se orientan a varios archivos, todos esos archivos deben compartir un esquema compatible.

      Para obtener más información sobre cómo usar los URI de Cloud Storage en BigQuery, consulta Ruta de acceso al recurso de Cloud Storage.

  3. Haz clic en Ejecutar.

Si deseas obtener información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

Ejemplos

En el siguiente ejemplo, se usa la detección automática de esquemas para crear una tabla externa llamada sales que está vinculada a un archivo CSV almacenado en Cloud Storage:

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
  OPTIONS (
  format = 'CSV',
  uris = ['gs://mybucket/sales.csv']);

En el siguiente ejemplo, se especifica un esquema de manera explícita y se omite la primera fila en el archivo CSV:

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales (
  Region STRING,
  Quarter STRING,
  Total_Sales INT64
) OPTIONS (
    format = 'CSV',
    uris = ['gs://mybucket/sales.csv'],
    skip_leading_rows = 1);

bq

Para crear una tabla externa, usa el comando bq mk con la marca --external_table_definition. Esta marca contiene una ruta de acceso a un archivo de definición de tabla o una definición de tabla intercalada.

Opción 1: Archivo de definición de tablas

Usa el comando bq mkdef para crear un archivo de definición de tablas y, luego, pasa la ruta de acceso del archivo al comando bq mk de la siguiente manera:

bq mkdef --source_format=SOURCE_FORMAT \
  BUCKET_PATH > DEFINITION_FILE

bq mk --table \
  --external_table_definition=DEFINITION_FILE \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

Reemplaza lo siguiente:

  • SOURCE_FORMAT: El formato de la fuente de datos externos. Por ejemplo, CSV

  • BUCKET_PATH: la ruta al bucket de Cloud Storage que contiene los datos de la tabla, en el formato gs://bucket_name/[folder_name/]file_pattern.

    Puedes elegir varios archivos del bucket si especificas un carácter comodín de asterisco (*) en la ruta de acceso file_pattern. Por ejemplo, gs://mybucket/file00*.parquet Si deseas obtener más información, consulta Compatibilidad de comodines para los URI de Cloud Storage.

    Puedes especificar varios buckets para la opción uris si proporcionas varias rutas de acceso.

    En los siguientes ejemplos, se muestran valores uris válidos:

    • gs://bucket/path1/myfile.csv
    • gs://bucket/path1/*.parquet
    • gs://bucket/path1/file1*, gs://bucket1/path1/*

    Cuando especificas valores uris que se orientan a varios archivos, todos esos archivos deben compartir un esquema compatible.

    Para obtener más información sobre cómo usar los URI de Cloud Storage en BigQuery, consulta Ruta de acceso al recurso de Cloud Storage.

  • DEFINITION_FILE: es la ruta de acceso al archivo de definición de tabla en tu máquina local.

  • DATASET_NAME: es el nombre del conjunto de datos que contiene la tabla.

  • TABLE_NAME: es el nombre de la tabla que crearás.

  • SCHEMA: especifica una ruta a un archivo de esquema JSON o especifica el esquema en el formato field:data_type,field:data_type,....

Ejemplo:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

Para usar la detección automática de esquemas, configura la marca --autodetect=true en el comando mkdef y omite el esquema:

bq mkdef --source_format=CSV --autodetect=true \
  gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable

Opción 2: Definición de tabla intercalada

En lugar de crear un archivo de definición de tablas, puedes pasar la definición de tabla directamente al comando bq mk:

bq mk --table \
  --external_table_definition=@SOURCE_FORMAT=BUCKET_PATH \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

Reemplaza lo siguiente:

  • SOURCE_FORMAT: El formato de la fuente de datos externos.

    Por ejemplo, CSV.

  • BUCKET_PATH: la ruta al bucket de Cloud Storage que contiene los datos de la tabla, en el formato gs://bucket_name/[folder_name/]file_pattern.

    Puedes elegir varios archivos del bucket si especificas un carácter comodín de asterisco (*) en la ruta de acceso file_pattern. Por ejemplo, gs://mybucket/file00*.parquet Si deseas obtener más información, consulta Compatibilidad de comodines para los URI de Cloud Storage.

    Puedes especificar varios buckets para la opción uris si proporcionas varias rutas de acceso.

    En los siguientes ejemplos, se muestran valores uris válidos:

    • gs://bucket/path1/myfile.csv
    • gs://bucket/path1/*.parquet
    • gs://bucket/path1/file1*, gs://bucket1/path1/*

    Cuando especificas valores uris que se orientan a varios archivos, todos esos archivos deben compartir un esquema compatible.

    Para obtener más información sobre cómo usar los URI de Cloud Storage en BigQuery, consulta Ruta de acceso al recurso de Cloud Storage.

  • DATASET_NAME: Es el nombre del conjunto de datos que contiene la tabla.

  • TABLE_NAME: es el nombre de la tabla que crearás.

  • SCHEMA: especifica una ruta a un archivo de esquema JSON o especifica el esquema en el formato field:data_type,field:data_type,.... Para usar la detección automática de esquemas, omite este argumento.

Ejemplo:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

API

Llama al método tables.insert de la API y crea un ExternalDataConfiguration en el recurso Table que pasas.

Especifica la propiedad schema o establece la propiedad autodetect en true para habilitar la detección automática de esquemas para las fuentes de datos compatibles.

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.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
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;

// Sample to queries an external data source using a permanent table
public class QueryExternalGCSPerm {

  public static void runQueryExternalGCSPerm() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    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);
    queryExternalGCSPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {
      // 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.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the GCS file
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).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 e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

Antes de probar este ejemplo, sigue las instrucciones de configuración para Node.js 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 Node.js.

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 the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSPerm() {
  // Queries an external data source using a permanent table

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  // Configure the external data source
  const dataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row
    csvOptions: {skipLeadingRows: 1},
  };

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    externalDataConfiguration: dataConfig,
  };

  // Create an external table linked to the GCS file
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${datasetId}.${tableId}\`
  WHERE name LIKE 'W%'`;

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(query);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

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

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
table_id = "your-project.your_dataset.your_table_name"

# TODO(developer): Set the external source format of your table.
# Note that the set of allowed values for external data sources is
# different than the set used for loading data (see :class:`~google.cloud.bigquery.job.SourceFormat`).
external_source_format = "AVRO"

# TODO(developer): Set the source_uris to point to your data in Google Cloud
source_uris = [
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/a-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/c-twitter.avro",
]

# Create ExternalConfig object with external source format
external_config = bigquery.ExternalConfig(external_source_format)
# Set source_uris that point to your data in Google Cloud
external_config.source_uris = source_uris

# TODO(developer) You have the option to set a reference_file_schema_uri, which points to
# a reference file for the table schema
reference_file_schema_uri = "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro"

external_config.reference_file_schema_uri = reference_file_schema_uri

table = bigquery.Table(table_id)
# Set the external data configuration of the table
table.external_data_configuration = external_config
table = client.create_table(table)  # Make an API request.

print(
    f"Created table with external source format {table.external_data_configuration.source_format}"
)

Crea tablas externas en datos con particiones

Puedes crear una tabla externa para los datos con particiones de Hive que residen en Cloud Storage. Después de crear una tabla particionada de forma externa, no podrás cambiar la clave de partición. Debes volver a crear la tabla para cambiar la clave de partición.

Para crear una tabla externa para los datos con particiones de subárbol, elige una de las siguientes opciones:

Console

  1. En la consola de Google Cloud, ve a BigQuery.

    Ir a BigQuery

  2. En el panel Explorador, expande tu proyecto y elige un conjunto de datos.
  3. Haz clic en Ver acciones y, luego, en Crear tabla. Se abrirá el panel Crear tabla.
  4. En la sección Fuente, especifica los siguientes detalles:
    1. En Create table from (Crear tabla desde), elige Google Cloud Storage.
    2. En Seleccionar archivo del bucket de Cloud Storage, escribe la ruta de acceso a la carpeta de Cloud Storage mediante comodines. Por ejemplo, my_bucket/my_files*. El bucket de Cloud Storage debe estar en la misma ubicación que el conjunto de datos que contiene la tabla que deseas crear, agregar o reemplazar.
    3. En la lista Formato de archivo, elige el tipo de archivo.
    4. Elegir la casilla de verificación Partición de datos de origen y, luego, para Seleccionar prefijo de URI de origen, escribe el prefijo de URI de Cloud Storage. Por ejemplo, gs://my_bucket/my_files.
    5. En la sección Modo de inferencia de la partición, elige una de las siguientes opciones:
      • Inferir tipos de forma de automática: configura el modo de detección de esquemas de partición como AUTO.
      • Todas las columnas son strings: configura el modo de detección de esquemas de partición como STRINGS.
      • Proporcionar uno propio: configura el modo de detección de esquemas de partición como CUSTOM e escribe de forma manual la información del esquema para las claves de partición. Para obtener más información, consulta Proporciona un esquema de clave de partición personalizado.
    6. Opcional: Para solicitar un filtro de partición en todas las consultas de esta tabla, elige la casilla de verificación Exigir filtro de partición. Exigir un filtro de partición puede reducir los costos y mejorar el rendimiento. Para obtener más información, consulta Filtros de predicado obligatorios en claves de partición de consultas.
  5. En la sección Destino, especifica los siguientes detalles:
    1. En Proyecto, elige el proyecto en el que deseas crear la tabla.
    2. En Conjunto de datos, elige el conjunto de datos en el que deseas crear la tabla.
    3. En Tabla, escribe el nombre de la tabla que deseas crear.
    4. En Tipo de tabla, elige Tabla externa.
  6. En la sección Esquema, escribe la definición del esquema.
  7. Para habilitar la detección automática de esquemas, elige Detección automática.
  8. Para omitir las filas con valores de columna adicionales que no coinciden con el esquema, expande la sección Opciones avanzadas y elige Valores desconocidos.
  9. Haga clic en Crear tabla.

SQL

Usa la declaración DDL CREATE EXTERNAL TABLE.

En el siguiente ejemplo, se usa la detección automática de claves de partición de Hive:

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

Reemplaza lo siguiente:

  • SOURCE_FORMAT: Es el formato de la fuente de datos externa, como PARQUET.
  • GCS_URIS: Es la ruta a la carpeta de Cloud Storage, en el formato de comodín.
  • GCS_URI_SHARED_PREFIX: Es el prefijo del URI de origen sin el comodín.
  • BOOLEAN: especifica si se requiere un filtro de predicado en el momento de la consulta. Esta marca es opcional. El valor predeterminado es false.

En el ejemplo siguiente, se usan claves y tipos de partición personalizados de Hive mediante la enumeración en la cláusula WITH PARTITION COLUMNS:

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS (PARTITION_COLUMN_LIST)
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

Reemplaza lo siguiente:

  • PARTITION_COLUMN_LIST: una lista de columnas que siguen el mismo orden en la ruta de la carpeta de Cloud Storage, en el siguiente formato:
KEY1 TYPE1, KEY2 TYPE2

En el siguiente ejemplo, se crea una tabla particionada de forma externa. Usa la detección automática de esquemas para detectar el esquema de archivos y el diseño de partición de subárbol. Si la ruta externa es gs://bucket/path/field_1=first/field_2=1/data.parquet, las columnas de partición se detectan como field_1 (STRING) y field_2 (INT64).

CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable
WITH PARTITION COLUMNS
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

En el siguiente ejemplo, se crean una tabla particionada de forma externa mediante la especificación explícita de las columnas de partición. En este ejemplo, se supone que la ruta de acceso al archivo externo tiene el patrón gs://bucket/path/field_1=first/field_2=1/data.parquet.

CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable
WITH PARTITION COLUMNS (
field_1 STRING, -- column order must match the external path
field_2 INT64)
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

bq

Primero, usa el comando bq mkdef para crear un archivo de definición de tablas:

bq mkdef \
--source_format=SOURCE_FORMAT \
--hive_partitioning_mode=PARTITIONING_MODE \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
--require_hive_partition_filter=BOOLEAN \
 GCS_URIS > DEFINITION_FILE

Reemplaza lo siguiente:

  • SOURCE_FORMAT: El formato de la fuente de datos externos. Por ejemplo, CSV.
  • PARTITIONING_MODE: El modo de partición de subárbol. Usa uno de los siguientes valores:
    • AUTO: Detecta de forma automática los nombres y tipos de claves.
    • STRINGS: Convierte de forma de automática los nombres de las claves en strings.
    • CUSTOM: Codifica el esquema de clave en el prefijo de URI de origen.
  • GCS_URI_SHARED_PREFIX: Es el prefijo del URI de origen.
  • BOOLEAN especifica si se requiere un filtro de predicado en el momento de la consulta. Esta marca es opcional. El valor predeterminado es false.
  • GCS_URIS: Es la ruta a la carpeta de Cloud Storage, en el formato de comodín.
  • DEFINITION_FILE: Es la ruta de acceso al archivo de definición de tabla en tu máquina local.

Si PARTITIONING_MODE es CUSTOM, incluye el esquema de la clave de partición en el prefijo de URI de origen mediante el siguiente formato:

--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Después de crear el archivo de definición de tablas, usa el comando bq mk para crear la tabla externa:

bq mk --external_table_definition=DEFINITION_FILE \
DATASET_NAME.TABLE_NAME \
SCHEMA

Reemplaza lo siguiente:

  • DEFINITION_FILE es la ruta de acceso al archivo de definición de tablas.
  • DATASET_NAME: es el nombre del conjunto de datos que contiene la tabla.
  • TABLE_NAME: es el nombre de la tabla que crearás.
  • SCHEMA: especifica una ruta a un archivo de esquema JSON o especifica el esquema en el formato field:data_type,field:data_type,.... Para usar la detección automática de esquemas, omite este argumento.

Ejemplos

En el siguiente ejemplo, se usa el modo de partición de subárbol AUTO:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=AUTO \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

En el siguiente ejemplo, se usa el modo de partición de subárbol STRING:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=STRING \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

En el siguiente ejemplo, se usa el modo de partición de subárbol CUSTOM:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=CUSTOM \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING} \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

API

Para configurar la partición de subárbol mediante la API de BigQuery, incluye un objeto hivePartitioningOptions en el objeto ExternalDataConfiguration cuando crees el archivo de definición de tablas.

Si configuras el campo hivePartitioningOptions.mode como CUSTOM, debes codificar el esquema de clave de partición en el campo hivePartitioningOptions.sourceUriPrefix de la siguiente manera: gs://BUCKET/PATH_TO_TABLE/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Para implementar el uso de un filtro de predicado en el momento de la consulta, establece el campo hivePartitioningOptions.requirePartitionFilter en true.

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.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.FormatOptions;
import com.google.cloud.bigquery.HivePartitioningOptions;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create external table using hive partitioning
public class SetHivePartitioningOptions {

  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 = "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/*";
    String sourceUriPrefix =
        "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/{pkey:STRING}/";
    setHivePartitioningOptions(datasetName, tableName, sourceUriPrefix, sourceUri);
  }

  public static void setHivePartitioningOptions(
      String datasetName, String tableName, String sourceUriPrefix, String sourceUri) {
    try {
      // 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.getDefaultInstance().getService();

      // Configuring partitioning options
      HivePartitioningOptions hivePartitioningOptions =
          HivePartitioningOptions.newBuilder()
              .setMode("CUSTOM")
              .setRequirePartitionFilter(true)
              .setSourceUriPrefix(sourceUriPrefix)
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      ExternalTableDefinition customTable =
          ExternalTableDefinition.newBuilder(sourceUri, FormatOptions.parquet())
              .setAutodetect(true)
              .setHivePartitioningOptions(hivePartitioningOptions)
              .build();
      bigquery.create(TableInfo.of(tableId, customTable));
      System.out.println("External table created using hivepartitioningoptions");
    } catch (BigQueryException e) {
      System.out.println("External table was not created" + e.toString());
    }
  }
}

Consulta tablas externas

Para obtener más información, visita Consulta datos de Cloud Storage en tablas externas.

Actualiza las tablas externas a BigLake

Puedes actualizar las tablas basadas en Cloud Storage a tablas de BigLake si asocias la tabla externa a una conexión. Si deseas usar el almacenamiento en caché de metadatos con la tabla de BigLake, puedes especificar la configuración para esto al mismo tiempo. Para obtener detalles de la tabla, como el formato de origen y el URI de origen, consulta Obtén información de la tabla.

Para actualizar una tabla externa a una tabla de BigLake, elige una de las siguientes opciones:

SQL

Usa la declaración DDL CREATE OR REPLACE EXTERNAL TABLE para actualizar una tabla:

  1. En la consola de Google Cloud, ve a la página de BigQuery.

    Ir a BigQuery

  2. En el editor de consultas, escribe la siguiente oración:

    CREATE OR REPLACE EXTERNAL TABLE
  `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
  WITH CONNECTION `REGION.CONNECTION_ID`
  OPTIONS(
    format ="TABLE_FORMAT",
    uris = ['BUCKET_PATH'],
    max_staleness = STALENESS_INTERVAL,
    metadata_cache_mode = 'CACHE_MODE'
    );

    Reemplaza lo siguiente:

    • PROJECT_ID: el nombre del proyecto que contiene la tabla
    • DATASET: el nombre del conjunto de datos que contiene la tabla
    • EXTERNAL_TABLE_NAME: el nombre de la tabla
    • REGION: la región que contiene la conexión
    • CONNECTION_ID: el nombre de la conexión que se usará
    • TABLE_FORMAT: el formato que usa la tabla

      No puedes cambiar esto cuando actualizas la tabla.

    • BUCKET_PATH: la ruta al bucket de Cloud Storage que contiene los datos de la tabla externa, en el formato ['gs://bucket_name/[folder_name/]file_name'].

      Puedes elegir varios archivos del bucket si especificas un carácter comodín de asterisco (*) en la ruta de acceso. Por ejemplo, ['gs://mybucket/file_name*'] Si deseas obtener más información, consulta Compatibilidad de comodines para los URI de Cloud Storage.

      Puedes especificar varios buckets para la opción uris si proporcionas varias rutas de acceso.

      En los siguientes ejemplos, se muestran valores uris válidos:

      • ['gs://bucket/path1/myfile.csv']
      • ['gs://bucket/path1/*.csv']
      • ['gs://bucket/path1/*', 'gs://bucket/path2/file00*']

      Cuando especificas valores uris que se orientan a varios archivos, todos esos archivos deben compartir un esquema compatible.

      Para obtener más información sobre cómo usar los URI de Cloud Storage en BigQuery, consulta Ruta de acceso al recurso de Cloud Storage.

    • STALENESS_INTERVAL: especifica si las operaciones en la tabla usan los metadatos almacenados en caché y qué tan recientes deben ser los metadatos almacenados en caché para que la operación los use.

      Si deseas obtener más información sobre las consideraciones de almacenamiento en caché de metadatos, consulta Almacenamiento de metadatos en caché para mejorar el rendimiento.

      Para inhabilitar el almacenamiento en caché de metadatos, especifica 0. Esta es la opción predeterminada.

      Para habilitar el almacenamiento en caché de metadatos, especifica un valor de literal de intervalo entre 30 minutos y 7 días. Por ejemplo, especifica INTERVAL 4 HOUR para un intervalo de inactividad de 4 horas. Con este valor, las operaciones en la tabla usan metadatos almacenados en caché si se actualizaron en las últimas 4 horas. Si los metadatos almacenados en caché son más antiguos, la operación recupera metadatos de Cloud Storage.

    • CACHE_MODE: especifica si la caché de metadatos se actualiza de forma automática o manual

      Si deseas obtener más información sobre las consideraciones de almacenamiento en caché de metadatos, consulta Almacenamiento de metadatos en caché para mejorar el rendimiento.

      Configúralo como AUTOMATIC para que la caché de metadatos se actualice a un intervalo definido por el sistema, por lo general, entre 30 y 60 minutos.

      Configúralo como MANUAL si deseas actualizar la caché de metadatos en un programa que determines. En este caso, puedes llamar al procedimiento del sistema BQ.REFRESH_EXTERNAL_METADATA_CACHE para actualizar la caché.

      Debes configurar CACHE_MODE si STALENESS_INTERVAL está configurado en un valor mayor que 0.

  3. Haz clic en Ejecutar.

Si deseas obtener información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

bq

Usa los comandos bq mkdef y bq update para actualizar una tabla:

  1. Genera una definición de tabla externa, que describa los aspectos de la tabla que se debe cambiar:

    bq mkdef --connection_id=PROJECT_ID.REGION.CONNECTION_ID \
--source_format=TABLE_FORMAT \
--metadata_cache_mode=CACHE_MODE \
"BUCKET_PATH" > /tmp/DEFINITION_FILE

    Reemplaza lo siguiente:

    • PROJECT_ID: el nombre del proyecto que contiene la conexión
    • REGION: la región que contiene la conexión
    • CONNECTION_ID: el nombre de la conexión que se usará
    • TABLE_FORMAT: el formato que usa la tabla No puedes cambiar esto cuando actualizas la tabla.

    • CACHE_MODE: especifica si la caché de metadatos se actualiza de forma automática o manual. Si deseas obtener más información sobre las consideraciones de almacenamiento en caché de metadatos, consulta Almacenamiento de metadatos en caché para mejorar el rendimiento.

      Configúralo como AUTOMATIC para que la caché de metadatos se actualice a un intervalo definido por el sistema, por lo general, entre 30 y 60 minutos.

      Configúralo como MANUAL si deseas actualizar la caché de metadatos en un programa que determines. En este caso, puedes llamar al procedimiento del sistema BQ.REFRESH_EXTERNAL_METADATA_CACHE para actualizar la caché.

      Debes configurar CACHE_MODE si STALENESS_INTERVAL está configurado en un valor mayor que 0.

    • BUCKET_PATH: la ruta al bucket de Cloud Storage que contiene los datos de la tabla externa, en el formato gs://bucket_name/[folder_name/]file_name.

      Puedes limitar los archivos elegidos del bucket si especificas un carácter comodín de asterisco (*) en la ruta de acceso. Por ejemplo, gs://mybucket/file_name* Si deseas obtener más información, consulta Compatibilidad de comodines para los URI de Cloud Storage.

      Puedes especificar varios buckets para la opción uris si proporcionas varias rutas de acceso.

      En los siguientes ejemplos, se muestran valores uris válidos:

      • gs://bucket/path1/myfile.csv
      • gs://bucket/path1/*.csv
      • gs://bucket/path1/*,gs://bucket/path2/file00*

      Cuando especificas valores uris que se orientan a varios archivos, todos esos archivos deben compartir un esquema compatible.

      Para obtener más información sobre cómo usar los URI de Cloud Storage en BigQuery, consulta Ruta de acceso al recurso de Cloud Storage.

    • DEFINITION_FILE: el nombre del archivo de definición de tablas que crearás.

  2. Actualiza la tabla con la definición de tabla externa nueva:

    bq update --max_staleness=STALENESS_INTERVAL \
--external_table_definition=/tmp/DEFINITION_FILE \
PROJECT_ID:DATASET.EXTERNAL_TABLE_NAME

    Reemplaza lo siguiente:

    • STALENESS_INTERVAL: especifica si las operaciones en la tabla usan los metadatos almacenados en caché y qué tan recientes deben ser los metadatos almacenados en caché para que la operación los use. Si deseas obtener más información sobre las consideraciones de almacenamiento en caché de metadatos, consulta Almacenamiento de metadatos en caché para mejorar el rendimiento.

      Para inhabilitar el almacenamiento en caché de metadatos, especifica 0. Esta es la opción predeterminada.

      Para habilitar el almacenamiento en caché de metadatos, especifica un valor de intervalo entre 30 minutos y 7 días, mediante el siguiente comando:Y-M D H:M:S formato que se describe en elINTERVAL tipo de datos. Por ejemplo, especifica 0-0 0 4:0:0 para un intervalo de inactividad de 4 horas. Con este valor, las operaciones en la tabla usan metadatos almacenados en caché si se actualizaron en las últimas 4 horas. Si los metadatos almacenados en caché son más antiguos, la operación recupera metadatos de Cloud Storage.

    • DEFINITION_FILE: el nombre del archivo de definición de tablas que creaste o actualizaste.

    • PROJECT_ID: el nombre del proyecto que contiene la tabla

    • DATASET: el nombre del conjunto de datos que contiene la tabla

    • EXTERNAL_TABLE_NAME: El nombre de la tabla.

Ruta de acceso a recursos de Cloud Storage

Cuando creas una tabla externa basada en una fuente de datos de Cloud Storage, debes proporcionar la ruta de acceso a los datos.

La ruta de acceso del recurso de Cloud Storage contiene el nombre del bucket y tu objeto (nombre del archivo). Por ejemplo, si el bucket de Cloud Storage se llama mybucket y el archivo de datos se llama myfile.csv, la ruta a la fuente será gs://mybucket/myfile.csv.

BigQuery no admite rutas de recursos de Cloud Storage que incluyen varias barras consecutivas después de la doble barra inicial. Los nombres de los objetos de Cloud Storage pueden contener varios caracteres de barras consecutivas (“/”). Sin embargo, BigQuery convierte varias barras consecutivas en una sola barra. Por ejemplo, la ruta de acceso al recurso siguiente, aunque es válida en Cloud Storage, no funciona en BigQuery: gs://bucket/my//object//name.

Para recuperar el URI del recurso de Cloud Storage, sigue estos pasos:

  1. Abre Cloud Storage Console.

    Consola de Cloud Storage

  2. Explora la ubicación del objeto (archivo) que contiene los datos de origen.

  3. Haz clic en el nombre del objeto.

    Se abrirá la página Detalles del objeto.

  4. Copia el valor proporcionado en el campo URI de gsutil, que inicia con gs://.

Admisión de comodines para el URI de Cloud Storage

Si los datos se separan en varios archivos, puedes usar un asterisco (*) para elegir varios archivos. El uso del comodín de asterisco debe seguir estas reglas:

  • El asterisco puede aparecer dentro del nombre del objeto o al final de este.
  • No se admite el uso de varios asteriscos. Por ejemplo, la ruta gs://mybucket/fed-*/temp/*.csv no es válida.
  • No se admite el uso de un asterisco con el nombre del bucket.

Ejemplos:

  • En el ejemplo siguiente, se muestra cómo elegir todos los archivos en todas las carpetas que inician con el prefijo gs://mybucket/fed-samples/fed-sample:

    gs://mybucket/fed-samples/fed-sample*

  • En el siguiente ejemplo, se muestra cómo elegir solo los archivos con una extensión .csv en la carpeta llamada fed-samples y cualquier subcarpeta de fed-samples:

    gs://mybucket/fed-samples/*.csv

  • En el siguiente ejemplo, se muestra cómo elegir archivos con un patrón de nombres de fed-sample*.csv en la carpeta llamada fed-samples. En este ejemplo, no se eligen archivos en subcarpetas de fed-samples.

    gs://mybucket/fed-samples/fed-sample*.csv

Cuando usas la herramienta de línea de comandos de bq, es posible que debas descartar el asterisco en algunas plataformas.

No puedes usar un comodín de asterisco cuando creas tablas externas vinculadas a exportaciones de Datastore o Firestore.

Limitaciones

Para obtener información sobre las limitaciones que se aplican a las tablas externas, consulta Limitaciones de las tablas externas.

¿Qué sigue?