PRQL: Pipelined Relational Query Language • Hacker News Board

Re: PRQL: Pipelined Relational Query Language

#171

Post by meitham » Wed, Jul 26, 2023, 5:32 AM UTC

viraptor wrote: post #167

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…

It's not the tiny changes in the syntax/order that matter here. It's that if you want to limit the columns or do something else with them afterwards, in PRQL you append that text to the end. In SQL you'd have to wrap it around your original query, it inject joins in the middle. It doesn't read well anymore, because "how" you're doing things eclipses "what" you're doing. For a simple select, this just doesn't matter.…

In python I use sqlalchemy core to build the SQL for me, but I manage the transactions myself. Sqlalchemy query builder offers a similar role to what you described, as in I can add another select or where field after I have already added a sort criteria. It’s clever enough to order these correctly based on the SQL dialect.

Re: PRQL: Pipelined Relational Query Language

#172

Post by jiggawatts » Wed, Jul 26, 2023, 5:44 AM UTC

There are about 17 of these "SQL but better if we start with the FROM statement at the top" languages.

The language-integrated query (Linq) feature of .NET works the same away:

    var studentsGroupByStandard = from s in studentList
                                  group s by s.StandardID into sg
                                  orderby sg.Key 
                                  select new { sg.Key, sg };

Another example is Microsoft's Kusto Query Language, which they use in Azure Log Analytics:

    StormEvents
    | where State == 'TEXAS' and EventType == 'Flood'
    | top 5 by DamageProperty desc
    | extend Duration = EndTime - StartTime

Re: PRQL: Pipelined Relational Query Language

#173

Post by spullara » Wed, Jul 26, 2023, 6:06 AM UTC

mulmen wrote: post #161

spullara wrote: post #154

Earlier quoted context omitted.

If you have auto-complete it can see what table you are using and complete the column names. Starting with SELECT it could be any column in the database.

Huh. I have been doing this for like 15 years and never have a problem. Text editors like Sublime Text suggest names based on the content of the file. SQL Developer, SQLWorkbench, and DataGrip all seem to handle it just fine.

It will suggest names that aren't in the table you are going to query unless it is psychic.

Re: PRQL: Pipelined Relational Query Language

#174

Post by AtNightWeCode » Wed, Jul 26, 2023, 6:43 AM UTC

jdmichal wrote: post #52

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 .…

Don't use *. It is a common source for all kinds of problems. Many query langs does not support it at all for that reason.

Re: PRQL: Pipelined Relational Query Language

#175

Post by xchkr1337 » Wed, Jul 26, 2023, 7:01 AM UTC

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` ?

The problem with SQL is the same as the problem with C-style variable declarations. It sounds slightly better than the alternatives when you say the code out loud, but in reality it causes problems with readability and parsing/processing the code

Re: PRQL: Pipelined Relational Query Language

#176

Post by jatins » Wed, Jul 26, 2023, 7:15 AM UTC

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…

Even though a small example doesn't highlight the advantages well, I still prefer the PRQL syntax in this because that reads like map/filter code I'd write in some programming language.

So the mental model seems easier to me

Re: PRQL: Pipelined Relational Query Language

#177

Post by hn_throwaway_99 » Wed, Jul 26, 2023, 7:17 AM UTC

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…

Completely agree, thanks for putting it better than I could have, with an excellent example. Correlated subqueries like the example you give, or similarly lateral joins in postgres, fundamentally are treated like for loops by DB engines anyway.

Semi-related, but the example you give is also why I love Postgres' "DISTINCT ON" functionality (I don't know if other DBs have something similar) - it makes it so much easier to reason about these "give me the 'first' one from each group" type queries without having to resort to correlated subqueries.

Re: PRQL: Pipelined Relational Query Language

#178

Post by inopinatus » Wed, Jul 26, 2023, 7:25 AM UTC

jiggawatts wrote: post #172

There are about 17 of these "SQL but better if we start with the FROM statement at the top" languages. The language-integrated query (Linq) feature of .NET works the same away: var studentsGroupByStandard = from s in studentList group s by s.StandardID into sg orderby sg.Key select new { sg.Key, sg }; Another example is Microsoft's Kusto Query Language, which they use in Azure Log Analytics: StormEvents | where State…

So did QUEL, as implemented in Ingres circa 1976. Perhaps this is what happens when you go back to basics and take a good long look at Codd's relational calculus (or, equivalently, relational algebra), to which SQL bears only a passing resemblance.

Re: PRQL: Pipelined Relational Query Language

#179

Post by euroderf » Wed, Jul 26, 2023, 9:32 AM UTC

akdor1154 wrote: post #144

slotrans wrote: post #128

Earlier quoted context omitted.

death before trailing commas

Well if it was after they wouldn't be trailing would they?

for the win

Re: PRQL: Pipelined Relational Query Language

#180

Post by glogla » Wed, Jul 26, 2023, 9:40 AM UTC

zX41ZdbW wrote: post #116

snthpy wrote: post #106

Earlier quoted context omitted.

That's awesome! ClickHouse is a great system by all accounts and I've been meaning to try out ClickHouse Local. I'm more familiar with DuckDB and they've also been doing some great innovation on the SQL front. I don't know offhand if they can also do the forward referencing thing but they allow putting the FROM first and having GROUP BY ALL etc.. It's great to see all this innovation happening in the SQL and Query La…

It is strange to hear about innovation in DuckDB - I see that they are gradually re-implementing the stuff already existing in ClickHouse. Sometimes they do a better job at promoting it.

They also do bunch of stuff that ClickHouse can't do, like correlated subqueries [1] or recursive CTEs.

I kinda wish for a database with ClickHouse storage and DuckDB optimizer. At least my experience with ClickHouse is that MergeTree is incredibly good at what it does, but the optimizer hurts.

[1]: https://duckdb.org/2023/05/26/correlated-subqueries-in-sql.h...