Programa consultas

Esta página describe cómo programar consultas recurrentes en BigQuery.

Descripción general

Puedes programar consultas para que se ejecuten de forma recurrente. Las consultas programadas deben estar escritas en SQL estándar, que puede incluir declaraciones en lenguaje de definición de datos (DDL) y en lenguaje de manipulación de datos (DML). La string de consulta y la tabla de destino se pueden parametrizar para permitirte organizar los resultados de la consulta por fecha y hora.

Antes de comenzar

Antes de crear una consulta programada:

  • Las consultas programadas utilizan los atributos del Servicio de transferencia de datos de BigQuery. Verifica si completaste todas las acciones necesarias en Habilita el Servicio de transferencia de datos de BigQuery.

  • Si creas la consulta programada con la IU web clásica de BigQuery, habilita las ventanas emergentes en tu navegador de bigquery.cloud.google.com, para que puedas ver la ventana de permisos. Debes otorgar permiso al Servicio de transferencia de datos de BigQuery para que administre tu consulta programada.

Permisos necesarios

Antes de programar una consulta:

  • Asegúrate de que la persona que crea la transferencia tenga los siguientes permisos obligatorios en BigQuery:

    • los permisos bigquery.transfers.update para crear la transferencia
    • los permisos bigquery.datasets.update en el conjunto de datos de destino

    La función de IAM bigquery.admin predefinida a nivel de proyecto incluye los permisos bigquery.transfers.update y bigquery.datasets.update. Para obtener más información sobre las funciones de IAM en BigQuery, consulta Control de acceso.

Opciones de configuración

String de consulta

La string de consulta debe ser válida y estar escrita en SQL estándar. Cada ejecución de una consulta programada puede recibir los siguientes parámetros de consulta.

Parámetros disponibles

Parámetro Tipo de SQL estándar Valor
@run_time MARCA DE TIEMPO Representado en la hora UTC. Para consultas programadas regularmente, run_time representa el tiempo previsto de ejecución. Por ejemplo, si la consulta programada se configura en "cada 24 horas", la diferencia de run_time entre dos consultas consecutivas será de 24 horas exactas, aunque el tiempo de ejecución real podría variar un poco.
@run_date FECHA Representa una fecha lógica del calendario.

Ejemplo

El parámetro @run_time es parte de la string de consulta en este ejemplo, que realiza una consulta a un conjunto de datos públicos llamado hacker_news.stories.

SELECT @run_time AS time,
  title,
  author,
  text
FROM `bigquery-public-data.hacker_news.stories`
LIMIT
  1000

Tabla de destino

Si no existe la tabla de destino para tus resultados cuando configuras la consulta programada, BigQuery intenta crear la tabla por ti. Si usas una consulta DDL o DML, deja la tabla de destino en blanco.

Si la tabla de destino existe, se debe actualizar el esquema de la tabla de destino, según los resultados de la consulta si agregas columnas al esquema (ALLOW_FIELD_ADDITION) o cambias el modo de una columna de REQUIRED a NULLABLE (ALLOW_FIELD_RELAXATION). En todos los demás casos, los cambios de esquema de la tabla entre ejecuciones provocan que la consulta programada falle.

Las consultas pueden hacer referencia a tablas de diferentes proyectos y conjuntos de datos. Al configurar tu consulta programada, no necesitas incluir el conjunto de datos de destino en el nombre de la tabla. Debes especificar el conjunto de datos de destino por separado.

Preferencia de escritura

La preferencia de escritura que selecciones determina cómo se escriben los resultados de tu consulta en una tabla de destino existente. Si usas una consulta DDL o DML, deja la preferencia de escritura en blanco.

  • WRITE_TRUNCATE: Si la tabla ya existe, BigQuery sobrescribe los datos de la tabla.
  • WRITE_APPEND: Si la tabla ya existe, BigQuery adjunta los datos a la tabla.

Crear, truncar o adjuntar una tabla de destino solo ocurre si BigQuery puede completar la consulta con éxito. Las acciones de creación, truncamiento o adjuntado se producen como una actualización atómica al finalizar el trabajo.

Opciones de partición

Las consultas programadas pueden crear tablas de destino particionadas o no particionadas. Si usas una consulta DDL o DML, deja el campo de partición en blanco. Hay dos tipos de partición de tablas en BigQuery:

  • Tablas particionadas por tiempo de transferencia: Tablas que se particionan según el tiempo de ejecución programado de la consulta.
  • Tablas particionadas: Tablas en las que se hace una partición según una columna TIMESTAMP o DATE.

Si la tabla de destino se particionará en una columna, deberás especificar el nombre de la columna al configurar una consulta programada. Deja el Campo de partición en blanco para las tablas particionadas o no particionadas en tiempo de transferencia.

Obtén más información sobre las tablas particionadas en Introducción a las tablas particionadas.

Ejemplos de partición

Parámetros disponibles

Cuando configuras la consulta programada, puedes especificar cómo deseas particionar la tabla de destino con los parámetros de tiempo de ejecución.

Parámetro Tipo de plantilla Valor
run_time Marca de tiempo con formato En horario UTC, según el programa. Para consultas programadas regularmente, run_time representa el tiempo previsto de ejecución. Por ejemplo, si la consulta programada se configura en "cada 24 horas", la diferencia de run_time entre dos consultas consecutivas será de 24 horas exactas, aunque el tiempo de ejecución real podría variar un poco.

Consulta TransferRun.runTime.
run_date String de fecha La fecha del parámetro run_time en el siguiente formato: %Y%m%d; por ejemplo, 20180101. Este formato es compatible con tablas particionadas en tiempo de transferencia.

Sistema de plantillas

Las consultas programadas admiten parámetros de tiempo de ejecución en el nombre de la tabla de destino con una sintaxis de plantilla.

Sintaxis de plantillas de parámetros

La sintaxis de plantillas admite plantillas de string y compensación horaria simples. Los parámetros se referencian en los siguientes formatos:

  • {run_date}
  • {run_time[+\-offset]|"time_format"}
Parámetro Motivo
run_date A este parámetro lo reemplaza la fecha en formato YYYYMMDD.
run_time Este parámetro admite las siguientes propiedades:


offset
Compensación horaria expresada en horas (h), minutos (m) y segundos (s), en ese orden.
Los días (d) no son compatibles.
Se permiten los decimales, por ejemplo: 1.5h.

time_format
Una string de formato. Los parámetros de formato más comunes son los años (%Y), los meses (%m), y los días (%d).
En las tablas particionadas, YYYYMMDD es el sufijo obligatorio, equivalente a "%Y%m%d".

Obtén más información sobre el formato de elementos de fecha y hora.

Notas de uso:
  • No se permiten espacios en blanco entre run_time, la compensación horaria y el formato de hora.
  • Para incluir llaves literales en la string, puedes marcarlas de la siguiente manera: ‘\{‘ and ‘\}’.
  • Para incluir comillas literales o una barra vertical en time_format, como “YYYY|MM|DD”, puedes marcarlas en la string de formato como: ‘\”’ o ‘\|’.

Ejemplos de plantillas de parámetros

Estos ejemplos demuestran la especificación de nombres de tablas de destino con diferentes formatos de hora y compensación del tiempo de ejecución.
run_time (UTC) Parámetro con plantilla Nombre de la tabla de destino de salida
2018-02-15 00:00:00 mytable mytable
2018-02-15 00:00:00 mytable_{run_time|"%Y%m%d"} mytable_20180215
2018-02-15 00:00:00 mytable_+25h{run_time|"%Y%m%d"} mytable_20180216
2018-02-15 00:00:00 mytable_-1h{run_time|"%Y%m%d"} mytable_20180214
2018-02-15 00:00:00 mytable_+1.5h{run_time|"%Y%m%d;%H"}
o
mytable_+90m{run_time|"%Y%m%d;%H"}
mytable_2018021501
2018-02-15 00:00:00 {run_time+97s|"%Y%m%d"}_mytable_{run_time|"%H%M%s"} 20180215_mytable_000137

Configura una consulta programada

Console

  1. Abre la IU web de BigQuery en GCP Console.

    Ir a la IU web de BigQuery

  2. Ejecuta la consulta que te interesa. Cuando estés satisfecho con los resultados, haz clic en Programar consulta (Schedule query) y Crear consulta programada nueva.

    Programar consulta en la IU web de BigQuery

  3. Las opciones de consulta programada se abren en el panel Consulta programada nueva (New scheduled query).

  4. En el panel Consulta programada nueva (New scheduled query):

    • En Nombre de la consulta programada (Name for the scheduled query), ingresa un nombre como My scheduled query. El nombre de la consulta programada puede ser cualquier valor que te permita identificarla con facilidad si necesitas modificarla más tarde.
    • (Opcional) En Opciones de programa (Schedule options), puedes dejar el valor predeterminado Diariamente (Daily) (cada 24 horas, según la hora de creación), o haz clic en Programar hora de inicio (Schedule start time) para cambiar la hora. También puedes cambiar el intervalo a Semanalmente, Mensualmente o Personalizado. Cuando seleccionas Personalizado, se espera una especificación de tiempo similar a Cron, por ejemplo, every 3 hours. El período más corto permitido es de quince minutos. Consulta el campo schedule en TransferConfig para valores de la API válidos adicionales.
    • En Nombre de conjunto de datos (Dataset name), selecciona el conjunto de datos de destino apropiado.
    • En Tabla de destino (Destination table):
      • Para una consulta SQL estándar, ingresa el nombre de tu tabla de destino.
      • Para una consulta DDL o DML, deja este campo en blanco.
    • En Preferencia de escritura de tabla de destino (Destination table write preference), selecciona WRITE_TRUNCATE para reemplazar la tabla de destino o WRITE_APPEND para adjuntar datos a la tabla. Para una consulta DDL o DML, esta opción no se encuentra disponible.
    • (Opcional) Marca Enviar notificaciones por correo electrónico para permitir notificaciones por correo electrónico de errores de ejecuciones de transferencia.

      Nueva consulta programada

  5. Haz clic en Programar (Schedule).

  6. Para ver el estado de tus consultas programadas, haz clic en Consultas programadas (Scheduled queries) en el panel de navegación. Actualiza la página para ver el estado actualizado de tus consultas programadas. Haz clic en una para obtener más detalles sobre esa consulta programada.

    Enumerar consultas programadas

IU clásica

  1. Ve a la IU web de BigQuery.

    Ir a la IU web de BigQuery

  2. Ejecuta la consulta que te interesa. Cuando estés satisfecho con los resultados, haz clic en Schedule Query (Programar consulta).

    Programar consulta en la IU web clásica de BigQuery

  3. Las opciones de consulta programada se abren debajo del cuadro de consulta.

  4. En la página New Scheduled Query (Consulta programada nueva), haz lo siguiente:

    • En Conjunto de datos de destino (Destination dataset), selecciona el conjunto de datos que corresponda.
    • En Nombre visible (Display Name), ingresa un nombre para la consulta programada, como My scheduled query. El nombre de la consulta programada puede ser cualquier valor que te permita identificarla con facilidad si necesitas modificarla más tarde.
    • En Tabla de destino (Destination table), haz lo siguiente:
      • Para una consulta SQL estándar, ingresa el nombre de tu tabla de destino.
      • Para una consulta DDL o DML, deja este campo en blanco.
    • En Preferencia de escritura (Write Preference), haz lo siguiente:
      • En una consulta SQL estándar, selecciona WRITE_TRUNCATE para sobrescribir la tabla de destino o WRITE_APPEND para adjuntar datos a la tabla.
      • Para una consulta DDL o DML, selecciona Sin especificar.
    • (Opcional) En Campo de partición (Partitioning Field):

    • (Opcional) En Programa (Schedule), puedes dejar el valor predeterminado de Diariamente (Daily) (cada 24 horas, según el tiempo de creación) o puedes hacer clic en Editar (Edit) para cambiar la hora. También puedes cambiar el intervalo a Semanalmente, Mensualmente o Personalizado. Cuando seleccionas Personalizado, se espera una especificación de tiempo similar a Cron, por ejemplo, every 3 hours. El período más corto permitido es de quince minutos. Consulta el campo schedule en TransferConfig para valores de la API válidos adicionales.

      Programa de consultas

    • (Opcional) Expande la sección Avanzado (Advanced) y configura las notificaciones de ejecución para tu transferencia. En la actualidad, las notificaciones de ejecución de transferencias se encuentran en versión Alfa.

      • En Tema de Cloud Pub/Sub (Cloud Pub/Sub topic), ingresa tu nombre de tema de Cloud Pub/Sub, por ejemplo, projects/myproject/topics/mytopic.
      • Marca Send email notifications (Enviar notificaciones por correo electrónico) para activar las notificaciones por correo de las ejecuciones de transferencia con errores.

        Tema de Cloud Pub/Sub

  5. Haz clic en Agregar (Add).

  6. Para ver el estado de tus consultas programadas, haz clic en Consultas programadas (Scheduled queries) en el panel de navegación. Actualiza la página para ver el estado actualizado de tus consultas programadas. Haz clic en una para obtener más detalles sobre esa consulta programada.

    Enumerar consultas programadas

Configura una ejecución manual en fechas históricas

Además de programar una consulta para que se ejecute en el futuro, también puedes activar ejecuciones inmediatas manualmente. Sería necesario activar una ejecución inmediata si tu consulta usa el parámetro run_date y si hubo problemas durante una ejecución anterior.

Por ejemplo, todos los días a las 09:00 consultas una tabla de origen para las filas que coinciden con la fecha actual. Sin embargo, descubres que los datos no se agregaron a la tabla de origen durante los últimos tres días. En esta situación, puedes configurar la consulta para que se ejecute en datos históricos dentro de un período que especifiques. Tu consulta se ejecuta con el uso de combinaciones de run_date y run-time que corresponden a las fechas que configuraste en tu consulta programada.

Después de configurar una consulta programada, a continuación se explica cómo puedes ejecutar la consulta mediante el uso del período de datos históricos:

Console

Después de hacer clic en Programar para guardar tu consulta programada, puedes hacer clic en el botón Consultas programadas para ver la lista de consultas programadas actualmente. Haz clic en cualquier nombre visible para ver los detalles del programa de la consulta. En la parte superior derecha de la página, haz clic en Programar reabastecimiento (Schedule backfill) para especificar el período de datos históricos.

botón programar reabastecimiento

Los tiempos de ejecución seleccionados están todos dentro de tu período seleccionado, incluida la primera fecha y excluida la última fecha.

configurar fechas históricas

Ejemplo 1

Tu consulta programada está configurada para ejecutarse every day 09:00 hora del Pacífico. Te faltan datos del 1, 2 y 3 de enero. Selecciona el siguiente período histórico:

Start Time = 1/1/19
End Time = 1/4/19

Tu consulta se ejecuta con los parámetros run_date y run_time que corresponden a los siguientes horarios:

  • 1/1/19 09:00 hora del Pacífico
  • 1/2/19 09:00 hora del Pacífico
  • 1/3/19 09:00 hora del Pacífico

Ejemplo 2

Tu consulta programada está configurada para ejecutarse every day 23:00 hora del Pacífico. Te faltan datos del 1, 2 y 3 de enero. Selecciona los siguientes períodos históricos (las fechas posteriores se seleccionan porque UTC tiene una fecha diferente a las 23:00 hora del Pacífico):

Start Time = 1/2/19
End Time = 1/5/19

Tu consulta se ejecuta con los parámetros run_date y run_time que corresponden a los siguientes horarios:

  • 1/2/19 09:00 UTC o 1/1/2019 23:00 hora del Pacífico
  • 1/3/19 09:00 UTC o 1/2/2019 23:00 hora del Pacífico
  • 1/4/19 09:00 UTC o 1/3/2019 23:00 hora del Pacífico

Después de configurar ejecuciones manuales, actualiza la página para verlas en la lista de ejecuciones.

IU clásica

Después de hacer clic en Agregar (Add) para guardar la consulta programada, se mostrarán los detalles de la consulta programada. Debajo de los detalles, haz clic en el botón Iniciar ejecuciones manuales (Start Manual Runs) para especificar un período histórico.

botón de inicio de ejecuciones manuales

Puedes refinar aún más el rango de fechas para tener una hora de inicio y término, o dejar los campos de hora como 00:00:00.

configurar fechas históricas

Ejemplo 1

Si tu consulta programada está configurada para ejecutarse every day 14:00 y aplicas el siguiente rango de fechas históricas:

Start Time = 2/21/2018 00:00:00 AM
End Time = 2/24/2018 00:00:00 AM

Tu consulta se ejecutará en los siguientes horarios:

  • 2/21/2018 14:00:00
  • 2/22/2018 14:00:00
  • 2/23/2018 14:00:00

Ejemplo 2

Si tu consulta programada está configurada para ejecutarse every fri at 01:05 y aplicas el siguiente rango de fechas históricas:

Start Time = 2/1/2018 00:00:00 (un jueves)
End Time = 2/24/2018 00:00:00 AM (también un jueves)

Tu consulta se ejecutará en los siguientes horarios:

  • 2/2/2018 01:05:00
  • 2/9/2018 01:05:00

Cuotas

Una consulta programada se ejecuta con las credenciales y el proyecto del creador, como si estuvieras ejecutando la consulta tú mismo. La consulta programada está sujeta a todas las Cuotas y límites de BigQuery en los trabajos de consulta.

Problemas conocidos y limitaciones

Regiones

Las consultas entre regiones no son compatibles y la tabla de destino para tu consulta programada debe estar en la misma región que los datos que se consultan. Consulta Ubicaciones de los conjuntos de datos para obtener más información sobre regiones y multirregiones.

Google Drive

Puedes consultar los datos de Google Drive en una consulta programada. Si programas una consulta existente, es posible que debas hacer clic en "Actualizar credenciales" (Update Credentials) en la pantalla de detalles de la consulta programada. Espera de 10 a 20 minutos para que se aplique el cambio. Es posible que debas borrar la caché de tu navegador. Las credenciales se actualizan automáticamente para las consultas programadas nuevas.

Actualizar credenciales

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

Enviar comentarios sobre...

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