Pipelined Relational Query Language, pronounced "Prequel".
PRQL is a modern language for transforming data — a simpler and more powerful SQL. Like SQL, it's readable, explicit and declarative. Unlike SQL, it forms a logical pipeline of transformations, and supports abstractions such as variables and functions. It can be used with any database that uses SQL, since it transpiles to SQL.
PRQL was discussed on Hacker News here and Lobsters here.
Here's a fairly simple SQL query:
sql
SELECT TOP 20
title,
country,
AVG(salary) AS average_salary,
SUM(salary) AS sum_salary,
AVG(salary + payroll_tax) AS average_gross_salary,
SUM(salary + payroll_tax) AS sum_gross_salary,
AVG(salary + payroll_tax + benefits_cost) AS average_gross_cost,
SUM(salary + payroll_tax + benefits_cost) AS sum_gross_cost,
COUNT(*) as count
FROM employees
WHERE salary + payroll_tax + benefits_cost > 0 AND country = 'USA'
GROUP BY title, country
ORDER BY sum_gross_cost
HAVING count > 200
Even this simple query demonstrates some of the problems with SQL's lack of abstractions:
WHERE
clause obfuscates the meaning of the expression.HAVING & WHERE are fundamentally
similar operations applied at different stages of the pipeline but SQL's lack
of pipeline-based precedence requires it to have two different operators.SELECT operator both
creates new aggregations, and selects which columns to include.SELECT list causes a syntax error because of how commas are handled, and
we need to repeat the columns in the GROUP BY clause in the SELECT list.Here's the same query with PRQL:
elm
from employees
filter country = "USA" # Each line transforms the previous result.
derive [ # This adds columns / variables.
gross_salary: salary + payroll_tax,
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 aggregation calcs run on each group.
sum salary,
average gross_salary,
sum gross_salary,
average gross_cost,
sum_gross_cost: sum gross_cost,
count,
]
sort sum_gross_cost
filter count > 200
take 20
As well as using variables to reduce unnecessary repetition, the query is also
more readable — it flows from top to bottom, each line representing a
transformation of the previous line's result. For example, TOP 20 / take 20
modify the final result in both queries — but only PRQL represents it as the
final transformation. And context is localized — the aggregate function
contains both the calculations and the columns to group by.
Here's another SQL query, which calculates returns from prices on days with valid prices.
sql
SELECT
date,
-- Can't use a `WHERE` clause, as it would affect the row that the `LAG` function referenced.
IF(is_valid_price, price_adjusted / LAG(price_adjusted, 1) OVER
(PARTITION BY sec_id ORDER BY date) - 1 + dividend_return, NULL) AS return_total,
IF(is_valid_price, price_adjusted_usd / LAG(price_adjusted_usd, 1) OVER
(PARTITION BY sec_id ORDER BY date) - 1 + dividend_return, NULL) AS return_usd,
IF(is_valid_price, price_adjusted / LAG(price_adjusted, 1) OVER
(PARTITION BY sec_id ORDER BY date) - 1 + dividend_return, NULL)
- interest_rate / 252 AS return_excess,
IF(is_valid_price, price_adjusted_usd / LAG(price_adjusted_usd, 1) OVER
(PARTITION BY sec_id ORDER BY date) - 1 + dividend_return, NULL)
- interest_rate / 252 AS return_usd_excess
FROM prices
This might seem like a convoluted example, but it's taken from a real query. Indeed, it's also simpler and smaller than the full logic — note that it starts from
price_adjusted, whose logic had to be split into a previous query to avoid the SQL becoming even less readable.
Here's the same query with PRQL:
```elm prql version:0.1 db:snowflake # Version number & database name.
func lagday x = ( window x by secid sort date lag 1 ) func ret x = x / (x | lagday) - 1 + dividendreturn func excess x = (x - interestrate) / 252 func ifvalid x = isvalidprice ? x : null
from prices
derive [
returntotal: pricesadj | ret | ifvalid # | can be used rather than newlines.
returnusd: pricesusd | ret | ifvalid
returnexcess: returntotal | excess
returnusdexcess: returnusd | excess
]
select [
date,
secid,
returntotal,
returnusd,
returnexcess,
returnusd_excess,
]
```
Because we define the functions once rather than copying & pasting the code, we get all the benefits of encapsulation and extensibility — we can have reliable & tested functions, whose purpose is explicit, which we can share across queries and colleagues.
PRQL is intended to be a modern, simple, declarative language for transforming data, with abstractions such as variables & functions. It's intended to replace SQL, but doesn't have ambitions as a general-purpose programming language. While it's at a pre-alpha stage, it has some immutable principles:
I'm excited and inspired by the level of enthusiasm behind the project. Many projects have fallen at this stage, though, so I'm hoping we can demonstrate viability by building things that are within our immediate reach, before potentially expanding.
Here's an initial plan — feedback welcome:
Already since becoming public, the language has improved dramatically, thanks to the feedback of dozens of contributors. The current state of the basics is now stable (at least I don't expect the examples in the README to change much). While we're going to hit corner-cases, we're in a good enough place to start writing the code.
I'd encourage continued discussion on Issues, and examples to be added to the examples path.
We need to parse the PRQL into an AST. We're planning to use nom for this. I'm also open to those who've suggested writing a formal grammar concurrently.
This is the area that needs the most immediate help. If anyone is looking for an area to contribute, check out #26 — thank you!
I'm broadly envisioning two passes:
We'll need to make initial progress on the parser before starting here.
As well as a command-line tool that transpiles queries, it would be great if we could allow the language to be accessible; e.g.:
One benefit of PRQL over SQL is that auto-complete, type-inference, and transpile-time error checking become much more powerful.
This is much harder to build though, since it requires a connection to the database in order to understand the schema of the table. So this would come after having a working transpiler.
We should focus on solving a distinct problem really well. PRQL's goal is to make reading and writing analytical queries easier, and so for the moment that means putting some things out of scope:
If you're interested in the ideas here and would like to contribute towards them being explored:
Any of these will inspire others to spend more time developing this; thank you in advance.
Joins are implemented as {join_type} {table} {[conditions]}. For example:
elm
from employees
left_join positions [id=employee_id]
...is equivalent to...
sql
SELECT * FROM employees LEFT JOIN positions ON id = employee_id
Possibly we could shorten [id=id] to id, and use SQL's USING, but it may
be ambiguous with using id as a boolean column.
So a function like:
elm
func lag col sort_col by_col=id = (
window col
by by_col
sort sort_col
lag 1
)
...is called lag, takes three arguments col, sort_col & by_col, of
which the first two much be supplied, the third can optionally be supplied
with by_col:sec_id.
derive {column_name}: {calculation} in a
pipeline. derive can also take a list of pairs.
Technically this "upserts" the column — it'll either create or overwrite a
column, depending on whether it already exists.
{column_name} = {calculation}, but it breaks
the pattern of every line starting with a keyword.= to just have derive {column_name} {calculation}, which
would be more consistent with the other functions, but I think less readable
(and definitely less familiar).Most keywords that take a single argument can also take a list, so these are equivalent:
diff
from employees
-select salary
+select [salary]
More examples in list-equivalence.md.
A line-break generally creates a pipelined transformation. For example:
elm
from tbl
select [
col1,
col2,
]
filter col1 = col2
...is equivalent to:
elm
from tbl | select [col1, col2] | filter col1 = col2
A line-break doesn't created a pipeline in a few cases:
select example above).func.Potentially something like:
```elm table newest_employees = ( from employees sort tenure take 50 )
from newest_employees join salary [id] select [name, salary] ```
This is no longer point-free, but that's a feature rather than a requirement. The alternative is subqueries, which are fine in some small queries, but as queries get more complex, become difficult to digest.
How functions represent the previous result — the previous result is passed as
the final argument of a function; i.e. aggregate would be like this; where
X is taken from the line above:
elm
aggregate by=[] calcs X
Raw syntax — I think we should have backticks represent raw SQL; i.e. UPPER
could be defined as:
``elm
func upper col =UPPER(col)`
func upper col = UPPER({col})
func upper col = "UPPER({col})"
func upper col = f"UPPER({col})" ```
Arrays — PRQL is in part inspired by
DataPipes.jl, which demonstrates
how effective point-free pipelines can be
(Chain.jl is similar). One benefit
of this is how well it deals with arbitrarily nested pipelines — which are
difficult to read in SQL and even in jq. Could we do something similar for
nested data in PRQL?
DataPipes.jl — and we could avoid the macros / do / end):julia
@p begin
text
strip
split(__, "\n")
map() do __
collect
map() do __
__ == chars[begin] ? 1 : 0
end
end
hcat(__...)'
end
Partials — how functional do we want to make the lang? e.g. should we have
partial functions? e.g. [now based on an old version of window] potentially
we don't need the col in lag here?
elm
func lag col = window col by:sec_id sort:date lag:1
Boolean logic — how should we represent boolean logic like or? With some
or function that takes *args (which we don't currently have a design for)?
Or implement dyadic operators; either or or ||? (Same for not)
from — do we need from? A previous version of this proposal didn't require
this — just start with the table name. But some initial feedback was that
removing from made it less clear.
Readme syntax — we can't get syntax highlighting in GitHub's markdown — is there a solution to this aside from submitting a parser to GitHub / screenshots / creating a website?
elm as it coincidentally provides the best syntax
highlight (open to suggestions for others!).In advance of a full parser & compiler, could we use something like Codex to generate the transformations, and let us explore the space? We can provide our owen examples, by using fine-tuning. Changing examples is easier than changing compilers!