Как быстро экспортировать данные из R в SQL Server
Стандартная функция RODBC package sqlSave
даже в виде одиночного оператора INSERT
(параметр fast = TRUE
) ужасно медленна для больших объемов данных из-за не минимальной загрузки. Как записать данные на мой SQL-сервер с минимальным протоколированием, чтобы он записывал гораздо быстрее?
В настоящее время пытается:
toSQL = data.frame(...);
sqlSave(channel,toSQL,tablename="Table1",rownames=FALSE,colnames=FALSE,safer=FALSE,fast=TRUE);
Ответы
Ответ 1
Записывая данные в CSV локально, а затем используя BULK INSERT
(не легкодоступный как предварительно построенная функция, сродни sqlSave
), данные могут быть записаны на MS SQL Server очень быстро.
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 должен иметь разрешение на доступ к сетевой папке, содержащей CSV файл, иначе этот процесс не будет работать. Хотя он требует некоторой настройки с различными разрешениями (сетевая папка и BULK ADMIN
привилегии, вознаграждение в скорости бесконечно более ценно).
Ответ 2
Я полностью согласен с тем, что BULK INSERT
- это правильный вариант для любых данных, которые не являются крошечными.
Однако, если вам нужно добавить 2-3 строки, например. отладочное сообщение BULK INSERT
похоже на перебор.
Ответ на ваш вопрос будет функцией DBI::dbWriteTable()
. Пример ниже (я соединяю свой код R с AWS RDS
экземпляром 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 clean the mess and force-close connection at the end of the process
conn %>% dbDisconnect()
Он работает довольно быстро для небольшого количества переданных данных и кажется довольно изящным, если вы хотите решение data.frame
→ SQL table
.
Наслаждайтесь!