It is a late night here in Tampa – 2:04AM EST to be exact. Pups are snoring. Crickets are chirping. Classical music is quietly playing on my little iTunes gadget. Seems like a perfect time to write a short article on one of my favorite SQL Server 2016 enhancements – SQL Server R Services.
R is the most popular programming language for advanced analytics today. You can use it for statistical analysis, uncover patterns, identify trends or even build predictive models. R has a massive collection of free packages on CRAN created by an ever-growing worldwide developer community.
For several years now I have written a variety of articles on how get started with R and how to integrate R with multiple data discovery tools, web applications, Excel and SQL Server Reporting Services. In SQL Server 2016, highly performant R integration is easier than ever before thanks to the acquisition of Revolution Analytics!
What is SQL Server R Services
In the latest SQL Server 2016 CTP3 release, new native in-database support for open source R and multi-threaded RevoScaleR functions are now available via R Services. SQL Server R Services provides a platform for using the powerful R language and package portfolio with a simple Transact-SQL interface. To overcome open source R’s performance and scale limitations, Enterprise Edition includes RevoScaleR features.
Data scientists, BI developers and applications can make parameterized calls to the R runtime from SQL code or stored procedures to get R computed result sets or data visualizations. In SQL Server 2016 CTP3, support for ad-hoc execution of R scripts via a new system stored procedure, sp_execute_external_script, was added. It will currently support pushing data from a single SELECT statement with multiple input parameters to R and return a single data frame as output.
The new R features in SQL Server open up a whole new world of possibilities. R integration with Reporting Services sure has come a long way since I first blogged about it in the Reporting Services R CodePlex project last November. Here is an example collection of Reporting Services R visuals that were pinned to a Power BI dashboard from Smita Parasa. She has more information on this hot topic in her LinkedIn article series.
In SQL Server 2016, R Services is comprised of both server and client components. After you have installed and configured the server components, you can execute R code on your SQL Server data. The server components include:
- Advanced Analytics Extensions: Note that you do need to install and configure this feature during SQL Server setup to enable secure execution of R scripts. You will also have to add permissions to execute external stored procedures on your database. Be sure to check your SQL Server R Services configuration file settings if you plan to use the database server to perform tasks beyond testing R scripting. By default, SQL Server will use most of the memory and does not leave enough memory for larger scale R projects. See docs online post-install tasks for more details.
- Revolution R Enterprise 7.5.0 and Revolution R Open 3.2.2: These downloads are prerequisites for using SQL Server R Services. They include a set of high performance R packages, connectivity tools and the open-source R distribution.
The client components of SQL Server R Services include:
- Revolution R Enterprise 7.5.0
- Revolution R Open 3.2.2
- Any R IDE
Essentially with the client components, R developers author scripts and create predictive models referencing the R libraries using any R IDE such as R Studio. Here are two common scenarios for using R Services in SQL Server 2016.
Why use R Services in SQL Server 2016
A few benefits to using the totally awesome new in-database SQL Server R Services include:
- Familiar user interface: You get to work with your R IDE of choice.
- In-database processing: You can execute R code and have the computations take place in-database on SQL Server. This eliminates the need of having to move data to an external machine running R.
- High performance and scale: By referencing the RevoScaleR package APIs, you are no longer restricted by open source R’s single thread and memory-bound architecture. You can now work with large datasets and multi-threaded, multi-core, multi-process computations.
- Code portability: The same R code that you run against SQL Server data can also be used with other databases and apps.
- More data visualization types and predictive functions for Reporting Services: With SQL Server 2016 R Services you can enjoy ggplot2 and other R graphic libraries in reports.
The Advanced Analytics product team has already published tutorials to help you get started using in-database R with SQL Server.
- How to Call R from a Stored Procedure
- Moving Data between SQL Server and R development environments
- Calling SQL Server from an R Client
The Revolution Analytics team also has a fabulous blog. Since R is coming to Power BI Desktop, (UPDATE: 11/20/2015: R is now in Power BI Desktop). I will be posting more articles here too. You know I can’t help myself when it comes to irresistible analytics candy despite my recent diet roller-coaster.