sql - 从 R 将表写入 SAP HANA 的有效方法

Question

我有一个约 50,000 行和 12 列的表 (df) 要写入 SAP HANA。我使用RJDBC库，逐行写如下:

# Returns the sql statement to insert one row
 build_insert_string <- function(db_output, row) {
  row_string <- paste(row, collapse="','")
  statement <- paste('INSERT INTO "', db_output$SCHEMA, '"."',db_output$table_name,'" (',db_output$string_of_columns,') VALUES (\'', row_string, '\');', sep='')
  return(statement)
}

# Insert row by row
for(i in 1:nrow(df)){
    tryCatch({ dbGetQuery(jdbcConnection, build_insert_string(db_output, df[i,])) }, error = function(e) {handle_db_errors(e)})
  }

其中 db_output 是包含输出常量(模式、表和列)的列表。

目前，写表需要将近半天的时间。好像HANA不支持批量插入比如:

INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

有没有人遇到过这个挑战，如果遇到过，你有没有想办法绕过它，提高写作效率？

Answer 1

为了后代，我会把它留在这里:

虽然 dbGetQuery 是大型表的干净解决方案 – 它执行查询，然后在每次插入后清除结果集，但它也很慢。

显然，从 SQL 编辑器发送到 SAP HANA 的多个 INSERT 是成功的，但从 R 发送时不是。

(真正)快速的解决方案将由以下人员提供:

dbWriteTable (
conn = jdbcConnection,
name= paste0(db_output$SCHEMA, ".",db_output$table_name),
value = df,
row.names = FALSE,
field.types = db_output$string_of_columns, 
append=TRUE
)

但是，dbWriteTable() 不适用于大型表(它会抛出内存限制错误)。可以通过修改 Xmx Java 选项来增加内存分配池来规避此限制，例如:options(java.parameters="- Xmx5000m")。使用它需要您自担风险，尤其是当您打算自动写入越来越大的表时。

我们探索的另一个可能的解决方案是将 R 输出导出为 .csv(或多个 .csv，以防超过100 万行)，然后发送查询以将 .csv 导入 SAP HANA。大型 csv 可以非常快速地导入到 SAP HANA，但此解决方案需要一个额外的步骤(中间 .csv 输出)并且更容易出现错误的数据导入。