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

PRQL: Pipelined Relational Query Language

github.com

Post reply on HN
Print view View original

181–190 of 214 posts

Re: PRQL: Pipelined Relational Query Language

#181

Post by globular-toast »

0cf8612b2e1e wrote: post #3

I really want this to take off and become a first party supported language for Postgres. Yes, yes, relational algebra is beautiful and all that jazz. SQL is a garbage, first pass design that should have been replaced decades ago. Even Codd has complaints about it. It is amazing what he invented, but we have learned a lot about PL design since then.

Same. I first learnt SQL 20 years ago as a teenager and even back then I remember thinking how odd and, quite literally, backwards the language was. Back then I probably thought I just didn't understand enough to see why it had to be so. Now I know there is no reason. SQL has become more like a natural language. There's no arguing against it, you have to speak it even if it doesn't make sense. But it's not a natural language and we can do better.

Re: PRQL: Pipelined Relational Query Language

#182

Post by euroderf »

fijiaarone wrote: post #81
animal_spirits wrote: post #51

Earlier quoted context omitted.

I counter the argument that just because it is still around it is the best solution. Many things stick around just because of inertia, for example, the QWERTY keyboard.

Big keyboard is keeping DVORAK down! In a fair world we’d all be typing NewQL faster with AI from our flying electric cars in a communist utopia.

The cross-silo synergy is strong in this one.

Re: PRQL: Pipelined Relational Query Language

#183

Post by housecarpenter »

the_mitsuhiko wrote: post #67
t8sr wrote: post #16

Earlier quoted context omitted.

I don't get that - to me the examples are much less readable than SQL and I don't understand why I should want to use this. Like, yes, you can reorder the query sections, which seems to be everyone's complaint about SQL, but then you also have multiple types of brackets, colons and other syntax for no reason, all while not really accomplishing anything SQL doesn't already do. What's the attraction?

I don't use PRQL but I absolutely get the appeal but specifically on the readability part, some things that are easy in PRQL are just awful in SQL. From the website for instance this is a nightmare to do in SQL: from employees group role (sort join_date take 1)

Isn't this a fairly simple way of doing this? That said, it is a bit non-obvious if you haven't seen it before.

    select earliest_joiner.* from employees as earliest
    left join employees as earlier on
      earlier.role = earliest.role
      and earlier.join_date

Re: PRQL: Pipelined Relational Query Language

#184

Post by aayjaychan »

pragmatic wrote: post #121
Pxtl wrote: post #102

Earlier quoted context omitted.

While there's some stuff in C#/LINQ/EF that's more verbose (left joins are often a nightmare) or not-supported, I'll always say that I prefer writing queries in EF than in SQL, at least when dealing with SQL features that are supported by EF (which is a lot of them, it's a very expressive dialect). But EF lets you start with FROM, lets you do whichever keywords you need in whichever order (instead of WHERE -> GROUP B…

The downside though is you have to grab the sql it’s generating somehow to try to optimize it, figure out what crazy cross apply madness it’s going or to figure out why it’s blowing sql servers recursion limit. I prefer to avoid linq syntax now. It’s a false economy.

I drew a different conclusion from similar experience. I avoid navigation properties and other advanced mapping features, so an entity maps flatly to one table.

The LINQ queries will be more verbose as you'll need to write the join and group clauses explicitly, but I find it much easier to predict the performance of queries since the generated SQL will look almost exactly the same as the LINQ syntax. It's also less likely to accidentally pull in half the database with `.Include()` this way.

Re: PRQL: Pipelined Relational Query Language

#185

Post by aidos »

mulmen wrote: post #73
aidos wrote: post #70

Earlier quoted context omitted.

I agree with your further example cases (they were added after my comment). I was thinking more of the original post where someone was complaining about readability when you have joins and subqueries - which is the usecase for CTEs.

Yeah sorry I edit a lot. I'll bump up my delay. Tables, views, and CTEs are all tools that can be used to make SQL more readable. They are all valid alternatives to subqueries in JOINs which is the only thing I would say you should "never" do. I tend to use all of them. I create tables with an optimized (typically star) schema for my purposes. My date dimensions are almost always views on top of a list of dates. I al…

> CTEs aren't materialized so they're not tables.

You might well know this, but one thing thats worth pointing out is that in Postgres you _can_ materialise the CTEs (though it's more to trick the planner when your use case is more about making sure everything to exploded and indexed in advance).

    with x as materialized (select ..) select ...

Re: PRQL: Pipelined Relational Query Language

#186

Post by RGamma »

snthpy wrote: post #84
RGamma wrote: post #56

Sweet, it looks like the language M should have been (or should become)! Granted, M would massively benefit from better tooling too.

Ah, nice to see a mention of M. I don't see it mentioned a lot on HN and get the feeling most folks here probably don't even really know about it because it's so embedded in PowerQuery and PowerBI. It's actually not a bad language, it's just quite verbose in terms of the keywords. I actually draw on my experience with M a lot in terms of the input that I give to PRQL because for data munging M is quite a good languag…

Yeah, it's a neat tool generally speaking.

What holds it back most of all I feel is the prison that is the Power Query editor (I have a sizable list of small and large papercuts in mind).

Now that PBI allows experimental export to pbip, change may be on the horizon. Hoping for more fine-grained tools, a better profiler and a better editor.

Will look at PRQL when I find the time. The planned column lineage feature and type checking e.g. seem like a godsend. Good work!

Re: PRQL: Pipelined Relational Query Language

#187

Post by imtringued »

wodenokoto wrote: post #165
mikea1 wrote: post #162

Earlier quoted context omitted.

> the pipelined nature of PRQL really maps much better to how people should think about queries I disagree. Database engines take SQL and transform it into an execution plan that takes into consideration database metadata (size, storage, index analytics, etc.). Queries should be thought of with a _set based_ instead of _procedural_ approach to maximize the benefits of this abstraction - diving into the implementation…

I disagree. I find it extremely hard to reason about large queries as set transformations, whereas it is much easier to break it down to "first this, then that". And this is long before I've even started writing my first line of SQL. So let me write it procedurally and have the optimization engine fix it for me, just like how it fixes my SQL. Even SQL queries are often better understood procedurally. Take this one [1…

Unpopular opinion.

The uncorrelated example should have been rewritten with a CTE and should have been aliased as 'article_max_price' as if it was a computed property and where price = amp.price

Re: PRQL: Pipelined Relational Query Language

#189

Post by imtringued »

andrewl-hn wrote: post #86
OJFord wrote: post #74

Earlier quoted context omitted.

To me it seems quite nice, but really just trivially different from SQL - like if Ruby was 'friendlier syntax that transpiles to Python', meh? You'd use whichever you happened to learn first and not bother with the other. (That's often true even though it's more than that of course.) The examples arbitrarily make SQL look more verbose: SELECT id, first_name, age FROM employees ORDER BY age LIMIT 10 Yes! Of course I'd…

I use CTEs, window functions, and groupings all the time when I write reporting queries. These things tend to be much more verbose in raw SQL, and ORMs / Query Builders either do not support some of these features at all or do very poor job (like, force me to put raw SQL substrings in my code), or force to write DSLs that are even more verbose than raw SQL. Look at corresponding PRQL samples, and you may see an appea…

I have some ideas how to implement a high performance ORM (yes that sounds strangey doesn't it?) that makes extensive use of CTEs, window functions and temporary tables. Writing reports in that ORM would be downright trivial.

From my perspective the JPA speciation is downright harmful. There can never be a good JPA implementation. I don't understand how there are no Java ORMs that actually learned anything from the mess that Hibernate etc are.

Take a look at how nasty even some of the most trivial concepts like calculated properties.are: https://blog.eyallupu.com/2009/07/hibernate-derived-properti...

In my hypothetical ORM design, calculated properties would be easy and fast by default.

Also, the CORBA style lazy loading based on proxies is ridiculous. As if people truly wanted a landmine like that to blow up on them.

Re: PRQL: Pipelined Relational Query Language

#190

Post by krick »

OJFord wrote: post #74
andrewl-hn wrote: post #6

For me the examples on the website https://prql-lang.org/ are the biggest selling point for PRQL, in particular the SQL it generates. It looks clean, straightforward, something I would've written myself. In general, I like this slightly more careful take on modern database development. 10-15 years people would start a brand new database like Mongo, or Riak, or Influx, or whatever, and would try to convince applicatio…

To me it seems quite nice, but really just trivially different from SQL - like if Ruby was 'friendlier syntax that transpiles to Python', meh? You'd use whichever you happened to learn first and not bother with the other. (That's often true even though it's more than that of course.) The examples arbitrarily make SQL look more verbose: SELECT id, first_name, age FROM employees ORDER BY age LIMIT 10 Yes! Of course I'd…

"Order by" is a much better name, by the way. I'm permanently slightly annoyed by the fact that in programming it's a custom to call ordering "sorting" for some unimaginable reason.
Post reply on HN