Installing Packages in SQL Server R Services

As you may already know In-database Analytics (also known as Advanced Analytics) is available in SQL Server 2016. To simplify, “In-database Advanced Analytics”: you can run powerful statistical / predictive modelling (from R) inside SQL Server. Read the official definition here.

Check Out SQL Latest Bits

• SQL Server 2016 RC3: this includes SQL Server R Services that you can install.
• SQL Server 2016 CTP 3 Sample: provides sample databases and guidance on how to best explore the new features.
• R Tools for Visual Studio: tools for developing R script in Visual Studio. This is an alternative to R Studio.

If you’re new to R and SQL Server R Service, my recommendation is to go through the Getting Started.docx document that comes in SQLServer2016CTP3Samples.zip (unzipped path “SQLServer2016CTP3Samples\Advanced Analytics\Getting Started.docx”) from SQL Server 2016 CTP 3 Sample. Please note that it is possible to have more than one R Servers running on the same machine – e.g one from Microsoft R Open and another one from SQL Server R Services.

Watch Out for Missing Package Issue

When you start playing with R in SQL Server, sooner or later you would need to install some packages, for example ggplot2. You may run into a problem that sounds like this “Error in library(“ggplot2”) : there is no package called ‘ggplot2’“.

The following script is used in the iris_demo.sql (SQLServer2016CTP3Samples\Advanced Analytics\iris_demo.sql), and would cause a missing library error if you don’t have the packages installed on SQL Server R Services yet.

USE [sqlr]
GO
EXECUTE [dbo].[get_iris_plot1]

The error indicating a package has not been installed in SQL Server R Services looks similar to the following.

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

Error in ScaleR.  Check the output for more information.
Error in eval(expr, envir, enclos) :
Error in ScaleR.  Check the output for more information.
Calls: source -> withVisible -> eval -> eval -> .Call
Execution halted
Msg 11536, Level 16, State 1, Procedure get_iris_plot1, Line 5 [Batch Start Line 33]
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

Installing R Packages

The Getting Started document says that you will need to run installpackages.r. You can open this on Visual Studio on Run As Administrator mode (after installing R Tools for Visual Studio) and run it.

The trick is to ensure that the R Tools is running on the SQL Server R Services.

1. To do this, go to Visual Studio > R Tools > Options.

   

2. A dialog box similar to the following will open. Navigate to R Tools > Advanced > R Engine. The default location of SQL Server R Services is here: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES. Please update it with the relevant path to your R_SERVICES directory.

   
3. After correcting the R Engine path to SQL Server R Services, click OK.
4. You can now re run installpackages.r. Place the cursor on the line you want to run and press Ctrl + Enter.

Then you can go back to SSMS to run that EXECUTE [dbo].[get_iris_plot1]. It will return a VARBINARY data representing the image.

Or, use Report Builder and open up the iris_plots.rdl from the sample folder where you can render R plots just like below.

Now if you have any other packages that you want to use in your R script inside SQL Server, you can install them on SQL Server R Services first using R Tools in Visual Studio.

The Rogue Way

If you have more than one R server, you can also install the packages you need in one server and copy them across to the other R server directories. This is obviously not the official way … But a trick that I learned from my good friend, Tomaž Kaštrun (b | t) – an R (and SQL Server) expert.

Below is a screenshot of where the libraries on Microsoft R Open (MRO) sit with all packages installed.

R and Visualization

Now that you know how to set it up, join Tomaž Kaštrun and I in 24 Hours of PASS Evolution of the Data Platform next week for 5 Techniques to Beautiful Data Insights with R and SSRS on May 25, 2016 at 4PM GMT (9AM PDT). Don’t forget to register for it and other interesting sessions!

Wrap Up

Use Adventure Works SQL Server 2016 CTP 3 to try out different Hero features in SQL Server 2016. Check out R to manipulate data or to generate plots. Don’t forget to install the right packages first on the SQL Server R Services.

Further Reading

SQL Server 2016 R Services: Guide for Server Configuration by Koen Verbeeck

Introducing Microsoft R Server by Henk Vandervalk