4

My RMarkdown notebook with a SQL chunk runs fine when I run all the chunks one by one interactively, but when I try to knit, the SQL chunk does not have save the data into the specified variable. When the dataset that was supposed to be generated using the SQL chunk is referenced in later R chunks, the dataset variable is simply empty.

Here's an example

{r setup, include=FALSE, warning=FALSE, message=FALSE}
# load necessary libraries
library(bigrquery)
library(knitr)
library(tidyverse)

db <- dbConnect(dbi_driver(), dataset = 'sandbox', project = 'project_id', use_legacy_sql = FALSE)

df <- NULL
```


```{sql, connection=db, output.var=df}
select * from example_dataset
limit 10
```
returns dataset


```{r}
head(df)
```
NULL

I've tried the solution here (R: Knitr gives error for SQL-chunk), but it didn't solve my problem.

Harry M
  • 1,848
  • 3
  • 21
  • 37

1 Answers1

6

Just ran into the same problem and it looks like you need to quote the variable you are assigning.

```{sql, connection=db, output.var="df"}
select * from example_dataset
limit 10
```

Source: http://rmarkdown.rstudio.com/authoring_knitr_engines.html#sql

vitallish
  • 312
  • 1
  • 12
  • Wow this is incredible! I was trying to figure this out for so long! I had originally looked at another example elsewhere that hadn't used quotes and never thought to include them. Thank you! – Harry M Sep 18 '17 at 06:31