java - 从 HashMap 高效批量插入/复制到表中

Question

任务:

给定这个 HashMap 结构:Map<String, Map<String, String>> mainMap = new HashMap<>()

我要INSERT或 COPY 内部的每个值 Map进入我的数据库中它自己的单元格。

• size()的 mainMap如果50,000。
• size()内部Map是 50。
• 要插入的表有 50 列。
• 每列的标题是内部 Map 的键.

编辑:最初，用户上传了一个包含 50 列中的 35 列的大型电子表格。然后我用各种格式“清理”该数据，并为每个 mainMap 添加我自己的 15 个新对到 innerMap 中。入口。我不能直接COPY从用户的源文件到我的数据库，无需清理/格式化/添加。

完成电子表格迭代并构建 mainMap 后，那是我需要有效地插入到我的数据库表中的时候。

研究:

我已经 read那COPY是最初批量填充表的最佳方法，但我仍然坚持我的要求是否保证该命令。

This post说明 Postgres 的一个查询的 Prepared Statement 参数限制为 34464。

我假设总共需要 50 x 50,000 = 2,500,000 个参数。这相当于 ~ 73 个单独的查询!

问题:

• 是COPY这里的正确方法而不是所有这些参数？
• 如果是，我是否转换 HashMap值变成 .sql文件，将其保存在我的 Web 应用程序服务器的磁盘上，然后在我的 COPY 中引用它命令，然后删除临时文件？或者我可以直接传递一个串联的 String进入它，而不冒 SQL 注入(inject)的风险？

这个命令会经常发生，因此需要优化。

我找不到任何将 Java 对象转换为兼容的 Postgres 文本文件格式的示例，因此任何反馈都会有所帮助。

你会如何解决这个问题？

附加信息:

我的表是预先存在的，无法删除，因为它是我的网络应用程序的后端，并且在任何给定时间都有多个用户连接。

我了解在使用 COPY 之前临时删除索引可以提高性能，但我只需要一次最多插入或复制 50,000 行，而不是数百万行。

StackExchange 让我在这里问。

Answer 1

虽然 Java 肯定不是执行此类 ETL 的最佳选择，但使用标准 INSERT 语句和准备好的查询当然是可能的并且开销相当小:

conn.setAutoCommit(false);
PreparedStatement stmt = conn.prepareStatement(
        "INSERT INTO my_table (col_a, col_b, ...)"
        + " VALUES (?, ?, ...)");
int batchSize = 1000;
int rows = 0;
for (Map<String, String> values : mainMap.values()) {
    int i = 0;
    stmt.setString(++i, values.get("col_a"));
    stmt.setString(++i, values.get("col_b"));
    // ...
    stmt.addBatch(); // add the row to the batch
    if (++rows % batchSize == 0) {
        // batch-sizing: execute...
        stmt.executeBatch();
    }
}

if (rows % batchSize != 0) {
    // a last execution if necessary...
    stmt.executeBatch();
}
conn.commit(); // atomic action - if any record fails, the whole import will fail

或者，您可以将 map 写入文件并使用 CopyManager ，但我严重怀疑这会比批量插入更快(尽管对于数百万行会有所不同)。