Install R Packages on SQL Server for Data Scientists

With SQL Server 2017 and SQL Server 2019, SQL database developers can install Machine Learning Services as a separate component on SQL Server instance and develop machine learning applications using R and Python languages which are popular among data scientists. In this SQL Server tutorial, I want to show how database programmers can install R packages on a SQL Server instance.

I assume that you install SQL Server 2017 with In-Database Machine Learning Services for R

After required software is installed, data engineers or data scientists should enable execution of stored procedure sp_execute_external_script

It is possible to test the installation of In-Database Machine Learning Services and execution of external scripts by running following SQL script on a SQL Server database.


EXEC sp_execute_external_script

 @language = N'R',

 @script = N'OutputDataSet <- as.data.frame( "Kodyaz.com" );';

Code

If the installation of machine learning services is not completed, SQL developer probably will get following error message:

Msg 39020, Level 16, State 2, Procedure sp_execute_external_script, Line 1 [Batch Start Line 4]
Feature 'Advanced Analytics Extensions' is not installed. Please consult Books Online for more information on this feature.

If in-database machine learning services is installed but external scripts are disabled (which is the default case), a SQL programmer will get following error message:

Msg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 0]
'sp_execute_external_script' is disabled on this instance of SQL Server. Use sp_configure 'external scripts enabled' to enable it.

If this is the case, then SQL database administrators can enable sp_execute_external_script to run Python or R script on SQL Server

If everything is OK and configured to execute R scripts on your SQL Server instance, first let's try to see whether the target R package or R library is already installed on the current instance or not. To check if an R package is installed, please execute following SQL R script. library command will try to load and attach the add-on packages. In this sample, accelerometry package


EXEC sp_execute_external_script

@language = N'R',

@script = N'library(accelerometry);';

Code

SQL developers or machine learning developers can see that I have tested to load accelerometry package and since the command is completed successfully without any error, I can easily say that the package is already installed on the current SQL Server instance.

load R package using SQL Server sp_execute_sql_script command

If the R package we are concerned is not installed, an error similar to shown below will be displayed after the execution of the SQL R command.
For the sake of this tutorial, I tried to use R package DT which provides a Javascript library DataTables. Please refer to official documentation at https://rstudio.github.io/DT/

SQL Server R script error: there is no package called

Msg 39004, Level 16, State 20, Line 0
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 0
An external script error occurred:
Error in library(DT) : there is no package called 'DT'
Calls: source -> withVisible -> eval -> eval -> library

Error in execution. Check the output for more information.
Error in eval(ei, envir) :
Error in execution. Check the output for more information.
Calls: runScriptFile -> source -> withVisible -> eval -> eval -> .Call
Çalýþtýrma durduruldu

If the SQL Engine error is indicating that "there is no package called ..." then it means this R package is not installed on that SQL Server instance.

Install R Package

Before installing a new R package, it might be useful to check installed R packages on SQL Server so that you can prefer to use a similar package in your R-script or the target package might be already installed and will be listed among installed packages.

To install an R package on a SQL Server In-Database Machine Learning Services, follow below procedure.

On the SQL Server machine, during installation of R Services a file folder is created on root drive. Please check it on the server. Here is how it is on my development computer.

SQL Server R Services folder

In bin directory there is an executable named R.exe. Run this application as administrator (right click on executable file and choose "Run as administrator")

SQL Server 2019 RTerm for Machine Learning Services with R

Rterm or R-Terminal can be used by data scientists to install R packages.
Just type following install.packages() command on the terminal and press Enter to execute it


install.packages("DT")

Code

The R package and all its dependent packages will be installed automatically.

install R package on SQL Server

After installing R package DT is completed, data scientist can test if it can be loaded successfully using following SQL R Script


EXEC sp_execute_external_script
@language = N'R',
@script = N'library(DT);';

Code