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

51–60 of 214 posts

Re: PRQL: Pipelined Relational Query Language

#51

Post by animal_spirits »

nonethewiser wrote: post #46

This is tangential but a new query language is inevitably based in the idea that SQL is deficient in some manner (hard, not ergonomic, whatever). More interestingly, it also implies that the countless alternatives aren't good enough either. Is there an existing query language that anyone will argue is better than SQL? I have limited exposure on this, but if SQL is really not that good then I'd expect there to be a be…

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.

Re: PRQL: Pipelined Relational Query Language

#52

Post by jdmichal »

t8sr wrote: post #14

If the main complaint people have about SQL is that you can't swap SELECT, FROM and WHERE, then that's pretty good for a language designed in the 70s. This, by contrast, looks like it has a bunch of random line noise for syntax. Why on earth should I like this: `join side:left p=positions (p.id==employees.employee_id)` better than this: `LEFT JOIN positions AS p ON p.id = employees.employee_id` ?

I've had two real gripes with SQL. The rest of it has been, as you said, pretty good.

Complaint 1: Not being able to use selected columns later in the same select.

    SELECT
        gnarly_calculation AS some_value,
        some_value * 2 AS some_value_doubled
Instead:

    SELECT
        subquery.*,
        some_value * 2 AS some_value_doubled
    FROM (
        gnarly_calculation AS some_value
    ) AS subquery
Complaint 2: Not being able to specify all columns except. This combines with the above, where I have to pull some intermediate calculations forward from a subquery, but I don't need them in the final output. So I have to then enumerate all the output columns that I actually want, instead of being able to say something like `* EXCEPT some_value`.

Re: PRQL: Pipelined Relational Query Language

#53

Post by zX41ZdbW »

We have recently merged PRQL support into ClickHouse: https://github.com/ClickHouse/ClickHouse/pull/50686

It's currently more like an experiment - I'm not sure if it will be usable or useful. There are some concerns about Rust, although minor: https://github.com/ClickHouse/ClickHouse/issues/52053#issuec...

Re: PRQL: Pipelined Relational Query Language

#54

Post by dlisboa »

t8sr wrote: post #16
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…

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?

Maybe you didn't see the more complex examples, but some of the SQL queries it abstracts are very unintuitive to write by hand. PRQL turns it into something more readable and closer to the expected mental model.

For people who can write error-free and complex portable SQL queries by heart this may not be really interesting. From experience, however, that is not a skill most developers have.

Re: PRQL: Pipelined Relational Query Language

#57

Post by aidos »

mulmen wrote: post #41
smartmic wrote: post #30

Earlier quoted context omitted.

Why not break these subqueries into their own common table expressions and prepend them to your join statements?

Or their own tables or views.

Adding tables doesn’t sound like a solution at all and views come with operational overheads that you need to be aware of.

CTEs should be the first thing you reach for when trying to clarify the intent of the way you’re chopping up your data.

Re: PRQL: Pipelined Relational Query Language

#58

Post by snthpy »

aidos wrote: post #49
snthpy wrote: post #27

Earlier quoted context omitted.

If you're happy with SQL then there isn't much point. For the folks building and supporting PRQL, SQL just has a few too many warts and the popularity of tools like Pandas, dplyr, Polars, LINQ, ... shows that for analytical work we often like to work with our data differently. Other frameworks and languages feel that we should throw out Relational Algebra as well but we feel that's like throwing the baby out with the…

For what it’s worth, it looks really readable to me. I have decades of sql experience at this point so consider myself pretty proficient but I can see the appeal of having a terser syntax for transformations. I especially like the “it just makes sql” approach. Stepping through the second example on that page I know how I could do the same in sql, and I also know that it would be harder for most people to follow. Ques…

I couldn't find that example right now but there is a similar line in the main example on the prql-lang.org homepage:

    aggregate {
        average total,
        ...
    }
I can't definitively say why it is there, other than perhaps just to show that you can specify aggregations without having to give them an alias. The column name won't be pretty but if you're just interactively trying something out and want to see the results then you probably won't care.

Does that help?

Re: PRQL: Pipelined Relational Query Language

#59

Post by 0cf8612b2e1e »

getmeinrn wrote: post #47
0cf8612b2e1e wrote: post #29

Earlier quoted context omitted.

PRQL seems the most realistic evolution out of SQL. Changing the programming paradigm will never convince the SQL true believers.

I don't really know what you're saying, can you say it another way? "Most realistic evolution"... why is that needed? If the problem is different database engines implementing the SQL spec differently, that's not something that can be papered over with another abstraction without a lot of wrinkles.

SQL does not need to be replaced, in the same way that COBOl never needed to be replaced. It can get the job done, but we have superior tools today. Better abstractions enable more productivity.

That there are N flavors of SQL is annoying, but there are foundational design choices in the language which we are stuck with today. PRQL is quite readable to those with SQL experience and feels like a plausible next language in the space without reinventing paradigms.

Re: PRQL: Pipelined Relational Query Language

#60

Post by mulmen »

aidos wrote: post #57
mulmen wrote: post #41

Earlier quoted context omitted.

Or their own tables or views.

Adding tables doesn’t sound like a solution at all and views come with operational overheads that you need to be aware of. CTEs should be the first thing you reach for when trying to clarify the intent of the way you’re chopping up your data.

Depends on the situation. Creating tables is a space/CPU tradeoff. Views can be managed the same way as the rest of your data pipeline.

CREATE TEMPORARY VIEW and CREATE TEMPORARY TABLE are both valid SQL.

> CTEs should be the first thing you reach for when trying to clarify the intent of the way you’re chopping up your data.

Sure but they aren't a silver bullet. Hence the "or" in my comment.

I use Redshift a lot and temporary tables are useful there for defining the sort and distribution keys when those are not favorable to the current query. Think joining several demographic tables into a single customer dimension, distributing it on the customer key then filtering the product_id distributed sales and re-distributing it on customer before joining. You can't do that with a CTE.

Views are handy when you have ugly CASE statements or other derived logic and need a convenience column that does something like string concatenation. I have a whole repository of them I deploy on top of the default system views for simplifying admin tasks.

If you are struggling to keep your SQL clean it's because you are doing too much at once. Take a step back and re-evaluate your data model. If your physical model is bad then no number of CTEs will save you.

Post reply on HN