How-To: Leverage R Markdown output files to create simple ETL processes on RStudio Connect (These are two separate data assets)

app.R

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)

output-file-example.Rmd

---
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)

Really nice example ! thanks !