Large csv data upload to google cloud sql

Question

I am try to upload large csv data to cloud sql

below is the my code for blobe store and csv loader.

1:-

Map<String, BlobKey> blobs = blobstoreService.getUploadedBlobs(req);
        BlobKey blobKey = blobs.get("file");

            BlobstoreInputStream **is** = new BlobstoreInputStream(new BlobKey(
                    blobKey.getKeyString()));

Than calling csv loader like this

loader.loadCSV(****is****, "salesstatus", true, req, resp,httpSession.getAttribute("username1").toString());

Here is the code to read my csv file with header and data. a)Header

String[] headerRow = csvReader.readNext();
String[] headerRow1 =new String[headerRow.length+1];

int size = headerRow.length;
System.out.println(size);
for(int i=0; i<=size; i++){
    if(i<size){
        headerRow1[i]=headerRow[i];
    }else{
        headerRow1[i]="UploadedBy";
    }
}

b)generating dynamic query

String questionmarks = StringUtils.repeat("?,", headerRow1.length);
        questionmarks = (String) questionmarks.subSequence(0, questionmarks
                .length() - 1);

        String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
        query = query
                .replaceFirst(KEYS_REGEX, StringUtils.join(headerRow1, ","));
        query = query.replaceFirst(VALUES_REGEX, questionmarks);
        log.info( "query----114-"+query);

c)Reading data from .csv file and insearting into cloud sql using Batch update/Batch execute command

Connection con = null;
        PreparedStatement ps = null;

            log.info("inside try 131");
            con = this.connection;
            con.setAutoCommit(false);
            ps = con.prepareStatement(query);
            if(truncateBeforeLoad) {
                log.info("truncate 136");

                  con.createStatement();
            }
            final int batchSize = 1000;
            int count = 0;


             try {
                  while((nextLine = csvReader.readNext())!=null){
                      int lastCol=  nextLine.length+1;
                      if (null != nextLine) {
                          int index = 1;
                          for (String colValue : nextLine) {
                              date = colValue;  //DateUtil.convertToDate(string);
                              if (null != date) {
                                  ps.setString(index++, date);
                                  if(lastCol== index){
                                      ps.setString(6, username);
                                  }
                              } else {

                                  if(lastCol== index){
                                      ps.setString(6, username);
                                  }else{
                                      ps.setString(index++, colValue);
                                  }
                              }

                          }
                          ps.addBatch();           
                      }
                      if (++count % batchSize == 0) {
                          ps.executeBatch();
                      }
                  }
                  ps.executeBatch(); // insert remaining records
                  con.commit();

Here problem is when I am trying to upload the csv file with

When I am uploading the csv data with more than 86000 record then at the end of uploading file my screen is going blank even though the csv data is saving to my application apprx 20000 records.

When I have check the log then I got execption like No operations allowed after statement closed. This type of unexpected error is comming to my application.When I am uploading very less data to my application(appox 50 records) then its working fine.

I have search in google then I got this is the problem like query per second limitations in google cloud sql. refer link-https://developers.google.com/cloud-sql/faq#sizeqps

Can someone please help me regarding this. Means if problem is above then how can I increase the query per second time to google cloud sql.

cflewis cflewis · Accepted Answer · 2014-06-25T18:27:00

There's a lot of code here and a lot of things could be going on, but I think what you want to do in this instance is to get some resilience in your code; right now it assumes the network connections are always up, which they might not be. You don't check whether the database connection or statement are open when you do ps.executeBatch().

What I would do is this:

Read in the batch to memory, not into ps.
Check that the database connection is live (recreate it if not), and then create ps.
Use the batch in memory, and put it into ps.
Check that ps succeeds. If not an an exception is thrown, go to 2 and start again. If it succeeds, go back to 1 and read in the next batch.

This way your app will deal better with network connectivity issues.

Hope this helps!

Large csv data upload to google cloud sql

1 Answers