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 :

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

Rôles

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

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

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 :

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

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 :

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 :

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

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 :

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 :

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 :

Afficher les instantanés de table

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

Étape suivante

• Pour en savoir plus sur les instantanés de table, consultez la page Utiliser des instantanés de table.
• Pour en savoir plus sur la planification des requêtes, consultez la page Planifier des requêtes.
• Pour en savoir plus sur les comptes de service Google Cloud , consultez Comptes de service.