如何在R中以最快的速度向数据库表写入数据？

Question

我是一名有用的助手，可以为您翻译文本。

我有一个包含1.5M行数据的R数据表，我想将其导出到MS SQL数据库表中。

我知道可以这样做：

dbWriteTable(conn,"benefit_custom.Trial_set",trial_set )

但是它非常慢。

我尝试的另一个选项是将数据写入平面文件，然后创建一个SSIS pkg将其转移到数据库中。这不是问题，但问题在于我的数据表中有字符串和数字数据，当R写入文件时，所有内容都是varchar类型并被引号包含。

FileLocation <-"\\Benefit_Analysis_Input.dat"

 FileName<- paste( bcpWorkspace,FileLocation,sep = "") 

write.table(trial_set,file =FileName,append = FALSE, sep = "\t",col.names = T, row.names = F) 

第一种方法可以保留数据类型，但性能非常差。有没有其他可以尝试的方法？

所以我猜如果我要写入一个平面文件，那么数据类型就不能被保留，所以我必须在将平面文件导入数据库时选择数据类型。

- Cyang

---

我对R还很陌生，你能告诉我dbWriteTable的SQL Server实现在哪里吗？到目前为止，我只看到了mysql。 - billinkc

我不确定你想要什么.. 我该如何获取？ - Cyang

我对R不是很了解，但看起来write.csv()接受一个参数，可以不引用字段。http://astrostatistics.psu.edu/datasets/R/html/base/html/write.table.html - MarkD

通常情况下，只有当R中存在字符或因子时，写入文件时才会引用值。正如前面提到的，这种行为将由write.table中的参数控制。至于@billinkc的问题，如果他们正在写入SQL Server，则最可能使用RODBC。 - joran

你不需要使用SSIS包将数据传输到数据库中，你可以直接从平面文件进行导入，并选择每个列的数据类型。 - rhealitycheck

Answer 1

回答您的问题：目前最快的似乎是rsqlserver

目前我知道以下内容：

• rsqlserver：使用System.Data.SqlClient驱动程序，仅适用于Windows操作系统
• RSQLServer：使用Java驱动程序从任何操作系统使用RJDBC连接SQL Server
• RODBC：使用ODBC驱动程序，在Windows操作系统上易于设置

从R会话角度来看，微软SQL Server似乎支持不太好。

这里是rsqlserver项目的有趣基准测试：https://github.com/agstudy/rsqlserver/wiki/benchmarking

还要注意与rsqlserver相关的重要事项：计划使用mono推出Linux版本。

最后，我的最新演示文稿关于使用R进行数据仓库，包括DBI、RJDBC和RODBC示例。

- jangorecki

Answer 2

我认为@rhealitycheck的想法是正确的 - 我会使用SQL导入和导出数据向导来生成一个SSIS包。我会保存它并稍后自定义它，例如添加上游执行进程任务来调用R并将文本文件写出。

这种解决方案的性能和灵活性难以超越。

- Mike Honey

---

但是如何保留数据类型呢？我知道使用这个更快，但我不希望所有的东西都变成varchar。 - Cyang

选项1. 在导入向导的“选择源表”步骤中，使用“编辑映射”窗口设置数据类型。选项2. 保存从导入向导生成的SSIS包，然后使用BIDS/Visual Studio进行编辑。 - Mike Honey