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

PRQL – A proposal for a better SQL

github.com

Post reply on HN
Print view View original

81–90 of 302 posts

Re: PRQL – A proposal for a better SQL

#81

Post by mst »

mindcrime wrote: post #56
gibsonf1 wrote: post #18

SPARQL. Representing human information in relational tables goes against how people actually think and use information. We humans think in tremendous numbers of nested hierarchies, and recursive hierarchy traversal is a nightmare in relational databases. A graph is the structure for data that works best, is most efficient, and actually reflects how things are connected in our brains.

I'm a big fan of SPARQL, but the one thing that would concern me about trying to use it outside of the SemWeb context is simply that it assumes data is stored in triples. Legacy databases by and large are not, so you need an adapter to bridge the representations. And while I know some exist, I haven't really used them and am not sure about the performance impact.

You can get quite far mapping the triple concept to (PK, column, value) or (PK, FK, related-row) and transpiling from there.

(I played around with this some years back, not to the point where anything came out of it worthy of publishing, but enough to be pleasantly surprised how far 'quite far' turned out to be in practice)

Re: PRQL – A proposal for a better SQL

#82

Post by maximilianroos »

ridaj wrote: post #77

Very cool Design goal question - is the goal to have a language that blindly compiles to SQL, or will compilation require data schema knowledge? Suggestion: where possible stick to the well established SQL keywords (prefer "group by" to "aggregate by")

Initially the transpiling would be context-free of the schema.

It would really nice to have context when _writing_ the code, so we could do things like autocomplete (IIUC this is what Malloy does already).

Are there features you can think of that would be helpful if we had the schema context during transpiling?

Re: PRQL – A proposal for a better SQL

#83

Post by edgyquant »

jacktheturtle wrote: post #23

Do people still write SQL?

Of course. Large companies like Pepsi have teams of analysts that only write SQL. I applied for a programming job there a long time ago and didn’t follow up when they explained in the interview that’s the only language they used.

Re: PRQL – A proposal for a better SQL

#84

Post by maximilianroos »

rchrch wrote: post #36

Awesome! Would love to see an implementation. I worked on something similar over the Summer. It’s just relational algebra with pipes for composition. If you are interested, we could get an antlr grammar going and plug it into this basic execution engine to get a feel for the language. - https://github.com/RCHowell/Sift - https://github.com/RCHowell/Sift/blob/main/src/main/kotlin/c...

Yes this looks really cool, and similar! Feel free to hit me up on Twitter https://twitter.com/max_sixty

Re: PRQL – A proposal for a better SQL

#85

Post by nurettin »

To be fair, lateral joins (cross/outer apply in mssql) can help with name aliasing and table functions give sql some reusability. I think the main pain points for sql are pivots and window functions.

A lot of the time you just want to transpose your result, but you have to choose an aggregate and handle null cases to force pivot to work the way you want it.

And a lot of the time you want to aggregate a window but keep the ids of the row so you avoid the having keyword altogether and go for row_number and dense_rank to get your aggregate results.

If I were to write a query language, I would discard group by and having and make it easier to apply transpose and window functions.

Re: PRQL – A proposal for a better SQL

#86

Post by ako »

magicalhippo wrote: post #62

Now this is actually nice, unlike the other suggestion posted today[1]. Maybe I'm just too used to non-standard extensions of our database but the SQL example could, at least for our db, be rewritten as SELECT TOP 20 title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_salary) AS sum_gross_salary, AVG(gross_cost) AS average_gross_cost, SUM(gros…

With a CTE it would read a bit more like prql:

  with usa_employees as (
    SELECT
            title,
            country,
            salary,
            (salary + payroll_tax)                   AS gross_salary,
            (salary + payroll_tax + healthcare_cost) AS gross_cost
    FROM  employees
    WHERE country = 'USA'
    AND   (salary + payroll_tax + healthcare_cost) > 0
  )
  select  title,
        country,
        AVG(salary)         AS average_salary,
        SUM(salary)         AS sum_salary,
        AVG(gross_salary)   AS average_gross_salary,
        SUM(gross_salary)   AS sum_gross_salary,
        AVG(gross_cost)     AS average_gross_cost,
        SUM(gross_cost)     AS sum_gross_cost,
        COUNT(*) as emp_count
  from      usa_employees
  group by  title, country
  having    count(*) > 200
  order by  sum_gross_cost
  limit 3
Readability is pretty similar to prql. It would really help in SQL if you could refer to column aliases so you don't have to repeat the expression.

Re: PRQL – A proposal for a better SQL

#88

Post by galkk »

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 / variable.
     let gross_cost   = gross_salary + benefits_cost  # Variables can use other variables.
     filter gross_cost > 0
     aggregate by:[title, country] [                  # `by` are the columns to group by.
          average salary,                              # These are the calcs to run on the groups.
          sum     salary,
          average gross_salary,
          sum     gross_salary,
          average gross_cost,
          sum     gross_cost,
          count,
 ]
     sort sum_gross_cost                              # Uses the auto-generated column name.
     filter count > 200
     take 20
of course, similar things are happening to SQL too, with CTEs becoming more widespread and "real" list of the columns hidden somewhere inside, but it's still parseable

Re: PRQL – A proposal for a better SQL

#89

Post by cies »

I love quality language proposals like this. I'm not so much in data processing/bigdata, but have had to interact with SQL a lot.

This syntax is lovely! It's more intuitively readable (and SQL is not that bad in that regard).

My feedback:

1. Lower case, underscored everything makes the terms a bit hard to differentiate. Maybe set some classes of symbols in CamelCase, or add a !@#$%& prefixes to them to make it more readable.

2. I dont like to use another language (SQL or PRSQL for that matter) to db interaction, I like to write the queries in the language that I'm using to develop in. There are ORMs in this design space, but I'm a little fed up with them. In Java there's jOOQ. Other less-OO-more-functional ORMs exist in Rust and Haskell land. These often have a code generation step, a library is generated that guarantees some type safety for a give schema version. Some are more SQL-like, some provide a different API. PRQL is much more diverted from SQL than these, and for good reasons. Maybe several languages could easily have libs like this building on top of JPQL?

3. You solution is a bit like GraphQL in some regards; where there is a tool needed to convert the query to SQL. Tools like this exist, like Hasura and the likes. Hasura does a lot more. To me GraphQL has the huge advantage of serving a schema so that clients can be generated. I can interact with GraphQL in a type safe fashion from by generating a client in, say, Elm. The generated client lib does not allow my to write syntax errors in my queries and ensures all type conversions are sound. Maybe PRQL can also be a language like GraphQL in that regard, and provide a schema too.

4. JPQL. It's close to SQL. It improves to SQL, but I never found it enough of an improvement to justify the cost. I think your proposal is better. But still I think JPQL deserves a mention as maybe one of the most widespread compile-to-SQL languages.

Re: PRQL – A proposal for a better SQL

#90

Post by magicalhippo »

ako wrote: post #86
magicalhippo wrote: post #62

Now this is actually nice, unlike the other suggestion posted today[1]. Maybe I'm just too used to non-standard extensions of our database but the SQL example could, at least for our db, be rewritten as SELECT TOP 20 title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_salary) AS sum_gross_salary, AVG(gross_cost) AS average_gross_cost, SUM(gros…

With a CTE it would read a bit more like prql: with usa_employees as ( SELECT title, country, salary, (salary + payroll_tax) AS gross_salary, (salary + payroll_tax + healthcare_cost) AS gross_cost FROM employees WHERE country = 'USA' AND (salary + payroll_tax + healthcare_cost) > 0 ) select title, country, AVG(salary) AS average_salary, SUM(salary) AS sum_salary, AVG(gross_salary) AS average_gross_salary, SUM(gross_s…

> With a CTE

The DB we use supports those, I just learned about them too late so keep forgetting they exist :(

> It would really help in SQL if you could refer to column aliases so you don't have to repeat the expression.

The DB we use supports that, so in your CTE you could write

   AND   gross_cost > 0
We do that all the time, which will be a pain now that we're migrating to a different DB server which doesn't.
Post reply on HN