Синтаксисът на канала на BigQuery: Правим SQL по-лесен, по-бърз и по-четлив

Уилям КарНоември 03, 2025

Прочети 6 минути

Как да използвате синтаксиса на BigQuery Pipe

Всеки, който е работил с SQL познава връзката „любов-омраза“, която идва с това. SQL е мощен, стандартизиран и изразителен, но е и един от най-неинтуитивните езици за четене и поддръжка в голям мащаб. Колкото по-дълбока става заявката ви, толкова по-трудно става да се разбере какво всъщност се случва и в какъв ред.

Това е така, защото SQL не се изпълнява в реда, в който си мислите. Започвате с писане SELECT, но базата данни всъщност не обработва това първо. Двигателят започва с FROM клауза, след това обединява данните, филтрира ги, агрегира ги и едва по-късно прилага сортиране и проекция. Резултатът е заявка, която е написана назад спрямо начина, по който е изпълнено.

Това несъответствие между синтаксиса и изпълнението прави SQL едновременно когнитивно тежък и изчислително скъп. Анализаторите често разчитат на подзаявки или Common Table Expressions (CTEs) за симулиране на поток отгоре надолу, добавяйки ненужна сложност и излишно количество информация. Дори опитни потребители в крайна сметка отстраняват грешки във вложени логически блокове, вместо да се фокусират върху проблема, който решават.

За да се справите с това, BigQuery supports1 се изпълняват в същия ред, в който се четат. Вместо да пишете логиката отначало навън, я пишете стъпка по стъпка, като конвейер за данни – всеки ред представлява отделна трансформация. Резултатът е SQL, който е по-лесен за разбиране, по-лесен за отстраняване на грешки и много по-интуитивен за съвременен анализ на данни.

Синтаксис на Google Pipe 1 — Източник: Google

Изображението по-горе илюстрира основния проблем, който решава синтаксисът на pipe. В традиционния SQL, синтактичен ред (както пишете заявка) е несъответстващо на семантичен ред (как базата данни всъщност го обработва). Например, SELECT се появява най-отгоре, но двигателят не го оценява, докато не се извърши обединяването, филтрирането, групирането и сортирането.

Тази последователност представлява логическия и синтактичен поток на заявка в BigQuery pipe – отгоре надолу, отляво надясно – отразявайки как базата данни действително обработва данните.

ОТ: Началната точка на всяка заявка за канал. Дефинира началната таблица или подзаявка, от която изтичат данните. В синтаксиса на канала, 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 заявка.

По-долу са най-често срещаните сценарии, при които синтаксисът на pipe опростява 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 оператори, така че да можете да прилагате промените последователно.

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;

Синтаксис на вертикалната черта

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 последователно, независимо къде го поставяте.

SQL

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. С помощта на синтаксис на канала можете да изпълните заявката до всяка точка в канала.

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;

Синтаксис на вертикалната черта

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

Всеки префикс на заявката е изпълним самостоятелно, което означава, че можете да „надникнете“ в данните на всеки етап. Това е по-чист и по-интерактивен начин за отстраняване на грешки и итерации.

Верижно свързване на модели и функции без влагане

Когато работите с функции, които генерират таблични стойности, или BigQuery ML модели, влагането може бързо да стане нечетливо. Синтаксисът на канала замества тези вложени извиквания с линейно верижно свързване, използвайки CALL.

SQL

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

Синтаксис на вертикалната черта

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

Ако приложите множество модела или трансформации, просто подреждате допълнителни CALL редове – не са необходими скоби.

Завъртане на данни без вложени селекции

Обръщането на данни винаги е било досаден процес, често изискващ слоеве от подзаявки. Синтаксисът на канала го опростява в една плавна последователност.

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

Синтаксис на вертикалната черта

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, но премахва когнитивните разходи, свързани с писането на логиката наобратно.

За специалистите по данни, анализаторите и инженерите това означава: