Resolució de subconsultes correlacionades amb BigQuery i limitacions UDF: una guia pràctica

UDF de BigQuery i subconsultes correlacionades: superació de reptes

En els fluxos de treball moderns de processament de dades, BigQuery de Google Cloud Platform s'utilitza sovint per gestionar grans conjunts de dades i realitzar càlculs complexos. Tanmateix, els usuaris sovint es troben amb limitacions a l'hora d'implementar una lògica empresarial específica mitjançant les funcions definides per l'usuari (UDF) i les subconsultes correlacionades. Això pot crear reptes, especialment quan es fa referència a taules dinàmiques que el personal actualitza regularment, com en el cas de les banderes de vacances o altres dades sensibles al temps.

El problema de les subconsultes correlacionades a les UDF es fa evident quan s'intenta integrar les dades de la taula en temps real amb els càlculs empresarials basats en dates. En aquests escenaris, els càlculs poden fallar quan hi ha múltiples taules i lògica condicional. Això és especialment problemàtic quan els valors codificats funcionen, però les dades dinàmiques fallen a causa d'aquestes limitacions.

En aquest article, explicarem un exemple concret d'un problema en què una UDF està destinada a calcular el retard total entre dues dates, tenint en compte els dies festius i no laborables, però falla a causa de les limitacions de BigQuery en les subconsultes correlacionades. També explorarem possibles solucions i bones pràctiques per solucionar aquest problema.

Si teniu reptes similars, aquesta guia us proporcionarà informació sobre com gestionar els errors de subconsulta correlacionats i optimitzar els vostres UDF a BigQuery. Submergiu-nos en l'exemple i explorem com superar aquests obstacles comuns.

Entendre les UDF de BigQuery i les solucions de subconsulta correlacionades

L'objectiu principal dels scripts proporcionats anteriorment és calcular el total d'hores de treball entre dues marques de temps, alhora que es tenen en compte elements específics de l'empresa, com ara les vacances i els caps de setmana. Aquest càlcul és fonamental per als processos d'informes que mesuren la durada de les feines tot excloent els dies no laborables. Aquí s'utilitza una funció definida per l'usuari (UDF) per encapsular aquesta lògica a Google BigQuery. Un dels principals reptes abordats és fer front subconsultes correlacionades dins de les UDF, cosa que pot provocar errors i problemes de rendiment en consultar grans conjunts de dades.

Un dels components clau del script és l'ús de la GENERATE_DATE_ARRAY funció. Aquesta funció crea una llista de totes les dates entre dues marques de temps donades. En generar un interval de dates, l'script pot calcular amb precisió quants dies laborables hi ha entre l'hora d'inici i de finalització de la feina. Per filtrar els dies festius i els caps de setmana d'aquesta llista, l'script utilitza el ARRAY_AGG funció per emmagatzemar dades de vacances i el UNNEST funció per convertir matrius en files per facilitar la comparació.

Una altra part crucial de la solució és el maneig de les dades de vacances. La taula de vacances, que el personal actualitza periòdicament, s'emmagatzema en una matriu i s'utilitza per filtrar qualsevol data que coincideixi amb festius o caps de setmana. Això s'aconsegueix mitjançant una combinació de UNIR-SE A L'ESQUERRA i el EXTRACTE funció, que aïlla parts específiques de la data, com ara el dia de la setmana. El filtratge dels caps de setmana (dissabte i diumenge) garanteix que només els dies laborables contribueixin al càlcul final del retard.

Finalment, l'UDF realitza una certa validació de la data per assegurar-se que els valors d'entrada tenen el format correcte mitjançant l' SAFE_CAST funció. Aquesta funció evita que l'UDF falli si s'introdueix un format de data no vàlid, proporcionant una capa addicional de seguretat. El resultat final es calcula sumant els dies laborables i ajustant les hores d'inici i finalització en jornades parcials. Aquest enfocament ofereix una solució flexible i reutilitzable al complex problema de calcular retards a BigQuery tot complint les limitacions 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));

Superació de les limitacions de BigQuery UDF: optimització del rendiment de les consultes

En qualsevol operació de dades a gran escala, el rendiment i l'eficiència són essencials. Un dels principals reptes que sorgeix a BigQuery és la capacitat limitada de Funcions definides per l'usuari (UDF) per gestionar subconsultes correlacionades de manera eficient, especialment quan l'UDF fa referència a taules externes o necessita realitzar múltiples unions. Aquests problemes solen provocar un rendiment més lent o fins i tot errors. Això és especialment problemàtic en els casos en què la lògica necessita extreure de forma dinàmica dades que s'actualitzen amb freqüència, com ara les taules de vacances. Per superar-ho, és crucial trobar maneres alternatives d'estructurar les vostres consultes per evitar aquestes limitacions.

Un enfocament és reduir la dependència de les subconsultes correlacionades mitjançant l'ús de càlculs intermedis o l'emmagatzematge de dades amb antelació. Per exemple, en lloc de fer referència a la taula de vacances diverses vegades a la vostra funció, considereu emmagatzemar la informació de vacances en un format més accessible, com ara una matriu agregada o una taula temporal. Això minimitza la necessitat d'unions en temps real durant l'execució de la vostra UDF. A més, aprofitant funcions de matriu com ARRAY_AGG() i UNNEST() garanteix que podeu gestionar estructures de dades complexes sense les penalitzacions de rendiment associades a les subconsultes repetides.

Una altra estratègia consisteix a utilitzar BigQuery SAFE_CAST() funció per gestionar possibles problemes de format amb gràcia, ja que això evita errors de consulta innecessaris. En garantir la robustesa de les dades d'entrada i gestionar els errors internament, podeu evitar problemes d'execució que, d'altra manera, farien que la vostra UDF fallés. A més, tingueu en compte sempre si un càlcul concret es pot simplificar o descarregar fora de l'UDF per agilitzar el processament. Aquests mètodes garanteixen que els vostres UDF s'executen de manera més eficient mentre s'adhereixen a les limitacions de l'entorn d'execució de BigQuery.