14 ms·
Pipe Syntax in SQL
- deleted 2y ago[deleted]
- simonw 2y agoGoogle: you are a web company. Please learn to publish your research papers as web pages.
- orangepanda 2y agoI expected to see some eldritch css monstrosity, but no, its just a pdf. A well formatted one, at that. What’s your issue there?
- simonw 2y agoReading 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?
- lrem 2y agoIndeed. That’s the easiest way to show your students/professor/coworkers which are the crucial bits.
- tmoertel 2y agoJust my personal take, but when I have to read something carefully, I find it easier to do on paper. For example, I recently wrote an article about taking random samples using SQL. Even though I was writing it for my blog, which is HTML, I proofread the article by rendering it as a PDF doc, printing it out, and reviewing it with a blue pen in hand. What surprised me is that I also found it easier to review the article on the screen when it was in PDF format. TeX just does a way better job of putting words on a page than does a web browser. Actually, if you want to do the comparison yourself, I'll put both versions online: HTML: https://blog.moertel.com/posts/2024-08-23-sampling-with-sql.html https://blog.moertel.com/posts/2024-08-23-sampling-with-sql.... PDF: https://blog.moertel.com/images/public_html/blog/pix-20240601/sampling-with-sql.pdf https://blog.moertel.com/images/public_html/blog/pix-2024060... I don't think either version is hard to read, but if I had my choice, I'd read the PDF version. But maybe that's just me. Let me know which you prefer.
- LeonB 2y agoOn mobile phone, as a reader with photophobia, the pdf causes physical pain, and is illegible, whereas the html is perfectly readable via reader mode (where text can be enlarged and dark mode settings are respected.
- tmoertel 2y agoThanks for sharing this perspective! HTML is a lot more accessible in general than PDF documents. QQ: Do the math formulas render properly in reader mode for you? (On my test with Chrome, the answer seems to be no.)
- samatman 2y agoPersonally, it's sending it to GoodReader on a 13" iPad. I don't know that I'd go so far as to say I 'prefer' this, but there are a lot of PDFs out there, this works fine, and it's a nice change of pace given how much time I spend in front of a monitor / laptop screen.
- VoodooJuJu 2y agoPDF content is not web-indexed. Their Google Scholar link doesn't even work either.
- estebarb 2y agoConference papers use templates. It's not like Google can choose.
- simonw 2y agoThey can choose to publish it in both HTML and PDF.
- lrem 2y agoMaybe. Maybe not. Depends on the publisher’s terms.
- goodfight 2y agoif you replace in an arxiv.org link with ar5iv.org it will auto translate to html if possible
- DonaldPShimoda 2y agoTranslating LaTeX to HTML is not a straightforward process, unfortunately. Many people have tried to implement automated translation systems, but nothing has really worked out yet. I think it's unfair to expect the research team to invest additional hours in learning how to make good websites, so to solve your problem would require hiring additional talent whose only job is to translate academic PDFs into accessible web pages. I don't think that's a bad idea, and certainly Google has the funds to do something like that, but I don't imagine they'd find it to be a good use of money. Accessibility is an afterthought for most major companies these days.
- lrem 2y agoThat’s not a blog post. This is an academic preprint, I imagine the format is as prescribed.
- irrelative 2y agoSeriously. It’s not like that was the actual purpose of html or anything.
- jml7c5 2y agoI 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.
- simonw 2y agoOne 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.
- jml7c5 2y agoYes, but it's not guaranteed to be self-contained. I wouldn't want to open a random HTML file knowing that it could phone home, or that the content might break one day without me realizing. There's a practical and psychological aspect to sharing `steves_paper_2014.html` versus `steves_paper_2014.offlinesitebundle`. The latter feels safe and immutable.
- irq-1 2y agoWhat you want is an HTML tag or response header that restricts network access, which the browser can then enforce. Offline or a list of allowed domains, this would be great for security in general. Not so great for advertisers though.
- mewpmewp2 2y agoThen you have to verify that the tag is there, right? But if it has another extension like .offlinebundle you can know thay browsers will not make any extra requests.
- abelcha 2y ago
- deleted 2y ago[deleted]
- slt2021 2y agoreminds me PRQL https://prql-lang.org https://prql-lang.org and SPL from Splunk
- mark_and_sweep 2y agoReminds me of KQL (Kusto Query Language) by Microsoft
- LeonB 2y agoYeh, 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.
- _huayra_ 2y agoDoes KQL still enforce no blank lines in the `let` clauses to the expression? When I last used KQL, it was infuriating that I could create my `let` clauses in chunks separated by whitespace because a blank line would be considered a terminated statement (and Kusto would say "Hey where's your expression?!"). This meant every Kusto file was a sea of text with no clear differentiation between subsequent clauses. I ended up using 3 blank comment lines as a "fake empty line" just to maintain my sanity. Again, hope they fixed that by now...
- simonw 2y agoTo 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-SQL.html https://static.simonwillison.net/static/2024/Pipe-Syntax-In-...
- dang 2y ago(This was originally a reply to https://news.ycombinator.com/item?id=41339138 https://news.ycombinator.com/item?id=41339138 but I detached it so the useful link could float higher in the thread - thanks!)
- simonw 2y agoI wrote up a few extra notes about how I did this here: https://simonwillison.net/2024/Aug/24/pipe-syntax-in-sql/ https://simonwillison.net/2024/Aug/24/pipe-syntax-in-sql/
- ku1ik 2y agoI 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.
- atum47 2y agoSame 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 2y agoIt'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 :)
- pajeets 2y agoI guess I'm shameless for generating 98% of my SQLs
- atum47 2y agoThere are variations between mysql and mariadb, I've used mysql for a long time now, when I switched to mariadb I found those differences. I find writing complex queries on the console first and then committing it to the code a good strategy. I can see the results right away and some error that I might have done.
- ibash 2y ago+1 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…
- Ciantic 2y agoHere 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.
- randomdata 2y 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?
- ibash 2y agoYes 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 2y agoNot 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?
- pajeets 2y agoYes, 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.
- DaiPlusPlus 2y agoThe 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 https://stackoverflow.com/a/3726846/159145 )
- 0cf8612b2e1e 2y 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.
- DaiPlusPlus 2y agoNot to be outdone: https://thedailywtf.com/articles/what_is_truth_0x3f_ https://thedailywtf.com/articles/what_is_truth_0x3f_
- orthoxerox 2y 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.
- 2y ago
- kmoser 2y agoI 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?
- akira2501 2y 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 2y agoLooks 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.
- ibash 2y agoThink 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.
- tmoertel 2y agoI'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.
- geertj 2y agoWhat’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.
- magicalhippo 2y 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.
- geertj 2y agoUnderstood. I should have asked my question a bit more specifically: what's wrong with CTEs that wouldn't be an issue with this new pipe syntax. I briefly scanned the paper and it appears there aren't any specific benefits to the pipe syntax that would make optimization easier. So we can expect that if a SQL engine doesn't optimize CTEs well it would likely have the same limitations for the pipe syntax. Section 2.1.4 the paper lists the benefits of the pipe syntax over CTEs, and they are all based on ergonomics. As someone who has never had issues with the ergonomics of CTEs I must say I am not convinced that proposed syntax is better. It may be that I've been doing SQL for so long that I don't see its warts. Overall SQL feels like a very well designed and consistent language to me. The new pipe syntax appears to bolt on an imperative construct to an otherwise purely functional language.
- solidsnack9000 2y agoThis 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 2y agoCan the first example not simply be done with a window function?
- 10000truths 2y agoThe 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
- almostgotcaught 2y ago> a directed graph of operations What syntax do you know that can represent a dag in text?
- RyanHamilton 2y agoA = select * from tbla B = select * from tblb C = select * from A join B
- wtetzner 2y agoI guess CTEs already provide that (even if they're a bit clunky). WITH a AS (select * from tbla), b AS (select * from tblb) select * from a join b
- almostgotcaught 2y agoYes we already have that, it's called `async def`.
- cha42 2y agoAny syntax with a let operator to name stuff or a lambda abstraction.
- almostgotcaught 2y agoThat only gives you trees not DAGs - you can't do fan-in (there's no way to "share" let bound names).
- ram_rar 2y agoI 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.
- gfody 2y agoI 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 2y agoI 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?
- abelcha 2y agowell maybe people dont want to suffer for 20 years
- parpfish 2y agoI 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
- brikym 2y agoI disagree. KQL is much nicer. It reads better having the source first and this also offers autocompletion in the editor.
- beart 2y agoAfter 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 2y agoIt looks great. When is Google going to create an IDE or extension that I can use?
- chuckadams 2y agoI wonder if the pipe operator will land in SQL before we see it in JS.
- scop 2y agoReminds me of Elixir’s Ecto library. https://hexdocs.pm/ecto/crud.html#pipe-based-queries https://hexdocs.pm/ecto/crud.html#pipe-based-queries
- freedomben 2y agoThat was my first thought too. I think working with ecto really primed me to like this syntax. I like it quite a bit
- sklivvz1971 2y agoSQL 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.
- tmoertel 2y 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.
- sklivvz1971 2y agoI 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.
- creatonez 2y 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). I think this syntax could accidentally give the impression that this is what's happening, but it's still an abstraction thanks to the optimizer. The "how" a pipe syntax describes roughly aligns with how a human would naturally want to construct a machinated process to sort through a file cabinet. Which may have been why it's been such a big hit for Unix and functional programming, despite poorly mapping to the machine model before the optimizations of modern OSes and functional compilers came along. Any choice whatsoever might give a false impression of the machine model. Maybe SQL is doing the right thing by essentially having its syntax elements in random order, thwarting any attempts by developers to relate what they are typing to what is actually happening, but I think the authors of this paper are right in their desire to make it more ergonomic.
- r1b 2y agoIt’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?
- sagarm 2y agoYou 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 2y agoPiping 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 2y agoQuestion 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 2y 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.
- deleted 2y ago[deleted]
- javcasas 2y agoI'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 2y agoOne 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.
- flusteredBias 2y ago... so dplyr.
- nsonha 2y agoWhat's wrong with single character pipe (|)? Not everyone uses ligatures even if they like that particular symbol. Just keep things simple please
- svat 2y agoGreat 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 2y agoI 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?
- svat 2y 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-syntax.md 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 2y agoOf 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 2y agoI've just update the EBNF for rarilroad diagram here https://github.com/google/zetasql/issues/134#issuecomment-1466668763 https://github.com/google/zetasql/issues/134#issuecomment-14... with the full grammar.
- darksaints 2y agoI'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 2y agoIt 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?
- cryptonector 2y agoThis strikes me as a) yet another FROM-clause-first variant of SQL, b) syntax for LinkQ.
- chucke1992 2y agoFix it by piping
- simonw 2y agoGoogle'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-syntax.md https://github.com/google/zetasql/blob/2024.08.2/docs/pipe-s...
- ccorcos 2y agoNow we just need a healthy SQL transpiler ecosystem so we can all live in the future!
- ec109685 2y agoYes, and to try new features to see if they gain traction before standardization.
- r1b 2y agoThis 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 2y agoYou 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 2y agoZetaSQL’s docs for the ‘OVER’ keyword you mention: https://github.com/google/zetasql/blob/master/docs/window-function-calls.md#def_over_clause 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 2y agohttps://en.m.wikipedia.org/wiki/Window_function_(SQL) https://en.m.wikipedia.org/wiki/Window_function_(SQL) They didn't add anything.
- mrjin 2y agoStarting 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.
- svat 2y agoYes, 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…)
- brikym 2y agoIt's been done before: Kusto Query Language
- snidane 2y agoThis 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 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 2y agoIt’s even more familiar to F# developers, which it almost looks identical to.
- imperfect_light 2y 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 2y agoThis is almost exactly how R’s dplyr library works, and it’s honestly a pretty ergonomic, intuitive way to write queries.
- singularity2001 2y agoon iPad FF when I tried to view the PDF it downloads binary gibbous instead
- sitkack 2y agoReminds me of Pig. Or a pathological stack of CTEs. Doesn't look compelling for the lockin.
- vendiddy 2y agoAre 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.
- sgarland 2y agoI 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 2y agoI built a compiler based on the ideas from this paper: https://github.com/yazaldefilimone/spipe https://github.com/yazaldefilimone/spipe.