通过R将数据上传到PostgresSQL 12的最快方法

Question

我使用以下代码连接到PostgreSQL 12数据库：

con <- DBI::dbConnect(odbc::odbc(), driver, server, database, uid, pwd, port)

这将我连接到Google Cloud SQL上的PostgreSQL 12数据库。然后使用以下代码上传数据：

DBI::dbCreateTable(con, tablename, df)
DBI::dbAppendTable(con, tablename, df)

其中df是我在 R 中创建的数据框。数据框由 ~ 550,000 条记录组成，总计 713 MB 数据。

通过上述方法上传时，以每秒 90 次写入操作的速度上传大约需要 40 个小时。有没有更快的方法可以将这些数据上传到我的PostgreSQL数据库中，最好是通过R？

Answer 1

我一直发现批量复制是最好的，在R之外。插入可以明显加快，并且您的开销是 (1( 写入文件，以及 (2( 更短的运行时间。

此测试的设置：

• 赢10 (2004(
• 码头工人
• postgres：11 容器，在本地主机上使用端口 35432，简单身份验证
• 主机操作系统(运行R的地方(中的psql二进制文件;使用Linux应该很容易，使用Windows我从 https://www.postgresql.org/download/windows/中获取了"zip"(不是安装程序(文件并提取了我需要的内容
• 我使用data.table::fwrite来保存文件，因为它很快; 在这种情况下，write.table和write.csv仍然比使用DBI::dbWriteTable快得多，但是根据您的数据大小，您可能更喜欢快速的东西

DBI::dbCreateTable(con2, "mt", mtcars)
DBI::dbGetQuery(con2, "select count(*) as n from mt")
#   n
# 1 0
z1000 <- data.table::rbindlist(replicate(1000, mtcars, simplify=F))
nrow(z1000)
# [1] 32000
system.time({
DBI::dbWriteTable(con2, "mt", z1000, create = FALSE, append = TRUE)
})
#    user  system elapsed 
#    1.56    1.09   30.90 
system.time({
data.table::fwrite(z1000, "mt.csv")
URI <- sprintf("postgresql://%s:%s@%s:%s", "postgres", "mysecretpassword", "127.0.0.1", "35432")
system(
sprintf("psql.exe -U postgres -c "\copy %s (%s) from %s (FORMAT CSV, HEADER)" %s",
"mt", paste(colnames(z1000), collapse = ","),
sQuote("mt.csv"), URI)
)
})    
# COPY 32000
#    user  system elapsed 
#    0.05    0.00    0.19 
DBI::dbGetQuery(con2, "select count(*) as n from mt")
#       n
# 1 64000

虽然这比您的数据(32K 行、11 列、1.3MB 数据(小得多，但从 30 秒到不到 1 秒的加速是不容忽视的。

---

旁注：dbAppendTable(慢(和dbWriteTable之间也有很大的差异。比较psql和这两个函数：

z100 <- rbindlist(replicate(100, mtcars, simplify=F))
system.time({
data.table::fwrite(z100, "mt.csv")
URI <- sprintf("postgresql://%s:%s@%s:%s", "postgres", "mysecretpassword", "127.0.0.1", "35432")
system(
sprintf("/Users/r2/bin/psql -U postgres -c "\copy %s (%s) from %s (FORMAT CSV, HEADER)" %s",
"mt", paste(colnames(z100), collapse = ","),
sQuote("mt.csv"), URI)
)
})
# COPY 3200
#    user  system elapsed 
#     0.0     0.0     0.1 
system.time({
DBI::dbWriteTable(con2, "mt", z100, create = FALSE, append = TRUE)
})
#    user  system elapsed 
#    0.17    0.04    2.95 
system.time({
DBI::dbAppendTable(con2, "mt", z100, create = FALSE, append = TRUE)
})
#    user  system elapsed 
#    0.74    0.33   23.59 

(我不想用上面的z1000dbAppendTable时间...

(对于踢球，我用replicate(10000, ...)运行它，并再次运行psql和dbWriteTable测试，它们分别花了 2 秒和 372 秒。您的选择 ：-( ...现在我有超过 650,000 行mtcars...呜呜......drop table mt......

Answer 2

我怀疑dbAppendTable会导致每行INSERT语句，这对于大量行可能需要很长时间。

但是，您可以使用sqlAppendTable函数为整个数据框生成单个INSERT语句，并通过显式使用dbSendQuery来运行它：

res <- DBI::dbSendQuery(con, DBI::sqlAppendTable(con, tablename, df, row.names=FALSE))
DBI::dbClearResult(res)

对我来说，这要快得多：30 秒的摄取减少到 0.5 秒的摄取。