Wie man schnell data von R zu SQL server exportiert

Die sqlSave-function des Standard-RODBC-Pakets selbst als einzelne INSERT statement (Parameter fast = TRUE ) ist für große datamengen durch nicht minimales Laden schrecklich langsam. Wie würde ich data auf meinen SQL-server mit minimaler logging schreiben, damit es viel schneller schreibt?

Derzeit versucht:

 toSQL = data.frame(...); sqlSave(channel,toSQL,tablename="Table1",rownames=FALSE,colnames=FALSE,safer=FALSE,fast=TRUE); 

Durch das Schreiben der data in eine CSV lokal und dann mit einem BULK INSERT (nicht leicht verfügbar als vorgebaute function ähnlich sqlSave ), können die data in den MS SQL server sehr schnell geschrieben werden.

 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' )"); ( 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' )"); 

SQL server muss über die Berechtigung zum Zugriff auf den Netzwerkordner verfügen, der die CSV-file enthält, oder dieser process wird nicht funktionieren. Während es einige Setup mit verschiedenen permissions (die Netzwerk-Ordner und BULK ADMIN Privilegien, die Belohnung in Geschwindigkeit ist unendlich wertvoller).

Ich bin völlig einverstanden, dass BULK INSERT die richtige Option für alle data ist, die nicht winzig sind . Allerdings, wenn Sie 2-3 Zeilen von zB Debug-Nachricht hinzufügen müssen, ist BULK INSERT ein Overkill zu sein.

Die Antwort auf Ihre Frage wäre eine DBI::dbWriteTable() function. Beispiel unten (Ich verbinde meinen R-Code mit AWS RDS Instanz von MS SQL Express ):

 library(DBI) library(RJDBC) library(tidyverse) # Specify where you driver lives drv <- JDBC( "com.microsoft.sqlserver.jdbc.SQLserverDriver", "c:/R/SQL/sqljdbc42.jar") # Connect to AWS RDS instance conn <- drv %>% dbConnect( host = "jdbc:sqlserver://xxx.ccgqenhjdi18.ap-southeast-2.rds.amazonaws.com", user = "xxx", password = "********", port = 1433, dbname= "qlik") if(0) { # check what the conn object has access to queryResults <- conn %>% dbGetQuery("select * from information_schema.tables") } # Create test data example_data <- data.frame(animal=c("dog", "cat", "sea cucumber", "sea urchin"), feel=c("furry", "furry", "squishy", "spiny"), weight=c(45, 8, 1.1, 0.8)) # Works in 20ms in my case system.time( conn %>% dbWriteTable( "qlik.export.test", example_data ) ) # Let us see if we see the exported results conn %>% dbGetQuery("select * FROM qlik.export.test") # Let's clean the mess and force-close connection at the end of the process conn %>% dbDisconnect() } library(DBI) library(RJDBC) library(tidyverse) # Specify where you driver lives drv <- JDBC( "com.microsoft.sqlserver.jdbc.SQLserverDriver", "c:/R/SQL/sqljdbc42.jar") # Connect to AWS RDS instance conn <- drv %>% dbConnect( host = "jdbc:sqlserver://xxx.ccgqenhjdi18.ap-southeast-2.rds.amazonaws.com", user = "xxx", password = "********", port = 1433, dbname= "qlik") if(0) { # check what the conn object has access to queryResults <- conn %>% dbGetQuery("select * from information_schema.tables") } # Create test data example_data <- data.frame(animal=c("dog", "cat", "sea cucumber", "sea urchin"), feel=c("furry", "furry", "squishy", "spiny"), weight=c(45, 8, 1.1, 0.8)) # Works in 20ms in my case system.time( conn %>% dbWriteTable( "qlik.export.test", example_data ) ) # Let us see if we see the exported results conn %>% dbGetQuery("select * FROM qlik.export.test") # Let's clean the mess and force-close connection at the end of the process conn %>% dbDisconnect() ) library(DBI) library(RJDBC) library(tidyverse) # Specify where you driver lives drv <- JDBC( "com.microsoft.sqlserver.jdbc.SQLserverDriver", "c:/R/SQL/sqljdbc42.jar") # Connect to AWS RDS instance conn <- drv %>% dbConnect( host = "jdbc:sqlserver://xxx.ccgqenhjdi18.ap-southeast-2.rds.amazonaws.com", user = "xxx", password = "********", port = 1433, dbname= "qlik") if(0) { # check what the conn object has access to queryResults <- conn %>% dbGetQuery("select * from information_schema.tables") } # Create test data example_data <- data.frame(animal=c("dog", "cat", "sea cucumber", "sea urchin"), feel=c("furry", "furry", "squishy", "spiny"), weight=c(45, 8, 1.1, 0.8)) # Works in 20ms in my case system.time( conn %>% dbWriteTable( "qlik.export.test", example_data ) ) # Let us see if we see the exported results conn %>% dbGetQuery("select * FROM qlik.export.test") # Let's clean the mess and force-close connection at the end of the process conn %>% dbDisconnect() ) library(DBI) library(RJDBC) library(tidyverse) # Specify where you driver lives drv <- JDBC( "com.microsoft.sqlserver.jdbc.SQLserverDriver", "c:/R/SQL/sqljdbc42.jar") # Connect to AWS RDS instance conn <- drv %>% dbConnect( host = "jdbc:sqlserver://xxx.ccgqenhjdi18.ap-southeast-2.rds.amazonaws.com", user = "xxx", password = "********", port = 1433, dbname= "qlik") if(0) { # check what the conn object has access to queryResults <- conn %>% dbGetQuery("select * from information_schema.tables") } # Create test data example_data <- data.frame(animal=c("dog", "cat", "sea cucumber", "sea urchin"), feel=c("furry", "furry", "squishy", "spiny"), weight=c(45, 8, 1.1, 0.8)) # Works in 20ms in my case system.time( conn %>% dbWriteTable( "qlik.export.test", example_data ) ) # Let us see if we see the exported results conn %>% dbGetQuery("select * FROM qlik.export.test") # Let's clean the mess and force-close connection at the end of the process conn %>% dbDisconnect() 

Es funktioniert ziemlich schnell für kleine Menge an data übertragen und scheint ziemlich elegant, wenn Sie data.frame -> SQL table data.frame wollen.

Genießen!