Resolvendo subconsultas correlacionadas do BigQuery e limitações de UDF: um guia prático

UDFs do BigQuery e subconsultas correlacionadas: superando desafios

Em fluxos de trabalho modernos de processamento de dados, o BigQuery do Google Cloud Platform é frequentemente usado para lidar com grandes conjuntos de dados e realizar cálculos complexos. No entanto, os usuários frequentemente encontram limitações ao implementar lógica de negócios específica por meio de Funções Definidas pelo Usuário (UDFs) e subconsultas correlacionadas. Isto pode criar desafios, especialmente ao fazer referência a tabelas dinâmicas que são atualizadas regularmente pela equipe, como no caso de sinalizadores de feriados ou outros dados urgentes.

O problema de subconsultas correlacionadas em UDFs torna-se evidente ao tentar integrar dados de tabelas em tempo real com cálculos de negócios baseados em datas. Nesses cenários, os cálculos podem falhar quando diversas tabelas e lógica condicional estão envolvidas. Isso é especialmente problemático quando os valores codificados funcionam, mas os dados dinâmicos falham devido a essas limitações.

Neste artigo, veremos um exemplo específico de problema em que uma UDF pretende calcular o atraso total entre duas datas, considerando feriados e dias não úteis, mas falha devido às limitações do BigQuery em subconsultas correlacionadas. Também exploraremos possíveis soluções e práticas recomendadas para resolver esse problema.

Se você estiver enfrentando desafios semelhantes, este guia fornecerá insights sobre como lidar com erros de subconsulta correlacionados e otimizar suas UDFs no BigQuery. Vamos mergulhar no exemplo e explorar como superar esses obstáculos comuns.

Noções básicas sobre UDFs do BigQuery e soluções de subconsultas correlacionadas

O objetivo principal dos scripts fornecidos acima é calcular o total de horas de trabalho entre dois carimbos de data/hora e, ao mesmo tempo, levar em consideração elementos específicos do negócio, como feriados e fins de semana. Este cálculo é fundamental para processos de relatórios que medem a duração do trabalho, excluindo os dias não úteis. Uma função definida pelo usuário (UDF) é usada aqui para encapsular essa lógica no Google BigQuery. Um dos principais desafios enfrentados é lidar com subconsultas correlacionadas dentro de UDFs, o que pode levar a erros e problemas de desempenho ao consultar grandes conjuntos de dados.

Um dos principais componentes do script é o uso do GENERATE_DATE_ARRAY função. Esta função cria uma lista de todas as datas entre dois carimbos de data/hora determinados. Ao gerar um intervalo de datas, o script pode calcular com precisão quantos dias úteis existem entre os horários de início e término do trabalho. Para filtrar feriados e fins de semana desta lista, o script utiliza o ARRAY_AGG função para armazenar dados de feriados e o DESCONECTAR função para converter arrays em linhas para facilitar a comparação.

Outra parte crucial da solução é o tratamento dos dados de feriados. A tabela de feriados, que é atualizada regularmente pela equipe, é armazenada em uma matriz e usada para filtrar quaisquer datas que coincidam com feriados ou finais de semana. Isto é conseguido através de uma combinação de PARTIR À ESQUERDA e o EXTRAIR função, que isola partes específicas da data, como o dia da semana. A filtragem dos finais de semana (sábado e domingo) garante que apenas os dias úteis contribuam para o cálculo final do atraso.

Finalmente, a UDF realiza alguma validação de data para garantir que os valores de entrada estejam no formato correto usando o método SAFE_CAST função. Esta função evita que a UDF falhe se um formato de data inválido for inserido, fornecendo uma camada adicional de segurança. O resultado final é calculado somando os dias úteis e ajustando os horários de início e término dos dias úteis parciais. Essa abordagem oferece uma solução flexível e reutilizável para o complexo problema de cálculo de atrasos no BigQuery, respeitando as limitações da UDF.

CREATE OR REPLACE FUNCTION my.gcp.optimized_function(ip_start_date TIMESTAMP, ip_end_date TIMESTAMP)
RETURNS NUMERIC AS ((
WITH temp_date AS (
  SELECT
    CASE
      WHEN ip_start_date > ip_end_date THEN DATE(ip_end_date)
      ELSE DATE(ip_start_date)
    END AS ip_date_01,
    CASE
      WHEN ip_start_date > ip_end_date THEN DATE(ip_start_date)
      ELSE DATE(ip_end_date)
    END AS ip_date_02
),
holiday_array AS (
  SELECT ARRAY_AGG(STRUCT(DATE(cal_date) AS cal_date, holiday_flag)) AS holidays
  FROM dataset.staff_time
),
working_days AS (
  SELECT
    CASE
      WHEN DATE(ip_start_date) <> DATE(ip_end_date) THEN
        SUM(CASE
          WHEN cal_date NOT IN (SELECT cal_date FROM UNNEST(holiday_array.holidays)) THEN 1
          ELSE 0
        END)
      ELSE 
    END AS working_day
  FROM UNNEST(GENERATE_DATE_ARRAY(ip_start_date, ip_end_date, INTERVAL 1 DAY)) AS cal_date
  WHERE cal_date NOT IN (SELECT cal_date FROM UNNEST(holiday_array.holidays))
),
SELECT working_day
FROM working_days));

CREATE OR REPLACE FUNCTION my.gcp.function_v2(ip_start_date TIMESTAMP, ip_end_date TIMESTAMP)
RETURNS NUMERIC AS ((
WITH temp_date AS (
  SELECT
    CASE
      WHEN ip_start_date > ip_end_date THEN DATE(ip_end_date)
      ELSE DATE(ip_start_date)
    END AS ip_date_01,
    CASE
      WHEN ip_start_date > ip_end_date THEN DATE(ip_start_date)
      ELSE DATE(ip_end_date)
    END AS ip_date_02
),
holiday_array AS (
  SELECT ARRAY_AGG(STRUCT(DATE(cal_date) AS cal_date, holiday_flag)) AS holidays
  FROM dataset.staff_time
),
working_days AS (
  SELECT
    CASE
      WHEN DATE(ip_start_date) <> DATE(ip_end_date) THEN
        SUM(CASE
          WHEN ot.cal_date IS  AND EXTRACT(DAYOFWEEK FROM cal_date) NOT IN (1, 7) THEN 1
          ELSE 0
        END)
      ELSE 
    END AS working_day
  FROM UNNEST(GENERATE_DATE_ARRAY(SAFE_CAST(ip_start_date AS DATE),
  DATE_SUB(SAFE_CAST(ip_end_date AS DATE), INTERVAL 1 DAY), INTERVAL 1 DAY)) AS cal_date
  LEFT JOIN holiday_array ot
  ON cal_date = ot.cal_date
  WHERE ot.cal_date IS 
    AND EXTRACT(DAYOFWEEK FROM cal_date) NOT IN (1, 7)
),
SELECT working_day
FROM working_days));

Superando as limitações do BigQuery UDF: otimizando o desempenho da consulta

Em qualquer operação de dados em grande escala, o desempenho e a eficiência são essenciais. Um grande desafio que surge no BigQuery é a capacidade limitada de Funções definidas pelo usuário (UDFs) para lidar com subconsultas correlacionadas de forma eficiente, especialmente quando a UDF faz referência a tabelas externas ou precisa realizar múltiplas junções. Esses problemas geralmente resultam em desempenho mais lento ou até mesmo em erros. Isso é particularmente problemático nos casos em que a lógica precisa extrair dinamicamente dados que são atualizados com frequência, como tabelas de feriados. Para superar isso, é crucial encontrar formas alternativas de estruturar suas consultas para contornar essas limitações.

Uma abordagem é reduzir a dependência de subconsultas correlacionadas usando cálculos intermediários ou armazenando dados em cache antecipadamente. Por exemplo, em vez de fazer referência à tabela de feriados diversas vezes em sua função, considere armazenar informações de feriados em um formato mais acessível, como uma matriz agregada ou uma tabela temporária. Isso minimiza a necessidade de junções em tempo real durante a execução de sua UDF. Além disso, alavancar funções de matriz como ARRAY_AGG() e UNNEST() garante que você possa lidar com estruturas de dados complexas sem as penalidades de desempenho associadas a subconsultas repetidas.

Outra estratégia envolve usar o BigQuery SAFE_CAST() função para lidar com possíveis problemas de formato normalmente, pois isso evita falhas de consulta desnecessárias. Ao garantir a robustez dos dados de entrada e ao lidar com erros internamente, você pode evitar problemas de tempo de execução que, de outra forma, causariam falha em sua UDF. Além disso, sempre considere se um cálculo específico pode ser simplificado ou descarregado fora da UDF para agilizar o processamento. Esses métodos garantem que suas UDFs sejam executadas com mais eficiência, respeitando as limitações do ambiente de execução do BigQuery.