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. Se pueden usar la creación y modificación de tablas con Cloud Console, la herramienta de línea de comandos de bq y la API de REST.

A fin de 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, que se parece a 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.
  • En este momento, la creación y modificación de tablas se limita a Cloud Console, la herramienta de línea de comandos de bq y la API de REST.
  • 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 en el mismo orden.

gs://bucket/table/dt=2019-10-31/lang=en/foo
gs://bucket/table/dt=2018-10-31/lang=fr/bar

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

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://bucket/table/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://bucket/table/dt=2019-10-31/lang=en/foo
gs://bucket/table/lang=fr/dt=2018-10-31/bar

Modos de detección de esquemas de partición

Las claves de partición de subárbol se muestran como columnas normales cuando se consultan datos de Cloud Storage. Se admiten 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 infieren de forma automática según el tipo de STRING.
  • CUSTOM: El esquema de clave de partición está codificado en el prefijo de URI de origen.

Proporciona un esquema de clave de partición personalizado

El modo de detección CUSTOM requiere que se codifique el esquema en el campo del prefijo de URI de origen. Usar el modo CUSTOM permite que el usuario especifique tipos para cada clave de partición. Los valores deben realizar análisis válidos, ya que, de lo contrario, el tipo especificado o la consulta fallarán.

Por ejemplo, establecer source_uri_prefix en gs://my_bucket/my_table/{dt:DATE}/{val:STRING} trata val como una STRING y dt como una DATE, y extrae gs://my_bucket/my_table como el prefijo de URI de origen para los archivos coincidentes.

Consulta las instrucciones detalladas para Cloud Console, la herramienta de línea de comandos de bq y la API de REST.

Reducción de particiones

BigQuery reduce las particiones siempre que sea posible mediante predicados de consulta en las claves de partición. De este modo, evita tener que leer archivos innecesarios. Esto 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.

Si deseas obtener más información sobre cómo agregar la opción requirePartitionFilter cuando creas una tabla particionada, consulta las instrucciones para la herramienta de línea de comandos de bq y la API de REST.

Configura HivePartitionOptions con Cloud Console

Para configurar las opciones de partición de subárbol en Cloud Console, sigue los pasos a continuación:

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

    Ir a la página BigQuery

  2. Selecciona el conjunto de datos en el panel Recursos.

  3. Haz clic en el botón Crear tabla para crear una tabla nueva.

  4. Selecciona Cloud Storage en el menú desplegable Crear tabla desde (Create table from).

Opciones de partición de subárbol

  1. En el campo Selecciona un archivo del depósito de GCS, ingresa la ruta de acceso del archivo del conjunto de datos y, luego, selecciona el tipo de archivo del menú desplegable.
  2. Marca la casilla Partición de datos de origen y, luego, ingresa el prefijo de URI de origen en el campo proporcionado.
  3. En Modo de inferencia de la partición, elige “Infiere tipos automáticamente” para AUTO o “Todas las columnas son strings” para STRINGS.
  4. Elige “Tabla externa” en el menú desplegable Tipo de tabla.

Configura HivePartitioningOptions con la herramienta de línea de comandos bq

HivePartitioningOptions se establecen en el objeto ExternalDataConfiguration mientras se crea el archivo de definición de tablas.

Solicita la detección automática del tipo de clave de partición

bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix \
gcs_uris >/tmp/table_def_file

Solicita la detección de claves de partición de tipo string

bq mkdef --source_format=PARQUET --hive_partitioning_mode=STRINGS \
--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix \
gcs_uris >/tmp/table_def_file

Comando para proporcionar un esquema de clave de partición personalizado codificado mediante el campo source_uri_prefix

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

El esquema de clave de partición se codifica de forma inmediata después del prefijo de URI de origen. Usa el siguiente formato para especificar --hive_partitioning_source_uri_prefix:

--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix/{key1:TYPE1}/{key2:TYPE2}/{key3:TYPE3}

Filtro de partición obligatorio

Para aplicar el uso de un filtro de predicado en el momento de la búsqueda, agrega --require_hive_partition_filter=True a los comandos bq mkdef anteriores. En este ejemplo, se usa la detección del tipo de clave de partición AUTO:

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

Configura HivePartitionOptions con la API de REST

La compatibilidad con la partición de subárbol existe si se establece HivePartitioningOptions en ExternalDataConfiguration cuando se crea un archivo de definición de tablas.

Ten en cuenta que cuando hivePartitioningOptions.mode se configura como CUSTOM, debes codificar el esquema de clave de partición dentro de sourceUriPrefix. Para hacerlo, configura hivePartitioningOptions.sourceUriPrefix como gs://bucket/path_to_table/{key1:TYPE1}/{key2:TYPE2}/{key3:TYPE3}.

Si quieres forzar el uso de un filtro de predicado en el momento de la consulta, establece hivePartitioningOptions.requirePartitionFilter en true.