Shiny server - schedule mysql query instead of running everytime the app is loaded?

Question

I have a Shiny Server running in Ubuntu VM in Azure.

How do I schedule every night the mysql queries inside server.R ? And how do I avoid running them every time the app is visited ?

Here's a sample of my server.R and ui.R :

server.R

library(shiny)
library(RMySQL)
library(ggplot2)
#library(ggiraph)
library(lubridate)

##Connect to Redmine db
con <- dbConnect(MySQL(),
                 user = '#',
                 password = '#',
                 host = '#',
                 dbname='#')

tickets<-dbGetQuery(con, "Select * from table")
issues_speed_unique<-unique(na.omit(dbGetQuery(con,"Select * from table2")))
dbDisconnect (con) 

some aggregations....

shinyServer(
  function(input,output){

ui.R

library(shiny)
library(ggplot2)
#library(ggiraph)
#library(htmltools)
library(lubridate)

shinyUI(fluidPage(

You could try to read data from a cache file named with the system date, and if not existing then create it from the query. But the first user of the day will be slower. — HubertL, Dec 09 '16 at 10:35
Thanks @HubertL , sounds good. I've been looking how to create a cache file, but don't seem to find any hint on how to do it (that I can understand). Could you please suggest something? — adlisval, Dec 09 '16 at 12:51

score 0 · Answer 1 · answered Dec 09 '16 at 15:05

Replace beginning code in server.R with this adaptation of code I found there:

filename <- paste0(Sys.Date(),'.RData')
if (!file.exists(filename)){
    # don't have a cached copy so run the query
    library(RMySQL)
    con <- dbConnect(MySQL(),
                     user = '#',
                     password = '#',
                     host = '#',
                     dbname='#')

    tickets<-dbGetQuery(con, "Select * from table")
    issues_speed_unique<-unique(na.omit(dbGetQuery(con,"Select * from table2")))
    dbDisconnect (con) 

    # save the query results for the future
   save(list=c('tickets', 'issues_speed_unique'), file=filename)
   rm(list=c('tickets', 'issues_speed_unique') )
}
load(file=filename)

Shiny server - schedule mysql query instead of running everytime the app is loaded?

1 Answers1