BigQuery toru süntaks: SQL-i lihtsamaks, kiiremaks ja loetavamaks muutmine

Igaüks, kes on töötanud SQL teab sellega kaasnevat armastuse-vihkamise suhet. SQL on võimas, standardiseeritud ja väljendusrikas – aga see on ka üks kõige ebaintuitiivsemaid keeli, mida lugeda ja suures mahus hallata. Mida sügavamaks teie päring läheb, seda raskem on aru saada, mis tegelikult toimub ja mis järjekorras.

Seda seetõttu, et SQL ei toimi teie arvates õiges järjekorras. Alustate kirjutamisest SELECT, aga andmebaas seda tegelikult esmalt ei töötle. Mootor alustab sellest FROM klausli abil, seejärel ühendab andmed, filtreerib need, koondab need ja alles hiljem rakendab sortimist ja projektsiooni. Tulemuseks on päring, mis kirjutatakse tagasi võrreldes selle teostamise viisiga.

See süntaksi ja teostuse mittevastavus muudab SQL-i nii kognitiivselt raskeks kui ka arvutuslikult kalliks. Analüütikud tuginevad sageli alampäringutele või tavalistele tabeliavaldistele (CTE-d), et simuleerida ülalt alla suunduvat voogu, lisades tarbetut keerukust ja koondamist. Isegi kogenud kasutajad siluvad pesastatud loogikaplokke selle asemel, et keskenduda probleemile, mida nad lahendavad.

Selle lahendamiseks suur päring supports1-d täidetakse samas järjekorras, kus neid loetakse. Loogika läbi ja lõhki kirjutamise asemel kirjutatakse see samm-sammult, nagu andmetorustik – iga rida esindab eraldi teisendust. Tulemuseks on SQL, mida on lihtsam mõista, kergemini siluda ja mis on tänapäevase andmeanalüüsi jaoks palju intuitiivsem.

Ülaltoodud pilt illustreerib põhiprobleemi, mille toru süntaks lahendab. Traditsioonilises SQL-is on süntaktiline järjekord (kuidas päringut kirjutate) on valesti joondatud semantiline järjekord (kuidas andmebaas seda tegelikult töötleb). Näiteks SELECT kuvatakse ülaosas, kuid mootor ei hinda seda enne, kui liitmine, filtreerimine, rühmitamine ja sortimine on lõpetatud.

See järjestus esindab BigQuery torupäringu loogilist ja süntaktilist voogu – ülevalt alla, vasakult paremale –, peegeldades seda, kuidas andmebaas tegelikult andmeid töötleb.

• FROM: Mis tahes torupäringu alguspunkt. Määrab esialgse tabeli või alampäringu, millest andmed voolavad. Toru süntaksis FROM saab iseseisvalt toimida kehtiva päringuna ja toimib järjestikuse torujuhtme sisenemisena.
• LIITU: Laiendab praegust tabelit täiendavate veergude ja ridadega teisest tabelist või alampäringust. Saab kasutada mitu korda järjest (|> JOIN table USING (key)), muutes vasakule sügavad liitmispuud loetavaks ilma pesastatud alampäringuteta.
• SET: Värskendab olemasolevaid veeruväärtusi (|> SET column = expression). Funktsioonid nagu SELECT * REPLACE(...) tavalises SQL-is, kuid on loetavam ja modulaarsem, kui seda kasutatakse torujuhtme etapina.
• PIKENDA: Lisab olemasolevale andmestikule arvutatud veerud (|> EXTEND expression AS new_column). Sarnane funktsiooniga SELECT *, new_column standardses SQL-is, kuid võimaldab tuletatud väljade astmelist loomist muude toimingute, näiteks ühenduste ja filtrite vahel.
• DOP: Eemaldab praegusest andmestikust ühe või mitu veergu (|> DROP column_name). Samaväärne SELECT * EXCEPT(column) standardses SQL-is ja seda kasutatakse sageli väljundi lihtsustamiseks või vahepealse andmemahu vähendamiseks.
• KUS: Filtreerib ridu, mis vastavad tingimusele (|> WHERE condition). Võib esineda ükskõik kus torujuhtmes, enne või pärast liitmist, kaotades vajaduse erinevate märksõnade (nt HAVING või QUALIFY) järele.
• KOGUMATERJAL: Teostab terve tabeli või rühmitatud agregatsioone (|> AGGREGATE SUM(column) AS total GROUP BY category). Asendab funktsiooni SELECT sees funktsiooni GROUP BY ja agregaatfunktsioonid, lihtsustades süntaksit ja viies selle vastavusse toimingute loogilise järjekorraga.
• TELLI: Sorteerib ridu kasvavas või kahanevas järjekorras (|> ORDER BY column DESC). Loob järjestatud tulemuste tabeli, millele saavad järgneda operaatorid, näiteks LIMIT.
• PIIRANG: Piirab tulemuses ridade arvu (|> LIMIT 10). Töötab pärast ORDER BY või järjestamata tabelites, säilitades filtrite ja agregatsioonide loomuliku järjestuse.
• HELISTAMA: Käivitab tabeli väärtusega funktsiooni või masinõppe mudeli, kasutades sisendina praegust tabelit (|> CALL ML.PREDICT(MODEL project.model_name)). Kõrvaldab vajaduse pesastatud funktsioonikõnede järele, luues lineaarse ja loetava töövoo.
• VALI: Määrab väljundisse kaasatavate veergude lõpliku projektsiooni (|> SELECT column1, column2). Toimib torujuhtme sulgemisoperatsioonina, sarnaselt viimasele SELECT tavalises SQL-päringus.

Allpool on toodud kõige levinumad stsenaariumid, kus toru süntaks lihtsustab SQL-loogikat, muutes päringud puhtamaks ja kiiremaks.

Andmete koondamine ilma alampäringuteta

Agregatsioonid on kohad, kus SQL hakkab tunduma pahupidi. Kui tahad midagi loendada ja need arvud kokku lugeda, oled järsku sulgudes nagu põrgus.

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;

Enam pole vaja pesastamist ega topeltrühmitamist. Iga samm voolab loogiliselt ja seda saab iseseisvalt muuta ilma kogu päringut ümber kirjutamata.

Veergude puhastamine ja teisendamine samm-sammult

Kui teil on vaja teksti väiketähtedega kirjutada, summasid arvutada ja lisaveergusid eemaldada, sunnib standardne SQL teid mitut ümber kirjutama. SELECT laused. Pipe'i süntaks tutvustab SET, EXTENDja DROP operaatorid, et saaksite muudatusi järjestuses rakendada.

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;

Iga toiming tugineb eelmisele, muutes teisenduste jälgimise ja loogika taaskasutamise lihtsamaks.

Filtreerimine pärast agregeerimist ilma „HAVING” meelespidamiseta

Üks SQL-i iseärasusi on see, et filtrite ajastus muutub sõltuvalt klauslist. Kasutate WHERE enne rühmitamist ja HAVING pärast, aga tegelikkuses filtreerivad mõlemad ainult ridu. Pipe'i süntaks võimaldab teil kasutada WHERE järjepidevalt, olenemata sellest, kuhu te selle paigutate.

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;

Nüüd saate filtreid kirjutada samas järjekorras, nagu te neid mõtlete: esmalt filtreerige andmed, seejärel rühmitage need ja seejärel filtreerige uuesti tulemuste põhjal.

Päringute silumine ilma ajutiste tabeliteta

Standardses SQL-is nõuab vahetulemuse kontrollimine kas ajutise tabeli loomist või koodi mähkimist mitmesse CTE-sse. Torusüntaksi abil saate päringu käivitada mis tahes punktini torujuhtmes.

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;

Iga päringu eesliide on eraldi käivitatav, mis tähendab, et saate andmeid igal etapil "piiluda". See on puhtam ja interaktiivsem viis vea parandamiseks ja itereerimiseks.

Mudelite ja funktsioonide aheldamine ilma pesastamiseta

Tabeliväärtustega funktsioonide või BigQuery masinõppemudelitega töötades võib pesastamine kiiresti loetamatuks muutuda. Pipe'i süntaks asendab need pesastatud kõned lineaarse aheldamisega, kasutades CALL-i.

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

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

Kui rakendate mitut mudelit või teisendust, peate lihtsalt lisama CALL-read – sulgusid pole vaja.

Andmete liigutamine ilma pesastatud valikuteta

Andmete teisendamine on alati olnud tüütu protsess, mis nõuab sageli alampäringute kihte. Pipe'i süntaks lihtsustab selle üheks voolavaks jadaks.

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

Päring kõlab nüüd nagu lugu: alusta oma andmetest, ühenda need, vali vajalik ja seejärel teisenda.

Miks toru süntaks on mängu muutja

Pipe'i süntaks ei leiuta SQL-i uuesti – see selgitab seda. See säilitab kõik SQL-i deklaratiivse struktuuri võimsa, kuid eemaldab loogika tagurpidi kirjutamisega seotud kognitiivse lisakoormuse.

Andmeteadlaste, analüütikute ja inseneride jaoks tähendab see järgmist:

• Lihtsam päringute lugemine, kirjutamine ja silumine
• Lihtsate ülesannete puhul pole enam vaja alampäringute või CTE-dega žongleerida
• Sujuv loogika, mis peegeldab teie tegelikku mõtlemist
• Parem jõudlus ja vähem üleliigseid toiminguid

BigQuery torusüntaks on tänapäevase andmeajastu SQL – lineaarne, intuitiivne ja reaalse maailma analüütika jaoks optimeeritud.

seotud Martech Zone sisu

Kuidas GA4-ga Universal Analyticsi ajaloolisi andmeid varundada ja nendest aru anda?

BigQuery: kuidas kasutada SQL-i demo PII nime, aadressi, e-posti ja telefoninumbri andmete loomiseks

Supermeetria: ekstraheerige, automatiseerige, muutke ja aktiveerige oma turundusandmeid ettevõtte kasvuks

Google Analytics 4: kuidas maksimeerida ülevaadet kasutaja atribuutide ja demograafiliste andmete abil