Pipe syntax

BigQuery's pipe syntax writes a query as a top-to-bottom chain of steps joined by |>, in the order they run. Querylab.io parses it, completes it, converts standard SQL to pipe and back with one shortcut, and lets you run any prefix of the chain to see intermediate rows.

FROM `ace-analytics.warehouse.orders`
|> WHERE order_date >= '2024-01-01' AND status = 'completed'
|> AGGREGATE COUNT(*) AS orders, SUM(amount) AS revenue
 GROUP BY user_id
|> ORDER BY revenue DESC
|> LIMIT 20

The same query in standard SQL puts the SELECT first and the FROM after it, which is the reverse of what BigQuery does with it. Both forms run identically and cost the same; pipe syntax is a way of writing, not a different engine.

SELECT user_id, COUNT(*) AS orders, SUM(amount) AS revenue
FROM `ace-analytics.warehouse.orders`
WHERE order_date >= '2024-01-01' AND status = 'completed'
GROUP BY user_id
ORDER BY revenue DESC
LIMIT 20

Converting between standard and pipe syntax

Standard to pipe and pipe to standard are both one action. They work on the whole document or, if you have a selection, on the selection alone.

The same two commands are in the editor’s right-click menu, and the ⋮ menu at the top right of the editor shows the one that applies to the query in the tab:

Convert to Pipe is a faithful translation: every CTE and subquery stays where it was. Convert to Pipe (Optimized) runs an extra pass that flattens subqueries, inlines single-use CTEs into the chain, and drops SELECT steps that only re-list existing columns: the idiomatic form you’d write by hand.

WITH completed AS (
SELECT user_id, amount
FROM `ace-analytics.warehouse.orders`
WHERE status = 'completed'
)
SELECT user_id, SUM(amount) AS revenue
FROM completed
GROUP BY user_id
HAVING SUM(amount) > 1000

FROM `ace-analytics.warehouse.orders`
|> WHERE status = 'completed'
|> AGGREGATE SUM(amount) AS revenue
 GROUP BY user_id
|> WHERE revenue > 1000

HAVING has no pipe operator: it becomes a WHERE after AGGREGATE, which can use the aggregate’s alias.

• The statement has a FROM. SELECT 1 + 1 has nothing to pipe from and is left unchanged.
• For a large script, select one statement and convert just that.

The same converter runs without an account at querylab.io/tools/pipe-to-sql.

Running part of the chain

Every |> gets an icon in the gutter. Hover it for the SQL up to that step; click to dry-run that prefix, Shift-click to run it and see its rows in the result grid. That is the fastest way to find which step lost the rows you expected. See partial execution.

FROM `ace-analytics.warehouse.orders`
|> WHERE status = 'completed'
|> AGGREGATE COUNT(*) AS orders GROUP BY user_id
|> ORDER BY orders DESC

Every operator and its standard equivalent

All 21 BigQuery pipe operators parse, complete, and convert. Where standard SQL has an equivalent it’s in the first column.

Column operators without a standard form

EXTEND, SET, DROP and RENAME change the column list without restating it. Converting to standard SQL expands them into a SELECT with the full list.

FROM `ace-analytics.warehouse.users`
|> EXTEND UPPER(country) AS country_code
|> SET email = LOWER(email)
|> DROP password_hash, salt
|> RENAME created_at AS signup_at

Aggregating

Grouping columns are listed once, after the aggregates, and are included in the output. ROLLUP, CUBE and GROUPING SETS work as in standard SQL. GROUP AND ORDER BY groups and sorts by the same keys in one step; a DESC or ASC suffix on an aggregate sorts by it.

FROM `ace-analytics.warehouse.orders`
|> AGGREGATE SUM(amount) AS total DESC
 GROUP AND ORDER BY region

Set operations

The right-hand query goes in parentheses; several can follow one keyword, separated by commas. BY NAME matches columns by name instead of position.

FROM `ace-analytics.warehouse.users`
|> SELECT user_id, email
|> UNION ALL BY NAME
   (SELECT email, user_id FROM `ace-analytics.warehouse.admins`),
   (FROM `ace-analytics.warehouse.partners` |> SELECT user_id, email)

WITH inside the chain

A pipe WITH defines CTEs mid-chain, passes its input through unchanged, and makes the CTEs available to the steps after it.

FROM `ace-analytics.warehouse.orders`
|> WHERE status = 'completed'
|> WITH regions AS (FROM `ace-analytics.warehouse.regions` |> SELECT id, name)
|> JOIN regions ON orders.region_id = regions.id
|> SELECT regions.name, orders.amount

Matching row patterns

MATCH_RECOGNIZE is row-pattern matching over an ordered partition: PATTERN uses regex-style quantifiers over symbols that DEFINE describes as row conditions, and MEASURES says what each match returns.

FROM `ace-analytics.warehouse.stock_prices`
|> MATCH_RECOGNIZE(
   PARTITION BY ticker
   ORDER BY trade_date
   MEASURES FIRST(price) AS start_price, LAST(price) AS end_price
   PATTERN (dip+ rise+)
   DEFINE dip AS price < PREV(price),
          rise AS price > PREV(price)
 )

Mixing pipe and standard syntax

Pipe operators can follow a standard query, and a pipe query can be a CTE or a subquery in a standard one. The converter handles both directions of that, and diagnostics, hover and completions work inside either part.

WITH cleaned AS (
FROM `ace-analytics.warehouse.events`
|> WHERE event_date >= '2024-01-01'
|> SELECT user_id, event_type
)
SELECT user_id, COUNT(*) AS events
FROM cleaned
GROUP BY user_id
|> WHERE events > 10

Formatting a pipe query

The formatter keeps the chain one step per line. Settings → Editor → SQL Formatting → Pipe Operator Style chooses between Standard (pipes indented with the content) and Left-Aligned (|> in column 0, keywords indented). See SQL formatting.