Как быстро экспортировать данные из R в SQL Server

Стандартная функция RODBC package sqlSave даже в виде одиночного оператора INSERT (параметр fast = TRUE) ужасно медленна для больших объемов данных из-за не минимальной загрузки. Как записать данные на мой SQL-сервер с минимальным протоколированием, чтобы он записывал гораздо быстрее?

Ответы

Ответ 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.

Наслаждайтесь!