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 DDLINSERT
Instrução DMLMERGE
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 palavraTABLE
.start_timestamp
: umTIMESTAMP
, que indica o primeiro horário em que uma mudança é incluída na saída. Se forNULL
, todas as mudanças desde a criação da tabela serão retornadas. Se a tabela for criada apósstart_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
: umTIMESTAMP
indicando o horário exclusivo, mais recente, em que uma mudança é incluída na saída. Se forNULL
, 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 valoresNULL
preenchidos em todas as linhas que foram inseridas antes da adição da coluna. _CHANGE_TYPE
: umSTRING
indicando o tipo de mudança que produziu a linha. ParaAPPENDS
, o único valor compatível éINSERT
._CHANGE_TIMESTAMP
: umaTIMESTAMP
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);
O resultado será assim:
+---------+-----------+--------+-------------+--------------------------------+ | 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 | +---------+-----------+--------+-------------+--------------------------------+
A coluna inventory
exibe os valores que foram definidos quando as linhas foram inseridas originalmente na tabela. Ele não mostra as mudanças na instrução UPDATE
. A linha com informações sobre bananas ainda está presente, já que o TVF APPENDS
captura apenas adições a tabelas, não exclusões.
Limitações
O histórico de alterações está sujeito às seguintes limitações:
- Só é possível ver informações sobre anexos, e não atualizações ou exclusões.
- Os dados são limitados à janela de viagem no tempo da tabela.