Read 100 GB .csv file from java and insert into MS SQL server DB

Question

I am trying to read data from csv file and insert into DB, using batch insertion. But it’s throwing multiple exceptions.

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>

Inserting code is bellow given :-

 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;
        }

I need to insert data up to 100 GB. In this code one row is inserting every time.

I had a very similar issue a while back. To make a long story short, I found it much easier to perform some pre-processing on the file to ensure it was valid. Once I could confirm that, I found it MUCH quicker to import into a temporary table. By that I mean an actual SQL Table I created for this express purpose in a different schema. I used .NET and SqlBulkCopy but you could look at BULK INSERT or something similar. From memory, there are loads of CSV readers for Java. — Mr Moose
– Mr Moose, Commented Jan 4, 2016 at 8:56

Jan · Accepted Answer · 2016-01-04 08:18:12Z

3

You're facing an OutOfMemoryError - which is not surprising if you try to stuff all your INSERTs into one batch.

Setup a reasonable batchsize (10000 in this example) and do like this:

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

Just the same, you should not call your method with the full array of lines read from CSV - so maybe you already have the batchsize implemented on that level?

Just as well, a batched statement is not supposed to return result sets - so instead of calling a procedure which does return a result, you re well advised to do this as "classical" INSERT operations.

answered Jan 4, 2016 at 8:18

Jan

13.9k3 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ravi Over a year ago

I am reading data in chunk using : 'ByteBuffer buffer = ByteBuffer.allocate(512); StringBuilder sb = new StringBuilder();'

Jan Over a year ago

how do you get full lines that way? Can you share the code calling the method you provided?

Mr_Thorynque · Accepted Answer · 2016-01-04 13:31:49Z

1

For this kind of work you can use mysql tool : mysqlimport You 'll have better performance and no deal with java limitation.

edit : You can use an ETL like talend or sql server integration services to do that and use bulk insert node.

edited Jan 4, 2016 at 13:31

answered Jan 4, 2016 at 10:16

Mr_Thorynque

2,0621 gold badge26 silver badges38 bronze badges

Comments

Lyubomyr Shaydariv · Accepted Answer · 2016-01-04 10:01:36Z

public SettingsDto uploadInChunck(Connection conn, SettingsDto oSettingsDto)throws Exception{
    SettingsDto objSettingsDto = new SettingsDto();
    List<SettingsDto> lstObj = null;
    FileChannel fc =null;
    File file = null;
    file = new File(oSettingsDto.getFormFile().getOriginalFilename());
    oSettingsDto.getFormFile().transferTo(file);
    RandomAccessFile raf = new RandomAccessFile(file, "r");
    fc = raf.getChannel();
    long startTime = System.currentTimeMillis();
        try{
            ByteBuffer buffer = ByteBuffer.allocate(512);
            StringBuilder sb = new StringBuilder();
            while(fc.read(buffer) > 0)
            {
                buffer.flip();
                for (int i = 0; i < buffer.limit(); i++)
                {
                    sb.append((char) buffer.get());
                }

                String[] strArr = sb.toString().split("\\n");

                if(null != oSettingsDto.getUploadFileTemplate() && oSettingsDto.getUploadFileTemplate().equals("Open Order Data")){
                    settingsDao.updateOpenOrdData(conn, strArr);
                } else if(null != oSettingsDto.getUploadFileTemplate() && oSettingsDto.getUploadFileTemplate().equals("Shipment Forecast Data")){
                    settingsDao.updateShipmentForcastData(conn, strArr);
                }else if(null != oSettingsDto.getUploadFileTemplate() && oSettingsDto.getUploadFileTemplate().equals("Inventory Target Data")){
                    settingsDao.updateInventoryTargetData(conn, strArr);
                }else if(null != oSettingsDto.getUploadFileTemplate() && oSettingsDto.getUploadFileTemplate().equals("Current Inventory Data")){
                    settingsDao.updateCurrentInventoryData(conn, strArr);
                }

                buffer.clear(); // do something with the data and clear/compact it.
                long endTime = System.currentTimeMillis();
                System.out.println("startTime :"+startTime);
                System.out.println("endTime :"+endTime);

            }
        }catch(Exception e){
        //e.printStackTrace();
        fc.close();
        raf.close();
        file.deleteOnExit();
    }


    return objSettingsDto;
}

please edit your question instead. And check out BufferedReader which reads a file line-by-line. You should also keep the e.printStackTrace() - it might contain vital information. Closing the streams only in case of an exception seems off as well

Collectives™ on Stack Overflow

Read 100 GB .csv file from java and insert into MS SQL server DB

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related