Consulta tablas particionadas

En este documento, se describen algunas consideraciones específicas para consultar tablas particionadas en BigQuery.

Para obtener información general sobre cómo ejecutar consultas en BigQuery, lee la sección Ejecuta consultas interactivas y por lotes.

Descripción general

Si una consulta usa un filtro apto en el valor de la columna de partición, BigQuery puede analizar las particiones que coinciden con el filtro y omitir las particiones restantes. Este proceso se llama reducción de particiones.

La reducción de las particiones es el mecanismo que utiliza BigQuery para eliminar las particiones innecesarias desde el análisis de entrada. Las particiones reducidas no se incluyen cuando se calculan los bytes analizados en la consulta. Por lo general, la reducción de la partición ayuda a reducir el costo de la consulta.

Los comportamientos de la reducción varían para los diferentes tipos de partición, por lo que puedes ver una diferencia en los bytes procesados cuando se consultan tablas particionadas de forma diferente, pero que son idénticas. Para estimar cuántos bytes procesará una consulta, realiza una ejecución de prueba.

Crea una tabla particionada por columnas de unidad de tiempo

Para reducir las particiones cuando consultas una tabla particionada por columnas de unidad de tiempo, incluye un filtro en la columna de partición.

En el siguiente ejemplo, supongamos que dataset.table está particionada en la columna transaction_date. La consulta de ejemplo reduce las fechas previas al 2016-01-01.

SELECT * FROM dataset.table
WHERE transaction_date >= '2016-01-01'

Consulta una tabla particionada por tiempo de transferencia

Las tablas particionadas por tiempo de transferencia contienen una seudocolumna llamada _PARTITIONTIME, que es la columna de partición. El valor de la columna es el tiempo de transferencia de UTC para cada fila, truncado al límite de partición (como por hora o por día), como un valor TIMESTAMP.

Por ejemplo, si agregas datos el 15 de abril de 2021 a las 08:15:00 UTC, la columna _PARTITIONTIME de esas filas contendrá los siguientes valores:

  • Tabla particionada por hora: TIMESTAMP("2021-04-15 08:00:00")
  • Tabla particionada por día: TIMESTAMP("2021-04-15")
  • Tabla particionada por mes: TIMESTAMP("2021-04-01")
  • Tabla particionada por año: TIMESTAMP("2021-01-01")

Si el nivel de detalle de la partición es diario, la tabla también contiene una seudocolumna llamada _PARTITIONDATE. El valor es igual a _PARTITIONTIME truncado a un valor DATE.

Ambos nombres de las seudocolumnas están reservados. No puedes crear una columna con esos nombres en ninguna de tus tablas.

Para reducir las particiones, filtra en cualquiera de estas columnas. Por ejemplo, la siguiente consulta analiza solo las particiones entre las fechas 1 de enero de 2016 y 2 de enero de 2016:

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')

Para seleccionar la seudocolumna _PARTITIONTIME, debes usar un alias. Por ejemplo, la siguiente consulta selecciona _PARTITIONTIME mediante la asignación del alias pt a la seudocolumna:

SELECT
  _PARTITIONTIME AS pt, column
FROM
  dataset.table

Para las tablas particionadas por día, puedes seleccionar la seudocolumna _PARTITIONDATE de la misma manera:

SELECT
  _PARTITIONDATE AS pd, column
FROM
  dataset.table

Una declaración SELECT * no muestra las seudocolumnas _PARTITIONTIME ni _PARTITIONDATE. Debes seleccionarlas de manera explícita:

SELECT
  _PARTITIONTIME AS pt, *
FROM
  dataset.table

Controla zonas horarias en tablas particionadas por tiempo de transferencia

El valor de _PARTITIONTIME se basa en la fecha UTC cuando se propaga el campo. Si deseas consultar datos basados en una zona horaria distinta de UTC, elige una de las siguientes opciones:

  • Ajusta las diferencias de zona horaria en tus consultas en SQL.
  • Usa decoradores de partición para cargar datos en particiones por tiempo de transferencia específicas, según una zona horaria distinta de UTC.

Mejor rendimiento con seudocolumnas

Para mejorar el rendimiento de las consultas, usa la seudocolumna _PARTITIONTIME sola en el lado izquierdo de la comparación.

Por ejemplo, las dos consultas siguientes son equivalentes. Según el tamaño de la tabla, la segunda consulta podría tener un mejor rendimiento, ya que coloca _PARTITIONTIME por sí solo en el lado izquierdo del operador >. Ambas consultas procesan la misma cantidad de datos.

-- Might be slower.
SELECT
  field1
FROM
  dataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15");

-- Often performs better.
SELECT
  field1
FROM
  dataset.table1
WHERE
  _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY);

Para limitar las particiones que se analizan en una consulta, usa una expresión constante en tu filtro. La siguiente consulta limita las particiones que se reducen según la primera condición de filtro en la cláusula WHERE. Sin embargo, la segunda condición de filtro no limita las particiones analizadas, ya que usa valores de tabla, que son dinámicos.

SELECT
  column
FROM
  dataset.table2
WHERE
  -- This filter condition limits the scanned partitions:
  _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')
  -- This one doesn't, because it uses dynamic table values:
  AND _PARTITIONTIME = (SELECT MAX(timestamp) from dataset.table1)

Para limitar las particiones analizadas, no incluyas ninguna otra columna en un filtro _PARTITIONTIME. Por ejemplo, la consulta siguiente no limita las particiones analizadas, ya que field1 es una columna de la tabla.

-- Scans all partitions of table2. No pruning.
SELECT
  field1
FROM
  dataset.table2
WHERE
  _PARTITIONTIME + field1 = TIMESTAMP('2016-03-28');

Si a menudo consultas un rango de veces en particular, considera crear una vista que filtre la seudocolumna _PARTITIONTIME. Por ejemplo, la siguiente declaración crea una vista que incluye solo los últimos siete días de datos de una tabla llamada dataset.partitioned_table:

-- This view provides pruning.
CREATE VIEW dataset.past_week AS
  SELECT *
  FROM
    dataset.partitioned_table
  WHERE _PARTITIONTIME BETWEEN
    TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 7 * 24 HOUR), DAY)
    AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, DAY);

Para obtener información sobre cómo crear vistas, consulta Crea vistas.

Consulta una tabla particionada por rango de números enteros

Para reducir las particiones cuando consultas una tabla particionada por rango de números enteros, incluye un filtro en la columna de partición por números enteros.

En el siguiente ejemplo, supongamos que dataset.table es una tabla particionada por rango de números enteros con una especificación de partición de customer_id:0:100:10. La consulta de ejemplo analiza las tres particiones que comienzan con 30, 40 y 50.

SELECT * FROM dataset.table
WHERE customer_id BETWEEN 30 AND 50

+-------------+-------+
| customer_id | value |
+-------------+-------+
|          40 |    41 |
|          45 |    46 |
|          30 |    31 |
|          35 |    36 |
|          50 |    51 |
+-------------+-------+

No se admite la reducción de particiones para funciones de más de una columna particionada por rango de números enteros. Por ejemplo, la siguiente consulta analiza la tabla completa.

SELECT * FROM dataset.table
WHERE customer_id + 1 BETWEEN 30 AND 50

Consulta datos en el almacenamiento optimizado para escritura

La partición __UNPARTITIONED__ almacena de forma temporal los datos que se transmiten a una tabla particionada mientras esta se encuentra en el almacenamiento optimizado para escritura. Los datos que se transmiten de forma directa a una partición específica de una tabla particionada no usan la partición __UNPARTITIONED__. En su lugar, los datos se envían a la partición de forma directa.

Los datos en el almacenamiento optimizado para escritura tienen valores NULL en las columnas _PARTITIONTIME y _PARTITIONDATE.

Para consultar datos en la partición __UNPARTITIONED__, usa la pseudocolumna _PARTITIONTIME con el valor NULL. Por ejemplo:

SELECT
  column
FROM dataset.table
WHERE
  _PARTITIONTIME IS NULL

Para obtener más información, consulta Transmite a tablas particionadas.

Prácticas recomendadas para la reducción de particiones

En esta sección, se describen las prácticas recomendadas para escribir consultas que utilizan la reducción de particiones para optimizar el rendimiento de las consultas y reducir los costos.

Usa una expresión de filtro constante

Para limitar las particiones que se analizan en una consulta, filtra la columna de partición con una expresión constante, en lugar de una expresión dinámica.

La siguiente consulta reduce particiones:

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = CURRENT_TIMESTAMP()

En comparación, la siguiente consulta no reduce las particiones, ya que el predicado WHERE t1.ts = (SELECT timestamp FROM table3 WHERE key = 2) no es una expresión constante. Esta consulta compara la columna de partición con un valor dinámico, lo que impide la eliminación de particiones.

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = (SELECT timestamp FROM table3 WHERE key = 2)

Además, una consulta con los siguientes predicados no reduce las particiones, ya que requiere un procesamiento basado en una segunda columna de tabla no constante ts2 o duration:

WHERE ts >= ts2

WHERE ts < CURRENT_TIMESTAMP() - duration

Aísla la columna de partición o usa funciones compatibles

Para reducir las particiones, las condiciones del filtro deben estructurarse de manera que BigQuery pueda determinar qué particiones analizar sin leer los datos de la tabla. Para lograrlo, aísla la columna de partición en un lado de un operador de comparación o envuelve la columna solo en una función integrada compatible. Puedes usar la ejecución de prueba para verificar si la eliminación de particiones es compatible con tu consulta en particular.

Las siguientes funciones integradas en la columna de partición admiten la reducción de particiones si sus argumentos adicionales son constantes:

Otras funciones y operaciones matemáticas complejas requerirán un análisis completo de la tabla.

Ejemplos

Las siguientes consultas muestran ejemplos de predicados que admiten la reducción de particiones.

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour = '2025-03-30 12:00:00';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-03-30'
  AND datehour < TIMESTAMP_ADD('2025-03-30', INTERVAL 1 DAY);
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE DATE(datehour) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(DATE FROM datehour) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE CAST(datehour AS DATE) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-01-01' AND datehour < '2025-02-01';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_TRUNC(datehour, MONTH) >= '2025-04-01'
  AND TIMESTAMP_TRUNC(datehour, MONTH) < '2025-07-01';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_DIFF(datehour, '2025-01-01', DAY) < 1;
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_ADD(datehour, INTERVAL 1 DAY) < '2025-01-03';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_SUB(datehour, INTERVAL 1 DAY) < '2025-01-01';

La siguiente consulta omite todas las particiones porque el predicado no coincide con ninguna fila.

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(YEAR FROM datehour) = 1900;

La siguiente consulta selecciona el primer día de cada mes en la tabla y admite la reducción de particiones. 

SELECT COUNT(*) FROM bigquery-public-data.wikipedia.pageviews_2025
WHERE DATE(datehour) IN UNNEST(GENERATE_DATE_ARRAY(
  DATE_TRUNC(CURRENT_DATE(), YEAR),
  DATE(DATE_TRUNC(CURRENT_DATE(), YEAR) + INTERVAL 1 YEAR - INTERVAL 1 DAY),
  INTERVAL 1 MONTH
))

Las consultas con los siguientes predicados no reducen particiones porque manipulan la columna de partición con funciones no admitidas:

WHERE FORMAT_DATE('%Y-%m-%d %H', ts) = '2025-03-28 20';

WHERE EXTRACT(MONTH FROM ts) = 3 AND EXTRACT(HOUR FROM ts) = 20

Del mismo modo, una consulta con el siguiente predicado no reduce las particiones porque manipula la columna de partición con una operación aritmética:

WHERE ts + INTERVAL 1 DAY > CURRENT_TIMESTAMP()

Para habilitar la reducción de particiones, debes volver a escribir la expresión aislando la columna de partición ts de las funciones o las operaciones aritméticas no admitidas. Para los períodos, usa >= y < para capturar el rango exacto. Para las operaciones aritméticas, mueve la operación al otro lado de la comparación.

La siguiente consulta permite la reducción de particiones aislando la columna de partición ts para un período:

WHERE ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

La siguiente consulta permite la reducción de particiones aislando la columna de partición de la operación aritmética:

WHERE ts > CURRENT_TIMESTAMP() - INTERVAL 1 DAY

Cómo filtrar por varias columnas

Un predicado en la columna de partición de una consulta no restringe los otros filtros que puedes aplicar. Puedes incluir predicados en otras columnas en la misma cláusula WHERE, y la reducción de particiones seguirá ocurriendo siempre que la condición que evalúa la columna de partición siga las prácticas recomendadas. Ten en cuenta que AND es importante en el siguiente ejemplo. Si AND se cambia a OR, la reducción de particiones no funcionará, ya que, incluso si una partición no coincide con el predicado en la columna de partición, no se puede reducir. Los datos de estas particiones con meter_id = 1234 aún cumplen con los requisitos para la consulta.

Ten en cuenta que los predicados no tienen que escribirse en un orden específico. En la siguiente consulta de ejemplo, si se supone que la partición se realiza en la columna ts, la reducción de particiones se sigue produciendo independientemente de la ubicación del predicado.

WHERE meter_id = 1234
  AND ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

Exige un filtro de partición en las consultas

Cuando creas una tabla particionada, puedes exigir el uso de filtros predicados si habilitas la opción Exigir filtro de partición. Cuando se aplica esta opción, los intentos de consultar la tabla particionada sin especificar una cláusula WHERE producen el siguiente error: .

Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination

Este requisito también se aplica a las consultas en vistas y vistas materializadas que hacen referencia a la tabla particionada.

Debe haber al menos un predicado que solo haga referencia a una columna de partición para que el filtro se considere apto para eliminar particiones. Para una tabla particionada en la columna partition_id con una columna adicional f en su esquema, las siguientes cláusulas WHERE cumplen con el requisito:

WHERE partition_id = "20221231"

WHERE partition_id = "20221231" AND f = "20221130"

Sin embargo, lo siguiente no es suficiente y generará un error:

WHERE partition_id = "20221231" OR f = "20221130"

Para las tablas particionadas por tiempo de transferencia, usa la seudocolumna _PARTITIONTIME o _PARTITIONDATE.

Para obtener más información sobre cómo agregar la opción Exigir filtro de partición cuando creas una tabla particionada, consulta Crea tablas particionadas. También puedes actualizar esta configuración en una tabla existente.

¿Qué sigue?