how to save a sql database in a real time dataframe? shiny

user_13550675 0
Viewed 352

1. The problem: I am working with a sql data base, which is updated both by my shiny app and by another shiny app. I use df=isolate(reactivePoll ()) to be able to subset the dataframe, because reactivePoll generates a function as a result,however isolate() approach has a serious problem: it only updates the dataframe when the shiny app opens, it runs only once.

How can I update the dataframe object in real time and be able to subsett it? without the error: Error in: object of type 'closure' is not subsettable or Warning: Error in .getReactiveEnvironment()$currentContext: Operation not allowed without an active reactive context. (You tried to do something that can only be done from inside a reactive expression or observer.)

the reactivepoll function works very well inside a render function to show tables in real time, for example, but how do I update a data.frame in real time? being able to subset it df$col1 df$col2 etc?

server(input,output,session){
df=reactivePoll(intervalMillis = 2000,session, checkFunc = function(){
   
    QUERY1= "SELECT *  FROM table"
       df_rs = dbSendQuery(storiesDb,QUERY1)
    dbFetch(df_rs)}, 
    valueFunc =function (){
     
      QUERY1= "SELECT *  FROM table"
    
      df_rs = dbSendQuery(storiesDb,QUERY1)
      dbFetch(df_rs)
      
      
      } ) 
dataframe=  isolate(df()) # this approach makes the dataframe update only when the shiny app open.
}

Attempts

1-I already tried to use dataframe=reactive({df ()}) but gives the error when i try to subset dataframe$col1: Error in: object of type 'closure' is not subsettable

2-I already tried to use dataframe=df () but gives the error: Error in .getReactiveEnvironment()$currentContext: Operation not allowed without an active reactive context. (You tried to do something that can only be done from inside a reactive expression or observer.)

shiny r
2 Answers

I think you're probably better off using invalidateLater instead of reactivePoll. The latter is intended to use a

relatively cheap "check" function with a more expensive value retrieval function

In your case, you intend to run your query every 2 seconds, regardless. There is no "check" needed.

Additionally, reactive* functions in shiny tend to be lazy (if nothing depends on it, it will never fire), whereas observe* functions are more greedy (and fire regardless of dependencies).

How about this?

dat <- reactiveValue(NULL)
observe({
  invalidateLater(2000)
  QUERY1 = "SELECT * FROM table"
  ret <- dbGetQuery(storiesDb, QUERY1)
  dat(ret)
})
output$tbl <- renderDataTable({
  dat()
})

You might want to consider only downloading recent data, depending on the schema of that table. For instance, if there is a timestamp (e.g., Created which indicates when the data was inserted into the table), then perhaps you can use something like

dat <- reactiveValue(NULL)
observeEvent(invalidateLater(2000), {
  olddat <- dat()
  latest <- max(c(as.POSIXct("1900-01-01"), olddat$Created), na.rm = TRUE)
  QUERY1 = "SELECT * FROM table t where t.Created > ?"
  newdat <- dbGetQuery(storiesDb, QUERY1, params = list(latest))
  if (NROW(newdat) > 0) {
    newdat <- rbind(olddat, newdat)
    dat(newdat)
  }
})
output$tbl <- renderDataTable({
  dat()
})

Depending on your schema, you may also have a Modified field in the table, where Created indicates when it was first added and Modified indicates when it was last changed. In this case, you'll likely need to check both fields for recency. (Perhaps I'm getting ahead of myself now.)

However, you can use reactivePoll to do a "fast" check function and a "slower" value function, perhaps something like:

df <- reactivePoll(2000, session,
  checkFunc = function() {
    dbGetQuery(storiesDb, "select count(*) as n from table")
  },
  valueFunc = function() {
    dbGetQuery(storiesDb, "select * from table")
  }  
})
output$tbl <- renderDataTable({
  df()
})

This idea (of using valueFunc for counting rows) can also be adapted to take advantage of Created and Modified ... in fact, if you have one or both of those fields, most tables will be index/optimized so that querying for the max(Created) is pretty fast. Granted, getting the row-count of a table is often optimized into a near-immediate query, so you might benefit from some internal benchmarking and/or a discussion with your DBA.

user_33582720

reactivePoll returns a reactive object, so you can only access it in a reactive context. If you want to subset it, you can use the following coding style:

df_subset <- reactive({
  df()[, input$selected_col]
})

This again returns a reactive object, so you could display it with:

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

If you want to make changes in the database upon a change in the df_subset, you can use an observeEvent:

observeEvent(subset_df(), {
  # code to change the DB
})
user_126473150
Related