بکارگیری زبان R در SQL Server 2016

مایکروسافت امکان جدیدی را برای تحلیل بهتر داده ها در نسخه 2016 بانک اطلاعاتی خود قرار داده است. با این امکان میتوان اسکریپتهای R را بر روی بانکهای اطلاعاتی SQL Server اجرا نمود . دانشمندان علم داده ها دیگر مجبور نیستند برای تحلیلهای آماری داده ها را از SQL Server به R انتقال دهند . برای اطلاعات بیشتر میتوانید متن زیر را مطالعه فرمایید.

In this article I will describe a way to couple SQL Server together with R, and show how we can get a good set of data mining possibilities out of this fusion. First I will introduce R as a statistical / analytical language, then I will show how to get data from and to SQL Server, and lastly I will give a simple example of a data analysis with R.

What is R and what noticeable features does it have

R is an open source software environment which is used for statistical data analysis. All operations are performed in memory, which means that it is very fast and flexible as long as there is enough memory available.

R does not have a storage engine of its own other than the file system, however it uses libraries of drivers to get data from, and send data to, different databases.

It is very modular in that there are many libraries which can be downloaded and used for different purposes. Also, there is a rapidly growing community of developers and data scientists which contribute to the library development and to the methods for exploring data and getting value from it.

Another great feature is that it has built-in graphical capabilities. With R it takes couple of lines of code to import data from a data source and only one line of code to display a plot graph of the data distribution. An example of this graphical representation will be given shortly. Of course, aside from the built-in graphics, there are libraries which are more advanced in data presentation (ggplot2, for example) and there are even libraries which enable interactive data exploration.

For more details on R features and on how to install it, refer to the R Basics article, which was recently published on Simple-talk.

Connecting to SQL Server from R

This part assumes that the reader has already gained some familiarity with the R environment and has the R and RStudio installed.

As mentioned, R does not have its own storage engine, but it relies on other systems to store the analyzed data. In this section we will go through some simple examples on how to couple R with SQL Server’s storage engine and thereby read data from, and write data to, SQL Server.

There are several options to connect to SQL Server from R and several libraries we can use: RODBC, RJDBC, rsqlserver for example. For the purpose of this article, however, we will just use the RODBC package

Let’s get busy and setup our R environment.

In order to get the connectivity to SQL Server working, first we need to install the packages for the connection method and then we need to load the libraries.

To install and load the RODBC package, do the following:

• Open the RStudio console (make sure the R version is at least 3.1.3: If it isn’t, then use the updateR() function)
• Run the following command: install.packages("RODBC")
• Run the following command: library(RODBC)