R Script error

[patoduck]

I have an R Script running on SQL 2017 ML R Server (So no memory problem, etc)

PowerBI connects to a file containing a dataset with product number and URL. Upon inspection it will fail with timeout, but 

library(readxl)
library(jpeg)
library(scales)
library(plotrix)
library(gridExtra)
library(dplyr)
library(data.table)

dataset = read_excel("C:/Temp/Products.xlsx", sheet = "Sheet1")

datalist = list()

nRowsDf <- nrow(dataset)

for (i in 1:nRowsDf) {
tryCatch({
#Convert this from Data.frame to Vector
avector <- as.vector(dataset$URL)
varenummer <- as.vector(dataset$Varenr)
Sku <- as.vector(varenummer[[i]])
download.file(avector[[i]], "image.jpg", mode = "wb")
painting <- readJPEG("image.jpg")
dimension <- dim(painting)
painting_rgb <- data.frame(
  x = rep(1:dimension[2], each = dimension[1]),
  y = rep(dimension[1]:1, dimension[2]),
  R = as.vector(painting[,, 1]), #slicing our array into three
  G = as.vector(painting[,, 2]),
  B = as.vector(painting[,, 3])
)

k_means = kmeans(painting_rgb[, c("R", "G", "B")], algorithm = "Lloyd", centers = 6, iter.max = 300)
test = (sapply(rgb(k_means$centers), color.id))

Color = lapply(test, `[[`, 1)
Values = k_means$size
Percentage = k_means$size / sum(k_means$size)
Final = do.call(rbind, Map(data.frame, Color = lapply(test, `[[`, 1), Values = k_means$size, ProductNumber = Sku, Percentage = Percentage))

Final$i <- i # keep track of  iteration 
datalist[[i]] <- Final # add iteration to list

#R = Final[with(Final, order(-Percentage)),]
}, error = function(e) { })
closeAllConnections() 

}

big_data = rbindlist(datalist)

Upon inspection it will fail with timeout, but opening the script is Rstudio o Visual Studio shows the folling error :

Error in file(file, ifelse(append, "a", "w")) : 
cannot open the connection
In addition: Warning message:
In file(file, ifelse(append, "a", "w")) :
cannot open file 'myDF.csv': Too many open files

I have also posted the error in Stackoverflow so if you can solve it you can also get some point overthere 🙂

[patoduck]

It looks like is not possible to use R in windows. The OS will lock itself!

This is an issue with Windows that locked files. The best things is to avoid Windows. For people that need to use Windows there is a solution. I tested it with Windows 7:

• copy the handle.exe (or handle64.exe) from: https://technet.microsoft.com/en-ca/sysinternals/bb896655.aspx?f=255&MSPPError=-2147217396
• open a cmd prompt windwos as administrator
• find the handle of the lock files: handle64.exe -nobanner -p python.exe pattern_of_your_file
• handle64.exe -p pid_your_file -c handle_your_file

[v-jiascu-msft]

Hi @patoduck,

Thanks for sharing with us. Why not run the code in the ML server? We can just use Power BI to retrieve plain data. The R in Power BI focus on data modeling. I would suggest doing unnecessary data manipulation outside of Power BI.

Best Regards,

Dale

[patoduck · Accepted solution]

The issue at the end is not related to Power BI. It is a limitation with R and Windows. It will affect even if you run the code in ML services.

If I find another solution I will post it accordingly.