This is an incremental load from an Oracle table based on lastmodified value from LAST_UPDT_TMSP column. Below are the other sqoop parameters used if it can help.
--query "SELECT USER_INFO_ID,USER_ACTV_DIR_ID,CRT_TMSP,CRT_BY_USER_ID,LAST_UPDT_TMSP,LAST_UPDT_BY_USER_ID from XXXDATABASE.USER_INFO WHERE \$CONDITIONS" \
--split-by 'USER_INFO_ID' \
--fields-terminated-by '\0001'
The ojdbc jar version we are using is ojdbc7-12.1.0.2.jar.
I am sure that there is no issues with the sqoop query, since it fails only first time. After we restart the process, the ingestion works fine. Also we are not facing this error every first time. This happens 2 out of 5 times maybe.
Any ideas on why Sqoop behaves this way? Below are the exceptions we get:
Exception 1
2018-08-07 00:03:46,304 INFO [IPC Server handler 5 on 41284] org.apache.hadoop.mapred.TaskAttemptListenerImpl: Diagnostics report from attempt_1532184866611_14461_m_000000_3: 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:170) 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:1866) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:164)Caused by: java.sql.SQLException: Fail to convert to internal representation at oracle.jdbc.driver.CharCommonAccessor.getBigDecimal(CharCommonAccessor.java:325) atoracle.jdbc.driver.GeneratedStatement.getBigDecimal(GeneratedStatement.java:96) at oracle.jdbc.driver.GeneratedScrollableResultSet.getBigDecimal(GeneratedScrollableResultSet.java:120) at org.apache.sqoop.lib.JdbcWritableBridge.readBigDecimal(JdbcWritableBridge.java:126) at com.cloudera.sqoop.lib.JdbcWritableBridge.readBigDecimal(JdbcWritableBridge.java:97) at QueryResult.readFields(QueryResult.java:170) at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:244) ... 12 more
Exception 2, which we got another day. The job completed successfully again on restart.
2018-07-26 00:02:33,829 INFO [IPC Server handler 4 on 38317] org.apache.hadoop.mapred.TaskAttemptListenerImpl: Diagnostics report from attempt_1532184866611_3307_m_000000_3: 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:170) 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:1866) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:164)Caused by: java.sql.SQLException: Invalid column type: getTimestamp not implemented for class oracle.jdbc.driver.T4CRawAccessor atoracle.jdbc.driver.GeneratedAccessor.getTimestamp(GeneratedAccessor.java:748) at oracle.jdbc.driver.GeneratedStatement.getTimestamp(GeneratedStatement.java:360) at oracle.jdbc.driver.GeneratedScrollableResultSet.getTimestamp(GeneratedScrollableResultSet.java:1008) at org.apache.sqoop.lib.JdbcWritableBridge.readTimestamp(JdbcWritableBridge.java:111) at com.cloudera.sqoop.lib.JdbcWritableBridge.readTimestamp(JdbcWritableBridge.java:83) at QueryResult.readFields(QueryResult.java:183) at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:244) ... 12 more
Exception 3:
2018-07-25 00:02:58,205 INFO [Thread-18] org.apache.sqoop.mapreduce.AutoProgressMapper: Auto-progress thread is finished. keepGoing=false 2018-07-25 00:02:58,224 WARN [main] org.apache.hadoop.mapred.YarnChild: Exception running child : 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:170) 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:1866) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:164)Caused by: java.sql.SQLException: Invalid conversion requested at oracle.jdbc.driver.CharCommonAccessor.getTimestamp(CharCommonAccessor.java:420) at oracle.jdbc.driver.T4CVarcharAccessor.getTimestamp(T4CVarcharAccessor.java:644) atoracle.jdbc.driver.GeneratedStatement.getTimestamp(GeneratedStatement.java:360) at oracle.jdbc.driver.GeneratedScrollableResultSet.getTimestamp(GeneratedScrollableResultSet.java:1008) at org.apache.sqoop.lib.JdbcWritableBridge.readTimestamp(JdbcWritableBridge.java:111) at com.cloudera.sqoop.lib.JdbcWritableBridge.readTimestamp(JdbcWritableBridge.java:83) at QueryResult.readFields(QueryResult.java:172) at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:244) ... 12 more Caused by:java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff] at java.sql.Timestamp.valueOf(Timestamp.java:204) atoracle.jdbc.driver.CharCommonAccessor.getTimestamp(CharCommonAccessor.java:415) ... 19 more
