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

61–70 of 214 posts

Re: PRQL: Pipelined Relational Query Language

#61

Post by aidos »

slt2021 wrote: post #22
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 think main difference is how PRQL translates into query execution plan, with SQL you need to read entirety of query to get rough understanding of order of operations (especially if you join two-three tables and have some nested and lateral queries). with PRQL I see that the order will be explicit and set up by developer, so any footguns will be evident. things like predicate push down, optimization fence, variable…

Is that true? Doesn’t this just effectively translate into sql to be executed on the db as usual if you’re running on an rdbms?

Looking at the examples and the sql generated in the playground you’re just going to have the query planner doing its thing to decide how to execute eventually anyway.

Re: PRQL: Pipelined Relational Query Language

#62

Post by codethief »

getmeinrn wrote: post #45
codethief wrote: post #39

Earlier quoted context omitted.

I've silently been a big fan of this approach for years but hadn't heard about it anywhere (most likely because I'm not familiar with Pulumi). Could you provide a reference that elaborates on "Pulumi's pattern"?

This is the closest example I could find https://www.pulumi.com/docs/concepts/how-pulumi-works/ Pulumi serves as the strongest contender to Terraform when doing IaC (infrastructure as code). Terraform attempts to be a declarative markup language (HCL) but it has a lot of weird imperative quirks due to (understandably) trying to support common complex use cases. In the end they have a clunky custom language that tries…

Thanks so much!

> Pulumi only really cares that the programming language generates a declarative spec that can be used to compare with your infrastructure. It's the best of both worlds.

Fully agree! I've called this approach "imperatively generating declarations".

Things might get complicated, though, if you try to nest this approach. E.g. if you imperatively generate the structure of your deployment pipeline, which in turn, contains imperative code to generate your infrastructure spec. :) Or something like that[0].

But that's probably just the nature of the problem.

[0]: I can't come up with a fully realistic scenario right now because it's late. Maybe tomorrow!

Re: PRQL: Pipelined Relational Query Language

#63

Post by aidos »

mulmen wrote: post #60
aidos wrote: post #57

Earlier quoted context omitted.

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…

CTEs are basically temporary views and tables.

I’d argue that the extra tables with duplicate data aren’t a space / cpu trade-off as much as they are a data management trade-off. There are cases for duplicating data in the system but I’d leave that to indexes and generated columns unless you really need to materialise it in a different structure.

Re: PRQL: Pipelined Relational Query Language

#64

Post by slt2021 »

aidos wrote: post #61
slt2021 wrote: post #22

Earlier quoted context omitted.

I think main difference is how PRQL translates into query execution plan, with SQL you need to read entirety of query to get rough understanding of order of operations (especially if you join two-three tables and have some nested and lateral queries). with PRQL I see that the order will be explicit and set up by developer, so any footguns will be evident. things like predicate push down, optimization fence, variable…

Is that true? Doesn’t this just effectively translate into sql to be executed on the db as usual if you’re running on an rdbms? Looking at the examples and the sql generated in the playground you’re just going to have the query planner doing its thing to decide how to execute eventually anyway.

It transpiles into sql currently, but my understanding it is not long term vision.

Language arch allows in the future to replace sql for querying data

Re: PRQL: Pipelined Relational Query Language

#65

Post by aidos »

snthpy wrote: post #58
aidos wrote: post #49

Earlier quoted context omitted.

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…

Gotcha. The thing that’s not immediately clear from the syntax is which columns I’m getting out as a final result of my query. I guess the last select you ran + any derived ones since then?

Re: PRQL: Pipelined Relational Query Language

#66

Post by sdesol »

This is from the GitHub page

> PRQL is being actively developed by a growing community

You can see this at https://devboard.gitsense.com/PRQL which shows a good mix of new and not new contributors. Not sure why there was a spike in the last couple of days though. Maybe the maintainers that in this thread can comment on this.

Full Disclosure: This is my tool

Re: PRQL: Pipelined Relational Query Language

#67

Post by the_mitsuhiko »

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?

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)

Re: PRQL: Pipelined Relational Query Language

#68

Post by mulmen »

aidos wrote: post #63
mulmen wrote: post #60

Earlier quoted context omitted.

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…

CTEs are basically temporary views and tables. I’d argue that the extra tables with duplicate data aren’t a space / cpu trade-off as much as they are a data management trade-off. There are cases for duplicating data in the system but I’d leave that to indexes and generated columns unless you really need to materialise it in a different structure.

> CTEs are basically temporary views and tables.

Not in any system I have ever used. CTEs aren't materialized so they're not tables.

Tables (or materialized views if you prefer) are literally space/CPU tradeoffs. I have a data pipeline that combines website logs into a table before I join it with product data. If you tried to do both in one query then the DB falls over.

You seem to be coming at this from a OLTP perspective but in OLAP data "duplication" is the name of the game. But what naively looks like wasteful duplication is actually creating coherent analytical models from disparate data sources. This leads to much simpler analytical queries.

Re: PRQL: Pipelined Relational Query Language

#70

Post by aidos »

mulmen wrote: post #68
aidos wrote: post #63

Earlier quoted context omitted.

CTEs are basically temporary views and tables. I’d argue that the extra tables with duplicate data aren’t a space / cpu trade-off as much as they are a data management trade-off. There are cases for duplicating data in the system but I’d leave that to indexes and generated columns unless you really need to materialise it in a different structure.

> CTEs are basically temporary views and tables. Not in any system I have ever used. CTEs aren't materialized so they're not tables. Tables (or materialized views if you prefer) are literally space/CPU tradeoffs. I have a data pipeline that combines website logs into a table before I join it with product data. If you tried to do both in one query then the DB falls over. You seem to be coming at this from a OLTP persp…

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.

Post reply on HN