Installing R packages in SQL Server using only T-SQL

Installing R packages in SSMS using T-SQL can take some time, especially when you need to switch between R Tools for Visual Studio or R Studio or your favorite R GUI tool (or R CMD). But so far, installing any additional library using sp_execute_external_script is just not working.

--InstallPackage using sp_execute_external_script
EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'install.packages("ggplot2")'
WITH RESULT SETS (( ResultSet VARCHAR(50)));

-- using Download.file command
EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'download.file("https://cran.r-project.org/bin/windows/contrib/
3.4/ggplot2_2.1.0.zip","ggplot2")
                 install.packages("ggplot2", repos = NULL, type = "source")'
WITH RESULT SETS (( ResultSet VARCHAR(50)));

Both executions yield in error messages. Especially the first one would be great way of installation. At this point, I would not find a way of just passing this command to install the package.

Julie Koesmarno made a great post on installing R packages. Please follow this post. Also Microsoft suggests the following way to install R packages on MSDN.

Since I wanted to be able to have packages installed directly from SQL Server Management Studio (SSMS) here is yet another way to do it. I have used  xp_cmdshell to install any additional package for my R (optionally you can set EXECUTE AS USER).

USE WideWorldImporters;
GO
-- enable xp_cmdshell
EXECUTE SP_CONFIGURE 'xp_cmdshell','1';
GO

RECONFIGURE;
GO

EXEC xp_cmdshell '"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\
R_SERVICES\bin\R.EXE" cmd -e install.packages(''tree'')';   GO

in this case I have installed package tree by calling Microsoft R Services with -e switch and adding straight R code. Please double check path to your R Engine.

Results are:

Capture.PNG

It might not be the proposed way, but when completing T-SQL and R code it is just a nice way with little annoyances.  I would still suggest the way Microsoft proposed or Julie described on her blog, but if you feel confident, you can use this method.

Code is available at Github.

Happy R-SQLing!

Advertisements

5 thoughts on “Installing R packages in SQL Server using only T-SQL

  1. I have to say that I prefer installing missing packages the way you describe here. But it’s not just installing missing packages, using this method you can provide more complex “deployment scripts” to a dba, who then will deploy these scripts on test and production environments. INMO – This is much more reallife than using the VSTR way or the way described

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s