Consulta datos particionados de forma externa

BigQuery admite consultas de datos particionados de forma externa en formatos Avro, Parquet, ORC, JSON y CSV que se almacenan en Cloud Storage con un diseño de partición de subárbol predeterminado. El soporte para la partición de subárbol se habilita mediante la configuración de las opciones apropiadas en el archivo de definición de tablas. Puedes crear y modificar tablas con la consola, la herramienta de línea de comandos de bq y la API de BigQuery.

Si deseas obtener instrucciones para consultar tablas particionadas administradas, consulta Introducción a las tablas particionadas.

Limitaciones

  • La compatibilidad para la partición de subárbol se compila bajo la suposición de que existe un prefijo de URI de origen común para todos los URI que finalizan justo antes de la codificación de partición, de la siguiente manera: gs://BUCKET/PATH_TO_TABLE/.
  • Se supone que la estructura de directorios de una tabla particionada de subárbol tiene las mismas claves de partición en el mismo orden, con un máximo de diez claves de partición por tabla.
  • Los datos deben tener un diseño predeterminado de partición de subárbol.
  • Las claves de partición de subárbol y las columnas de los archivos subyacentes no pueden superponerse.
  • Se aplican todas las limitaciones para consultar fuentes de datos externas almacenadas en Cloud Storage.
  • Solo se admite SQL estándar.

Diseños de datos admitidos

Los datos deben seguir un diseño particionado de subárbol predeterminado. Por ejemplo, los siguientes archivos siguen el diseño predeterminado, con los pares clave-valor establecidos como directorios con un signo = como separador y las claves de partición siempre están en el mismo orden:

gs://myBucket/myTable/dt=2019-10-31/lang=en/foo
gs://myBucket/myTable/dt=2018-10-31/lang=fr/bar

El prefijo de URI de origen común en este ejemplo es gs://myBucket/myTable.

Diseños de datos no admitidos

Si los nombres de las claves de partición no están codificados en la ruta de acceso del directorio, la detección del esquema de partición fallará. Por ejemplo, considera la siguiente ruta de acceso, en la que los nombres de las claves de partición no están codificados:

gs://myBucket/myTable/2019-10-31/en/foo

Los archivos en los que el esquema no está en un orden coherente también generan fallas en la detección. Por ejemplo, considera los siguientes dos archivos que tienen codificaciones de claves de partición invertidas:

gs://myBucket/myTable/dt=2019-10-31/lang=en/foo
gs://myBucket/myTable/lang=fr/dt=2018-10-31/bar

Para estos archivos, la detección del esquema de partición fallará.

Modos de detección de esquema de partición

Las claves de partición en colmena aparecen como columnas normales cuando se cargan datos desde Cloud Storage. BigQuery admite tres modos de detección de esquemas de partición de subárbol:

  • AUTO: Los nombres y tipos de claves se detectan de forma automática. Se pueden detectar los siguientes tipos: STRING, INTEGER, DATE y TIMESTAMP.
  • STRINGS: Los nombres de las claves se convierten de forma automática al tipo STRING.
  • CUSTOM: El esquema de clave de partición está codificado como se especifica en el prefijo de URI de origen.

Proporciona un esquema de clave de partición personalizado

Para usar un esquema CUSTOM, debes especificar el esquema en el campo del prefijo de URI de origen. Usar un esquema CUSTOM te permite especificar el tipo de cada clave de partición. Los valores deben analizarse de forma válida como el tipo especificado o la consulta fallará.

Por ejemplo, si estableces la marca source_uri_prefix en gs://myBucket/myTable/{dt:DATE}/{val:STRING}, BigQuery trata a val como una STRING, dt como DATE y usa gs://myBucket/myTable como el prefijo del URI de origen para los archivos coincidentes.

Reducción de particiones

BigQuery reduce las particiones siempre que sea posible mediante predicados de consulta en las claves de partición. Esto permite que BigQuery evite la lectura de archivos innecesarios, lo que mejora el rendimiento.

Filtros de predicado obligatorios en claves de partición de consultas

Cuando creas una tabla particionada de forma externa, puedes hacer obligatorio el uso de filtros de predicado en claves de partición si habilitas la opción requirePartitionFilter en HivePartitioningOptions.

Cuando se habilita esta opción, los intentos de buscar en la tabla particionada de forma externa sin especificar una cláusula WHERE producen el siguiente error: Cannot query over table <table_name> without a filter over column(s) <partition key names> that can be used for partition elimination.

Crea una tabla externa para los datos con particiones de subárbol

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

Console

  1. En la consola, 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, luego, en Abrir.
  4. En el panel de detalles, haz clic en Crear tabla (Create table).
  5. En la página Crear tabla, especifica los siguientes detalles:
    1. En la sección Fuente, para Crear tabla desde, selecciona Google Cloud Storage. A continuación, sigue estos pasos:
      1. En Seleccionar archivo del bucket de Cloud Storage, ingresa 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.
      2. En la lista Formato de archivo, selecciona el tipo de archivo.
      3. Selecciona la casilla de verificación Partición de datos de origen y, luego, para Seleccionar prefijo de URI de origen, ingresa el prefijo de URI de Cloud Storage. Por ejemplo, gs://my_bucket/my_files.
      4. En la sección Modo de inferencia de la partición, selecciona una de las siguientes opciones:
        • Inferir tipos automáticamente: 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 ingresa 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.
      5. Opcional: A fin de solicitar un filtro de partición en todas las consultas de esta tabla, selecciona 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.
    2. En la sección Destino, especifica los siguientes detalles:
      1. En Nombre del conjunto de datos, selecciona el conjunto de datos en el que deseas crear la tabla.
      2. En el campo Tabla, ingresa el nombre de la tabla que deseas crear.
      3. Verifica que el campo Tipo de tabla esté configurado como Tabla externa.
    3. En la sección Esquema, ingresa la definición del esquema.
    4. Para habilitar la detección automática de esquemas, selecciona Detección automática.
    5. Haga clic en Crear tabla.

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

Aquí:

  • SOURCE_FORMAT es el formato de la fuente de datos externa; por ejemplo, CSV.
  • PARTITIONING_MODE es 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 automáticamente 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 de 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

Aquí:

  • 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 creas.
  • 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.

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

Actualiza tablas particionadas de forma externa

Las claves de partición no deben ser variantes cuando se actualizan tablas externas permanentes.