Get R package information

Applies to: SQL Server 2016 (13.x) and later Azure SQL Managed Instance

This article describes how to get information about installed R packages on Machine Learning Services on SQL Server and on SQL Server 2019 Big Data Clusters. Example R scripts show you how to list package information such as installation path and version.

Note

Feature capabilities and installation options vary between versions of SQL Server. Use the version selector dropdown to choose the appropriate version of SQL Server.

Default R library location

When you install machine learning with SQL Server, a single package library is created at the instance level for each language that you install. On Windows, the instance library is a secured folder registered with SQL Server.

All script that runs in-database on SQL Server must load functions from the instance library. SQL Server can't access packages installed to other libraries. This applies to remote clients as well: any R script running in the server compute context can only use packages installed in the instance library. To protect server assets, the default instance library can be modified only by a computer administrator.

The default path of the binaries for R is:

C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\R_SERVICES\library

This assumes the default SQL instance, MSSQLSERVER. If SQL Server is installed as a user-defined named instance, the given name is used instead.

Run the following statement to verify the default R package library for the current instance:

EXECUTE sp_execute_external_script  
  @language = N'R',
  @script = N'OutputDataSet <- data.frame(.libPaths());'
WITH RESULT SETS (([DefaultLibraryName] VARCHAR(MAX) NOT NULL));
GO

Component upgrades

By default, R packages are refreshed through service packs and cumulative updates. Additional packages and full version upgrades of core R components are possible only through product upgrades.

List all installed R packages

The following example uses the R function installed.packages() in a Transact-SQL stored procedure to display a list of R packages that have been installed in the R_SERVICES library for the current SQL instance. This script returns package name and version fields in the DESCRIPTION file.

EXECUTE sp_execute_external_script
  @language=N'R',
@script = N'str(OutputDataSet);
packagematrix <- installed.packages();
Name <- packagematrix[,1];
Version <- packagematrix[,3];
OutputDataSet <- data.frame(Name, Version);',
@input_data_1 = N'
  '
WITH RESULT SETS ((PackageName nvarchar(250), PackageVersion nvarchar(max) ))

For more information about the optional and default fields for the R package DESCRIPTION field, see https://cran.r-project.org.

Find a single R package

If you've installed an R package and want to make sure that it's available to a particular SQL Server instance, you can execute a stored procedure to load the package and return messages.

For example, the following statement looks for and loads the glue package, if available. If the package cannot be located or loaded, you get an error.

EXECUTE sp_execute_external_script  
  @language =N'R',
  @script=N'
require("glue")
'

To see more information about the package, view the packageDescription. The following statement returns information for the MicrosoftML package.

EXECUTE sp_execute_external_script
  @language = N'R',
  @script = N'
print(packageDescription("MicrosoftML"))
'

Next steps

• Install new R packages with sqlmlutils