Pipe Syntax in SQL

https://research.google/pubs/sql-has-problems-we-can-fix-them-pipe-syntax-in-sql/

by legrangramgroum • 2 years ago

308 229 2 years ago

research.google

simonw7• 2 years ago

Google: you are a web company. Please learn to publish your research papers as web pages.

orangepanda• 2 years ago

I expected to see some eldritch css monstrosity, but no, its just a pdf. A well formatted one, at that.

What’s your issue there?

simonw7• 2 years ago

Reading two column PDFs on a mobile phone sucks.

Plus I can't use web tools, like "Read this page" in Mobile Safari.

And copying and pasting is harder.

And I can't link to individual sections.

I'm honestly baffled by people who prefer PDFs for this kind of information. Are they printing them out on paper and going at them with a highlighter or something?

VoodooJuJu• 2 years ago

PDF content is not web-indexed. Their Google Scholar link doesn't even work either.

estebarb• 2 years ago

Conference papers use templates. It's not like Google can choose.

simonw7• 2 years ago

They can choose to publish it in both HTML and PDF.

lrem3• 2 years ago

That’s not a blog post. This is an academic preprint, I imagine the format is as prescribed.

irrelative• 2 years ago

Seriously. It’s not like that was the actual purpose of html or anything.

jml7c53• 2 years ago

I really wish that browsers had developed first-class support for offline web page bundles. There's no way to share a page that is guaranteed to be self-contained and not hit the network, especially if you want to use javascript. It's particularly frustrating since browsers supported offline mode as far back as the 90s; it just needed to be combined with support for loading from zipped folders.

That simple change would've largely solved the academic paper problem decades ago. It's bizarre that it still isn't a feature.

simonw7• 2 years ago

One option her is to inline all assets - images etc - as bas64 URIs. The HTML page ends up huge but it will at least be self-contained.

abelcha3• 2 years ago

this is what you are looking for i believe (web archive format) https://en.wikipedia.org/wiki/WARC_(file_format)?oldformat=t...

smsm42• 2 years ago

Mail clients kinda do that (or at least they can, if asked to). Also, why would academic papers need JS anyway? CSS and images, I can get, but beyond that there's no need for anything fancier.

slt2021• 2 years ago

reminds me PRQL https://prql-lang.org

and SPL from Splunk

mark_and_sweep• 2 years ago

Reminds me of KQL (Kusto Query Language) by Microsoft

LeonB6• 2 years ago

Yeh, when I saw the example above I thought it was KQL.

But they do sort of acknowledge it in the paper. Eg on the first page it says:

…we present a solution – adding pipe-structured data flow syntax to SQL. This makes SQL more flexible, extensible and easy to use. This paradigm works well in other languages like Kusto’s KQL[5]

Strange typo though, to say “Kusto’s KQL” instead of “Microsoft’s KQL”

Kusto is allegedly named after (sort of in reference to) Jacques Cousteau, so “Kusto’s” doesn’t make sense.

wizardist• 2 years ago

Also OpenSearch PPL.

feldrim• 2 years ago

BTW, PRQL is mentioned in the article as well. In sum, they decided that instead of a new language against SQL, extending SQL with pipes would be better for user experience, learning curve, etc.

simonw7• 2 years ago

To see how well it works, I uploaded the PDF to Google AI Studio and ran the prompt "Convert this document to neatly styled semantic HTML" against the gemini-1.5-pro-exp-0801 model - the result is actually pretty good!

https://static.simonwillison.net/static/2024/Pipe-Syntax-In-...

dang• 2 years ago

(This was originally a reply to https://news.ycombinator.com/item?id=41339138 but I detached it so the useful link could float higher in the thread - thanks!)

simonw7• 2 years ago

I wrote up a few extra notes about how I did this here: https://simonwillison.net/2024/Aug/24/pipe-syntax-in-sql/

ku1ik• 2 years ago

I would welcome this! I’ve been using SQL for over 20 years and I still sometimes have trouble expressing certain queries, which in my head are well understood. I typically get there but I wish the amount of trial and error was shorter, and the process of turning what I have in my head inside out disappeared.

atum472• 2 years ago

Same here. I often connect to mariadb via console and try my queries there. I confess that I've used gpt to help me with some hairy ones and it worked quite good.

vinnymac• 2 years ago

It's difficult for me to tell how sincere your confession was, I am guessing you meant it in jest.

But it is worth stating nonetheless. No one confesses when they search StackOverflow for something they've solved 100 times before, or when they ask Google a question a dozen times a day. Asking ChatGPT for some insight should equally not be considered sinful :)

ibash3• 2 years ago

I do find common table expressions (eg with clause) lets me express what I want way easier.

Probably because it makes it easy to express a bunch of operators that are then pipelined…

pajeets3• 2 years ago

Absolutely as soon as I start doing multiple joins and throwing up all sorts of trailing syntaxes to further refine/filter/group I get lost

But piping is both intuitive and very easy to understand without having to get everything right from the beginning.

I really hope Postgres 16 can implement pipes, might be interesting to be able to use triggers and functions to pipe to and from, actually might simplify that aspect of it too.

Ciantic• 2 years ago

Here is one example from the PDF:

    FROM r JOIN s USING (id)
    |> WHERE r.c < 15
    |> AGGREGATE sum(r.e) AS s GROUP BY r.d
    |> WHERE s > 3
    |> ORDER BY d
    |> SELECT d, s, rank() OVER (order by d)

Can we call this SQL anymore after this? This re-ordering of things has been done by others too, like PRQL, but they didn't call it SQL. I do think it makes things more readable.

ibash3• 2 years ago

Yes we can call it sql.

Language syntax changes all the time. Their point is that sql syntax is a mess and can be cleaned up.

extr• 2 years ago

Not bad, very similar to dplyr syntax. Personally i’m too used to classic SQL though and this would be more readable as CTEs. In particular how would this syntax fair if it was much more complicated with with 4-5 tables and joins?

randomdata• 2 years ago

Can we call this SQL anymore after this?

Maybe not, just as we don't call "rank() OVER" SQL. We call it SQL:2003. Seems we're calling this GoogleSQL. But perhaps, in both cases, we can use SQL for short?

esafak• 2 years ago

You show a good example. Many people would call that SQL, and if pipes become popular, they too might simply be called SQL one day.

euroderf• 2 years ago

GoogleSQL

EssGyooGell: A Modest Proposal

pajeets3• 2 years ago

Yes, having |> isn't breaking SQL but rather enhancing it.

I really like this idea of piping SQL queries rather than trying to create the perfect syntax from the get go.

+1 for readability too.

oxym0ron• 2 years ago

Honestly, it seems like a band-aid on legacy query language.

andy8005• 2 years ago

The multiple uses of WHERE with different meanings is problematic for me. The second WHERE, filtering an aggregate, would be HAVING in standard SQL.

Not sure if this is an attempt to simplify things or an oversight, but favoring convenience (no need to remember multiple keywords) over explicitness (but the keywords have different meanings) tends to cause problems, in my observation.

singron2• 2 years ago

In the query plan, filtering before or after an aggregation is the same, so it's a strange quirk that SQL requires a different word.

wvenable6• 2 years ago

The second WHERE, filtering an aggregate, would be HAVING in standard SQL.

Only if you aren't using a subquery otherwise you would use WHERE even in plain SQL. Since the pipe operator is effectively creating subqueries the syntax is perfectly consistent with SQL.

yen2233• 2 years ago

Should we introduce a SUBSELECT keyword to distinguish between a top-level select and a subquery?

To me that feels as redundant as having WHERE vs HAVING, i.e. they do the same things, but at different points in the execution plan. It feels weird to need two separate keywords for that.

cryptonector4• 2 years ago

You can always turn a HAVING in SQL into a WHERE by wrapping the SELECT that has the GROUP BY in another SELECT that has the WHERE that would have been the HAVING if you hadn't bothered.

You don't need a |> operator to make this possible. Your point is that there is a reason that SQL didn't just allow two WHERE clauses, one before and one after GROUP BY: to make it clearer syntactically.

Whereas the sort of proposal made by TFA is that if you think of the query as a sequence of steps to execute then you don't need the WHERE vs. HAVING clue because you can see whether a WHERE comes before or after GROUP BY in some query.

But the whole point of SQL is to _not have to_ think of how the query is to be implemented. Which I think brings us back to: it's better to have HAVING. But it's true also that it's better to allow arbitrary ordering of some clauses: there is no reason that FROM/JOIN, SELECT, ORDER BY / LIMIT have to be in the order that they are -- only WHERE vs. GROUP BY ordering matters, and _only_ if you insist on using WHERE for pre- and post-GROUP BY, but if you don't then all clauses can come in any order you like (though all table sources should come together, IMO).

So all in all I agree with you: keep HAVING.

richbell3• 2 years ago

IMO having SELECT before FROM is one of SQL's biggest mistakes. I would gladly welcome a new syntax that rectifies this. (Also https://duckdb.org/2022/05/04/friendlier-sql.html)

I don't love the multiple WHEREs.

abelcha3• 2 years ago

duckDB is what sql should be in 2024

https://duckdbsnippets.com/

viraptor• 2 years ago

Duckdb also supports prql with an extension https://github.com/ywelsch/duckdb-prql

RaftPeople6• 2 years ago

That's a great list of friendlier sql in DuckDB. For most of that list I either run into it regularly or have wanted the exact fix they have.

mr_toad5• 2 years ago

SQL was supposed to follow English grammar. Having FROM before SELECT is like having “Begun” before “these clone wars have.”

jmull• 2 years ago

The proposal here adds pipe syntax to SQL.

So it would be reasonable to call it SQL, if it gets traction. You want to see some of the big dogs adopting it.

That should at least be possible since it looks like it could be added to an existing implementation without significant disruption/extra complexity.

cryptonector4• 2 years ago

There may be trademark issues, but even if not, doing sufficient violence to the original thing argues for using a new name for the new thing.

tmoertel9• 2 years ago

The point of SQL pipe syntax is that there is no reordering. You read the query as a sequence of operations, and that's exactly how it's executed. (Semantically. Of course, the query engine is free to optimize the execution plan as long as the semantics are preserved.)

The pipe operator is a semantic execution barrier:everything before the `|>` is assumed to have executed and returned a table before what follows begins:

From the paper:

Each pipe operator is a unary relational operation that takes one table as input and produces one table as output.

Vanilla SQL is actually more complex in this respect because you have, for example, at least 3 different keywords for filtering (WHERE, HAVING, QUALIFY) and everyone who reads your query needs to understand what each keyword implies regarding execution scheduling. (WHERE is before grouping, HAVING is after aggregates, and QUALIFY is after analytic window functions.)

quietbritishjim• 2 years ago

The point of SQL pipe syntax is that there is no reordering.

If you're referring to this in the comment you're replying to:

Can we call this SQL anymore after this? This re-ordering of things ...

Then they're clearly just saying that this is a reordering compared to SQL, which is undeniably true (and the while point).

mattashii• 2 years ago

Vanilla SQL [...] QUALIFY is after analytic window functions

Isn't that FILTER (WHERE), as in SELECT avg(...) FILTER (WHERE ...) FROM ...?

aidos• 2 years ago

Golly, QUALIFY, a new SQL operator I didn’t know existed. I tend not to do much with window functions and I would have reached for a CTE instead but it’s always nice to be humbled by finding something new in a language you thought you knew well.

thwarted• 2 years ago

The pipe operator is a semantic execution barrier:everything before the `|>` is assumed to have executed and returned a table before what follows begins

I already think about SQL like this (as operation on lists/sets), however thinking of it like that, and having previous operations feed into the next, which is conceptually nice, seems to make it hard to do, and think about:

*(the query engine is free to optimize the execution plan as long as the semantics are preserved)

since logically each part between the pipes doesn't know about the others, so global optimizations, such as use of indexes to restrict the result of a join based on the where clause can't be done/is more difficult.

_a_a_a_• 2 years ago

'qualify' is now standard? Thought it was a vendor extension currently.

cryptonector4• 2 years ago

The point of SQL pipe syntax is that there is no reordering.

But this thing resembles other FROM-clause-first variants of SQL, thus GP's point about this being just a reordering. GP is right: the FROM clause gets re-ordered to be first, so it's a reordering.

vendiddy2• 2 years ago

This is an interesting point.

All these years I've been doing that reordering and didn't even realize!

camgunz• 2 years ago

This kind of implies there's better or worse ordering. AFAIK that's pretty subjective. If the idea was to expose how the DB is ordering things, or even make things easier for autocomplete OK, but this just feels like a "I have a personal aesthetic problem with SQL and I think we should spend thousands of engineering hours and bifurcate SQL projects forever to fix it" kind of thing.

Glyptodon• 2 years ago

In the example would there a difference between `|> where s > 3` and `|> having s > 3` ?

Edit: nope, just that you don't need having to exist with the pipe syntax.

racecar789• 2 years ago

In that example, "s" has two meanings: 1. A table being joined. 2. A column being summed.

For clarity, they should have assigned #2 to a different variable letter.

hyperman1• 2 years ago

This is an extension on top of all existing SQL. The pipe functions more or less as a unix pipe. There is no reordering, but the user selects the order. The core syntax is simply:

  query | operator

Which results in a new query that can be piped again. So e.g. this would be valid too:

  SELECT id,a,b FROM  table WHERE id>1
  |WHERE id < 10

Personally, I can see this fix so much SQL pain.

larodi• 2 years ago

okay, now I can see why this so much reminds of CTE

thiht3• 2 years ago

Honestly SQL screwed things up from the very beginning. "SELECT FROM" makes no sense at all. The projection being before the selection is dumb as hell. This is why we can’t get proper tooling for writing SQL, even autocompletion can’t work sanely. You write "SELECT", what’s it gonna autocomplete?

PRQL gives me hope that we might finally get something nice some day

akira25013• 2 years ago

    SELECT 1+2;

FROM clauses aren't required, and using multiple tables in FROM doesn't seem to work out too well when that syntax is listed first.

gfody2• 2 years ago

what’s it gonna autocomplete?

otoh if you selected something the from clause and potentially some joins could autocomplete

parpfish2• 2 years ago

I also hate having SELECT before FROM because I want to think of the query as a transformation that can be read from top to bottom to understand the flow.

But I assume that that’s part of why they didn’t set it up that way — it’s just a little thing to make the query feel more declarative and less imperative

scrlk• 2 years ago

The initial version of SQL was called "Structured English Query Language".

If the designers intended to create a query language that resembled an English sentence, it makes sense why they chose "SELECT FROM".

"Select the jar from the shelf" vs. "From the shelf, select the jar".

specialist• 2 years ago

My initial reaction is that the pipes are redundant (syntactic vinegar). Syntactic order is sufficient.

The changes to my SQL grammar to accomodate this proposal are minor. Move the 'from' rule to the front. Add a star '*' around a new filters rule (eg zero-or-more, in any order), removing the misc dialect specific alts, simplifying my grammar a bit.

Drop the pipes and this would be terrific.

setr• 2 years ago

this is consistent, non-pseudo-english, reusable, and generic. The SQL standard largely defines the aesthetic of the language, and is in complete opposition to these qualities. I think would be fundamentally incorrect to call it SQL

Perhaps if they used a keyword PIPE and used a separate grammar definition for the expressions that follow the pipe, such that it is almost what you’d expect but randomly missing things or changes up some keywords

sklivvz19713• 2 years ago

We can call it "Linq2SQL" and what a disaster it was...

iblaine• 2 years ago

Looking at this reminds me of Apache Pig. That’s not a compliment.

nextaccountic• 2 years ago

At this point I think that vanilla SQL should just support optionally putting the from before the select. It's useful for enabling autocompletion, among other things.

RaftPeople6• 2 years ago

And a simple keyword that does a GROUP BY on all columns in select that aren't aggregates, just a syntax level macro-ish type of thing.

gajus• 2 years ago

What's the SQL equivalent of this?

DaiPlusPlus3• 2 years ago

The first-page of the paper has 13 co-authors listed - but all with the same affiliation ("Google, Inc") - so this is ultimately a single-vendor making a unilateral proposal to break with the past - which means I'm confident this proposal won't be gracing the pages of the ISO/IEC 9075 (ISO SQL) standards in my lifetime - no matter how badly we all need QoL improvements to SQL.

...okay, if I dial-back my feelings of resignation to mediocrity, then I'll admit that Google probably does have enough clout to make this go somewhere - but they'd need to add this to all their database offerings (BigQuery, Spanner, Firebase's SQL mode) and contribute patches to Postgres and MySQL/Maria - maybe after Microsoft relents a decade later to add it to MSSQL we'll maybe start to see Oracle's people refer to it vaguely as a nice-to-have they'll implement only after they start losing more blue-chip customers[1].

Also, it's giving me M (Excel PowerQuery) vibes too.

-------

[1]For context, Oracle's DB lacked a `bit`/`bool` column type for the past 40 years until last year. People had to use `char(1)` columns with CHECK constraints to store '0'/'1' - or worse: 'T'/'F' or 'Y'/'N' (see https://stackoverflow.com/a/3726846/159145 )

0cf8612b2e1e4• 2 years ago

… People had to use `char(1)` columns with CHECK constraints to store '0'/'1' - or worse: 'T'/'F' or 'Y'/'N'

If you are truly blessed you get to see all of these in a single database. Also “Yes”/“No” with that specific casing.

DaiPlusPlus3• 2 years ago

Not to be outdone: https://thedailywtf.com/articles/what_is_truth_0x3f_

orthoxerox• 2 years ago

"Check constraints make the database run slower, just don't generate any values other than YN in the application layer"

You can guess how many foreign keys that database had.

bhawks• 2 years ago

And to be fair it is not like any database implementations implement the entire spec, or that the spec itself is nearly as long as the C++ but still very underspecified.

Piped SQL fits in perfectly with the overall SQL pot-luck buffet! I for one welcome Google to the table, enjoy the language that works everywhere and nowhere but is the best there is.

kmoser• 2 years ago

I understand the desire to reorder clauses in a way that makes sense to the user, but they seem to discount the value in the formality of enforcing a particular order to the clauses: you're assured that if the query contains, say an ORDER BY clause, it will always appear at the end, no need to hunt for it.

Also, why the need to match semantic evaluation order when there are far more important things happening under the hood that affect the execution plan (indexes, etc.)?

Side-effects at a distance...The same columns are cross-referenced in SELECT, GROUP BY and ORDER BY, and corresponding edits are often required in three places

Can't this be avoided by using aliases?

akira25013• 2 years ago

when there are far more important things happening under the hood that affect the execution plan

It feels like google is only concerned with large query engines like BigQuery where you're relying on partitioning and sort order within partitions more than you can indexes or complicated multi table reference semantics.

Can't this be avoided by using aliases?

In any language where variables are hard to declare and use pipes always feel like a good option; however, just adding some variables in is probably the better solution in most cases.

misiek08• 2 years ago

Looks like from Elixir. If old SQL syntax will be supported then why not - but this one will introduce a lot less readable queries when multiple JOINs, subqueries and aggs are involved - it is very easy in plain SQL and here they will probably look bad.

ibash3• 2 years ago

Think it’ll be up to the query author to decide what’s most expressive.

Given it’s adding the pipe operator you can always only add it to the part of the query where it makes sense.

A nice sql linter for legibility could be great for this.

tmoertel9• 2 years ago

I'm glad to see pipe syntax moving forward! In my time at Google, I wrote a lot of SQL queries that required a long sequence of intermixed filtering, grouping, aggregation, and analytic window operations. But the ordering of those operations rarely matched SQL's inside-out syntax, so I commonly had to chop up my logic and glue it into multiple chained CTEs. This busywork made my code's logic harder to follow and was frustrating, especially since I was familiar with alternative coding models, such as R's dplyr, that would have allowed me to express my logic as a simple linear pipeline.

I hope SQL pipes become commonplace so that I can use them anywhere I have to write SQL queries.

geertj4• 2 years ago

What’s wrong with CTEs though? I have never thought of them as busywork and start with them if I know my query is going to be a multi-step process. I already thought them as “pipes” (in the abstract, not related to this paper). If the query flow is linear so is the sequence of CTEs.

The only slightly annoying thing with CTEs is that you always have to name them. You might consider this an overhead if the flow is strictly linear. But when the flow is not linear (eg when doing two different aggregations over the same data, to join them later) you need a way to refer to the intended CTE, so always requiring a name does add some consistency and doesn’t seem a big deal overall.

magicalhippo3• 2 years ago

What’s wrong with CTEs though?

Depends on DB engines I suppose. I've come across that certain operations were not allowed in CTEs, and they can be an optimization barrier.

However if your query is dynamically modified at runtime, then CTEs can be a no-go. For example, we have a grid component which first does a count and then only selects the visible rows. This is great if you have expensive subselects as columns in a large table. However to do the counting it turns the main query into a sub-query, and it doesn't handle CTEs.

tmoertel9• 2 years ago

CTEs are not inherently busywork. I rather like them. What is busywork is having to chop a linear flow of operations into chunks and then wrap those chunks in CTEs that you must wire together. All this, simply because the SQL syntax doesn't let you express that flow directly.

RaftPeople6• 2 years ago

What’s wrong with CTEs though?

At least in SQL Server CTE's are syntax level, so multiple uses of a CTE in a query causes it to get expanded in each of those places, which typically increases the complexity of the query and can cause issues with the optimizer and performance.

svat4• 2 years ago

The paper's section 5.1 (“Usage at Google”) and its Figure 4 (page 9) shows the usage growing steadily, “despite limited documentation and incomplete tooling” — of course 1600 users is still small relative to the number of SQL users at Google, and it's possible the growth will plateau at some point, but I for one adopted pipe syntax the very day I saw it (even sent an excited email to their mailing list), and have been using it ever since. As it's an extension, it's always possible to use regular SQL for parts of the same query (copying others' queries etc), but often I find myself rewriting queries into pipe SQL and find it significantly simplifies them (CTEs can be removed, etc).

humanfromearth9• 2 years ago

I upvote you for using CTEs. It can be so powerful.

solidsnack9000• 2 years ago

This may be the most practical way to make things better for developers, analysts, data scientists...

There have been so many attempts to alleviate the difficulty of writing SQL -- from ORMs, to alternate syntaxes, to alternate databases -- and none have been successful. The authors identify many reasons for this. The approach the authors have taken is incremental and makes only slight demands one people already familiar with elementary SQL -- and even people familiar with advanced SQL will likely find this approach to be easier to use for advanced queries.

croes• 2 years ago

Can the first example not simply be done with a window function?

10000truths• 2 years ago

The pipeline syntax as presented is nicer than the status quo, but I'd prefer a syntax that models query execution as a directed graph of operations. Doing so would not only make some of the more complex SQL query constructs much more straightforward to represent:

* Joins can be modelled as a "cross-referencing" operation that consume two (or more) data streams and produce a single data stream

* CTEs can be modelled as producing multiple data streams

* Recursive CTEs can be modelled as cycles in the execution graph

almostgotcaught5• 2 years ago

a directed graph of operations

What syntax do you know that can represent a dag in text?

RyanHamilton• 2 years ago

A = select * from tbla

B = select * from tblb

C = select * from A join B

cha42• 2 years ago

Any syntax with a let operator to name stuff or a lambda abstraction.

mr_toad5• 2 years ago

Something like the DOT language used in GraphViz

https://graphviz.org/doc/info/lang.html

aranke• 2 years ago

Check out Substrait, it sounds like what you’re describing.

ram_rar• 2 years ago

I appreciated the paper's effort to highlight data analysis techniques, but I was hoping for a more forward-thinking perspective.

The inclusion of pipe syntax or data processing using pipe-structured data flow syntax is not very novel at all. Splunk's SPL and similar languages have been a longstanding industry practice.

I wish the paper had provided more extensive references to the existing body of work in this domain, as it would have added depth and context to the discussion.

gfody2• 2 years ago

I seriously wonder if the people who are so adament that sql is flawed have spent as much time using at as they have trying to "fix" it. After 20 years of sequeling I have come to believe that this language is so far ahead of its time that we're only just beginning to see what a proper tooling for it looks like. Azure Data Studio w/Copilot makes starting queries with "select" the most natural thing in the world and this pipe syntax is barbaric in contrast.

cl3misch• 2 years ago

I think their claim isn't that it's impossible to be efficient in existing SQL but rather that pipe syntax is more natural and approachable to a lot of people?

abelcha3• 2 years ago

well maybe people dont want to suffer for 20 years

parpfish2• 2 years ago

I think that engineers and analysts each have very different relationships with SQL.

When I was doing data science, all the other DS folks would be perfectly content to read and write queries that were hundreds of lines long. There were plenty of minor bits to pick, but it was a great lingua franca for describing data processing.

But engineers hate SQL because they generally only need a tiny little subset of the feature to enable transactional data updates. So they write an ORM to do the subset of SQL they need and never get the opportunity to be indoctrinated into the SQuLt

brikym2• 2 years ago

I disagree. KQL is much nicer. It reads better having the source first and this also offers autocompletion in the editor.

beart• 2 years ago

After 10 years of sequeling, I disagree. Maybe I'll feel differently in 10 more years, but I somehow doubt it.

Using an AI model that ate up half the internet to produce an efficient autocomplete is one path... Improving the language is another...

rawgabbit• 2 years ago

It looks great. When is Google going to create an IDE or extension that I can use?

chuckadams• 2 years ago

I wonder if the pipe operator will land in SQL before we see it in JS.

scop• 2 years ago

Reminds me of Elixir’s Ecto library.

https://hexdocs.pm/ecto/crud.html#pipe-based-queries

freedomben• 2 years ago

That was my first thought too. I think working with ecto really primed me to like this syntax. I like it quite a bit

sklivvz19713• 2 years ago

SQL doesn't have problems. It's arguably one of the oldest and most successful languages of all times.

Some developers have problems, because they don't understand its abstraction. Periodically they try to re-implement feature to "fix" SQL. Every time it's an absolute disaster.

I realize this is how the world works, but it's just a waste of time.

tmoertel9• 2 years ago

Some developers have problems, because they don't understand its abstraction.

I think the point of this new syntax is to lower the cost of understanding the abstraction. You keep the familiar clause syntax but let clauses be chained into pipelines in which the actual (semantic) execution order is the same as the written order.

sklivvz19713• 2 years ago

I think this new syntax is more imperative (you tell the DB "how" to get you the data). The old syntax is more declarative (you tell the DB "what" you need).

The big advantage is that the SQL engine can optimize the how if you focus on the what -- especially after so much research has been don building query compilers.

If you want to focus on the "how" then perhaps a "bare bones" DB like Redis is more in line with this kind of thinking.

r1b2• 2 years ago

It’s strange to me that the interop story here only considers compatibility with GoogleSQL.

These days, we have tooling that can generate an IR for SQL and transpile it to any dialect (see: sqlglot).

Doesn’t coupling SQL syntax to dialect-specific SQL semantics throw a wrench in this?

sagarm4• 2 years ago

You could implement a family of poorly defined dialects using SQLGlot, sure, in the same way you could define a meta-language targeted at Algol-family languages. That's not a substitute for adding language features.

hintymad• 2 years ago

Piping syntax is particularly useful when querying time series too, especially when we need to write complex queries in a one-liner to share with others.

mocamoca• 2 years ago

Question for people writing highly complex SQL queries.

Why not write simple SQL queries and use another language to do the transformations?

Are SQL engines really more efficient at filtering/matching/aggregating data when doing complex queries? Doesn't working without reusable blocks / tests / logs make development harder?

Syntax is one thing, but actual performance (and safety/maintenance) is another deal?

SahAssar• 2 years ago

Why not write simple SQL queries and use another language to do the transformations?
Are SQL engines really more efficient at filtering/matching/aggregating data when doing complex queries?

Yes. With knowledge of the data (like indices) and statistics of the data it is usually very much more efficient than piping the data to another process to handle the same job.

Of course that requires you to write a good query and know how to tell what makes a good query, but if your data is in a relational database it is usually more efficient to do your filtering/matching/aggregating there.

javcasas• 2 years ago

I'm not interested in loading all the data in memory, or swapping it out if it doesn't fit, but also it may be a lot of data, and just moving it over the network is hard.

I have tests. I have reusable blocks (SQL functions, WITH blocks and views). I don't have logging though.

I can put the result in a non-materialized view and have it update in real time as the data changes. Or I can toggle it to materialized view and now it's snapshotted data.

Finally, views that depend on views that depend on views get automatically optimized by the query planner. You need a lot of very tricky custom code to start approaching that.

fifilura• 2 years ago

One reason SQL has become more popular lately is as an API for map/reduce.

Before you would write a Java/c++ class that would do the map/reduce job for you distributed over 100s of CPUS. And you would feel like you were on the bleeding edge doing innovative stuff.

Turns out that SQL is a perfect API for map/reduce.

Everything you write in SQL can be transformed into a massively parallel job. And you don't even know about it.

This is the secret behind BigQuery and Trino/Presto/Athena.

mr_toad5• 2 years ago

If your data is relational. SQL can get pretty ugly with semi-structured data, graph traversal etc.

crazygringo2• 2 years ago

Performance. A million times performance.

If I need to join a million rows to another table and then calculate an aggregate value, and do it all in a single query, it's fast. It might only take 0.01 seconds.

Whereas if I make separate queries to retrieve a million rows, and a million rows from another table, then it's incredibly slow just due to the data transfer. No matter how conceptually easier another language may be. So it might take 30 seconds for repeated sequential serialization and network and deserialization.

And even if you're looking up individual rows (not a million), with lots of joins that would be lots of round-trips to the database which multiplies latency and load -- so even if you can still get your final result quickly (e.g. 0.01 seconds rather than 0.001 seconds), the load you can handle drops by the same multiple (e.g. you can now only serve 10% as many users).

The general rule of thumb with databases is that they can be super-fast when everything is done on the database machine in a single query and your final result is a small amount of data (assuming everything is indexed properly and the query is written properly). But they become super-slow when you're doing a bunch of queries in a row, and where those intermediate queries can return massive amounts of data, or (even worse) need to send back massive amounts of data.

sagarm4• 2 years ago

I've worked on a few SQL systems used for analytics and ETL.

My users fell into (for the purposes of this discussion) three categories:

1. Analysts who prefer sheets

2. Data scientists that prefer pandas

3. Engineers who prefer C++/Java/JavaScript/Python

I'm fairly sure SQL isn't the first choice for any of them, but in all three cases a modern vectorized SQL engine will be the fastest option for expressing and executing many analysis and ETL tasks, especially when the datasets don't fit on a single machine. It's also easier to provide a shared pool of compute to run SQL than arbitrary code, especially with low latency.

Even as a query engine developer, I would prefer using a SQL engine. Performing even the basic optimizations a modern engine would perform -- columnar execution, predicate pushdown, pre-aggregation for shuffles, etc -- would be at least a week of work for me. A bit less if I built up a large library to assist.

xpe2• 2 years ago

Re #2: I prefer https://pola.rs over Pandas

wvenable6• 2 years ago

Are SQL engines really more efficient at filtering/matching/aggregating data when doing complex queries?

As others have said, yes. In most cases, the more complex the query the better the result.

Doesn't working without reusable blocks / tests / logs make development harder?

SQL isn't a programming language, it's a query language. You're effectively writing a single expression that describes the shape of the data that you want and then then the RDBMS goes off and finds the most performant way to retrieve the data that matches that shape. It doesn't compare well with procedural programming.

The closest "languages" that comparable to SQL are HTML and CSS. However you manage HTML and CSS is also how you can manage SQL.

crazygringo2• 2 years ago

> Doesn't working without reusable blocks / tests / logs make development harder?
SQL isn't a programming language, it's a query language. You're effectively writing a single expression that describes the shape of the data that you want...

Exactly this. Generally speaking, your SQL queries won't have "bugs" the way that you can create bugs when writing a function.

The challenging parts of building a complex query are usually 1) getting it to function at all (just being a valid SQL statement for your tables that gives you your desired output fields), and 2) making sure it runs performantly on realistically large table sizes (usually in milliseconds as opposed to seconds), which may involve rewriting things like joins vs. subqueries and/or adding indexes.

A lot of bugs in functions come from edge cases or different combinations of paths through code or unexpected combinations of parameter values or math formula errors or whatever... but a SQL query won't usually really have any of those things. It's just a single transformation that basically either works or doesn't.

flusteredBias• 2 years ago

... so dplyr.

nsonha• 2 years ago

What's wrong with single character pipe (|)? Not everyone uses ligatures even if they like that particular symbol. Just keep things simple please

svat4• 2 years ago

Great question, answered in section 4.1.5 of the paper (page 6): it turns out that the single character | is already used for bitwise OR, and although that's rarely used and in most cases there would be no confusion, there are at least a few cases, and at this time, “More drastic solutions requiring users to migrate queries did not seem desirable.”

saltcured• 2 years ago

I don't have time to read this closely or ponder the grammar right now. They have a section on "complex queries" that acknowledges my first concern, but doesn't seem to really address it. Namely, that SQL allows tree-like composition of queries which is more general than a linear composition.

Has anybody figured out whether they are proposing this pipeline syntax to be mixed with regular compositional forms like CTEs and subqueries? Or is it another limited, top-level syntax, similar to how some DB engines do not allow nesting of CTE syntax?

svat4• 2 years ago

this pipeline syntax to be mixed with regular compositional forms like CTEs and subqueries?

Yes, it's an extension (available by default), which means you can freely mix with regular SQL and use pipes for just parts of your query.

https://github.com/google/zetasql/blob/2024.08.2/docs/pipe-s...

Pipe syntax can be mixed with standard syntax in the same query. For example, subqueries can use different syntax from the parent query.

thom• 2 years ago

Of all the problems with SQL, this solves one I absolutely don’t care about at all. Kill SQL or don’t kill SQL, but don’t add pointless syntax sugar to make normal queries look alien.

mingodad• 2 years ago

I've just update the EBNF for rarilroad diagram here https://github.com/google/zetasql/issues/134#issuecomment-14... with the full grammar.

darksaints• 2 years ago

I've got plenty of complaints about SQL, but as one of the most useful programming languages to have ever been invented, I have to say that syntax complaints are one of lowest items I would have on my list of things to be prioritized for a change. Sure, the syntax could be better, but why do we care so much about it over the dozens of other problems with it?

How about we get a SQL successor with algebraic data types, true boolean logic (as opposed to SQL's ternary logic), or functional composition? Null values are the bane of any query writer's existence, and we should have a reasonable solution by now...we've already done it with other programming languages.

crabmusket• 2 years ago

It sounds like you want SQL to be more like a "real programming language", but I feel like there's a real chasm. SQL itself seems to be oriented towards "non-programmers" which is why it has declarative English syntax. But so many systems that interface with SQL databases are software systems written by programmers.

Why are our programming languages, which have rich ADTs, Boolean logic, etc. serialising queries into an English syntax written for ad-hoc business intelligence tasks? Why not have a binary query interface to the database that provides a programmatic, rather than human-readable, API?

The first time I got a "too many bind variables" error I was flabbergasted. All I wanted to do was insert a ton of rows into a table. But the database expects me to construct an English sentence containing a placeholder for each value of each row?

chucke1992• 2 years ago

Fix it by piping

simonw7• 2 years ago

Google's open source ZetaSQL project added documentation covering pipe syntax last week, which I'm finding easier to follow than this paper:

https://github.com/google/zetasql/blob/2024.08.2/docs/pipe-s...

ccorcos• 2 years ago

Now we just need a healthy SQL transpiler ecosystem so we can all live in the future!

ec109685• 2 years ago

Yes, and to try new features to see if they gain traction before standardization.

r1b2• 2 years ago

This was my first thought as well. I like the language, but I’m hesitant to adopt it without tooling that can extract an AST, ideally interoperable with the hard-won tooling we already have for SQL.

Improved UX at query development time is nice, but data teams need so much more than this. Stuff like lineage, linting, cross-dialect interop - these are no longer niche concerns, they are table stakes, and they come from having a shared syntax for data transformation.

typedef_struct• 2 years ago

You can tell the authors realized this was a bad idea when they had to add the 'OVER' keyword, which isn't documented and hardly mentioned in the paper.

roryokane• 2 years ago

ZetaSQL’s docs for the ‘OVER’ keyword you mention: https://github.com/google/zetasql/blob/master/docs/window-fu...

I disagree that the paper not mentioning ‘OVER’ implies that the paper authors secretly think pipe syntax is a bad idea. They probably just wanted to keep the paper concise, or forgot about that one less-used bit of syntax.

Do you think that ‘OVER’ keyword implies something fundamentally wrong about pipe syntax? If so, how?

nhinck2• 2 years ago

https://en.m.wikipedia.org/wiki/Window_function_(SQL)

They didn't add anything.

mrjin• 2 years ago

Starting with SELECT was a mistake in SQL as you need to know where before what to select from. What we really need is something like

FROM r JOIN s on r.Id = s.Id ORDER BY r.Id SELECT *

But the thing is, such changes will break pretty much all existing code, so the author added |> to distinguish, but why not use | instead? Don't make people typing one more character please.

svat4• 2 years ago

Yes, being able to start with FROM is what makes this so intuitive to use (and autocomplete work better); your hypothetical

    FROM r JOIN s on r.Id = s.Id ORDER BY r.Id SELECT *

would indeed be pretty much that (linebreaks optional):

    FROM r JOIN s on r.Id = s.Id 
    |> ORDER BY r.Id 
    |> SELECT *

The question about “typing one more character” is answered in the paper's section 4.1.5 “Why use ‘|>’ for the pipe character?” (page 6): “The most natural and obvious choice would be to use ‘|’. Unfortunately…” — they don't want to break existing queries that use | for bitwise OR.

(I wonder if one day, if the majority of users are using pipe syntax and don't mind updating their old queries or adding a special directive to enable single pipe…)

brikym2• 2 years ago

It's been done before: Kusto Query Language

snidane• 2 years ago

This style is familiar to those writing dataframe logic in df libraries with sql semantics - spark, polars or duckdb relational (https://duckdb.org/docs/api/python/relational_api.html).

It definitely makes things easier to follow, but only for linear, ie. single table, transformations. The moment joins of multiple tables come into the picture things become hairy quick and then you actually start to appreciate the plain old sql which accounts for exactly this and allows you to specify column aliases in the entire cte clause. With this piping you lose scope of the table aliases and then you have to use weird hacks like mangling names of the joined in table in polars.

For single table processing the pipes are nice though. Especially eliminating the need for multiple different keywords for filter based on the order of execution (where, having, qualify (and pre-join filter which is missing)).

A missed opportunity here is the redundant [AGGREGATE sum(x) GROUP BY y]. Unless you need to specify rollups, [AGGREGATE y, sum(x)] is a sufficient syntax for group bys and duckdb folks got it right in the relational api.

nickpeterson• 2 years ago

It’s even more familiar to F# developers, which it almost looks identical to.

imperfect_light• 2 years ago

But SQL is an old language with significant design problems, making it difficult to learn, difficult to use, and difficult to extend.

It's difficult to learn and use? Compared to what? The "difficult to extend" might be accurate, but I really question the initial premise here.

jwilber• 2 years ago

This is almost exactly how R’s dplyr library works, and it’s honestly a pretty ergonomic, intuitive way to write queries.

singularity2001• 2 years ago

on iPad FF when I tried to view the PDF it downloads binary gibbous instead

cryptonector4• 2 years ago

This strikes me as a) yet another FROM-clause-first variant of SQL, b) syntax for LinkQ.

sitkack• 2 years ago

Reminds me of Pig. Or a pathological stack of CTEs.

Doesn't look compelling for the lockin.

vendiddy2• 2 years ago

Are there any gentle intros to how a simple SQL engine implemented under the hood?

I vaguely remember someone telling me that it has mathematical underpinnings.

sgarland2• 2 years ago

I personally find the syntactical flow of SQL to be quite logical: you SELECT tuples FROM a table, JOINing others as needed, filtering WHERE necessary. It’s much like list comprehensions in Python, which I love, so maybe that's biasing me.

yazaldefilimone• 2 years ago

I built a compiler based on the ideas from this paper: https://github.com/yazaldefilimone/spipe.

Pipe Syntax in SQL

Related Stories