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 Cloud Console, 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 seguir un diseño de partición de subárbol predeterminado.
  • Las claves de partición de subárbol y las columnas de los archivos subyacentes no se pueden superponer.
  • 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: los pares clave-valor se configuran como directorios con un signo = como separador y las claves de partición siempre 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 falla. 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 falla.

Modos de detección de esquema de partición

Las claves de partición de subárbol se muestran como columnas normales cuando se consultan datos de Cloud Storage. BigQuery admite tres modos de detección de esquemas de partición de subárbol:

  • AUTO: Los nombres y tipos de clave 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 automáticamente 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 para cada clave de partición. Los valores deben analizarse de forma válida como el tipo especificado o la búsqueda 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 ayuda a mejorar 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 partición de subárbol

Puedes crear una tabla externa para datos particionados de subárbol de las siguientes maneras:

  • Usa Cloud Console.
  • Usa la herramienta de línea de comandos de bq.
  • Usa bibliotecas cliente.

Console

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

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

  3. Haz clic en Crear tabla para crear una tabla nueva.

  4. En la sección Fuente de la página Crear tabla, haz lo siguiente:

    1. En la lista desplegable Crear tabla desde, selecciona Cloud Storage.
    2. En el campo Seleccionar archivo del bucket de Cloud Storage (Select file from Cloud Storage bucket), ingresa la ruta de acceso a la carpeta de Cloud Storage con el formato comodín. Por ejemplo, my_bucket/my_files*.
    3. En la lista desplegable Formato de archivo, selecciona el tipo de archivo.
    4. Selecciona la casilla de verificación Partición de datos de origen.
    5. En el campo Seleccionar prefijo de URI de origen, ingresa el prefijo de URI de Cloud Storage. Por ejemplo, gs://my_bucket/my_files
    6. Selecciona un Modo de inferencia de la partición. Si seleccionas Proporcionar mi propia, ingresa la información del esquema para las claves de partición.
    7. Opcional: Para exigir que todas las consultas en esta tabla deben usar un filtro de predicado, marca Requerir filtro de partición. Para obtener más información, consulta Requerir filtros de predicado en claves de partición en las consultas.
  5. En la sección Destino (Destination) de la página Crear tabla, haz lo siguiente:

    1. Selecciona el nombre del proyecto y el nombre del conjunto de datos.
    2. En la lista desplegable Tipo de tabla, selecciona Tabla externa.
    3. En el campo Nombre de tabla, ingresa un nombre para la tabla externa.
  6. En la página Crear tabla, en la sección Esquema, ingresa la información del esquema. BigQuery admite la detección automática de esquemas para algunos formatos. Para obtener más información, consulta Usa la detección automática de esquemas.

  7. Haz clic en Crear tabla.

bq

  • Para configurar la detección automática de clave de partición, establece la marca --hive_partitioning_mode en AUTO. Por ejemplo:
bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
GCS_URIS > TABLE_DEF_FILE
  • Para configurar la detección de claves de partición de tipo string, configura la marca --hive_partitioning_mode como STRINGS. Por ejemplo:
bq mkdef --source_format=PARQUET --hive_partitioning_mode=STRINGS \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
GCS_URIS > TABLE_DEF_FILE
  • Para especificar un esquema de clave de partición personalizado con la marca --source_uri_prefix, establece la marca --hive_partitioning_mode en CUSTOM. Por ejemplo:
bq mkdef --source_format=NEWLINE_DELIMITED_JSON --hive_partitioning_mode=CUSTOM \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX/CUSTOM_SCHEMA_ENCODING \
GCS_URIS FILE_SCHEMA > TABLE_DEF_FILE

Para la marca --hive_partitioning_source_uri_prefix, especifica la parte <var>CUSOM_SCHEMA_ENCODING</var> del argumento inmediatamente posterior a la parte <var>GCS_URI_SHARED_PREFIX</var>, de la siguiente manera:

GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Por ejemplo:

--hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING}

  • Para requerir el uso de un filtro de predicado en el momento de la consulta, agrega --require_hive_partition_filter=True al comando bq mkdef. Por ejemplo:
bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
--require_hive_partition_filter=True \
GCS_URIS > TABLE_DEF_FILE

API

Para configurar la partición de subárbol con 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 forzar 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 que se encuentran 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());
    }
  }
}