-
-
Save rdewald/44d0fe6a4b951cacc0a8390a96e502a5 to your computer and use it in GitHub Desktop.
SQL chunks in RMarkdown
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--- | |
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 | |
``` | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- !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