[HN Gopher] Pipe Syntax in SQL
___________________________________________________________________
Pipe Syntax in SQL
Author : legrangramgroum
Score : 201 points
Date : 2024-08-24 15:15 UTC (7 hours ago)
(HTM) web link (research.google)
(TXT) w3m dump (research.google)
| simonw wrote:
| Google: you are a web company. Please learn to publish your
| research papers as web pages.
| orangepanda wrote:
| 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?
| simonw wrote:
| 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?
| lrem wrote:
| Indeed. That's the easiest way to show your
| students/professor/coworkers which are the crucial bits.
| tmoertel wrote:
| Just 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....
|
| PDF: https://blog.moertel.com/images/public_html/blog/pix-2
| 024060...
|
| 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 wrote:
| On 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 wrote:
| Thanks 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.)
| LeonB wrote:
| I don't think the formulas are rendered in reader view.
| (iOS Safari)
|
| In the browser (iOS Safari) I use an extension (dark
| reader) to give it a dark theme, and the formulas render
| just fine there.
| VoodooJuJu wrote:
| PDF content is not web-indexed. Their Google Scholar link
| doesn't even work either.
| estebarb wrote:
| Conference papers use templates. It's not like Google can
| choose.
| simonw wrote:
| They can choose to publish it in both HTML and PDF.
| lrem wrote:
| Maybe. Maybe not. Depends on the publisher's terms.
| lrem wrote:
| That's not a blog post. This is an academic preprint, I imagine
| the format is as prescribed.
| irrelative wrote:
| Seriously. It's not like that was the actual purpose of html or
| anything.
| jml7c5 wrote:
| 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.
| simonw wrote:
| 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.
| jml7c5 wrote:
| Yes, 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 wrote:
| What 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.
| abelcha wrote:
| this is what you are looking for i believe (web archive
| format) https://en.wikipedia.org/wiki/WARC_(file_format)?oldf
| ormat=t...
| jml7c5 wrote:
| Browsers don't have native support for opening WARC. It
| doesn't solve the safety problem either: you can still
| construct a WARC that phones home, AFAIK.
|
| It's a great format for the problem it solves, but if
| browsers supported offline-only files the container format
| wouldn't (and shouldn't) need to be that complicated.
| slt2021 wrote:
| reminds me PRQL https://prql-lang.org
|
| and SPL from Splunk
| mark_and_sweep wrote:
| Reminds me of KQL (Kusto Query Language) by Microsoft
| LeonB wrote:
| 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.
| _huayra_ wrote:
| Does 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...
| LeonB wrote:
| I'm not sure if that's fixed, it seems pretty much "by
| design", with the way the editor (in the browser) selects
| the "current query" for you.
| beoberha wrote:
| I'm nearly positive that's an editor "feature". If you
| submit queries via the API, you can have blank lines.
| Nathanba wrote:
| unfortunately KQL doesn't seem to have INSERT, UPDATE etc.
| support, it seems to be a pure query language for querying.
| Unless this strange different .insert syntax is what they
| intended for their language from the start? I don't know:
| https://learn.microsoft.com/en-us/kusto/management/data-
| inge...
| LeonB wrote:
| Good question, I don't know i've only used it for
| querying.
|
| For SQL to Have a good/improved syntax for
| insert/update/delete/merge would be very nice (in theory)
| wizardist wrote:
| Also OpenSearch PPL.
| feldrim wrote:
| 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.
| simonw wrote:
| 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 wrote:
| (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!)
| ku1ik wrote:
| 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.
| atum47 wrote:
| 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 wrote:
| 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 :)
| pajeets wrote:
| I guess I'm shameless for generating 98% of my SQLs
| ibash wrote:
| +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...
| pajeets wrote:
| 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 wrote:
| 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.
| ibash wrote:
| 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 wrote:
| 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 wrote:
| _> 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 wrote:
| 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 wrote:
| > GoogleSQL
|
| EssGyooGell: A Modest Proposal
| pajeets wrote:
| 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 wrote:
| Honestly, it seems like a band-aid on legacy query language.
| lpapez wrote:
| SQL a legacy query language?
|
| In order for a thing to be considered legacy, there needs
| to be a widespread successor available.
|
| SQL might have been invented in the 70s but it's still
| going strong as no real alternative has been widely adopted
| so far - I'd wager that you will find SQL at most software
| companies today.
|
| Calling it legacy is not realistic IMO.
| Spivak wrote:
| I mean kinda? It's legacy in the "we would never invent
| this as the solution to the problem domain that's today
| asked of it."
|
| We would invent the underlying engines for sure but not
| the language on top of it. It doesn't map at all to how
| it's actually used by programmers. SQL is the JS to
| WebAssembly, being able to write the query plan directly
| via whatever language or mechanism you prefer would be
| goated.
|
| It has to be my biggest pain point dealing with SQL,
| having to hint to the optimizer or write meta-SQL to get
| it to generate the query plan I already know I want
| dammit! is unbelievably frustrating.
| wvenable wrote:
| By that definition JavaScript is also legacy.
|
| > having to hint to the optimizer or write meta-SQL to
| get it to generate the query plan I already know I want
| dammit'
|
| That's not in the domain of SQL. If you're not getting
| the most optimized query plan, there is something wrong
| with the DBMS engine or statistics -- SQL, the language,
| isn't supposed to care about those details.
| lpapez wrote:
| > It's legacy in the "we would never invent this as the
| solution to the problem domain that's today asked of it."
|
| I don't think that definition of legacy is useful because
| so many things which hardly anyone calls "legacy" fit the
| definition - for example: Javascript as the web standard,
| cars in cities and bipartisan democracy.
|
| I think many of us would say that that none of these is
| an ideal solution for the problem being solved, but it's
| what we are stuck with and I cannot think anyone could
| call it "legacy systems" until a viable successor is
| widespread.
| andy800 wrote:
| 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.
| singron wrote:
| 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.
| 0cf8612b2e1e wrote:
| Indeed. Just as I think git's N different ways to refer to
| the same operation was a blunder.
| andy800 wrote:
| But pre- and post- aggregation filtering is not really
| "the same" operation.
| 0cf8612b2e1e wrote:
| If I use a CTE and filter the aggregate, feels the same
| to me.
| andy800 wrote:
| If you perform an aggregation query in a CTE, then filter
| on that in a subsequent query, that is different, because
| you have also added another SELECT and FROM. You would
| use WHERE in that case whether using a CTE or just an
| outer query on an inner subquery. HAVING is different
| from WHERE because it filters after the aggregation,
| without requiring a separate query with an extra SELECT.
| RaftPeople wrote:
| > _HAVING is different from WHERE because it filters
| after the aggregation, without requiring a separate query
| with an extra SELECT._
|
| Personally I rarely use HAVING and instead use WHERE with
| subqueries for the following reasons:
|
| 1-I don't like repeating/duplicating a bunch of complex
| calcs, easier to just do WHERE in outer query on result
|
| 2-I typically have outer queries anyway for multiple
| reasons: break logic into reasonable chunks for humans,
| also for join+performance reasons (to give the optimizer
| a better chance at not getting confused)
| andy800 wrote:
| I was not there at the original design decisions of the
| language, but I imagine it was there specifically to help
| the person writing/editing the query easily recognize and
| interpret filtering before or after an aggregation. The
| explicitness makes debugging a query much easier and
| ensures it fails earlier. I don't see much reason to stop
| distinguishing one use case from the other, I'm not sure
| how that helps anything.
| 0cf8612b2e1e wrote:
| I think this stems from the non-linear approach to
| reading a SQL statement. If it were top-to-bottom linear,
| like PRQL, then the distinction does not seem merited. It
| would then always be filtering from what you have
| collected up to this line.
| wvenable wrote:
| > 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.
| andy800 wrote:
| Perhaps, however then you eliminate the use of WHERE/HAVING
| sum(r.e) > 3, so in case you forgot what the alias s means,
| you have to figure that part out before proceeding. Maybe
| I'm just used to the existing style but as stated earlier,
| seems this is reducing explicitness which IMO tends to lead
| to more bugs.
| wvenable wrote:
| A lot of SQL engines don't support aliases in the HAVING
| clause and that can require duplication of potentially
| complex expressions which I find very bug-inducing.
| Removing duplication and using proper naming I think
| would be much better.
|
| I will already use subqueries to avoid issues with
| HAVING.
| magicalhippo wrote:
| > A lot of SQL engines don't support aliases in the
| HAVING clause
|
| We're moving from SQLAnywhere to MSSQL, and boy, we're
| adding 2-5 levels of subqueries to most non-trivial
| queries due to issues like that. Super annoying.
|
| I had one which went from 2 levels deep to 9... not
| pleasant. CTEs had some issues so couldn't use those
| either.
| wvenable wrote:
| I'm surprised you had issues with CTEs -- MS SQL has one
| of the better CTE implementations. But I could see how it
| might take more than just trivial transformations to make
| efficient use of them.
| yen223 wrote:
| 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.
| richbell wrote:
| 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.
| abelcha wrote:
| duckDB is what sql should be in 2024
|
| https://duckdbsnippets.com/
| jmull wrote:
| 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.
| tmoertel wrote:
| 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 wrote:
| > 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).
| tmoertel wrote:
| The post I was referring to said that this new pipe syntax
| was a big reordering compared to the vanilla syntax, which
| it is. But my point is that if you're going to understand
| the vanilla syntax, you already have to do this reordering
| in your head because the order in which the the vanilla
| syntax executes (inside out) is the order in which pipes
| syntax reads. So it's just easier all around to adopt the
| pipe syntax so that reading and execution are the same.
| mattashii wrote:
| > Vanilla SQL [...] QUALIFY is after analytic window
| functions
|
| Isn't that FILTER (WHERE), as in SELECT avg(...) FILTER
| (WHERE ...) FROM ...?
| aidos wrote:
| 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 wrote:
| > _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.
| Glyptodon wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| okay, now I can see why this so much reminds of CTE
| thiht wrote:
| 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
| akira2501 wrote:
| 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.
| yen223 wrote:
| WITH clauses are optional and appear before SELECT. No
| reason why the FROM clause couldn't behave the same
| akira2501 wrote:
| Isn't that strictly for CTEs? In which case, you are
| SELECTing from the CTE.
| gfody wrote:
| > what's it gonna autocomplete?
|
| otoh if you selected something the from clause and
| potentially some joins could autocomplete
| parpfish wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| 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
| sklivvz1971 wrote:
| We can call it "Linq2SQL" and what a disaster it was...
| iblaine wrote:
| Looking at this reminds me of Apache Pig. That's not a
| compliment.
| DaiPlusPlus wrote:
| 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 )
| 0cf8612b2e1e wrote:
| >... 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 wrote:
| Not to be outdone:
| https://thedailywtf.com/articles/what_is_truth_0x3f_
| orthoxerox wrote:
| "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.
| datadrivenangel wrote:
| Must have been super fast though
| DaiPlusPlus wrote:
| Not just fast, it's _web-scale_
|
| https://www.youtube.com/watch?v=b2F-DItXtZs
| bhawks wrote:
| 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 wrote:
| 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?
| akira2501 wrote:
| > 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 wrote:
| 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.
| ibash wrote:
| 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.
| tmoertel wrote:
| 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.
| geertj wrote:
| 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.
| magicalhippo wrote:
| > 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.
| tmoertel wrote:
| 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.
| solidsnack9000 wrote:
| 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 wrote:
| Can the first example not simply be done with a window function?
| 10000truths wrote:
| 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
| almostgotcaught wrote:
| > a directed graph of operations
|
| What syntax do you know that can represent a dag in text?
| RyanHamilton wrote:
| A = select * from tbla
|
| B = select * from tblb
|
| C = select * from A join B
| wtetzner wrote:
| I 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 wrote:
| Yes we already have that, it's called `async def`.
| cha42 wrote:
| Any syntax with a let operator to name stuff or a lambda
| abstraction.
| almostgotcaught wrote:
| That only gives you trees not DAGs - you can't do fan-in
| (there's no way to "share" let bound names).
| aranke wrote:
| Check out Substrait, it sounds like what you're describing.
| ram_rar wrote:
| 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.
| gfody wrote:
| 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 wrote:
| 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?
| abelcha wrote:
| well maybe people dont want to suffer for 20 years
| parpfish wrote:
| 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
| rawgabbit wrote:
| It looks great. When is Google going to create an IDE or
| extension that I can use?
| chuckadams wrote:
| I wonder if the pipe operator will land in SQL before we see it
| in JS.
| scop wrote:
| Reminds me of Elixir's Ecto library.
|
| https://hexdocs.pm/ecto/crud.html#pipe-based-queries
| freedomben wrote:
| That was my first thought too. I think working with ecto really
| primed me to like this syntax. I like it quite a bit
| sklivvz1971 wrote:
| 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.
| tmoertel wrote:
| > 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 wrote:
| 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.
| creatonez wrote:
| > 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 wrote:
| 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?
| sagarm wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| > 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 wrote:
| 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 wrote:
| 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.
| crazygringo wrote:
| 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.
| sagarm wrote:
| 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.
| wvenable wrote:
| > 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.
| crazygringo wrote:
| >> _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 wrote:
| ... so dplyr.
| nsonha wrote:
| What's wrong with single character pipe (|)? Not everyone uses
| ligatures even if they like that particular symbol. Just keep
| things simple please
| saltcured wrote:
| 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?
| thom wrote:
| 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 wrote:
| I've just update the EBNF for rarilroad diagram here
| https://github.com/google/zetasql/issues/134#issuecomment-14...
| with the full grammar.
| darksaints wrote:
| 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.
| chucke1992 wrote:
| Fix it by piping
___________________________________________________________________
(page generated 2024-08-24 23:00 UTC)