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:
- Scrape an API and clean/manipulate the data in R.
- 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.
- 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.