Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Managing Packages in R Services


By:   |   Read Comments   |   Related Tips: More > 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:

All Packages list for R Services

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:

All Packages folder list for R Services

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.

Install R Services Package

Some Considerations When Installing an R Package

  1. Do not use SQL Server to install packages. It will return "Error library folder is not writable" error.
  2. 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.
  3. 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.
  4. 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.

Remove R Services Package

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

Find Package in R Services
Next Steps


Last Update:






About the author
MSSQLTips author Vitor Montalvao 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.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools