如何高效地将数据从R上传到SQL数据库（Snowflake）？

Question

我想创建一个函数，以尽可能高效的方式从 R 中将潜在的大数据框（1M + 行）上传到 Snowflake。下面的代码是我目前采用的方法，我将数据框分成100行的块，并通过 dbSendUpdate 循环迭代100行和余数。对于大数据框，这通常需要很长时间，有没有更有效的方法可以使用？

upload_func <- function (dataframe) {
for (i in 0:(nrow(dataframe)/100 - (nrow(dataframe)/100)%%1)) {
  if (100*(i + 1) <= nrow(dataframe)) {

    b <- 100*i + 1
    e <- 100*(i + 1)

    values <- paste0(apply(dataframe[b:e,], 1, function(x) paste0("('", paste0(x, collapse = "', '"), "')")), collapse = ", ")

    dbSendUpdate(connection, paste0("INSERT INTO database_table
                                  VALUES ", values, ";"))
  }

  else {
    values <- paste0(apply(dataframe[(i*100 + 1):(i*100 + nrow(dataframe)%%100), ], 1, function(x) paste0("('", paste0(x, collapse = "', '"), "')")), collapse = ", ")

    dbSendUpdate(connection, paste0("INSERT INTO database_table 
                                  VALUES ", values, ";"))
  }
}

}

- Kyang

---

不确定R是否适用，但对于插入操作，你根本不应该使用循环。应该使用Snowflake提供的本地函数，如COPY INTO...等，这样会更有效率。如果这种方法不起作用，那么另一种方法就是使用单个insert语句进行批量插入。 - mad_

我注意到dbSendUpdate能够发送的记录数量有限制，因此需要循环。您知道批量插入是否受到相同的影响吗？ - Kyang

@mad_ 推荐的 COPY INTO 是最快速将数据导入Snowflake的正确方法。R将数据放到blob存储上的文件中，然后使用 COPY INTO 将其导入到Snowflake中。任何类型的 INSERT 语句不会像对于较大数据集那样高效。 - Mike Walton

Answer 1

为了获得最佳性能，请勿直接从 R 中插入数据。将数据帧写入云存储中的 CSV 文件，创建一个指向该位置的外部阶段，然后使用 COPY INTO 命令。这样可以利用并行处理来摄取数据。为了优化负载的并行操作数量，建议生成大约100-250 MB压缩的数据文件。

- Robert Long