03/20/2018
by Marlon Ribunal
1 Comment

Identify R Package Dependencies Using miniCRAN

Installing R packages in SQL Server 2017 is a breeze with the stored procedure sp_execute_external_script. But so far, it is limited to installing one package at a time. If you have an R Package with dependencies, you have to install those in a separately.

Here’s an easy way to check for package dependencies using the package miniCRAN.

Using RGUI.exe

Find RGUI.exe in C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\bin\x64. Run the executable as an administrator (Right-Click & Select “Run as administrator”).

If you want to add the new package to the default library, you have to tell the installer exactly that by using the lib argument. I previously installed R and its environment in my laptop. Without specifying a library, R would have installed the package in the old default library. See the list of my library paths below (.libPaths())

install.packages("miniCRAN", lib="C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\R_SERVICES\\library")

 

Then you should get a message like this one:

trying URL 'https://mran.microsoft.com/snapshot/2017-05-01/bin/windows/contrib/3.3/miniCRAN_0.2.7.zip'
Content type 'application/zip' length 1025529 bytes (1001 KB)
downloaded 1001 KB

package ‘miniCRAN’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
C:\Users\Marlon.Ribunal\AppData\Local\Temp\Rtmp4UPMso\downloaded_packages

You can find all your library paths with the .libPaths() function.

> .libPaths() [1] "C:/Users/Marlon.Ribunal/Documents/R/win-library/3.3" [2] "C:/Program Files/Microsoft SQL Server/MSSQL14.MSSQLSERVER/R_SERVICES/library"

Then we can double check real quick if the package actually made it:

sp_execute_external_script @language = N'R',
 @script = N'OutputDataSet <- data.frame(installed.packages())';

 

Then

> library("miniCRAN")
> pkg <- "ggplot2"
> pkgDep(pkg)

Results:

 

You may not need all the listed packages. Check the web page for the package you’re installing for the info.

03/13/2018
by Marlon Ribunal
2 Comments

Getting Started With R In SQL Server 2017

Prior to SQL Server 2016, running R on Microsoft platform required an installation of the R Language and its environment and an external IDE to write R codes. Then came the Microsoft R Server and its own flavor of R, the Microsoft R Open. With SQL Server 2016, and now 2017, Microsoft has brought R (and Python) closer to the database engine – SQL Server in this case.

I have a SQL Server 2016 instance that I upgraded to SQL Server 2017 with In-Database Machine Learning. I was not going to reinvent the wheel here, hence the upgrade.

Note: If you’re upgrading SQL Server 2016 Reporting Services to the 2017 version, the SQL Server 2017 RTM installer does include SSRS. It actually uninstalls the service. There is now a separate installer for SSRS 2017. So, you have to plan your SSRS migration prior to the upgrade.

Here are the general steps to install or upgrade an older version of SQL Server, 2016 in this case, to the 2017 version.

Let’s start with the SQL Server Installation Center. Select Upgrade from a previous version of SQL Server because that is what we’re doing here – upgrade from 2016 to 2017.

After  entering the product key, accepting the license terms, and  selecting the instance to upgrade, the installer asks you to confirm the separate migration of the SQL Server Reporting Services

Also, please take note that the SQL Server 2017 RTM installer does not allow changing features. So, it installs both the In-Database Machine Learning Services and the Standalone Machine Learning Server which you can access via Microsoft R Client.

The installer includes Microsoft R Open which is a good thing because you do not have to install and configure it separately.

Configure the instance, Service Accounts, and Full-Text. Check the Summary, then hit Upgrade

 

Before you can run R in SQL Server 2017, you need to enable external script to allow the sp_execute_external_script system stored procedure to run. This is not on by default.

EXEC sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;

Once that is set, let’s execute the obligatory Hello, World! in R + TSQL on SQL Operations Studio.

EXEC sp_execute_external_script

@language =N'R',

@script=N'OutputDataSet<-InputDataSet',

@input_data_1 =N'SELECT ''Hello R From TSQL'''

WITH RESULT SETS (([HelloR] VARCHAR(100) notnull));

GO

Enjoy! I hope to write more about R and Machine Learning in the future posts.

03/06/2018
by Marlon Ribunal
1 Comment

Book Review: SQL Server 2017 Machine Learning Services With R

Disclaimer: I’m one of the Technical Reviewers for this book.

With the rising demand for cloud computing and wide adaptation of cloud-based analytics platforms such as Data Lake Analytics and the likes, the chasm between the traditional Data Professionals and their need for analytics literacy is becoming wider and wider. There seems to be an insurmountable gap between the analytics discipline and the in-house SQL Server database platform pros who are looking to transition to Analytics and Data Science. If you’re a SQL Server pro and want to go after a data science career path, you have to “go out” of the SQL Server path and learn, for example, R or Python.

SQL Server 2016 has changed the data science landscape for the SQL Server platform. SQL Server 2016 R Services provided the data pro a direct path to Analytics and Data Science. It facilitated an easier way to and eliminated the barrier to entry for Data Science. The next version of SQL Server pushed the envelope further. SQL Server 2017 has basically become a database with built-in Artificial Intelligence with its In-database Machine Learning feature. Aside from R, you have another option to use Python. There is much, much more about SQL Server 2017 to be excited about!

If you are looking for a reading material for Machine Learning With R, there is this book published by Packt titled SQL Server 2017 Machine Learning with R: Data Exploration, Modeling and Advanced Analytics (Amazon) written by Tomaž Kaštrun (T | B) and Julie Koesmarno (T | B); and reviewed by Dave Wentzel (B) and yours truly. This book provides essential knowledge to get started with Machine Learning. “It gives you the foundational knowledge and insights to help you understand SQL Server 2017 Machine Learning with R,” the back cover says.

One of the strong features of R is its Visualization. Chapter 4 shows you around Data Exploration and Data Munging. You’ll learn about data frames and dplyr, how to use R with TSQL, integrating R in SSRS and Power BI.

Some statistics is covered in Chapter 5 as well as dataset subsetting and merging. Also covered in the chapter are statistical tests and sampling. Predictive Modeling is covered in Chapter 6.

In Chapter 7, Operationalizing R Code, you can see how R and TSQL work together to train and save a model, then operationalize the model using real-time scoring and native scoring. So, TSQL still plays a big part in Machine Learning; and, you’ll see that all throughout the book.

Chapter 8 covers information about deploying, managing, and monitoring database solutions containing R codes. If you are a SQL Server DBA, the whole Chapter 9, Machine Learning Services with R for DBAs, is for you. It talks about how you can use R to do DBA tasks such as prediction of disk usage. This is an example that you can build upon for more complicated DBA metrics or monitoring tasks. Chapter 10 deals with the extended built-in capabilities of JSON. It also discusses Columnstore and in-memory OLTP.

The book is an easy-to-follow step-by-step guide that will bring you up to speed with Machine Learning with R in SQL Server 2017.