Viagem no tempo com o BigQuery

Esse é um daqueles recursos que nos deixam impressionados, tanto pela utilidade quanto pela facilidade de uso. Podemos literalmente voltar no tempo e visualizar nossos dados dentro do BigQuery do momento atual até 6 dias 23 horas, 59 minutos e 59 segundos atrás.

É, eu sei, parece mentira… mas é verdade.

O que é?

Recurso que permite olhar para seus dados em qualquer ponto do passado dentro da janela padrão de 7 dias.

Resumindo, é possível ver como um dado no BigQuery estava a até 7 dias atrás. E tudo isso é default do BQ, sem nenhuma necessidade de configuração adicional. Incrível né?!

Sintaxe

SELECT *
FROM `mydataset.mytable`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

A instrução acima retorna os dados da sua tabela da forma como estavam a 1 hora atrás.

Exemplo Prático:

Imagine que você executou uma instrução DML errada, e quer saber as linhas que foram afetadas ou simplesmente restaurar a tabela para o momento anterior a execução da instrução, com o Time Travel isso é perfeitamente possível e ridiculamente simples.

Da uma olhada no exemplo seguinte:

 -- criando tabela teste.usuarios
CREATE TABLE teste.usuarios AS 
SELECT 
    id,
    GENERATE_UUID() AS uuid,
    RAND() > 0.5 as ativo
FROM UNNEST(GENERATE_ARRAY(1, 10, 1)) id;

-- alterando coluna ativo dos ids 1 e 7
update teste.usuarios 
set ativo = IF(ativo, false, true) 
where id in (1,7);

Nas instruções acima, criamos uma tabela, e executamos o update na coluna ativo para os ids 1 e 7.

Abaixo um script de exemplo de como podemos consultar o que foi alterado:

-- consultando os registros que foram afetados
BEGIN

    CREATE TEMP TABLE temp_tb AS 
    SELECT * FROM teste.usuarios for system_time AS OF timestamp_sub(current_timestamp(), interval 5 minute);

    select * from teste.usuarios t inner join (
        select * from temp_tb
        except distinct
        select * from teste.usuarios
    ) t2 on t.id = t2.id;

END

Note que criamos uma temporária com os dados da tabela teste.usuarios de 5 minutos atrás. E por que criar essa temporária? Porque hoje (abril/2023) o BigQuery exige que todas as tabelas da query estejam no mesmo período de tempo. Então a solução foi criar essa tabela temporária para guardar a “foto” da tabela de 5 minutos atrás e usá-la para comparar com a tabela no presente.

Ainda no script acima, note que criei um join da teste.usuarios com o resultado da except distinct entre a temporária e a própria teste.usuarios. Isso somente para que o resultado final fosse uma única linha contendo o dado atual e o dado alterado.

E se quisermos restaurar a tabela? Usando esse mesmo script acima, poderíamos apenas trocar o select de comparação entre as tabelas por uma instrução de insert com select… assim:

-- restaurando os registros que foram afetados
BEGIN

    CREATE TEMP TABLE temp_tb AS 
    SELECT * FROM teste.usuarios FOR system_time AS OF timestamp_sub(current_timestamp(), interval 5 minute);

    TRUNCATE TABLE teste.usuarios;
    INSERT INTO teste.usuarios SELECT * FROM temp_tb;

END

Fant´ástico né?! 😀

E como isso funciona “por baixo dos panos”? Não faço a mínima idéia. Mas vou descobrir e volto aqui pra contar =D.

E se quisermos ver como estava nossa tabela em um período de tempo maior que 7 dias… ai podemos usar o recurso de SNAPSHOT. Mas isso fica pra outro post =D.

Abraço!

Outros artigos sobre o BigQuery:

https://variavelconstante.com.br/category/bigquery/

Documentação oficial do Time Travel

https://cloud.google.com/bigquery/docs/time-travel?hl=pt-br