Exporting R dataframe to SQL

Question

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.

You'd need write privileges on the db, I would think, and then write to a table or temp table that you create, using something like `sqlSave`. — joran, May 03 '18 at 19:38
Perhaps I didn't state the question very clearly, but I don't think that I need write privilege. I'm not trying to write the mydat dataframe to the SQL server, I'm just trying to export it from R into SQL as an object that SQL scripts than recognize and intersect with objects that are on the server. — Max, May 03 '18 at 19:53
No, I understood you. The **sqldf** package allows for stuff kind of like what you're describing, but it uses SQLite locally. If you want the join to happen in the db, the data frame has to exist in some fashion on the db. Writing it to a table/temp table is the only way I've ever seen that done from R. — joran, May 03 '18 at 19:55
The most relevant thread that I found on the topic is the one here https://stackoverflow.com/questions/4330072/pass-r-variable-to-rodbcs-sqlquery , does the suggested answer require writing to the SQL server (it didn't work for me, in any case)? — Max, May 03 '18 at 20:29

score 1 · Answer 1 · edited Jun 07 '18 at 04:24

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

Exporting R dataframe to SQL

1 Answers1