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 ti guiderà attraverso il seguente esempio:
- Nel progetto
PROJECT
, crea un account di servizio denominatosnapshot-bot
. - Concedi all'account di servizio
snapshot-bot
le autorizzazioni necessarie per acquisire gli snapshot delle tabelle della tabellaTABLE
, che si trova nel set di datiDATASET
, e archiviare gli snapshot della tabella nel set di datiBACKUP
. - Scrivi una query che crei snapshot mensili della tabella
TABLE
e li posizioni nel set di datiBACKUP
. Poiché non puoi sovrascrivere uno snapshot di tabella esistente, gli snapshot della tabella devono avere nomi univoci. A questo scopo, la query aggiunge la data corrente ai nomi degli snapshot delle tabelle, ad esempioTABLE_20220521
. Gli snapshot delle tabelle scadono dopo 40 giorni. - Pianifica l'esecuzione della query l'account di servizio
snapshot-bot
il primo giorno di ogni mese.
Questo documento è destinato agli utenti che hanno familiarità con BigQuery e gli snapshot delle tabelle BigQuery.
Autorizzazioni e ruoli
Questa sezione descrive le autorizzazioni IAM (Identity and Access Management) necessarie per creare un account di servizio e per 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
Di seguito sono riportati i ruoli predefiniti che forniscono le autorizzazioni necessarie per lavorare con un account di servizio:
Role | Risorsa | Tipo di risorsa |
---|---|---|
Uno dei seguenti:roles/iam.serviceAccountAdmin roles/editor roles/owner
|
PROJECT |
progetto |
Di seguito sono riportati i ruoli predefiniti di BigQuery che forniscono le autorizzazioni necessarie per pianificare una query:
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 l'account di servizio snapshot-bot
e concedergli le autorizzazioni necessarie per eseguire le query nel progetto PROJECT
:
Console
Nella console Google Cloud, vai alla pagina Account di servizio:
Seleziona il progetto
PROJECT
.Crea l'account di servizio
snapshot-bot
:Fai clic su Crea account di servizio.
Nel campo Nome account di servizio, inserisci snapshot-bot.
Fai clic su Crea e continua.
Concedi all'account di servizio le autorizzazioni necessarie per eseguire i job BigQuery:
Nella sezione Concedi a questo account di servizio l'accesso al progetto, seleziona il ruolo Utente BigQuery.
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 con le autorizzazioni specificate, segui questi passaggi:
Console
Verifica che BigQuery abbia creato l'account di servizio:
Nella console Google Cloud, vai alla pagina Account di servizio:
Seleziona il progetto
PROJECT
.Fai clic su snapshot-bot@PROJECT.iam.gserviceaccount.com.
Verifica che il messaggio Stato account di servizio indichi che il tuo account di servizio è attivo.
Verifica che BigQuery abbia concesso al tuo account di servizio l'autorizzazione necessaria per eseguire le query:
Nella console Google Cloud, vai alla pagina Gestisci risorse:
Fai clic su
PROJECT
.Fai clic su Mostra riquadro informazioni.
Nella scheda Autorizzazioni, espandi il nodo Utente BigQuery.
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
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 per acquisire snapshot della tabella DATASET.TABLE
, segui questi passaggi:
Console
Nella console Google Cloud, apri la pagina BigQuery.
Nel riquadro Explorer, espandi il nodo del progetto
PROJECT
.Espandi il nodo del set di dati DATASET.
Seleziona la tabella TABLE.
Fai clic su Condividi. Si apre il riquadro Condividi.
Fai clic su Aggiungi entità. Viene visualizzato il riquadro Concedi l'accesso.
In Nuove entità, inserisci l'indirizzo email dell'account di servizio: snapshot-bot@PROJECT.iam.gserviceaccount.com.
Nel menu a discesa Seleziona un ruolo, seleziona il ruolo Editor dati BigQuery.
Fai clic su Salva.
Nel riquadro Condividi, espandi il nodo Editor dati BigQuery e verifica che l'account di servizio snapshot-bot@PROJECT.iam.gserviceaccount.com sia elencato.
Fai clic su Chiudi.
bq
Nella console Google Cloud, attiva Cloud Shell:
Inserisci il seguente 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 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 snapshot della tabella nel set di dati BACKUP
, come segue:
Console
Nella console Google Cloud, vai alla pagina BigQuery.
Nel riquadro Explorer, espandi il nodo del progetto
PROJECT
.Fai clic sul menu per il nodo del set di dati BACKUP e seleziona Apri.
Fai clic su Condividi set di dati. Viene visualizzato il riquadro Autorizzazioni set di dati.
Nel campo Aggiungi membri, inserisci l'indirizzo email dell'account di servizio: snapshot-bot@PROJECT.iam.gserviceaccount.com.
Nel menu a discesa Seleziona un ruolo, seleziona il ruolo Proprietario dati BigQuery.
Fai clic su Aggiungi.
Nel riquadro Autorizzazioni set di dati, verifica che l'account di servizio snapshot-bot@PROJECT.iam.gserviceaccount.com sia elencato sotto il nodo Proprietario dati BigQuery.
Fai clic su Fine.
Il tuo account di servizio snapshot-bot
ora dispone dei seguenti ruoli IAM 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 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 all'account di servizio snapshot-bot
per eseguire query che creano snapshot della tabella DATASET.TABLE
e posizionarli nel set di dati BACKUP
.
Scrivere una query con più istruzioni
Questa sezione descrive come scrivere una query con più istruzioni che crei 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 05:00 del primo giorno di ogni mese nel seguente modo:
bq
Nella console Google Cloud, attiva Cloud Shell:
Inserisci il seguente 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;'
BigQuery pianifica la query.
La query con più istruzioni nel comando dello strumento a riga di comando bq è diversa da quella eseguita nella console Google Cloud, come indicato di seguito:
- 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 utilizzarecurrent_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 puoi usarecurrent_timestamp()
al posto di@run_time
per testare la query interattiva. - La query dello strumento a riga di comando bq utilizza una barra e due virgolette
\"
anziché una virgola singola'
perché la query viene racchiusa tra virgolette singole.
Configura l'account di servizio per eseguire la query pianificata
L'esecuzione della query è attualmente pianificata con le tue credenziali.
Aggiorna la query pianificata in modo che venga eseguita con le credenziali dell'account di servizio snapshot-bot
, come segue:
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
Utilizzando l'identificatore nel campo
name
, esegui il seguente comandobq 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 verificare se si sono verificati errori durante l'esecuzione della query e come verificare che gli snapshot mensili vengano creati.
Visualizza la query pianificata
Per verificare che BigQuery abbia pianificato la query mensile degli snapshot della tabella, segui questi passaggi:
Console
Nella console Google Cloud, vai alla pagina Query pianificate:
Fai clic su Istantanee mensili della tabella TABLE.
Fai clic su Configurazione.
Verifica che la stringa di query contenga la query e che sia pianificata per l'esecuzione il primo giorno di ogni mese.
Visualizzare la cronologia delle esecuzioni della query pianificata
Dopo l'esecuzione della query pianificata, puoi verificare se è stata eseguita correttamente nel seguente modo:
Console
Nella console Google Cloud, vai alla pagina Query pianificate:
Fai clic sulla descrizione della query Istantanee mensili della tabella TABLE.
Fai clic su Esegui cronologia.
Puoi visualizzare la data e l'ora in cui è stata eseguita la query, se l'esecuzione è riuscita e, in caso negativo, quali errori si sono verificati. Per ulteriori dettagli su una determinata esecuzione, fai clic sulla relativa riga nella tabella Cronologia esecuzioni. Il riquadro Dettagli esecuzione mostra ulteriori dettagli.
Visualizza gli snapshot delle tabelle
Per verificare che gli snapshot delle tabelle vengano creati, segui questi passaggi:
Console
Nella console Google Cloud, vai alla pagina BigQuery:
Nel riquadro Explorer, apri il set di dati
BACKUP
e verifica che siano stati creati gli snapshotTABLE_YYYYMMDD
, doveYYYYMMDD
è il primo giorno di ogni mese.Ad esempio:
TABLE_20220601
TABLE_20220701
TABLE_20220801
Passaggi successivi
- Per ulteriori informazioni sugli snapshot delle tabelle, consulta Utilizzo degli snapshot delle tabelle.
- Per ulteriori informazioni sulla pianificazione delle query, consulta Programmazione delle query.
- Per ulteriori informazioni sugli account di servizio Google Cloud, consulta Account di servizio.