Pipe Operators

Documentation for pipe operators

Pipe operators allow writing queries as a linear chain of transformations that reads from top to bottom, similar to the pipe syntax of GoogleSQL:

FROM orders
|> WHERE cancelled = 0
|> AGGREGATE sum(amount) AS total GROUP BY customer
|> ORDER BY total DESC
|> LIMIT 3

Any SELECT query can be followed by a chain of pipe operators. Each operator starts with the |> token, takes the result of the query before it as input, and applies one more transformation to it. Inside every operator, the regular ClickHouse syntax is used.

Pipe operators are a syntax extension: every operator wraps the query before it into a subquery, so the resulting AST is the same as the AST of the equivalent query written with nested subqueries, and the query above is equivalent to:

SELECT * FROM
(
    SELECT customer, sum(amount) AS total FROM
    (
        SELECT * FROM
        (
            SELECT * FROM orders
        )
        WHERE cancelled = 0
    )
    GROUP BY customer
)
ORDER BY total DESC
LIMIT 3

FROM queries

A query can start with the FROM clause, and the SELECT clause is optional in such queries - when it is omitted, the query works as if SELECT * was written:

FROM orders;
FROM orders WHERE amount > 100;
FROM orders |> WHERE amount > 100;

Table aliases can be written with or without the AS keyword, as in the FROM clause of an ordinary SELECT query: FROM orders o WHERE o.amount > 100. The only exception is an alias written as the bare word select: after the tables it starts the explicit SELECT clause instead of being treated as an alias. A table named select is unaffected and keeps its own alias: FROM select s WHERE s.id = 1.

The SELECT clause cannot be omitted when the sample offset of the last table could also be read as a query-level OFFSET, because in FROM t SAMPLE 1/10 OFFSET 5 the OFFSET belongs to SAMPLE, while in FROM t SAMPLE 1/10 SELECT * OFFSET 5 it is a query-level OFFSET - the explicit SELECT is required to disambiguate the two. When the query continues with a clause that a query-level OFFSET cannot precede, there is no ambiguity and the SELECT clause is optional as usual: FROM t SAMPLE 1/10 OFFSET 5 WHERE x > 0, FROM t SAMPLE 1/10 OFFSET 5 JOIN dim USING (id).

Operators

WHERE

|> WHERE condition filters the input rows. When it is applied after an aggregation, it works like HAVING:

FROM orders
|> AGGREGATE sum(amount) AS total GROUP BY customer
|> WHERE total > 100

SELECT

|> SELECT [DISTINCT] expr1 [AS alias1], ... leaves only the listed expressions as the output columns:

FROM orders |> SELECT customer, amount * 2 AS doubled

A trailing comma is allowed at the end of the list of expressions in the same positions as in the SELECT clause of an ordinary query - here it can be followed by the end of the query or by the next |> operator: FROM orders |> SELECT customer, amount, |> LIMIT 1. The same applies to the EXTEND and AGGREGATE operators.

EXTEND

|> EXTEND expr1 [AS alias1], ... appends the listed expressions to the input columns; it is equivalent to SELECT *, expr1 AS alias1, ...:

FROM orders |> EXTEND amount * 10 AS big

SET

|> SET column1 = expr1, ... replaces the values of the listed columns; it is equivalent to SELECT * REPLACE (expr1 AS column1, ...):

FROM orders |> SET amount = amount + 1000

DROP

|> DROP column1, ... removes the listed columns; it is equivalent to SELECT * EXCEPT (column1, ...):

FROM orders |> DROP cancelled

AS

|> AS alias gives an alias to the input of the next operator, so it can be referenced in that operator, which is mostly useful for joins:

FROM orders
|> AGGREGATE sum(amount) AS total GROUP BY customer
|> AS agg
|> JOIN orders AS o ON agg.customer = o.customer

AGGREGATE

|> AGGREGATE agg1 [AS alias1], ... [GROUP BY expr1 [AS alias1], ...] aggregates the input rows. The output columns are the grouping columns followed by the aggregate columns. Without GROUP BY, the whole input is aggregated to a single row:

FROM orders |> AGGREGATE count() AS c, sum(amount) AS total GROUP BY customer;
FROM orders |> AGGREGATE count() AS c;

DISTINCT

|> DISTINCT removes duplicate rows; it is equivalent to SELECT DISTINCT *.

ORDER BY

|> ORDER BY expr1 [ASC/DESC], ... sorts the input rows. The full ORDER BY clause syntax is supported, including ORDER BY ALL, WITH FILL, and INTERPOLATE:

FROM orders |> ORDER BY amount DESC;
FROM orders |> SELECT customer, amount |> ORDER BY ALL;
FROM points |> ORDER BY x WITH FILL FROM 1 TO 10 INTERPOLATE (y AS y + 1)

LIMIT and OFFSET

|> LIMIT length [OFFSET offset] and |> OFFSET offset limit the number of rows:

FROM orders |> ORDER BY amount DESC |> LIMIT 3 OFFSET 1

JOIN and ARRAY JOIN

|> [GLOBAL] [ANY/ALL/ASOF/SEMI/ANTI] [INNER/LEFT/RIGHT/FULL/CROSS] JOIN table [ON expr | USING (columns)] joins the input with another table, subquery, or table function. All kinds of JOIN and ARRAY JOIN are supported, and a single operator can contain several joins, like a FROM clause:

FROM customers
|> AS c
|> LEFT JOIN orders AS o ON c.name = o.customer
|> ARRAY JOIN tags

Since every operator is a new subquery scope, table aliases are visible only inside the same operator (in the ON condition). The following operators see the combined columns of the join result, as after SELECT *.

The comma spelling of a cross join is supported as well, with the input of the operator as the left side: FROM customers |> AS c |> , orders. As with the other joins, the input needs an alias when the joined_subquery_requires_alias setting is enabled (it is by default).

As in the FROM clause of an ordinary query, a comma (cross) join is not supported right after an ARRAY JOIN: a comma after the ARRAY JOIN always belongs to its expression list.

UNION, INTERSECT, and EXCEPT

|> UNION [ALL/DISTINCT] (query1) [, (query2), ...], |> INTERSECT [ALL/DISTINCT] ..., and |> EXCEPT [ALL/DISTINCT] ... combine the input with the results of other queries:

FROM orders
|> SELECT customer
|> UNION ALL (FROM customers |> SELECT name)
|> DISTINCT

The parentheses around an operand are optional for a single query, but they are required when the chain continues with another pipe operator after the set operation - otherwise it would be unclear whether the next operator applies to the last operand or to the whole result.

Notes

• The WITH clause of the query stays visible in all following pipe operators, both for scalar aliases and for CTEs: WITH 10 AS threshold FROM t |> WHERE x < threshold.
• In INSERT ... SELECT, a WITH clause written before INSERT is attached to the outermost generated SELECT, and it reaches the inner pipe stages during interpretation via the enable_global_with_statement setting (enabled by default) — the same way it reaches a hand-written nested subquery. If that setting is disabled, aliases and CTEs from an INSERT-scoped WITH are not visible inside the pipe stages, exactly as they are not visible inside a hand-written subquery.
• Like any SELECT query, the query generated by a pipe operator can end with a SETTINGS clause, which is attached to that generated query: FROM t |> LIMIT 1 SETTINGS max_threads = 1 is the same as SELECT * FROM (SELECT * FROM t) LIMIT 1 SETTINGS max_threads = 1. This also works where there is no separate pass for query settings, such as in a subquery, in CREATE VIEW, or in the view table function. A SETTINGS clause in the middle of a chain stays on its stage, which becomes a subquery of the next operator. After a set operation with a parenthesized operand, a trailing SETTINGS is not accepted - the equivalent query with subqueries cannot have a SETTINGS clause in that position either.
• A SETTINGS clause of the query before the first pipe operator stays on that query, which becomes a subquery of the generated wrapper. Ordinary settings keep working, because settings of a subquery are applied when that subquery is interpreted. The only exception is the pair of settings that select the query analyzer, enable_analyzer and its alias allow_experimental_analyzer: changing them in a subquery is not allowed, so SELECT number FROM numbers(1) SETTINGS enable_analyzer = 0 |> LIMIT 1 throws INCORRECT_QUERY — exactly as the equivalent hand-written SELECT * FROM (SELECT number FROM numbers(1) SETTINGS enable_analyzer = 0) LIMIT 1 does. Write these two settings after the last pipe operator, or pass them outside of the query.
• Pipe operators bind to the whole query before them, including set operations: in SELECT 1 UNION ALL SELECT 2 |> AGGREGATE count(), the aggregation is applied to the result of the UNION ALL. To continue a query with UNION after a pipe operator, use the |> UNION operator or parentheses.
• Pipe operators can be used everywhere a SELECT query is expected: in subqueries, in INSERT ... SELECT (including the form INSERT INTO t FROM src |> ...), in CREATE VIEW, in the view table function, and so on.
• The renaming of columns in place is not provided as a separate operator; use |> SELECT * EXCEPT (old_name), old_name AS new_name or the SET and DROP operators.