Consulta tablas particionadas

Consulta tablas particionadas

Puedes consultar tablas particionadas si:

  • usas GCP Console o la IU web de BigQuery
  • usas el comando bq query de la herramienta de línea de comandos
  • llamas al método de la API jobs.insert y configuras un trabajo de consulta

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

Permisos necesarios

Para consultar una tabla particionada a nivel de conjunto de datos, es necesario el acceso READER al conjunto de datos que contiene la tabla.

En lugar de usar permisos a nivel de conjunto de datos, puedes aprovechar una función de IAM a nivel de proyecto que incluya permisos bigquery.tables.getData. Los permisos bigquery.tables.getData se necesitan para leer los datos en la tabla que se consulta.

Todas las funciones IAM predeterminadas a nivel de proyecto incluyen los permisos bigquery.tables.getData, a excepción de bigquery.user, bigquery.jobUser y bigquery.metadataViewer.

También debes tener permisos bigquery.jobs.create para ejecutar los trabajos de consulta. Las siguientes funciones de IAM predefinidas a nivel de proyecto incluyen los permisos bigquery.jobs.create:

Para obtener más información sobre los permisos y las funciones de IAM en BigQuery, consulta Control de acceso. Para obtener más información sobre las funciones a nivel de conjunto de datos, consulta Funciones básicas para conjuntos de datos.

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. Para obtener información acerca de cómo realizar consultas en tablas particionadas, consulta Consultar tablas particionadas.

La pseudocolumna _PARTITIONTIME

La pseudocolumna _PARTITIONTIME contiene una marca de tiempo basada en la hora UTC y representa el número de microsegundos desde el punto de inicio del tiempo unix. Por ejemplo, si los datos se anexan a una tabla el 5 de abril de 2016, todas las filas de datos que se anexan ese día contienen el valor TIMESTAMP("2016-04-15") en la columna _PARTITIONTIME.

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

SELECT
  _PARTITIONTIME AS pt,
  [COLUMN]
FROM
  [DATASET].[TABLE]

Donde:

  • [COLUMN] es el nombre de la columna a 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 en el 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 utilizar un alias. Por ejemplo, la siguiente consulta selecciona _PARTITIONDATE mediante la asignación del alias a la pseudocolumna pd:

SELECT
  _PARTITIONDATE AS pd,
  [COLUMN]
FROM
  [DATASET].[TABLE]

Donde:

  • [COLUMN] es el nombre de la columna a 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 en el 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 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].[TABLE2] 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].[TABLE2] t2
WHERE
  t1.[COLUMN2] = "one"

La siguiente consulta limita algunas particiones que están basadas 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

Los siguientes ejemplos utilizan pseudocolumnas, pero 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 cercanamente posible al nombre de la tabla. Por ejemplo, la siguiente consulta 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 siguiente consulta no limita las particiones que se analizan, 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 utilizar para limitar el número de particiones analizadas en una tabla particionada. Por ejemplo, la siguiente consulta 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 siguientes consultas procesan la misma cantidad de datos, pero el segundo ejemplo proporciona un mejor rendimiento.

Ejemplo 1: La siguiente consulta podría ser más lenta, 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 siguiente consulta podría tener un mejor rendimiento, ya que ubica la pseudocolumna sola al 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 al 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 el número 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 teniendo como referencia las 12:00 AM UTC. Si quieres consultar datos en función de una zona horaria distinta de UTC, debes seleccionar una de las siguientes opciones antes de comenzar a cargar datos en tu tabla.

Hay 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 por separado o utilizar decoradores de particiones para cargar datos en una partición específica.

Si usas una columna de marca de tiempo, puedes utilizar 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

Para ajustar las zonas horarias mediante una marca de tiempo, crea una columna por separado para almacenar una marca de tiempo que te permita abordar las filas por hora o por minuto.

Para consultar datos basados en una zona horaria distinta a UTC, usa tanto la pseudocolumna _PARTITIONTIME como 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, se puede utilizar la siguiente consulta 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 en tiempo de transferencia

La partición __UNPARTITIONED__ almacena temporalmente los datos que se envían a una tabla particionada mientras esta se encuentra en el búfer de transmisión. Los datos que se envían directamente a una partición específica de una tabla particionada no utilizan la partición __UNPARTITIONED__. En su lugar, los datos se envían directamente a la partición. Consulta Envíos a tablas particionadas para obtener más información.

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

SELECT
  [COLUMN]
FROM
  [DATASET].[TABLE]
WHERE
  _PARTITIONTIME IS NULL

Donde:

  • [COLUMN] es el nombre de la columna a 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.

Cómo consultar tablas particionadas

Las tablas particionadas basadas en una columna TIMESTAMP o DATE no tienen pseudocolumnas. Para limitar el número de particiones analizadas al consultar tablas particionadas, usa un filtro de predicado (una cláusula WHERE).

Cómo reducir (limitar) particiones

Expresa el filtro del predicado lo más cercanamente 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 eliminarán las particiones de la consulta.

Por ejemplo, la siguiente consulta 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 siguiente consulta no reduce las particiones (observa que se utiliza 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 al expresar 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 utilizando 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 en un segundo campo ts2:

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

Próximos pasos

¿Te sirvió esta página? Envíanos tu opinión:

Enviar comentarios sobre…

¿Necesitas ayuda? Visita nuestra página de asistencia.