Skip to content

Instantly share code, notes, and snippets.

@kellobri
Last active April 10, 2023 09:20
Show Gist options
  • Save kellobri/f026e30f672b1ab8d6eb2c2f2b91deeb to your computer and use it in GitHub Desktop.
Save kellobri/f026e30f672b1ab8d6eb2c2f2b91deeb to your computer and use it in GitHub Desktop.
How-To: Leverage R Markdown output files to create simple ETL processes on RStudio Connect (These are two separate data assets)
library(shiny)
library(DT)
data <- read.csv('https://colorado.rstudio.com/rsc/content/2352/data.csv')
ui <- fluidPage(
titlePanel("Basic Data Filter Application"),
hr(),
h4("This application presents data generated by a scheduled R Markdown process: ", tags$a(href="https://colorado.rstudio.com/rsc/content/2352", "See it here!")),
p("Use this framework to build out your own R Markdown-based ETL jobs hosted on RStudio Connect."),
br(),
fluidRow(
column(4,
selectInput("a",
"Attribute A:",
c("All","Positive Values","Negative Values"))
),
column(4,
selectInput("b",
"Attribute B:",
c("All","Positive Values","Negative Values"))
),
column(4,
selectInput("c",
"Attribute C:",
c("All","Positive Values","Negative Values"))
)
),
dataTableOutput("table")
)
server <- function(input, output) {
# Filter data based on selections
output$table <- renderDataTable(DT::datatable({
if (input$a != "All") {
if (input$a == "Positive Values") {
data <- data[data$a >= 0,]
} else data <- data[data$a < 0,]
}
if (input$b != "All") {
if (input$b == "Positive Values") {
data <- data[data$b >= 0,]
} else data <- data[data$b < 0,]
}
if (input$c != "All") {
if (input$c == "Positive Values") {
data <- data[data$c >= 0,]
} else data <- data[data$c < 0,]
}
data
}))
}
# Run the application
shinyApp(ui = ui, server = server)
---
title: "Output File Framework for R Markdown ETL on RStudio Connect"
output: html_document
rmd_output_metadata:
rsc_output_files:
- "data.csv"
---
```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```
A common use case for [Scheduling on RStudio Connect](https://docs.rstudio.com/connect/user/r-markdown-schedule.html), is to use that feature as part of an R-based process to automate scheduled data updates. This example report outputs a CSV file that can be used/consumed by other assets hosted on RStudio Connect.
## R Markdown Output Metadata and Output Files
- [User Guide Reference](https://docs.rstudio.com/connect/user/r-markdown.html#r-markdown-output-files)
The purpose of this R Markdown document is to make an output data file (updated on a schedule) available over HTTP on my RStudio Connect server.
### Extract/Transform Data
```{r}
df <- data.frame(a=rnorm(50), b=rnorm(50), c=rnorm(50), d=rnorm(50), e=rnorm(50))
```
Every time this report is executed, it creates a new random data frame. _Creating dummy data is not representative of a typical ETL process._ You'll likely want to replace this section with code that pulls data from a database or API.
- Best practices for working with databases can be found at [db.rstudio.com](https://db.rstudio.com/)
- The `httr` package is a [good place to start](https://cran.r-project.org/web/packages/httr/vignettes/quickstart.html) when working with REST APIs and the http protocol
### Show a nice table preview (optional)
```{r message=FALSE, warning=FALSE}
library(gt)
library(dplyr)
df %>%
sample_n(6) %>%
gt() %>%
tab_header(
title = "Current Data Sample"
)
```
### Write data (CSV file) Important!
```{r}
write.csv(df, "data.csv", row.names=FALSE)
```
This is the step that creates the data.csv output file. There are two ways to specify output files:
- List file names in the R Markdown YAML header under `rmd_output_metadata` and `rsc_output_files` _(done above)_
- List the output files from within the R code chunk
Reference: [How to work with output files](https://docs.rstudio.com/connect/user/r-markdown.html#how-to-work-with-output-files)
---
### Download data
#### Here is the data generated from this report: [data.csv](data.csv)
@cderv
Copy link

cderv commented Apr 25, 2019

Really nice example ! thanks !

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment