Write table periodically at 20:00 each day

Question

Situation depiction: I got a shiny app up and running on a server 24/7 connected to live data via SQL Server updated every day.

Problem: I want to automate table writing process and set it up to create a table every day at 20:00. The write-up process should include an automated refresh of the shiny app as it needs to update & recalculate the numbers from SQL source connection.

Hence, the ideal process would look like: day1 20:00 -->shiny app is re-run, updated data are fed into analysis-->backup table of the result is saved as csv in a directory under the name 'Back-up 1' day2 20:00 -->shiny app is re-run, updated data are fed into analysis-->backup table of the result is saved as csv in a directory under name 'Back-up 2' and so on...

What I seek is R code indication for: 1 how to make a table to be written periodically 2 how to make shiny app refresh periodically

Question Update: Creating a reactive database connection as per advice below: The problem with implementation is that in my Shiny app I create 3 reactive datasets in order to visualise data, The solution would be (I imagine) to build in the reactiveness of the database into each of the 3 reactive dataset, however I wonder if there would be an intelligent way how to do this without repetition of the same lines of code 3 time. Here is the server side of Shiny (not working)

##server.R##
server<-function(input,output, session){

MyData <- reactive({   
invalidateLater(86400000, session) 

#connect to the Server 
connection <- odbcConnect(dns, user, pass)
SituationToday<-{cat("test");sqlQuery(connection, "SELECT ALL * FROM   Table;")}
odbcClose(connection)

#data manipulation of SituationToday dataset including cleaning, filtering, joins, re-coding, labelling & as result I get 2 datasets
df1
df2

#backup
write.csv2(df1, paste0("filepath/Backup1", sys.Date(), ".csv"))
write.csv2(df2, paste0("filepath/Backup2", sys.Date(), ".csv"))

#reactive datasets that I need in order to visualise the data
data.df<-reactive({
VARIABLE<-input$variable
df1[df1$variable %in% VARIABLE,]})

data2.df<-reactive({
VARIABLE2<-input$variable2
df2[df2$variable2 %in% VARIABLE2,]})

data3.df<-reactive({
SELECT<-input$select
GROUP<-input$group
df1[df1$variable %in% SELECT & df1$GROUP %in% GROUP,]})
})

#different outputs follow
output$plot<-renderPlot({
  plot(data.df()) })
output$plot<-renderPlot({
  plot(data.df2()) })
output$plot<-renderPlot({
  plot(data.df3()) })
}

shinyApp(ui=ui,server=server)

How can I keep the connection reactive while creating 3 different reactive datasets within the Server function without having to repeat the 1/3rsd of the server code inside each of the reactive datasets?

Answer 1

As an answer to your comment @Patrik, here is an example on how I generally deal with those things:

(Only providing the server side of the shiny application. UI needs no adjustments.)

server = function(input, output, session){

  YourData <- reactive({                   # Responds to changes. But in here, only the invalidation triggers change.
    invalidateLater(86400000, session)     # Invalidates YourData() after 86400000 millisecs = 24 hours.

    #-------- Some statements that gets Data from SQL Database, e.g. with library RODBC
    conn <- odbcDriverConnect("DNS=DB;UID=Usrname;PWD=12345")
    rawData <- sqlQuery(conn, "SELECT * FROM Table1")
    odbcClose(conn)

    #-------- Some statements that process your Data.
    rawData$value <- rawData$value * 2
    rawData$time <- strptime(rawData$time, format = "%Y-%m-%d %H:%M")

    #-------- Backup creation after data processing.
    write.csv2(rawData, paste0("filepath/Backup", sys.Date(), ".csv"))

    #-------- And finally call the Dataset you want to return to use in your Shiny-App
    rawData 
 })

  output$plot <- renderPlot({
    plot(YourData())
  })
}

With that, your Shiny App keeps on running, but fetches new Data and does all computations and backup creating once every 24 hours.

It might still be a bit abstract, but feel free to ask, if anything is unclear.

Reaction to Updated Question

Problem: MyData() is not just a chunk of code, but an Object with a normal R class, stored as a normal R variable. But in contrast to other variables, Shiny checks on its value permanently to detect changes and it tracks all dependencies to that variable.

Solution: My first approach would be to let the invalidation only affect your Query. And then this effects your other reactive environments in some sort of "cascade".

Here the Code:

server<-function(input,output, session){

  # First, return only your SQL results

  MyData <- reactive({   
    invalidateLater(86400000, session) 

    #connect to the Server 
    connection <- odbcConnect(dns, user, pass)
    SituationToday<-{cat("test");sqlQuery(connection, "SELECT ALL * FROM   Table;")}
    odbcClose(connection)

    SituationToday
  })

  # Second, manipulate you dataframes 1 and 2

  df1 <- reactive({
    SituationToday <- MyData()        # Reacts whenever MayData() changes

    #data manipulation of SituationToday dataset including cleaning, filtering, joins, re-coding, labelling
    # Resulting in your Set df1

    #backup
    write.csv2(df1, paste0("filepath/Backup1", sys.Date(), ".csv"))

    # Return the dataframe
    df1
  })

  # Same for df2

  df2 <- reactive({
    SituationToday <- MyData()        # Reacts whenever MayData() changes

    #data manipulation of SituationToday dataset including cleaning, filtering, joins, re-coding, labelling
    # Resulting in your Set df2

    #backup
    write.csv2(df2, paste0("filepath/Backup2", sys.Date(), ".csv"))

    # Return the dataframe
    df2
  })

  #reactive datasets that I need in order to visualise the data

  data.df <- reactive({
    VARIABLE<-input$variable              # Reacts to input
    df1()[df1()$variable %in% VARIABLE,]  # As well as change in df1()
  })

  data2.df<-reactive({
    VARIABLE2<-input$variable2              # Reacts to input
    df2()[df2()$variable2 %in% VARIABLE2,]  # As well as change in df2()
  })

  data3.df<-reactive({
    SELECT<-input$select
    GROUP<-input$group
    df1()[df1()$variable %in% SELECT & df1()$GROUP %in% GROUP,] # Again df1() dependant
  })

  #different outputs follow

  output$plot<-renderPlot({
    plot(data.df()) })
  output$plot<-renderPlot({
    plot(data.df2()) })
  output$plot<-renderPlot({
    plot(data.df3()) })
}

If you are really annoyed by having to split creating df1 and df2, you could also consider returning a list of your 2 data.frames.