CODEWITHSUNDEEP
sqlrgoogle-bigqueryrstudioknitr
Harry M
Harry M

Reputation: 2088

knitr sql chunk not saving data into variable

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.

Upvotes: 4

Views: 797

Answers (1)

vitallish
vitallish

Reputation: 322

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

Upvotes: 6

Related Questions