Identify R Package Dependencies Using miniCRAN

Installing R packages in SQL Server 2017 is a breeze with the stored procedure sp_execute_external_script. But so far, it is limited to installing one package at a time. If you have an R Package with dependencies, you have to install those in a separately.

Here’s an easy way to check for package dependencies using the package miniCRAN.

Using RGUI.exe

Find RGUI.exe in C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\bin\x64. Run the executable as an administrator (Right-Click & Select “Run as administrator”).

If you want to add the new package to the default library, you have to tell the installer exactly that by using the lib argument. I previously installed R and its environment in my laptop. Without specifying a library, R would have installed the package in the old default library. See the list of my library paths below (.libPaths())

install.packages("miniCRAN", lib="C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\R_SERVICES\\library")

 

Then you should get a message like this one:

trying URL 'https://mran.microsoft.com/snapshot/2017-05-01/bin/windows/contrib/3.3/miniCRAN_0.2.7.zip'
Content type 'application/zip' length 1025529 bytes (1001 KB)
downloaded 1001 KB

package ‘miniCRAN’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
C:\Users\Marlon.Ribunal\AppData\Local\Temp\Rtmp4UPMso\downloaded_packages

You can find all your library paths with the .libPaths() function.

> .libPaths() [1] "C:/Users/Marlon.Ribunal/Documents/R/win-library/3.3" [2] "C:/Program Files/Microsoft SQL Server/MSSQL14.MSSQLSERVER/R_SERVICES/library"

Then we can double check real quick if the package actually made it:

sp_execute_external_script @language = N'R',
 @script = N'OutputDataSet <- data.frame(installed.packages())';

 

Then

> library("miniCRAN")
> pkg <- "ggplot2"
> pkgDep(pkg)

Results:

 

You may not need all the listed packages. Check the web page for the package you’re installing for the info.