„BigQuery“ kanalo sintaksė: kaip padaryti SQL lengvesnį, greitesnį ir lengviau skaitomą

Kiekvienas, kas dirbo su SQL žino apie su tuo susijusį meilės ir neapykantos santykį. SQL yra galinga, standartizuota ir išraiškinga, tačiau tai taip pat viena iš neintuityviausių kalbų, kurią sunku skaityti ir palaikyti dideliu mastu. Kuo gilesnė jūsų užklausa, tuo sunkiau suprasti, kas iš tikrųjų vyksta ir kokia tvarka.

Taip yra todėl, kad SQL neveikia tokia tvarka, kokia jūs manote. Pradedate rašydami SELECT, bet duomenų bazė to pirmiausia neapdoroja. Variklis pradeda nuo FROM sąlygą, tada sujungia duomenis, juos filtruoja, agreguoja ir tik vėliau taiko rūšiavimą ir projektavimą. Rezultatas yra užklausa, parašyta atgal palyginti su tuo, kaip jis vykdomas.

Dėl šio sintaksės ir vykdymo neatitikimo SQL yra sudėtingas ir brangus kognityviniu požiūriu. Analitikai dažnai pasikliauja antrinėmis užklausomis arba bendromis lentelių išraiškomis (CTE), kad imituotų srautą iš viršaus į apačią, pridedant nereikalingo sudėtingumo ir pertekliaus. Net patyrę vartotojai galiausiai derina įdėtus logikos blokus, užuot sutelkę dėmesį į problemą, kurią jie sprendžia.

Norėdami tai išspręsti, „BigQuery“ „supports1“ vykdomi ta pačia tvarka, kaip ir skaitomi. Užuot rašę logiką išvirkščia puse, rašote ją žingsnis po žingsnio, kaip duomenų srautą – kiekviena eilutė žymi atskirą transformaciją. Rezultatas – SQL, kurį lengviau suprasti, lengviau derinti ir kuris yra daug intuityvesnis šiuolaikinei duomenų analizei.

Aukščiau pateiktame paveikslėlyje pavaizduota pagrindinė problema, kurią išsprendžia kanalo sintaksė. Tradiciniame SQL sintaksės tvarka (kaip rašote užklausą) nesutampa su semantinė tvarka (kaip duomenų bazė tai iš tikrųjų apdoroja). Pavyzdžiui, SELECT rodomas viršuje, bet variklis jo neįvertina, kol nebus atliktas sujungimas, filtravimas, grupavimas ir rūšiavimas.

Ši seka atspindi loginę ir sintaksinę „BigQuery“ kanalo užklausos eigą – iš viršaus į apačią, iš kairės į dešinę – atspindinčią, kaip duomenų bazė iš tikrųjų apdoroja duomenis.

• IŠ: Bet kurios „pipe“ užklausos pradinis taškas. Apibrėžia pradinę lentelę arba papildomą užklausą, iš kurios teka duomenys. „Pipe“ sintaksėje FROM gali būti savarankiška užklausa ir veikti kaip nuoseklaus srauto įvedimo elementas.
• PRISIJUNGTI: Išplečia dabartinę lentelę papildomais stulpeliais ir eilutėmis iš kitos lentelės arba antrinės užklausos. Galima naudoti kelis kartus iš eilės (|> JOIN table USING (key)), todėl kairėje pusėje esantys sujungimo medžiai yra skaitomi be įdėtųjų antrinių užklausų.
• NUSTATYTI: Atnaujina esamas stulpelių reikšmes vietoje (|> SET column = expression). Funkcijos, tokios kaip SELECT * REPLACE(...) standartiniame SQL, bet yra lengviau skaitomas ir modulinis, kai naudojamas kaip konvejerio žingsnis.
• pratęsti: Prideda apskaičiuotus stulpelius prie esamo duomenų rinkinio (|> EXTEND expression AS new_column). Panašiai kaip SELECT *, new_column standartiniame SQL, bet leidžia palaipsniui kurti išvestinius laukus tarp kitų operacijų, pvz., sujungimų ir filtrų.
• DROP: Pašalina vieną ar daugiau stulpelių iš dabartinio duomenų rinkinio (|> DROP column_name). Atitinka SELECT * EXCEPT(column) standartiniame SQL ir dažnai naudojamas išvesties supaprastinimui arba tarpinio duomenų dydžio sumažinimui.
• KUR: Filtruoja eilutes, kurios atitinka sąlygą (|> WHERE condition). Gali atsirasti bet kurioje srauto vietoje, prieš arba po agregavimo, todėl nereikia naudoti skirtingų raktinių žodžių, tokių kaip HAVING arba QUALIFY.
• VIDAUS: Atlieka visos lentelės arba grupuotus agregavimus (|> AGGREGATE SUM(column) AS total GROUP BY category). Pakeičia GROUP BY ir agregavimo funkcijas SELECT viduje, supaprastindamas sintaksę ir suderindamas ją su logine operacijų tvarka.
• RŪŠIUOTI PAGAL: Rūšiuoja eilutes didėjimo arba mažėjimo tvarka (|> ORDER BY column DESC). Sukuria sutvarkytą rezultatų lentelę, po kurios gali sekti operatoriai, tokie kaip LIMIT.
• RIBOTIS: Apriboja eilučių skaičių rezultate (|> LIMIT 10). Veikia po ORDER BY arba netvarkingose ​​lentelėse, išsaugant natūralią filtrų ir agregacijų seką.
• SKAMBINKITE: Vykdo lentelės reikšmių funkciją arba mašininio mokymosi modelį, naudodama dabartinę lentelę kaip įvestį (|> CALL ML.PREDICT(MODEL project.model_name)). Pašalina įdėtinių funkcijų iškvietimų poreikį, sukuriant linijinį ir lengvai skaitomą darbo eigą.
• PASIRINKTI: Apibrėžia galutinę stulpelių projekciją, kuri bus įtraukta į išvestį (|> SELECT column1, column2). Atlieka uždarymo operaciją vamzdyne, panašiai kaip ir galutinė SELECT standartinėje SQL užklausoje.

Žemiau pateikiami dažniausiai pasitaikantys scenarijai, kai kanalo sintaksė supaprastina SQL logiką, todėl užklausos tampa aiškesnės ir greitesnės.

Duomenų agregavimas be papildomų užklausų

Agregacijos yra ta vieta, kur SQL pradeda atrodyti išvirkščia. Jei norite ką nors suskaičiuoti, tada suskaičiuokite tuos skaičius ir staiga atsidursite skliaustų pragare.

SELECT c_count, COUNT(*) AS custdist
FROM (
  SELECT c_custkey, COUNT(o_orderkey) AS c_count
  FROM customer
  JOIN orders ON c_custkey = o_custkey
  WHERE o_comment NOT LIKE '%unusual%packages%'
  GROUP BY c_custkey
)
GROUP BY c_count
ORDER BY custdist DESC;

FROM customer
|> JOIN orders ON c_custkey = o_custkey
   AND o_comment NOT LIKE '%unusual%packages%'
|> AGGREGATE COUNT(o_orderkey) AS c_count GROUP BY c_custkey
|> AGGREGATE COUNT(*) AS custdist GROUP BY c_count
|> ORDER BY custdist DESC;

Daugiau jokio įdėjimo ar dvigubo grupavimo. Kiekvienas žingsnis vyksta logiškai ir gali būti modifikuotas nepriklausomai, neperrašant visos užklausos.

Kolonų valymas ir transformavimas žingsnis po žingsnio

Kai reikia rašyti mažosiomis raidėmis, apskaičiuoti sumas ir pašalinti papildomus stulpelius, standartinis SQL verčia perrašyti kelis SELECT teiginiai. Pristato „Pipe“ sintaksę SET, EXTENDir DROP operatoriai, kad galėtumėte taikyti pakeitimus iš eilės.

SELECT o_custkey, ROUND(o_totalprice) AS total_price
FROM (
  SELECT
    o_custkey,
    o_totalprice,
    LOWER(o_orderstatus) AS o_orderstatus
  FROM orders
)
WHERE total_price > 1000;

FROM orders
|> SET o_orderstatus = LOWER(o_orderstatus)
|> EXTEND ROUND(o_totalprice) AS total_price
|> WHERE total_price > 1000
|> SELECT o_custkey, total_price;

Kiekviena operacija remiasi ankstesne, todėl lengviau atsekti transformacijas ir pakartotinai panaudoti logiką.

Filtravimas po agregavimo neprisimenant „TURĖJIMO“

Viena iš SQL ypatumų yra ta, kad filtrų laikas keičiasi priklausomai nuo sąlygos. Jūs naudojate WHERE prieš grupavimą ir HAVING po to, bet iš tikrųjų abu tik filtruoja eilutes. „Pipe“ sintaksė leidžia naudoti WHERE nuosekliai, nesvarbu, kur jį dedate.

SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE active = TRUE
GROUP BY department
HAVING COUNT(*) > 5;

FROM employees
|> WHERE active = TRUE
|> AGGREGATE COUNT(*) AS emp_count GROUP BY department
|> WHERE emp_count > 5;

Dabar filtrus galite rašyti ta pačia tvarka, kuria apie juos galvojate: pirmiausia filtruokite duomenis, tada juos sugrupuokite, tada dar kartą filtruokite pagal rezultatus.

Užklausų derinimas be laikinų lentelių

Standartinėje SQL sistemoje, norint patikrinti tarpinį rezultatą, reikia sukurti laikiną lentelę arba kodą apvynioti keliais CTE. Naudojant kanalo sintaksę, užklausą galima vykdyti iki bet kurio srauto taško.

WITH filtered AS (
  SELECT * FROM orders WHERE o_totalprice > 500
),
summed AS (
  SELECT o_custkey, SUM(o_totalprice) AS total
  FROM filtered GROUP BY o_custkey
)
SELECT * FROM summed WHERE total > 10000;

FROM orders
|> WHERE o_totalprice > 500
|> AGGREGATE SUM(o_totalprice) AS total GROUP BY o_custkey
|> WHERE total > 10000;

Kiekvienas užklausos prefiksas yra vykdomas atskirai, o tai reiškia, kad galite „peržvelgti“ duomenis bet kuriame etape. Tai švaresnis ir interaktyvesnis būdas derinti ir iteruoti.

Modelių ir funkcijų sujungimas be įdėjimo

Dirbant su lentelinėmis funkcijomis arba „BigQuery ML“ modeliais, įterptosios užklausos gali greitai tapti neįskaitomos. „Pipe“ sintaksė šiuos įterptuosius užklausimus pakeičia tiesiniu sujungimu naudojant CALL.

SELECT *
FROM ML.PREDICT(
  MODEL `project.sentiment_model`,
  (SELECT text FROM reviews)
);

SELECT text FROM reviews
|> CALL ML.PREDICT(MODEL `project.sentiment_model`);

Jei taikote kelis modelius arba transformacijas, tiesiog sudedate papildomas CALL eilutes – skliaustų nereikia.

Duomenų suvedimas be įdėtųjų pasirinkimų

Duomenų suvedimas į vieną eilę visada buvo varginantis procesas, dažnai reikalaujantis daugybės papildomų užklausų sluoksnių. „Pipe“ sintaksė jį supaprastina į vieną sklandžią seką.

SELECT *
FROM (
  SELECT n_name, c_acctbal, c_mktsegment
  FROM customer JOIN nation USING (n_nationkey)
)
PIVOT(SUM(c_acctbal) FOR n_name IN ('PERU', 'KENYA', 'JAPAN'));

FROM customer
|> JOIN nation USING (n_nationkey)
|> SELECT n_name, c_acctbal, c_mktsegment
|> PIVOT(SUM(c_acctbal) FOR n_name IN ('PERU', 'KENYA', 'JAPAN'));

Užklausa dabar skamba kaip istorija: pradėkite nuo savo duomenų, sujunkite juos, pasirinkite, ko jums reikia, tada atlikite posūkį.

Kodėl „Pipe“ sintaksė yra esminė perversmo priežastis

„Pipe“ sintaksė neišradinėja SQL iš naujo – ji jį paaiškina. Ji išsaugo visas deklaratyvios SQL struktūros savybes, bet pašalina kognityvinį krūvį, susijusį su logikos rašymu atgal.

Duomenų mokslininkams, analitikams ir inžinieriams tai reiškia:

• Lengviau skaityti, rašyti ir derinti užklausas
• Daugiau jokių papildomų užklausų ar CTE žongliravimo atliekant paprastas užduotis
• Supaprastinta logika, atspindinti jūsų iš tikrųjų mąstymą
• Geresnis našumas ir mažiau nereikalingų operacijų

„BigQuery“ kanalų sintaksė yra šiuolaikinės duomenų eros SQL – linijinė, intuityvi ir optimizuota realaus pasaulio analizei.

Susijęs Martech Zone Turinys

Kaip sukurti „Universal Analytics“ istorinių duomenų atsargines kopijas ir pranešti apie juos naudojant GA4?

„BigQuery“: kaip naudoti SQL kuriant demonstracinę AII pavadinimą, adresą, el. pašto adresą ir telefono numerio duomenis

Supermetrika: ištraukite, automatizuokite, transformuokite ir suaktyvinkite savo rinkodaros duomenis verslo augimui

„Google Analytics 4“: kaip maksimaliai padidinti įžvalgas naudojant vartotojo atributus ir demografinius duomenis