Etiqueta: sql

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!

BigQuery: Como encontrar o próximo dia útil
Dicas para escrever códigos mais concisos e otimizados

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

BigQuery: Como encontrar o próximo dia útil

Recentemente precisei desenvolver uma tabela agregada para servir a um dashboard e um dos dados a serem calculados era a previsão de entrega de um pedido com base em um SLA.

Até ai tudo tranquilo…

DECLARE data_inicio DATE DEFAULT '2022-09-01';
DECLARE sla INT64 DEFAULT 10;
SELECT DATE_ADD(data_inicio, INTERVAL sla DAY);

Só tinha um detalhe: no cálculo deveria ser considerado apenas os dias úteis.

Utilizando uma lógica parecida com a que foi usada na function de cálculo de dias úteis, resolvi desenvolver uma nova function para encontrar o próximo dia útil dado um intervalo de tempo e uma lista de datas a desconsiderar.

A função é bastante simples, recebe apenas 3 parametros: Data de inicio da contagem, intervalo e a lista de datas a serem desconsideradas no calculo. O resultado foi esse:

CREATE TEMP FUNCTION calc_proxima_data_util(DATA_INICIO DATE, SLA INT64, LISTA_FERIADOS ARRAY<DATE>) AS (
  (
    SELECT MAX(dt) FROM (
    SELECT dt, RANK() OVER(ORDER BY dt) as id
    FROM UNNEST(GENERATE_DATE_ARRAY(DATE_ADD(DATA_INICIO, INTERVAL 1 DAY), DATE_ADD(DATA_INICIO,INTERVAL SLA*10 DAY))) dt 
    WHERE EXTRACT(DAYOFWEEK from dt) between 2 and 6 and dt not in UNNEST(LISTA_FERIADOS)
    QUALIFY id <= SLA)
  )
);

A lógica é bastante simples: é gerado um range de datas partindo da data_inicio (mas não contabilizando-a) e indo até o intervalo * 10. Isso para ter uma margem de segurança e não correr o risco de perder alguma data.

Após a geração do range, com a cláusula where tiramos os finais de semana bem como os feriados.

Do resultado, criei um sequencial para cada linha e com a instrução QUALIFY retornei apenas as linhas com sequencial <= SLA. Precisei fazer dessa forma pois o BigQuery não aceitou que eu usasse o LIMIT passando o valor como um parâmetro… infelizmente =/

Depois só precisei fazer um MAX() na data. Legal né?! =D Eu achei =D.

Segue um exemplo de uso completo:

DECLARE DATA_INICIO DATE DEFAULT '2022-09-01';
DECLARE SLA INT64 DEFAULT 10;
DECLARE lista_feriados ARRAY<DATE> DEFAULT NULL;
SET lista_feriados = [
  DATE('2022-01-01'), 
  DATE('2022-03-01'), 
  DATE('2022-04-15'), 
  DATE('2022-04-17'), 
  DATE('2022-04-21'), 
  DATE('2022-05-01'), 
  DATE('2022-06-16'), 
  DATE('2022-09-07'), 
  DATE('2022-10-12'), 
  DATE('2022-11-02'), 
  DATE('2022-11-15'), 
  DATE('2022-12-25')
];

CREATE TEMP FUNCTION calc_proxima_data_util(DATA_INICIO DATE, SLA INT64, LISTA_FERIADOS ARRAY<DATE>) AS (
  (
    SELECT MAX(dt) FROM (
    SELECT dt, RANK() OVER(ORDER BY dt) as id
    FROM UNNEST(GENERATE_DATE_ARRAY(DATE_ADD(DATA_INICIO, INTERVAL 1 DAY), DATE_ADD(DATA_INICIO,INTERVAL SLA*10 DAY))) dt 
    WHERE EXTRACT(DAYOFWEEK from dt) between 2 and 6 and dt not in UNNEST(LISTA_FERIADOS)
    QUALIFY id <= SLA)
  )
);

SELECT 
  dt_inicio, 
  sla, 
  calc_proxima_data_util(dt_inicio, sla, lista_feriados) as previsao_entrega
FROM (
    SELECT date('2022-09-01') as dt_inicio, 10 as sla union all
    SELECT date('2022-02-25'), 7 union all
    SELECT date('2022-04-07'), 20
);

Abraço!

BigQuery: Contando os dias úteis entre datas

Existem várias formas de fazer esse cálculo, a que vou mostrar nesse post é a mais simples q eu já vi.

Cálculo com datas é algo extremamente comum quando falamos de uma tabela agregada que servirá um relatório ou dashboard, e fazer esses cálculos as vezes pode parecer muito complexo, quando na verdade não é.

Para o exemplo que vou mostrar, criei um script simples, onde simulo algumas datas de inicio e algumas datas fim, e calculo os dias úteis entre elas usando uma UDF temporária

DECLARE lista_feriados ARRAY<DATE> DEFAULT NULL;
SET lista_feriados = [
  DATE('2022-01-01'), 
  DATE('2022-03-01'), 
  DATE('2022-04-15'), 
  DATE('2022-04-17'), 
  DATE('2022-04-21'), 
  DATE('2022-05-01'), 
  DATE('2022-06-16'), 
  DATE('2022-09-07'), 
  DATE('2022-10-12'), 
  DATE('2022-11-02'), 
  DATE('2022-11-15'), 
  DATE('2022-12-25')
];

CREATE TEMP FUNCTION total_dias_uteis(DATA_INICIO DATE, DATA_FIM DATE, LISTA_FERIADOS ARRAY<DATE>) AS (
  (
      SELECT COUNT(dt)
      FROM UNNEST(GENERATE_DATE_ARRAY(DATA_INICIO, DATA_FIM)) dt 
      WHERE EXTRACT(DAYOFWEEK from dt) between 2 and 6 and dt not in UNNEST(LISTA_FERIADOS)
  )
);
SELECT dt_inicio, dt_fim, total_dias_uteis(dt_inicio, dt_fim, lista_feriados) as total_dias_uteis
FROM (
    SELECT date('2021-12-25') as dt_inicio, date('2022-01-05') as dt_fim union all
    SELECT date('2022-02-25'), date('2022-03-05') union all
    SELECT date('2022-04-05'), date('2022-04-25')
);

Acima a versão completa pra quem não quer mais explicações rsrsr.

Nesse inicio de código fiz apenas a declaração de uma variável: lista_feriados e logo em seguida inseri os feriados de 2022. Esses feriados poderiam vir de uma tabela, ai vai da imaginação de cada um 😉

DECLARE lista_feriados ARRAY<DATE> DEFAULT NULL;
SET lista_feriados = [
  DATE('2022-01-01'), 
  DATE('2022-03-01'), 
  DATE('2022-04-15'), 
  DATE('2022-04-17'), 
  DATE('2022-04-21'), 
  DATE('2022-05-01'), 
  DATE('2022-06-16'), 
  DATE('2022-09-07'), 
  DATE('2022-10-12'), 
  DATE('2022-11-02'), 
  DATE('2022-11-15'), 
  DATE('2022-12-25')
];

Logo em seguida criei um UDF temporária para facilitar a vida na hora de usar. O mais interessante aqui é criar uma UDF permanente, nesse caso, basta remover a instrução TEMP, e incluir o id do projeto e do dataset antes do nome da function.

A function recebe 3 parâmetros: data inicio, data fim e uma lista de feriados (criada logo acima).

CREATE TEMP FUNCTION total_dias_uteis(DATA_INICIO DATE, DATA_FIM DATE, LISTA_FERIADOS ARRAY<DATE>) AS (
  (
      SELECT COUNT(dt)
      FROM UNNEST(GENERATE_DATE_ARRAY(DATA_INICIO, DATA_FIM)) dt 
      WHERE EXTRACT(DAYOFWEEK from dt) between 2 and 6 and dt not in UNNEST(LISTA_FERIADOS)
  )
);

E por fim, um select simples simulando algumas datas apenas para mostrar como usar a function, porém se você sabe usar as funções built-in como count, sum, avg… vc sabe usar as UDF’s 🙂

SELECT 
    dt_inicio, 
    dt_fim, 
    total_dias_uteis(dt_inicio, dt_fim, lista_feriados) as total_dias_uteis
FROM (
    SELECT date('2021-12-25') as dt_inicio, date('2022-01-05') as dt_fim union all
    SELECT date('2022-02-25'), date('2022-03-05') union all
    SELECT date('2022-04-05'), date('2022-04-25')
);

Antes de sair… dá uma conferida na série de posts para o Projeto Video Creator que eu postei aqui no blog. Pra quem curte automatizar o trabalho, vai gostar do conteúdo =D.

Gerando videos para o Youtube com linguagem Python
Como extrair o máximo que a sua máquina pode oferecer!

Espero ter ajudado 🙂

Grande abraço!