R, Azure SQL Server, and Mac OS X

[This article was first published on r – Jonathan Fowler, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

There are a few options out there for those of us who work (mostly or solely) on a Mac and need to access MSSQL databases through R.

 

RODBC and RSqlServer are the two I’ve worked with. RODBC requires some additional workarounds with a Unix ODBC driver; RSqlServer has issues with rJava in OS X High Sierra. I’ve found RODBC to require the least amount of workarounds and frustration. Another wrinkle here is having an Azure SQL Server as the endpoint with SQL Authentication (not Windows Auth).

 

First, you will need to update your Mac with Homebrew then add a few packages. This page discusses how but did not work for me out of the box. There are extra steps.

 

1. Install HomeBrew via Terminal
/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

 

2. Install unixODBC via Terminal
brew update
brew install unixodbc

 

3. Install freeTDS via Terminal

brew install freetds --with-unixodbc

 

4. Add to /usr/local/etc/odbcinst.ini
[MSSQL]
 
Description   = Microsoft SQL Server driver
 
Driver        = /usr/local/Cellar/freetds/0.95.18/lib/libtdsodbc.so

 

5. Add to /usr/local/etc/freetds.conf
[MY_SQL_SERVER] 
host = myazureserver.database.windows.net
 
port = 1433
 
tds version = 7.0

 

6. Add to /usr/local/etc/odbc.ini
[myazureserver]
Driver=/usr/local/lib/libtdsodbc.so
Trace=No
Server=myazureserver.database.windows.net
Port=1433
TDS_Version=8.0
Database=myazuredatabase

 

7. In Terminal, enter (with user and pass replaced by your credentials):
isql -v myazureserver user pass
You should see a success message and a new prompt. See the Connecting section on the page for what it should look like. Type quit to exit that SQL shell.

 

8. If that is successful, you know the system-level configuration is complete. Run the following commands in Terminal to create symbolic links:
ln -vs /usr/local/Cellar/freetds/0.95.18/etc/freetds.conf ~/.freetds.conf
ln -vs /usr/local/Cellar/unixodbc/2.3.2_1/etc/odbc.ini ~/.odbc.ini
ln -vs /usr/local/Cellar/unixodbc/2.3.2_1/etc/odbcinst.ini ~/.odbcinst.ini
9. In R, run this script (after replacing user and pass with your credentials)
# install RODBC package (can comment this out once run)
install.packages("RODBC", type = "source")

# call RODBC package
library(RODBC)

# create a connection
mycon <- odbcConnect("myazureserver", uid="user", pwd="pass")

# see what it looks like:
mycon 

# Select the top 100 records from table dbo.Table and load into dataframe "rs"
rs <- sqlQuery(mycon, "SELECT TOP (100) * FROM dbo.Table")

 

Now you should have a data frame named “rs” with 100 rows of data.

The post R, Azure SQL Server, and Mac OS X appeared first on Jonathan Fowler.

To leave a comment for the author, please follow the link and comment on their blog: r – Jonathan Fowler.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)