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

Usa una expresión de filtro constante

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

Por ejemplo, la siguiente consulta reduce 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 reduce las particiones, ya que 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 la partición en tu filtro

Aísla la columna de partición cuando expresa un filtro. Los filtros que requieren datos de varios campos para el procesamiento no reducirán las particiones. Por ejemplo, una consulta con una comparación de fecha con la columna de partición y un segundo campo, o las consultas que contienen algunas concatenaciones de campo no reducirán las particiones.

Por ejemplo, el siguiente filtro no reduce las particiones, ya que requiere un procesamiento basado en el campo de partición ts y un segundo campo ts2:

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

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.

Debe haber al menos un predicado que solo haga referencia a una columna de partición a fin de que el filtro se considere apto para eliminar particiones. Por ejemplo, para una tabla particionada en una 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, WHERE (partition_id = "20221231" OR f = "20221130") no es suficiente.

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?