Cystrawen Pipe BigQuery: Gwneud SQL yn Haws, yn Gyflymach, ac yn Fwy Darllenadwy

William KarrTachwedd 03, 2025

Darllenwch 6 munud

Sut i Ddefnyddio Cystrawen Pipe BigQuery

Unrhyw un sydd wedi gweithio gyda SQL yn gwybod y berthynas cariad-casineb sy'n dod gydag ef. Mae SQL yn bwerus, yn safonol, ac yn fynegiannol—ond mae hefyd yn un o'r ieithoedd mwyaf anreddfol i'w darllen a'i chynnal ar raddfa fawr. Po ddyfnaf y daw eich ymholiad, y anoddaf y daw i ddeall beth sy'n digwydd mewn gwirionedd ac ym mha drefn.

Dyna oherwydd nad yw SQL yn llifo yn y drefn rydych chi'n ei meddwl. Rydych chi'n dechrau trwy ysgrifennu SELECT, ond nid yw'r gronfa ddata yn prosesu hynny yn gyntaf mewn gwirionedd. Mae'r peiriant yn dechrau gyda'r FROM cymal, yna'n uno data, yn ei hidlo, yn ei agregu, a dim ond yn ddiweddarach yn cymhwyso didoli a rhagamcanu. Y canlyniad yw ymholiad sydd wedi'i ysgrifennu yn ôl o'i gymharu â'r ffordd y caiff ei weithredu.

Mae'r anghydweddiad hwn rhwng cystrawen a gweithrediad yn gwneud SQL yn drwm yn wybyddol ac yn ddrud yn gyfrifiadurol. Mae dadansoddwyr yn aml yn dibynnu ar is-ymholiadau neu Fynegiadau Tabl Cyffredin (CTEs) i efelychu llif o'r top i'r gwaelod, gan ychwanegu cymhlethdod a diswyddiad diangen. Mae hyd yn oed defnyddwyr profiadol yn gorffen dadfygio blociau rhesymeg nythu yn lle canolbwyntio ar y broblem maen nhw'n ei datrys.

I fynd i'r afael â hyn, BigQuery Mae supports1s yn gweithredu yn yr un drefn ag y cânt eu darllen. Yn lle ysgrifennu rhesymeg y tu mewn allan, rydych chi'n ei ysgrifennu gam wrth gam, fel piblinell ddata—mae pob llinell yn cynrychioli trawsnewidiad penodol. Y canlyniad yw SQL sy'n haws i'w ddeall, yn haws i'w ddadfygio, ac yn llawer mwy greddfol ar gyfer dadansoddi data modern.

cystrawen pibell google 1 — ffynhonnell: google

Mae'r ddelwedd uchod yn dangos y broblem graidd y mae cystrawen pibell yn ei datrys. Yn SQL traddodiadol, y trefn gystrawennol (sut rydych chi'n ysgrifennu ymholiad) wedi'i gamlinio â'r trefn semantig (sut mae'r gronfa ddata yn ei brosesu mewn gwirionedd). Er enghraifft, SELECT yn ymddangos ar y brig, ond nid yw'r injan yn ei werthuso tan ar ôl i'r ymuno, hidlo, grwpio a didoli gael eu gwneud.

Mae'r dilyniant hwn yn cynrychioli llif rhesymegol a chystrawennol ymholiad pibell BigQuery — o'r top i'r gwaelod, o'r chwith i'r dde — gan adlewyrchu sut mae'r gronfa ddata'n prosesu'r data mewn gwirionedd.

O: Man cychwyn unrhyw ymholiad pibell. Yn diffinio'r tabl neu'r is-ymholiad cychwynnol y mae data'n llifo ohono. Mewn cystrawen pibell, FROM gall sefyll ar ei ben ei hun fel ymholiad dilys a gweithredu fel y cofnod i biblinell ddilyniannol.
YMUNWCH: Yn ymestyn y tabl cyfredol gyda cholofnau a rhesi ychwanegol o dabl neu is-ymholiad arall. Gellir ei ddefnyddio sawl gwaith yn olynol (|> JOIN table USING (key)), gan wneud coed ymuno chwith-dwfn yn ddarllenadwy heb is-ymholiadau nythu.
SET: Yn diweddaru gwerthoedd colofn presennol yn eu lle (|> SET column = expressionSwyddogaethau fel SELECT * REPLACE(...) mewn SQL safonol ond mae'n fwy darllenadwy a modiwlaidd pan gaiff ei ddefnyddio fel cam piblinell.
ESTYNIAD: Yn ychwanegu colofnau cyfrifedig at y set ddata bresennol (|> EXTEND expression AS new_column). Yn debyg i SELECT *, new_column mewn SQL safonol ond yn caniatáu creu meysydd deilliedig fesul tipyn rhwng gweithrediadau eraill fel ymuniadau a hidlwyr.
GALWAD: Yn tynnu un neu fwy o golofnau o'r set ddata gyfredol (|> DROP column_name). Cyfwerth â SELECT * EXCEPT(column) mewn SQL safonol ac fe'i defnyddir yn aml i symleiddio allbwn neu leihau maint data canolradd.
LLE: Yn hidlo rhesi sy'n bodloni amod (|> WHERE condition). Gall ymddangos yn unrhyw le yn y biblinell, cyn neu ar ôl agregu, gan ddileu'r angen am allweddeiriau gwahanol fel HAVING neu QUALIFY.
CYTUNDEB: Yn perfformio crynhoadau tabl llawn neu grwpiedig (|> AGGREGATE SUM(column) AS total GROUP BY category). Yn disodli GROUP BY a ffwythiannau agregu o fewn SELECT, gan symleiddio cystrawen a'i alinio â threfn resymegol y gweithrediadau.
GORCHYMYN GAN: Yn trefnu rhesi mewn trefn esgynnol neu ddisgynnol (|> ORDER BY column DESC). Yn cynhyrchu tabl canlyniadau trefnus y gellir ei ddilyn gan weithredwyr fel LIMIT.
TERFYN: Yn cyfyngu nifer y rhesi yn y canlyniad (|> LIMIT 10). Yn gweithio ar ôl ORDER BY neu ar dablau heb eu trefnu, gan gadw dilyniant naturiol hidlwyr ac agregau.
GALW: Yn gweithredu swyddogaeth neu fodel dysgu peirianyddol â gwerth tabl gan ddefnyddio'r tabl cyfredol fel mewnbwn (|> CALL ML.PREDICT(MODEL project.model_name)). Yn dileu'r angen am alwadau swyddogaeth nythu, gan greu llif gwaith llinol a darllenadwy.
DETHOL: Yn diffinio'r rhagamcan terfynol o golofnau i'w cynnwys yn yr allbwn (|> SELECT column1, column2). Yn gwasanaethu fel y gweithrediad cau mewn piblinell, yn debyg i'r un terfynol SELECT mewn ymholiad SQL safonol.

Isod mae'r senarios mwyaf cyffredin lle mae cystrawen pibell yn symleiddio rhesymeg SQL, gan wneud ymholiadau'n lanach ac yn gyflymach i weithio gyda nhw.

Agregu Data Heb Is-ymholiadau

Agregiadau yw lle mae SQL yn dechrau teimlo'n hollol anghywir. Os ydych chi eisiau cyfrif rhywbeth, yna cyfrifwch y cyfrifiadau hynny, rydych chi'n sydyn mewn uffern cromfachau.

SQL

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;

Cystrawen Pipe

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;

Dim mwy o nythu na grwpio dwbl. Mae pob cam yn llifo'n rhesymegol a gellir ei addasu'n annibynnol heb ailysgrifennu'r ymholiad cyfan.

Glanhau a Thrawsnewid Colofnau Gam wrth Gam

Pan fydd angen i chi ysgrifennu testun mewn llythrennau bach, cyfrifo cyfansymiau, a gollwng colofnau ychwanegol, mae SQL safonol yn eich gorfodi i ailysgrifennu lluosog SELECT datganiadau. Mae cystrawen pibell yn cyflwyno SET, EXTEND, a DROP gweithredwyr fel y gallwch gymhwyso newidiadau yn olynol.

SQL

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;

Cystrawen Pipe

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

Mae pob gweithrediad yn adeiladu ar yr un blaenorol, gan ei gwneud hi'n haws olrhain trawsnewidiadau ac ailddefnyddio rhesymeg.

Hidlo Ar ôl Agregu Heb Gofio “CAEL”

Un o bethau rhyfedd SQL yw bod amseriad hidlwyr yn newid yn dibynnu ar y cymal. Rydych chi'n defnyddio WHERE cyn grwpio a HAVING ar ôl, ond mewn gwirionedd, dim ond rhesi sy'n cael eu hidlo gan y ddau. Mae cystrawen pibell yn caniatáu ichi ddefnyddio WHERE yn gyson, ni waeth ble rydych chi'n ei osod.

SQL

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

Cystrawen Pipe

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

Nawr gallwch chi ysgrifennu hidlwyr yn yr un drefn ag yr ydych chi'n meddwl amdanyn nhw: hidlwch y data yn gyntaf, yna grwpiwch ef, yna hidlwch eto ar y canlyniadau.

Ymholiadau Dadfygio Heb Dablau Dros Dro

Mewn SQL safonol, mae gwirio canlyniad canolradd yn gofyn am greu tabl dros dro neu lapio'r cod mewn sawl CTE. Gyda chystrawen pibell, gallwch redeg yr ymholiad hyd at unrhyw bwynt yn y biblinell.

SQL

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;

Cystrawen Pipe

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

Mae pob rhagddodiad o'r ymholiad yn weithredadwy ar ei ben ei hun, sy'n golygu y gallwch chi "edrych" ar y data ar unrhyw gam. Mae'n ffordd lanach a mwy rhyngweithiol o ddadfygio ac ailadrodd.

Cadwyno Modelau a Swyddogaethau Heb Nythu

Wrth weithio gyda ffwythiannau gwerth-tabl neu fodelau BigQuery ML, gall nythu fynd yn annarllenadwy'n gyflym. Mae cystrawen pibell yn disodli'r galwadau nythu hynny gyda chadwyn llinol gan ddefnyddio CALL.

SQL

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

Cystrawen Pipe

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

Os ydych chi'n defnyddio sawl model neu drawsnewidiad, rydych chi'n syml yn pentyrru llinellau CALL ychwanegol—nid oes angen cromfachau.

Troi Data Heb Ddewisiadau Nythu

Mae troi data wedi bod yn broses ddiflas erioed, gan olygu bod angen haenau o is-ymholiadau yn aml. Mae cystrawen pibell yn ei symleiddio i un dilyniant llifo.

SQL

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

Cystrawen Pipe

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

Mae'r ymholiad bellach yn darllen fel stori: dechreuwch gyda'ch data, ymunwch ag ef, dewiswch yr hyn sydd ei angen arnoch, yna newidiwch ei gylch.

Pam Mae Cystrawen Pipe yn Newid Gêm

Nid yw cystrawen pibellau yn ailddyfeisio SQL—mae'n ei egluro. Mae'n cadw popeth pwerus am strwythur datganiadol SQL ond yn dileu'r gorbenion gwybyddol o ysgrifennu rhesymeg yn ôl.

I wyddonwyr data, dadansoddwyr a pheirianwyr, mae hynny'n golygu: