CODEWITHSUNDEEP
sqlrsql-server
Max
Max

Reputation: 725

Exporting R dataframe to SQL

I am trying to export an R dataframe (that I had previously created using a SQL query) to SQL in order to perform a merge (JOIN) with data tables on a SQL server.

Specifically, if mydat is a dataframe in R, I would like to be able to do something along the following lines

library('RODBC')
edw = odbcConnect(<some sql server>)
new_mat = sqlQuery(edw,"SELECT
        db.code1
        db.code2
        FROM mydat as a
        JOIN SQLServer_Tables.Table1 as fd on fd.codenew=a.codenew and  fd.codenew2 = a.codenew2q
 "
)

The problem is that I don't know how to get the R data frame "mydat" into SQL as a suitable object. I did find some old stackoverflow threads about exporting R objects (strings, vectors) into SQL, but none of them seem to work on the data frame.

Upvotes: 2

Views: 490

Answers (1)

Arnaud
Arnaud

Reputation: 71

If you are using SQL Server 2016 or later version, with R or Machine Learning service installed, you can execute your R (or Python) code directly on the SQL Server instance with sp_execute_external_script (ms doc)

Example of R code executed on SQL Server:

-- Return 1 to 10
EXEC sp_execute_external_script
   @language =N'R',
   @output_data_1_name =N'myD',
   @script=N'
   myD <- data.frame(c(1:10))
'

Then in order to use the result in JOIN type structure, you can create a table variable and store the result of the R execution code inside.

-- Declare a temporary table:
DECLARE @MY_TBL AS TABLE(X INT, Y1 INT)

-- Store in the table variable the result of the R code execution
INSERT INTO @MY_TBL 
EXEC sp_execute_external_script
  @language =N'R',
  @output_data_1_name =N'myD',
  @script=N'

  myD <- data.frame(c(1:50))
  colnames(myD) <- "X"

  myD$y1 <- rpois(n = 50, lambda = 10)

  '

-- Use the @MY_TBL variable in the rest of your script
SELECT * FROM @MY_TBL

Upvotes: 1

Related Questions