Using SQL Server db as data source for R Shiny application

Posit Professional & Hosted Products shinyapps.io
1

I'm new to R Shiny and I would like to host my application on shinyapps.io. My app.R file gets data from my local SQL database, then I use R to manipulate that data for my R Shiny app. When I upload to shinyapps.io I get error code 1 that seems rather ambiguous based on my google searches.

The current workflow for my project is:

  1. Scrape an API and clean/manipulate the data in R.
  2. Using R and the dbWriteTable function, write the cleaned data into my local SQL Express db. Some data is overwritten, some is appended.

Steps 1 & 2 are automated to run either hourly or daily using Windows task scheduler.

  1. Access the data in SQL and store in R for my app using the following code for the various SQL tables. I am not using any SQL queries in R to manipulate the SQL data before it is stored in R.
con <- dbConnect(odbc(),
                 Driver = "SQL Server",
                 Server = "laptop\\SQLEXPRESS",
                 Database = "myDB",
                 Trusted_Connection = "True")

tradingLog <- as.data.frame(tbl(con,"tradingLog"))

The above code is being repeated within the app.R shiny script for different SQL tables to supply the data for my app, but my novice understanding is that having the local database will be a problem when I want to host the app on shiny apps online. The application is fully functional running on my computer but breaks when on shinyapps, so that has led me to believe I would need to host my SQL database.

To accomplish this I think I can use Google Cloud, which I have found some resources on how to import my SQL database. If Google Cloud is a viable option, I would prefer to use it for the project because my application is using RgoogleMaps so I already setup the Google Cloud account. From here I assume I can change the driver and server settings in the dbConnect code above and this may address the shinyapps problem?

Any advice or direction on how address the problem would be appreciated. My goal is to be able to share this project with team members via shinyapps.io while accessing the SQL database I have setup that has the various scraping tasks scheduled which feed new data into the database.

2

When the app is deployed to shinyapps.io, it runs on their servers not in your local machine so it doesn't have access to your local database anymore. To tackle this, you could expose your database to the public internet and point the connection to your public IP or host the database in the cloud as you have mentioned, Google Cloud would be one option among many.

3

Thank you, I was able to write my SQL tables to CSVs that I needed to run the app and then deploy those files with the app at regular intervals using task scheduler. I appreciate your help. This ended up being far simpler given the amount of data I have at the moment for this project.

4

Redeploying is not very efficient, a similar but better solution would be to use Google Sheets, that way you just have to deploy the app once and your scheduled task can just update the Google Sheet.

5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.