Trabalhar com o histórico de alterações

O histórico de alterações do BigQuery permite rastrear o histórico de alterações em uma tabela do BigQuery. O histórico de alterações de uma tabela é exposto como uma função de valor de tabela (TVF, na sigla em inglês) do SQL que mostra tipos específicos de alterações feitas durante um período especificado. Esse recurso permite processar alterações incrementais feitas em uma tabela. Entender quais alterações foram feitas em uma tabela pode ajudar você a realizar tarefas como manter uma réplica de tabela fora do BigQuery e evitar cópias caras.

Permissões necessárias

Para ver o histórico de alterações em uma tabela, é necessário ter a permissão bigquery.tables.getData nela. Os seguintes papéis predefinidos do Identity and Access Management (IAM) incluem essa permissão:

roles/bigquery.dataViewer
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin

Se uma tabela tiver ou teve políticas de acesso no nível da linha, somente um administrador poderá acessar os dados históricos da tabela. A permissão bigquery.rowAccessPolicies.overrideTimeTravelRestrictions é necessária na tabela e está incluída no papel predefinido do IAM roles/bigquery.admin.

Se uma tabela tiver segurança no nível da coluna, só será possível ver o histórico de alterações nas colunas a que você tem acesso.

TVF `APPENDS`

O TVF APPENDS retorna uma tabela com todas as linhas anexadas a uma tabela em um determinado período. As seguintes operações adicionam linhas ao histórico de alterações APPENDS:

CREATE TABLE Instrução DDL
INSERT Instrução DML
MERGE Instrução DML
Como carregar dados no BigQuery
Ingestão de streaming

Sintaxe

APPENDS(
  TABLE table,
  start_timestamp DEFAULT NULL,
  end_timestamp DEFAULT NULL)

table: o nome da tabela do BigQuery. Ele não pode ser uma visualização, uma subconsulta, uma tabela externa, uma visualização materializada ou uma tabela curinga. Esse argumento precisa ser precedido pela palavra TABLE.
start_timestamp: um TIMESTAMP, que indica o primeiro horário em que uma mudança é incluída na saída. Se for NULL, todas as mudanças desde a criação da tabela serão retornadas. Se a tabela for criada após start_timestamp, o horário atual de criação da tabela será usado. Se o tempo for anterior ao permitido pela viagem de tempo, um erro será retornado. Para tabelas padrão, essa janela é de sete dias, mas é possível configurar a janela de viagem no tempopara ser menor que isso.
end_timestamp: um TIMESTAMP indicando o horário exclusivo, mais recente, em que uma mudança é incluída na saída. Se for NULL, todas as alterações feitas até o início da consulta serão incluídas.

Valor de retorno

O TVF APPENDS retorna uma tabela com as seguintes colunas:

Todas as colunas da tabela de entrada no momento em que a consulta é executada. Se uma coluna for adicionada depois de end_timestamp, ela aparecerá com valores NULL preenchidos em todas as linhas que foram inseridas antes da adição da coluna.
_CHANGE_TYPE: um STRING indicando o tipo de mudança que produziu a linha. Para APPENDS, o único valor compatível é INSERT.
_CHANGE_TIMESTAMP: uma TIMESTAMP indicando o tempo de confirmação da transação que fez a alteração.

Detalhes

Os registros de linhas inseridas persistem mesmo que esses dados sejam excluídos posteriormente. As exclusões não são refletidas no TVF APPENDS. Se uma tabela for copiada, a chamada do TVF APPENDS na tabela copiada retornará todas as linhas conforme inseridas no momento da criação da tabela. Se uma linha for modificada devido a uma operação UPDATE, não haverá efeito.

Examples

Este exemplo mostra o histórico de alterações retornado por APPENDS conforme várias alterações são feitas em uma tabela chamada Produce. Este exemplo pode não funcionar se for concluído por mais tempo do que a janela de viagem no tempo. Primeiro, crie a tabela.

CREATE TABLE mydataset.Produce (product STRING, inventory INT64) AS (
  SELECT "apples" AS product, 10 AS inventory);

Em seguida, insira duas linhas na tabela.

INSERT INTO mydataset.Produce
VALUES
  ("bananas", 20),
  ("carrots", 30);

Veja o histórico de alterações completo dos anexos. Use os valores de NULL para ver o histórico completo na janela de tempo.

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

O resultado será assim:

+---------+-----------+-------------+--------------------------------+
| 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 |
+---------+-----------+-------------+--------------------------------+

Em seguida, adicione uma coluna, insira uma nova linha de valores, atualize o inventário e exclua a linha "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";

Veja a nova tabela.

SELECT * FROM mydataset.Produce;

+---------+-----------+--------+
| product | inventory | color  |
+---------+-----------+--------+
| apples  | 15        | NULL   |
| carrots | 35        | NULL   |
| grapes  | 45        | purple |
+---------+-----------+--------+

Veja o histórico de alterações completo dos anexos.

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