Tinkering with Azure SQL Databases and shinyapps.io
I enjoy testing cloud services by putting together small projects that integrate with the R data science toolchain. Compared to other frameworks, there seem to be fewer tutorials that focus on connecting R data products with cloud-based infrastructure. This can be frustrating when you go out looking for information or pre-existing solutions, but it’s also a great because there is a lot of space to contribute new and creative content to the data science community. Join the #radmins “R Admins” community on twitter to find and share R analytic administration materials.
Project-based tinkering is a great way to quickly come up to speed on new cloud computing platforms and technologies. I’ve been tinkering on AWS for a few years, and it remains my platform of choice simply because I have the most experience on it. Lately I’ve shifted focus to learn more about GCP and Azure because it seems like a lot of good stuff is happening on those platforms as well.
My goal for this project was to stand up an Azure SQL Database and connect to it from a Shiny application hosted on shinyapps.io.
Azure offers $200 credit to get you started on the platform. All three of the big cloud platforms (AWS, GCP and Azure) offer some sort of credit, free tier offerings or a combination of the two. The free credit is very nice because it helps give you a sense for the cost of different resources — so you can develop and set expectations accordingly.
Follow the Azure SQL Database Quickstart
I followed this quickstart guide to set up a resource project for Azure SQL Database and server. At a high level, these are the steps detailed in the quickstart:
- Pre-work: Created an Azure Account
- Azure portal > Create a resource > SQL Database
- Create and name your SQL Database
- Select Source: Example Database — AdventureWorksLT
4. Create and name your SQL Server
- Admin login
- Admin password
- Region selection (I chose one called US-East)
5. Configure the SQL Server
- The tutorial suggested ‘Standard’ but I downgraded it to ‘Basic’ since this is just a personal tinkering project
6. Explore the Example AdventureWorksLT Data Source
- Run example queries using the Azure Query editor tool
SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName FROM SalesLT.ProductCategory pc JOIN SalesLT.Product p ON pc.productcategoryid = p.productcategoryid;Connect to the Azure SQL Database from RStudio
Now that the Azure database is up and running, the next thing to do was get access to the database in R locally. I happen to be evaluating RStudio Desktop Pro, which gives me access to free professional drivers, including one for SQLServer.
- RStudio IDE Connections Pane > New Connection > SQLServer
- Download/Install Driver
- Input the Azure SQL Server name, admin user name and database name
Before Testing Connection: Create Server-Level Firewall Rule for Azure SQL
- Add client IP > Save
Finish connecting to the SQL Server database through RStudio:
Once successfully connected to the database, find the database tables in the RStudio IDE connections pane:
Clicking through the connections pane will allow you to explore and preview all the tables available. This is the Azure SQL example database — AdventureWorksLT.
Build and Deploy a Test Shiny Application
I’d like to build a shiny application for exploring the sample SalesLT data tables from the Azure SQL example database. Whenever possible, it is a great idea to make a plan for application deployment from the very beginning of the project development cycle.
Making upfront decisions about where and how to publish the application will inform how I write the database connection code and could save me from needing to do code refactoring in order to satisfy deployment requirements later. As Jeff Geerling says in the book “Ansible for DevOps”:
> When developers begin to think of infrastructure as part of their application, stability and performance become normative.
Project Deployment Plan:
- Publishing Platform: shinyapps.io
- Database Connection Pattern: config package
The config package allows connection code in R to reference an external file that defines values based on the environment. This means that I’ll be able to run the exact same app.R code locally as in my deployment environment. Follow the instructions on db.rstudio.com to set up a config.yml file for value definitions.
Example R / Shiny code for connecting to a database with the config package:
# Shiny App Starter Code:library(DBI)
library(odbc)
library(config)
dw <- get("datawarehouse")
con <- dbConnect(
Driver = dw$driver,
Server = dw$server,
UID = dw$uid,
PWD = dw$pwd,
Port = dw$port,
Database = dw$database
)
- Build a POC “proof of concept” app to test that the database connection will still work in the deployment environment
I used the config package pattern to write a super basic ugly shiny app.
The sole purpose of this first draft app was to test that I could make the database connection to my application work both locally and on shinyapps.io. For this reason, the app executes a hardcoded test example query with `DBI` and has no interactivity whatsoever.
- Docs: shinyapps.io Using the config package
- db.rstudio.com Deploying with the config package
2. Whitelist the required IP addresses from shinyapps.io
To allow connections from shinyapps.io to the Azure SQL Database, there are three IP addresses that need firewall rule updates on the Azure side.
- Docs: shinyapps.io Firewalls
- Azure Quickstart: Firewall Rules
3. Test App Deployment
From the RStudio IDE, I used push button publishing to test deploy the basic application on my shinyapps.io account.
Push button publishing makes use of the open source rsconnect package for R. There will be some initial configuration steps if this is the first time you’re publishing to shinyapps.io.
- Docs: shinyapps.io Creating an Account
- Docs: shinyapps.io Deploying Applications
Here is the Test Application Code:
#
# Shinyapps.io Azure SQL Connection App
#library(shiny)
library(odbc)
library(config)
library(DBI)conn_args <- config::get("dataconnection")con <- dbConnect(odbc::odbc(),
Driver = conn_args$driver,
Server = conn_args$server,
UID = conn_args$uid,
PWD = conn_args$pwd,
Port = conn_args$port,
Database = conn_args$database
)ui <- fluidPage(titlePanel("Shiny + Azure SQL Test"),sidebarLayout(
sidebarPanel(
h4('Create an Azure SQL database in the Azure portal'),
tags$a(href="https://docs.microsoft.com/en-us/azure/sql-database/sql-database-get-started-portal",
"Quickstart: Docs"),
hr(),
h4('SQL Query:'),
tags$code("
SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName
FROM SalesLT.ProductCategory pc
JOIN SalesLT.Product p
ON pc.productcategoryid = p.productcategoryid;
")
),mainPanel(
dataTableOutput("products")
)
)
)server <- function(input, output) {
output$products <- renderDataTable({
dbGetQuery(con,'
SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName
FROM SalesLT.ProductCategory pc
JOIN SalesLT.Product p
ON pc.productcategoryid = p.productcategoryid;
')
})
}# Run the application
shinyApp(ui = ui, server = server)
Build and Deploy the “Real” Shiny Application
Finally — I did some iteration on the test POC app to make it somewhat interesting and actually interactive. This application serves no real purpose to me, so I didn’t spend a great deal of time making it useful, but I did want to end on a nicer looking final data product to illustrate the art of the possible:
Destroy All Evidence of your Tinkering
If this was a tinkering project like mine, now is the time to take your screenshots, commit your code, write up a post-mortem and tear it all down to the ground.
- Head back to the Azure SQL DB Quickstart section on cleaning up
- From Azure portal, find and delete the whole resource group
