Crea 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 pianificazione Query DDL. Il documento ti guida nell'esempio seguente:

  1. In PROJECT creare un account di servizio denominato snapshot-bot.
  2. Concedi all'account di servizio snapshot-bot le autorizzazioni necessarie prendere snapshot di tabelle della tabella TABLE, ovvero situata nel set di dati DATASET e archiviare gli snapshot della tabella BACKUP.
  3. Scrivi una query che crei snapshot mensili di la tabella TABLE e le inserisce nella set di dati BACKUP. Perché non puoi sovrascrivere una tabella esistente snapshot, gli snapshot delle tabelle devono avere nomi univoci. Per ottenere questo risultato, 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. Pianifica l'account di servizio snapshot-bot in modo che esegua la query il primo giorno di ogni mese.

Questo documento è rivolto agli utenti che conoscono bene BigQuery e Snapshot delle tabelle BigQuery.

Autorizzazioni e ruoli

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

Autorizzazioni

Per utilizzare 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 di 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 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 l'account di servizio snapshot-bot

Segui questi passaggi per creare snapshot-bot account di servizio e assegnagli il autorizzazioni necessarie per eseguire query nel progetto PROJECT:

Console

  1. Nella console Google Cloud, vai alla pagina Account di servizio:

    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 account di servizio, inserisci snapshot-bot.

    3. Fai clic su Crea e continua.

  4. Concedi all'account di servizio le autorizzazioni necessarie per l'esecuzione Job BigQuery:

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

    2. Fai clic su Fine.

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

Per verificare che BigQuery abbia creato l'account di servizio le autorizzazioni specificate, segui questi passaggi:

Console

Verifica che BigQuery abbia creato l'account di servizio:

  1. Nella console Google Cloud, vai alla pagina Account di servizio:

    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 dell'account di servizio indichi che le tue che l'account di servizio sia attivo.

Verifica che BigQuery abbia concesso al tuo account di servizio la l'autorizzazione necessaria per eseguire 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 la sezione Utente BigQuery. nodo.

  5. Verifica che il tuo account di servizio snapshot-bot sia presente nell'elenco.

Concedi le autorizzazioni all'account di servizio

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

Autorizzazione per creare snapshot della tabella di base

Per concedere all'account di servizio snapshot-bot le autorizzazioni necessarie 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 Explorer, espandi il nodo del progetto PROJECT.

  3. Espandi il nodo del set di dati DATASET.

  4. Seleziona la tabella TABLE.

  5. Fai clic su Condividi. Si apre il riquadro Condividi.

  6. Fai clic su Aggiungi entità. Si apre il riquadro Concedi l'accesso.

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

  8. Nel menu a discesa Seleziona un ruolo, seleziona Editor dati BigQuery ruolo.

  9. Fai clic su Salva.

  10. Nel riquadro Condividi, espandi l'editor dati BigQuery e verifica che snapshot-bot@PROJECT.iam.gserviceaccount.com con un account di servizio.

  11. Fai clic su Chiudi.

bq

  1. Nella console Google Cloud, attiva Cloud Shell:

    Attiva Cloud Shell

  2. Inserisci quanto segue 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 la nuova associazione di criteri è stata aggiunta.

Autorizzazione per creare tabelle nel set di dati di destinazione

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

Console

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

    Vai a BigQuery

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

  3. Fai clic sul menu relativo al nodo del set di dati BACKUP e seleziona Apri.

  4. Fai clic su Condividi set di dati. Si apre il riquadro Autorizzazioni per il set di dati.

  5. Nel campo Aggiungi membri, inserisci l'indirizzo email dell'account di servizio: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  6. Nel menu a discesa Seleziona un ruolo, seleziona Proprietario dati BigQuery ruolo.

  7. Fai clic su Aggiungi.

  8. Nel riquadro Autorizzazioni per il set di dati, verifica che il L'account di servizio snapshot-bot@PROJECT.iam.gserviceaccount.com è elencato sotto il nodo Proprietario dati BigQuery.

  9. Fai clic su Fine.

Il tuo account di servizio snapshot-bot ora ha il seguente IAM ruoli per le seguenti risorse:

Ruolo Risorsa Tipo di risorsa Finalità
Editor dati BigQuery PROJECT:DATASET.TABLE Tabella Acquisisci snapshot della tabella TABLE.
Proprietario dati BigQuery PROJECT:BACKUP Set di dati Crea ed elimina gli 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 che l'account di servizio snapshot-bot deve eseguire query che creano snapshot della tabella DATASET.TABLE tavola e posizionare gli snapshot della tabella nel set di dati BACKUP.

Scrivi una query con più dichiarazioni

Questa sezione descrive come scrivere un query multi-istruzione che crea uno snapshot della tabella di DATASET.TABLE utilizzando la classe CREATE SNAPSHOT TABLE Istruzione 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 05:00 del giorno il primo giorno di ogni mese come segue:

bq

  1. Nella console Google Cloud, attiva Cloud Shell:

    Attiva Cloud Shell

  2. Inserisci quanto segue 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 a più istruzioni nel comando dello strumento a riga di comando bq è diversa dalla query è stata eseguita nella console Google Cloud nel modo seguente:

  • La query dello strumento a riga di comando bq utilizza @run_date anziché current_date(). In un query pianificata, il parametro @run_date contiene data corrente. Ma in una query interattiva, il parametro @run_date non è supportati. Puoi utilizzare current_date() anziché @run_date per i test 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 in query interattive, ma è possibile utilizzare current_timestamp() al posto di @run_time per testare il una query interattiva.
  • La query dello strumento a riga di comando bq utilizza una barra e le virgolette doppie \" anziché un virgolette singole ' perché vengono utilizzate virgolette singole per racchiudere la query.

Configura l'account di servizio per eseguire la query pianificata

Al momento l'esecuzione della query è pianificata utilizzando le tue credenziali. Aggiorna la query pianificata in modo da eseguirla con l'account di servizio snapshot-bot le credenziali 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 questo comando: 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.

Controlla il tuo lavoro

Questa sezione descrive come verificare che la query sia pianificata correttamente. come vedere se ci sono stati errori al momento dell'esecuzione della query e come verificare gli snapshot mensili vengono creati.

Visualizzare la query pianificata

Per verificare che BigQuery abbia pianificato la tua tabella mensile usa gli snapshot, segui questi passaggi:

Console

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

    Vai a Query programmate

  2. Fai clic su Istantanee mensili della tabella TABLE.

  3. Fai clic su Configurazione.

  4. Verifica che la stringa query contenga la query e che quest'ultima è programmata per essere eseguita il primo giorno di ogni mese.

Visualizzare la cronologia delle esecuzioni della query pianificata

Una volta eseguita la query pianificata, puoi vedere se è stata eseguita correttamente che segue:

Console

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

    Vai a Query programmate

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

  3. Fai clic su Cronologia esecuzioni.

Puoi visualizzare la data e l'ora in cui è stata eseguita la query, se quest'ultima è è riuscita e, in caso negativo, quali errori si sono verificati. Per visualizzare ulteriori dettagli su un una determinata esecuzione, fai clic sulla riga corrispondente nella tabella Cronologia esecuzioni. La Il riquadro Dettagli esecuzione mostra ulteriori dettagli.

Visualizza gli snapshot della tabella

Per verificare che gli snapshot delle tabelle vengano creati, segui questi passaggi:

Console

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

    Vai a BigQuery

  2. Nel riquadro Explorer, apri il set di dati BACKUP e verifica che sono stati creati gli snapshot TABLE_YYYYMMDD, dove YYYYMMDD è il primo giorno di ogni mese.

    Ad esempio:

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

Passaggi successivi