Loading prqlr makes the prql code chunks
executable in R Markdown and Quarto Markdown files.
Output formats
It can either output the results of the actual query execution or display a SQL query compiled from a PRQL query.
Use with {DBI} connections
If using with database connections (set the name of
DBI connection to the connection chunk
option), PRQL code chunks pass the output SQL to knitr’s
SQL engine and behaves like SQL
code chunks. So, usage is the same as for SQL code chunks.
For example, let’s render an R Markdown file named
test.Rmd with the following contents with the
knitr::knit() function.
```{r}
#| echo: false
library(DBI)
library(prqlr)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
```
```{prql}
#| connection: con
from mtcars
filter cyl > 6
select {cyl, mpg}
derive {mpg_int = math.round 0 mpg}
take 3
```After rendering, a Markdown file named test.md is
generated with the following contents.
``` elm
from mtcars
filter cyl > 6
select {cyl, mpg}
derive {mpg_int = math.round 0 mpg}
take 3
```
| cyl| mpg| mpg_int|
|---:|----:|-------:|
| 8| 18.7| 19|
| 8| 14.3| 14|
| 8| 16.4| 16|Note that the syntax highlighting of Elm is the best for PRQL, so the
output code blocks are marked as elm. This can be changed
by setting the lang chunk option.
Without database connections
PRQL code chunks without the connection option will
generate SQL code blocks.
```{r}
#| echo: false
library(prqlr)
```
```{prql}
from mtcars
filter cyl > 6
select {cyl, mpg}
derive {mpg_int = math.round 0 mpg}
take 3
```The R Markdown file above will be converted to the Markdown file below.
Engine options
Compiler options
We can pass some options of prql_compile() via the chunk
option engine-opts (or engine.opts).
Note that the format option is always TRUE
for PRQL code chunks.
Parameterized PRQL code blocks
The experimental engine option use_glue can be used to
insert R variables into PRQL queries.
Characters enclosed in {{ and }} are
replaced by the glue::glue() function before being passed
to the prql_compile() function.
```{r}
#| echo: false
library(prqlr)
cyl_min <- 6
derive_or_select <- "derive"
```
```{prql}
#| engine-opts:
#| use_glue: true
from mtcars
filter cyl > {{cyl_min}}
select {cyl, mpg}
{{derive_or_select}} {mpg_int = math.round 0 mpg}
take 3
```The R Markdown file above will be converted to the Markdown file below.
``` elm
from mtcars
filter cyl > 6
select {cyl, mpg}
derive {mpg_int = math.round 0 mpg}
take 3
```
```sql
SELECT
cyl,
mpg,
ROUND(mpg, 0) AS mpg_int
FROM
mtcars
WHERE
cyl > 6
LIMIT
3
-- Generated by PRQL compiler version:0.13.0 (https://prql-lang.org)
```Use query strings in R code blocks
With the experimental use_glue option, the query string
can even be defined completely in R side.
```{r}
#| echo: false
library(prqlr)
prql_query <- "from mtcars
select cyl"
```
```{prql}
#| engine-opts:
#| use_glue: true
{{prql_query}}
```This example outputs the following.
Set special info string to output SQL code blocks
When making SQL code blocks output without a DB connection, the
info_string option can be used to control the info string
of the output code block. This is convenient when we want to add special
attributes that are used in Quarto Markdown files. The default value is
"sql".
For example, the following R Markdown file includes a PRQL code block
with the info_string option will be converted to…
```{r}
#| echo: false
library(prqlr)
```
```{prql}
#| engine-opts:
#| info_string: '{.sql filename="SQL"}'
from mtcars
take 3
```This Markdown file.