Managing Packages in R Services

By: Vitor Montalvao | Updated: 2017-04-14 | Comments | Related: > SQL Server 2016

Problem

In my tip Setup R Services for SQL Server 2016, I explained that Microsoft purchased Revolution Analytics and that they were able to incorporate R inside of SQL Server (R Services). R Services includes a complete distribution of the base R packages, documentation and tools that are installed in the provided location during the setup (default location is C:\Program Files\Microsoft SQL Server\MSSQL13.<instance_name>\R_SERVICES). Within many libraries it has also included ScaleR APIs, an algorithm library with a set of R functions developed by Revolution Analytics that will let you perform a wide range of data analytics functions.

R is a collection of libraries and being an open source, the number of available libraries grows every day. As said before, R Services installs a base package, but allows you to add more as needed. In this tip I will show how to manage packages in R Services.

Solution

Retrieving installed packages

Let start with the most simple, which is to get all installed packages in our R Services instance. The next script will return the installed packages:

EXECUTE [sys].[sp_execute_external_script] 
 @language = N'R'
 ,@script = N'AllPackages <- as.data.frame(installed.packages())
 OutputDataSet <- AllPackages[c("Package", "LibPath", "Version", "Priority", "Depends", "Imports", "LinkingTo", "Suggests", "Enhances", "OS_type", "License", "Built")]'
WITH RESULT SETS((PackageName NVARCHAR (100), 
  LibPath NVARCHAR (255), 
  Version NVARCHAR (20), 
  Priority NVARCHAR (20), 
  Depends NVARCHAR (255), 
  Imports NVARCHAR (100), 
  LinkingTo NVARCHAR (100), 
  Suggests NVARCHAR (100), 
  Enhances NVARCHAR (100), 
  OS_type NVARCHAR (100), 
  License NVARCHAR (100),
  Built NVARCHAR (100)))

NOTE: Read my tip about sys.sp_execute_external_script in case you did not understand the above command.

The previous command should return something similar to the following image:

The above list should match the packages installed in the LibPath column (default path is C:\Program Files\Microsoft SQL Server\MSSQL13.<instance_name>\R_SERVICES\library) and currently for R Services it is the only allowed package library:

Install a R Package

When trying to run a function from a non existing package it will throw the following error:"Error in library(<library_name>) : there is no package called '<library_name>'". The next example shows a failed attempt to use jsonlite library:

Error No Package Installed for R Services

To install a R library it should be as easy as the below code:

install.packages("jsonlite")

But if the server where the R Services is installed does not have access to the Internet you should manually download the package and have it copied to the server or to a share the computer has access to. Be sure that you download the Windows version of the package (usually a .zip file) and also that the package version is supported by your R version.

Use R.exe to install a package. You can find R.exe in the R Services folder (default path is C:\Program Files\Microsoft SQL Server\MSSQL13.<instance_name>\R_SERVICES\bin\R.exe). Run the following command to install the required package:

install.packages("<path>\\<file_name>", repos=NULL)

You can see the actual command below that I used. With this the package is successfully installed.

Some Considerations When Installing an R Package

Do not use SQL Server to install packages. It will return "Error library folder is not writable" error.
Install the package in the default library location otherwise when trying to run R code in R Services it will throw a "there is no package called..." error.
Be sure that you download the Windows version of the package (usually a .zip file). When installing a package for a non-Windows platform it will return a "compilation failed for package" error.
Also be sure to download the right Windows version of the package that is supported by your R version, otherwise it will be installed and you may get a "LoadLibrary failure" error when using the library.

If you are facing one of the above errors when installing R packages, read my article about how to solve these common issues with packages in R Services.

Uninstall an R Package

As with installing a package, also do not use SQL Server to uninstall packages. It will not return an error, but the package will not be removed.

Use R.exe to uninstall a package. You can find R.exe in the R Services folder (default path is C:\Program Files\Microsoft SQL Server\MSSQL13.<instance_name>\R_SERVICES\bin\R.exe). Run the following command to uninstall the desired package:

remove.packages("<library_name>",.libPaths())

See the actual command I ran below.

It will not return any message, but you can confirm the package has been removed by running the find.package command as follows.

Next Steps

Check out some of these other R tips
- Common Issues with R Services Packages
- Setup R Services for SQL Server 2016

About the author

Vitor Montalvão is a SQL Server engineer with 20 years of experience in SQL Server, specializing in performance & tuning, data modelling, migration and security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2017-04-14