Crear capturas de tablas con una consulta programada

En este documento se describe cómo crear copias de una tabla cada mes mediante una cuenta de servicio que ejecuta una consulta DDL programada. En el documento se explica el siguiente ejemplo:

  1. En el proyecto PROJECT, crea una cuenta de servicio llamada snapshot-bot.
  2. Concede a la cuenta de servicio snapshot-bot los permisos que necesita para crear instantáneas de la tabla TABLE, que se encuentra en el conjunto de datos DATASET, y para almacenar las instantáneas de la 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. Como no puedes sobrescribir una captura de una tabla, las capturas de tablas deben tener nombres únicos. Para ello, la consulta añade la fecha actual a los nombres de las capturas de tablas. Por ejemplo, TABLE_20220521. Las capturas de tabla caducan al cabo 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 que conocen BigQuery y las instantáneas de tablas de BigQuery.

Permisos y roles

En esta sección se describen los permisos de Gestión de Identidades y Accesos (IAM) que necesitas para crear una cuenta de servicio y programar una consulta, así como los roles de IAM predefinidos que conceden esos permisos.

Permisos

Para trabajar con una cuenta de servicio, debes tener 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

Roles

Los roles predefinidos que proporcionan los permisos necesarios para trabajar con una cuenta de servicio son los siguientes:

Role Recurso Tipo de recurso
Cualquiera de las siguientes:

roles/iam.serviceAccountAdmin
roles/editor
roles/owner 		PROJECT Proyecto

Los roles predefinidos de BigQuery que proporcionan los permisos necesarios para programar una consulta son los siguientes:

Role Recurso Tipo de recurso
Cualquiera de las siguientes:

roles/bigquery.user
roles/bigquery.jobuser
roles/bigquery.admin` 		PROJECT Proyecto

Crea la cuenta de servicio snapshot-bot

Sigue estos pasos para crear la snapshot-bot cuenta de servicio y concederle los permisos que necesita para ejecutar consultas en el proyecto PROJECT:

Consola

  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 snapshot-botcuenta de servicio:

    1. Haz clic en Crear cuenta de servicio.

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

    3. Haz clic en Crear y continuar.

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

    1. En la sección Conceder 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 snapshot-bot@PROJECT.iam.gserviceaccount.com.

Para verificar que BigQuery ha creado la cuenta de servicio con los permisos que has especificado, sigue estos pasos:

Consola

Comprueba 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. Comprueba que el mensaje Estado de la cuenta de servicio indique que tu cuenta de servicio está activa.

Verifica que BigQuery haya concedido a tu cuenta de servicio el permiso que necesita para ejecutar consultas:

  1. En la consola, ve a la página Gestionar recursos: Google Cloud

    Ir a Gestionar recursos

  2. Haz clic en PROJECT.

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

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

  5. Comprueba que aparece tu cuenta de servicio snapshot-bot.

Conceder permisos a la cuenta de servicio

En esta sección se describe cómo conceder 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 hacer capturas de la tabla base

Para dar a la cuenta de servicio snapshot-bot los permisos que necesita para hacer copias de la tabla DATASET.TABLE, sigue estos pasos:

Consola

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

    Ir a BigQuery

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

  3. Despliega 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 Añadir principal. Se abrirá el panel Dar acceso.

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

  8. En el menú desplegable Seleccionar un rol, elija el rol Editor de datos de BigQuery.

  9. Haz clic en Guardar.

  10. En el panel Compartir, despliega el nodo Editor de datos de BigQuery y comprueba que aparece la cuenta de servicio snapshot-bot@PROJECT.iam.gserviceaccount.com.

  11. Haz clic en Cerrar.

bq

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

    Activar Cloud Shell

  2. Introduce 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 ha añadido el nuevo enlace de política.

Permiso para crear tablas en el conjunto de datos de destino

Da a la cuenta de servicio snapshot-bot los permisos que necesita para crear instantáneas de tablas en el conjunto de datos BACKUP de la siguiente manera:

Consola

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

    Ir a BigQuery

  2. En el panel Explorador, expande el nodo del 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 Añadir miembros, introduce la dirección de correo de la cuenta de servicio: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  6. En el menú desplegable Selecciona un rol, elige el rol Propietario de datos de BigQuery.

  7. Haz clic en Añadir.

  8. En el panel Permisos del conjunto de datos, compruebe que la cuenta de servicio snapshot-bot@PROJECT.iam.gserviceaccount.com aparece en el nodo Propietario de datos de BigQuery.

  9. Haz clic en Listo.

Tu cuenta de servicio snapshot-bot ahora tiene los siguientes roles de gestión de identidades y accesos para los siguientes recursos:

Rol Recurso Tipo de recurso Finalidad
Editor de datos de BigQuery PROJECT:DATASET.TABLE Tabla Haz capturas de la tabla TABLE.
Propietario de datos de BigQuery PROJECT:BACKUP Conjunto de datos Crea y elimina capturas de tablas en el conjunto de datos BACKUP.
Usuario de BigQuery PROJECT Proyecto Ejecuta la consulta programada que crea las capturas de la tabla.

Estos roles proporcionan los permisos que necesita la cuenta de servicio snapshot-bot para ejecutar consultas que creen copias de la tabla DATASET.TABLE y las coloquen en el conjunto de datos BACKUP.

Escribir una consulta con varias instrucciones

En esta sección se describe cómo escribir una consulta de varias instrucciones que crea una instantánea de la tabla DATASET.TABLE mediante la declaración de DDL CREATE SNAPSHOT TABLE. La instantánea se guarda en el conjunto de datos BACKUP y caduca al cabo 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;

Programar la consulta mensual

Programa la consulta para que se ejecute a las 5:00 del primer día de cada mes de la siguiente manera:

bq

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

    Activar Cloud Shell

  2. Introduce 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 del comando de la herramienta de línea de comandos bq se diferencia de la consulta que ejecutaste en la consola Google Cloud de la siguiente manera:

  • La consulta de la herramienta de línea de comandos 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, no se admite el parámetro @run_date. 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 bq usa @run_time en lugar de current_timestamp() por un motivo similar: el parámetro @run_time no se admite en las consultas interactivas, pero se puede usar current_timestamp() en lugar de @run_time para probar la consulta interactiva.
  • La consulta de la herramienta de línea de comandos bq usa una barra inclinada y comillas dobles \" en lugar de comillas simples ' porque estas últimas se usan para delimitar la consulta.

Configurar la cuenta de servicio para ejecutar la consulta programada

La consulta está programada para ejecutarse con tus credenciales. Actualiza la 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 de la tarea de consulta programada:

    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 del 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 ha actualizado correctamente.

Revisar tu trabajo

En esta sección se describe cómo verificar que tu consulta se ha programado correctamente, cómo comprobar si se han producido errores al ejecutarla y cómo verificar que se están creando las copias de seguridad mensuales.

Ver la consulta programada

Para comprobar que BigQuery ha programado la consulta de las copias de las tablas mensuales, sigue estos pasos:

Consola

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

    Ir a Consultas programadas

  2. Haz clic en Resumen mensual de la tabla TABLE.

  3. Haz clic en Configuración.

  4. Verifica que la cadena de consulta contenga tu consulta y que esta se haya programado para ejecutarse el primer día de cada mes.

Ver el historial de ejecuciones de una consulta programada

Una vez que se haya ejecutado la consulta programada, puedes comprobar si se ha ejecutado correctamente de la siguiente manera:

Consola

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

    Ir a Consultas programadas

  2. Haz clic en la descripción de la consulta, Resumen mensual de la tabla TABLE.

  3. Haz clic en Historial de ejecuciones.

Puedes ver la fecha y la hora en las que se ejecutó la consulta, si se completó correctamente y, si no, qué errores se produjeron. Para ver más detalles sobre una ejecución concreta, haz clic en su fila en la tabla Historial de ejecuciones. En el panel Detalles de la ejecución se muestra información adicional.

Ver las capturas de la tabla

Para verificar que se están creando las copias de la tabla, sigue estos pasos:

Consola

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

    Ir a BigQuery

  2. En el panel Explorador, abra el conjunto de datos BACKUP y compruebe que se han creado las TABLE_YYYYMMDD instantáneas, donde YYYYMMDD es el primer día de cada mes.

    Por ejemplo:

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

Siguientes pasos