Consulta tablas particionadas

Consulta tablas particionadas

Puedes consultar tablas particionadas de las siguientes maneras:

  • Con Cloud Console o la IU web clásica de BigQuery
  • Mediante el comando bq query de la herramienta de línea de comandos
  • Con una llamada al método jobs.insert de la API y la configuración de un trabajo de consulta
  • Con las bibliotecas cliente

Para obtener más información sobre cómo ejecutar consultas, revisa Ejecuta consultas interactivas o por lotes.

Permisos necesarios

Como mínimo, para consultar una tabla, debes tener los permisos bigquery.tables.getData.

Las siguientes funciones predefinidas de Cloud IAM incluyen los permisos bigquery.tables.getData:

  • bigquery.dataViewer
  • bigquery.dataOwner
  • bigquery.dataEditor
  • bigquery.admin

Además, si un usuario tiene permisos bigquery.datasets.create, se le otorga el acceso bigquery.dataOwner cuando crea un conjunto de datos. El acceso a bigquery.dataOwner permite al usuario consultar tablas y vistas del conjunto de datos.

También debes tener permisos de bigquery.jobs.create si quieres ejecutar trabajos de consulta. Las siguientes funciones predefinidas de Cloud IAM incluyen los permisos bigquery.jobs.create:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

Para obtener más información sobre las funciones y los permisos de Cloud IAM en BigQuery, consulta la página sobre control de acceso.

Seudocolumnas de tablas particionadas por tiempo de transferencia

Cuando creas una tabla particionada por tiempo de transferencia, se agregan dos seudocolumnas a la tabla: una seudocolumna _PARTITIONTIME y una _PARTITIONDATE. La seudocolumna _PARTITIONTIME contiene una marca de tiempo basada en la fecha para los datos que se cargan en la tabla. La seudocolumna _PARTITIONDATE contiene una representación de la fecha. Ambos nombres de las seudocolumnas están reservados, lo que significa que no puedes crear una columna con esos nombres en ninguna de tus tablas.

_PARTITIONTIME y _PARTITIONDATE están disponibles solo en las tablas particionadas en tiempo de transferencia. Las tablas particionadas no tienen seudocolumnas. Si quieres obtener información acerca de cómo consultar tablas particionadas, lee Consulta tablas particionadas.

La seudocolumna _PARTITIONTIME

La seudocolumna _PARTITIONTIME contiene una marca de tiempo basada en la hora UTC y representa la cantidad de microsegundos desde la época Unix. Por ejemplo, si los datos se anexaron a una tabla el 15 de abril de 2016, todas las filas de datos que se anexaron ese día contienen el valor TIMESTAMP("2016-04-15") en la columna _PARTITIONTIME. En las tablas particionadas por hora, este valor también incluye la hora en que se agregaron (por ejemplo, TIMESTAMP("2016-04-15 12:00:00")).

Para consultar 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

En el ejemplo anterior, se ilustra lo siguiente:

  • column es el nombre de la columna que se consultará. Puedes especificar varias columnas como una lista separada por comas.
  • dataset es el conjunto de datos que contiene la tabla particionada.
  • table es la tabla particionada.

Los datos del búfer de transmisión tienen valores NULL en la columna _PARTITIONTIME.

La seudocolumna _PARTITIONDATE

La seudocolumna _PARTITIONDATE contiene la fecha UTC correspondiente al valor de la seudocolumna _PARTITIONTIME. Esta columna no es compatible con las tablas particionadas por hora.

Para consultar la seudocolumna _PARTITIONDATE, debes usar un alias. Por ejemplo, la siguiente consulta selecciona _PARTITIONDATE mediante la asignación del alias pd a la seudocolumna:

SELECT
  _PARTITIONDATE AS pd,
  column
FROM
  dataset.table

En el ejemplo anterior, se ilustra lo siguiente:

  • column es el nombre de la columna que se consultará. Puedes especificar varias columnas como una lista separada por comas.
  • dataset es el conjunto de datos que contiene la tabla particionada.
  • table es la tabla particionada.

Los datos del búfer de transmisión tienen valores NULL en la columna _PARTITIONDATE.

Consulta tablas particionadas por tiempo de transferencia mediante seudocolumnas

Cuando consultas datos almacenados en tablas particionadas por tiempo de transferencia, haces referencia a particiones concretas mediante la especificación de los valores de las seudocolumnas _PARTITIONTIME o _PARTITIONDATE. Por ejemplo:

  • _PARTITIONTIME >= "2018-01-29 00:00:00" AND _PARTITIONTIME < "2018-01-30 00:00:00"
  • _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')

o

  • _PARTITIONDATE >= "2018-01-29" AND _PARTITIONDATE < "2018-01-30"
  • _PARTITIONDATE BETWEEN '2016-01-01' AND '2016-01-02'

Limita las particiones consultadas mediante seudocolumnas

Usa las seudocolumnas _PARTITIONTIME y _PARTITIONDATE para limitar el número de particiones analizadas en una consulta. Esto también se conoce como 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, lo que reduce el costo del análisis bajo demanda. Por lo general, la reducción de la partición disminuirá el costo de la consulta cuando los filtros se puedan evaluar al inicio de la consulta sin requerir ninguna evaluación de subconsultas ni análisis de datos.

Por ejemplo, la siguiente consulta analiza solo las particiones entre las fechas 1 de enero de 2016 y 2 de enero de 2016 de la tabla particionada:

_PARTITIONTIME

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

_PARTITIONDATE

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONDATE BETWEEN '2016-01-01'
  AND '2016-01-02'

Ejemplos de reducción de particiones

Este ejemplo demuestra la limitación del número de particiones analizadas utilizando un filtro de pseudocolumnas en una subconsulta:

_PARTITIONTIME

SELECT
  column1,
  column2
FROM (
  SELECT
    column1,
    column2
  FROM
    dataset.table
  WHERE
    _PARTITIONTIME = TIMESTAMP('2016-03-28')) t1
CROSS JOIN
  dataset.table t2
WHERE
  t1.column2 = "one"

_PARTITIONDATE

SELECT
  column1,
  column2
FROM (
  SELECT
    column1,
    column2
  FROM
    dataset.table
  WHERE
    _PARTITIONDATE = '2016-03-28') t1
CROSS JOIN
  dataset.table t2
WHERE
  t1.column2 = "one"

La consulta siguiente limita algunas particiones que se basan en parte de la condición del filtro, _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01'). No las limita en función de la condición que involucra la subconsulta:

_PARTITIONTIME

SELECT
  column
FROM
  dataset.table2
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')
  AND _PARTITIONTIME = (SELECT MAX(timestamp) from dataset.table1)

_PARTITIONDATE

SELECT
  column
FROM
  dataset.table2
WHERE
  _PARTITIONDATE BETWEEN '2017-01-01' AND '2017-03-01'
  AND _PARTITIONDATE = (SELECT MAX(date) from dataset.table1)

Consultas de seudocolumnas que analizan todas las particiones

En los siguientes ejemplos, se usan seudocolumnas, pero se analizan todas las particiones de una tabla particionada por tiempo.

En SQL heredado, el filtro _PARTITIONTIME funciona solo cuando este se especifica lo más cerca posible al nombre de la tabla. Por ejemplo, la consulta siguiente analiza todas las particiones en table1 a pesar de la presencia del filtro _PARTITIONTIME:

#legacySQL
# Scans all partitions on t1
SELECT
  t1.field1,
  t2.field1
FROM
  dataset.table1 t1
CROSS JOIN
  dataset.table2 t2
WHERE
  table1._PARTITIONTIME = TIMESTAMP('2016-03-28')
  AND t1.field1 = "one"

No incluyas ninguna otra columna en un filtro _PARTITIONTIME. Por ejemplo, la consulta siguiente no limita las particiones que se analizaron, ya que field1 es una columna de la tabla y BigQuery no puede determinar por adelantado qué partición seleccionar.

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

Los filtros en _PARTITIONTIME que incluyen subconsultas no se pueden usar para limitar la cantidad de particiones analizadas en una tabla particionada. Por ejemplo, la consulta siguiente no limita las particiones analizadas en la tabla dataset.table2:

# Scans all partitions of `table2`
SELECT
  field1
FROM
  dataset.table2
WHERE
  _PARTITIONTIME = (SELECT MAX(timestamp) FROM dataset.table1)

Mejora el 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 consultas siguientes procesan la misma cantidad de datos, pero el segundo ejemplo proporciona un mejor rendimiento.

Ejemplo 1: La siguiente consulta podría ejecutarse con más lentitud, ya que combina el valor de la seudocolumna con otras operaciones en el filtro WHERE.

SQL estándar

#standardSQL
/* Can be slower */
SELECT
  field1
FROM
  dataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15")

SQL heredado

#legacySQL
/* Can be slower */
SELECT
  field1
FROM
  dataset.table1
WHERE
  DATE_ADD(_PARTITIONTIME, 5, "DAY") > TIMESTAMP("2016-04-15")

Ejemplo 2: La siguiente consulta podría tener un mejor rendimiento, ya que ubica la seudocolumna sola en el lado izquierdo de la comparación del filtro.

SQL estándar

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

SQL heredado

#legacySQL
/* Often performs better */
SELECT
  field1
FROM
  dataset.table1
WHERE
  _PARTITIONTIME > DATE_ADD(TIMESTAMP('2016-04-15'), -5, "DAY")

Según el tamaño de la tabla, la segunda consulta, que ubica a _PARTITIONTIME sola en el lado izquierdo del operador de comparación >, puede proporcionar un mejor rendimiento que la primera consulta. Dado que las consultas procesan la misma cantidad de datos, el número de bytes que se facturan es el mismo para ambos casos.

Cómo consultar tablas particionadas en tiempo de transferencia mediante una tabla comodín

Además de usar las seudocolumnas para limitar la cantidad de particiones analizadas durante una consulta, también puedes usarlas para consultar varias tablas particionadas mediante una tabla comodín. Para obtener información sobre el uso de una tabla comodín con tablas particionadas, consulta Cómo analizar varias tablas particionadas con _PARTITIONTIME.

Consulta tablas particionadas por tiempo de transferencia mediante zonas horarias

El valor de _PARTITIONTIME se basa en la fecha UTC cuando se propaga el campo, lo que significa que las particiones se dividen en función de las 12:00 a.m. UTC. Si quieres consultar los datos en función de una zona horaria distinta a UTC, debes seleccionar una de las opciones siguientes antes de comenzar a cargar datos en tu tabla

Existen dos maneras de consultar datos en una tabla particionada mediante una zona horaria personalizada distinta a UTC. Puedes crear una columna de marca de tiempo distinta o usar decoradores de particiones para cargar datos en una partición específica.

Si usas una columna de marca de tiempo, puedes usar la partición basada en UTC predeterminada y considerar las diferencias en las zonas horarias en tus consultas de SQL. Como alternativa, si prefieres tener particiones agrupadas por zonas horarias distintas a UTC, usa decoradores de partición para cargar datos en particiones basadas en una zona horaria distinta.

Consulta zonas horarias mediante una columna de marca de tiempo

A fin de ajustar las zonas horarias mediante una marca de tiempo, crea una columna distinta para almacenar una marca de tiempo que te permita trabajar con las filas por hora o por minuto.

Para consultar datos basados en una zona horaria distinta a UTC, usa la pseudocolumna _PARTITIONTIME o tu columna de marca de tiempo personalizada. El uso de _PARTITIONTIME limita el análisis de la tabla a particiones relevantes, y tu marca de tiempo limita aún más los resultados para tu zona horaria. Por ejemplo, a fin de consultar datos de una tabla particionada (mydataset.partitioned_table) con un campo de marca de tiempo MY_TIMESTAMP_FIELD para datos agregados a la tabla entre 2016-05-01 12:00:00 PST y 2016-05-05 14:00:00 PST, haz lo siguiente:

SQL estándar

#standardSQL
SELECT
  field1
FROM
  dataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
  AND TIMESTAMP("2016-05-06")
  AND TIMESTAMP_ADD(MY_TIMESTAMP_FIELD, INTERVAL 8 HOUR) BETWEEN TIMESTAMP("2016-05-01 12:00:00")
  AND TIMESTAMP("2016-05-05 14:00:00");

SQL heredado

#legacySQL
SELECT
  field1
FROM
  dataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
  AND TIMESTAMP("2016-05-06")
  AND DATE_ADD(MY_TIMESTAMP_FIELD, 8, 'HOUR') BETWEEN TIMESTAMP("2016-05-01 12:00:00")
  AND TIMESTAMP("2016-05-05 14:00:00");

Crea una vista mediante seudocolumnas de tablas particionadas por tiempo de transferencia

Para limitar la cantidad de datos que lee una consulta en un conjunto de particiones, crea una vista que contenga un filtro en la pseudocolumna _PARTITIONTIME o _PARTITIONDATE. Por ejemplo, la siguiente consulta se puede usar para crear una vista que incluya solo los últimos siete días de datos de una tabla llamada dataset.partitioned_table:

SQL estándar

#standardSQL
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);

SQL heredado

#legacySQL
SELECT
  *
FROM
  dataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP(UTC_USEC_TO_DAY(NOW() - 7 * 60 * 60 * 24 * 1000000))
  AND TIMESTAMP(UTC_USEC_TO_DAY(CURRENT_TIMESTAMP()));

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

Partición_UNPARTITIONED_ de las tablas particionadas por tiempo de transferencia

La partición __UNPARTITIONED__ almacena de forma temporal los datos que se transmiten a una tabla particionada mientras esta se encuentra en el búfer de transmisión. 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. Consulta transmite a tablas particionadas para obtener más información.

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

En el ejemplo anterior, se ilustra lo siguiente:

  • column es el nombre de la columna que se consultará. Puedes especificar varias columnas como una lista separada por comas.
  • dataset es el conjunto de datos que contiene la tabla particionada.
  • table es la tabla particionada.

Consulta tablas particionadas

Las tablas particionadas basadas en una columna TIMESTAMP o DATE no tienen pseudocolumnas. Para limitar la cantidad de particiones analizadas cuando se consultan tablas particionadas, usa un filtro de predicado (una cláusula WHERE). Cuando se consulta una tabla particionada por hora, se usan filtros en la columna de partición para reducir las particiones y el costo de la consulta. Las tablas particionadas por hora solo se pueden consultar a través de SQL estándar.

Cuando creas una tabla particionada, puedes exigir el uso de filtros de predicado 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.

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.

Si no habilitas la opción Requerir filtro de partición cuando creas una tabla particionada, puedes actualizar la tabla para agregar la opción.

Reduce (limita) las particiones

Expresa el filtro del predicado lo más cerca posible al identificador de la tabla. Las consultas complejas que requieren la evaluación de múltiples etapas de una consulta para resolver el predicado (como las consultas internas o las subconsultas) no borrarán las particiones de la consulta.

Por ejemplo, la consulta siguiente reduce particiones:

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

La consulta siguiente no reduce las particiones (observa que se usa una subconsulta):

#standardSQL
SELECT
  t1.name,
  t2.category
FROM
  table1 t1
INNER JOIN
  table2 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

Pasos siguientes