make a query and data frame reactive and refresh it on every 5 minutes

Question

//
library(plyr)
library(shiny)
library(ggplot2)
library(scales)
library(shinydashboard)
library(gridExtra)
library(DT)
library(ggthemes)
library(plotly)
library(data.table)
library(plotrix)
library(shinyjs)
library(shinycssloaders)

# connection with dash db
shinyServer(function(input, output, session) {

  # withProgress(message = 'Data Downloading',
  #              detail = 'This may take a while...', value = 0, {
  #                for (i in 1:15) {
  #                  incProgress(1/15)
  #                  Sys.sleep(10)
  #                }})

  dsn_driver = ""
  dsn_database = ""            # e.g. "BLUDB"
  dsn_hostname = "" # e.g.: "awh-yp-small03.services.dal.bluemix.net"
  dsn_port = "50000"                # e.g. "50000"
  dsn_protocol = "TCPIP"            # i.e. "TCPIP"
  dsn_uid = ""        # e.g. "dash104434"
  dsn_pwd = ""
  jcc = JDBC("com.ibm.db2.jcc.DB2Driver", "db2jcc4.jar");
  jdbc_path = paste("jdbc:db2://",  dsn_hostname, ":", dsn_port, "/", dsn_database, sep="");
  conn = dbConnect(jcc, jdbc_path, user=dsn_uid, password=dsn_pwd)

I want to make this query to be updated on every 5 min

query="select RETAIL_STORE.STR_NM as STR_NM,year(RETAIL_STR_SALES_DETAIL.SALE_DATE) as YEAR,month(retail_str_sales_detail.sale_date) as Monthnumber,
  monthname(RETAIL_STR_SALES_DETAIL.SALE_DATE) AS MONTHNAME,WEEK(RETAIL_STR_SALES_DETAIL.SALE_DATE) AS WEEKNAME
  ,RETAIL_STR_SALES_DETAIL.prod_id
  ,RETAIL_STR_SALES_DETAIL.PROD_NM as PROD_NM 
  ,retail_store_area_wise.area_name AS Area_Name
  ,SUM(RETAIL_STR_SALES_DETAIL.qty) AS QTY
  ,round(sum(RETAIL_STR_SALES_DETAIL.total),2) as TOTAL
  ,RETAIL_STORE_PRODUCT_HEMAS.MFG as MFG
  from RETAIL_STORE_PRODUCT_HEMAS
  INNER JOIN RETAIL_STR_SALES_DETAIL ON RETAIL_STORE_PRODUCT_HEMAS.prod_id = RETAIL_STR_SALES_DETAIL.prod_id
  INNER JOIN retail_dstr_prod ON retail_dstr_prod.prod_id = RETAIL_STR_SALES_DETAIL.prod_id
  INNER JOIN retail_store ON retail_store.store_id = RETAIL_STR_SALES_DETAIL.store_id
  INNER JOIN retail_store_area_wise ON retail_store_area_wise.store_id = RETAIL_STR_SALES_DETAIL.store_id
  where retail_dstr_prod.dstr_id='1495220190'
  group by RETAIL_STORE.STR_NM,RETAIL_STR_SALES_DETAIL.SALE_DATE
    ,year(RETAIL_STR_SALES_DETAIL.SALE_DATE)
    , monthname(RETAIL_STR_SALES_DETAIL.SALE_DATE)
    , RETAIL_STR_SALES_DETAIL.prod_id
    , RETAIL_STR_SALES_DETAIL.PROD_NM
    , retail_store_area_wise.area_name
    , RETAIL_STORE_PRODUCT_HEMAS.MFG 
    , RETAIL_STR_SALES_DETAIL.store_id
    , retail_store.store_id, WEEK(RETAIL_STR_SALES_DETAIL.SALE_DATE)
  ORDER BY year(RETAIL_STR_SALES_DETAIL.SALE_DATE),month(retail_str_sales_detail.sale_date),WEEK(RETAIL_STR_SALES_DETAIL.SALE_DATE)";
  rs=dbSendQuery(conn,query)   
  query1 <- fetch(rs, -1)

and also refresh the data frame with query

biz=data.frame(

    year=query1$YEAR,
    ProdNm=query1$PROD_NM,
    Total = as.numeric(as.character(query1$TOTAL)),
    Sold_that_day = query1$QTY,
    Month = query1$MONTHNAME,
    Weekand= query1$WEEKNAME,
    AreaName=query1$AREA_NAME,
    Manufacturer=query1$MFG,
    stringsAsFactors = FALSE
  )


  # Total sales By year In  2017 #


    totalsales="select year(RETAIL_STR_SALES_DETAIL.SALE_DATE) as YEAR,
      monthname(RETAIL_STR_SALES_DETAIL.SALE_DATE) AS MONTHNAME
      ,round(sum(RETAIL_STR_SALES_DETAIL.total),2) as TOTAL

      from retail_str_sales_detail where year(RETAIL_STR_SALES_DETAIL.SALE_DATE)='2017'
      group by year(RETAIL_STR_SALES_DETAIL.SALE_DATE),
      monthname(RETAIL_STR_SALES_DETAIL.SALE_DATE)";


      totalsalesbyyear <- fetch(dbSendQuery(conn,totalsales), -1)



          bizmonthly=data.frame(

                MonthName=factor(totalsalesbyyear$MONTHNAME,levels = month.name),
                Year=totalsalesbyyear$YEAR,
                MonthTotal=as.numeric(as.character(totalsalesbyyear$TOTAL))
              )

              print(bizmonthly)

Please read: [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) and also [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) — waka, Dec 18 '17 at 14:02

Pork Chop · Accepted Answer · 2017-12-18T09:17:20.837

Something like this should do the trick. Note that it will update globally once every 5 mins so its not going to fire on every session. The time checking is every 10 seconds as per reactiveTimer. Make sure you access the data for biz via biz()

library(shiny)

autoInvalidate <- reactiveTimer(10000,session = NULL)
Getupdates <- function(qfrequency){
  rs <- dbSendQuery(conn,query)   
  if(!exists("nextCall")){
    message("Initiating")
    query1 <<- fetch(rs, -1)
    nextCall <<- Sys.time() + qfrequency
    message("Got Initial Data")
  }
  else if (Sys.time() >= nextCall){
    message(paste0(Sys.time(), " Querying Periodically"))
    query1 <<- fetch(rs, -1)
    nextCall <<- Sys.time() + qfrequency
  }
  else{
    return()
  }
}

ui <- fluidPage(tableOutput("table"))

server <- function(input, output, session) {
  observe({
    autoInvalidate()
    # 300 is 5 mins
    Getupdates(300)
  })

  biz <- reactive({
    bizdata <- data.frame(
      year=query1$YEAR,
      ProdNm=query1$PROD_NM,
      Total = as.numeric(as.character(query1$TOTAL)),
      Sold_that_day = query1$QTY,
      Month = query1$MONTHNAME,
      Weekand= query1$WEEKNAME,
      AreaName=query1$AREA_NAME,
      Manufacturer=query1$MFG,
      stringsAsFactors = F
    )
    bizdata
  })

  output$table <- renderTable({biz()})
}

shinyApp(ui, server)

thanks for your answer but it is going to make my query dynamic — ROHIT JHA, Dec 18 '17 at 09:14
I have edited the answer so you have `query1` variable. You can add your other queries in there too with `<<-` operator — Pork Chop, Dec 18 '17 at 09:17

make a query and data frame reactive and refresh it on every 5 minutes

1 Answers1

Linked