Sqoop import is failed due to java.io.IOException: SQLException in nextKeyValue

Question

I am joining three tables data and importing the data from Oracle to Hive using Sqoop import command. Find table data count below.

select count(*) from table1; -- 40446561

select count(*) from table2; -- 16886690

select count(*) from  table3;  -- 15142664

Sqoop Query:

sqoop-import -D mapred.child.java.opts="-Djava.security.egd=file:/dev/../dev/urandom" --connect $CONNECTION --username $DB_USER_NAME --password $DB_PASSWORD --hive-import --hive-overwrite --hive-table ${HIVE_TABLE_NAME} --target-dir $HDFS_TARGET_DIR --mapreduce-job-name $JOB_NAME --query " SELECT t.* FROM (SELECT rownum ID, a.column1, a.column2, a.column3, a.column4, b.column5, b.column6, c.column7 FROM table1 a LEFT OUTER JOIN table2 b on (b.column5 = a.column1) LEFT OUTER JOIN table3 c on (c.column7= a.column1)) t WHERE \$CONDITIONS" --split-by t.ID --null-string '\\N' --null-non-string '\\N' --num-mappers 12 --fetch-size 10000 --delete-target-dir --direct --verbose

I am getting the below exception:

Error: java.io.IOException: SQLException in nextKeyValue
        at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:277)
        at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:556)
        at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:80)
        at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:91)
        at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)
        at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
        at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
        at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:422)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724)
        at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:162)
Caused by: java.sql.SQLRecoverableException: IO Error: Connection reset
        at oracle.jdbc.driver.T4CPreparedStatement.fetch(T4CPreparedStatement.java:1080)
        at oracle.jdbc.driver.OracleStatement.fetchMoreRows(OracleStatement.java:3716)
        at oracle.jdbc.driver.InsensitiveScrollableResultSet.fetchMoreRows(InsensitiveScrollableResultSet.java:1015)
        at oracle.jdbc.driver.InsensitiveScrollableResultSet.absoluteInternal(InsensitiveScrollableResultSet.java:979)
        at oracle.jdbc.driver.InsensitiveScrollableResultSet.next(InsensitiveScrollableResultSet.java:579)
        at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:237)
        ... 12 more
Caused by: java.net.SocketException: Connection reset
        at java.net.SocketInputStream.read(SocketInputStream.java:209)
        at java.net.SocketInputStream.read(SocketInputStream.java:141)
        at oracle.net.ns.Packet.receive(Packet.java:311)
        at oracle.net.ns.DataPacket.receive(DataPacket.java:105)
        at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:305)
        at oracle.net.ns.NetInputStream.read(NetInputStream.java:249)
        at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:104)
        at oracle.jdbc.driver.T4CMAREngineStream.getNBytes(T4CMAREngineStream.java:646)
        at oracle.jdbc.driver.T4CMAREngineStream.unmarshalNBytes(T4CMAREngineStream.java:616)
        at oracle.jdbc.driver.DynamicByteArray.unmarshalBuffer(DynamicByteArray.java:338)
        at oracle.jdbc.driver.DynamicByteArray.unmarshalCLR(DynamicByteArray.java:226)
        at oracle.jdbc.driver.T4CMarshaller$BasicMarshaller.unmarshalBytes(T4CMarshaller.java:124)
        at oracle.jdbc.driver.T4CMarshaller$BasicMarshaller.unmarshalOneRow(T4CMarshaller.java:101)
        at oracle.jdbc.driver.T4CVarcharAccessor.unmarshalOneRow(T4CVarcharAccessor.java:212)
        at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:1474)
        at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:1282)
        at oracle.jdbc.driver.T4C8Oall.readRXD(T4C8Oall.java:851)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:448)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
        at oracle.jdbc.driver.T4CPreparedStatement.fetch(T4CPreparedStatement.java:1066)
        ... 17 more

Please let me know to hot to fix it.

Answer 1

This simply means that something in the backend ( DBMS ) decided to stop working due to unavailability of resources etc. It has nothing to do with your code or the number of inserts. You can read more about similar problems here:

http://kr.forums.oracle.com/forums/thread.jspa?threadID=941911 http://forums.oracle.com/forums/thread.jspa?messageID=3800354

This may not answer your question, but you will get an idea of why it might be happening. You could further discuss with your DBA and see if there is something specific in your case.[duplicate]