Сінтаксіс канала BigQuery: як зрабіць SQL прасцейшым, хутчэйшым і больш чытэльным

Кожны, хто працаваў з SQL ведае пра адносіны любові і нянавісці, якія з гэтым звязаны. SQL — магутная, стандартызаваная і выразная мова праграмавання, але яна таксама адна з самых неінтуітыўна зразумелых для чытання і падтрымкі ў вялікіх маштабах. Чым глыбейшы ​​ваш запыт, тым цяжэй зразумець, што насамрэч адбываецца і ў якім парадку.

Гэта таму, што SQL не ідзе ў тым парадку, у якім вы думаеце. Вы пачынаеце з напісання SELECTале база дадзеных насамрэч не апрацоўвае гэта спачатку. Рухавік пачынае з FROM пункт, затым аб'ядноўвае дадзеныя, фільтруе іх, агрэгуе і толькі пазней ужывае сартаванне і праекцыю. У выніку атрымліваецца запыт, які запісваецца назад адносна таго, як яно выконваецца.

Гэта неадпаведнасць паміж сінтаксісам і выкананнем робіць SQL як кагнітыўна складаным, так і вылічальна дарагім. Аналітыкі часта абапіраюцца на падзапыты або агульныя таблічныя выразы (КТЭ) для мадэлявання патоку зверху ўніз, дадаючы непатрэбную складанасць і лішнюю лішнюю. Нават дасведчаныя карыстальнікі ў выніку адладжваюць укладзеныя блокі логікі замест таго, каб засяродзіцца на праблеме, якую яны вырашаюць.

Каб вырашыць гэта, BigQuery Функцыі supports1 выконваюцца ў тым жа парадку, у якім яны чытаюцца. Замест таго, каб пісаць логіку навыварат, вы пішаце яе крок за крокам, як канвеер дадзеных — кожны радок прадстаўляе асобнае пераўтварэнне. У выніку атрымліваецца SQL, які прасцей зразумець, прасцей адладзіць і значна больш інтуітыўна зразумелы для сучаснага аналізу дадзеных.

На малюнку вышэй паказана асноўная праблема, якую вырашае сінтаксіс канвеера. У традыцыйным SQL сінтаксічны парадак (як вы пішаце запыт) не адпавядае семантычны парадак (як база дадзеных насамрэч апрацоўвае гэта). Напрыклад SELECT адлюстроўваецца ўверсе, але рухавік не ацэньвае яго, пакуль не будуць завершаны аб'яднанне, фільтрацыя, групаванне і сартаванне.

Гэтая паслядоўнасць адлюстроўвае лагічны і сінтаксічны паток запыту BigQuery pipe — зверху ўніз, злева направа — адлюстроўваючы тое, як база дадзеных фактычна апрацоўвае дадзеныя.

• FROM: Пачатковая кропка любога канвеернага запыту. Вызначае пачатковую табліцу або падзапыт, з якога паступаюць дадзеныя. У сінтаксісе канвеера, FROM можа існаваць як асобны запыт і выступаць у якасці ўваходу ў паслядоўны канвеер.
• ДАЛУЧАЙСЯ: Пашырае бягучую табліцу дадатковымі слупкамі і радкамі з іншай табліцы або падзапыту. Можна выкарыстоўваць некалькі разоў запар (|> JOIN table USING (key)), што робіць леваглыбокія дрэвы злучэнняў чытальнымі без укладзеных падзапытаў.
• УСТАВ: Абнаўляе існуючыя значэнні слупкоў на месцы (|> SET column = expression). Функцыі, падобныя на SELECT * REPLACE(...) у стандартным SQL, але больш чытэльны і модульны пры выкарыстанні ў якасці кроку канвеера.
• ПАШЫРЫЦЬ: Дадае вылічаныя слупкі да існуючага набору даных (|> EXTEND expression AS new_column). Падобна да SELECT *, new_column у стандартным SQL, але дазваляе паступовае стварэнне вытворных палёў паміж іншымі аперацыямі, такімі як аб'яднанні і фільтры.
• ПАДЗЕННЕ: Выдаляе адзін або некалькі слупкоў з бягучага набору даных (|> DROP column_name). Эквівалентна SELECT * EXCEPT(column) у стандартным SQL і часта выкарыстоўваецца для спрашчэння вываду або памяншэння памеру прамежкавых дадзеных.
• ДЗЕ: Фільтруе радкі, якія адпавядаюць умове (|> WHERE condition). Можа з'яўляцца ў любым месцы канвеера, да або пасля агрэгацыі, што выключае неабходнасць выкарыстання розных ключавых слоў, такіх як HAVING або QUALIFY.
• АГРЭГЕТ: Выконвае поўнатаблічную або згрупаваную агрэгацыю (|> AGGREGATE SUM(column) AS total GROUP BY category). Замяняе GROUP BY і агрэгатныя функцыі ў SELECT, спрашчаючы сінтаксіс і выраўноўваючы яго з лагічным парадкам аперацый.
• ЗАРАДКА ПА: Сартуе радкі па ўзрастанні або па змяншэнні (|> ORDER BY column DESC). Вырабляе ўпарадкаваную табліцу вынікаў, за якой могуць ісці такія аператары, як LIMIT.
• ЛІМІТ: Абмяжоўвае колькасць радкоў у выніку (|> LIMIT 10). Працуе пасля ORDER BY або на неўпарадкаваных табліцах, захоўваючы натуральную паслядоўнасць фільтраў і агрэгацый.
• НАЗВА: Выконвае функцыю, якая вяртае значэнні ў табліцу, або мадэль машыннага навучання, выкарыстоўваючы бягучую табліцу ў якасці ўваходных дадзеных (|> CALL ML.PREDICT(MODEL project.model_name)). Выключае неабходнасць укладзеных выклікаў функцый, ствараючы лінейны і чытэльны працоўны працэс.
• ВЫБАРЫ: Вызначае канчатковую праекцыю слупкоў, якія трэба ўключыць у вынік (|> SELECT column1, column2). Выконвае функцыю замыкання ў канвееры, падобна да фінальнага SELECT у стандартным SQL-запыце.

Ніжэй прыведзены найбольш распаўсюджаныя сцэнарыі, у якіх сінтаксіс канала спрашчае логіку SQL, робячы запыты больш зразумелымі і хуткімі ў выкарыстанні.

Агрэгаванне дадзеных без падзапытаў

Агрэгацыі — гэта тое, дзе 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;

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;

Больш ніякіх укладзеных элементаў або падвойнага групавання. Кожны крок лагічна зыходзіць з яго і можа быць зменены незалежна адзін ад аднаго без перапісвання ўсяго запыту.

Ачыстка і трансфармацыя калон крок за крокам

Калі вам трэба пісаць тэкст у ніжнім рэгістры, падлічваць вынікі і выдаляць лішнія слупкі, стандартны SQL прымушае вас перапісваць некалькі SELECT аператары. Сінтаксіс канала ўводзіць SET, EXTEND, і DROP аператары, каб вы маглі ўжываць змены паслядоўна.

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;

Кожная аперацыя грунтуецца на папярэдняй, што спрашчае адсочванне пераўтварэнняў і паўторнае выкарыстанне логікі.

Фільтраванне пасля агрэгацыі без запамінання «НАЯЎЛЕННЯ»

Адна з асаблівасцей SQL заключаецца ў тым, што час фільтрацыі змяняецца ў залежнасці ад раздзела. Вы выкарыстоўваеце WHERE перад групаваннем і HAVING пасля, але на самой справе абодва проста фільтруюць радкі. Сінтаксіс канала дазваляе выкарыстоўваць WHERE паслядоўна, незалежна ад таго, дзе вы яго размясціце.

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;

Цяпер вы можаце пісаць фільтры ў тым жа парадку, у якім вы пра іх думаеце: спачатку фільтруйце дадзеныя, затым групуйце іх, а потым зноў фільтруйце вынікі.

Адладка запытаў без часовых табліц

У стандартным SQL праверка прамежкавага выніку патрабуе альбо стварэння часовай табліцы, альбо абгортвання кода некалькімі CTE. З дапамогай сінтаксісу канала можна выканаць запыт да любой кропкі канвеера.

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;

Кожны прэфікс запыту выконваецца асобна, што азначае, што вы можаце "падгледзець" дадзеныя на любым этапе. Гэта больш чысты і інтэрактыўны спосаб адладкі і ітэрацыі.

Злучэнне мадэляў і функцый без укладання

Пры працы з функцыямі, якія вяртаюць значэнні ў табліцу, або мадэлямі машыннага навучання BigQuery укладзенасць можа хутка стаць нечытэльнай. Сінтактыс канвеера замяняе гэтыя ўкладзеныя выклікі лінейным ланцужком з выкарыстаннем CALL.

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

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

Калі вы ўжываеце некалькі мадэляў або пераўтварэнняў, вы проста дадаяце дадатковыя радкі CALL — дужкі не патрэбныя.

Звядзенне дадзеных без укладзеных выбараў

Звядзенне дадзеных заўсёды было стомным працэсам, які часта патрабаваў слаёў падзапытаў. Сінтаксіс канвеера спрашчае яго да адной плыўнай паслядоўнасці.

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

Запыт цяпер чытаецца як гісторыя: пачніце з вашых дадзеных, аб'яднайце іх, выберыце тое, што вам трэба, а затым зрабіце паварот.

Чаму сінтаксіс канвеера змяняе правілы гульні

Сінтаксіс канала не пераасэнсоўвае SQL — ён яго ўдакладняе. Ён захоўвае ўсе магутныя магчымасці дэкларатыўнай структуры SQL, але пазбаўляе ад кагнітыўных накладных выдаткаў, звязаных з запісам логікі ў зваротным парадку.

Для спецыялістаў па апрацоўцы дадзеных, аналітыкаў і інжынераў гэта азначае:

• Прасцей чытаць, пісаць і адладжваць запыты
• Больш ніякіх маніпуляцый з падзапытамі або CTE для простых задач
• Аптымізаваная логіка, якая адлюстроўвае тое, як вы насамрэч думаеце
• Лепшая прадукцыйнасць і менш лішніх аперацый

Сінтаксіс канвеера BigQuery — гэта SQL для сучаснай эры дадзеных — лінейны, інтуітыўна зразумелы і аптымізаваны для аналітыкі рэальнага свету.

роднасны Martech Zone змест

Як зрабіць рэзервовую копію гістарычных даных Universal Analytics і зрабіць справаздачу аб іх з дапамогай GA4?

BigQuery: Як выкарыстоўваць SQL для стварэння дэманстрацыйнай ідэнтыфікацыйнай інфармацыі, імя, адраса, электроннай пошты і нумара тэлефона

Supermetrics: здабывайце, аўтаматызуйце, трансфармуйце і актывуйце свае маркетынгавыя даныя для росту бізнесу

Google Analytics 4: Як атрымаць максімальную інфармацыю, выкарыстоўваючы атрыбуты карыстальніка і дэмаграфічныя дадзеныя