Consulta tablas particionadas

Consulta tablas particionadas

Puedes consultar tablas particionadas si cumples con estas condiciones:

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

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, cuando cree un conjunto de datos, se le otorgará el permiso bigquery.dataOwner para acceder a este. 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.

Pseudocolumnas de tablas particionadas en tiempo de transferencia

Cuando creas una tabla particionada por tiempo de transferencia, se agregan dos pseudocolumnas a la tabla: una pseudocolumna _PARTITIONTIME y una _PARTITIONDATE. La pseudocolumna _PARTITIONTIME contiene una marca de tiempo basada en la fecha para los datos que se cargan en la tabla. La pseudocolumna _PARTITIONDATE contiene una representación de la fecha. Ambos nombres de las pseudocolumnas 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 pseudocolumnas. Si quieres obtener información acerca de cómo consultar tablas particionadas, lee Consulta tablas particionadas.

La pseudocolumna _PARTITIONTIME

La pseudocolumna _PARTITIONTIME contiene una marca de tiempo basada en la hora UTC y representa la cantidad de microsegundos desde el punto de inicio del tiempo 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.

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

SELECT
  _PARTITIONTIME AS pt,
  column
FROM
  dataset.table

Esta es una explicación de los parámetros del ejemplo anterior:

  • column es el nombre de una 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 pseudocolumna _PARTITIONDATE

La pseudocolumna _PARTITIONDATE contiene la hora UTC correspondiente al valor en la pseudocolumna _PARTITIONTIME.

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

SELECT
  _PARTITIONDATE AS pd,
  column
FROM
  dataset.table

Esta es una explicación de los parámetros del ejemplo anterior:

  • column es el nombre de una columna que se consultará. Puedes especificar varias columnas como una lista separada por comas.
  • dataset.table es el conjunto de datos que contiene la tabla particionada.
  • dataset.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 pseudocolumnas

Cuando consultas datos en tablas particionadas por tiempo de transferencia, haces referencia a particiones concretas mediante la especificación de los valores en las pseudocolumnas _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'

Cómo limitar las particiones consultadas mediante pseudocolumnas

Usa las pseudocolumnas _PARTITIONTIME y _PARTITIONDATE para limitar el número de particiones analizadas durante 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 consulta siguiente 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

En este ejemplo, se muestra la limitación de la cantidad de particiones analizadas con 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 pseudocolumnas que analizan todas las particiones

En los ejemplos siguientes, se usan pseudocolumnas, pero se analizan todas las particiones en una tabla con particiones por tiempo.

En SQL heredado, el filtro _PARTITIONTIME funciona solo cuando el filtro 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
  mydataset.table2 t1
CROSS JOIN
  mydataset.table2 t2
WHERE
  t1._PARTITIONTIME = TIMESTAMP('2016-03-28')
  AND t1.field2 = "one"

No incluyas ninguna otra columna en un filtro _PARTITIONTIME. Por ejemplo, la consulta siguiente no limita las particiones que se analizaron, ya que field2 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
  mydataset.table2
WHERE
  _PARTITIONTIME + field2 = 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 mydataset.table2:

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

Mejor rendimiento con pseudocolumnas

Para mejorar el rendimiento de la consulta, utiliza la pseudocolumna _PARTITIONTIME de la izquierda sola en una comparación. Por ejemplo, las consultas siguientes procesan la misma cantidad de datos, pero el segundo ejemplo proporciona un mejor rendimiento.

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

SQL estándar

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

SQL heredado

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

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

SQL estándar

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

SQL heredado

#legacySQL
/* Often performs better */
SELECT
  field1
FROM
  mydataset.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 pseudocolumnas para limitar la cantidad de particiones analizadas durante una consulta, también puedes usar las pseudocolumnas 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.

Cómo consultar tablas particionadas en 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.

Cómo consultar 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, para 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:

SQL estándar

#standardSQL
SELECT
  field1
FROM
  mydataset.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
  mydataset.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 pseudocolumnas de tablas particionadas en 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 consulta siguiente se puede usar para crear una vista que incluya solo los últimos siete días de datos de una tabla llamada mydataset.partitioned_table:

SQL estándar

#standardSQL
SELECT
  *
FROM
  mydataset.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
  mydataset.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 que:

  • column es el nombre de una 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 transmisiones analizadas cuando se consultan tablas particionadas, usa un filtro de predicado (una cláusula WHERE).

Cuando creas una tabla particionada, puedes requerir el uso de filtros predicados si habilitas la opción Requerir 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 error siguiente: 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 Requerir 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.

Cómo reducir (limitar) 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 filtro siguiente 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

¿Te ha resultado útil esta página? Enviar comentarios:

Enviar comentarios sobre...

Si necesitas ayuda, visita nuestra página de asistencia.