List Installed R Packages on SQL Server
As data scientiest if you want to get the list of all installed R packages on SQL Server instance, there a a few methods you can choose. In this SQL database R script tutorial, I want to share a few methods you can choose to display installed R package list on your SQL Server database instance. Checking previously installed R packages before you install a new R package on your SQL Server Machine Learning Services may save you from double work.
List R Packages using SQL Server Management Studio
For SQL developers the easiest method to display all R packages as a list is running installed.packages() R command by executing following SQL sp_execute_external_script statement.
EXEC sp_execute_external_script
@language = N'R',
@script = N'OutputDataSet <- as.data.frame( installed.packages() );';
When you execute above SQL sp_execute_external_script external script, all of the R packages installed on that SQL Server Machine Learning Services will be listed as seen in below screenshot.
If you need to get the column names, following SQL script can be executed:
EXEC sp_execute_external_script
@language = N'R',
@script = N'OutputDataSet <- as.data.frame( installed.packages() );'
WITH RESULT SETS (
(
Package nvarchar(max),
LibPath nvarchar(max),
"Version" nvarchar(max),
"Priority" nvarchar(max),
Depends nvarchar(max),
Imports nvarchar(max),
LinkingTo nvarchar(max),
Suggests nvarchar(max),
Enhances nvarchar(max),
License nvarchar(max),
License_is_FOSS nvarchar(max),
License_restricts_use nvarchar(max),
OS_type nvarchar(max),
MD5sum nvarchar(max),
NeedsCompilation nvarchar(max),
Built nvarchar(max)
)
)
As SQL developers can see the R packages installed on current SQL Server list has now column names at the top.
If you want to store the output R packages list in a database table, one of the following methods can be used.
The first way requires a loop back linked server definition.
I mean, you have to create a linked server which is pointing to itself and using the current user's security context.
This method is known as Select from Stored Procedure using OpenQuery
Then following SELECT command can be executed to select data from stored procedure (sp_execute_external_script) and create temp table to store result set in the current database.
SELECT *
INTO ##InstalledRPackagesList
FROM OPENQUERY([.\SQL2019CTP21], '
EXEC sp_execute_external_script
@language = N''R'',
@script = N''OutputDataSet <- as.data.frame( installed.packages() );''
WITH RESULT SETS (
(
Package nvarchar(max),
LibPath nvarchar(max),
"Version" nvarchar(max),
"Priority" nvarchar(max),
Depends nvarchar(max),
Imports nvarchar(max),
LinkingTo nvarchar(max),
Suggests nvarchar(max),
Enhances nvarchar(max),
License nvarchar(max),
License_is_FOSS nvarchar(max),
License_restricts_use nvarchar(max),
OS_type nvarchar(max),
MD5sum nvarchar(max),
NeedsCompilation nvarchar(max),
Built nvarchar(max)
)
)
') as [OpenQuery]
select * from ##InstalledRPackagesList
drop table ##InstalledRPackagesList
Other method to store output of sp_execute_external_script external script procedure is more easy compared with above solution.
Database developers must first create the table on the database to store the installed R packages list data.
This table's field list must match the columns in the WITH RESULTS SET clause.
Here is the SQL code that can be used.
Create Table RPackagesList (
Package nvarchar(max),
LibPath nvarchar(max),
"Version" nvarchar(max),
"Priority" nvarchar(max),
Depends nvarchar(max),
Imports nvarchar(max),
LinkingTo nvarchar(max),
Suggests nvarchar(max),
Enhances nvarchar(max),
License nvarchar(max),
License_is_FOSS nvarchar(max),
License_restricts_use nvarchar(max),
OS_type nvarchar(max),
MD5sum nvarchar(max),
NeedsCompilation nvarchar(max),
Built nvarchar(max)
)
Insert Into RPackagesList
EXEC sp_execute_external_script
@language = N'R',
@script = N'OutputDataSet <- as.data.frame( installed.packages() );'
select * from RPackagesList
If as a data scientist, you are interested only for certain columns then below SQL code and R script can be combined to display desired output
For example, assume that the data scientist is only concerns with package name, version of the R package and the built number. These are the columns in 1st, 3rd and 16th order.
R script code should be modified to return only those columns by using [,c(1,3,16)] column selection option.
Please note, if you are interested all columns between 5th and 10th columns then you can use [,c(5:10)]
Database developers will realize, I have a table with all columns are mapped to the output of the sp_execute_external_script stored procedure.
INSERT INTO command from stored procedure will store output of the R-script into database table
Create Table RPackagesList
(
Package nvarchar(max),
"Version" nvarchar(max),
Built nvarchar(max)
);
Insert Into RPackagesList
EXEC sp_execute_external_script
@language = N'R',
@script = N'
installedRPackages <- data.frame( installed.packages() )
OutputDataSet <- installedRPackages[,c(1,3,16)];';
select * from RPackagesList;
Installed R Packages List on RTerm
Another option for data engineers to get all of R packages installed on a SQL Server instance is using the RTerm, R script terminal tool
Rterm or R for Windows front-end tool is accessible using R.exe executable file in related SQL Server instance's R_SERVICES bin folder
Launch Rterm software by running the R.exe file. When the application is launched, type installed.packages() command on the command line and press Enter.
List R Packages using Library() Command
While using Rterm another option to list all installed R packages on SQL Server is executing the library() R command as seen in below screenshot.
library();
If you test the library() command on your SQL Server 2019 instance you will see that a popup screen will be displayed and the R packages installed on that instance will be listed with the default library path.