<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/did-you-say-sql-server-yes-i-did/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://longhowlam.wordpress.com/2016/12/23/did-you-say-sql-server-yes-i-did/"> R – Longhow Lam's Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<h2><img sizes="(max-width: 589px) 100vw, 589px" data-image-description="" data-orig-file="https://i1.wp.com/longhowlam.files.wordpress.com/2016/12/rsqlserver.png?resize=456%2C299&amp;ssl=1" data-orig-size="689,452" data-image-title="rsqlserver" class="wp-image-2120" data-comments-opened="1" data-recalc-dims="1" data-attachment-id="2120" data-permalink="https://longhowlam.wordpress.com/2016/12/23/did-you-say-sql-server-yes-i-did/rsqlserver/" alt="rsqlserver" data-medium-file="https://i1.wp.com/longhowlam.files.wordpress.com/2016/12/rsqlserver.png?resize=456%2C299&amp;ssl=1?w=300" data-large-file="https://i1.wp.com/longhowlam.files.wordpress.com/2016/12/rsqlserver.png?resize=456%2C299&amp;ssl=1?w=440" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://i1.wp.com/longhowlam.files.wordpress.com/2016/12/rsqlserver.png?resize=456%2C299&ssl=1"></h2>
<h2>Introduction</h2>
<p>My last blog post in 2016 on SQL Server 2016….. Some years ago, I have heard predictions from ‘<em>experts</em>‘ that within a few years Hadoop / Spark systems would take over traditional RDBMS’s like SQL Server. I don’t think that has happened (yet). Moreover, what some people don’t realize is that at least <strong>half of the world still depends on good old SQL Server</strong>. If tomorrow all the Transact stored procedures would somehow magically fail to run anymore, I think our society as we know it would collapse…..</p>
<p><img sizes="(max-width: 452px) 100vw, 452px" data-image-description="" data-orig-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/postapo.png?resize=452%2C269&amp;ssl=1" data-orig-size="481,287" data-image-title="postapo" data-comments-opened="1" class="wp-image-2102" data-recalc-dims="1" data-attachment-id="2102" data-permalink="https://longhowlam.wordpress.com/2016/12/23/did-you-say-sql-server-yes-i-did/postapo/" alt="postapo" data-medium-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/postapo.png?resize=452%2C269&amp;ssl=1?w=300" data-large-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/postapo.png?resize=452%2C269&amp;ssl=1?w=440" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/postapo.png?resize=452%2C269&ssl=1"></p>
<p>OK, I might be exaggerating a little bit. The point is, there are still a lot of companies and use cases out there that are running SQL Server without the need for something else. And now with the integrated <a target="_blank" rel="nofollow" href="http://msdn.microsoft.com/en-us/library/mt604845.aspx"><strong>R services</strong> <strong>in SQL Server 2016</strong></a> that might not be necessary at all<img alt="🙂" class="wp-smiley" src="https://i0.wp.com/s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f642.png?w=456&ssl=1" data-recalc-dims="1"></p>
<h2>Deploying Predictive models created in R</h2>
<p>From a business standpoint, creating a good predictive model and spending time on this, is only useful if you can deploy such a model in a system where the business can make use of the predictions in their <em>‘day-to-day operations’</em>. Otherwise creating a predictive model is just an academic exercise / experiment….</p>
<p>Many predictive models are created in R on a ‘stand-alone’ laptop /server. There are different ways to deploy such models. Among others:</p>
<ul>
<li>Re-build the scoring logic ‘by hand’ in the operational system. I did this in the past, it can be a little bit cumbersome and it’s not what you really want to do. If you do not have much data prep steps and your model is a logistic regression or a single tree, this is doable<img alt="🙂" class="wp-smiley" src="https://i0.wp.com/s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f642.png?w=456&ssl=1" data-recalc-dims="1"></li>
<li>Make use of PMML scoring. The idea is to create a model (in R) transform that to pmml and import the pmml in the operational system where you need the predictions. Unfortunately, not all models are supported and not all systems support importing (the latest version of) PMML</li>
<li>Create API’s (automatically) with technology like for example <a target="_blank" rel="nofollow" href="http://cran.r-project.org/web/packages/AzureML/vignettes/getting_started.html">Azure ML</a>, <a target="_blank" rel="nofollow" href="http://msdn.microsoft.com/en-us/microsoft-r/deployr-about">DeployR</a>, sense.io or openCPU, so that the application that needs the prediction can call the API.</li>
</ul>
<h2>SQL Server 2016 R services</h2>
<p>If your company is running SQL Server (2016) there is an other nice alternative to deploy R models by using the SQL Server R services. At my work at <a target="_blank" rel="nofollow" href="http://www.werkenbijrtl.nl/">RTL Nederland</a> [Oh btw we are <strong>looking for data engineers and data scientists </strong>:-)] we are using this technology to deploy the predictive churn and response models created in R. The process is not difficult; the few steps that are needed are demonstrated below.</p>
<h3><strong>Create any model in R</strong></h3>
<p>I am using an <a target="_blank" rel="nofollow" href="http://github.com/dmlc/xgboost">extreme gradient boosting</a> algorithm to fit a classification model on the <a target="_blank" rel="nofollow" href="http://github.com/paulhendricks/titanic">titanic data set</a>. Instead of calling xgboost directly I am using the <a target="_blank" rel="nofollow" href="http://mlr-org.github.io/mlr-tutorial/release/html/">mlr package</a> to train the model. Mlr provides a unified interface to machine learning in R, it takes care of some of the frequently used steps in creating a predictive model regardless of the underlying machine learning algorithm. So your code can become very compact and uniform.</p>
<p><img sizes="(max-width: 542px) 100vw, 542px" data-image-description="" data-orig-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/xgboostexample.png?resize=456%2C366&amp;ssl=1" data-orig-size="807,648" data-image-title="xgboostexample" data-comments-opened="1" class="wp-image-2105" data-recalc-dims="1" data-attachment-id="2105" data-permalink="https://longhowlam.wordpress.com/2016/12/23/did-you-say-sql-server-yes-i-did/xgboostexample/" alt="xgboostexample" data-medium-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/xgboostexample.png?resize=456%2C366&amp;ssl=1?w=300" data-large-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/xgboostexample.png?resize=456%2C366&amp;ssl=1?w=440" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/xgboostexample.png?resize=456%2C366&ssl=1"></p>
<h3><strong>Push the (xgboost) predictive model to SQL Server</strong></h3>
<p>Once you are satisfied with the predictive model (on your R laptop), you need to bring that model over to SQL Server so that you can use it there. This consists of the following steps:</p>
<p><strong>SQL Code in SQL Server, </strong>write a stored procedure in SQL server that can accept a predictive R model, some meta data and saves that into a table in SQL Server.</p>
<p><img sizes="(max-width: 565px) 100vw, 565px" data-image-description="" data-orig-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/sqlr_sp.png?resize=456%2C366&amp;ssl=1" data-orig-size="1056,849" data-image-title="sqlr_sp" data-comments-opened="1" class="wp-image-2110" data-recalc-dims="1" data-attachment-id="2110" data-permalink="https://longhowlam.wordpress.com/2016/12/23/did-you-say-sql-server-yes-i-did/sqlr_sp/" alt="sqlr_sp" data-medium-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/sqlr_sp.png?resize=456%2C366&amp;ssl=1?w=300" data-large-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/sqlr_sp.png?resize=456%2C366&amp;ssl=1?w=440" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/sqlr_sp.png?resize=456%2C366&ssl=1"></p>
<p>This stored procedure can then be called from your R session.</p>
<p><strong>Bring the model from R to SQL, </strong>to make it a little bit easier you can write a small <em>helper function.</em></p>
<p><img sizes="(max-width: 582px) 100vw, 582px" data-image-description="" data-orig-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/rhelper.png?resize=456%2C302&amp;ssl=1" data-orig-size="857,567" data-image-title="rhelper" data-comments-opened="1" class="wp-image-2100" data-recalc-dims="1" data-attachment-id="2100" data-permalink="https://longhowlam.wordpress.com/2016/12/23/did-you-say-sql-server-yes-i-did/rhelper/" alt="rhelper" data-medium-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/rhelper.png?resize=456%2C302&amp;ssl=1?w=300" data-large-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/rhelper.png?resize=456%2C302&amp;ssl=1?w=440" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/rhelper.png?resize=456%2C302&ssl=1"></p>
<p>So what is the result? In SQL Server I now have a table (dbo.R_Models) with predictive models. My xgboost model to predict the survival on the Titanic is now added as an extra row. Such a table becomes like a sort of model store in SQL server.</p>
<p><img sizes="(max-width: 577px) 100vw, 577px" data-image-description="" data-orig-file="https://i0.wp.com/longhowlam.files.wordpress.com/2016/12/sqlmodels.png?resize=456%2C181&amp;ssl=1" data-orig-size="985,392" data-image-title="sqlmodels" data-comments-opened="1" class="wp-image-2090" data-recalc-dims="1" data-attachment-id="2090" data-permalink="https://longhowlam.wordpress.com/2016/12/23/did-you-say-sql-server-yes-i-did/sqlmodels/" alt="sqlmodels" data-medium-file="https://i0.wp.com/longhowlam.files.wordpress.com/2016/12/sqlmodels.png?resize=456%2C181&amp;ssl=1?w=300" data-large-file="https://i0.wp.com/longhowlam.files.wordpress.com/2016/12/sqlmodels.png?resize=456%2C181&amp;ssl=1?w=440" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://i0.wp.com/longhowlam.files.wordpress.com/2016/12/sqlmodels.png?resize=456%2C181&ssl=1"></p>
<h3><strong>Apply the predictive model in SQL Server.</strong></h3>
<p>Now that we have a model we can use it to calculate model scores on data in SQL Server. With the new R services in SQL Server 2016 there is a function called <a target="_blank" rel="nofollow" href="http://msdn.microsoft.com/en-us/library/mt604368.aspx"><strong>sp_exec_external_script</strong></a><strong>. </strong>In this function you can call R to calculate model scores.</p>
<p><img sizes="(max-width: 526px) 100vw, 526px" data-image-description="" data-orig-file="https://i1.wp.com/longhowlam.files.wordpress.com/2016/12/sqlserver_rmodel_call.png?resize=456%2C384&amp;ssl=1" data-orig-size="747,628" data-image-title="sqlserver_rmodel_call" data-comments-opened="1" class="wp-image-2097" data-recalc-dims="1" data-attachment-id="2097" data-permalink="https://longhowlam.wordpress.com/2016/12/23/did-you-say-sql-server-yes-i-did/sqlserver_rmodel_call/" alt="sqlserver_rmodel_call" data-medium-file="https://i1.wp.com/longhowlam.files.wordpress.com/2016/12/sqlserver_rmodel_call.png?resize=456%2C384&amp;ssl=1?w=300" data-large-file="https://i1.wp.com/longhowlam.files.wordpress.com/2016/12/sqlserver_rmodel_call.png?resize=456%2C384&amp;ssl=1?w=440" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://i1.wp.com/longhowlam.files.wordpress.com/2016/12/sqlserver_rmodel_call.png?resize=456%2C384&ssl=1"></p>
<p>The scores (and the inputs) are stored added in a table.</p>
<p><img sizes="(max-width: 449px) 100vw, 449px" data-image-description="" data-orig-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/sqltabel.png?resize=449%2C267&amp;ssl=1" data-orig-size="632,376" data-image-title="sqltabel" data-comments-opened="1" class="wp-image-2094" data-recalc-dims="1" data-attachment-id="2094" data-permalink="https://longhowlam.wordpress.com/2016/12/23/did-you-say-sql-server-yes-i-did/sqltabel/" alt="sqltabel" data-medium-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/sqltabel.png?resize=449%2C267&amp;ssl=1?w=300" data-large-file="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/sqltabel.png?resize=449%2C267&amp;ssl=1?w=440" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://i2.wp.com/longhowlam.files.wordpress.com/2016/12/sqltabel.png?resize=449%2C267&ssl=1"></p>
<p>The code is very generic, instead of xgboost models it works for any model. The scoring can (and should be) be done inside a stored procedure so that scoring can be done at regular intervals or triggered by certain events.</p>
<h2>Conclusion</h2>
<p>Deploying predictive models (that are created in R) in SQL Server has become easy with the new SQL R services. It does not require new technology or specialized data engineers. If your company is already making use of SQL Server then integrated R services are definitely something to look at if you want to deploy predictive models!</p>
<p>Some more examples with code can be found on the <a target="_blank" rel="nofollow" href="http://github.com/Microsoft/SQL-Server-R-Services-Samples">Microsoft GitHub</a> pages.</p>
<p>Cheers, Longhow</p>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/longhowlam.wordpress.com/2086/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/longhowlam.wordpress.com/2086/" border="0"></a> <img alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" border="0" data-recalc-dims="1"></p>
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://longhowlam.wordpress.com/2016/12/23/did-you-say-sql-server-yes-i-did/"> R – Longhow Lam's Blog</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/nb8-f92WayY"></a></div>