Search code examples
rsql-serverwindowsmicrosoft-r

R Server working with flat file paths

I am trying to load a flat file .csv using fread function in a stored procedure in R (in-database) Server.

Apparently, R Server (in the background) tries to copy this file to a working directory in C:\, but this fails.

First, there is the issue that R cannot read \, but Windows file paths require \. I seem to have overcome this issue (I think), but am not sure.

Has anyone worked with loading flat files from a file path into the R Server engine?

Here is the code:

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[usp_R_FileMap]    Script Date: 7/9/2018 11:25:25 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create PROCEDURE [dbo].[usp_R_FileMap]
(
@FilePath nvarchar(255),
@FileName nvarchar(255),
@FileExtension nvarchar(10)
)
AS

declare @RCode nvarchar(max)

set @FilePath = replace(@FilePath,'\','\\')

set @RCode = N'


##PREPARE ENVIRONMENT##########################################################

usrInputFilePath <- "' + @FilePath + N'"
usrInputFileName <- "' + @FileName + N'"
usrInputFileType <- "' + @FileExtension + N'"

sysPackages <- c("data.table"
                 ,"foreach"
                 ,"reader"
                 ,"stringr"
)

##install / load packages as necessary
for (ii in 1:length(sysPackages)) {
  if (paste0("package:",sysPackages[ii]) %in% search()==F) {library(sysPackages[ii],character.only=T)}##if (!paste0("package:",sysPackages[ii]) %in% search())
}##for (ii in length(sysPackages))


##determine full file paths
vcInputFile <- paste0(usrInputFilePath,ifelse(substring(usrInputFilePath,nchar(usrInputFilePath),nchar(usrInputFilePath))!="\\","\\",""),usrInputFileName,usrInputFileType)
# vcInputFile <- file.path(usrInputFilePath,paste0(ifelse(substring(usrInputFilePath,nchar(usrInputFilePath),nchar(usrInputFilePath))!="\\","\\",""),usrInputFileName,usrInputFileType))




##DETERMINE DELIMITER##########################################################


  vcInputFileDelimiter <- gsub("(Detecting sep ... '')","",grep("Detecting sep",capture.output(fread(vcInputFile,nrows=1,stringsAsFactors=F,verbose=T)),value=T))



print(vcInputFileDelimiter)

'


EXEC sp_execute_external_script
  @language =N'R',
  @script=@RCode;


GO
Solution

  • Beyond the \\ vs \ problem, if you're trying to read a file that's on a network share you're going to be blocked by a Windows firewall rule that was installed with SQL Server R Services.

    If you really want to pull files off the network share from R Services you can disable this rule (Start -> Windows Firewall with Advanced Security -> Outbound Rules -> Block network access for R local user accounts in SQL Server instance MSSQLSERVER -> Right click -> Disable).

    Additionally, R processes run under a local computer account, something like MSSQLSERVER01, and these users would need permissions to access the file on the network share.