BigQueryga seotud alampäringute ja UDF-i piirangute lahendamine: praktiline juhend

BigQuery UDF-id ja nendega seotud alampäringud: väljakutsete ületamine

Kaasaegsetes andmetöötluse töövoogudes kasutatakse Google Cloud Platformi BigQueryt sageli suurte andmekogumite haldamiseks ja keerukate arvutuste tegemiseks. Kasutajad puutuvad aga sageli kokku piirangutega konkreetse äriloogika rakendamisel kasutaja määratud funktsioonide (UDF) ja korrelatsiooniga alampäringute kaudu. See võib tekitada probleeme, eriti viidates dünaamilistele tabelitele, mida töötajad regulaarselt värskendavad, näiteks puhkuselippude või muude ajatundlike andmete puhul.

UDF-ides korreleeritud alampäringute probleem ilmneb siis, kui proovite integreerida reaalajas tabeliandmeid kuupäevapõhiste äriarvutustega. Selliste stsenaariumide korral võivad arvutused nurjuda, kui tegemist on mitme tabeli ja tingimusliku loogikaga. See on eriti problemaatiline, kui kõvakodeeritud väärtused töötavad, kuid dünaamilised andmed ebaõnnestuvad nende piirangute tõttu.

Selles artiklis käsitleme konkreetset näidet probleemist, kus UDF on mõeldud kahe kuupäeva vahelise viivituse arvutamiseks, võttes arvesse pühasid ja puhkepäevi, kuid ebaõnnestub BigQuery piirangute tõttu korreleeritud alampäringute suhtes. Samuti uurime selle probleemi lahendamiseks võimalikke lahendusi ja parimaid tavasid.

Kui teil on sarnaseid probleeme, annab see juhend ülevaate korrelatsiooniga seotud alampäringu vigade käsitlemisest ja UDF-ide optimeerimisest BigQuerys. Sukeldume näitesse ja uurime, kuidas neid levinud teetõkkeid ületada.

BigQuery UDF-ide ja nendega seotud alampäringulahenduste mõistmine

Ülaltoodud skriptide peamine eesmärk on arvutada kahe ajatempli vaheline töötundide koguarv, võttes arvesse ettevõttespetsiifilisi elemente, nagu pühad ja nädalavahetused. See arvutus on ülioluline aruandlusprotsesside jaoks, mis mõõdavad töö kestust, jättes välja vabad päevad. Selle loogika koondamiseks Google BigQuerysse kasutatakse siin kasutaja määratud funktsiooni (UDF). Üks peamisi väljakutseid, millega tegeletakse, on tegelemine korreleeritud alampäringud UDF-ides, mis võib suurte andmekogumite päringute tegemisel põhjustada vigu ja jõudlusprobleeme.

Üks skripti põhikomponente on GENERATE_DATE_ARRAY funktsiooni. See funktsioon loob kõigi kahe etteantud ajatempli vahele jäävate kuupäevade loendi. Kuupäevavahemiku genereerimisel saab skript täpselt arvutada, mitu tööpäeva jääb töö algus- ja lõpuaja vahele. Sellest loendist pühade ja nädalavahetuste välja filtreerimiseks kasutab skript ARRAY_AGG funktsioon puhkuseandmete salvestamiseks ja UNEST funktsioon massiivide teisendamiseks ridadeks, et neid oleks lihtsam võrrelda.

Teine oluline osa lahendusest on puhkuseandmete käsitlemine. Pühade tabel, mida töötajad regulaarselt värskendavad, salvestatakse massiivina ja seda kasutatakse pühade või nädalavahetustega kattuvate kuupäevade filtreerimiseks. See saavutatakse kombinatsiooni abil VASAKUID LIITUMINE ja VÄLJAtõmme funktsioon, mis eraldab kuupäeva teatud osad, näiteks nädalapäeva. Nädalavahetuste (laupäev ja pühapäev) väljafiltreerimine tagab, et lõplikku viivitusarvestust osalevad ainult tööpäevad.

Lõpuks kontrollib UDF teatud kuupäeva, et sisendväärtused oleksid õiges vormingus, kasutades SAFE_CAST funktsiooni. See funktsioon hoiab ära UDF-i ebaõnnestumise, kui sisestatakse vale kuupäevavorming, pakkudes täiendavat turvalisust. Lõpptulemus arvutatakse tööpäevade summeerimisel ning osaliste tööpäevade algus- ja lõpuaegade korrigeerimisel. See lähenemisviis pakub paindlikku ja korduvkasutatavat lahendust BigQuery viivituste arvutamise keerukale probleemile, järgides samas UDF-i piiranguid.

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-i piirangute ületamine: päringu toimivuse optimeerimine

Iga suuremahulise andmetöötluse puhul on jõudlus ja tõhusus olulised. Üks BigQuery peamisi väljakutseid on piiratud võimekus Kasutaja määratud funktsioonid (UDF-id) korrelatsiooni alampäringute tõhusaks käsitlemiseks, eriti kui UDF viitab välistele tabelitele või vajab mitut liitumist. Need probleemid põhjustavad sageli aeglasemat jõudlust või isegi vigu. See on eriti problemaatiline juhtudel, kui loogika peab dünaamiliselt tõmbama andmeid, mida sageli värskendatakse, näiteks pühadetabelid. Sellest ülesaamiseks on ülioluline leida alternatiivseid viise päringute struktureerimiseks, et neist piirangutest mööda minna.

Üks võimalus on vähendada sõltuvust korreleeritud alampäringutest, kasutades vahepealseid arvutusi või salvestades andmed enne tähtaega vahemällu. Näiteks selle asemel, et oma funktsioonis mitu korda pühadetabelile viidata, kaaluge pühadeteabe salvestamist paremini juurdepääsetavas vormingus, näiteks koondatud massiivi või ajutise tabelina. See vähendab teie UDF-i täitmise ajal reaalajas liitumiste vajadust. Lisaks võimendus massiivi funktsioonid meeldib ARRAY_AGG() ja UNNEST() tagab, et saate hakkama keeruliste andmestruktuuridega ilma korduvate alampäringutega seotud jõudlustrahvideta.

Teine strateegia hõlmab BigQuery kasutamist SAFE_CAST() funktsioon võimalike vorminguprobleemide elegantseks käsitlemiseks, kuna see hoiab ära tarbetuid päringutõrkeid. Kui tagate sisendandmete töökindluse ja sisemiselt vigade käsitlemise, saate vältida käitusaegseid probleeme, mis muidu põhjustaksid teie UDF-i ebaõnnestumise. Lisaks kaaluge alati, kas konkreetset arvutust saab töötlemise lihtsustamiseks lihtsustada või välja laadida väljaspool UDF-i. Sellised meetodid tagavad, et teie UDF-id töötavad tõhusamalt, järgides samal ajal BigQuery täitmiskeskkonna piiranguid.