Consultar tablas con particiones

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

Para obtener información general sobre cómo ejecutar consultas en BigQuery, consulta Ejecutar consultas interactivas y por lotes.

Información general

Si una consulta usa un filtro calificador en el valor de la columna con particiones, BigQuery podrá analizar las particiones que coincidan con el filtro y omitir las demás. Este proceso se denomina recorte de particiones.

El recorte de particiones es el mecanismo que usa BigQuery para retirar las particiones innecesarias del análisis de entradas. Las particiones recortadas no se incluyen al calcular los bytes analizados por la consulta. En general, el recorte de particiones ayuda a reducir el coste de las consultas.

El comportamiento de la poda varía en función del tipo de partición, por lo que puede observar una diferencia en los bytes procesados al consultar tablas que tienen particiones diferentes, pero que son idénticas en otros aspectos. Para estimar cuántos bytes procesará una consulta, haz una prueba de funcionamiento.

Consultar una tabla con particiones por columnas de unidades de tiempo

Para eliminar particiones al consultar una tabla con particiones por columnas de unidades de tiempo, incluye un filtro en la columna de partición.

En el ejemplo siguiente, supongamos que dataset.table tiene particiones en la columna transaction_date. La consulta de ejemplo elimina las fechas anteriores al 2016-01-01.

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

Consultar una tabla con particiones por hora de ingestión

Las tablas con particiones por hora de ingestión contienen una pseudocolumna llamada _PARTITIONTIME, que es la columna de partición. El valor de la columna es la hora de ingestión UTC de cada fila, truncada al límite de la partición (por ejemplo, por horas o por días), como valor TIMESTAMP.

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

  • Tabla con particiones por horas: TIMESTAMP("2021-04-15 08:00:00")
  • Tabla con particiones diarias: TIMESTAMP("2021-04-15")
  • Tabla con particiones mensuales: TIMESTAMP("2021-04-01")
  • Tabla con particiones anuales: TIMESTAMP("2021-01-01")

Si la granularidad de la partición es diaria, la tabla también contiene una pseudocolumna llamada _PARTITIONDATE. El valor es igual a _PARTITIONTIME truncado a un valor de DATE.

Ambos nombres de pseudocolumna están reservados. No puedes crear una columna con ninguno de los dos nombres en ninguna de tus tablas.

Para eliminar particiones, filtre por cualquiera de estas columnas. Por ejemplo, la siguiente consulta solo analiza las particiones entre el 1 y el 2 de enero del 2016:

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

Para seleccionar la pseudocolumna _PARTITIONTIME, debe usar un alias. Por ejemplo, la siguiente consulta selecciona _PARTITIONTIME asignando el alias pt a la pseudocolumna:

SELECT
  _PARTITIONTIME AS pt, column
FROM
  dataset.table

En las tablas particionadas por día, puedes seleccionar la pseudocolumna _PARTITIONDATE de la misma forma:

SELECT
  _PARTITIONDATE AS pd, column
FROM
  dataset.table

Las pseudocolumnas _PARTITIONTIME y _PARTITIONDATE no se devuelven con una instrucción SELECT *. Debes seleccionarlos explícitamente:

SELECT
  _PARTITIONTIME AS pt, *
FROM
  dataset.table

Gestionar zonas horarias en tablas con particiones por hora de ingestión

El valor de _PARTITIONTIME se basa en la fecha UTC en la que se rellena el campo. Si quieres consultar datos en función de una zona horaria distinta a UTC, elige una de las siguientes opciones:

  • Ajusta las diferencias de zona horaria en tus consultas de SQL.
  • Usa decoradores de partición para cargar datos en particiones específicas de tiempo de ingestión, en función de una zona horaria diferente a UTC.

Mejor rendimiento con pseudocolumnas

Para mejorar el rendimiento de las consultas, usa la pseudocolumna _PARTITIONTIME por sí sola en la parte izquierda de una comparación.

Por ejemplo, las dos consultas siguientes son equivalentes. En función del 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 el filtro. La siguiente consulta limita las particiones que se eliminan en función de la primera condición de filtro de 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 incluya ninguna otra columna en un filtro _PARTITIONTIME. Por ejemplo, la siguiente consulta no limita las particiones analizadas porque 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 suele consultar un intervalo de tiempo concreto, le recomendamos que cree una vista que filtre por la pseudocolumna _PARTITIONTIME. Por ejemplo, la siguiente instrucción crea una vista que incluye solo los datos de los últimos siete días 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 Crear vistas.

Consultar una tabla con particiones de rangos de números enteros

Para eliminar particiones al consultar una tabla con particiones de intervalos de números enteros, incluye un filtro en la columna de partición de números enteros.

En el siguiente ejemplo, supongamos que dataset.table es una tabla con particiones de intervalo 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 empiezan por 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 |
+-------------+-------+

La eliminación de particiones no se admite en las funciones de una columna particionada de intervalo de números enteros. Por ejemplo, la siguiente consulta analiza toda la tabla.

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

Consultar datos en el almacenamiento optimizado para escritura

La partición __UNPARTITIONED__ contiene temporalmente los datos que se transmiten a una tabla particionada mientras se encuentran en el almacenamiento optimizado para escritura. Los datos que se transmiten directamente a una partición específica de una tabla con particiones no usan la partición __UNPARTITIONED__. En su lugar, los datos se envían directamente a la partición.

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

Para consultar datos de 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 Insertar datos en tablas particionadas mediante streaming.

Prácticas recomendadas para la eliminación de particiones

Usar una expresión de filtro constante

Para limitar las particiones que se analizan en una consulta, usa una expresión constante en el filtro. Si usas expresiones dinámicas en el filtro de la consulta, BigQuery debe analizar todas las particiones.

Por ejemplo, la siguiente consulta elimina particiones porque el filtro contiene una expresión constante:

SELECT
  t1.name,
  t2.category
FROM
  table1 AS t1
INNER JOIN
  table2 AS t2
ON t1.id_field = t2.field2
WHERE
  t1.ts = CURRENT_TIMESTAMP()

Sin embargo, la siguiente consulta no elimina particiones porque el filtro WHERE t1.ts = (SELECT timestamp from table where key = 2) no es una expresión constante, sino que depende de los valores dinámicos de los campos timestamp y key:

SELECT
  t1.name,
  t2.category
FROM
  table1 AS t1
INNER JOIN
  table2 AS t2
ON
  t1.id_field = t2.field2
WHERE
  t1.ts = (SELECT timestamp from table3 where key = 2)

Aísla la columna de partición en el filtro

Aísla la columna de partición al expresar un filtro. Los filtros que requieren datos de varios campos para hacer cálculos no eliminarán particiones. Por ejemplo, una consulta con una comparación de fechas que use la columna de partición y un segundo campo, o las consultas que contengan concatenaciones de campos, no eliminarán particiones.

Por ejemplo, el siguiente filtro no recorta particiones porque requiere un cálculo basado en el campo de partición ts y en un segundo campo ts2:

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

Requerir un filtro de partición en las consultas

Cuando creas una tabla con particiones, puedes requerir el uso de filtros de predicado habilitando la opción Requerir filtro de partición. Cuando se aplica esta opción, al intentar consultar la tabla particionada sin especificar una cláusula WHERE, se produce 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 con particiones.

Debe haber al menos un predicado que solo haga referencia a una columna de partición para que el filtro se considere apto para la eliminación de particiones. Por ejemplo, en una tabla con particiones en la columna partition_id y una columna adicional f en su esquema, se cumplen los requisitos de las dos cláusulas WHERE siguientes:

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

Sin embargo, WHERE (partition_id = "20221231" OR f = "20221130") no es suficiente.

En el caso de las tablas con particiones por hora de ingestión, usa la pseudocolumna _PARTITIONTIME o _PARTITIONDATE.

Para obtener más información sobre cómo añadir la opción Requerir filtro de partición al crear una tabla con particiones, consulta el artículo Crear tablas con particiones. También puedes actualizar este ajuste en una tabla ya creada.

Siguientes pasos