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.
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:
To install a R library it should be as easy as the below code:
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:
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:
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.
- Check out some of these other R tips
Last Update: 4/14/2017
About the author
View all my tips