Crea snapshot delle tabelle con una query programmata

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

  1. Nel progetto PROJECT, crea un account di servizio denominato snapshot-bot.
  2. Concedi al account di servizio snapshot-bot le autorizzazioni necessarie per acquisire snapshot della tabella della tabella TABLE, che si trova nel set di dati DATASET, e archiviare gli snapshot della tabella nel set di dati BACKUP.
  3. Scrivi una query che crea snapshot mensili della tabella TABLE e li inserisce nel set di dati BACKUP. Poiché non puoi sovrascrivere uno snapshot della tabella esistente, gli snapshot della tabella devono avere nomi univoci. A questo scopo, la query aggiunge la data corrente ai nomi degli snapshot della tabella, ad esempio TABLE_20220521. Gli snapshot delle tabelle scadono dopo 40 giorni.
  4. Pianifica l'esecuzione della query per il account di servizio snapshot-bot il primo giorno di ogni mese.

Questo documento è destinato agli utenti che hanno familiarità con BigQuery e con gli snapshot delle tabelle BigQuery.

Autorizzazioni e ruoli

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

Autorizzazioni

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

Autorizzazione Risorsa Tipo di risorsa
iam.serviceAccounts.* PROJECT Progetto

Per pianificare una query, devi disporre della seguente autorizzazione:

Autorizzazione Risorsa Tipo di risorsa
bigquery.jobs.create PROJECT Progetto

Ruoli

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

Role Risorsa Tipo di risorsa
Uno dei seguenti:

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

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

Role Risorsa Tipo di risorsa
Uno dei seguenti:

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

Crea il account di servizio snapshot-bot

Segui questi passaggi per creare il snapshot-bot service account e concedergli le autorizzazioni necessarie per eseguire query nel progetto PROJECT:

Console

  1. Nella console Google Cloud , vai alla pagina Service account:

    Vai ad Account di servizio

  2. Seleziona il progetto PROJECT.

  3. Crea l'account di servizio snapshot-bot:

    1. Fai clic su Crea account di servizio.

    2. Nel campo Nome service account, inserisci snapshot-bot.

    3. Fai clic su Crea e continua.

  4. Concedi al account di servizio le autorizzazioni necessarie per eseguire i job BigQuery:

    1. Nella sezione Concedi a questo account di servizio l'accesso al progetto, seleziona il ruolo Utente BigQuery.

    2. Fai clic su Fine.

BigQuery crea il account di servizio con l'indirizzo email snapshot-bot@PROJECT.iam.gserviceaccount.com.

Per verificare che BigQuery abbia creato il account di servizio con le autorizzazioni che hai specificato, segui questi passaggi:

Console

Verifica che BigQuery abbia creato il account di servizio:

  1. Nella console Google Cloud , vai alla pagina Service account:

    Vai a Service account

  2. Seleziona il progetto PROJECT.

  3. Fai clic su snapshot-bot@PROJECT.iam.gserviceaccount.com.

  4. Verifica che il messaggio Stato del service account indichi che il tuo account di serviziot è attivo.

Verifica che BigQuery abbia concesso al tuo account di servizio l'autorizzazione necessaria per eseguire le query:

  1. Nella console Google Cloud , vai alla pagina Gestisci risorse:

    Vai a Gestisci risorse

  2. Fai clic su PROJECT.

  3. Fai clic su Mostra riquadro informazioni.

  4. Nella scheda Autorizzazioni, espandi il nodo Utente BigQuery.

  5. Verifica che il account di servizio snapshot-bot sia elencato.

Concedi le autorizzazioni al service account

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

Autorizzazione per creare snapshot della tabella di base

Per concedere al account di servizio snapshot-bot le autorizzazioni necessarie per acquisire snapshot della tabella DATASET.TABLE, segui questi passaggi:

Console

  1. Nella console Google Cloud , apri la pagina BigQuery.

    Vai a BigQuery

  2. Nel riquadro a sinistra, fai clic su Explorer:

    Pulsante evidenziato per il riquadro Explorer.

    Se non vedi il riquadro a sinistra, fai clic su Espandi riquadro a sinistra per aprirlo.

  3. Nel riquadro Explorer, espandi il nodo del progetto PROJECT.

  4. Fai clic su Set di dati, quindi sul set di dati DATASET.

  5. Fai clic su Panoramica > Tabelle e poi sulla tabella TABLE.

  6. Fai clic su Condividi. Viene visualizzato il riquadro Condividi.

  7. Fai clic su Aggiungi entità. Viene visualizzato il riquadro Concedi l'accesso.

  8. In Nuove entità, inserisci l'indirizzo email del service account: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  9. Dal menu a discesa Seleziona un ruolo, seleziona il ruolo Editor dati BigQuery.

  10. Fai clic su Salva.

  11. Nel riquadro Condividi, espandi il nodo BigQuery Data Editor e verifica che sia elencato l'account di servizio snapshot-bot@PROJECT.iam.gserviceaccount.com.

  12. Fai clic su Chiudi.

bq

  1. Nella console Google Cloud , attiva Cloud Shell:

    Attiva Cloud Shell

  2. Inserisci questo 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 conferma che è stata aggiunta la nuova associazione di criteri.

Autorizzazione per creare tabelle nel set di dati di destinazione

Concedi al account di servizio snapshot-bot le autorizzazioni necessarie per creare snapshot delle tabelle nel set di dati BACKUP nel seguente modo:

Console

  1. Nella console Google Cloud , vai alla pagina BigQuery.

    Vai a BigQuery

  2. Nel riquadro a sinistra, fai clic su Explorer:

    Pulsante evidenziato per il riquadro Explorer.

  3. Nel riquadro Explorer, espandi il nodo del progetto PROJECT.

  4. Fai clic su Set di dati, quindi sul set di dati BACKUP.

  5. Fai clic su Condividi > Gestisci autorizzazioni. Viene visualizzato il riquadro delle autorizzazioni del set di dati.

  6. Fai clic su Aggiungi entità. Nel campo Nuove entità, inserisci l'indirizzo email del account di servizio: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  7. Dal menu a discesa Seleziona un ruolo, seleziona il ruolo Proprietario dati BigQuery.

  8. Fai clic su Salva.

  9. Nel riquadro delle autorizzazioni del set di dati, verifica che l'account di servizio snapshot-bot@PROJECT.iam.gserviceaccount.com sia elencato nel nodo Proprietario dati BigQuery.

  10. Fai clic su Chiudi.

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

Ruolo Risorsa Tipo di risorsa Finalità
BigQuery Data Editor PROJECT:DATASET.TABLE Tabella Scatta snapshot della tabella TABLE.
Proprietario dati BigQuery PROJECT:BACKUP Set di dati Crea ed elimina snapshot delle tabelle nel set di dati BACKUP.
Utente BigQuery PROJECT Progetto Esegui la query pianificata che crea gli snapshot della tabella.

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

Scrivere una query con più istruzioni

Questa sezione descrive come scrivere una query multi-istruzione che crea uno snapshot della tabella della tabella DATASET.TABLE utilizzando l'CREATE SNAPSHOT TABLEistruzione DDL. 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 5:00 del primo giorno di ogni mese nel seguente modo:

bq

  1. Nella console Google Cloud , attiva Cloud Shell:

    Attiva Cloud Shell

  2. Inserisci questo 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 pianifica la query.

La query con più istruzioni nel comando dello strumento a riga di comando bq differisce dalla query eseguita nella console Google Cloud nel seguente modo:

  • 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 le doppie virgolette \" anziché le virgolette singole ' perché queste ultime vengono utilizzate per racchiudere la query.

Configura il account di servizio per eseguire la query pianificata

La query è attualmente programmata per essere eseguita utilizzando le tue credenziali. Aggiorna la query pianificata in modo che venga eseguita con le credenziali del account di servizio snapshot-bot come segue:

  1. Esegui il comando bq ls per ottenere 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 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 conferma che la query pianificata è stata aggiornata correttamente.

Controllare il lavoro

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

Visualizza la query programmata

Per verificare che BigQuery abbia pianificato la query mensile degli snapshot della tabella:

Console

  1. Nella console Google Cloud , vai alla pagina Query programmate:

    Vai a Query programmate

  2. Fai clic su Snapshot mensili della tabella TABLE.

  3. Fai clic su Configurazione.

  4. Verifica che la stringa di query contenga la query e che l'esecuzione della query sia pianificata per il primo giorno di ogni mese.

Visualizzare la cronologia di esecuzione della query pianificata

Dopo l'esecuzione della query pianificata, puoi verificare se è stata eseguita correttamente nel seguente modo:

Console

  1. Nella console Google Cloud , vai alla pagina Query programmate:

    Vai a Query programmate

  2. Fai clic sulla descrizione della query, Snapshot mensili della tabella TABLE.

  3. Fai clic su Cronologia corse.

Puoi visualizzare la data e l'ora in cui è stata eseguita la query, se l'esecuzione è andata a buon fine e, in caso contrario, quali errori si sono verificati. Per visualizzare ulteriori dettagli su una corsa specifica, fai clic sulla relativa riga nella tabella Cronologia corse. Il riquadro Dettagli esecuzione mostra ulteriori dettagli.

Visualizza gli snapshot della tabella

Per verificare che vengano create le istantanee della tabella:

Console

  1. Nella console Google Cloud , vai alla pagina BigQuery:

    Vai a BigQuery

  2. Nel riquadro a sinistra, fai clic su Explorer:

    Pulsante evidenziato per il riquadro Explorer.

  3. Nel riquadro Spazio di esplorazione, apri il set di dati BACKUP e verifica che siano state create le istantanee TABLE_YYYYMMDD, dove YYYYMMDD è il primo giorno di ogni mese.

    Ad esempio:

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

Passaggi successivi