java - 从 java 中读取 100 GB .csv 文件并插入到 MS SQL Server 数据库中

Question

我正在尝试使用批量插入从 csv 文件中读取数据并插入到数据库中。但是它会抛出多个异常。

java.sql.BatchUpdateException: A statement attempted to return a result set in executeBatch().<br>
SEVERE: Servlet.service() for servlet oms threw exception
java.lang.OutOfMemoryError: Java heap space ……. <br>

插入代码如下:-

 public long updateOpenOrdData(Connection conn, String[] paramStrObj)throws Exception {
            long updatedRow = 0;
            CallableStatement cstmt = null;
             // System.out.println("StrAtt length :"+ strArr.length);
            try{
                for(int i=0; i<paramStrObj.length; i++){
                    int count =1;
                    int index = 0;
                    String[] dataArr = paramStrObj[i].split(",(?=([^\"]*\"[^\"]*\")*[^\"]*$)", -1); 
                    if(!dataArr[0].equals("SLC_Code_Desc") && dataArr.length >= 24 ){
                        cstmt = conn.prepareCall(PROC_INSERT_OPEN_ORD_TEMP);
                        System.out.print(dataArr[index]+", ");
                        cstmt.setString(count++, dataArr[index++]);
                        System.out.print(dataArr[index]+", ");
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index++]);
                        cstmt.setString(count++, dataArr[index]);
                        cstmt.setString(count++, null);
                        cstmt.setString(count++, null);
                        cstmt.setString(count++, null);
                        cstmt.addBatch();
                        //cstmt.executeUpdate();
                        }
                    }
                int[] insertRow =cstmt.executeBatch();
                System.out.println("Inserted row are :: "+insertRow);
                }catch (Exception e) {
                //System.out.println("Wrong data at line "+i+" and column "+ --index);
                e.printStackTrace();
            }finally{
                new DBService().releaseResources(null, cstmt);
            }

            return updatedRow;
        }

我需要插入最多 100 GB 的数据。在此代码中，每次都插入一行。

Answer 1

您正面临 OutOfMemoryError - 如果您尝试将所有 插入到一个 批处理中，这并不奇怪。

设置一个合理的批量大小(本例中为 10000)并像这样:

  cstmt.addBatch();
  if(++batchCounter % 10000 == 0) {
  cstmt.executeBatch();
  }
}
cstmt.executeBatch(); //one final time for the remaining rows

同样，您不应该使用从 CSV 中读取的完整行数组来调用您的方法 - 所以也许您已经在该级别实现了批大小？

同样，batched 语句不应返回结果集 - 因此，与其调用返回结果的过程，不如调用我们建议您将此操作作为“经典”INSERT 操作。