A BigQuery-vel kapcsolatos részlekérdezések és az UDF-korlátozások megoldása: Gyakorlati útmutató

BigQuery UDF-ek és kapcsolódó részlekérdezések: A kihívások leküzdése

A modern adatfeldolgozási munkafolyamatokban a Google Cloud Platform BigQuery-jét gyakran használják nagy adatkészletek kezelésére és összetett számítások elvégzésére. A felhasználók azonban gyakran találkoznak korlátokkal, amikor meghatározott üzleti logikát felhasználó által definiált függvényeken (UDF) és kapcsolódó részlekérdezéseken keresztül valósítanak meg. Ez kihívásokat jelenthet, különösen akkor, ha dinamikus táblázatokra hivatkozik, amelyeket a személyzet rendszeresen frissít, például az ünnepnapi jelzők vagy más időérzékeny adatok esetében.

A korrelált részlekérdezések problémája az UDF-ekben nyilvánvalóvá válik, amikor a valós idejű táblaadatokat dátumvezérelt üzleti számításokkal kívánjuk integrálni. Ilyen esetekben a számítások meghiúsulhatnak, ha több tábla és feltételes logika is érintett. Ez különösen akkor jelent problémát, ha a merevkódolt értékek működnek, de a dinamikus adatok e korlátozások miatt meghiúsulnak.

Ebben a cikkben egy olyan problémára mutatunk be egy konkrét példát, ahol az UDF-nek két dátum közötti teljes késést kell kiszámítania, figyelembe véve az ünnepnapokat és a munkaszüneti napokat is, de a BigQuery korrelált részlekérdezésekre vonatkozó korlátai miatt meghiúsul. Megvizsgáljuk a lehetséges megoldásokat és a bevált gyakorlatokat is a probléma kezelésére.

Ha hasonló problémákkal szembesül, ez az útmutató betekintést nyújt a kapcsolódó segédlekérdezési hibák kezelésébe és az UDF-ek BigQuery alkalmazásban történő optimalizálásához. Merüljünk el a példában, és vizsgáljuk meg, hogyan lehet leküzdeni ezeket a gyakori akadályokat.

A BigQuery UDF-ek és a kapcsolódó segédlekérdezési megoldások megértése

A fent megadott szkriptek elsődleges célja a teljes munkaidő kiszámítása két időbélyeg között, miközben figyelembe veszi a vállalkozásspecifikus elemeket, például az ünnepeket és a hétvégéket. Ez a számítás kritikus fontosságú azoknál a jelentéskészítési folyamatoknál, amelyek a munka időtartamát mérik, miközben kizárják a munkaszüneti napokat. A Google BigQuery logikáját egy felhasználói függvény (UDF) használja. Az egyik fő megoldandó kihívás a kezelés korrelált részlekérdezések az UDF-eken belül, ami hibákhoz és teljesítményproblémákhoz vezethet nagy adatkészletek lekérdezésekor.

A szkript egyik kulcseleme a GENERATE_DATE_ARRAY funkció. Ez a funkció létrehoz egy listát az összes dátumról két megadott időbélyeg között. A dátumtartomány generálásával a szkript pontosan ki tudja számítani, hány munkanap van a feladat kezdő és befejező időpontja között. Az ünnepek és hétvégék kiszűréséhez a listából a szkript a következőt használja ARRAY_AGG funkció az ünnepi adatok tárolására és a UNEST függvény a tömbök sorokká alakításához a könnyebb összehasonlítás érdekében.

A megoldás másik döntő része az ünnepi adatok kezelése. A személyzet által rendszeresen frissített ünnepi táblázat egy tömbben kerül tárolásra, és az ünnepnapokkal vagy hétvégékkel egybeeső dátumok kiszűrésére szolgál. Ezt kombinációjával érik el BAL CSATLAKOZÁS és a KIVONAT funkció, amely elkülöníti a dátum bizonyos részeit, például a hét napját. A hétvégék (szombat és vasárnap) kiszűrése biztosítja, hogy csak a munkanapok járuljanak hozzá a végső késedelemszámításhoz.

Végül az UDF dátumellenőrzést hajt végre annak biztosítására, hogy a bemeneti értékek a megfelelő formátumban legyenek a következő használatával SAFE_CAST funkció. Ez a funkció megakadályozza az UDF meghibásodását, ha érvénytelen dátumformátumot ad meg, és további biztonsági réteget biztosít. A végeredmény a munkanapok összegzésével és a részmunkanapok kezdési és befejezési időpontjainak korrigálásával kerül kiszámításra. Ez a megközelítés rugalmas és újrafelhasználható megoldást kínál a BigQuery késések kiszámításának összetett problémájára, miközben betartja az UDF korlátozásait.

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));

A BigQuery UDF korlátainak leküzdése: A lekérdezés teljesítményének optimalizálása

Minden nagyszabású adatkezelésnél elengedhetetlen a teljesítmény és a hatékonyság. A BigQuery egyik legnagyobb kihívása a korlátozott képesség Felhasználó által meghatározott függvények (UDF-ek) a korrelált részlekérdezések hatékony kezeléséhez, különösen akkor, ha az UDF külső táblákra hivatkozik, vagy több összekapcsolást kell végrehajtania. Ezek a problémák gyakran lassabb teljesítményt vagy akár hibákat eredményeznek. Ez különösen problémás olyan esetekben, amikor a logikának dinamikusan kell begyűjtenie a gyakran frissülő adatokat, például az ünnepi táblázatokat. Ennek kiküszöbölése érdekében kulcsfontosságú alternatív módszereket találni a lekérdezések strukturálására, hogy megkerülje ezeket a korlátozásokat.

Az egyik megközelítés a korrelált részlekérdezésektől való függés csökkentése közbenső számítások vagy az adatok idő előtti gyorsítótárazásával. Például ahelyett, hogy a függvényében többször hivatkozna az ünnepnapi táblázatra, fontolja meg az ünnepnapok információinak könnyebben hozzáférhető formátumban való tárolását, például összesített tömbben vagy ideiglenes táblában. Ez minimálisra csökkenti a valós idejű csatlakozások szükségességét az UDF végrehajtása során. Továbbá a tőkeáttétel tömbfüggvények mint ARRAY_AGG() és UNNEST() biztosítja, hogy összetett adatstruktúrákat kezelhessen az ismételt segédlekérdezésekhez kapcsolódó teljesítménybüntetések nélkül.

Egy másik stratégia a BigQuery használata SAFE_CAST() funkcióval kecsesen kezelheti a lehetséges formátumproblémákat, mivel ez megakadályozza a szükségtelen lekérdezési hibákat. A bemeneti adatok robusztusságának és a hibák belső kezelésének biztosításával megelőzheti a futásidejű problémákat, amelyek egyébként az UDF meghibásodását okoznák. Ezenkívül mindig fontolja meg, hogy egy adott számítás leegyszerűsíthető-e vagy az UDF-en kívülre helyezhető-e a feldolgozás egyszerűsítése érdekében. Az ilyen módszerek biztosítják, hogy az UDF-ek hatékonyabban fussanak, miközben betartják a BigQuery végrehajtási környezetének korlátait.