8 ms·

SQL pipe syntax available in public preview in BigQuery

247 points by marcyb5st 2y ago

iddan 2y ago
This is such an improvement. Can’t wait for it to get into Postgres
whalesalad 2y ago
Who invented pipe syntax? Elixir? Or is there another runtime or language that made this prevalent beforehand?
- ellisv 2y ago
  The |> syntax? Perhaps F#. Pipes have been around since at least Unix v3.
  - _huayra_ 2y ago
    Many such things from F# come from Haskell (given the influence from MSR UK, specifically Simon Peyton Jones before he left a few years ago), so likely Haskell or earlier imo (at least in terms of "complex" "pipe" operations that have higher-level operations than "bytes in, bytes out" of Unix).
    - ubertaco 2y ago
      Worth noting that F# started out life as an implementation of OCaml for the .NET runtime [1], so most likely the pipe syntax was taken from there, although the pipeline-of-functions construction is much older than that [2] [1] https://softwareengineering.stackexchange.com/questions/209984/why-was-f-created https://softwareengineering.stackexchange.com/questions/2099... [2] https://retrocomputing.stackexchange.com/questions/17335/what-was-the-earliest-use-of-pipe-in-programming https://retrocomputing.stackexchange.com/questions/17335/wha...
      - grndn 2y ago
        OCaml took the '|>' pipe symbol from F#. And F# was the language that made the '|>' pipe symbol popular in mainstream programming (as opposed to the unix '|' pipe symbol), afaik. According to Don Syme, it was used in F# in 2003 (see "Early History of F#", section 9.1, [1] which references [2]). Here's his full comment: /quote Despite being heavily associated with F#, the use of the pipeline symbol in ML dialects actually originates from Tobias Nipkow, in May 1994 (with obvious semiotic inspiration from UNIX pipes) [archives 1994; Syme 2011]. ... I promised to dig into my old mail folders to uncover the true story behind |> in Isabelle/ML, which also turned out popular in F#... In the attachment you find the original mail thread of the three of us [ Larry Paulson; Tobias Nipkow; Marius Wenzel], coming up with this now indispensable piece of ML art in April/May 1994. The mail exchange starts as a response of Larry to my changes. ...Tobias ...came up with the actual name |> in the end... /endquote Haskell has had "$" or "backwards pipe" for ages, but that is just another way of doing function application and it does not feel the same as (and is not used the same way as) the unix-style piping paradigm. [1] https://fsharp.org/history/hopl-final/hopl-fsharp.pdf https://fsharp.org/history/hopl-final/hopl-fsharp.pdf [2] https://web.archive.org/web/20190217164203/https://blogs.msdn.microsoft.com/dsyme/2011/05/17/archeological-semiotics-the-birth-of-the-pipeline-symbol-1994/ https://web.archive.org/web/20190217164203/https://blogs.msd...
- xnx 2y ago
  Originally? Douglas McIlroy for Unix in 1973: https://en.wikipedia.org/wiki/Pipeline_(Unix) https://en.wikipedia.org/wiki/Pipeline_(Unix)
  - jmount 2y ago
    I agree. And then functional languages pick up the pipe, as it is roughly "poor man's method chaining" (so the object oriented languages don't need it).
- nothrabannosir 2y ago
  Shell would at least qualify as prior art, I’d assume
- grndn 2y ago
  Elixir took the pipe symbol from F# (See Jose's comment at [1]). For more details see my other comment below. [1] https://elixirforum.com/t/which-language-first-introduced-the-pipe-operator/16791/8 https://elixirforum.com/t/which-language-first-introduced-th...
pjmlp 2y ago
Ever since using Kusto Query Language, that I look forward to SQL getting something like this, maybe there is hope if enough DBs have it as an extension.
- DarkCrusader2 2y ago
  I heavily use KQL on a daily basis and want nothing more than using it for Postgres and SQlite as well.
Taikonerd 2y ago
This is so exciting. I hope Postgres and MySQL get some level of support for it, too.
notpushkin 2y ago
PRQL is a similar idea, and it compiles to SQL: https://prql-lang.org/ https://prql-lang.org/ from invoices filter invoice_date >= @1970-01-16 derive { transaction_fees = 0.8, income = total - transaction_fees } filter income > 1
- mritchie712 2y ago
  there's an duckdb extension[0] for it too 0 - https://duckdb.org/community_extensions/extensions/prql.html https://duckdb.org/community_extensions/extensions/prql.html
- throwaway127482 2y ago
  I think I like this new pipe syntax a lot better than PRQL. Feels like it has the potential to become officially supported by DBs like MySQL since it's a relatively small change to existing SQL syntax.
- gorkaerana 2y ago
  I'm tempted to give it a try just for the "For HackerNews enthusiasts" section of their landing page, which states "The PRQL compiler is written in Rust" and "We talk about “orthogonal language features” a lot". Love me some banter in technical documentation :D
- benrutter 2y ago
  I love the design behind PRQL, was a little dissapointed when I tried using it with an MSSQL server and found there was no decent way to filter for `LIKE '%something%'`. (PRQL uses regex for stuff like this, which sounds great but then isn't supported on some databases like MSSQL)
  - default-kramer 2y ago
    I'm very surprised to learn that PRQL does not natively support `like`, but you can add it yourself: https://github.com/PRQL/prql/issues/1123#issuecomment-1353859034 https://github.com/PRQL/prql/issues/1123#issuecomment-135385...
Taikonerd 2y ago
Relatedly, PRQL is a lovely pipe-oriented syntax for SQL DBs: https://prql-lang.org/ https://prql-lang.org/ Their syntax is a lot cleaner, because it's a new language -- it's not backwards-compatible with SQL. But then again, they don't have the resources of Google behind them.
- systems 2y ago
  if its not backward compatible with SQL, is not pipe syntax for SQL its just a new Query language most DBMS have proprietary extension to SQL, its messy, real success would be adding the new syntax to the standard and have implemented across multiple DBMSs
  - Taikonerd 2y ago
    Well, OK. PRQL is "for SQL DBs" in the sense that you can translate it to SQL on the client side, and then execute it on regular Postgres, MySQL, etc.
    - RyanHamilton 2y ago
      That's exactly what qstudio offers: https://www.timestored.com/qstudio/prql-ide https://www.timestored.com/qstudio/prql-ide a desktop client that translates prql to run against many databases.
deleted 2y ago
[deleted]
epistasis 2y ago
This is beautiful. I first encountered this style of data manipulation in R's tidyverse. Some say C# (or F#?)had similar pipes and influences, but I haven't seen specifics. Some other data libraries like Polars have sort of similar parse trees, but they also have lots of extra cruft because of limitations to the underlying language. Python's functional calling semantics are extremely powerful, but not quite as powerful as R's.
- jddj 2y ago
  The C# and F# bit is LINQ (language integrated query) and it's a lovely piece of engineering.
  - ziml77 2y ago
    The LINQ syntax was an interesting experiment but the value of that syntax always felt minimal to me. Like it looked out of place in the middle of my C# code, so instead I just use the extension methods.
    - neonsunset 2y ago
      It grows on you after you start using F# too
nicoritschel 2y ago
Also is in DuckDB https://github.com/ywelsch/duckdb-psql https://github.com/ywelsch/duckdb-psql
- conradev 2y ago
  and SQLite is waiting on Postgres: https://news.ycombinator.com/item?id=41385005 https://news.ycombinator.com/item?id=41385005
jmull 2y ago
I like it, but I'm not sure it's worth the trouble of further expanding an ever-ballooning extended family of SQL syntaxes. Of course, SQL has numerous issues, both in an absolute sense and relative to what we've come to expect. And the obvious course of action in each individual case is to add syntax to support the missing feature. But as you keep adding syntax, SQL gets more and more complex, both in the individual variants and due to the complexities of multiple variants with different syntax support at different times. A transpiling approach makes more sense to me. I'd like the sql implementors to focus on source maps and other things to better support plugging in external, alternate syntaxes (is a standardized mechanism too much to ask for?). Then individual projects/people/efforts can choose the SQL syntax variant that works for them, and it can be one that evolves separate from the host db.
- _dark_matter_ 2y ago
  There are a lot of transpilers, and they don't get much adoption because the people implementing them are not the people implementing the database. The database and the query language are tightly coupled. The latter determines the kinds of optimizations that the former can do, and a transpiled language means that optimization info needs to be passed through in weird and inefficient ways; it also means that obvious optimizations aren't possible, because the people building the language are not building the db. If you've ever tried used query hints, or deeply inspected a distributed query plan, you'll know exactly what I mean.
- 9rx 2y ago
  > A transpiling approach makes more sense to me. That's the solution we've been using, oft referred to as ORM, to work around the lack of this syntax. But then you had to live in a world where you had an almost SQL, but not really, language that doesn't always quite map perfectly to SQL and all the pain to go along with that. Now instead of that song and dance you can compose queries with plain SQL and simple string concatenation. That is a huge win, even if troubling that it has taken so long (and still not widely supported).
  - jmull 2y ago
    Generally, ORM's try to present a high-level abstraction of your data and data model than the engine does natively. That makes it the ORM's responsibility to implement the abstraction in a good way. Problems come in when the "good" way depends on factors the ORM doesn't consider or expose well. I actually mean transpiler in a mainly syntactic sense, and would want it to avoid higher-level abstractions (after all, as you point out, ORMs have that ground covered).
beoberha 2y ago
Somehow Microsoft (my employer) has totally failed in evangelizing Kusto to the masses. KQL is by far the best query language you’ll ever use. This syntax from Google is nice but it’s still just SQL.
- summarity 2y ago
  Plus one for Kusto. I’ve never had this much success teaching people data analysis. It’s good for simple obvious and powerful composed queries. Though I work at GitHub so still too close to the mothership for an unbiased opinion. There’s some Apache energy around KQL though and a few open source parsers so there’s hope yet.
- default-kramer 2y ago
  I remain unimpressed by KQL. Comparing SQL to KQL is approximately like comparing Java to C#. Yeah it's better in many ways, but at the end of the day it doesn't make a huge difference. I want to go from Java to Lisp. Consider how many of these complaints are equally valid against KQL: https://www.scattered-thoughts.net/writing/against-sql/ https://www.scattered-thoughts.net/writing/against-sql/
  - bradford 2y ago
    Totally disagree, I've used KQL for about 10 years now, and SQL for 20. Given the choice, I'll always prefer KQL. Sorry, I don't have time for a thorough rebuttal of all the topics mentioned in the link you provided, but if I had to bring up a few counterpoints: 1. (Can't be expressed) KQLs dynamic datatype handles JSON much better than SQLs language additions. 2. (variables/Fragile structure/Functions) KQL fixes many of the orthogonality issues in SQL. (Specifically: both variable assignments and function parameters can accept scalar and tabular values in a similiar way, where-as SQL uses different syntax for each) (disclaimer, msft employee)
  - beoberha 2y ago
    Eh, I don’t need Lisp. I need to do my job. KQL allows me to write queries intuitively and others to easily understand what I’m doing. The summarize operator is also a game changer. I use something like “ | summarize count() by bin(TIMESTAMP, 1h) “ multiple times daily. Getting the same result in SQL is disgusting.
schultzer 2y ago
When I first started working in Elixir I loved Ecto and the pipe syntax, but now I want a SQL sigil, that wont care about where I put my where or from clauses. Why are SQL parsers even concerned with that? Isn’t that why we have RD parser.
mwexler 2y ago
Though not a pipe syntax, the Malloy language has some similar analytic sugar in its syntax. Malloy is from Lloyd Tabb, a co-founder of Looker. https://www.malloydata.dev/ https://www.malloydata.dev/
mccanne 2y ago
Really cool though typing ">" after "|" is a pain https://github.com/brimdata/super/blob/main/docs/language/pipe-ambiguity.md https://github.com/brimdata/super/blob/main/docs/language/pi...
- valyala 2y ago
  Agreed! LogsQL uses `|` as pipe delimiter too - https://docs.victoriametrics.com/victorialogs/sql-to-logsql/ https://docs.victoriametrics.com/victorialogs/sql-to-logsql/
willvarfar 2y ago
When the pipe syntax was first published last year the SQLite team did a quick hack to try it out. https://sqlite.org/forum/forumpost/5f218012b6e1a9db https://sqlite.org/forum/forumpost/5f218012b6e1a9db (Note that the fiddle linked no longer supports the syntax) It is very interesting that they found it completely unnecessary to actually require the pipe characters. The grammar works when the pipe characters are optional. And, imo, looks a lot better!
- specialist 2y ago
  Good. I vote for omitting the pipe lexemes. I hate them. Of the alternatives linked (elsethread), PSQL is the least bad. I'm ambivalent about the curly brackets; will have to ponder. Malloy would be improved by omitting the superfluous (JSON-esque) semicolons.
ajfriend 2y ago
I have project that's still very much at the experimental stage, where I try to get something similar to this pipe syntax by allowing users to chain "SQL snippets" together. That is, you can use standalone statements like `where col1 > 10` because the `select * from ...` is implied. https://ajfriend.github.io/duckboat/ https://ajfriend.github.io/duckboat/ import duckboat as uck csv = 'https://raw.githubusercontent.com/allisonhorst/palmerpenguins/main/inst/extdata/penguins.csv' uck.Table(csv).do( "where sex = 'female' ", 'where year > 2008', 'select *, cast(body_mass_g as double) as grams', 'select species, island, avg(grams) as avg_grams group by 1,2', 'select * replace (round(avg_grams, 1) as avg_grams)', 'order by avg_grams', ) I still can't tell if it's too goofy, or if I really like it. :) I write a lot of SQL anyway, so this approach is nice in that I find I almost never need to look up function syntax like I would with Pandas, since it is just using DuckDB SQL under the hood, but removing the need to write `select * from ...` repeatedly. And when you're ready to exit the data exploration phase, its easy to gradually translate things back to "real SQL". The whole project is pretty small, essentially just a light wrapper around DuckDB to do this expression chaining and lazy evaluation.
Xmd5a 2y ago
https://github.com/seancorfield/honeysql#vanilla-sql-clause-helpers https://github.com/seancorfield/honeysql#vanilla-sql-clause-... (-> (select :a :b :c) (from :foo) (where [:= :foo.a "baz"])) => {:select [:a :b :c] :from [:foo] :where [:= :foo.a "baz"]} Effort: zero (0). That's what "simple made easy" is about.
jakozaur 2y ago
The SQL with pipe syntax is also been implemented at Databricks since Jan 30, 2025: https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-pipeline.html https://docs.databricks.com/en/sql/language-manual/sql-ref-s... Still, the best is yet to come. Previously, SQL extensions were a pain. There was no good place, and table-value functions were a mess. Now, it would be possible to have higher-order functions such as enrichment, predictions, grouping or other data contracts. Example: FROM orders |> WHERE order_date >= '2024-01-01' |> AGGREGATE SUM(order_amount) AS total_spent GROUP BY customer_id |> WHERE total_spent > 1000 |> INNER JOIN customers USING(customer_id) |> CALL ENRICH.APOLLO(EMAIL > customers.email) |> AGGREGATE COUNT(*) high_value_customer GROUP BY company.country This may be called one SQL to determine distinct e-mail domains, then prepare an enriching dataset and later execute the final SQL with JOIN. Iterative SQL with pipes may also work better with GenAI.
- memhole 2y ago
  Thanks for sharing. I wasn't aware they pushed that out. The ordering of this makes so much more sense. My only real concern, is that I think CTEs are so common place and part of ANSI SQL that you'll see people trade a standard for this. But, I also gripe that Snowflake uses // for comments and people get confused when their code doesn't work on another DB. Or Oracles' join syntax is another example.
- mc_maurer 2y ago
  If only it worked on Databricks serverless SQL warehouses...
  - rxin 2y ago
    This is coming to Serverless SQL warehouses soon!
- valyala 2y ago
  Pipe-based query syntax has been appeared a better alternative to SQL in many databases, because it is easier to read, write and maintain. The only thing is that this syntax isn't standardized among databases yet. BTW, we at VictoriaLogs also use pipe-based syntax for LogsQL query language [1] since the first release in 2023. Recently I wrote an SQL -> LogsQL conversion guide [2], which has been appeared quite clear and easy to follow. This guide was written during the conversion of SQL queries to LogsQL at ClickBench benchmark from ClickHouse [3]. [1] https://docs.victoriametrics.com/victorialogs/logsql/ https://docs.victoriametrics.com/victorialogs/logsql/ [2] https://docs.victoriametrics.com/victorialogs/sql-to-logsql/ https://docs.victoriametrics.com/victorialogs/sql-to-logsql/ [3] https://github.com/ClickHouse/ClickBench/tree/main/victorialogs https://github.com/ClickHouse/ClickBench/tree/main/victorial...
lordofgibbons 2y ago
I love it. Why did it take us so long to make SQL easier to reason about? Hopefully, it gets adopted in Postgresql too.
lincpa 2y ago
[dead]
data-ottawa 2y ago
I’ve been using this for a bit more than a week already, I would say this is a great feature for iteration and interactive queries. Here’s my review: Doing data exploration, analysis, and cleaning, this is way more productive than just SQL. As an example, fusing the aggregate functions with the group by keys creates a much faster workflow. I like that it unifies WHERE/HAVING/QUALIFY, and the set/extend/drop functions help (though Snowflake still wins for being able to declare a column and use it in the same select). Ultimately this gives me a one liner for situations where I’m creating hard to name intermediate CTEs, and that’s awesome! Iterating on array columns or parsing non-trivial JSON is much better with this syntax too. This is a shift back to the data frame API of a few years ago, but we’re still missing typing helpers and support that data frame APIs could provide. It would be nice to have a system like this which plugs into language servers, or can list fields at each step with a mouse over/cursor hover, and getting editor completions (the BQ console does an okay job). This syntax is great for DBT macros. You can just drop in entire transforms without worrying about polluting the query namespace or working around existing columns on your reference tables. There’s a dark side to this syntax. The imperative coding style this comes with a tradeoff that the reader needs to track internal state through many steps. It’s the same reason why SELECT * is often not recommended. As a best practice I like to throw a `|> SELECT X,Y,Z` at the end of these pipe blocks to reinforce to the reader what the output looks like. I should mention that it’s not as portable, but frankly all the major DBs aren’t portable, and other DBs also now support this syntax. tl;dr: I like this feature, but use it sparingly. Avoid overuse in model files, definitely lean on it in analyses.
- eb0la 2y ago
  Just a question: if you're adding stuff at the end of the query... does the previous steps get cached? I mean, if you type first: FROM customers |> WHERE signup_date >= '2024-01-01T00:00:00Z' And then you add (and execute) |> COUNT() as tally GROUP BY CHANNEL Does the first expression get cached by BQ?
  - data-ottawa 2y ago
    No, I don't think BQ does a lot of caching for you even if you re-run the same query.
perdomon 2y ago
This is great. It's a more linear workflow that mimics how we filter data in our minds. How long until it's available in MySQL (never, probably)
dangoodmanUT 2y ago
IMO pipe syntax is so much better. Not necessarily this version, but in general
gigatexal 2y ago
I think the syntax is awesome. Too bad I’m using snowflake at the $DAYJOB
tqi 2y ago
If this helps people write queries more easily, I think that is unequivocally a great thing and have no reservations. Personally, I continue to prefer CTEs because it allows me to write queries in a more modular way, and in conjunction with descriptive names helps me to keep less context in my head when working out the logic. When I look at a pipe syntax query, it almost feels like the difference between reading a long paragraph vs bullet points?
- jeffbee 2y ago
  [flagged]
  - throw-qqqqq 2y ago
    First hit on Google for “sql cte” gives me: CTE (Common Table Expression) and this page: https://www.geeksforgeeks.org/cte-in-sql/ https://www.geeksforgeeks.org/cte-in-sql/
  - scarmig 2y ago
    For anyone who doesn't know, CTE = common table expression, which is a part of a query representing a temporary result set that can be referenced from other parts of a query.
- setr 2y ago
  I don’t think this impacts your usage of CTEs; it’s a reorganization of the SELECT statement, and presumably enables the use of multiple operators and ordering of the SELECT clauses (eg you can have multiple WHERE clauses and it’ll do the right thing) and unifying the clauses (WHERE/HAVING/QUALIFY is now simply WHERE for all 3 cases) You’d still be using CTEs on top of this to deal with deduplicating multiple SELECT/SUBSELECTs
  - tqi 2y ago
    Interesting - the examples I've seen seem to have multiple layers of FROM > WHERE > AGGREGATE > WHERE > JOIN...[1], where the goal seems to be to remove the need to have nested CTEs? [1] https://news.ycombinator.com/item?id=43037330 https://news.ycombinator.com/item?id=43037330
tdfirth 2y ago
It should have always worked this way. Without this feature you take the algebra out of relational algebra. That's the root of most of the composition issues in SQL. Sadly it's a few decades too late though, and sadly this just fragments the "ecosystem" further.
ralmidani 2y ago
The first time I looked at Elixir, I thought it was hideous (I was really into Python and Ruby, and still admire them while liking Elixir much more). But I kept hearing about the virtues of Elixir (and of the BEAM) and coming back. Finally, it clicked, and I’m now in my 3rd year of doing Elixir full-time. I hope to never again use a language that doesn’t have pipes. The moral of the story? Let’s give this new SQL syntax a chance.
fforflo 2y ago
I'm all for such an approach, but I also like Firebolt's approach with Lambda functions focusing on arrays. [0] [0] https://www.firebolt.io/blog/sql-thinking-in-lambdas https://www.firebolt.io/blog/sql-thinking-in-lambdas
ejcx 2y ago
We made pql.dev that works with the different sql syntaxes by translating kusto like queries to sql (using CTE). It's worked really well thusfar and I wish someone would make a standard pipelined query language that gets supported across most databases I know prql exists, but the syntax is pretty cumbersome and not something I enjoyed writing, but I do understand why folks would gravitate towards it
xiphias2 2y ago
One great feature of the SQL pipe syntax is that it works much better together with programming languages (generating, or modifying existing SQL queries). It would be great to have it standardized fast and implemented everywhere.
zendist 2y ago
This reminds me a bit of MSFTs Kusto language. Such an immensely useful way to slice and dice large amounts of structured data.
dnst 2y ago
It seems unfortunate that InfluxDB had the pipe operator with flux in version 2 and dropped it the new version due to low adoption. Now it seems to become more popular.
- valyala 2y ago
  As I know, Flux query language from InfluxDB v2 was too complex comparing to the simple pipe-based SQL from the BigQuery announcement. That is the main reason why Flux is abandoned in InfluxDB v3.
code_runner 2y ago
I appreciate that somebody somewhere may appreciate and enjoy this, but I am not that person. I love SQL. I have always loved SQL. I know why others don't, but I do and its beautiful.
- yujzgzc 2y ago
  This isn't an alternative to it. You can still write ``` SELECT country, COUNT() AS num_records FROM t GROUP BY country ``` And then append something like ``` |> EXTEND FLOOR(num_records / 10) 10 AS num_records_bin |> AGGREGATE COUNT(*) AS num_countries GROUP BY num_records_bin ``` to get a histogram without having to nest stuff
  - code_runner 2y ago
    this is SUPER interesting actually. I definitely didn't realize this. I'm still probably going to use a small army of CTEs for this sort of stuff, but I'm very interested to give this a shot the next time I'm exploring a dataset.
    - yujzgzc 2y ago
      The main value proposition is that it unnests the code. Constantly moving logic into CTEs is another way to do it but comparatively it feels very clunky after a while. For example to inspect intermediate stages of a raft of CTEs, you end up having to write a different final clause. You also can't easily skip a stage. With pipes you just comment out the part of the query you don't want, at the bottom or in the middle, and you still have a syntactically valid query. With today's pipe syntax CTEs are still needed to deal with teeing situations (multiple references to a single table) but for just a chain of transforms it is a lot simpler.
- valyala 2y ago
  While SQL is great, complex analytical queries are much easier to write, read and modify when they are written in pipe-based syntax because of the reasons outlined in the announcement: - Pipe-based query is executed in clear sequential stages according to the pipes written in the query. SQL execution stages aren't clear enough. - SQL requires mentioning GROUP BY fields twice - at SELECT and GROUP BY statements. This complicates modifying the query, since you need to modify the set of GROUP BY fields at SELECT too. - There are no limits on the combination and the number of pipes, which can be used in a single query. There are various limits in SQL. For example, you cannot use WHERE after GROUP BY (there is HAVING workaround, but it looks unnatural). Another example - you cannot easily use GROUP BY multiple times with arbitrary additional calculations and transformations in the middle (of course, you can use subqueries as a workaround for these limitations, but this will complicate the resulting query too much). I recommend reading the following short migration guide from SQL to pipe-based LogsQL (query language optimized for typical queries over logs), in order to feel the difference - https://docs.victoriametrics.com/victorialogs/sql-to-logsql/ https://docs.victoriametrics.com/victorialogs/sql-to-logsql/
eb0la 2y ago
First thing I thought is "this is a SELECT * in disguise", which is something that you SHOULD never do in BigQuery. If you can combine it with CTEs, seems good because it adds legibility for complex queries. Also looks easier for the planner. Not for the optimizer, though.
sweeter 2y ago
The Unix pipe paradigm was honestly way ahead of it's time.
etaham 2y ago
KQL has this as well. Quite helpful and can make it easier to show intent.
brikym 2y ago
As a user of Kusto query language in Azure I can highly recommend pipe syntax. The autocomplete is very good.

247 points by marcyb5st 2y ago

iddan 2y ago

This is such an improvement. Can’t wait for it to get into Postgres

whalesalad 2y ago

Who invented pipe syntax? Elixir? Or is there another runtime or language that made this prevalent beforehand?

ellisv 2y ago
The |> syntax? Perhaps F#. Pipes have been around since at least Unix v3.
- _huayra_ 2y ago
  Many such things from F# come from Haskell (given the influence from MSR UK, specifically Simon Peyton Jones before he left a few years ago), so likely Haskell or earlier imo (at least in terms of "complex" "pipe" operations that have higher-level operations than "bytes in, bytes out" of Unix).
  - ubertaco 2y ago
    Worth noting that F# started out life as an implementation of OCaml for the .NET runtime [1], so most likely the pipe syntax was taken from there, although the pipeline-of-functions construction is much older than that [2] [1] https://softwareengineering.stackexchange.com/questions/209984/why-was-f-created https://softwareengineering.stackexchange.com/questions/2099... [2] https://retrocomputing.stackexchange.com/questions/17335/what-was-the-earliest-use-of-pipe-in-programming https://retrocomputing.stackexchange.com/questions/17335/wha...
    - grndn 2y ago
      OCaml took the '|>' pipe symbol from F#. And F# was the language that made the '|>' pipe symbol popular in mainstream programming (as opposed to the unix '|' pipe symbol), afaik. According to Don Syme, it was used in F# in 2003 (see "Early History of F#", section 9.1, [1] which references [2]). Here's his full comment: /quote Despite being heavily associated with F#, the use of the pipeline symbol in ML dialects actually originates from Tobias Nipkow, in May 1994 (with obvious semiotic inspiration from UNIX pipes) [archives 1994; Syme 2011]. ... I promised to dig into my old mail folders to uncover the true story behind |> in Isabelle/ML, which also turned out popular in F#... In the attachment you find the original mail thread of the three of us [ Larry Paulson; Tobias Nipkow; Marius Wenzel], coming up with this now indispensable piece of ML art in April/May 1994. The mail exchange starts as a response of Larry to my changes. ...Tobias ...came up with the actual name |> in the end... /endquote Haskell has had "$" or "backwards pipe" for ages, but that is just another way of doing function application and it does not feel the same as (and is not used the same way as) the unix-style piping paradigm. [1] https://fsharp.org/history/hopl-final/hopl-fsharp.pdf https://fsharp.org/history/hopl-final/hopl-fsharp.pdf [2] https://web.archive.org/web/20190217164203/https://blogs.msdn.microsoft.com/dsyme/2011/05/17/archeological-semiotics-the-birth-of-the-pipeline-symbol-1994/ https://web.archive.org/web/20190217164203/https://blogs.msd...
xnx 2y ago
Originally? Douglas McIlroy for Unix in 1973: https://en.wikipedia.org/wiki/Pipeline_(Unix) https://en.wikipedia.org/wiki/Pipeline_(Unix)
- jmount 2y ago
  I agree. And then functional languages pick up the pipe, as it is roughly "poor man's method chaining" (so the object oriented languages don't need it).
nothrabannosir 2y ago
Shell would at least qualify as prior art, I’d assume
grndn 2y ago
Elixir took the pipe symbol from F# (See Jose's comment at [1]). For more details see my other comment below. [1] https://elixirforum.com/t/which-language-first-introduced-the-pipe-operator/16791/8 https://elixirforum.com/t/which-language-first-introduced-th...

pjmlp 2y ago

Ever since using Kusto Query Language, that I look forward to SQL getting something like this, maybe there is hope if enough DBs have it as an extension.

DarkCrusader2 2y ago
I heavily use KQL on a daily basis and want nothing more than using it for Postgres and SQlite as well.

Taikonerd 2y ago

This is so exciting. I hope Postgres and MySQL get some level of support for it, too.

notpushkin 2y ago

PRQL is a similar idea, and it compiles to SQL: https://prql-lang.org/ https://prql-lang.org/ from invoices filter invoice_date >= @1970-01-16 derive { transaction_fees = 0.8, income = total - transaction_fees } filter income > 1

mritchie712 2y ago
there's an duckdb extension[0] for it too 0 - https://duckdb.org/community_extensions/extensions/prql.html https://duckdb.org/community_extensions/extensions/prql.html
throwaway127482 2y ago
I think I like this new pipe syntax a lot better than PRQL. Feels like it has the potential to become officially supported by DBs like MySQL since it's a relatively small change to existing SQL syntax.
gorkaerana 2y ago
I'm tempted to give it a try just for the "For HackerNews enthusiasts" section of their landing page, which states "The PRQL compiler is written in Rust" and "We talk about “orthogonal language features” a lot". Love me some banter in technical documentation :D
benrutter 2y ago
I love the design behind PRQL, was a little dissapointed when I tried using it with an MSSQL server and found there was no decent way to filter for `LIKE '%something%'`. (PRQL uses regex for stuff like this, which sounds great but then isn't supported on some databases like MSSQL)
- default-kramer 2y ago
  I'm very surprised to learn that PRQL does not natively support `like`, but you can add it yourself: https://github.com/PRQL/prql/issues/1123#issuecomment-1353859034 https://github.com/PRQL/prql/issues/1123#issuecomment-135385...

Taikonerd 2y ago

Relatedly, PRQL is a lovely pipe-oriented syntax for SQL DBs: https://prql-lang.org/ https://prql-lang.org/ Their syntax is a lot cleaner, because it's a new language -- it's not backwards-compatible with SQL. But then again, they don't have the resources of Google behind them.

systems 2y ago
if its not backward compatible with SQL, is not pipe syntax for SQL its just a new Query language most DBMS have proprietary extension to SQL, its messy, real success would be adding the new syntax to the standard and have implemented across multiple DBMSs
- Taikonerd 2y ago
  Well, OK. PRQL is "for SQL DBs" in the sense that you can translate it to SQL on the client side, and then execute it on regular Postgres, MySQL, etc.
  - RyanHamilton 2y ago
    That's exactly what qstudio offers: https://www.timestored.com/qstudio/prql-ide https://www.timestored.com/qstudio/prql-ide a desktop client that translates prql to run against many databases.

deleted 2y ago

[deleted]

epistasis 2y ago

This is beautiful. I first encountered this style of data manipulation in R's tidyverse. Some say C# (or F#?)had similar pipes and influences, but I haven't seen specifics. Some other data libraries like Polars have sort of similar parse trees, but they also have lots of extra cruft because of limitations to the underlying language. Python's functional calling semantics are extremely powerful, but not quite as powerful as R's.

jddj 2y ago
The C# and F# bit is LINQ (language integrated query) and it's a lovely piece of engineering.
- ziml77 2y ago
  The LINQ syntax was an interesting experiment but the value of that syntax always felt minimal to me. Like it looked out of place in the middle of my C# code, so instead I just use the extension methods.
  - neonsunset 2y ago
    It grows on you after you start using F# too

nicoritschel 2y ago

Also is in DuckDB https://github.com/ywelsch/duckdb-psql https://github.com/ywelsch/duckdb-psql

conradev 2y ago
and SQLite is waiting on Postgres: https://news.ycombinator.com/item?id=41385005 https://news.ycombinator.com/item?id=41385005

jmull 2y ago

I like it, but I'm not sure it's worth the trouble of further expanding an ever-ballooning extended family of SQL syntaxes. Of course, SQL has numerous issues, both in an absolute sense and relative to what we've come to expect. And the obvious course of action in each individual case is to add syntax to support the missing feature. But as you keep adding syntax, SQL gets more and more complex, both in the individual variants and due to the complexities of multiple variants with different syntax support at different times. A transpiling approach makes more sense to me. I'd like the sql implementors to focus on source maps and other things to better support plugging in external, alternate syntaxes (is a standardized mechanism too much to ask for?). Then individual projects/people/efforts can choose the SQL syntax variant that works for them, and it can be one that evolves separate from the host db.

_dark_matter_ 2y ago
There are a lot of transpilers, and they don't get much adoption because the people implementing them are not the people implementing the database. The database and the query language are tightly coupled. The latter determines the kinds of optimizations that the former can do, and a transpiled language means that optimization info needs to be passed through in weird and inefficient ways; it also means that obvious optimizations aren't possible, because the people building the language are not building the db. If you've ever tried used query hints, or deeply inspected a distributed query plan, you'll know exactly what I mean.
9rx 2y ago
> A transpiling approach makes more sense to me. That's the solution we've been using, oft referred to as ORM, to work around the lack of this syntax. But then you had to live in a world where you had an almost SQL, but not really, language that doesn't always quite map perfectly to SQL and all the pain to go along with that. Now instead of that song and dance you can compose queries with plain SQL and simple string concatenation. That is a huge win, even if troubling that it has taken so long (and still not widely supported).
- jmull 2y ago
  Generally, ORM's try to present a high-level abstraction of your data and data model than the engine does natively. That makes it the ORM's responsibility to implement the abstraction in a good way. Problems come in when the "good" way depends on factors the ORM doesn't consider or expose well. I actually mean transpiler in a mainly syntactic sense, and would want it to avoid higher-level abstractions (after all, as you point out, ORMs have that ground covered).

beoberha 2y ago

Somehow Microsoft (my employer) has totally failed in evangelizing Kusto to the masses. KQL is by far the best query language you’ll ever use. This syntax from Google is nice but it’s still just SQL.

summarity 2y ago
Plus one for Kusto. I’ve never had this much success teaching people data analysis. It’s good for simple obvious and powerful composed queries. Though I work at GitHub so still too close to the mothership for an unbiased opinion. There’s some Apache energy around KQL though and a few open source parsers so there’s hope yet.
default-kramer 2y ago
I remain unimpressed by KQL. Comparing SQL to KQL is approximately like comparing Java to C#. Yeah it's better in many ways, but at the end of the day it doesn't make a huge difference. I want to go from Java to Lisp. Consider how many of these complaints are equally valid against KQL: https://www.scattered-thoughts.net/writing/against-sql/ https://www.scattered-thoughts.net/writing/against-sql/
- bradford 2y ago
  Totally disagree, I've used KQL for about 10 years now, and SQL for 20. Given the choice, I'll always prefer KQL. Sorry, I don't have time for a thorough rebuttal of all the topics mentioned in the link you provided, but if I had to bring up a few counterpoints: 1. (Can't be expressed) KQLs dynamic datatype handles JSON much better than SQLs language additions. 2. (variables/Fragile structure/Functions) KQL fixes many of the orthogonality issues in SQL. (Specifically: both variable assignments and function parameters can accept scalar and tabular values in a similiar way, where-as SQL uses different syntax for each) (disclaimer, msft employee)
- beoberha 2y ago
  Eh, I don’t need Lisp. I need to do my job. KQL allows me to write queries intuitively and others to easily understand what I’m doing. The summarize operator is also a game changer. I use something like “ | summarize count() by bin(TIMESTAMP, 1h) “ multiple times daily. Getting the same result in SQL is disgusting.

schultzer 2y ago

When I first started working in Elixir I loved Ecto and the pipe syntax, but now I want a SQL sigil, that wont care about where I put my where or from clauses. Why are SQL parsers even concerned with that? Isn’t that why we have RD parser.

mwexler 2y ago

Though not a pipe syntax, the Malloy language has some similar analytic sugar in its syntax. Malloy is from Lloyd Tabb, a co-founder of Looker. https://www.malloydata.dev/ https://www.malloydata.dev/

mccanne 2y ago

Really cool though typing ">" after "|" is a pain https://github.com/brimdata/super/blob/main/docs/language/pipe-ambiguity.md https://github.com/brimdata/super/blob/main/docs/language/pi...

valyala 2y ago
Agreed! LogsQL uses `|` as pipe delimiter too - https://docs.victoriametrics.com/victorialogs/sql-to-logsql/ https://docs.victoriametrics.com/victorialogs/sql-to-logsql/

willvarfar 2y ago

When the pipe syntax was first published last year the SQLite team did a quick hack to try it out. https://sqlite.org/forum/forumpost/5f218012b6e1a9db https://sqlite.org/forum/forumpost/5f218012b6e1a9db (Note that the fiddle linked no longer supports the syntax) It is very interesting that they found it completely unnecessary to actually require the pipe characters. The grammar works when the pipe characters are optional. And, imo, looks a lot better!

specialist 2y ago
Good. I vote for omitting the pipe lexemes. I hate them. Of the alternatives linked (elsethread), PSQL is the least bad. I'm ambivalent about the curly brackets; will have to ponder. Malloy would be improved by omitting the superfluous (JSON-esque) semicolons.

ajfriend 2y ago

I have project that's still very much at the experimental stage, where I try to get something similar to this pipe syntax by allowing users to chain "SQL snippets" together. That is, you can use standalone statements like `where col1 > 10` because the `select * from ...` is implied. https://ajfriend.github.io/duckboat/ https://ajfriend.github.io/duckboat/ import duckboat as uck csv = 'https://raw.githubusercontent.com/allisonhorst/palmerpenguins/main/inst/extdata/penguins.csv' uck.Table(csv).do( "where sex = 'female' ", 'where year > 2008', 'select *, cast(body_mass_g as double) as grams', 'select species, island, avg(grams) as avg_grams group by 1,2', 'select * replace (round(avg_grams, 1) as avg_grams)', 'order by avg_grams', ) I still can't tell if it's too goofy, or if I really like it. :) I write a lot of SQL anyway, so this approach is nice in that I find I almost never need to look up function syntax like I would with Pandas, since it is just using DuckDB SQL under the hood, but removing the need to write `select * from ...` repeatedly. And when you're ready to exit the data exploration phase, its easy to gradually translate things back to "real SQL". The whole project is pretty small, essentially just a light wrapper around DuckDB to do this expression chaining and lazy evaluation.

Xmd5a 2y ago

https://github.com/seancorfield/honeysql#vanilla-sql-clause-helpers https://github.com/seancorfield/honeysql#vanilla-sql-clause-... (-> (select :a :b :c) (from :foo) (where [:= :foo.a "baz"])) => {:select [:a :b :c] :from [:foo] :where [:= :foo.a "baz"]} Effort: zero (0). That's what "simple made easy" is about.

jakozaur 2y ago

The SQL with pipe syntax is also been implemented at Databricks since Jan 30, 2025: https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-pipeline.html https://docs.databricks.com/en/sql/language-manual/sql-ref-s... Still, the best is yet to come. Previously, SQL extensions were a pain. There was no good place, and table-value functions were a mess. Now, it would be possible to have higher-order functions such as enrichment, predictions, grouping or other data contracts. Example: FROM orders |> WHERE order_date >= '2024-01-01' |> AGGREGATE SUM(order_amount) AS total_spent GROUP BY customer_id |> WHERE total_spent > 1000 |> INNER JOIN customers USING(customer_id) |> CALL ENRICH.APOLLO(EMAIL > customers.email) |> AGGREGATE COUNT(*) high_value_customer GROUP BY company.country This may be called one SQL to determine distinct e-mail domains, then prepare an enriching dataset and later execute the final SQL with JOIN. Iterative SQL with pipes may also work better with GenAI.

memhole 2y ago
Thanks for sharing. I wasn't aware they pushed that out. The ordering of this makes so much more sense. My only real concern, is that I think CTEs are so common place and part of ANSI SQL that you'll see people trade a standard for this. But, I also gripe that Snowflake uses // for comments and people get confused when their code doesn't work on another DB. Or Oracles' join syntax is another example.
mc_maurer 2y ago
If only it worked on Databricks serverless SQL warehouses...
- rxin 2y ago
  This is coming to Serverless SQL warehouses soon!
valyala 2y ago
Pipe-based query syntax has been appeared a better alternative to SQL in many databases, because it is easier to read, write and maintain. The only thing is that this syntax isn't standardized among databases yet. BTW, we at VictoriaLogs also use pipe-based syntax for LogsQL query language [1] since the first release in 2023. Recently I wrote an SQL -> LogsQL conversion guide [2], which has been appeared quite clear and easy to follow. This guide was written during the conversion of SQL queries to LogsQL at ClickBench benchmark from ClickHouse [3]. [1] https://docs.victoriametrics.com/victorialogs/logsql/ https://docs.victoriametrics.com/victorialogs/logsql/ [2] https://docs.victoriametrics.com/victorialogs/sql-to-logsql/ https://docs.victoriametrics.com/victorialogs/sql-to-logsql/ [3] https://github.com/ClickHouse/ClickBench/tree/main/victorialogs https://github.com/ClickHouse/ClickBench/tree/main/victorial...

lordofgibbons 2y ago

I love it. Why did it take us so long to make SQL easier to reason about? Hopefully, it gets adopted in Postgresql too.

lincpa 2y ago

[dead]

data-ottawa 2y ago

I’ve been using this for a bit more than a week already, I would say this is a great feature for iteration and interactive queries. Here’s my review: Doing data exploration, analysis, and cleaning, this is way more productive than just SQL. As an example, fusing the aggregate functions with the group by keys creates a much faster workflow. I like that it unifies WHERE/HAVING/QUALIFY, and the set/extend/drop functions help (though Snowflake still wins for being able to declare a column and use it in the same select). Ultimately this gives me a one liner for situations where I’m creating hard to name intermediate CTEs, and that’s awesome! Iterating on array columns or parsing non-trivial JSON is much better with this syntax too. This is a shift back to the data frame API of a few years ago, but we’re still missing typing helpers and support that data frame APIs could provide. It would be nice to have a system like this which plugs into language servers, or can list fields at each step with a mouse over/cursor hover, and getting editor completions (the BQ console does an okay job). This syntax is great for DBT macros. You can just drop in entire transforms without worrying about polluting the query namespace or working around existing columns on your reference tables. There’s a dark side to this syntax. The imperative coding style this comes with a tradeoff that the reader needs to track internal state through many steps. It’s the same reason why SELECT * is often not recommended. As a best practice I like to throw a `|> SELECT X,Y,Z` at the end of these pipe blocks to reinforce to the reader what the output looks like. I should mention that it’s not as portable, but frankly all the major DBs aren’t portable, and other DBs also now support this syntax. tl;dr: I like this feature, but use it sparingly. Avoid overuse in model files, definitely lean on it in analyses.

eb0la 2y ago
Just a question: if you're adding stuff at the end of the query... does the previous steps get cached? I mean, if you type first: FROM customers |> WHERE signup_date >= '2024-01-01T00:00:00Z' And then you add (and execute) |> COUNT() as tally GROUP BY CHANNEL Does the first expression get cached by BQ?
- data-ottawa 2y ago
  No, I don't think BQ does a lot of caching for you even if you re-run the same query.

perdomon 2y ago

This is great. It's a more linear workflow that mimics how we filter data in our minds. How long until it's available in MySQL (never, probably)

dangoodmanUT 2y ago

IMO pipe syntax is so much better. Not necessarily this version, but in general

gigatexal 2y ago

I think the syntax is awesome. Too bad I’m using snowflake at the $DAYJOB

tqi 2y ago

If this helps people write queries more easily, I think that is unequivocally a great thing and have no reservations. Personally, I continue to prefer CTEs because it allows me to write queries in a more modular way, and in conjunction with descriptive names helps me to keep less context in my head when working out the logic. When I look at a pipe syntax query, it almost feels like the difference between reading a long paragraph vs bullet points?

jeffbee 2y ago
[flagged]
- throw-qqqqq 2y ago
  First hit on Google for “sql cte” gives me: CTE (Common Table Expression) and this page: https://www.geeksforgeeks.org/cte-in-sql/ https://www.geeksforgeeks.org/cte-in-sql/
- scarmig 2y ago
  For anyone who doesn't know, CTE = common table expression, which is a part of a query representing a temporary result set that can be referenced from other parts of a query.
setr 2y ago
I don’t think this impacts your usage of CTEs; it’s a reorganization of the SELECT statement, and presumably enables the use of multiple operators and ordering of the SELECT clauses (eg you can have multiple WHERE clauses and it’ll do the right thing) and unifying the clauses (WHERE/HAVING/QUALIFY is now simply WHERE for all 3 cases) You’d still be using CTEs on top of this to deal with deduplicating multiple SELECT/SUBSELECTs
- tqi 2y ago
  Interesting - the examples I've seen seem to have multiple layers of FROM > WHERE > AGGREGATE > WHERE > JOIN...[1], where the goal seems to be to remove the need to have nested CTEs? [1] https://news.ycombinator.com/item?id=43037330 https://news.ycombinator.com/item?id=43037330

tdfirth 2y ago

It should have always worked this way. Without this feature you take the algebra out of relational algebra. That's the root of most of the composition issues in SQL. Sadly it's a few decades too late though, and sadly this just fragments the "ecosystem" further.

ralmidani 2y ago

The first time I looked at Elixir, I thought it was hideous (I was really into Python and Ruby, and still admire them while liking Elixir much more). But I kept hearing about the virtues of Elixir (and of the BEAM) and coming back. Finally, it clicked, and I’m now in my 3rd year of doing Elixir full-time. I hope to never again use a language that doesn’t have pipes. The moral of the story? Let’s give this new SQL syntax a chance.

fforflo 2y ago

I'm all for such an approach, but I also like Firebolt's approach with Lambda functions focusing on arrays. [0] [0] https://www.firebolt.io/blog/sql-thinking-in-lambdas https://www.firebolt.io/blog/sql-thinking-in-lambdas

ejcx 2y ago

We made pql.dev that works with the different sql syntaxes by translating kusto like queries to sql (using CTE). It's worked really well thusfar and I wish someone would make a standard pipelined query language that gets supported across most databases I know prql exists, but the syntax is pretty cumbersome and not something I enjoyed writing, but I do understand why folks would gravitate towards it

xiphias2 2y ago

One great feature of the SQL pipe syntax is that it works much better together with programming languages (generating, or modifying existing SQL queries). It would be great to have it standardized fast and implemented everywhere.

zendist 2y ago

This reminds me a bit of MSFTs Kusto language. Such an immensely useful way to slice and dice large amounts of structured data.

dnst 2y ago

It seems unfortunate that InfluxDB had the pipe operator with flux in version 2 and dropped it the new version due to low adoption. Now it seems to become more popular.

valyala 2y ago
As I know, Flux query language from InfluxDB v2 was too complex comparing to the simple pipe-based SQL from the BigQuery announcement. That is the main reason why Flux is abandoned in InfluxDB v3.

code_runner 2y ago

I appreciate that somebody somewhere may appreciate and enjoy this, but I am not that person. I love SQL. I have always loved SQL. I know why others don't, but I do and its beautiful.

yujzgzc 2y ago
This isn't an alternative to it. You can still write ``` SELECT country, COUNT() AS num_records FROM t GROUP BY country ``` And then append something like ``` |> EXTEND FLOOR(num_records / 10) 10 AS num_records_bin |> AGGREGATE COUNT(*) AS num_countries GROUP BY num_records_bin ``` to get a histogram without having to nest stuff
- code_runner 2y ago
  this is SUPER interesting actually. I definitely didn't realize this. I'm still probably going to use a small army of CTEs for this sort of stuff, but I'm very interested to give this a shot the next time I'm exploring a dataset.
  - yujzgzc 2y ago
    The main value proposition is that it unnests the code. Constantly moving logic into CTEs is another way to do it but comparatively it feels very clunky after a while. For example to inspect intermediate stages of a raft of CTEs, you end up having to write a different final clause. You also can't easily skip a stage. With pipes you just comment out the part of the query you don't want, at the bottom or in the middle, and you still have a syntactically valid query. With today's pipe syntax CTEs are still needed to deal with teeing situations (multiple references to a single table) but for just a chain of transforms it is a lot simpler.
valyala 2y ago
While SQL is great, complex analytical queries are much easier to write, read and modify when they are written in pipe-based syntax because of the reasons outlined in the announcement: - Pipe-based query is executed in clear sequential stages according to the pipes written in the query. SQL execution stages aren't clear enough. - SQL requires mentioning GROUP BY fields twice - at SELECT and GROUP BY statements. This complicates modifying the query, since you need to modify the set of GROUP BY fields at SELECT too. - There are no limits on the combination and the number of pipes, which can be used in a single query. There are various limits in SQL. For example, you cannot use WHERE after GROUP BY (there is HAVING workaround, but it looks unnatural). Another example - you cannot easily use GROUP BY multiple times with arbitrary additional calculations and transformations in the middle (of course, you can use subqueries as a workaround for these limitations, but this will complicate the resulting query too much). I recommend reading the following short migration guide from SQL to pipe-based LogsQL (query language optimized for typical queries over logs), in order to feel the difference - https://docs.victoriametrics.com/victorialogs/sql-to-logsql/ https://docs.victoriametrics.com/victorialogs/sql-to-logsql/

eb0la 2y ago

First thing I thought is "this is a SELECT * in disguise", which is something that you SHOULD never do in BigQuery. If you can combine it with CTEs, seems good because it adds legibility for complex queries. Also looks easier for the planner. Not for the optimizer, though.

sweeter 2y ago

The Unix pipe paradigm was honestly way ahead of it's time.

etaham 2y ago

KQL has this as well. Quite helpful and can make it easier to show intent.

brikym 2y ago

As a user of Kusto query language in Azure I can highly recommend pipe syntax. The autocomplete is very good.