Last active
February 13, 2022 14:00
-
-
Save isteves/aaf339505c82762e8747faa3efb29c89 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