Crear tablas externas de Cloud Storage

BigQuery permite consultar datos de Cloud Storage en los siguientes formatos:

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

BigQuery permite consultar datos de Cloud Storage de estas clases de almacenamiento:

  • Estándar
  • Nearline
  • Coldline
  • Archivar

Para consultar una tabla externa de Cloud Storage, debe tener permisos tanto en la tabla externa como en los archivos de Cloud Storage. Te recomendamos que uses 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 tu segmento de Cloud Storage cuando consultes datos almacenados en Cloud Storage.

Antes de empezar

Concede roles de gestión de identidades y accesos (IAM) que proporcionen a los usuarios los permisos necesarios para realizar cada tarea de este documento. Los permisos necesarios para realizar una tarea (si los hay) se indican en la sección "Permisos necesarios" de la tarea.

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)

También necesitas los siguientes permisos para acceder al segmento 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 Gestión de Identidades y Accesos de administrador de almacenamiento de Cloud Storage (roles/storage.admin) incluye estos permisos.

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.

Ámbitos de acceso de las instancias de Compute Engine

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

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

Si configuras una instancia de Compute Engine para que se ejecute como la cuenta de servicio predeterminada de Compute Engine, se le concederán de forma predeterminada varios ámbitos predeterminados, incluido el ámbito https://www.googleapis.com/auth/devstorage.read_only.

Si, por el contrario, configuras la instancia con una cuenta de servicio personalizada, asegúrate de conceder explícitamente el https://www.googleapis.com/auth/devstorage.read_only 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.

Crear tablas externas en datos sin particiones

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

Selecciona una de las opciones siguientes:

Consola

  1. Ve a la página BigQuery.

    Ir a BigQuery

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

  3. Abre la opción Acciones y haz clic en Crear tabla.

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

    1. En Crear tabla a partir de, selecciona Google Cloud Storage.

    2. En Selecciona un archivo del segmento de Google Cloud Storage o usa un patrón de URI, busca el segmento y el archivo que quieras usar o escribe la ruta con el formato gs://bucket_name/[folder_name/]file_name.

      No puedes especificar varias URIs en la Google Cloud consola, pero puedes seleccionar varios archivos especificando un carácter comodín de asterisco (*). Por ejemplo, gs://mybucket/file_name*. Para obtener más información, consulta Compatibilidad con comodines en URIs de Cloud Storage.

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

    3. En Formato de archivo, seleccione el formato que coincida con el de su archivo.

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

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

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

    3. En Tabla, introduce el nombre de la tabla que vas a crear.

    4. En Tipo de tabla, selecciona Tabla externa.

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

    • Para habilitar la detección automática de esquemas, selecciona la opción Detectar automáticamente.

    • Para especificar un esquema manualmente, desmarca la opción Detectar automáticamente. Habilita Editar como texto e introduce el esquema de la tabla como una matriz JSON.

  7. Para ignorar las filas con otros valores de columna que no coincidan con el esquema, despliega la sección Opciones avanzadas y selecciona Valores desconocidos.

  8. Haz clic en Crear tabla.

Una vez creada la tabla permanente, puedes ejecutar una consulta en ella como si fuera una tabla nativa de BigQuery. Una vez que se haya completado la consulta, puedes exportar los resultados como archivos CSV o JSON, guardarlos como una tabla o guardarlos en Hojas de cálculo de Google.

SQL

Puedes crear una tabla externa permanente ejecutando la CREATE EXTERNAL TABLE instrucción DDL. 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 Google Cloud consola, ve a la página BigQuery.

    Ir a BigQuery

  2. En el editor de consultas, introduce la siguiente instrucción:

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

    Haz los cambios siguientes:

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

      Puede seleccionar varios archivos del segmento especificando un carácter comodín de asterisco (*) en la ruta. Por ejemplo, ['gs://mybucket/file_name*']. Para obtener más información, consulta Compatibilidad con comodines para URIs de Cloud Storage.

      Puedes especificar varios segmentos para la opción uris proporcionando varias rutas.

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

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

      Si especifica valores de uris que se dirijan a varios archivos, todos esos archivos deben compartir un esquema compatible.

      Para obtener más información sobre cómo usar URIs de Cloud Storage en BigQuery, consulta Ruta de recursos de Cloud Storage.

  3. Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, consulta Ejecutar 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 forma explícita y se omite la primera fila del 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 a un archivo de definición de tabla o una definición de tabla insertada.

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

Usa el comando bq mkdef para crear un archivo de definición de tabla y, a continuación, pasa la ruta 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

Haz los cambios siguientes:

  • SOURCE_FORMAT: el formato de la fuente de datos externa. Por ejemplo, CSV.

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

    Puede seleccionar varios archivos del segmento especificando un asterisco (*) en el carácter comodín file_pattern. Por ejemplo, gs://mybucket/file00*.parquet. Para obtener más información, consulta Compatibilidad con comodines en URIs de Cloud Storage.

    Puedes especificar varios segmentos para la opción uris proporcionando varias rutas.

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

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

    Si especifica valores de uris que se dirijan a varios archivos, todos esos archivos deben compartir un esquema compatible.

    Para obtener más información sobre cómo usar URIs de Cloud Storage en BigQuery, consulta Ruta de recursos de Cloud Storage.

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

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

  • TABLE_NAME: el nombre de la tabla que vas a crear.

  • SCHEMA: especifica una ruta a un archivo de esquema JSON o 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, define 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 insertada

En lugar de crear un archivo de definición de tabla, 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

Haz los cambios siguientes:

  • SOURCE_FORMAT: el formato de la fuente de datos externa

    Por ejemplo, CSV.

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

    Puede seleccionar varios archivos del segmento especificando un asterisco (*) en el carácter comodín file_pattern. Por ejemplo, gs://mybucket/file00*.parquet. Para obtener más información, consulta Compatibilidad con comodines en URIs de Cloud Storage.

    Puedes especificar varios segmentos para la opción uris proporcionando varias rutas.

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

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

    Si especifica valores de uris que se dirijan a varios archivos, todos esos archivos deben compartir un esquema compatible.

    Para obtener más información sobre cómo usar URIs de Cloud Storage en BigQuery, consulta Ruta de recursos de Cloud Storage.

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

  • TABLE_NAME: el nombre de la tabla que vas a crear.

  • SCHEMA: especifica una ruta a un archivo de esquema JSON o 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 le envíes.

Especifique la propiedad schema o asigne el valor true a la propiedad autodetect para habilitar la detección automática del esquema en las fuentes de datos compatibles.

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.

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. 

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 Node.jsinstrucciones 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 Node.js 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. 

// 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 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

# 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}"
)

Crear tablas externas en datos con particiones

Puedes crear una tabla externa para los datos con particiones de Hive que se encuentren en Cloud Storage. Después de crear una tabla particionada externamente, no puedes cambiar la clave de partición. Para cambiar la clave de partición, debes volver a crear la tabla.

Para crear una tabla externa de datos con particiones de Hive, elige una de las siguientes opciones:

Consola

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

    Ir a BigQuery

  2. En el panel Explorador, expande tu proyecto y selecciona un conjunto de datos.
  3. Haz clic en Ver acciones y, a continuación, en Crear tabla. Se abrirá el panel Crear tabla.
  4. En la sección Fuente, especifica los siguientes detalles:
    1. En Crear tabla a partir de, selecciona Google Cloud Storage.
    2. En Seleccionar archivo del segmento de Cloud Storage, introduce la ruta de la carpeta de Cloud Storage con comodines. Por ejemplo, my_bucket/my_files*. El segmento de Cloud Storage debe estar en la misma ubicación que el conjunto de datos que contiene la tabla que quieres crear, añadir o sobrescribir.
    3. En la lista Formato de archivo, selecciona el tipo de archivo.
    4. Marca la casilla Partición de datos de origen y, en Seleccionar prefijo del URI de origen, introduce el prefijo del URI de Cloud Storage. Por ejemplo, gs://my_bucket/my_files.
    5. En la sección Modo de inferencia de particiones, seleccione una de las siguientes opciones:
      • Tipos de inferencias automáticas: define el modo de detección del esquema de partición como AUTO.
      • Todas las columnas son cadenas: define el modo de detección del esquema de partición como STRINGS.
      • Proporcionar mi propio esquema: define el modo de detección del esquema de partición como CUSTOM e introduce manualmente la información del esquema de las claves de partición. Para obtener más información, consulta el artículo Proporcionar un esquema de clave de partición personalizado.
    6. Opcional: Para requerir un filtro de partición en todas las consultas de esta tabla, seleccione la casilla Requerir filtro de partición. Si usas este filtro, es posible que rebajes los costes y mejores el rendimiento. Para obtener más información, consulta Requerir filtros de predicado en claves de partición en consultas.
  5. En la sección Destino, especifica los siguientes detalles:
    1. En Proyecto, selecciona el proyecto en el que quieras crear la tabla.
    2. En Conjunto de datos, selecciona el conjunto de datos en el que quieras crear la tabla.
    3. En Tabla, introduce el nombre de la tabla que quieras crear.
    4. En Tipo de tabla, selecciona Tabla externa.
  6. En la sección Schema (Esquema), introduce la definición de schema.
  7. Para habilitar la detección automática del esquema, selecciona Detectar automáticamente.
  8. Para ignorar las filas con otros valores de columna que no coincidan con el esquema, despliega la sección Opciones avanzadas y selecciona Valores desconocidos.
  9. Haz clic en Crear tabla.

SQL

Usa la CREATE EXTERNAL TABLE instrucción DDL.

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

Haz los cambios siguientes:

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

En el siguiente ejemplo se usan claves y tipos de partición de Hive personalizados. Para ello, se enumeran 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);

Haz los cambios siguientes:

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

En el siguiente ejemplo se crea una tabla con particiones externas. Utiliza la detección automática de esquemas para detectar tanto el esquema del archivo como el diseño de partición de Hive. 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 crea una tabla con particiones externas especificando explícitamente las columnas de partición. En este ejemplo se presupone que la ruta del archivo externo sigue 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 tabla:

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

Haz los cambios siguientes:

  • SOURCE_FORMAT: el formato de la fuente de datos externa. Por ejemplo, CSV.
  • PARTITIONING_MODE: el modo de particiones de Hive. Utilice uno de los siguientes valores:
    • AUTO: detecta automáticamente los nombres y los tipos de las claves.
    • STRINGS: convierte automáticamente los nombres de las claves en cadenas.
    • CUSTOM: codifica el esquema de clave en el prefijo del URI de origen.
  • GCS_URI_SHARED_PREFIX: el prefijo del URI de origen.
  • BOOLEAN: especifica si se debe requerir un filtro de predicado en el momento de la consulta. Esta marca es opcional. El valor predeterminado es false.
  • GCS_URIS: la ruta a la carpeta de Cloud Storage, con formato de comodín.
  • DEFINITION_FILE: la ruta al archivo de definición de tabla en tu máquina local.

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

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

Una vez que hayas creado el archivo de definición de tabla, usa el comando bq mk para crear la tabla externa:

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

Haz los cambios siguientes:

  • DEFINITION_FILE: la ruta al archivo de definición de la tabla.
  • DATASET_NAME: el nombre del conjunto de datos que contiene la tabla.
  • TABLE_NAME: el nombre de la tabla que vas a crear.
  • SCHEMA: especifica una ruta a un archivo de esquema JSON o 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 particiones de Hive 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 particiones de Hive 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 particiones de Hive 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 definir las particiones de Hive mediante la API de BigQuery, incluye un objeto hivePartitioningOptions en el objeto ExternalDataConfiguration al crear el archivo de definición de tabla.

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

Para forzar el uso de un filtro de predicado en el momento de la consulta, asigna el valor true al campo hivePartitioningOptions.requirePartitionFilter.

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.

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. 

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

Consultar tablas externas

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

Actualizar tablas externas a BigLake

Puedes actualizar las tablas basadas en Cloud Storage a tablas de BigLake asociando la tabla externa a una conexión. Si quieres usar el almacenamiento de metadatos en caché con la tabla de BigLake, puedes especificar los ajustes correspondientes al mismo tiempo. Para obtener detalles de la tabla, como el formato de origen y el URI de origen, consulta Obtener información de la tabla.

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

SQL

Usa la CREATE OR REPLACE EXTERNAL TABLEdeclaración de DDL para actualizar una tabla:

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

    Ir a BigQuery

  2. En el editor de consultas, introduce la siguiente instrucción:

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

    Haz los cambios siguientes:

    • 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 va a usar

      Para usar una conexión predeterminada, especifica DEFAULT en lugar de la cadena de conexión que contiene REGION.CONNECTION_ID.

    • TABLE_FORMAT: el formato que usa la tabla

      No puedes cambiar este ajuste al actualizar la tabla.

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

      Puede seleccionar varios archivos del segmento especificando un carácter comodín de asterisco (*) en la ruta. Por ejemplo, ['gs://mybucket/file_name*']. Para obtener más información, consulta Compatibilidad con comodines para URIs de Cloud Storage.

      Puedes especificar varios segmentos para la opción uris proporcionando varias rutas.

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

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

      Si especifica valores de uris que se dirijan a varios archivos, todos esos archivos deben compartir un esquema compatible.

      Para obtener más información sobre cómo usar URIs de Cloud Storage en BigQuery, consulta Ruta de recursos de Cloud Storage.

    • STALENESS_INTERVAL: especifica si las operaciones de la tabla usan metadatos almacenados en caché y cuánto tiempo deben haber pasado desde que se almacenaron en caché para que la operación los use.

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

      Para inhabilitar el almacenamiento en caché de metadatos, especifica 0. Este es el valor predeterminado.

      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 obsolescencia de 4 horas. Con este valor, las operaciones en la tabla usan metadatos almacenados en caché si se han actualizado en las últimas 4 horas. Si los metadatos almacenados en caché son anteriores a esa fecha, la operación recupera los metadatos de Cloud Storage.

    • CACHE_MODE: especifica si la caché de metadatos se actualiza automáticamente o manualmente.

      Para obtener más información sobre las consideraciones relativas al almacenamiento en caché de metadatos, consulta el artículo Almacenamiento en caché de metadatos para mejorar el rendimiento.

      Defina el valor AUTOMATIC para que la caché de metadatos se actualice a un intervalo definido por el sistema, que suele ser de entre 30 y 60 minutos.

      Seleccione MANUAL si quiere actualizar la caché de metadatos según la programación que determine. En ese caso, puedes llamar al BQ.REFRESH_EXTERNAL_METADATA_CACHEprocedimiento del sistema para actualizar la caché.

      Debes definir CACHE_MODE si STALENESS_INTERVAL tiene un valor superior a 0.

  3. Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, consulta Ejecutar 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 quieres cambiar:

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

    Haz los cambios siguientes:

    • 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 va a usar
    • TABLE_FORMAT: el formato que usa la tabla. No puedes cambiar este ajuste al actualizar la tabla.

    • CACHE_MODE: especifica si la caché de metadatos se actualiza automáticamente o manualmente. Para obtener más información sobre las consideraciones relativas al almacenamiento en caché de metadatos, consulta Almacenamiento en caché de metadatos para mejorar el rendimiento.

      Defina el valor AUTOMATIC para que la caché de metadatos se actualice a un intervalo definido por el sistema, que suele ser de entre 30 y 60 minutos.

      Introduzca MANUAL si quiere actualizar la caché de metadatos según la programación que determine. En este caso, puedes llamar al procedimiento del sistema BQ.REFRESH_EXTERNAL_METADATA_CACHE para actualizar la caché.

      Debes definir CACHE_MODE si STALENESS_INTERVAL tiene un valor superior a 0.

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

      Puedes limitar los archivos seleccionados del segmento especificando un carácter comodín de asterisco (*) en la ruta. Por ejemplo, gs://mybucket/file_name*. Para obtener más información, consulta Compatibilidad con comodines en URIs de Cloud Storage.

      Puedes especificar varios segmentos para la opción uris proporcionando varias rutas.

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

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

      Si especifica valores de uris que se dirijan a varios archivos, todos esos archivos deben compartir un esquema compatible.

      Para obtener más información sobre cómo usar URIs de Cloud Storage en BigQuery, consulta Ruta de recursos de Cloud Storage.

    • DEFINITION_FILE: el nombre del archivo de definición de tabla que estás creando.

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

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

    Haz los cambios siguientes:

    • STALENESS_INTERVAL: especifica si las operaciones de la tabla usan metadatos almacenados en caché y cómo de actualizados deben estar los metadatos almacenados en caché para que la operación los use. Para obtener más información sobre las consideraciones relativas al almacenamiento en caché de metadatos, consulta Almacenamiento en caché de metadatos para mejorar el rendimiento.

      Para inhabilitar el almacenamiento en caché de metadatos, especifica 0. Este es el valor predeterminado.

      Para habilitar el almacenamiento en caché de metadatos, especifica un valor de intervalo de entre 30 minutos y 7 días con el formato Y-M D H:M:S descrito en la documentación del INTERVAL tipo de datos. Por ejemplo, especifica 0-0 0 4:0:0 para un intervalo de obsolescencia de 4 horas. Con este valor, las operaciones en la tabla usan metadatos almacenados en caché si se han actualizado en las últimas 4 horas. Si los metadatos almacenados en caché son anteriores a ese periodo, la operación recupera los metadatos de Cloud Storage.

    • DEFINITION_FILE: el nombre del archivo de definición de la tabla que has creado o actualizado.

    • 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 recurso de Cloud Storage

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

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

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

Para obtener la ruta del recurso de Cloud Storage, sigue estos pasos:

  1. Abre la consola de Cloud Storage.

    Consola de Cloud Storage

  2. Desplázate hasta 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 empieza por gs://.

Compatibilidad con comodines en URIs de Cloud Storage

Si los datos están separados en varios archivos, puede usar un asterisco (*) como comodín para seleccionar varios archivos. El uso del carácter comodín asterisco debe seguir estas reglas:

  • El asterisco puede aparecer dentro del nombre del objeto o al final del nombre del objeto.
  • 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 segmento.

Ejemplos:

  • En el siguiente ejemplo se muestra cómo seleccionar todos los archivos de todas las carpetas que empiezan por el prefijo gs://mybucket/fed-samples/fed-sample:

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

  • En el siguiente ejemplo se muestra cómo seleccionar solo los archivos con la extensión .csv de la carpeta fed-samples y de sus subcarpetas:fed-samples

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

  • En el siguiente ejemplo se muestra cómo seleccionar archivos con un patrón de nomenclatura de fed-sample*.csv en la carpeta llamada fed-samples. En este ejemplo no se seleccionan archivos de las subcarpetas de fed-samples.

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

Cuando uses la herramienta de línea de comandos bq, es posible que tengas que usar la barra inversa para el asterisco en algunas plataformas.

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

Precios

Las solicitudes de BigQuery están sujetas a las siguientes tarifas de recuperación y transferencia de datos de Cloud Storage:

Limitaciones

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

Siguientes pasos