„BigQuery“ susijusių papildomų užklausų ir UDF apribojimų sprendimas: praktinis vadovas

„BigQuery“ UDF ir susijusios papildomos užklausos: iššūkių įveikimas

Šiuolaikinėse duomenų apdorojimo eigose „Google Cloud Platform“ „BigQuery“ dažnai naudojama dideliems duomenų rinkiniams tvarkyti ir sudėtingiems skaičiavimams atlikti. Tačiau vartotojai dažnai susiduria su apribojimais diegdami konkrečią verslo logiką naudodami vartotojo apibrėžtas funkcijas (UDF) ir susijusias antrines užklausas. Dėl to gali kilti problemų, ypač nurodant dinamines lenteles, kurias nuolat atnaujina darbuotojai, pavyzdžiui, švenčių vėliavėlių ar kitų laiko atžvilgiu svarbių duomenų atveju.

Koreliuojamų antrinių užklausų UDF problema tampa akivaizdi, kai bandoma integruoti realiojo laiko lentelės duomenis su data pagrįstais verslo skaičiavimais. Tokiais atvejais skaičiavimai gali nepavykti, kai naudojamos kelios lentelės ir sąlyginė logika. Tai ypač problematiška, kai veikia užkoduotos reikšmės, tačiau dinaminiai duomenys nepavyksta dėl šių apribojimų.

Šiame straipsnyje apžvelgsime konkretų problemos pavyzdį, kai UDF skirtas apskaičiuoti bendrą delsą tarp dviejų datų, atsižvelgiant į šventes ir ne darbo dienas, bet nepavyksta dėl BigQuery apribojimų koreliuojamoms antrinėms užklausoms. Taip pat išnagrinėsime galimus šios problemos sprendimus ir geriausią praktiką.

Jei susiduriate su panašiais iššūkiais, šiame vadove rasite įžvalgų, kaip tvarkyti susijusias antrinių užklausų klaidas ir optimizuoti UDF „BigQuery“. Pasinerkime į pavyzdį ir išsiaiškinkime, kaip įveikti šias įprastas kliūtis.

„BigQuery“ UDF ir susijusių antrinių užklausų sprendimų supratimas

Pagrindinis pirmiau pateiktų scenarijų tikslas yra apskaičiuoti bendrą darbo valandų skaičių tarp dviejų laiko žymų, atsižvelgiant į specifinius verslo elementus, pvz., šventes ir savaitgalius. Šis skaičiavimas yra labai svarbus ataskaitų teikimo procesams, kuriuose vertinama darbo trukmė, neįskaitant nedarbo dienų. Šiai logikai „Google BigQuery“ įtraukti čia naudojama vartotojo nustatyta funkcija (UDF). Vienas iš pagrindinių sprendžiamų iššūkių yra susidoroti su susijusios papildomos užklausos UDF, o tai gali sukelti klaidų ir našumo problemų užklausant didelius duomenų rinkinius.

Vienas iš pagrindinių scenarijaus komponentų yra naudojimas GENERATE_DATE_ARRAY funkcija. Ši funkcija sukuria visų datų tarp dviejų nurodytų laiko žymų sąrašą. Sugeneravęs dienų seką, scenarijus gali tiksliai apskaičiuoti, kiek darbo dienų yra tarp darbo pradžios ir pabaigos. Norėdami iš šio sąrašo išfiltruoti šventes ir savaitgalius, scenarijus naudoja ARRAY_AGG funkcija saugoti atostogų duomenis ir UNEST funkcija konvertuoti masyvus į eilutes, kad būtų lengviau palyginti.

Kita esminė sprendimo dalis – atostogų duomenų tvarkymas. Darbuotojų nuolat atnaujinama švenčių lentelė yra saugoma masyve ir naudojama išfiltruoti bet kokias datas, kurios sutampa su šventėmis ar savaitgaliais. Tai pasiekiama naudojant derinį KAIRĖ PRISIJUNGTI ir IŠTRAUKAS funkcija, kuri išskiria tam tikras datos dalis, pvz., savaitės dieną. Savaitgalio (šeštadienio ir sekmadienio) filtravimas užtikrina, kad tik darbo dienos prisideda prie galutinio vėlavimo skaičiavimo.

Galiausiai, UDF atlieka tam tikrą datos patvirtinimą, kad įsitikintų, jog įvesties reikšmės yra tinkamo formato, naudojant SAFE_CAST funkcija. Ši funkcija apsaugo nuo UDF gedimo, jei įvedamas netinkamas datos formatas, tai suteikia papildomą saugumo lygį. Galutinis rezultatas apskaičiuojamas susumavus darbo dienas ir koreguojant dalinių darbo dienų pradžios ir pabaigos laikus. Šis metodas siūlo lankstų ir pakartotinai naudojamą sudėtingos problemos, susijusios su „BigQuery“ vėlavimo skaičiavimo, sprendimą, laikantis UDF apribojimų.

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

„BigQuery UDF“ apribojimų įveikimas: užklausos našumo optimizavimas

Atliekant bet kokią didelio masto duomenų operaciją, našumas ir efektyvumas yra labai svarbūs. Vienas iš pagrindinių BigQuery iššūkių yra ribotos galimybės Vartotojo nustatytos funkcijos (UDF) efektyviai tvarkyti susijusias antrines užklausas, ypač kai UDF nurodo išorines lenteles arba reikia atlikti kelis sujungimus. Dėl šių problemų dažnai lėtėja veikimas ar net atsiranda klaidų. Tai ypač problematiška tais atvejais, kai logika turi dinamiškai gauti duomenis, kurie dažnai atnaujinami, pvz., atostogų lenteles. Norint tai įveikti, labai svarbu rasti alternatyvių būdų, kaip susisteminti užklausas, kad būtų išvengta šių apribojimų.

Vienas iš būdų yra sumažinti priklausomybę nuo koreliuojamų antrinių užklausų naudojant tarpinius skaičiavimus arba iš anksto kaupiant duomenis talpykloje. Pavyzdžiui, užuot kelis kartus nuorodų į švenčių lentelę savo funkcijoje, apsvarstykite galimybę saugoti švenčių informaciją prieinamesniu formatu, pvz., sukauptą masyvą arba laikiną lentelę. Tai sumažina sujungimų realiuoju laiku poreikį vykdant UDF. Be to, sverto panaudojimas masyvo funkcijos patinka ARRAY_AGG() ir UNNEST() užtikrina, kad galite tvarkyti sudėtingas duomenų struktūras be našumo nuobaudų, susijusių su pasikartojančiomis papildomomis užklausomis.

Kita strategija apima „BigQuery“ naudojimą SAFE_CAST() funkcija, leidžianti maloniai spręsti galimas formatavimo problemas, nes taip išvengiama nereikalingų užklausų klaidų. Užtikrindami įvesties duomenų patikimumą ir tvarkydami klaidas viduje, galite išvengti vykdymo problemų, dėl kurių kitu atveju UDF sugestų. Be to, visada apsvarstykite, ar konkretų skaičiavimą galima supaprastinti arba perkelti už UDF ribų, kad būtų supaprastintas apdorojimas. Tokie metodai užtikrina, kad jūsų UDF veiktų efektyviau, laikantis „BigQuery“ vykdymo aplinkos apribojimų.