Pipelined SQL in ClickHouse 26.8

Summary

ClickHouse 26.8 introduces pipelined SQL, extending the earlier ability to place FROM before SELECT with a |> operator for sequencing query transformations. Using the UK property prices dataset, the post compares conventional, FROM-first, and pipelined forms for filtering London sales, aggregating median prices by district, ordering results, and limiting output. Each pipeline stage acts as a complete-query checkpoint, while EXPLAIN SYNTAX shows that ClickHouse translates the syntax into nested standard SQL and optimizes the complete query without materializing every intermediate result. The syntax also supports EXTEND, reuse of aggregation aliases in later stages, views, and INSERT ... SELECT statements, though stage order affects which rows feed subsequent aggregations in these examples; the conclusion is that pipelines complement rather than replace conventional SQL.

Takeaways

1. 1

   Each |> stage is an explicit checkpoint where the preceding pipeline forms a complete query, making incremental construction and inspection easier.

2. 2

   EXPLAIN SYNTAX reveals nested SELECT statements generated from a pipeline, but ClickHouse optimizes the complete query rather than materializing every intermediate result.

3. 3

   Pipeline stage order changes semantics: placing LIMIT before a second aggregation makes that aggregation use only the limited intermediate rows.