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’“.
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.
- To do this, go to Visual Studio > R Tools > Options.
- 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.
- After correcting the R Engine path to SQL Server R Services, click OK.
- 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.
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!
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.
SQL Server 2016 R Services: Guide for Server Configuration by Koen Verbeeck
Introducing Microsoft R Server by Henk Vandervalk