Any recommendation of R packages or ways to write large (over 10 million rows, 10 columns) of R dataframe to a SQL Server database table. Thanks.
Add a comment
|
1 Answer
By writing the data to a CSV locally and then using a BULK INSERT (not readily available as a prebuilt function akin to sqlSave), the data can be written to the MS SQL Server very quickly.
toSQL = data.frame(...);
write.table(toSQL,"C:\\export\\filename.txt",quote=FALSE,sep=",",row.names=FALSE,col.names=FALSE,append=FALSE);
sqlQuery(channel,"BULK
INSERT Yada.dbo.yada
FROM '\\\\<server-that-SQL-server-can-see>\\export\\filename.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\\n'
)");
Also...
Since insert INTO is limited to 1000 rows, you can dbBulkCopy from rsqlserver package.
dbBulkCopy is a DBI extension that interfaces the Microsoft SQL Server popular command-line utility named bcp to quickly bulk copying large files into table. For example:
url = "Server=localhost;Database=TEST_RSQLSERVER;Trusted_Connection=True;"
conn <- dbConnect('SqlServer',url=url)
## I assume the table already exist
dbBulkCopy(conn,name='T_BULKCOPY',value=df,overwrite=TRUE)
dbDisconnect(conn)
4 Comments
waith
I can only use R and SQL Server in my company
ASH
I just modified my original post. I don't have a massive dataframe to test the concepts above, but you can try these ideas. They definitely work on smaller to mid-size datasets. I'm not sure if they scale up or not.
waith
Thanks for the response. I will try the recommendation of writing to csv then dbBulkCopy to database. I was hoping to find a way to "bulk write" R dataframe to sqlServer database table directly without going through csv.
ASH
Ah, that's a great idea. You could save your analysis from R to a CSV file and bulk insert that into SQL Server. I have done it countless times. See the link below for an example of how to do this. blog.sqlauthority.com/2008/02/06/…