Utilizzare la cronologia delle modifiche
La cronologia delle modifiche di BigQuery consente di tenere traccia delle modifiche apportate a una tabella BigQuery. La cronologia delle modifiche per una tabella viene esposta come funzione con valore di tabella (TVF) SQL che mostra particolari tipi di modifiche apportate durante l'intervallo di tempo specificato. Questa funzionalità consente di elaborare le modifiche incrementali apportate a una tabella. Comprendere quali modifiche sono state apportate a una tabella può aiutarti a svolgere operazioni come gestire in modo incrementale una replica della tabella al di fuori di BigQuery evitando copie costose.
Autorizzazioni obbligatorie
Per visualizzare la cronologia delle modifiche in una tabella, devi disporre dell'autorizzazione bigquery.tables.getData
per la tabella. I seguenti ruoli predefiniti di Identity and Access Management (IAM) includono questa autorizzazione:
roles/bigquery.dataViewer
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin
Se una tabella ha o ha avuto criteri di accesso a livello di riga, solo un amministratore della tabella può accedere ai dati storici per la tabella. L'autorizzazione bigquery.rowAccessPolicies.overrideTimeTravelRestrictions
è obbligatoria nella tabella ed è inclusa nel ruolo IAM roles/bigquery.admin
predefinito.
Se una tabella ha una sicurezza a livello di colonna, puoi visualizzare la cronologia delle modifiche solo nelle colonne a cui hai accesso.
APPENDS
TVF
Il valore TVF APPENDS
restituisce una tabella di tutte le righe aggiunte a una tabella per un determinato intervallo di tempo. Le seguenti operazioni aggiungono righe alla cronologia delle modifiche di APPENDS
:
CREATE TABLE
Istruzione DDLINSERT
Istruzione DMLMERGE
Istruzione DML- Caricamento di dati in BigQuery
- Importazione di flussi di dati
Sintassi
APPENDS( TABLE table, start_timestamp DEFAULT NULL, end_timestamp DEFAULT NULL)
table
: il nome della tabella BigQuery. Non può essere una vista, una sottoquery, una tabella esterna, una vista materializzata otabella con funzione carattere jollyy. Questo argomento deve essere preceduto dalla parolaTABLE
.start_timestamp
: unaTIMESTAMP
che indica il momento in cui una modifica viene inclusa nell'output. Se èNULL
, vengono restituite tutte le modifiche dopo la creazione della tabella. Se la tabella viene creata dopo il giornostart_timestamp
, viene utilizzata l'ora effettiva di creazione della tabella. Se l'ora è precedente a quella consentita dal criterio tempo di percorrenza, viene restituito un errore. Per le tabelle standard, questa finestra è di sette giorni, ma puoi configurare la finestra di spostamento cronologico in modo che sia inferiore.end_timestamp
: unTIMESTAMP
che indica l'ora più recente, esclusiva, alla quale viene inclusa una modifica nell'output. Se èNULL
, vengono incluse tutte le modifiche apportate fino all'inizio della query.
Valore restituito
Il valore TVF APPENDS
restituisce una tabella con le seguenti colonne:
- Tutte le colonne della tabella di input nel momento in cui viene eseguita la query. Se una colonna viene
aggiunta dopo
end_timestamp
, verrà visualizzata con i valoriNULL
compilati in tutte le righe inserite prima dell'aggiunta della colonna. _CHANGE_TYPE
: unSTRING
che indica il tipo di modifica che ha prodotto la riga. PerAPPENDS
, l'unico valore supportato èINSERT
._CHANGE_TIMESTAMP
: unTIMESTAMP
che indica il tempo di commit della transazione che ha apportato la modifica.
Dettagli
I record delle righe inserite rimangono memorizzati anche se i dati vengono successivamente eliminati. Le eliminazioni
non si riflettono nel TVF APPENDS
. Se una tabella viene copiata, la chiamata al TVF APPENDS
nella tabella copiata restituisce ogni riga inserita al momento della creazione della tabella. Se una riga viene modificata a causa di
un'operazione UPDATE
, non ci sono effetti.
Esempi
Questo esempio mostra la cronologia delle modifiche restituita da APPENDS
man mano che vengono apportate varie modifiche a una tabella denominata Produce
. Questo esempio potrebbe non funzionare se
viene completato in un periodo di tempo superiore alla
finestra di spostamento cronologico.
Per prima cosa, crea la tabella.
CREATE TABLE mydataset.Produce (product STRING, inventory INT64) AS ( SELECT "apples" AS product, 10 AS inventory);
Quindi, inserisci due righe nella tabella.
INSERT INTO mydataset.Produce VALUES ("bananas", 20), ("carrots", 30);
Visualizza la cronologia completa delle modifiche delle aggiunte. Usa i valori NULL
per ottenere la cronologia
completa all'interno della finestra di spostamento cronologico.
SELECT product, inventory, _CHANGE_TYPE AS change_type, _CHANGE_TIMESTAMP AS change_time FROM APPENDS(TABLE mydataset.Produce, NULL, NULL);
L'output è simile al seguente:
+---------+-----------+-------------+--------------------------------+ | product | inventory | change_type | change_time | +---------+-----------+-------------+--------------------------------+ | apples | 10 | INSERT | 2022-04-15 20:06:00.488000 UTC | | bananas | 20 | INSERT | 2022-04-15 20:06:08.490000 UTC | | carrots | 30 | INSERT | 2022-04-15 20:06:08.490000 UTC | +---------+-----------+-------------+--------------------------------+
Poi, aggiungi una colonna, inserisci una nuova riga di valori, aggiorna l'inventario ed elimina la riga "bananas".
ALTER TABLE mydataset.Produce ADD COLUMN color STRING; INSERT INTO mydataset.Produce VALUES ("grapes", 40, "purple"); UPDATE mydataset.Produce SET inventory = inventory + 5 WHERE TRUE; DELETE mydataset.Produce WHERE product = "bananas";
Visualizza la nuova tabella.
SELECT * FROM mydataset.Produce;
+---------+-----------+--------+ | product | inventory | color | +---------+-----------+--------+ | apples | 15 | NULL | | carrots | 35 | NULL | | grapes | 45 | purple | +---------+-----------+--------+
Visualizza la cronologia completa delle modifiche delle aggiunte.
SELECT product, inventory, color, _CHANGE_TYPE AS change_type, _CHANGE_TIMESTAMP AS change_time FROM APPENDS(TABLE mydataset.Produce, NULL, NULL);
L'output è simile al seguente:
+---------+-----------+--------+-------------+--------------------------------+ | product | inventory | color | change_type | change_time | +---------+-----------+--------+-------------+--------------------------------+ | apples | 10 | NULL | INSERT | 2022-04-15 20:06:00.488000 UTC | | bananas | 20 | NULL | INSERT | 2022-04-15 20:06:08.490000 UTC | | carrots | 30 | NULL | INSERT | 2022-04-15 20:06:08.490000 UTC | | grapes | 40 | purple | INSERT | 2022-04-15 20:07:45.751000 UTC | +---------+-----------+--------+-------------+--------------------------------+
La colonna inventory
mostra i valori impostati quando le righe sono state originariamente inserite nella tabella. Non mostra le modifiche
dell'istruzione UPDATE
. La riga con informazioni sulle banane è ancora presente poiché
il TVF APPENDS
acquisisce solo le aggiunte alle tabelle, non le eliminazioni.
Limitazioni
La cronologia delle modifiche è soggetta alle seguenti limitazioni:
- Puoi visualizzare solo le informazioni relative alle aggiunte, non agli aggiornamenti o alle eliminazioni.
- I dati sono limitati alla finestra di spostamento cronologico della tabella.