Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Google's new pipe syntax in SQL

simonwillison.net

Post reply on HN
Print view View original

161–170 of 192 posts

Re: Google's new pipe syntax in SQL

#161

Post by fader »

BeefWellington wrote: post #154
mixedCase wrote: post #76

Earlier quoted context omitted.

https://prql-lang.org/ has a bunch of good examples on its home page. If you engage the syntax with your System 2 thinking (prefrontal cortex, slow, the part of thinking we're naturally lazy to engage) rather than System 1 (automated, instinctual, optimized brain path to things we're used to) you'll most likely find that it is simpler, makes more logical sense so that you're filtering down things naturally like a sie…

Edit: In my pre-coffee rush this morning I completely missed the grouping by role (which is not that much harder FWIW). This unfortunately invalidates my entire post as it was posted and I don't want to spread misinfo.

I don't think your alternatives actually solve the same problem. Your alternatives would give you the single most recently joined employee. The actual problem being solved is to find the most recently joined employee in each role.

You'd need to do some grouping in there to be able to get one employee per role instead of a single employee out of the whole data set.

Re: Google's new pipe syntax in SQL

#162

Post by BeefWellington »

fader wrote: post #161
BeefWellington wrote: post #154

Earlier quoted context omitted.

Edit: In my pre-coffee rush this morning I completely missed the grouping by role (which is not that much harder FWIW). This unfortunately invalidates my entire post as it was posted and I don't want to spread misinfo.

I don't think your alternatives actually solve the same problem. Your alternatives would give you the single most recently joined employee. The actual problem being solved is to find the most recently joined employee in each role. You'd need to do some grouping in there to be able to get one employee per role instead of a single employee out of the whole data set.

Yeah you're correct, I caught that and edited my reply right as you responded.

Time willing I will provide an updated reply with fixed SQL.

Re: Google's new pipe syntax in SQL

#163

Post by theodpHN »

ahmed_ds wrote: post #132

This is why I like tools like datastation and hex.tech. You write the initial query using SQL than process the results as a dataframe using Python/pandas. Surely, mixing Pandas and SQL like that is not good for data pipelines but for exploration and analytics, I have found this approach to be enjoyable.

Yes, it's very convenient to be able to use SQL with your massively parallel commercial database (Oracle, Snowflake, etc.) and then again with the results sets (Pandas, etc.). Interestingly, it's a concept that was implemented 35 years ago in SAS (link below) but is just now gaining traction in today's "modern" software (e.g., via DuckDB).

USING THE NEW SQL PROCEDURE IN SAS PROGRAMS (1989) https://support.sas.com/resources/papers/proceedings-archive... The Sql procedure uses SQL to create, modify, and retrieve data from SAS data sets and views derived from those data sets. You can also use the SOL procedure to join data sets and views with those from other database management systems through the SAS/ACCESS software interfaces.

Re: Google's new pipe syntax in SQL

#165

Post by WorldMaker »

mjevans wrote: post #48
aragonite wrote: post #47

> This remains a long-standing pet peeve of mine. PDFs like this are horrible to read on mobile phones, hard to copy-and-paste from ... I've never understood why copying text from digitally native PDFs (created directly from digital source files, rather than by OCR-ing scanned images) is so often such a poor experience. Even PDFs produced from LaTex often contain undesirable ligatures in the copied text like fi and fl.…

ligatures like fi fl ffi ffl etc are for changes in fonts specific to rendering correctly on a screen or printer. It's intended to be a _rendered_ format, rather than a parse-able format. Well formatted epub and HTML generally are usually intended to update to end user needs and better fit available layout space.

Though it's also a stuck legacy throwback. Modern advice would be to not send ligatures directly to the renderer and instead let the renderer poll OpenType features (and Unicode/ICU algorithms) to build them itself. PDF's baking of some ligatures in its files seems something of a backwards compatibility legacy mistake to still support ancient "dumb" PostScript fonts and pre-Unicode font encodings (or least pre-Unicode Normalization Forms). It's also a bit of the fact that PDF has always been confused about if it is the final renderer in a stack or not.

Re: Google's new pipe syntax in SQL

#166

Post by WorldMaker »

BeefWellington wrote: post #69

Every time this FROM-first syntax style crops up it's always the most basic simple query (one table, no projections / subselects / consideration to SP/Views). Just for once I want to see complete examples of the syntax on an actual advanced query of any kind right away. Sure, toss out one simple case, but then show me how it looks when I have to join 4-5 reference tables to a fact table and then filter based on those…

> Once you do that, it becomes clear why SELECT first won out originally: legibility and troubleshooting.

Select first was as much an accident of "it sounded better as an English sentence" to the early SQL designers. Plus also they were working with early era parsers with very limited look ahead and putting the primary "verb" up front was important at the time.

But English is very flexible, especially in "command syntax" and From first is surprisingly common: "From the middle cupboard, grab a plate". SQL trying to sound like English here only shows how inflexible it still is in comparison to actual English.

I've been using C#'s LINQ since it was added to the language in 2007 and the from/where/join/group by/select order feels great, is very legible especially because it gives you great autocomplete support, and troubleshooting is easier than people think.

Re: Google's new pipe syntax in SQL

#167

Post by WorldMaker »

neonsunset wrote: post #50
yarg wrote: post #12

This reminds me .NET's short lived Linq to SQL; There was a talk at the time, but I can't find the video: http://jaoo.dk/aarhus2007/presentation/Using+LINQ+to+SQL+to+... . Basically, it was a way to cleanly plug SQL queries into C# code. It used this sort of ordering (where the constraints come after the thing being constrained); it needed to do so for IntelliSense to work.

There is https://github.com/linq2db/linq2db which is LINQ to SQL reincarnated. Of course there's EF Core too.

And NHibernate.Linq and Dapper.Extensions.Linq… Most ORMs in the ecosystem have at least one Linq support library, even if just a third-party extension.

Also, there are fun things that support Linq syntax for non-ORM uses, too, such as System.Reactive.Linq and LanguageExt: https://github.com/louthy/language-ext/wiki/How-to-deal-with...

Re: Google's new pipe syntax in SQL

#168

Post by WorldMaker »

beart wrote: post #33
slaymaker1907 wrote: post #10

I actually work on SQL Server, but I also write a lot of KQL queries which also work this way and I totally agree that the sequential pipe stuff is easier to write. I haven't read through the whole paper, but one aspect that I really like is that I think it's easier to guide the query optimization in this sequential style.

Is there any internal inertia for such changes to SQL server?

Given how Entity Framework is quite ubiquitous as "the ORM of choice" for SQL Server and its usage of C# Linq, there's certainly external momentum, whether or not SQL Server devs themselves are paying attention to how the majority of their users are writing queries today.

Re: Google's new pipe syntax in SQL

#169

Post by WorldMaker »

isoprophlex wrote: post #97

I love the idea but something in my brain starts to itch when I see that pipe operator |> What IS that thing? A unix pipe that got confused with a redirect? A weird smiley of a bird wearing sunglasses? It'll take some getting used to, for me...

It's like other "arrow" digraphs in common programming languages today, such as =>. You can picture it as a triangle pointing to the right.

Many Programming Ligature fonts even often draw it that way. For instance it is shown under F# in the Fira Code README: https://github.com/tonsky/FiraCode

Re: Google's new pipe syntax in SQL

#170

Post by kccqzy »

spatulon wrote: post #134
crazygringo wrote: post #56

Earlier quoted context omitted.

> Is it due to something inherent in PDF technology? Exactly. PDF doesn't have instructions to say "render this paragraph of text in this box", it has instructions to say "render each of these glyphs at each of these x,y coordinates". It was never designed to have text extracted from it. So trying to turn it back into text involves a lot of heuristics and guesswork, like where enough separation between characters sho…

I've never looked into the PDF format, but, does it not allow for annotations that say, "the glyphs in the rectangle ((x0, y0), (x1, y1)) represent the text 'foobar'")? That's been my mental model for how they are text-searchable.

They do but such annotations are optional.
Post reply on HN