PRQL – A proposal for a better SQL • Hacker News Board

Re: PRQL – A proposal for a better SQL

#211

Post by yen223 » Tue, Jan 25, 2022, 1:34 AM UTC

blintz wrote: post #109

This is a nice idea, especially given all the work people have done recently to make in-language querying nicer (Spark comes to mind). My only gripe is the 'auto-generated' column names for aggregates. This seems like a recipe for disaster - what if there is already (as there almost certainly will be) named "sum_gross_cost"? The behavior also just seems rather unexpected and implicit. My suggestion would be simple sy…

> My only gripe is the 'auto-generated' column names for aggregates

For what it's worth, a similar problem already exists with SQL. Something simple like

  select count(*) from my_table;

automatically aliases the column to `count`, even if `my_table` has a column called `count`.

In practice, I don't think this is a major problem.

Re: PRQL – A proposal for a better SQL

#212

Post by brigandish » Tue, Jan 25, 2022, 1:40 AM UTC

makeitdouble wrote: post #193

hn_throwaway_99 wrote: post #180

Earlier quoted context omitted.

> On the brief topic of form vs. flexibility. SQL is a thing that, when complex, is written by many people over the course of its lifetime - removing the ability to make bad decisions is better than enabling the ability to write simple things even simpler Hallelujah! But, to your footnote, this is a major reason why I despise ORMs. In my mind they make writing simple code slightly easier, but they make complicated SQ…

On ORMs, the best use I see of them is for “transparent” queries that you don’t define. Like fetching a record by id, or a single record and all of its related properties. Or a list of all the record in a table matching a simple filter. That’s 98% of what we do against the DB, and I’m all for having it basically invisible. Then let’s just bypass the ORM altogether the minute we think about joining or grouping things…

Isn’t it more important that the query you write with the ORM is readable than the underlying SQL it spits out? Using an ORM I can get reusable parts of a query, while writing complex joins, I’m not sure why skipping that part is good?

Re: PRQL – A proposal for a better SQL

#213

Post by sanderjd » Tue, Jan 25, 2022, 1:52 AM UTC

taeric wrote: post #176

sanderjd wrote: post #171

Earlier quoted context omitted.

Just throwing in another point of anecdata onto this pile: "Often I know what columns I want, but I'm not clear what table I need to get them from" does not make sense to me. I don't relate at all to their being a global namespace of columns, rather than a namespace of tables, each with its own columns specific to its context.

I challenge this. I accept that there are ambiguities, but I assert that you can go really fast by just telling someone to fetch a few columns by name. I further assert that if your database is filled with "Id" and "name" columns, instead of "department_name" and similar, you are probably as likely to mess up a join as any benefit you get from the name being short. (And really, what advantage is there in short names…

I think we have just done most of our data work in different environments.

When I'm trying to query stuff, the first question is "which service's database is that in?", so I can guess "user_service" (or whatever I think it is called), but I have no idea what they call anything in their schema, but now that the autocomplete system knows what table I'm interested in, it can help me figure that out.

Re: PRQL – A proposal for a better SQL

#214

Post by gerdesj » Tue, Jan 25, 2022, 2:12 AM UTC

I'm nowhere near an expert but an interested bystander. I find the SQL example easier to understand than the PRQL one.

... then I read the PRQL one again and again. It does read more like English and more like how people think.

PRQL is much more descriptive.

I quite like it.

Re: PRQL – A proposal for a better SQL

#215

Post by freedomben » Tue, Jan 25, 2022, 2:12 AM UTC

Interesting, this language reminds me a lot of Ecto[1] (the DSL "ORM" for Elixir).

It took a bit of getting used to but I'm coming to really enjoy Ecto and especially now that sqlite is fully supported, I'm finding myself using it in places I normally wouldn't have.

[1]: http://hexdocs.pm/ecto/Ecto.Query.html

Re: PRQL – A proposal for a better SQL

#216

Post by mschaef » Tue, Jan 25, 2022, 2:34 AM UTC

jnsie wrote: post #133

munk-a wrote: post #48

I like the flow direction compared to standard SQL. SQL is supposed to read like a sentence I suppose but I have many times looked at it and really wanted things to be in a more logical order. My main suggestion would be to be a bit less terse and introduce a bit more firm formatting. I'm not a huge fan of the term "split" and feel like jazzing that up to "split over" or even just reviving "group by" would improve re…

I like the flow direction specifically for intellisense/autocomplete. I'm sure it would be easier to provide hints when the table name is known immediately.

This is exactly why LINQ uses a similar ordering.

Re: PRQL – A proposal for a better SQL

#217

Post by kortex » Tue, Jan 25, 2022, 2:50 AM UTC

galkk wrote: post #88

I'm quite opposed to the idea "from should be first". I want to understand what exactly the query returns, not the implementation detail of the source of this data (that can later be changed). Literally first example from page - I have no idea what is being returned: from employees filter country = "USA" # Each line transforms the previous result. let gross_salary = salary + payroll_tax # This _adds_ a column / varia…

I think it's quite a common convention in engineering - not just software - that the input to a process "goes in the top and out the bottom". We humans read top->bottom (regardless of left/right/vertical, I don't know any languages that write bottom up). Conventional voltage in circuit diagrams usually flow top to bottom. Gravity loads in schematics flow top to bottom. Chemical pathways are usually written top to bottom. And of course functions take arguments up top and return at the bottom, maybe with some short circuits. I think the only counter example of note is distillation columns.

Where is the data coming from? Employees table. What's coming out? 20 rows of sum_gross_cost.

What could improve this is function signatures. It's kind of nice to have the whole abstraction up top...like an abstract.

Re: PRQL – A proposal for a better SQL

#218

Post by kortex » Tue, Jan 25, 2022, 2:57 AM UTC

Love it. This reads so much better in my mind than SQL. I love how easy it is to abstract with functions.

My one suggestion: use a distinct symbol for assignment and equality, either :=/==, =/==, or even :=/=. I kinda like the Go way of doing things, := assigns and initializes, == is equality (not sure you have a need for = re-assignment, but maybe). But I would definitely warn against = for context-dependent assignment or equality comparison.

You've got "let" so I guess that is kind of a syntactical difference; I guess the trippy part is seeing "=" do equality comparisons.

Re: PRQL – A proposal for a better SQL

#219

Post by hadley » Tue, Jan 25, 2022, 3:07 AM UTC

maximilianroos wrote: post #63

tomtheelder wrote: post #61

Earlier quoted context omitted.

Not the original commenter, but just using `by` makes total sense to me.

I've made this change [1]. Thank you! [1] https://github.com/max-sixty/prql/commit/dde7fcfc13daaadbdce...

FWIW the separate `group_by()` is one of my greatest design regrets with dplyr — I wish I had made `by` a parameter of `summarise()`, `mutate()`, `filter()` etc.

Re: PRQL – A proposal for a better SQL

#220

Post by hadley » Tue, Jan 25, 2022, 3:09 AM UTC

maximilianroos wrote: post #21

tfehring wrote: post #17

Very cool! A couple questions/suggestions off the top of my head: 1. Did you consider using a keyword like `let` for column declarations, e.g. `let gross_salary = salary + payroll_tax` instead of just `gross_salary = salary + payroll_tax`? It's nice to be able to scan for keywords along the left side of the window, even if it's a bit more verbose. 2. How does it handle the pattern where you create two moderately comp…

Thanks! > Did you consider using a keyword like `let` for column declarations Yeah, the current design for that is not nice. Good point re the keyword scanning. I actually listed `let` as an option in the notes section. Kusto uses `extend`; dplyr uses `mutate`; pandas uses `assign`. I opened an issue here: https://github.com/max-sixty/prql/issues/2

acquero uses derive (https://uwdata.github.io/arquero/api/verbs#derive) which I rather like (it's better than mutate, IMO)