Créer des instantanés de table avec une requête programmée

Ce document explique comment créer des instantanés mensuels d'une table à l'aide d'un compte de service qui exécute une requête LDD programmée. Ce document vous présente l'exemple suivant :

  1. Dans le projet PROJECT, créez un compte de service nommé snapshot-bot.
  2. Accordez au compte de service snapshot-bot les autorisations nécessaires pour prendre des instantanés de table de la table TABLE, qui se trouve dans l'ensemble de données DATASET et stockez les instantanés de table dans l'ensemble de données BACKUP.
  3. Écrivez une requête qui crée des instantanés mensuels de la table TABLE et les place dans l'ensemble de données BACKUP. Comme les instantanés de table ne peuvent pas être écrasés, les instantanés de table doivent porter des noms uniques. Pour ce faire, la requête ajoute la date actuelle aux noms des instantanés de la table. par exemple, TABLE_20220521. Les instantanés de la table expirent au bout de 40 jours.
  4. Planifiez le compte de service snapshot-bot pour exécuter la requête le premier jour de chaque mois.

Ce document est destiné aux utilisateurs qui connaissent déjà BigQuery et les instantanés de table BigQuery.

Autorisations et rôles

Cette section décrit laAutorisations IAM (Identity and Access Management) que vous devez avoir pour créer un compte de service et programmer une requête, et les Rôles IAM prédéfinis qui accordent ces autorisations.

Autorisations

Pour utiliser un compte de service, vous devez disposer des autorisations suivantes :

Autorisation Ressource Type de ressource
iam.serviceAccounts.* PROJECT Projet

Pour planifier une requête, vous devez disposer des autorisations suivantes :

Autorisation Ressource Type de ressource
bigquery.jobs.create PROJECT Projet

Rôles

Les rôles prédéfinis qui fournissent les autorisations requises pour utiliser un compte de service sont les suivants :

Rôle Ressource Type de ressource
Au choix :

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

Les rôles BigQuery prédéfinis qui fournissent les autorisations requises pour programmer une requête sont les suivants :

Rôle Ressource Type de ressource
Au choix :

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

Créez le compte de service snapshot-bot

Suivez les étapes ci-dessous pour créer le compte de service snapshot-bot et lui accorder les autorisations nécessaires pour exécuter des requêtes dans le projet PROJECT :

Console

  1. Dans Cloud Console, accédez à la page Comptes de service :

    Accéder à la page "Comptes de service"

  2. Sélectionnez le projet PROJECT.

  3. Créez le compte de service snapshot-bot :

    1. Cliquez sur Créer un compte de service.

    2. Dans le champ Nom du compte de service, saisissez snapshot-bot.

    3. Cliquez sur Créer et continuer.

  4. Accordez au compte de service les autorisations nécessaires pour exécuter des tâches BigQuery :

    1. Dans la section Autoriser ce compte de service à accéder au projet, sélectionnez le rôle Utilisateur BigQuery.

    2. Cliquez sur OK.

BigQuery crée le compte de service avec l'adresse e-mail snapshot-bot@PROJECT.iam.gserviceaccount.com.

Pour vérifier que BigQuery a créé le compte de service avec les autorisations que vous avez spécifiées, procédez comme suit :

Console

Vérifiez que BigQuery a créé le compte de service :

  1. Dans la console Google Cloud, accédez à la page Comptes de service :

    Accéder à la page "Comptes de service"

  2. Sélectionnez le projet PROJECT.

  3. Cliquez sur snapshot-bot@PROJECT.iam.gserviceaccount.com.

  4. Vérifiez que le message État du compte de service indique que votre compte de service est actif.

Vérifiez que BigQuery a accordé à votre compte de service l'autorisation dont il a besoin pour exécuter des requêtes :

  1. Dans la console Google Cloud, accédez à la page Gérer les ressources :

    Accéder à la page Gérer les ressources

  2. Cliquez sur PROJECT.

  3. Cliquez sur Afficher le panneau d'informations.

  4. Dans l'onglet Autorisations, développez le nœud Utilisateur BigQuery.

  5. Vérifiez que votre compte de service snapshot-bot est répertorié.

Accordez des autorisations au compte de service.

Cette section explique comment accorder au compte de service snapshot-bot les autorisations nécessaires pour créer des instantanés de table de la table DATASET.TABLE dans l'ensemble de données BACKUP.

Autorisation de prendre des instantanés de la table de base

Pour accorder au compte de service snapshot-bot les autorisations nécessaires pour prendre des instantanés de la table DATASET.TABLE, procédez comme suit :

Console

  1. Dans la console Google Cloud, ouvrez la page BigQuery.

    Accéder à BigQuery

  2. Dans le volet Explorer, développez le nœud du projet PROJECT.

  3. Développez le nœud d'ensemble de données DATASET.

  4. Sélectionnez la table TABLE.

  5. Cliquez sur Partager. Le volet Partager s'ouvre.

  6. Cliquez sur Ajouter un compte principal. Le volet Accorder l'accès s'affiche.

  7. Dans Nouveaux comptes principaux, saisissez l'adresse e-mail du compte de service: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  8. Dans le menu déroulant Sélectionner un rôle, sélectionnez le rôle Éditeur de données BigQuery.

  9. Cliquez sur Enregistrer.

  10. Dans le volet Partager, développez le nœud Lecteur de données BigQuery et vérifiez que le compte de service snapshot-bot@PROJECT.iam.gserviceaccount.com est répertorié.

  11. Cliquez sur Fermer.

bq

  1. Dans Cloud Console, activez Cloud Shell :

    Activer Cloud Shell

  2. Saisissez la commande bq add-iam-policy-binding suivante :

    bq add-iam-policy-binding \
--member=serviceAccount:snapshot-bot@PROJECT.iam.gserviceaccount.com \
--role=roles/bigquery.dataEditor DATASET.TABLE

BigQuery confirme que la nouvelle liaison de stratégie a été ajoutée.

Autorisation de créer des tables dans l'ensemble de données de destination

Accordez au compte de service snapshot-bot les autorisations nécessaires pour créer des instantanés de table dans l'ensemble de données BACKUP, comme suit :

Console

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans le volet Explorer, développez le nœud du projet PROJECT.

  3. Cliquez sur le menu du nœud de l'ensemble de données BACKUP et sélectionnez Ouvrir.

  4. Cliquez sur Partager l'ensemble de données. Le volet Autorisations de l'ensemble de données s'ouvre.

  5. Dans le champ Ajouter des membres, saisissez l'adresse e-mail du compte de service : snapshot-bot@PROJECT.iam.gserviceaccount.com.

  6. Dans le menu déroulant Sélectionner un rôle, sélectionnez le rôle Propriétaire de données BigQuery.

  7. Cliquez sur Ajouter.

  8. Dans le volet Autorisations de l'ensemble de données, vérifiez que le compte de service snapshot-bot@PROJECT.iam.gserviceaccount.com est répertorié sous le nœud Propriétaire de données BigQuery.

  9. Cliquez sur OK.

Votre compte de service snapshot-bot dispose désormais des rôles IAM suivants pour les ressources suivantes :

Rôle Ressource Type de ressource Objectif
Éditeur de données BigQuery PROJECT:DATASET.TABLE Table Réaliser des instantanés de la table TABLE.
Propriétaire de données BigQuery PROJECT:BACKUP Ensemble de données Créer et supprimer des instantanés de table dans l'ensemble de données BACKUP.
Utilisateur BigQuery PROJECT Projet Exécuter la requête programmée qui crée les instantanés de table.

Ces rôles fournissent les autorisations nécessaires au compte de service snapshot-bot pour exécuter des requêtes qui créent des instantanés de table de la table DATASET.TABLE et les placent dans l'ensemble de données BACKUP.

Écrire une requête à plusieurs instructions

Cette section décrit comment écrire une requête à plusieurs instructions qui crée un instantané de table de la table DATASET.TABLE à l'aide de l'instruction LDD CREATE SNAPSHOT TABLE. L'instantané est enregistré dans l'ensemble de données BACKUP et expire au bout d'un jour.

-- 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;

Planifier la requête mensuelle

Planifiez l'exécution de votre requête à 5h00 le premier jour de chaque mois comme suit :

bq

  1. Dans Cloud Console, activez Cloud Shell :

    Activer Cloud Shell

  2. Saisissez la commande bq query suivante :

    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 planifie la requête.

La requête à plusieurs instructions de la commande de l'outil de ligne de commande bq diffère de la requête que vous avez exécutée dans la console Google Cloud comme suit :

  • La requête de l'outil de ligne de commande bq utilise @run_date au lieu de current_date(). Dans une requête programmée, le paramètre @run_date contient la date actuelle. Toutefois, dans une requête interactive, le paramètre @run_date n'est pas accepté. Vous pouvez utiliser current_date() au lieu de @run_date pour tester une requête interactive avant de la planifier.
  • La requête de l'outil de ligne de commande bq utilise @run_time au lieu de current_timestamp() pour une raison semblable : le paramètre @run_time n'est pas compatible avec les requêtes interactives, mais current_timestamp() peut être utilisé au lieu de @run_time pour tester la requête interactive.
  • La requête de l'outil de ligne de commande bq utilise une barre oblique et un guillemet double \" au lieu d'un guillemet simple ', car les guillemets simples sont utilisés pour délimiter la requête.

Configurer le compte de service pour exécuter la requête programmée

La requête est actuellement programmée pour s'exécuter en utilisant vos identifiants. Mettez à jour votre requête programmée pour qu'elle s'exécute avec les identifiants du compte de service snapshot-bot comme suit :

  1. Exécutez la commande bq ls pour obtenir l'identité de la tâche de requête programmée :

    bq ls --transfer_config=true --transfer_location=us

    La sortie ressemble à ceci :

    name displayName dataSourceId state
    projects/12345/locations/us/transferConfigs/12345 Monthly snapshots of the TABLE table scheduled_query RUNNING

  2. En utilisant l'identifiant figurant dans le champ name, exécutez la commande bq update suivante :

    bq update --transfer_config --update_credentials \
--service_account_name=snapshot-bot@PROJECT.iam.gserviceaccount.com \
projects/12345/locations/us/transferConfigs/12345

Cloud Shell confirme que la requête programmée a bien été mise à jour.

Vérifiez votre travail

Cette section explique comment vérifier que votre requête est correctement planifiée, si des erreurs se sont produites lors de son exécution et comment vérifier que des instantanés mensuels ont bien été créés.

Afficher la requête programmée

Pour vérifier que BigQuery a programmé votre requête d'instantanés de table mensuelle, procédez comme suit :

Console

  1. Dans la console Google Cloud, accédez à la page Requêtes programmées :

    Accéder aux requêtes programmées

  2. Cliquez sur Instantanés mensuels de la table TABLE.

  3. Cliquez sur Configuration

  4. Vérifiez que la chaîne de requête contient votre requête et que celle-ci est programmée pour s'exécuter le premier jour de chaque mois.

Afficher l'historique d'exécution d'une requête programmée

Une fois la requête programmée exécutée, vous pouvez voir si elle s'est bien exécutée comme suit :

Console

  1. Dans la console Google Cloud, accédez à la page Requêtes programmées :

    Accéder aux requêtes programmées

  2. Cliquez sur la description de la requête, Instantanés mensuels de la table TABLE.

  3. Cliquez sur Historique d'exécution.

Vous pouvez voir la date et l'heure d'exécution de la requête, si l'exécution a abouti, et si non, quelles erreurs se sont produites. Pour afficher plus de détails sur une exécution particulière, cliquez sur sa ligne dans le tableau Run history (Historique d'exécution). Le volet Détails de l'exécution affiche des détails supplémentaires.

Afficher les instantanés de table

Pour vérifier que les instantanés de table sont bien créés, procédez comme suit :

Console

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans le volet Explorer, ouvrez l'ensemble de données BACKUP et vérifiez que les instantanés TABLE_YYYYMMDD ont été créés, où YYYYMMDD correspond au premier jour de chaque mois. .

    Exemple :

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

Étape suivante