Crea instantáneas de tablas con una consulta programada
En este documento, se describe cómo crear instantáneas mensuales de una tabla mediante una cuenta de servicio que ejecuta una consulta de DDL programada. En el documento, se explica el siguiente ejemplo:
- En el proyecto
PROJECT
, crea una cuenta de servicio llamadasnapshot-bot
. - Otorga a la cuenta de servicio
snapshot-bot
los permisos que necesita para tomar instantáneas de tablas de la tablaTABLE
, que se encuentra en el conjunto de datosDATASET
y almacena las instantáneas de tabla en el conjunto de datosBACKUP
. - Escribe una consulta que cree instantáneas mensuales de la tabla
TABLE
y las coloque en el conjunto de datosBACKUP
. Debido a que no puedes reemplazar una instantánea de tabla existente, las instantáneas de tabla deben tener nombres únicos. Para lograr esto, la consulta agrega la fecha actual a los nombres de las instantáneas de la tabla. por ejemplo,TABLE_20220521
. Las instantáneas de tabla vencen después de 40 días. - Programa la cuenta de servicio
snapshot-bot
para que ejecute la consulta el primer día de cada mes.
Este documento está dirigido a usuarios familiarizados con BigQuery y con las instantáneas de tabla de BigQuery.
Permisos y funciones
En esta sección, se describen losPermisos de administración de identidades y accesos (IAM) Debes crear una cuenta de servicio y programar una consulta, y lasfunciones predefinidas de IAM que otorguen esos permisos.
Permisos
Para trabajar con una cuenta de servicio, necesitas los siguientes permisos:
Permiso | Recurso | Tipo de recurso |
---|---|---|
iam.serviceAccounts.*
|
PROJECT |
Proyecto |
Para programar una consulta, necesitas el siguiente permiso:
Permiso | Recurso | Tipo de recurso |
---|---|---|
bigquery.jobs.create
|
PROJECT |
Proyecto |
Funciones
A continuación, se enumeran los roles predefinidos que proporcionan los permisos necesarios para trabajar con una cuenta de servicio:
Función | Recurso | Tipo de recurso |
---|---|---|
Cualquiera de las siguientes:roles/iam.serviceAccountAdmin roles/editor roles/owner
|
PROJECT |
Proyecto |
A continuación, se enumeran los roles predefinidos de BigQuery que proporcionan los permisos necesarios para programar una consulta:
Función | Recurso | Tipo de recurso |
---|---|---|
Cualquiera de las siguientes:roles/bigquery.user roles/bigquery.jobuser roles/bigquery.admin
|
PROJECT |
Proyecto |
Cree la cuenta de servicio snapshot-bot
:
Sigue estos pasos a fin de crear la cuenta de servicio snapshot-bot
y otorgarle los permisos que se necesitan para ejecutar consultas en el proyecto PROJECT
:
Console
En la consola de Google Cloud, ve a la página Cuentas de servicio.
Selecciona el proyecto
PROJECT
.Crea la cuenta de servicio
snapshot-bot
:Haga clic en Crear cuenta de servicio.
En el campo Nombre de la cuenta de servicio, ingresa snapshot-bot.
Haz clic en Crear y continuar.
Otorga a la cuenta de servicio los permisos que necesita para ejecutar trabajos de BigQuery:
En la sección Otorga a esta cuenta de servicio acceso al proyecto, selecciona el rol Usuario de BigQuery.
Haz clic en Listo.
BigQuery crea la cuenta de servicio con la dirección de correo electrónico snapshot-bot@PROJECT.iam.gserviceaccount.com
.
Para verificar que BigQuery haya creado la cuenta de servicio con los permisos que especificaste, sigue estos pasos:
Console
Verifica que BigQuery haya creado la cuenta de servicio:
En la consola de Google Cloud, ve a la página Cuentas de servicio:
Selecciona el proyecto
PROJECT
.Haz clic en snapshot-bot@PROJECT.iam.gserviceaccount.com.
Verifica que el mensaje Estado de la cuenta de servicio indique que tu cuenta de servicio está activa.
Verifica que BigQuery le haya otorgado a la cuenta de servicio el permiso que necesita para ejecutar consultas:
En la consola de Google Cloud, ve a la página Administrar recursos.
Haga clic en
PROJECT
.Haz clic en Mostrar panel de información.
En la pestaña Permisos, expande el nodo Usuario de BigQuery.
Verifica que tu cuenta de servicio snapshot-bot aparezca en la lista.
Otorga permisos a la cuenta de servicio.
En esta sección, se describe cómo otorgar a la cuenta de servicio snapshot-bot
los permisos que necesita para crear instantáneas de la tabla DATASET.TABLE
en el conjunto de datos BACKUP
.
Permiso para tomar instantáneas de la tabla base
A fin de otorgar a la cuenta de servicio snapshot-bot
los permisos que necesita para tomar instantáneas de la tabla DATASET.TABLE
, sigue estos pasos:
Console
En la consola de Google Cloud, abre la página de BigQuery.
En el panel Explorador, expande el nodo de proyecto
PROJECT
.Expande el nodo del conjunto de datos DATASET.
Selecciona la tabla TABLE.
Haz clic en Compartir. Se abrirá el panel Compartir.
Haz clic en Agregar principal. Se abrirá el panel Otorgar acceso.
En Principales nuevas, ingresa la dirección de correo electrónico de la cuenta de servicio: snapshot-bot@PROJECT.iam.gserviceaccount.com.
En el menú desplegable Selecciona un rol, selecciona el rol BigQuery Data Editor.
Haz clic en Guardar.
En el panel Compartir, expande el nodo Editor de datos de BigQuery y verifica que enumere la cuenta de servicio snapshot-bot@PROJECT.iam.gserviceaccount.com.
Haz clic en Cerrar.
bq
En la consola de Google Cloud, activa Cloud Shell.
Ingresa el siguiente comando
bq add-iam-policy-binding
:bq add-iam-policy-binding \ --member=serviceAccount:snapshot-bot@PROJECT.iam.gserviceaccount.com \ --role=roles/bigquery.dataEditor DATASET.TABLE
BigQuery confirma que se agregó la nueva vinculación de política.
Permiso para crear tablas en el conjunto de datos de destino
Otorga a la cuenta de servicio snapshot-bot
los permisos que necesita para crear instantáneas de tabla en el conjunto de datos BACKUP
de la siguiente manera:
Console
En la consola de Google Cloud, ve a la página de BigQuery.
En el panel Explorador, expande el nodo de proyecto
PROJECT
.Haz clic en el menú del nodo del conjunto de datos BACKUP y selecciona Abrir.
Haz clic en Compartir conjunto de datos. Se abrirá el panel Permisos del conjunto de datos.
En el campo Agregar miembros, ingresa la dirección de correo electrónico de la cuenta de servicio: snapshot-bot@PROJECT.iam.gserviceaccount.com.
En el menú desplegable Selecciona un rol, selecciona el rol BigQuery Data Owner.
Haz clic en Agregar.
En el panel Permisos del conjunto de datos, verifica que la cuenta de servicio snapshot-bot@PROJECT.iam.gserviceaccount.com aparezca en el nodo BigQuery Data Owner.
Haz clic en Listo.
Tu cuenta de servicio snapshot-bot
ahora tiene las siguientes funciones de IAM para los siguientes recursos:
Función | Recurso | Tipo de recurso | Objetivo |
---|---|---|---|
Editor de datos de BigQuery | PROJECT:DATASET.TABLE |
Tabla | Toma instantáneas de la tabla TABLE . |
Propietario de datos de BigQuery | PROJECT:BACKUP |
Conjunto de datos | Crea y borra instantáneas de tablas en el conjunto de datos BACKUP . |
Usuario de BigQuery | PROJECT |
Proyecto | Ejecuta la consulta programada que crea las instantáneas de tabla. |
Estos roles proporcionan los permisos que la cuenta de servicio snapshot-bot
necesita para ejecutar consultas que crean instantáneas de la tabla DATASET.TABLE
y colocan las instantáneas de tabla en el conjunto de datos BACKUP
.
Escribe una consulta de varias instrucciones
En esta sección, se describe cómo escribir una consulta de varias declaraciones que crea una instantánea de la tabla DATASET.TABLE
mediante la declaración DDL CREATE SNAPSHOT TABLE
.
La instantánea se guarda en el conjunto de datos BACKUP
y vence después de un día.
-- Declare variables DECLARE snapshot_name STRING; DECLARE expiration TIMESTAMP; DECLARE query STRING; -- Set variables SET expiration = DATE_ADD(current_timestamp(), INTERVAL 1 DAY); SET snapshot_name = CONCAT( "BACKUP.TABLE_", FORMAT_DATETIME('%Y%m%d', current_date())); -- Construct the query to create the snapshot SET query = CONCAT( "CREATE SNAPSHOT TABLE ", snapshot_name, " CLONE mydataset.mytable OPTIONS(expiration_timestamp = TIMESTAMP '", expiration, "');"); -- Run the query EXECUTE IMMEDIATE query;
Programa la consulta mensual
Programa tu consulta para que se ejecute a las 5:00 a.m. el primer día de cada mes de la siguiente manera:
bq
En la consola de Google Cloud, activa Cloud Shell.
Ingresa el siguiente comando
bq query
:bq query --use_legacy_sql=false --display_name="Monthly snapshots of the TABLE table" \ --location="us" --schedule="1 of month 05:00" \ --project_id=PROJECT \ 'DECLARE snapshot_name STRING; DECLARE expiration TIMESTAMP; DECLARE query STRING; SET expiration = DATE_ADD(@run_time, INTERVAL 40 DAY); SET snapshot_name = CONCAT("BACKUP.TABLE_", FORMAT_DATETIME("%Y%m%d", @run_date)); SET query = CONCAT("CREATE SNAPSHOT TABLE ", snapshot_name, " CLONE PROJECT.DATASET.TABLE OPTIONS(expiration_timestamp=TIMESTAMP \"", expiration, "\");"); EXECUTE IMMEDIATE query;'
BigQuery programa la consulta.
La consulta de varias instrucciones en el comando de la herramienta de línea de comandos de bq difiere de la consulta que ejecutaste en la consulta de Google Cloud de la siguiente manera:
- La consulta de la herramienta de línea de comandos de bq usa
@run_date
en lugar decurrent_date()
. En una consulta programada, el parámetro@run_date
contiene la fecha actual. Sin embargo, en una consulta interactiva, el parámetro@run_date
no es compatible. Puedes usarcurrent_date()
en lugar de@run_date
para probar una consulta interactiva antes de programarla. - La consulta de la herramienta de línea de comandos de bq usa
@run_time
en lugar decurrent_timestamp()
por un motivo similar: el parámetro@run_time
no es compatible con las consultas interactivas, perocurrent_timestamp()
se puede usar en lugar de@run_time
para probar la consulta interactiva. - La consulta de la herramienta de línea de comandos de bq usa una barra y una comilla doble
\"
en lugar de una comilla simple'
porque se usan comillas simples para encerrar la consulta.
Configura la cuenta de servicio para ejecutar la consulta programada
La consulta está programada para ejecutarse con tus credenciales.
Actualiza tu consulta programada para que se ejecute con las credenciales de la cuenta de servicio snapshot-bot
de la siguiente manera:
Ejecuta el comando
bq ls
para obtener la identidad del trabajo de consulta programado:bq ls --transfer_config=true --transfer_location=us
El resultado es similar al siguiente:
name
displayName
dataSourceId
state
projects/12345/locations/us/transferConfigs/12345
Monthly snapshots of the TABLE table
scheduled_query
RUNNING
Con el identificador en el campo
name
, ejecuta el siguiente comandobq update
:bq update --transfer_config --update_credentials \ --service_account_name=snapshot-bot@PROJECT.iam.gserviceaccount.com \ projects/12345/locations/us/transferConfigs/12345
Cloud Shell confirma que la consulta programada se actualizó correctamente.
Revise su trabajo
En esta sección, se describe cómo verificar que tu consulta esté programada correctamente, cómo ver si hubo errores cuando se ejecutó tu consulta y cómo verificar que se creen las instantáneas mensuales.
Visualiza la consulta programada
Para verificar que BigQuery haya programado la consulta de instantáneas de tabla mensuales, sigue estos pasos:
Console
En la consulta de Google Cloud, ve a la página Consultas programadas:
Haz clic en Instantáneas mensuales de la tabla TABLE.
Haz clic en Configuración.
Verifica que la String de consulta contenga tu consulta y que tu consulta esté programada para ejecutarse el primer día de cada mes.
Visualiza el historial de ejecuciones de la consulta programada
Una vez que se ejecutó la consulta programada, puedes ver si hizo de manera correcta de la siguiente manera:
Console
En la consulta de Google Cloud, ve a la página Consultas programadas:
Haz clic en la descripción de la consulta, Instantáneas mensuales de la tabla TABLE.
Haz clic en Historial de ejecuciones.
Puedes ver la fecha y hora en que se ejecutó la consulta, si la ejecución se realizó de forma correcta y, si no, qué errores ocurrieron. Para ver más detalles sobre una ejecución en particular, haz clic en su fila en la tabla Historial de ejecuciones. En el panel Detalles de ejecución, se muestran detalles adicionales.
Visualiza las instantáneas de tabla
Para verificar que las instantáneas de tabla se estén creando, sigue estos pasos:
Console
En la consola de Google Cloud, ve a la página de BigQuery:
En el panel Explorador (Explorer), abre el conjunto de datos
BACKUP
y verifica que se hayan creado las instantáneasTABLE_YYYYMMDD
, en las queYYYYMMDD
es el primer día de cada mes.Por ejemplo:
TABLE_20220601
TABLE_20220701
TABLE_20220801
¿Qué sigue?
- Para obtener más información sobre las instantáneas de tablas, consulta Trabaja con instantáneas de tablas.
- Para obtener más información sobre cómo programar consultas, visita Programa consultas.
- Para obtener más información sobre las cuentas de servicio de Google Cloud, consulta la sección Cuentas de servicio.