Pipe Syntax Conversion

Convert between standard SQL and BigQuery's pipe syntax.

---

What is Pipe Syntax?

A linear, data-flow oriented way to write SQL using the |> operator.

Standard SQL:

SELECT user_id, COUNT(*) AS orders
FROM orders
WHERE status = 'completed'
GROUP BY user_id
ORDER BY orders DESC;

Pipe Syntax:

FROM orders
|> WHERE status = 'completed'
|> AGGREGATE COUNT(*) AS orders GROUP BY user_id
|> ORDER BY orders DESC;

Pipe syntax reads top-to-bottom in execution order.

---

Conversion Shortcuts

Action	Mac	Windows/Linux
Convert to Pipe	Cmd+Alt+P	Ctrl+Alt+P
Convert to Standard	Cmd+Alt+S	Ctrl+Alt+S

Works on full document or selection.

---

Pipe Operations

Standard SQL	Pipe Syntax
SELECT cols FROM table	FROM table |> SELECT cols
WHERE condition	|> WHERE condition
GROUP BY cols + aggregates	|> AGGREGATE ... GROUP BY cols
HAVING condition	|> WHERE condition (after AGGREGATE)
JOIN table ON ...	|> JOIN table ON ...
ORDER BY cols	|> ORDER BY cols
LIMIT n	|> LIMIT n

---

Pipe-Only: EXTEND

Add computed columns without listing all existing columns.

FROM users
|> EXTEND UPPER(name) AS name_upper;

Keeps all columns and adds name_upper.

---

Mixed Syntax

BigQuery supports mixing standard and pipe syntax in the same query.

WITH cleaned AS (
  FROM raw_events
  |> WHERE event_date >= '2024-01-01'
  |> SELECT user_id, event_type
),
stats AS (
  SELECT user_id, COUNT(*) AS count
  FROM cleaned
  GROUP BY user_id
)
SELECT * FROM stats;

---

Limitations

• SELECT without FROM cannot convert to pipe (pipe requires FROM)
• UNION/INTERSECT/EXCEPT have limited support
• Use partial conversion (select portion, then convert) for complex queries