Handling database fetch size in Sqoop

SQOOP

Handling database fetch size in Sqoop

DECEMBER 1, 2015  VENKAT S LEAVE A COMMENT

When importing data from various RDBMSs you might not have found any issue in the process. This does not mean your Sqoop Import command will work perfectly all the time. When the size of fetched data is small which can fit into allocated memory you will not face any issues. But when fetched data reaches beyond the allocated memory size, you will start finding issues.  Your MapReduce job fails and throws exception “GC Overhead limit exceeded”.

Why Sqoop Import throws this exception?
The answer is – During the process, RDBMS database (NOT SQOOP) fetches all the rows at one shot and tries to load everything into memory. This causes memory spill out and throws error. To overcome this you need to tell RDBMS database to return the data in batches. The following parameters “?dontTrackOpenResources=true&defaultFetchSize=10000&useCursorFetch=true” following the jdbc connection string tells database to fetch 10000 rows per batch.

sqoop import \
  --connect jdbc:mysql://host.name.or.ip/mydb?dontTrackOpenResources=true&defaultFetchSize=10000&useCursorFetch=true \
  --username SOMEUSER -P -m 1

My issue and solution was specific to MySQL, but I suspect that the same problem could occur for other RDBMS and jdbc drivers. You might need to figure out options for others.

You can post here your solution if you were in similar situation and got it resolved for RDBMSs other than MySQL.