Is it possible to cache the result of a SQL chunk in R Markdown?


#1

I wanted to cache the result of a SQL chunk as well as its output.var, but I couldn't. Knitting the .Rmd like bellow ends up with an error object 'select_1_result' not found at the second run:

---
title: "sql chunk"
output: html_document
---

```{r setup, include=FALSE}
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
knitr::opts_chunk$set(echo = TRUE, connection = "con")
```

```{sql select_1, output.var="select_1_result", cache=TRUE}
SELECT 1
```

```{r print_result,  error=TRUE}
knitr::kable(select_1_result)
```

It's strange. I guess this had been possible in the past, at least at the moment of this SO was answered:

Now I use a work around of using an external file and it's just fine, but am I wrong at some point?

```{sql select_1, output.var="select_1_result", eval=!file.exists("/path/to/cache_file")}
SELECT 1
```

```{r write_feather, eval=!file.exists("/path/to/cache_file")}
feather::write_feather(select_1_result, "/path/to/cache_file")
```

```{r read_feather, eval=!exists("select_1_result")}
select_1_result <- feather::read_feather("/path/to/cache_file")
```

#2

Hi, I just tested your script on my computer and got an error as well when I run it for the second time.

Maybe the next step is to open an issue in the knitr GitHub repo? https://github.com/yihui/knitr/issues

cc @yihui


#3

Thanks for comfirming! Will file a issue.


#4

Update: It has been fixed in the development version of knitr:

devtools::install_github('yihui/knitr')

It has been filed here: https://github.com/rstudio/rmarkdown/issues/914 (although it is really a knitr issue). I don't have time to look into it yet. If you are able to figure out a solution, I'll be happy to review a pull request. Thanks!


#5

Thanks, I'll look into this. Sorry that I missed that issue...

Anyway, I'm glad to know this is not that I'm doing something wrong :slight_smile: