Uploading New R Library Packages

Somehow I have become the R DBA at my job which I don’t mind, I plan on taking Microsoft’s Professional Program on Data Science to be familiar with it.  But recently I’ve had to upload files to our R servers which the first time wasn’t too bad.  Copy these files to six different servers but come the second time around it became apparent that the Predictive Analytics Manger was going to be asking me to do this more frequently than I wanted to to it manually.  So I wrote a quick PowerShell function to take care of this added to our module we use in house.  It unzips the file provided to the correct location.  It does assume you have administrative rights to your server i.e. you can use the admin shares (c$) for example on the server.  You will need to get the function Get-CMSHost from my Running SQL Scripts Against Multiple Servers Using PowerShell post to run the code below.

UPDATE: If you install 9.1 R services on your existing 2016 R server you will end up two paths to your libraries. To fix this we just need to add a TOP 1 to the cmdlet to select the path for the 9.1 libraries.

function Copy-RLibrary() 
{
    <# 
.SYNOPSIS This function unzips the R Library packages to the appropriate location on the servers specified. 

.PARAMETER zipfile R LIbrary zip file to be loaded 

.PARAMETER searchPattern !!NOT INJECTION SAFE!! this parameter simply gets inserted into a wildcard query on the list of available instances on the CMS. This parameter accepts a pipe delimeter list of patterns, allowing you to match instance names on multiple conditions. 

.PARAMETER cleanup Clean up current libraries 

.EXAMPLE This will return all instances that start with 'Pattern' and are on SQL 2016 RTM 

Copy-RLibrary -zipFile '\\share\DemandForecast_Library.zip' -searchPattern 'SQL-RServer' -cleanup $false 

#>
        
    param
    (
        [Parameter(Mandatory=$true)]
        [string]$zipfile,

        [Parameter(Mandatory=$true)]
        [string]$searchPattern,

        [string]$cleanup = $false
    )

    $servers = Get-CmsHosts -searchPattern $searchPattern

    ForEach ( $server in $servers ) 
    {
        $query = @"
        SET NOCOUNT ON

        CREATE TABLE #path
        (path VARCHAR(256))

        INSERT INTO #path
        EXECUTE sp_execute_external_script  @language = N'R'
        , @script = N'OutputDataSet <- data.frame(.libPaths());'

        SELECT TOP 1 '\\$($server)\' + REPLACE(REPLACE(path,'/','\'),':','$') as path
        FROM #path

        DROP TABLE #path
"@
 
 $dt = Invoke-Sqlcmd -Query $query -ServerInstance $server -Database master 
 $path = $dt.path

 if ($cleanup -eq $true)
 {
       Remove-Item ($path)\* -Force -Recurse 
 }

 Expand-Archive -LiteralPath $zipfile -DestinationPath $path -Force
 }
}