Introducción a las tablas particionadas

En esta página, se proporciona una descripción general de la compatibilidad de tablas de partición en BigQuery.

Una tabla de partición es una tabla especial que se divide en segmentos, denominados particiones, que facilitan la administración y la consulta de tus datos. Dividir una tabla grande en particiones más pequeñas puede mejorar el rendimiento de la consulta. Además, puedes controlar los costos si reduces la cantidad de bytes que lee una consulta.

Puedes realizar particiones de las tablas de BigQuery de la siguiente manera:

Tabla de partición por tiempo de transferencia

Cuando creas una tabla particionada por tiempo de transferencia, BigQuery carga los datos en particiones diarias basadas en fechas que reflejan la transferencia de datos o la hora de llegada de forma automática. Los identificadores de sufijo y seudocolumna te permiten rectificar (reemplazar) y redireccionar los datos a las particiones de un día específico.

Las tablas particionadas por tiempo de transferencia incluyen una seudocolumna llamada _PARTITIONTIME que, a su vez, contiene una marca de tiempo basada en la fecha para los datos cargados en la tabla. Las búsquedas en tablas particionadas por tiempo pueden restringir la lectura de datos mediante el suministro de filtros _PARTITIONTIME que representan la ubicación de una partición. La búsqueda lee todos los datos de la partición especificada, pero el filtro del predicado _PARTITIONTIME restringe la cantidad de particiones analizadas.

Cuando creas tablas particionadas por tiempo de transferencia, las particiones tienen la misma definición de esquema que la tabla. Si necesitas cargar datos en una partición con un esquema distinto al de la tabla, debes actualizar el esquema de la tabla antes de cargar dichos datos. De forma alternativa, puedes usar las opciones de actualización de esquema para actualizar el esquema de la tabla en un trabajo de carga o de búsqueda.

Tablas particionadas por fecha, marca de tiempo o fecha y hora

BigQuery también permite tablas particionadas basadas en una columna DATE, TIMESTAMP o DATETIME específica. Los datos escritos en una tabla particionada por fecha, marca de tiempo o fecha y hora se entregan automáticamente a la partición adecuada según el valor de unidad de tiempo (expresado en UTC para TIMESTAMP) especificado en la columna de partición.

Si la tabla está particionada en una columna DATE, puedes crear particiones con un nivel de detalle diario, mensual o anual. Cada partición contiene un rango de valores en el que el inicio es el comienzo de un día, mes o año, y el intervalo de ese rango es de un día, mes o año, según el nivel de detalle de partición. Si la tabla está particionada en una columna TIMESTAMP o DATETIME, puedes crear particiones con cualquier tipo de nivel de detalle de unidad de tiempo, incluido HOUR.

Las tablas particionadas por fecha/marca de tiempo/fecha y hora no necesitan una seudocolumna _PARTITIONTIME. Las consultas en tablas particionadas por fecha/marca de tiempo/fecha y hora pueden especificar filtros de predicado según la columna de partición para reducir la cantidad de datos analizados.

Cuando creas tablas particionadas por fecha/marca de tiempo/fecha y hora, se generan dos particiones especiales:

  • La partición __NULL__ representa filas con valores NULL en la columna de partición.
  • La partición __UNPARTITIONED__ representa datos que existen fuera del rango de fechas permitido.

Con la excepción de las particiones __NULL__ y __UNPARTITIONED__, todos los datos en la columna de partición coinciden con la fecha del identificador de la partición. Esto permite que una consulta determine qué particiones no contienen datos que cumplan las condiciones del filtro. Las búsquedas que filtran datos en la columna de partición pueden restringir los valores y reducir las particiones innecesarias por completo.

Partición de fecha, marca de tiempo o fecha en comparación con la fragmentación

Como alternativa a las tablas particionadas por fecha/marca de tiempo/fecha y hora, puedes fragmentar tablas con un enfoque de nomenclatura basado en el tiempo, como [PREFIX]_YYYYMMDD. Esto se conoce como creación de tablas fragmentadas por fecha. Con SQL estándar o SQL heredado, puedes especificar una búsqueda con un operador UNION para limitar las tablas que analiza la búsqueda.

Las tablas particionadas por fecha/marca de tiempo/fecha y hora funcionan mejor que las que se encuentran fragmentadas por fecha. Cuando creas tablas denominadas por fecha, BigQuery debe mantener una copia del esquema y los metadatos para cada tabla llamada por fecha. Además, cuando las tablas denominadas por fecha no se usan, es posible que BigQuery necesite verificar los permisos para cada tabla consultada. Esta práctica también aumenta la sobrecarga de la búsqueda y afecta su rendimiento. La mejor práctica recomendada es usar tablas particionadas por fecha, marca de tiempo o fecha y hora en lugar de tablas fragmentadas por fecha.

Compara opciones de partición

En la siguiente tabla, se comparan tablas fragmentadas y tablas particionadas por fecha/marca de tiempo/fecha y hora.

Función Tablas fragmentadas Tabla de partición por tiempo de transferencia Tablas de partición
Método de partición Ninguno: fragmentar tablas y consultarlas con un operador UNION puede simular una partición. Partición basada en la fecha de llegada o transferencia de los datos. Se puede hacer referencia a la información de partición con una seudocolumna. Partición basada en datos de una columna TIMESTAMP , DATE o DATETIME especificada.

En las tablas particionadas por hora, solo se admiten las columnas TIMESTAMP y DATETIME en la partición.
Identificadores de partición Ninguno Puedes usar cualquier fecha válida entre el 01/01/0001 y el 31/12/9999, pero las declaraciones DML no pueden hacer referencia a las fechas anteriores al 01/01/1970 o posteriores al 31/12/2159. Una entrada válida de la columna DATE, TIMESTAMP o DATETIME vinculada. Por el momento, los valores de fecha anteriores al 01/01/1960 y posteriores al 31/12/2159 se colocan en una partición UNPARTITIONED compartida. Los valores NULL residen en una partición NULL explícita.

Los identificadores de partición deben seguir los siguientes formatos:
  • yyyyMMddHH para la partición por hora.
  • yyyyMMdd para la partición diaria.
  • yyyyMM para la partición mensual.
  • yyyy para la partición anual.
Limitación de los datos analizados Solo se hace referencia a los fragmentos que necesitas y se limitan los datos mediante la exclusión de las columnas innecesarias de la consulta. Usa la seudocolumna _PARTITIONTIME para reducir las particiones. Usa filtros de predicado en la columna de partición.
Cantidad de particiones La cantidad de tablas no está restringida, pero las consultas solo pueden hacer referencia hasta a 1,000 tablas. Hasta 4,000 particiones. Hasta 4,000 particiones.
Operaciones de actualización Tienes un límite de 1,000 actualizaciones por día. Una operación individual puede confirmar una sola partición. La última partición (según la configuración predeterminada), o una especificada mediante un decorador de partición como [TABLE]$[DATE]. Una operación individual puede confirmar datos hasta en 2,000 particiones distintas.
Inserciones de transmisión Puedes transmitir a cualquier tabla de fragmentos, pero debes especificar manualmente el fragmento. Los datos se ubican inicialmente en la partición UNPARTITIONED y, luego, se extraen a la fecha actual de forma predeterminada. Mediante el uso de sufijos de partición, puedes transmitir directamente a las particiones de los últimos 31 días y 16 días siguientes, en relación con la fecha actual, según la hora UTC actual. Puedes transmitir datos entre 5 años en el pasado y 1 año en el futuro. Los datos fuera de este rango se rechazan. Los datos dentro de este rango se colocan, en un principio, en la partición UNPARTITIONED. Cuando hay suficientes datos no particionados, BigQuery vuelve a particionar de forma automática los datos.
Evaluación de zona horaria Definido por la semántica del usuario. UTC UTC

Tablas particionadas por rango de números enteros

BigQuery permite la creación de tablas particionadas basadas en una columna INTEGER específica, con los valores de intervalo, inicio y finalización que prefieras. Las búsquedas relativas a tablas particionadas por rango de números enteros pueden especificar filtros de predicado basados en la columna de partición a fin de reducir la cantidad de datos analizados.

Para crear una tabla particionada por rango de números enteros, debes proporcionar lo siguiente:

  • la columna utilizada para crear las particiones por rango de números enteros
  • el inicio de la partición del rango (inclusivo)
  • el fin de la partición de rango (exclusivo)
  • el intervalo de cada rango dentro de la partición

Los valores que están fuera del rango de la tabla van a la partición SIN PARTICIONAR.

Por ejemplo, si creas una partición por rango de números enteros con los siguientes valores, sigue estos pasos:

Argumento Valor
nombre de la columna customer_id
inicio 0
finalización 100
intervalo 10

Se realizará una partición de la tabla en la columna customer_id en rangos del intervalo 10. Los valores del 0 al 9 estarán en una partición; los valores del 10 al 19 en otra partición; …, y, finalmente, los valores del 90 al 99 estarán en otra partición. Los valores fuera del 0 al 99 (como -1 o 100) estarán en la partición SIN PARTICIONAR. Los valores nulos estarán en la partición NULO.

Cuando creas tablas particionadas por rango de números enteros, se generan dos particiones especiales:

  • La partición __NULL__ representa filas con valores NULL en la columna de partición.
  • La partición __UNPARTITIONED__ representa los datos que existen fuera del rango permitido del inicio y el intervalo de números enteros.

Con la excepción de las particiones __NULL__ y __UNPARTITIONED__, todos los datos en la columna de partición se encuentran dentro del rango del comienzo y el intervalo de números enteros. Esto permite que una consulta determine qué particiones no contienen datos que cumplan las condiciones del filtro. Las búsquedas que filtran datos en la columna de partición pueden restringir los valores y reducir las particiones innecesarias por completo.

El límite para la cantidad de rangos posibles entre los valores inicial y final es 10,000. Sin embargo, la cantidad de rangos con datos se limita a 4,000 particiones por tabla, ya que cada rango es una partición.

Partición por rango de números enteros frente al agrupamiento en clústeres

Tanto la partición de rango por números enteros como el agrupamiento en clústeres pueden mejorar el rendimiento y reducir el costo de la búsqueda. Poseen diferencias clave y casos prácticos.

Si quieres, puedes utilizar la partición por rango de números enteros:

  • Define de forma explícita rangos utilizados para particionar la tabla. Tú especificas cómo se realizará la partición de los datos, así como qué datos hay en cada partición.

  • Averigua el costo de una búsqueda antes de que se ejecute. La reducción de la partición se realiza antes de que se ejecute la búsqueda, por lo que puedes obtener el costo de la búsqueda después de reducir la partición a través de una ejecución de prueba. La reducción del clúster se realiza cuando se ejecuta la búsqueda, por lo que el costo se conocerá una vez que finalice la búsqueda.

  • Aborda una partición, como cuando deseas cargar o borrar datos para una partición específica.

Usa el agrupamiento en clústeres en los siguientes casos:

  • No importa cómo se agruparán los datos, siempre que obtengas la mejora potencial del rendimiento y la reducción de costos. BigQuery determinará automáticamente cómo se agruparán los datos en clústeres para obtener un rendimiento y un costo óptimos.

  • Se requieren más de 4,000 particiones. BigQuery tiene un límite de 4,000 particiones para una tabla particionada. No hay límite para la cantidad de clústeres en una tabla.

Ten en cuenta que puedes realizar particiones y agrupar datos en clústeres en la misma columna de números enteros con el fin de obtener los beneficios de ambos. Los datos se particionarán, en primer lugar, de acuerdo con los rangos de números enteros especificados. Dentro de cada rango, si el volumen de datos es lo suficientemente grande, esos datos también se agruparán en clústeres. Cuando se consulta la tabla, la partición establece un límite superior para el costo de la búsqueda en función de la reducción de la partición. Puede haber otros ahorros en el costo de la búsqueda cuando esta realmente se ejecuta.

Usa require_partitioning_filter

Con la actualización de la partición por rango de números enteros, BigQuery ahora admite varios tipos de particiones:

  • Tiempo de transferencia
  • Fecha/marca de tiempo/fecha y hora
  • Rango de números enteros

Para simplificar la API de BigQuery, quitamos el parámetro require_partitioning_filter del nivel de tipo de partición y lo agregamos al nivel de la tabla. Para establecer la retrocompatibilidad con versiones anteriores de la partición por fecha/marca de tiempo/fecha y hora, aún se admite require_partitioning_filter a nivel de la partición. También se puede especificar a nivel de la tabla. Para la partición por rango de números enteros, puedes especificar require_partitioning_filter solo a nivel de la tabla. La herramienta de línea de comandos bq ya usa la opción de nivel de tabla, por lo que no hay cambios en la forma en que usas el comando bq. Si usas la API de BigQuery, debes utilizar la opción require_partitioning_filter a nivel de la tabla.

Límites y cuotas de tablas particionadas

Las tablas particionadas definieron límites en BigQuery.

Las cuotas y los límites se aplican a los diferentes tipos de trabajos que puedes ejecutar en las tablas particionadas, incluidos los siguientes:

Para obtener más información sobre todas las cuotas y límites, consulta Cuotas y límites.

Precios de tablas de partición

Cuando creas y usas tablas de partición en BigQuery, el cobro se basa en la cantidad de datos almacenados en las particiones y en las consultas que ejecutas en ellos.

Muchas operaciones en tablas de partición son gratuitas, como cargar datos a particiones, copiarlas y exportar datos desde ellas. A pesar de esto, estas operaciones están sujetas a las Cuotas y límites de BigQuery. Para obtener información sobre todas las operaciones gratuitas, consulta Operaciones gratuitas en la página de precios.

Próximos pasos