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

Show HN: PRQL 0.2 – a better SQL

github.com

Post reply on HN
Print view View original

61–70 of 166 posts

Re: Show HN: PRQL 0.2 – a better SQL

#61

Post by jhgb »

coremoff wrote: post #33
aerzen wrote: post #14

Earlier quoted context omitted.

As said, currently PRQL transpiles to SQL, so all expressions in PRQL are can be expressed in SQL. But not all SQL expression can be translated back into PRQL - some intentionally and some are just not yet implemented (UNION - i.e. vertical concat). But we also have plans for doing things that some SQL databases may not support, such as pivot (rows to columns).

hopefully you'll forgive my pedantry - "union all" is vertical concat - "union" without the "all" gives you the distinct list

One of the reasons why SQL is crap: there should be no distinction between the two in relational algebra. A set of {A, B, C, B, C} is the same as {A, B, C}.

Re: Show HN: PRQL 0.2 – a better SQL

#62

Post by igorkraw »

maximilianroos wrote: post #41
jitl wrote: post #37

I'm surprised that none of the examples on Github or the website deals with join. I eventually found some in the "book" here: https://prql-lang.org/book/transforms/join.html from employees join side:left positions [id==employee_id] turns into SELECT employees.*, positions.* FROM employees LEFT JOIN positions ON id = employee_id I would love to see joins worked into the main learning examples. Without join, the exampl…

Great point, we'll add that. I don't think we do joins that much better than SQL does. We're thinking whether there's potential there, maybe through understanding foreign keys — but we're being conservative about introducing change without value.

I looked at the book after this and have to say, I'd heavily recommend spending the next dew months just improving joins (and complex joins especially). Like GP says, relational modelling is the interesting bit about SQL and I don't feel exaggerative in saying the only reason I use SQL are joins, and so the only reason I'd introduce the complexity of your project into my stack would be if it makes handling joins, views and other aspects of relational modeling and slicing nicer - one example could be many to many relationships, or the gradient between graph/document based and normalised table based modeling

Re: Show HN: PRQL 0.2 – a better SQL

#63

Post by digisign »

Thanks, I've frequently wanted a query language that was designed after the 70s. The ideas are sound, but a modernized syntax with variables to reuse subqueries would be lovely. This looks like it.

I noticed one issue though... please don't copy the prefix of f-strings! That only exists because Python boxed itself in and it was literally the only ascii syntax left that could be used for string interpolation. It's mildly ugly but the best that could be done given those requirements. Not so here.

The way shells do it with single quotes producing literal strings and double quotes available for interpolation has not been topped imho. Triple quotes are a nice extension as well, not sure if that made it in.

Re: Show HN: PRQL 0.2 – a better SQL

#64

Post by snthpy »

hui-zheng wrote: post #52
ithrow wrote: post #28

Earlier quoted context omitted.

To avoid working with SQL strings.

SQL/jinja like dbt could also avoid working with SQL strings. what would be the better advantage?

There is already an integration for dbt: https://github.com/prql/dbt-prql

For example

    {% prql %}
    from source = {{ source('salesforce', 'in_process') }}
    derive expected_sales = probability * value
    join {{ ref('team', 'team_sales') }} [name]
    group name (
      aggregate (sum expected_sales)
    )
    {% endprql %}
would appear to dbt as

    SELECT
      name,
      SUM(source.probability * source.value) AS expected_sales
    FROM
      {{ source('salesforce', 'in_process') }} AS source
      JOIN {{ ref('team', 'team_sales') }} USING(name)
    GROUP BY
      name
dbt is definitely a use case we are very aware of and I am personally very keen on (since I use that in my $dayjob). With some of the ideas in https://github.com/prql/prql/issues/381 , I think PRQL could really shine in this area!

With your contribution we can get there faster!

Re: Show HN: PRQL 0.2 – a better SQL

#65

Post by srcreigh »

hardwaregeek wrote: post #45

I've thought about building a better query language too. I'd love the ability to model sum types in databases, something like: enum SchoolType { College { degrees: Vec }, HighSchool } It's such a common pattern and yet it's so annoying to model in a normal relational database. I wouldn't be surprised if the rise of NoSQL is tied to the inability of relational databases to model basic patterns like this. Part of me ha…

You'd have 1 table per sum type which requires extra data. Then polymorphic foreign key (aka a pair of fields school_type, school_id).

(No foreign key constraints, but those are falling out of use in some cases due to inability to online migrate mysql schemas anyways.)

Re: Show HN: PRQL 0.2 – a better SQL

#66

Post by notimportant0 »

I mostly work using T-SQL but I like PRQL.

Are you able to add in the examples the following:

    1) Use of delimiters for names that include space, etc. I don't know if PRQL uses double quote or square brackets. 
    2) Use of two/three/four-naming convention to refer to servers, databases, tables and columns.

Re: Show HN: PRQL 0.2 – a better SQL

#68

Post by snthpy »

hui-zheng wrote: post #51
snthpy wrote: post #46

Earlier quoted context omitted.

That's a really good question! (and one we should probably answer explicitly in the [FAQ]( https://prql-lang.org/faq/ ) rather than just implicitly) The README states that "PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement. Like SQL, it's readable, explicit and declarative. Unlike SQL, it forms a logical pipeline of transformations, and supports abstractions such as varia…

One benefit of SQL is that the Database Engine will do the hard work of optimizing the query plan. Do you think the SQL complied by PRQL could be as effective and optimized by database engine as the direct-written SQL?

As you said, let the Database Engine do the hard work of optimizing the query plan for you.

I currently have no reason to believe that the PRQL generated SQL would be any worse than hand written SQL. That said, I don't think we've currently looked at any ways of passing hints to the query planner. We're always open to suggestions!

In the worst case, you have full access to the generated SQL, and for absolutely crucial queries you can hand modify that SQL. At least PRQL might have saved you the trouble of writing a cumbersome window function or something like that (see for example the example of picking the top row by some GROUP BY expression).

Re: Show HN: PRQL 0.2 – a better SQL

#69

Post by Cilvic »

snthpy wrote: post #46
qolop wrote: post #25

Why should I use this instead of SQL?

That's a really good question! (and one we should probably answer explicitly in the [FAQ]( https://prql-lang.org/faq/ ) rather than just implicitly) The README states that "PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement. Like SQL, it's readable, explicit and declarative. Unlike SQL, it forms a logical pipeline of transformations, and supports abstractions such as varia…

This reminds me of KUSTO I'm not sure how it compares to SQL in general. But it was really fun to work with for querying Azure application insigts

Re: Show HN: PRQL 0.2 – a better SQL

#70

Post by CuriousSkeptic »

paol wrote: post #15

Here's one suggestion: SQL tediously requires specifying the equality condition on joins, when 90% of the time you just want to join on the fk defined between the tables. from a join b should implicitly join on the FK if no condition is given. It would require knowledge of the schema. I don't know if this is possible in PRQL, or if the transpilation to SQL has to be stateless.

SQL has that actually

select * from a natural join b

(not based on fk constraints though, it will join on all attributes with the same name in the relations)

Post reply on HN