Create View from prql

[jannishuebl]

Hi,

great work!

Is it somehow possible to create a View from an prql-Query?

Thanks,

Jannis

[ywelsch]

Hi Jannis,

this sounds more like a feature request for prql itself (see also https://github.com/PRQL/prql/issues/16). My project currently just wraps the prql compiler and only supports the language features of that compiler.

An alternative might be to look at my other project PSQL, which is a piped language that has full DuckDB support. In the README there is an example for creating a view.

[jdoig]

I'd also like this feature, it would be nice to have some kind of delimiter the plugin recognizes to dispatch the PRQL code to the compiler, and replace the result into the rest of the quey e.g:

SET prql_literal=🇵;
create temp table foo as (
🇵
  from stuff
  filter weight < 3
  select { name, colour }
  derive fun_factor 6 * cost
🇵
);

I know that's a lot easier said than done, and if I had a moment to spare I'd even try and do it myself :smile:

[max-sixty]

From the PRQL side, we'd like to make it easy for tools to support this sort of thing. We've tried to avoid implementing it in the PRQL language, so we can keep the PRQL language focused on the querying, and then other tools can wrap the queries in whatever they wish.

If there's anything we can do on our end, let us know. For example, I think we could guarantee that something like ``` wouldn't be in PRQL, so it could be used as a delimiter. (We'd have to think a bit more if we were going to design something really robust — what if someone decides to use that in a string — but would be happy to agree it in principle)

[ywelsch]

I've pushed a change that provides this now with a special syntax (| and |) to delimit start and end of PRQL (see README):

create view invoices_filtered as (|
  from invoices
  filter invoice_date >= @1970-01-16
  derive {
    transaction_fees = 0.8,
    income = total - transaction_fees
  }
  filter income > 1
|);

Future work could include making the syntax customizable.

[jdoig]

This is fantastic, though I can now understand any hesitance to take it on :sweat_smile: ... My first use case was similar to this

 WITH invoices_remote_data AS (FROM read_csv_auto('https://raw.githubusercontent.com/PRQL/prql/0.8.0/prql-compiler/tests/integration/data/chinook/invoices.csv'))
(|
  let foo = [{x=3,y=4}, {x=1,y=7}]
  from invoices_remote_data
  join foo (3==foo.x)
  filter invoice_date >= @1970-01-16
   derive { transaction_fees = 0.8, income = total - transaction_fees }
|);

Which causes the PRQL to compile it's own with statement under the SQL one. Don't get me wrong I'll happily take this as is :star_struck: ... This is great work, thank you again.

[ywelsch]

Ah, I see. WITH statements cannot be nested that way.

You can work around the WITH limitation as follows though:

WITH invoices_remote_data AS (
  FROM read_csv_auto('https://raw.githubusercontent.com/PRQL/prql/0.8.0/prql-compiler/tests/integration/data/chinook/invoices.csv')
),
prql_query AS (|
  let foo = [{x=3,y=4}, {x=1,y=7}]
  from invoices_remote_data
  join foo (3==foo.x)
  filter invoice_date >= @1970-01-16
   derive { transaction_fees = 0.8, income = total - transaction_fees }
|)
FROM prql_query;

[jdoig]

Ahh of course! thank again.