<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/publish-r-functions-as-stored-procedures-with-the-sqlrutils-package/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://blog.revolutionanalytics.com/2017/04/sqlrutils.html"> Revolutions</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<p>If you've created an R function (say, a routine to clean up missing values in a data set, or a function to make forecasts using a machine learning model), and you want to make it easy for DBAs to use it, it's now possible to <a target="_blank" rel="nofollow" href="https://docs.microsoft.com/en-us/sql/advanced-analytics/r-services/generating-an-r-stored-procedure-for-r-code-using-the-sqlrutils-package">publish R functions as a SQL Server 2016 stored procedure</a>. The <strong>sqlrutils</strong> package provides tools to convert an existing R function to a stored procedure which can then be executed by anyone with authenticated access to the database — even if they don't know any R.</p>
<p>To use an R function as a stored procedure, you'll need SQL Server 2016 with <a target="_blank" rel="nofollow" href="https://docs.microsoft.com/en-us/sql/advanced-analytics/r-services/sql-server-r-services">R Services</a> installed. You'll also need to use the sqlrutils package to publish the function as a stored procedure: it's included with both <a target="_blank" rel="nofollow" href="https://msdn.microsoft.com/en-us/microsoft-r/r-client-get-started">Microsoft R Client</a> (available free) and <a target="_blank" rel="nofollow" href="https://msdn.microsoft.com/en-us/microsoft-r/rserver">Microsoft R Server</a> (included with SQL Server 2016), version 9.0 or later.</p>
<p>With that set up, the process is fairly simple:</p>
<ul>
<li>Using Microsoft R (Client or Server), call <code>library(sqlrutils)</code> to make the publishing functions available in R.
<ul>
<li>Call <code>help(package=&quot;sqlrutils&quot;)</code> to see the list of functions provided.</li>
</ul>
</li>
<li>Create the R function you want to publish. It should include one <code>data.frame</code> argument (this will be the input from the database) and return a <code>data.frame</code> as the value of the stored procedure.
<ul>
<li>The function can also return NULL, if the goal of the function is to modify the database directly.</li>
<li>You can use functions from the <a target="_blank" rel="nofollow" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler/scaler">RevoScaleR</a> package. For example, use <code><a target="_blank" rel="nofollow" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler/packagehelp/rxsqlserverdata">RxSqlServerData</a></code> to access data in the database with a SQL query, use <code><a target="_blank" rel="nofollow" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler/packagehelp/rxdatastep">RxDataStep</a></code> to transform data using R, or use functions like <code><a target="_blank" rel="nofollow" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler/packagehelp/rxlogit">rxLogit</a></code> to train a predictive model.</li>
<li>Note that the function will only access data passed in as inputs — don't try reaching out to global variables, as they won't be there when the function runs within SQL Server.</li>
</ul>
</li>
<li>Declare the input parameter for the stored procedure with the <code>InputData</code> function. In addition to naming the parameter, you can specify a default query to use if none is provided.</li>
<li>Optionally, define one or more additional input parameters for the stored procedure. These will become inputs to the R function defined above.
<ul>
<li>Only R objects of type POSIXct, numeric, character, integer, logical, and raw are allowed as inputs.</li>
</ul>
</li>
<li>Prepare the R function for publishing with the <code>StoredProcedure</code> function. This is where you name the stored procedure and define the input parameters declared above.</li>
<li>Publish the stored procedure to the database with <code>registerStoredProcedure</code>. 
<ul>
<li>You'll need to provide a connection string with location and authentication information for the database. You can also provide this in the previous step, when you create the stored procedure.</li>
<li>You can test out the stored procedure from your R console with <code>executeStoredProcedure</code>.</li>
</ul>
</li>
</ul>
<p>That's it! Now your R function is available as an ordinary stored procedure, and can be executed (with its provided input data and optional parameters) like any other stored procedure.  </p>
<p>This is just an outline, and I've omitted some of the options for clarity. For more details on the process, you can find complete documentation at the link below.</p>
<p>Microsoft Docs: <a target="_blank" rel="nofollow" href="https://docs.microsoft.com/en-us/sql/advanced-analytics/r-services/generating-an-r-stored-procedure-for-r-code-using-the-sqlrutils-package">Generating an R Stored Procedure for R Code using the sqlrutils Package</a></p>
<p> </p>
</div>
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://blog.revolutionanalytics.com/2017/04/sqlrutils.html"> Revolutions</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/3x0hjGJHZ2U"></a></div>