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 di una tabella viene mostrata come una funzione con valore di tabella (TVF) SQL che mostra particolari tipi di modifiche apportate durante un intervallo di tempo specificato. Questa funzionalità consente di elaborare le modifiche incrementali apportate a una tabella. Capire quali modifiche sono state apportate a una tabella può aiutarti a eseguire operazioni come la gestione incrementale di 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 tale tabella. I seguenti ruoli IAM (Identity and Access Management) predefiniti 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 la sicurezza a livello di colonna, puoi visualizzare la cronologia delle modifiche solo nelle colonne a cui hai accesso.

`APPENDS` TVF

Il 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 DDL
INSERT Istruzione DML
MERGE Istruzione DML
Caricamento dei dati in BigQuery
Importazione dei 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 o tabella con funzione carattere jolly. Questo argomento deve essere preceduto dalla parola TABLE.
start_timestamp: un valore TIMESTAMP che indica la prima volta in cui una modifica viene inclusa nell'output. Se è NULL, vengono restituite tutte le modifiche apportate dopo la creazione della tabella. Se la tabella viene creata dopo il giorno start_timestamp, viene utilizzata la data e l'ora di creazione effettiva della tabella. Se l'ora è antecedente a quella consentita dal viaggio nel tempo, 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: un TIMESTAMP che indica l'ora più recente, esclusiva, in cui è inclusa una modifica nell'output. Se è NULL, vengono incluse tutte le modifiche apportate fino all'inizio della query.

Valore restituito

Il TVF APPENDS restituisce una tabella con le seguenti colonne:

Tutte le colonne della tabella di input al momento dell'esecuzione della query. Se una colonna viene aggiunta dopo end_timestamp, verrà visualizzata con i valori NULL completati nelle righe inserite prima dell'aggiunta della colonna.
_CHANGE_TYPE: STRING che indica il tipo di modifica che ha generato la riga. L'unico valore supportato per APPENDS è INSERT.
_CHANGE_TIMESTAMP: TIMESTAMP che indica il tempo di commit della transazione che ha apportato la modifica.

Dettagli

I record delle righe inserite vengono conservati anche se i dati vengono eliminati in un secondo momento. Le eliminazioni non si riflettono nel TVF APPENDS. Se una tabella viene copiata, la chiamata del TVF APPENDS nella tabella copiata restituisce ogni riga così come inserita al momento della creazione della tabella. La modifica di una riga a causa di un'operazione UPDATE non ha alcun effetto.

Esempi

Questo esempio mostra la cronologia delle modifiche restituita da APPENDS mentre vengono apportate varie modifiche a una tabella denominata Produce. Questo esempio potrebbe non funzionare se completato su una durata più lunga di quella della 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 delle modifiche completa delle aggiunte. Utilizza 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 |
+---------+-----------+-------------+--------------------------------+

Successivamente, aggiungi una colonna, inserisci una nuova riga di valori, aggiorna l'inventario ed elimina la riga "banane".

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 delle modifiche completa delle aggiunte.

SELECT
  product,
  inventory,
  color,
  _CHANGE_TYPE AS change_type,
  _CHANGE_TIMESTAMP AS change_time
FROM
  APPENDS(TABLE mydataset.Produce, NULL, NULL);