Résoudre les sous-requêtes corrélées BigQuery et les limitations UDF : un guide pratique

FDU BigQuery et sous-requêtes corrélées : surmonter les défis

Dans les workflows de traitement de données modernes, BigQuery de Google Cloud Platform est souvent utilisé pour gérer de grands ensembles de données et effectuer des calculs complexes. Cependant, les utilisateurs rencontrent fréquemment des limitations lors de la mise en œuvre d'une logique métier spécifique via des fonctions définies par l'utilisateur (UDF) et des sous-requêtes corrélées. Cela peut créer des défis, en particulier lors du référencement de tableaux dynamiques régulièrement mis à jour par le personnel, comme dans le cas des indicateurs de jours fériés ou d'autres données sensibles au facteur temps.

Le problème des sous-requêtes corrélées dans les UDF devient évident lorsque l'on tente d'intégrer des données de table en temps réel avec des calculs métier basés sur la date. Dans de tels scénarios, les calculs peuvent échouer lorsque plusieurs tables et logiques conditionnelles sont impliquées. Cela est particulièrement problématique lorsque les valeurs codées en dur fonctionnent, mais que les données dynamiques échouent en raison de ces limitations.

Dans cet article, nous examinerons un exemple spécifique d'un problème dans lequel une FDU est censée calculer le délai total entre deux dates, en tenant compte des jours fériés et des jours non ouvrés, mais échoue en raison des limitations de BigQuery sur les sous-requêtes corrélées. Nous explorerons également les solutions potentielles et les meilleures pratiques pour résoudre ce problème.

Si vous rencontrez des problèmes similaires, ce guide vous fournira des informations sur la gestion des erreurs de sous-requêtes corrélées et l'optimisation de vos UDF dans BigQuery. Examinons cet exemple et explorons comment surmonter ces obstacles courants.

Comprendre les UDF BigQuery et les solutions de sous-requêtes corrélées

L'objectif principal des scripts fournis ci-dessus est de calculer le total des heures de travail entre deux horodatages tout en prenant en compte des éléments spécifiques à l'entreprise comme les vacances et les week-ends. Ce calcul est essentiel pour les processus de reporting qui mesurent la durée des tâches tout en excluant les jours non ouvrés. Une fonction définie par l'utilisateur (UDF) est utilisée ici pour encapsuler cette logique dans Google BigQuery. L’un des principaux défis à relever consiste à gérer sous-requêtes corrélées au sein des UDF, ce qui peut entraîner des erreurs et des problèmes de performances lors de l'interrogation de grands ensembles de données.

L'un des éléments clés du script est l'utilisation du GENERATE_DATE_ARRAY fonction. Cette fonction crée une liste de toutes les dates entre deux horodatages donnés. En générant une plage de dates, le script peut calculer avec précision le nombre de jours ouvrables entre les heures de début et de fin de la tâche. Pour filtrer les jours fériés et les week-ends de cette liste, le script utilise le ARRAY_AGG fonction pour stocker les données de vacances et les UNNEST fonction pour convertir des tableaux en lignes pour une comparaison plus facile.

Un autre élément crucial de la solution est le traitement des données de vacances. Le tableau des jours fériés, régulièrement mis à jour par le personnel, est stocké dans un tableau et utilisé pour filtrer toutes les dates qui coïncident avec les jours fériés ou les week-ends. Ceci est réalisé en utilisant une combinaison de REJOINDRE À GAUCHE et le EXTRAIT fonction, qui isole des parties spécifiques de la date, comme le jour de la semaine. Le filtrage des week-ends (samedi et dimanche) garantit que seuls les jours ouvrables contribuent au calcul final du retard.

Enfin, l'UDF effectue une validation de date pour garantir que les valeurs d'entrée sont au format correct à l'aide du SAFE_CAST fonction. Cette fonction empêche l'échec de l'UDF si un format de date non valide est saisi, offrant ainsi une couche de sécurité supplémentaire. Le résultat final est calculé en additionnant les jours ouvrables et en ajustant les heures de début et de fin des jours ouvrables partiels. Cette approche offre une solution flexible et réutilisable au problème complexe du calcul des délais dans BigQuery tout en respectant les limitations 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));

Surmonter les limitations de BigQuery UDF : optimiser les performances des requêtes

Dans toute opération de données à grande échelle, les performances et l’efficacité sont essentielles. L'un des défis majeurs qui se posent dans BigQuery est la capacité limitée de Fonctions définies par l'utilisateur (UDF) pour gérer efficacement les sous-requêtes corrélées, en particulier lorsque l'UDF fait référence à des tables externes ou doit effectuer plusieurs jointures. Ces problèmes entraînent souvent un ralentissement des performances, voire des erreurs. Cela est particulièrement problématique dans les cas où la logique doit extraire dynamiquement des données fréquemment mises à jour, comme les tables de jours fériés. Pour surmonter ce problème, il est crucial de trouver des moyens alternatifs de structurer vos requêtes afin de contourner ces limitations.

Une approche consiste à réduire le recours aux sous-requêtes corrélées en utilisant des calculs intermédiaires ou en mettant les données en cache à l'avance. Par exemple, plutôt que de référencer la table des jours fériés plusieurs fois dans votre fonction, envisagez de stocker les informations sur les jours fériés dans un format plus accessible, comme un tableau agrégé ou une table temporaire. Cela minimise le besoin de jointures en temps réel lors de l'exécution de votre UDF. De plus, en tirant parti fonctions de tableau comme ARRAY_AGG() et UNNEST() garantit que vous pouvez gérer des structures de données complexes sans les pénalités de performances associées aux sous-requêtes répétées.

Une autre stratégie consiste à utiliser les outils de BigQuery SAFE_CAST() fonction pour gérer les problèmes de format potentiels avec élégance, car cela évite les échecs de requête inutiles. En garantissant la robustesse des données d'entrée et en gérant les erreurs en interne, vous pouvez éviter les problèmes d'exécution qui autrement entraîneraient l'échec de votre UDF. De plus, demandez-vous toujours si un calcul particulier peut être simplifié ou déchargé en dehors de l'UDF pour rationaliser le traitement. De telles méthodes garantissent que vos UDF s'exécutent plus efficacement tout en respectant les limitations de l'environnement d'exécution de BigQuery.