Create table snapshots with a scheduled query

This document describes how to create monthly snapshots of a table using a service account that runs a scheduled DDL query . The document steps you through the following example:

1. In the PROJECT project, create a service account named snapshot-bot .
2. Grant the snapshot-bot service account the permissions that it needs to take table snapshots of the TABLE table, which is located in the DATASET dataset, and store the table snapshots in the BACKUP dataset.
3. Write a query that creates monthly snapshots of the TABLE table and places them in the BACKUP dataset. Because you can't overwrite an existing table snapshot, the table snapshots must have unique names. To achieve this, the query appends the current date to the table snapshot names; for example, TABLE _20220521 . The table snapshots expire after 40 days.
4. Schedule the snapshot-bot service account to run the query on the first day of every month.

This document is intended for users who are familiar with BigQuery and BigQuery table snapshots .

Permissions and roles

This section describes the Identity and Access Management (IAM) permissions you need to create a service account and to schedule a query, and the predefined IAM roles that grant those permissions.

Permissions

To work with a service account, you need the following permissions:

To schedule a query, you need the following permission:

Roles

The predefined roles that provide the permissions that are required to work with a service account are as follows:

The predefined BigQuery roles that provide the permissions that are required to schedule a query are as follows:

Create the snapshot-bot service account

Follow these steps to create the snapshot-bot service account and grant it the permissions that it needs to run queries in the PROJECT project:

To verify that BigQuery created the service account with the permissions that you specified, follow these steps:

Grant permissions to the service account

This section describes how to grant the snapshot-bot service account the permissions it needs to create table snapshots of the DATASET . TABLE table in the BACKUP dataset.

Permission to take snapshots of the base table

To give the snapshot-bot service account the permissions that it needs to take snapshots of the DATASET . TABLE table, follow these steps:

Permission to create tables in the destination dataset

Give the snapshot-bot service account the permissions that it needs to create table snapshots in the BACKUP dataset as follows:

Your snapshot-bot service account now has the following IAM roles for the following resources:

These roles provide the permissions that the snapshot-bot service account needs to run queries that create table snapshots of the DATASET . TABLE table and place the table snapshots in the BACKUP dataset.

Write a multi-statement query

This section describes how to write a multi-statement query that creates a table snapshot of the DATASET . TABLE table by using the CREATE SNAPSHOT TABLE DDL statement . The snapshot is saved in the BACKUP dataset and it expires after one day.

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

Schedule the monthly query

Schedule your query to run at 5:00 AM on the first day of every month as follows:

The multi-statement query in the bq command-line tool command differs from the query you ran in Google Cloud console as follows:

• The bq command-line tool query uses @run_date instead of current_date() . In a scheduled query, the @run_date parameter contains the current date. But in an interactive query, the @run_date parameter is not supported. You can use current_date() instead of @run_date for testing an interactive query before you schedule it.
• The bq command-line tool query uses @run_time instead of current_timestamp() for a similar reason—the @run_time parameter is not supported in interactive queries, but current_timestamp() can be used instead of @run_time for testing the interactive query.
• The bq command-line tool query uses a slash and a double quote \" instead of a single quote ' because single quotes are used to enclose the query.

Configure the service account to run the scheduled query

The query is currently scheduled to run using your credentials. Update your scheduled query to run with the snapshot-bot service account credentials as follows:

1. Run the bq ls command to get the identity of the scheduled query job:

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

   The output looks similar to the following:

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

2. Using the identifier in the name field, run the following bq update command:

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

Cloud Shell confirms that the scheduled query has been successfully updated.

Check your work

This section describes how to verify that your query is scheduled correctly, how to see if there were any errors when your query ran, and how to verify that the monthly snapshots are being created.

View the scheduled query

To verify that BigQuery has scheduled your monthly table snapshots query, follow these steps:

View the scheduled query's run history

After the scheduled query has run, you can see whether it ran successfully as follows:

View the table snapshots

To verify that the table snapshots are being created, follow these steps:

What's next

• For more information about table snapshots, see Working with table snapshots .
• For more information about scheduling queries, see Scheduling queries .
• For more information about Google Cloud service accounts, see Service accounts .