Rmd file with embedded SQL chunk. Possible to move the SQL to external file then source?

You can use the power of knitr to fill the content of a chunk programmatically thanks to the code chunk option. That means you can read you sql from a file to the chunk, then it is executed as any other SQL chunk.

Here is a reproducible example to show what I mean. I think it covers what you are looking for. Please tell me otherwise.

You can put the lines below in a Rmd file and render it.

---
title: "Use external SQL file for SQL chunks"
output: html_document
---

In order to provide a reproducible example, we use dplyr and dbplyr with `tbl_memdb` to load a table into a memory db.

```{r}
library(dplyr)
library(dbplyr)
iris_db <- tbl_memdb(iris)
# we isolate the connection object 
con <- src_memdb()$con
```

We check that it works with SQL chunk to filter data into an R object

```{sql, connection=con, output.var="setosa"}
SELECT * FROM iris WHERE Species == "setosa"
```

The object `setosa` exists

```{r}
glimpse(setosa)
```

Know, let see how to load the SQL command from a file to a chunk then execute to save in a R object. We can do it through the power of knitr.

For the demo, we write some SQL in a script `versicolor.sql` using the `cat` engine that can write the content of a chunk to file.

```{cat, engine.opts = list(file = "versicolor.sql", lang = "sql")}
SELECT * FROM iris WHERE Species == "versicolor"
```

and we check that the file exists with some content

```{r}
readLines("versicolor.sql")
```

Now we can use this script to fill the content of the chunk and execute it like any sql chunk. We use the knitr chunk option `code` that allow to fill the content of a chunk programmatically. 

```{sql, connection = con, code=readLines("versicolor.sql"), output.var="versicolor"}
```

The chunk is executed as any other SQL chunk. We check that versicolor data exists

```{r}
glimpse(versicolor)
```

9 Likes