By: Vitor Montalvao | 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:
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
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
