<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/handling-required-and-missing-r-packages-in-microsoft-r-services/"></div></div><div><a data-url="https://www.r-bloggers.com/handling-required-and-missing-r-packages-in-microsoft-r-services/" data-counturl="https://www.r-bloggers.com/handling-required-and-missing-r-packages-in-microsoft-r-services/" data-count="horizontal" data-text="Handling required and missing R packages in Microsoft R Services" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://tomaztsql.wordpress.com/2016/08/11/handling-required-and-missing-r-packages-in-microsoft-r-services/"> R – TomazTsql</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I have seen several time, that execute R code using procedure sp_execute_external_script was not valid due to missing library or library dependencies.</p>
<p>Problem is – in general – not solved out of the box. But can be solved using and maintaining a list of installed libraries used by Microsoft R services or by simply create a “pre-code” R code to do a check for you.</p>
<p>In both cases, user will end up with additional code, but it might be a good check if you are installing library in production and you run such check prior to running any relevant R code.</p>
<p>Let’s start with simple R code:</p>
<pre>USE WideWorldImporters;
GO

EXECUTE sp_execute_external_script    
           @language = N'R'
          ,@script=N'library(Hmisc) 
                    df &lt;- data.frame(rcorr(as.matrix(sp_RStats_query), 
                            type=&quot;pearson&quot;)$P)
                    OutputDataSet&lt;-df'
          ,@input_data_1 = N'SELECT 
                     SupplierID
                    ,UnitPackageID
                    ,OuterPackageID
                FROM [Warehouse].[StockItems]'
          ,@input_data_1_name = N'sp_RStats_query'
    WITH RESULT SETS ((SupplierID NVARCHAR(200)
                    ,UnitPackageID NVARCHAR(200)
                    ,OuterPackageID NVARCHAR(200)));</pre>
<p> </p>
<p>This code will in my case return error message, that R Service is missing a specific library in order to execute R code – in my case library Hmisc.</p>
<p><img alt="Error_installed_library_01.Wide" class="wp-image-1947" src="https://i2.wp.com/tomaztsql.files.wordpress.com/2016/08/error_installed_library_01-wide1.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>So the first step to solve this issue is to declare variable for R script and parametrize  @script parameter for procedure sp_execute_external_script.</p>
<pre>DECLARE <strong>@OutScript</strong> NVARCHAR(MAX) 
SET <strong>@OutScript</strong> =N'library(Hmisc) 
                    df &lt;- data.frame(rcorr(as.matrix(sp_RStats_query), 
                     type=&quot;pearson&quot;)$P)
                    OutputDataSet&lt;-df'

EXECUTE sp_execute_external_script    
           @language = N'R'
          <strong>,@script= @OutScript</strong>
          ,@input_data_1 = N'SELECT 
                     SupplierID
                    ,UnitPackageID
                    ,OuterPackageID
                FROM [Warehouse].[StockItems]'
          ,@input_data_1_name = N'sp_RStats_query'
    WITH RESULT SETS ((
                         SupplierID    NVARCHAR(200)
                        ,UnitPackageID NVARCHAR(200)
                        ,OuterPackageID NVARCHAR(200)
                    ));</pre>
<p>Now we need to do a string search for following patterns:</p>
<ul>
<li>library (c(package1,package2))</li>
<li>library (package1),  library(package2)</li>
<li>library(package1)</li>
</ul>
<p>All these reserved R words denote the need for particular library/libraries installation.</p>
<pre>DECLARE @OutScript NVARCHAR(MAX) 
SET @OutScript =N'library(Hmisc)
                  library(test123) 
                    df &lt;- data.frame(rcorr(as.matrix(sp_RStats_query), 
                      type=&quot;pearson&quot;)$P)
                    OutputDataSet&lt;-df'

/*
***************************************************************
START: CODE FOR CHECKING AND INSTALLING MISSING R LIBRARIES
***************************************************************
*/


DECLARE @Tally TABLE (num TINYINT,R_Code NVARCHAR(MAX))
INSERT INTO @Tally VALUES (1,@OutScript)
DECLARE @libstatement NVARCHAR(MAX)
DECLARE @cmdstatement NVARCHAR(MAX)

;WITH CTE_R(num,R_Code, libname)
AS
(
SELECT
      1 AS num,
      RIGHT(R_Code, LEN(R_Code) - CHARINDEX(')', R_Code, 0)) AS  R_Code, 
      substring(R_Code, CHARINDEX('library(', R_Code, 0) + 0, CHARINDEX(')',
 R_Code, 0) - CHARINDEX('library(', R_Code, 0) + 1) AS libname
FROM @Tally
WHERE  
        CHARINDEX('(', R_Code, 0) > 0 
    AND CHARINDEX('library(',R_Code,0) > 0

UNION ALL

SELECT
     1 AS num,
     RIGHT(R_Code, LEN(R_Code) - CHARINDEX(')', R_Code, 0)) AS  R_Code,
     substring(R_Code, CHARINDEX('library(', R_Code, 0) + 0, CHARINDEX(')', 
R_Code, 0) - CHARINDEX('library(', R_Code, 0) + 1) AS libname
FROM CTE_R
WHERE 
    CHARINDEX('(', R_Code, 0) > 0 
AND CHARINDEX('library(',R_Code,0) > 0

)
, fin AS
(
SELECT TOP 1 stuff((SELECT ' install.packages(''''' + 
REPLACE(REPLACE(REPLACE(c1.libname,'library',''),')',''),'(','') + '''''
           , dependencies = T)'
              FROM CTE_R AS c1 
              WHERE 
                    c1.num = c2.num
              FOR XML PATH (''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'') AS lib_stat
              FROM CTE_R AS c2
)
SELECT 
        @libstatement = lib_stat 
FROM fin

SET @cmdstatement = 'EXEC xp_cmdshell ''&quot;C:\Program Files\Microsoft SQL Server\
MSSQL13.MSSQLSERVER\R_SERVICES\bin\R.EXE&quot; cmd -e ' + @libstatement + ''''
EXEC SP_EXECUTESQL @cmdstatement

/*
***************************************************************
END: CODE FOR CHECKING AND INSTALLING MISSING R LIBRARIES
***************************************************************
*/


EXECUTE sp_execute_external_script    
           @language = N'R'
          ,@script= @OutScript
          ,@input_data_1 = N'SELECT 
                     SupplierID
                    ,UnitPackageID
                    ,OuterPackageID
                FROM [Warehouse].[StockItems]'
          ,@input_data_1_name = N'sp_RStats_query'
    WITH RESULT SETS ((
                         SupplierID    NVARCHAR(200)
                        ,UnitPackageID NVARCHAR(200)
                        ,OuterPackageID NVARCHAR(200)
                    ));</pre>
<p> </p>
<p>Result in this case will be successful with correct R results and sp_execute_external_script will not return error for missing libraries.</p>
<p>I added a “fake” library called test123 for testing purposes if all the libraries will be installed successfully.</p>
<p>At the end the script generated xp_cmdshell command (in one line):</p>
<pre>EXEC xp_cmdshell '&quot;C:\Program Files\Microsoft SQL Server\
MSSQL13.MSSQLSERVER\R_SERVICES\bin\R.EXE&quot; cmd 
-e install.packages(''Hmisc'') install.packages(''test123'')'</pre>
<p>You might also experience the following error in the output of xp_cmdshell command:</p>
<p><img alt="Error_installed_library_02.Wide" class="wp-image-2002" src="https://i2.wp.com/tomaztsql.files.wordpress.com/2016/08/error_installed_library_02-wide.png?resize=456%2C25&ssl=1" data-recalc-dims="1"></p>
<p>In this case, go to the following location and enable write permission for this folder and subfolders.</p>
<p><img alt="Error_installed_library_03.Wide" class="wp-image-2007" src="https://i1.wp.com/tomaztsql.files.wordpress.com/2016/08/error_installed_library_03-wide.png?resize=397%2C358&ssl=1" data-recalc-dims="1"></p>
<p>For the end, you will always find more elegant and easy way to write R code that will inadvertently check the installation (as well as version and dependencies for library) with following R code:</p>
<pre><span>if(!is.element(&quot;Hmisc&quot;, installed.packages()))
{install.packages(&quot;Hmisc&quot;, dependencies = T)
}else{library(&quot;Hmisc&quot;)}</span></pre>
<p>So the original code can simply be changed to:</p>
<pre>USE WideWorldImporters;
GO

EXECUTE sp_execute_external_script    
           @language = N'R'
          ,@script=N'<span>if(!is.element(&quot;Hmisc&quot;, installed.packages()))
{install.packages(&quot;Hmisc&quot;, dependencies = T)
}else{library(&quot;Hmisc&quot;)}</span>
                    df &lt;- data.frame(rcorr(as.matrix(sp_RStats_query), 
 type=&quot;pearson&quot;)$P)
                    OutputDataSet&lt;-df'
          ,@input_data_1 = N'SELECT 
                     SupplierID
                    ,UnitPackageID
                    ,OuterPackageID
                FROM [Warehouse].[StockItems]'
          ,@input_data_1_name = N'sp_RStats_query'
    WITH RESULT SETS ((SupplierID NVARCHAR(200)
                    ,UnitPackageID NVARCHAR(200)
                    ,OuterPackageID NVARCHAR(200)));

</pre>
<p>But unfortunately, one can not always count on the consistence of developer or data scientists or author of the code, that they will always add a simple check for library installation.</p>
<p>Code is available at <a target="_blank" href="https://github.com/tomaztk/handling_required_R_packages">GitHub</a>.</p>
<p>Happy R-TSQLing!</p>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/tomaztsql.wordpress.com/1919/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/tomaztsql.wordpress.com/1919/" 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><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="https://www.r-bloggers.com/handling-required-and-missing-r-packages-in-microsoft-r-services/"></div></div><div><a data-url="https://www.r-bloggers.com/handling-required-and-missing-r-packages-in-microsoft-r-services/" data-counturl="https://www.r-bloggers.com/handling-required-and-missing-r-packages-in-microsoft-r-services/" data-count="vertical" data-text="Handling required and missing R packages in Microsoft R Services" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://tomaztsql.wordpress.com/2016/08/11/handling-required-and-missing-r-packages-in-microsoft-r-services/"> R – TomazTsql</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/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/?s=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/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/QmTmThOGZ-4"></a></div>