Changelog category:

• Experimental Feature

Changelog entry:

Parser supports pipelined SQL syntax introduced by Google [1]. This syntax allows users to build queries top-to-bottom instead of current inside-out flow. Comparison of syntaxes can be found below.

allow_experimental_pipe_syntax setting enables ParserPipelinedQuery which for now supports filters, sortings, limitings, aggregations and joins. Parser produces the same AST as default parser so no changes on optimizer and executor is needed.

Related issue: #69364

Documentation entry for user-facing changes

Pipe syntax adds a |> operator that allows users to process data sequentially. Queries start with standalone FROM clause and connect operators with pipes |>. Scope of subsequent clause is limited to output of previous operator.

Settings:

• allow_experimental_pipe_syntax - enables parsing of |> pipe operators (default = false)

Supported operations:

• |> WHERE <expr> - apply filters on rows
• |> ORDER BY <expr> - apply sorting
• |> LIMIT <n> [OFFSET <m>] - truncate output
• |> AGGREGATE <agg_expr> - full-table aggregation
• |> AGGREGATE <agg_expr> GROUP BY <grouping_expr> - aggregation with grouping
• |> [LEFT|...] JOIN <table_expr> [ON <condition>] - join operation

Examples:

Query with standard SQL syntax

SELECT * FROM (
    SELECT number % 2 AS k, count() AS c
    FROM numbers(6)
    GROUP BY number % 2
) WHERE k = 1;

Pipelined equivalent

FROM numbers(6)
|> AGGREGATE number % 2 AS k, count() AS c GROUP BY number % 2
|> WHERE k = 1;

TPC-H 13 query translation:

:) set allow_experimental_pipe_syntax=1;
:) ... # initialize TPC-H databases
:) FROM customer
   |> LEFT OUTER JOIN orders ON c_custkey = o_custkey AND o_comment NOT LIKE '%special%requests%'
   |> AGGREGATE COUNT(o_orderkey) AS c_count GROUP BY c_custkey
   |> AGGREGATE COUNT(*) AS custdist GROUP BY c_count
   |> ORDER BY custdist DESC, c_count DESC;

SELECT c_count, COUNT(*) AS custdist
FROM
(
    SELECT
        c_custkey, COUNT(o_orderkey) AS c_count
    FROM customer
    LEFT JOIN orders ON (c_custkey = o_custkey) AND (o_comment NOT LIKE '%special%requests%')
    GROUP BY c_custkey
) AS _pipe_subquery_0
GROUP BY c_count
ORDER BY custdist DESC, c_count DESC

[1] - SQL Has Problems. We Can Fix Them: Pipe Syntax in SQL