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

Puedes crear una tabla externa para datos con partición 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. Expande la opción Acciones y haz clic en Abrir.

  4. En el panel de detalles, haz clic en Crear tabla  (Create table).

  5. 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 Selecciona el 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 Proporcionarlos de forma manual, luego, ingresa la información del esquema de las claves de partición.
    7. Opcional: Para requerir que todas las consultas de esta tabla usen un filtro de predicado, marca Exigir filtro de partición. Para obtener más información, consulta Filtros de predicado obligatorios en claves de partición de consultas.
  6. 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.
  7. 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.

  8. Haz clic en Crear tabla.

bq

  • Para configurar la detección automática de claves 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, configura la marca --hive_partitioning_mode como 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 que sigue inmediatamente a la parte <var>GCS_URI_SHARED_PREFIX</var>, como se muestra a continuación:

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