Creare snapshot delle tabelle con una query pianificata

Questo documento descrive come creare snapshot mensili di una tabella utilizzando un account di servizio che esegue una query DDL pianificata. Il documento illustra il seguente esempio:

1. Nel progetto PROJECT, crea un account di servizio denominato snapshot-bot.
2. Concedi all'account di servizio snapshot-bot le autorizzazioni necessarie per eseguire snapshot delle tabelle della tabella TABLE, che si trova nel set di dati DATASET, e archiviare gli snapshot delle tabelle nel set di dati BACKUP.
3. Scrivi una query che crei istantanee mensili della tabella TABLE e le inserisca nel set di dati BACKUP. Poiché non puoi sovrascrivere uno snapshot tabella esistente, gli snapshot tabella devono avere nomi univoci. Per farlo, la query aggiunge la data corrente ai nomi degli snapshot delle tabelle, ad esempio TABLE_20220521. Gli snapshot delle tabelle scadono dopo 40 giorni.
4. Programma l'account di servizio snapshot-bot in modo che esegua la query il primo giorno di ogni mese.

Questo documento è rivolto agli utenti che hanno dimestichezza con BigQuery e con gli snapshot delle tabelle BigQuery.

Autorizzazioni e ruoli

Questa sezione descrive le autorizzazioni di Identity and Access Management (IAM) di cui hai bisogno per creare un account di servizio e pianificare una query, nonché i ruoli IAM predefiniti che concedono queste autorizzazioni.

Autorizzazioni

Per utilizzare un account di servizio, devi disporre delle seguenti autorizzazioni:

Per pianificare una query, devi disporre della seguente autorizzazione:

Ruoli

I ruoli predefiniti che forniscono le autorizzazioni necessarie per lavorare con un account di servizio sono i seguenti:

I ruoli BigQuery predefiniti che forniscono le autorizzazioni necessarie per pianificare una query sono i seguenti:

Crea l'account di servizio snapshot-bot

Segui questi passaggi per creare l'account di servizio snapshot-bot e concedergli le autorizzazioni necessarie per eseguire query nel progetto PROJECT:

Per verificare che BigQuery abbia creato l'account di servizio con le autorizzazioni specificate:

Concedi le autorizzazioni al service account

Questa sezione descrive come concedere all'account di servizio snapshot-bot le autorizzazioni necessarie per creare istantanee della tabella DATASET.TABLE nel set di dati BACKUP.

Autorizzazione per acquisire snapshot della tabella di base

Per concedere all'account di servizio snapshot-bot le autorizzazioni necessarie per acquisire istantanee della tabella DATASET.TABLE:

Autorizzazione per creare tabelle nel set di dati di destinazione

Concedi all'account di servizio snapshot-bot le autorizzazioni necessarie per creare snapshot tabellari nel set di dati BACKUP come segue:

Il tuo account di servizio snapshot-bot ora dispone dei seguenti ruoli IAM per le seguenti risorse:

Questi ruoli forniscono le autorizzazioni necessarie all'account di servizio snapshot-bot per eseguire query che creano snapshot delle tabelle della tabella DATASET.TABLE e li inseriscono nel set di dati BACKUP.

Scrivere una query con più istruzioni

Questa sezione descrive come scrivere una query con più istruzioni che crea uno snapshot della tabella della tabella DATASET.TABLE utilizzando l'istruzione DDL CREATE SNAPSHOT TABLE. Lo snapshot viene salvato nel set di dati BACKUP e scade dopo un giorno.

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

Pianifica la query mensile

Pianifica l'esecuzione della query alle ore 05:00 del primo giorno di ogni mese nel seguente modo:

La query con più istruzioni nel comando dello strumento a riga di comando bq è diversa dalla query eseguita nella console Google Cloud come segue:

• La query dello strumento a riga di comando bq utilizza @run_date anziché current_date(). In una query pianificata, il parametro @run_date contiene la data corrente. Tuttavia, in una query interattiva, il parametro @run_date non è supportato. Puoi utilizzare current_date() anziché @run_date per testare una query interattiva prima di pianificarla.
• La query dello strumento a riga di comando bq utilizza @run_time anziché current_timestamp() per un motivo simile: il parametro @run_time non è supportato nelle query interattive, ma current_timestamp() può essere utilizzato al posto di @run_time per testare la query interattiva.
• La query dello strumento a riga di comando bq utilizza una barra e un'apostrofo \" anziché un'apostrofo ' perché le virgolette singole vengono utilizzate per racchiudere la query.

Configura l'account di servizio per eseguire la query pianificata

Al momento, la query è pianificata per l'esecuzione utilizzando le tue credenziali. Aggiorna la query pianificata in modo che venga eseguita con le credenziali dell'account di servizio snapshot-bot come segue:

1. Esegui il comando bq ls per recuperare l'identità del job di query pianificato:

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

   L'output è simile al seguente:

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

2. Utilizzando l'identificatore nel campo name, esegui il seguente bq update comando:

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

Cloud Shell conferma che la query pianificata è stata aggiornata correttamente.

Controlla il tuo lavoro

Questa sezione descrive come verificare che la query sia pianificata correttamente, come vedere se sono stati rilevati errori durante l'esecuzione della query e come verificare che gli snapshot mensili vengano creati.

Visualizza la query programmata

Per verificare che BigQuery abbia pianificato la query degli istantanei mensili della tabella:

Visualizzare la cronologia di esecuzione della query pianificata

Dopo l'esecuzione della query pianificata, puoi verificare se è andata a buon fine come segue:

Visualizza le istantanee della tabella

Per verificare che gli snapshot delle tabelle vengano creati:

Passaggi successivi

• Per ulteriori informazioni sugli snapshot delle tabelle, consulta Utilizzare gli snapshot delle tabelle.
• Per ulteriori informazioni sulla pianificazione delle query, consulta Pianificazione delle query.
• Per ulteriori informazioni sugli account di servizio Google Cloud, consulta Account di servizio.