Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL chunks in RMarkdown
---
title: "SQL chunks in Rmd"
output: html_document
editor_options:
chunk_output_type: inline
---
## Set up the database table
```{r setup, message = FALSE}
library(dplyr)
library(dbplyr)
conn <- src_memdb() # create a SQLite database in memory
con <- conn$con # needed for SQL chunk
copy_to(conn, storms, overwrite = TRUE)
```
## View the file
```{sql connection=con}
select * from storms limit 5
```
## Passing in a single variable
```{r}
storm_status <- "hurricane"
```
```{sql connection=con}
select * from storms where status = ?storm_status limit 5;
```
## Passing in a column name
```{r}
col_name <- glue::glue_sql("status")
```
```{sql connection=con}
select * from storms where ?col_name = 'hurricane' limit 5;
```
## Passing in a vector
```{r}
types <- c("hurricane", "tropical depression")
storm_status <- glue::glue_sql("{types*}", .con = con)
```
```{sql connection=con}
select * from storms where status IN (?storm_status) limit 5;
```
## Using SQL files
```{r}
types <- c("hurricane", "tropical depression")
storm_status <- glue::glue_sql("{types*}", .con = con)
```
```{sql connection=con, code=readLines("storm.sql"), output.var="storm_preview"}
```
```{r}
storm_preview
```
-- !preview conn=src_memdb()$con
SELECT * FROM storms WHERE status IN (?storm_status) LIMIT 5;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.