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:

  1. En el proyecto PROJECT, crea una cuenta de servicio llamada snapshot-bot.
  2. Otorga a la cuenta de servicio snapshot-bot los permisos que necesita para tomar instantáneas de tablas de la tabla TABLE, que se encuentra en el conjunto de datos DATASET y almacena las instantáneas de tabla en el conjunto de datos BACKUP.
  3. Escribe una consulta que cree instantáneas mensuales de la tabla TABLE y las coloque en el conjunto de datos BACKUP. 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.
  4. 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

  1. En la consola de Google Cloud, ve a la página Cuentas de servicio.

    Ir a Cuentas de servicio

  2. Selecciona el proyecto PROJECT.

  3. Crea la cuenta de servicio snapshot-bot:

    1. Haga clic en Crear cuenta de servicio.

    2. En el campo Nombre de la cuenta de servicio, ingresa snapshot-bot.

    3. Haz clic en Crear y continuar.

  4. Otorga a la cuenta de servicio los permisos que necesita para ejecutar trabajos de BigQuery:

    1. En la sección Otorga a esta cuenta de servicio acceso al proyecto, selecciona el rol Usuario de BigQuery.

    2. 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:

  1. En la consola de Google Cloud, ve a la página Cuentas de servicio:

    Ir a Cuentas de servicio

  2. Selecciona el proyecto PROJECT.

  3. Haz clic en snapshot-bot@PROJECT.iam.gserviceaccount.com.

  4. 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:

  1. En la consola de Google Cloud, ve a la página Administrar recursos.

    Ir a Administrar recursos

  2. Haga clic en PROJECT.

  3. Haz clic en Mostrar panel de información.

  4. En la pestaña Permisos, expande el nodo Usuario de BigQuery.

  5. 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

  1. En la consola de Google Cloud, abre la página de BigQuery.

    Ir a BigQuery

  2. En el panel Explorador, expande el nodo de proyecto PROJECT.

  3. Expande el nodo del conjunto de datos DATASET.

  4. Selecciona la tabla TABLE.

  5. Haz clic en Compartir. Se abrirá el panel Compartir.

  6. Haz clic en Agregar principal. Se abrirá el panel Otorgar acceso.

  7. En Principales nuevas, ingresa la dirección de correo electrónico de la cuenta de servicio: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  8. En el menú desplegable Selecciona un rol, selecciona el rol BigQuery Data Editor.

  9. Haz clic en Guardar.

  10. 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.

  11. Haz clic en Cerrar.

bq

  1. En la consola de Google Cloud, activa Cloud Shell.

    Activa Cloud Shell.

  2. 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

  1. En la consola de Google Cloud, ve a la página de BigQuery.

    Ir a BigQuery

  2. En el panel Explorador, expande el nodo de proyecto PROJECT.

  3. Haz clic en el menú del nodo del conjunto de datos BACKUP y selecciona Abrir.

  4. Haz clic en Compartir conjunto de datos. Se abrirá el panel Permisos del conjunto de datos.

  5. En el campo Agregar miembros, ingresa la dirección de correo electrónico de la cuenta de servicio: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  6. En el menú desplegable Selecciona un rol, selecciona el rol BigQuery Data Owner.

  7. Haz clic en Agregar.

  8. 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.

  9. 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

  1. En la consola de Google Cloud, activa Cloud Shell.

    Activa Cloud Shell.

  2. 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;'

  3. 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 de current_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 usar current_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 de current_timestamp() por un motivo similar: el parámetro @run_time no es compatible con las consultas interactivas, pero current_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:

  1. 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

  2. Con el identificador en el campo name, ejecuta el siguiente comando bq 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

  1. En la consulta de Google Cloud, ve a la página Consultas programadas:

    Ir a Consultas programadas

  2. Haz clic en Instantáneas mensuales de la tabla TABLE.

  3. Haz clic en Configuración.

  4. 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

  1. En la consulta de Google Cloud, ve a la página Consultas programadas:

    Ir a Consultas programadas

  2. Haz clic en la descripción de la consulta, Instantáneas mensuales de la tabla TABLE.

  3. 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

  1. En la consola de Google Cloud, ve a la página de BigQuery:

    Ir a BigQuery

  2. En el panel Explorador (Explorer), abre el conjunto de datos BACKUP y verifica que se hayan creado las instantáneas TABLE_YYYYMMDD, en las que YYYYMMDD es el primer día de cada mes.

    Por ejemplo:

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

¿Qué sigue?