Best practice to manage data processing / loading from SQL Server into Shiny app?

[Alejandro]

Hi, I´m considering using Shiny but have some questions about data I/O. Our production environment uses SQL Server mainly.

This confuses me a bit because I come from Power BI. There, the server loads the processed data into the model, so when the model is consumed there is no I/O (no calls to a database to retrieve data, does not matter if it takes 2 hours to refresh, the data it´s already in the model when the user consumes it, so it is fast)

So, back to Shiny, what would be the best practice?

• Should I load the data directly from the SQL Server each time the app launches? (Outside ui and server functions of course).

It is my understanding that when the app "launches" it is "open" in the Shiny server so this code won´t run each time a user consumes the app, it´s already available (similar to Power BI). Is this correct? The code in the app would then have a structure like this:

Table1 <- sqlQuery(conn, "SELECT ... FROM ... WHERE ...")
Table2 <- sqlQuery(conn, "SELECT ... FROM ... WHERE ...")
# Maybe even process the data directly in the SQL query??
processedTable <- Table1 %>%
                    inner_join(...) %>%
                    mutate(...) %>%
                    unite(...) %>%
                    group_by(...) %>%
                    summarise(...)
ui <- ...
server <- ...
shinyApp(ui = ui, server = server)

• But, if heavy data processing is needed (I don't know, imagine a %>% complete(...) call that takes 45 minutes to run), would be better to do this outside app.R? For example, having an R script process and download the data into the Shiny Server as a .csv, feather file or whatever and then simply read the processed data directly into the app?

processedTable <- read_feather(...)
ui <- ...
server <- ...
shinyApp(ui = ui, server = server)

• And what about data refreshes? If an R script process the data outside the app it´s trivial, but if the data is loaded inside the app, should the app be relaunched periodically?

I´m sure this is an easy one for you all
Have a nice day.

[Enuma]

Hi Alejandro.
I am sure that there are many knowledgeable users that can expand on this. But to answer some general questions:

1. You should load you data in the server function. The UI calls/displays elements created in the server.
2. When the app launches, all the content is rendered as long as you don't use some functions/parameters to prevent it
3. If you have reactive elements embeded in your app, these allow you to re-render your content with new input parameters.
4. It is strongly advised to do all the heavy lifting outside the shiny app.
5. I didn't come across of all that many examples of periodically refreshing shiny apps (with this I mean an app that refreshes every 1s for example). It definitely can be done, but I am not sure whether shiny is the best tool for such an app. But I did a limited search on this after I found that there is no use case for this for me personally.

[andresrcs]

@Alejandro

It is my understanding that when the app "launches" it is "open" in the Shiny server so this code won´t run each time a user consumes the app, it´s already available (similar to Power BI). Is this correct?

This depends on the scoping rules, take a look to this article for clarification

Shiny - Scoping rules for Shiny apps

[Alejandro]

@andresrcs

This depends on the scoping rules, take a look to this article for clarification
https://shiny.rstudio.com/articles/scoping.html

Thanks for the link, that confirms what I thought. But is it better to do the data prep inside the Shiny app or script it outside and just load it already prepared? Or it doesn't matter a lot?

And when the underlying data of the SQL Server changes (for my case it´s constantly changing), what is the best approach to update it in the app? Relaunch it?

[Alejandro]

Looking for info I came across this presentation. I think that solves the question.

TLDR: Do it outside the app.

[andresrcs]

Actually it depends on your specific application and requirements, that approach is not going to give you real time data updates, if that is something important for your application then you should use a reactivePoll().

[Alejandro]

@andresrcs

Actually it depends on your specific application and requirements, that approach is not going to give you real time data updates, if that is something important for your application then you should use a reactivePoll() .

Thank you for pointing me to reactivePoll(), didn't know about the function. Looks like a good alternative to the cron approach. I could save the previous MAX( ID ) from a table and update only the new data.