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

71–80 of 214 posts

Re: PRQL: Pipelined Relational Query Language

#71

Post by snthpy »

netcraft wrote: post #55

I dont know the right words, but creating a plugin for DBT for this would give it a lot of traction I bet.

Totally agree. This was a high priority goal for us and @maximilianroos did a lot of work putting something together. In the end, I believe it was really fighting against how dbt is set up though as it is so tightly interwoven with Jinja2. Perhaps with the introduction of the Python dbt models, things might have changed now and it's worth taking another look.

https://github.com/PRQL/dbt-prql

(Disclaimer: I'm a PRQL contributor.)

Re: PRQL: Pipelined Relational Query Language

#72

Post by Taikonerd »

The limitation of PRQL is that it only does SELECTs, by design. If you want to insert/update/delete data, you're back to SQL.

That means that your team's data scientist might give you a query written in PRQL, but if you want to actually incorporate it into the data pipeline, you'll need to translate it into SQL.

I wish that PRQL would support at least a limited ability to insert -- for example, maybe just the case of inserting into a new temp table. No update or ON CONFLICT logic to worry about. It could look like this:

    from tracks
    filter artist == "Bob Marley"
    save bob_marley_tmp

Re: PRQL: Pipelined Relational Query Language

#73

Post by mulmen »

aidos wrote: post #70
mulmen wrote: post #68

Earlier quoted context omitted.

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

Yeah sorry I edit a lot. I'll bump up my delay.

Tables, views, and CTEs are all tools that can be used to make SQL more readable. They are all valid alternatives to subqueries in JOINs which is the only thing I would say you should "never" do.

I tend to use all of them. I create tables with an optimized (typically star) schema for my purposes. My date dimensions are almost always views on top of a list of dates. I also use views to create a ~"feature store" that pre-joins the underlying star schema. I can then write "simple" analytical queries that utilize CTEs. Those CTEs tend to not have joins and they tend to only apply relevant filters so the final select is clear and concise.

Re: PRQL: Pipelined Relational Query Language

#74

Post by OJFord »

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 rather:

    from employees
    select {id, first_name, age}
    sort age
    take 10
..but wait, actually the SQL could've been:

    select id, first_name, age
    from employees
    order by age
    limit 10
and it's more verbose by a character or two... (no braces, but 'order by' vs 'sort')

Re: PRQL: Pipelined Relational Query Language

#76

Post by crote »

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 main issue with SQL is that you are stuck in a very strict way of writing things, which does not clearly match to how I think. The top-down way of writing PRQL where each step is simply a transformation of the previous one makes way more sense to me. SQL is something I'd need a reference manual for, PRQL is simply writing down what I want the query to do.

I do agree that PRQL's join syntax is extremely bad, though. They should've stuck to explicit "left join"-like keywords, and the alias & join column shorthand could be done better.

Re: PRQL: Pipelined Relational Query Language

#77

Post by nonethewiser »

animal_spirits wrote: post #51
nonethewiser wrote: post #46

This is tangential but a new query language is inevitably based in the idea that SQL is deficient in some manner (hard, not ergonomic, whatever). More interestingly, it also implies that the countless alternatives aren't good enough either. Is there an existing query language that anyone will argue is better than SQL? I have limited exposure on this, but if SQL is really not that good then I'd expect there to be a be…

I counter the argument that just because it is still around it is the best solution. Many things stick around just because of inertia, for example, the QWERTY keyboard.

Then state the better solution.

Besides, I'm not saying it's the best because it's most common, or even that its the best at all. It just makes it difficult to understand the argument that SQL sucks when its so widely used and no one can agree on an alternative. Including in new databases which could opt for these "new and improved" variants.

I grant that there is a first-mover advantage and it's not easy to shift paradigms. That's a big factor. That's why I am honestly - not rhetorically - asking which query language is clearly better than SQL? And why do people keep making new challengers instead of backing something that's already better than SQL? There seems to be very little support for these query languages despite many people saying SQL sucks and they want a redo.

Re: PRQL: Pipelined Relational Query Language

#79

Post by mhink »

0cf8612b2e1e wrote: post #50
nonethewiser wrote: post #46

This is tangential but a new query language is inevitably based in the idea that SQL is deficient in some manner (hard, not ergonomic, whatever). More interestingly, it also implies that the countless alternatives aren't good enough either. Is there an existing query language that anyone will argue is better than SQL? I have limited exposure on this, but if SQL is really not that good then I'd expect there to be a be…

I think of it in the same way as JavaScript. Clearly, it has issues, but if you want to work on the web, it is what you have available. If you want to speak to Oracle/MySQL/SQL Server/Postgres database you are writing SQL. New entrants, could make a new language, but now they are fighting a battle on two fronts: the novel technology +query language.

> I think of it in the same way as JavaScript. Clearly, it has issues, but if you want to work on the web, it is what you have available.

And, interestingly, the approach they're taking here is similar to how folks have dealt with JS: introduce a transpiled language whose paradigms are close enough to the host language to feel familiar. Reminds me of CoffeeScript, actually (although if we're being honest, I couldn't stand CoffeeScript.)

Re: PRQL: Pipelined Relational Query Language

#80

Post by snthpy »

heywhatupboys wrote: post #37

This is just tidyr ?

Kind of. dplyr, Pandas, LINQ, Kusto, etc... are big inspirations. The fact that these are so popular and have reinvented the same workflows with slightly different syntaxes to me is a sign that they capture something fundamental about how humans like to think about data transformations.

PRQL is indeed very close to dplyr. In my (biased) opinion, PRQL is actually a bit cleaner than dplyr because it is its own language and doesn't have to work as DSL inside R. The same goes for comparisons with Pandas and Polars having to work as DSLs inside Python.

Compare

    from mtcars
    filter cyl > 6
    select {cyl, mpg}
    sort {-mpg}
with

    mtcars %>%
    filter(cyl > 6) %>%
    select(cyl, mpg) %>%
    arrange(dplyr::desc(mpg))
Incidentally, I produced the dplyr code from the PRQL with

    ```R
    install.packages("prqlr", repos = "https://eitsupi.r-universe.dev", lib="~/.local/R_libs/")
    library(prqlr, lib.loc="~/.local/R_libs/")
    library("tidyquery")
    "
    from mtcars
    filter cyl > 6
    select {cyl, mpg}
    sort {-mpg}
    " |> prql_to_sql() |> tidyquery::show_dplyr()
    ```
(Disclaimer: I'm a PRQL contributor.)
Post reply on HN