Docs
Playground Open app
Docs /Querying /Pipe syntax
Querying

Pipe syntax in BigQuery

BigQuery pipe syntax writes a query as a chain of |> steps in execution order, starting with FROM. WHERE replaces HAVING and QUALIFY, AGGREGATE holds the GROUP BY, and every prefix of the query is itself a valid query.

Pipe syntax lets you write a BigQuery query as a sequence of steps, each starting with |>. The query starts with FROM, and every step takes the table produced by the step before it. You read it top to bottom in the order it runs, instead of SELECT first and FROM second. It is part of GoogleSQL, so it works in views and table functions as well as plain queries, and you can mix it with standard syntax.

The same query, both ways

We want customers with more than 10 in paid orders, largest first. In pipe syntax:

filter, aggregate, filter, sort
1234567891011
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> WHERE status = 'paid'
|> AGGREGATE SUM(amount) AS total GROUP BY customer
|> WHERE total > 10
|> ORDER BY total DESC
Result in BigQuery
customertotal
1c320
2c115
2 rows

And in standard syntax:

the same query with SELECT, WHERE, GROUP BY, HAVING
123456789101112
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
SELECT customer, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) > 10
ORDER BY total DESC
Result in BigQuery
customertotal
1c320
2c115
2 rows

The results are the same. The pipe version runs in the order you read it: filter rows, aggregate, filter the totals, sort. Standard syntax has the filter on totals in a different clause (HAVING) than the filter on rows (WHERE), and the SELECT list at the top refers to things that are only defined further down. Querylab.io converts a query from one form to the other with a keyboard shortcut (pipe syntax).

The cost is the same. A dry run of a pipe query and its standard equivalent over a public table report the same bytes:

pipe query on samples.shakespeare
123
FROM `bigquery-public-data.samples.shakespeare`
|> WHERE corpus = 'hamlet'
|> AGGREGATE SUM(word_count) AS words
BigQuery dry run

This query will process 3.6 MB when run.

the standard equivalent
123
SELECT SUM(word_count) AS words
FROM `bigquery-public-data.samples.shakespeare`
WHERE corpus = 'hamlet'
BigQuery dry run

This query will process 3.6 MB when run.

Why people use it

Every prefix of a pipe query, up to any |>, is a valid query. When a long query gives a wrong answer, delete steps from the end and see what each stage produced. In standard syntax you have to restructure the query with subqueries to look inside. In Querylab.io every |> line has a gutter icon: click it to dry-run the query up to that step, or Shift-click to run it and see its rows (partial execution).

A query can be just FROM, which is SELECT *:

FROM with a LIMIT
12345678
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> LIMIT 2
Result in BigQuery
customeramountstatus
1c110paid
2c15paid
2 rows

The operators

The main operators are SELECT, EXTEND, SET, DROP, RENAME, AS, WHERE, AGGREGATE, DISTINCT, JOIN, CALL, ORDER BY, LIMIT, UNION, INTERSECT, EXCEPT, TABLESAMPLE, WITH, PIVOT, UNPIVOT and MATCH_RECOGNIZE. Most match the standard clause of the same name. The ones with no direct counterpart are the interesting ones.

WHERE does the job of HAVING and QUALIFY

There is one filter operator, and it can go anywhere. After AGGREGATE it filters on totals, like HAVING. It also accepts window functions, so the usual QUALIFY pattern needs no extra clause:

largest order per customer with a window function in WHERE
123456789
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> WHERE ROW_NUMBER() OVER (PARTITION BY customer ORDER BY amount DESC) = 1
|> ORDER BY customer
Result in BigQuery
customeramountstatus
1c110paid
2c27refunded
3c320paid
3 rows

An aggregate function in WHERE is an error. Aggregation belongs to AGGREGATE.

EXTEND adds columns and keeps the rest

SELECT lists columns and drops everything else. EXTEND keeps all columns and adds new ones. SET replaces a column’s value, DROP removes columns and RENAME renames them:

SET, RENAME and DROP
1234567891011
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> SET amount = amount * 100
|> RENAME amount AS cents
|> DROP status
|> ORDER BY cents
Result in BigQuery
customercents
1c1500
2c2700
3c11000
4c32000
4 rows

New columns from one EXTEND can’t refer to each other. The second expression here fails:

using doubled in the same EXTENDBigQuery rejects this query
12345678
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> EXTEND amount * 2 AS doubled, doubled + 1 AS plus_one
Error in BigQuery

Unrecognized name: doubled at [8:34]

Use a second EXTEND, which sees the first one’s result:

two EXTEND steps
1234567891011
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> EXTEND amount * 2 AS doubled
|> EXTEND doubled + 1 AS plus_one
|> SELECT customer, amount, doubled, plus_one
|> ORDER BY amount
Result in BigQuery
customeramountdoubledplus_one
1c151011
2c271415
3c1102021
4c3204041
4 rows

AGGREGATE holds the GROUP BY

AGGREGATE takes the aggregate expressions and, optionally, a GROUP BY. The grouping columns appear in the output first, then the aggregates, and you don’t repeat them in a select list. A GROUP BY item can have an alias:

GROUP BY with an alias
123456789
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> AGGREGATE SUM(amount) AS total GROUP BY UPPER(status) AS state
|> ORDER BY state
Result in BigQuery
statetotal
1PAID35
2REFUNDED7
2 rows

GROUP AND ORDER BY groups and sorts by the grouping columns in one go, and each item can take ASC or DESC:

GROUP AND ORDER BY status DESC
123456789
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> AGGREGATE SUM(amount) AS total, COUNT(*) AS orders
   GROUP AND ORDER BY status DESC
Result in BigQuery
statustotalorders
1refunded71
2paid353
2 rows

There is no standalone GROUP BY step. A |> GROUP BY fails with a message that tells you where it goes:

a GROUP BY stepBigQuery rejects this query
123456789
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> AGGREGATE SUM(amount) AS total
|> GROUP BY customer
Error in BigQuery

Syntax error: GROUP BY should be part of a pipe AGGREGATE operator, without a leading pipe symbol at [9:4]

SELECT can’t aggregate either:

SUM in a SELECT stepBigQuery rejects this query
12345678
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> SELECT customer, SUM(amount) AS total
Error in BigQuery

Aggregate function SUM not allowed in pipe SELECT at [8:21]

Each step sees only the step before it

After |> SELECT customer, the amount column no longer exists. In standard syntax WHERE runs before SELECT, so it can use any column. In a pipe each step starts from the previous result:

filter on a column dropped by SELECTBigQuery rejects this query
123456789
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> SELECT customer
|> WHERE amount > 5
Error in BigQuery

Unrecognized name: amount at [9:10]

So order the steps with that in mind: filter first, then select. Use EXTEND instead of SELECT when you want to keep the other columns.

JOIN and AS

JOIN takes the pipe’s table as its left side and the table in the step as its right side. Use AS before the pipe or in the FROM to name the left side so the ON clause can refer to it:

join, then aggregate by a joined column
1234567891011
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders AS o
|> JOIN (SELECT 'c1' AS customer, 'Ana' AS name UNION ALL SELECT 'c3', 'Cy') AS c
   ON o.customer = c.customer
|> AGGREGATE SUM(o.amount) AS total GROUP BY c.name
|> ORDER BY name
Result in BigQuery
nametotal
1Ana15
2Cy20
2 rows

Set operators and PIVOT

UNION, INTERSECT and EXCEPT take the pipe’s table as the left side and the queries in parentheses as the right. See set operations for the ALL / DISTINCT rules, which are the same:

UNION ALL as a pipe step
1234567891011
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> WHERE status = 'refunded'
|> SELECT customer, amount
|> UNION ALL (SELECT 'c9', 99)
|> ORDER BY customer
Result in BigQuery
customeramount
1c27
2c999
2 rows

PIVOT and UNPIVOT are steps as well. Columns that aren’t pivoted become the grouping, as in standard PIVOT:

PIVOT as a step
123456789
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> PIVOT(SUM(amount) FOR status IN ('paid', 'refunded'))
|> ORDER BY customer
Result in BigQuery
customerpaidrefunded
1c115null
2c2null7
3c320null
3 rows

TABLESAMPLE

TABLESAMPLE exists as a step, but BigQuery only samples base tables. A step on a CTE or inline data fails the same way as in standard syntax:

TABLESAMPLE on a CTEBigQuery rejects this query
12345678
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
FROM orders
|> TABLESAMPLE SYSTEM (50 PERCENT)
Error in BigQuery

TABLESAMPLE SYSTEM can only be applied directly to base tables.

See table sampling for what can be sampled.

Mixing with standard syntax

A pipe query works anywhere a query does, and standard queries can contain pipe queries. A subquery in the FROM of a standard query can be a pipe chain:

a pipe subquery inside a standard query
12345678910111213
WITH orders AS (
  SELECT 'c1' AS customer, 10 AS amount, 'paid' AS status UNION ALL
  SELECT 'c1', 5, 'paid' UNION ALL
  SELECT 'c2', 7, 'refunded' UNION ALL
  SELECT 'c3', 20, 'paid'
)
SELECT customer, total
FROM (
  FROM orders
  |> AGGREGATE SUM(amount) AS total GROUP BY customer
)
WHERE total > 10
ORDER BY customer
Result in BigQuery
customertotal
1c115
2c320
2 rows

Limits

Google’s guide lists one: you can’t put a differential privacy clause in a SELECT after a pipe operator. Write the clause in standard syntax and add the pipe steps after the query.

A pipe query can stand wherever a query does. We ran CREATE TABLE ... AS FROM ... |> SELECT ..., INSERT INTO ... FROM ... |> SELECT ... and a pipe query inside parentheses as a subquery, and all three worked.