4 ms·

It's just syntax, it compiles to SQL and runs on today's DBMS. It has no difference in speed or functionality.

by remram 3y ago

ttfkam 3y ago
(I couldn't help but notice you didn't comment on the difference in formatting in the examples.) Do you have examples of PRQL working with jsonpath? Generating JSON? Unnesting arrays? Returning ids from an INSERT or UPDATE without making a separate read query? Not trying to be argumentative. Honest question.
- remram 3y ago
  I am not affiliated in any way with the PRQL project. Those are great questions though, I hope we get an answer.
  - snthpy 3y ago
    @remram I replied to the parent comment.
- snthpy 3y ago
  Thanks, that's a great question. You're right in that so far we haven't highlighted working with JSON and I hadn't actually tried until this point. IMHO the true power of PRQL comes from the fact that it allows you to define functions and with that you get the power of composability which is the true power of almost every programming language (and which is for the most part completely lacking in SQL). So with that said, I tried the following POC (remember that PRQL is just a SQL generator so the JSON capabilities depend on your underlying RDBMS): ```sh > prqlc compile <<EOF let get = path obj -> s"""{obj} -> {path}""" let getstr = path obj -> s"""{obj} ->> {path}""" let extract = obj path -> s"""json_extract({obj}, {path})""" from [{data='{"duck": [1, 2, 3]}'}] select { data | get '$.duck[0]', data | getstr '$.duck[1]', extract data '$.duck[2]' } EOF WITH table_0 AS ( SELECT '{"duck": [1, 2, 3]}' AS data ) SELECT data -> '$.duck[0]', data ->> '$.duck[1]', json_extract(data, '$.duck[2]') FROM table_0 -- Generated by PRQL compiler version:0.9.4 (https://prql-lang.org) ``` What's going on here is that I used [s-strings](https://prql-lang.org/book/reference/syntax/s-strings.html https://prql-lang.org/book/reference/syntax/s-strings.html) to define custom PRQL functions `get`, `getstr` and `extract` which translate into the underlying `->`, `->>` and `json_extract` SQL constructs. You could then for example pipe that query to DuckDB (the example is taken from the following DuckDB blogpost [Shredding Deeply Nested JSON, One Vector at a Time](https://duckdb.org/2023/03/03/json.html https://duckdb.org/2023/03/03/json.html)): ```sh > prqlc compile <<EOF - | duckdb let get = path obj -> s"""{obj} -> {path}""" let getstr = path obj -> s"""{obj} ->> {path}""" let extract = obj path -> s"""json_extract({obj}, {path})""" from [{data='{"duck": [1, 2, 3]}'}] select { data | get '$.duck[0]', data | getstr '$.duck[1]', extract data '$.duck[2]'} EOF ┌───────────────────────┬──────────────────────────┬───────────────────────────────────┐ │ "data" -> '$.duck[0]' │ ("data" ->> '$.duck[1]') │ json_extract("data", '$.duck[2]') │ │ json │ varchar │ json │ ├───────────────────────┼──────────────────────────┼───────────────────────────────────┤ │ 1 │ 2 │ 3 │ └───────────────────────┴──────────────────────────┴───────────────────────────────────┘ ``` HTH
  - ttfkam 3y ago
    Excellent, thank you for the response.
lolinder 3y ago
It does make a difference if the resulting SQL is unperformant. Someone who is good at SQL can look at a query and see where the query planner might go wrong, then make subtle tweaks to get better performance. Optimizing queries in a compile-to-SQL language basically has to be left to the compiler, which may not have the context needed to write performant SQL. Compiling a query is different than compiling a whole program to assembly, because the query makes assumptions about schemas and indexes that cannot be encapsulated in what you're feeding to the compiler at that moment.
- maximilianroos 3y ago
  So I agree that unperformant SQL is unperformant, but PRQL can _reduce_ the chance of making mistakes there. Here's an example [1] of someone reporting that a query engine was far more performant with one SQL construction, and then PRQL changing the SQL we output to use that construction. GCC & Clang are much better at compiling to assembly than any person! PRQL isn't there yet, but each improvement scales to everyone who uses it. [1]: https://github.com/PRQL/prql/issues/2182 https://github.com/PRQL/prql/issues/2182 [Disclaimer: PRQL dev]
  - ttfkam 3y ago
    Be honest. C to object code to linker to final binary are a MUCH bigger leap than PRQL to SQL. You make it sound like SQL is some insurmountable hurdle while PRQL is a bunny slope. You're not getting anywhere with that nonsense. [Disclaimer: not a PRQL dev]
    - maximilianroos 3y ago
      I don't know where this aggression is coming from — we're an open-source project, only volunteers, trying to do something that makes it easier for folks to work with data. Critical & constructive feedback would be really appreciated, but that's not this — why all the snark?
      - ttfkam 3y ago

It's just syntax, it compiles to SQL and runs on today's DBMS. It has no difference in speed or functionality.

by remram 3y ago

It's just syntax, it compiles to SQL and runs on today's DBMS. It has no difference in speed or functionality.

(I couldn't help but notice you didn't comment on the difference in formatting in the examples.) Do you have examples of PRQL working with jsonpath? Generating JSON? Unnesting arrays? Returning ids from an INSERT or UPDATE without making a separate read query? Not trying to be argumentative. Honest question.

remram 3y ago
I am not affiliated in any way with the PRQL project. Those are great questions though, I hope we get an answer.
- snthpy 3y ago
  @remram I replied to the parent comment.
snthpy 3y ago
Thanks, that's a great question. You're right in that so far we haven't highlighted working with JSON and I hadn't actually tried until this point. IMHO the true power of PRQL comes from the fact that it allows you to define functions and with that you get the power of composability which is the true power of almost every programming language (and which is for the most part completely lacking in SQL). So with that said, I tried the following POC (remember that PRQL is just a SQL generator so the JSON capabilities depend on your underlying RDBMS): ```sh > prqlc compile <<EOF let get = path obj -> s"""{obj} -> {path}""" let getstr = path obj -> s"""{obj} ->> {path}""" let extract = obj path -> s"""json_extract({obj}, {path})""" from [{data='{"duck": [1, 2, 3]}'}] select { data | get '$.duck[0]', data | getstr '$.duck[1]', extract data '$.duck[2]' } EOF WITH table_0 AS ( SELECT '{"duck": [1, 2, 3]}' AS data ) SELECT data -> '$.duck[0]', data ->> '$.duck[1]', json_extract(data, '$.duck[2]') FROM table_0 -- Generated by PRQL compiler version:0.9.4 (https://prql-lang.org) ``` What's going on here is that I used [s-strings](https://prql-lang.org/book/reference/syntax/s-strings.html https://prql-lang.org/book/reference/syntax/s-strings.html) to define custom PRQL functions `get`, `getstr` and `extract` which translate into the underlying `->`, `->>` and `json_extract` SQL constructs. You could then for example pipe that query to DuckDB (the example is taken from the following DuckDB blogpost [Shredding Deeply Nested JSON, One Vector at a Time](https://duckdb.org/2023/03/03/json.html https://duckdb.org/2023/03/03/json.html)): ```sh > prqlc compile <<EOF - | duckdb let get = path obj -> s"""{obj} -> {path}""" let getstr = path obj -> s"""{obj} ->> {path}""" let extract = obj path -> s"""json_extract({obj}, {path})""" from [{data='{"duck": [1, 2, 3]}'}] select { data | get '$.duck[0]', data | getstr '$.duck[1]', extract data '$.duck[2]'} EOF ┌───────────────────────┬──────────────────────────┬───────────────────────────────────┐ │ "data" -> '$.duck[0]' │ ("data" ->> '$.duck[1]') │ json_extract("data", '$.duck[2]') │ │ json │ varchar │ json │ ├───────────────────────┼──────────────────────────┼───────────────────────────────────┤ │ 1 │ 2 │ 3 │ └───────────────────────┴──────────────────────────┴───────────────────────────────────┘ ``` HTH
- ttfkam 3y ago
  Excellent, thank you for the response.

lolinder 3y ago

It does make a difference if the resulting SQL is unperformant. Someone who is good at SQL can look at a query and see where the query planner might go wrong, then make subtle tweaks to get better performance. Optimizing queries in a compile-to-SQL language basically has to be left to the compiler, which may not have the context needed to write performant SQL. Compiling a query is different than compiling a whole program to assembly, because the query makes assumptions about schemas and indexes that cannot be encapsulated in what you're feeding to the compiler at that moment.

maximilianroos 3y ago
So I agree that unperformant SQL is unperformant, but PRQL can _reduce_ the chance of making mistakes there. Here's an example [1] of someone reporting that a query engine was far more performant with one SQL construction, and then PRQL changing the SQL we output to use that construction. GCC & Clang are much better at compiling to assembly than any person! PRQL isn't there yet, but each improvement scales to everyone who uses it. [1]: https://github.com/PRQL/prql/issues/2182 https://github.com/PRQL/prql/issues/2182 [Disclaimer: PRQL dev]
- ttfkam 3y ago
  Be honest. C to object code to linker to final binary are a MUCH bigger leap than PRQL to SQL. You make it sound like SQL is some insurmountable hurdle while PRQL is a bunny slope. You're not getting anywhere with that nonsense. [Disclaimer: not a PRQL dev]
  - maximilianroos 3y ago
    I don't know where this aggression is coming from — we're an open-source project, only volunteers, trying to do something that makes it easier for folks to work with data. Critical & constructive feedback would be really appreciated, but that's not this — why all the snark?
    - ttfkam 3y ago