Sqoop fails for a particular table

[#1]

Sqoop works perfectly for one particular table. When I try to import another, I get the following error. Please let me know what I should do...

17/03/14 20:34:51 INFO mapreduce.Job: Task Id : attempt_1489520357633_0004_m_000000_0, Status : FAILED 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: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=PROJECT_ID, DRIVER=4.11.77 at com.ibm.db2.jcc.am.gd.a(gd.java:676) at com.ibm.db2.jcc.am.gd.a(gd.java:60) at com.ibm.db2.jcc.am.gd.a(gd.java:127) at com.ibm.db2.jcc.am.jn.c(jn.java:2561) at com.ibm.db2.jcc.am.jn.d(jn.java:2549) at com.ibm.db2.jcc.am.jn.a(jn.java:2025) at com.ibm.db2.jcc.am.kn.a(kn.java:6836) at com.ibm.db2.jcc.t4.cb.g(cb.java:140) at com.ibm.db2.jcc.t4.cb.a(cb.java:40) at com.ibm.db2.jcc.t4.q.a(q.java:32) at com.ibm.db2.jcc.t4.rb.i(rb.java:135) at com.ibm.db2.jcc.am.jn.ib(jn.java:1996) at com.ibm.db2.jcc.am.kn.sc(kn.java:3058) at com.ibm.db2.jcc.am.kn.b(kn.java:3841) at com.ibm.db2.jcc.am.kn.fc(kn.java:702) at com.ibm.db2.jcc.am.kn.executeQuery(kn.java:672) at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111) at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235) ... 12 more 17/03/14 20:34:58 INFO mapreduce.Job: Task Id : attempt_1489520357633_0004_m_000000_1, Status : FAILED 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: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=PROJECT_ID, DRIVER=4.11.77 at com.ibm.db2.jcc.am.gd.a(gd.java:676) at com.ibm.db2.jcc.am.gd.a(gd.java:60) at com.ibm.db2.jcc.am.gd.a(gd.java:127) at com.ibm.db2.jcc.am.jn.c(jn.java:2561) at com.ibm.db2.jcc.am.jn.d(jn.java:2549) at com.ibm.db2.jcc.am.jn.a(jn.java:2025) at com.ibm.db2.jcc.am.kn.a(kn.java:6836) at com.ibm.db2.jcc.t4.cb.g(cb.java:140) at com.ibm.db2.jcc.t4.cb.a(cb.java:40) at com.ibm.db2.jcc.t4.q.a(q.java:32) at com.ibm.db2.jcc.t4.rb.i(rb.java:135) at com.ibm.db2.jcc.am.jn.ib(jn.java:1996) at com.ibm.db2.jcc.am.kn.sc(kn.java:3058) at com.ibm.db2.jcc.am.kn.b(kn.java:3841) at com.ibm.db2.jcc.am.kn.fc(kn.java:702) at com.ibm.db2.jcc.am.kn.executeQuery(kn.java:672) at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111) at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235) ... 12 more 17/03/14 20:35:05 INFO mapreduce.Job: Task Id : attempt_1489520357633_0004_m_000000_2, Status : FAILED 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: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=PROJECT_ID, DRIVER=4.11.77 at com.ibm.db2.jcc.am.gd.a(gd.java:676) at com.ibm.db2.jcc.am.gd.a(gd.java:60) at com.ibm.db2.jcc.am.gd.a(gd.java:127) at com.ibm.db2.jcc.am.jn.c(jn.java:2561) at com.ibm.db2.jcc.am.jn.d(jn.java:2549) at com.ibm.db2.jcc.am.jn.a(jn.java:2025) at com.ibm.db2.jcc.am.kn.a(kn.java:6836) at com.ibm.db2.jcc.t4.cb.g(cb.java:140) at com.ibm.db2.jcc.t4.cb.a(cb.java:40) at com.ibm.db2.jcc.t4.q.a(q.java:32) at com.ibm.db2.jcc.t4.rb.i(rb.java:135) at com.ibm.db2.jcc.am.jn.ib(jn.java:1996) at com.ibm.db2.jcc.am.kn.sc(kn.java:3058) at com.ibm.db2.jcc.am.kn.b(kn.java:3841) at com.ibm.db2.jcc.am.kn.fc(kn.java:702) at com.ibm.db2.jcc.am.kn.executeQuery(kn.java:672) at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111) at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235) ... 12 more 17/03/14 20:35:12 INFO mapreduce.Job: map 100% reduce 0% 17/03/14 20:35:14 INFO mapreduce.Job: Job job_1489520357633_0004 failed with state FAILED due to: Task failed task_1489520357633_0004_m_000000 Job failed as tasks failed. failedMaps:1 failedReduces:0 17/03/14 20:35:14 INFO mapreduce.Job: Counters: 8 Job Counters Failed map tasks=4 Launched map tasks=4 Other local map tasks=4 Total time spent by all maps in occupied slots (ms)=23472 Total time spent by all reduces in occupied slots (ms)=0 Total time spent by all map tasks (ms)=23472 Total vcore-milliseconds taken by all map tasks=23472 Total megabyte-milliseconds taken by all map tasks=5868000 17/03/14 20:35:14 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead 17/03/14 20:35:14 INFO mapreduce.ImportJobBase: Transferred 0 bytes in 69.4089 seconds (0 bytes/sec) 17/03/14 20:35:14 WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead 17/03/14 20:35:14 INFO mapreduce.ImportJobBase: Retrieved 0 records. 17/03/14 20:35:14 ERROR tool.ImportTool: Error during import: Import job failed!

[#2]

Try this below URL

jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull

[#3]

I try the following:

• sqoop import --driver com.ibm.db2.jcc.DB2Driver --connect jdbc:db2://localhost/database?zeroDateTimeBehavior=convertToNull --username username --password password --table schema.tablename -hive-import -hive-table hive_sqoop1 --m 1

And the following is the error message:

Warning: /usr/hdp/2.5.0.0-1245/accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. find: failed to restore initial working directory: Permission denied 17/03/14 21:09:00 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.5.0.0-1245 17/03/14 21:09:00 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. 17/03/14 21:09:00 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override 17/03/14 21:09:00 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc. 17/03/14 21:09:00 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time. 17/03/14 21:09:00 INFO manager.SqlManager: Using default fetchSize of 1000 17/03/14 21:09:00 INFO tool.CodeGenTool: Beginning code generation 17/03/14 21:09:01 ERROR manager.SqlManager: Error executing statement: com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2057][11264][4.11.77] The application server rejected establishment of the connection. An attempt was made to access a database, CAMH1?zeroDateTimeBehavior=convertToNull, which was either not found or does not support transactions. ERRORCODE=-4499, SQLSTATE=08004 com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2057][11264][4.11.77] The application server rejected establishment of the connection. An attempt was made to access a database, CAMH1?zeroDateTimeBehavior=convertToNull, which was either not found or does not support transactions. ERRORCODE=-4499, SQLSTATE=08004 at com.ibm.db2.jcc.am.gd.a(gd.java:319) at com.ibm.db2.jcc.am.gd.a(gd.java:365) at com.ibm.db2.jcc.t4.ab.u(ab.java:1674) at com.ibm.db2.jcc.t4.ab.n(ab.java:536) at com.ibm.db2.jcc.t4.ab.a(ab.java:343) at com.ibm.db2.jcc.t4.ab.a(ab.java:115) at com.ibm.db2.jcc.t4.b.m(b.java:1242) at com.ibm.db2.jcc.t4.b.b(b.java:1113) at com.ibm.db2.jcc.t4.b.d(b.java:696) at com.ibm.db2.jcc.t4.b.c(b.java:682) at com.ibm.db2.jcc.t4.b.a(b.java:367) at com.ibm.db2.jcc.t4.b.<init>(b.java:307) at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(DB2SimpleDataSource.java:214) at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:460) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247) at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:904) at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52) at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:763) at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:786) at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:289) at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260) at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:246) at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:328) at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1853) at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1653) at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107) at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:488) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615) at org.apache.sqoop.Sqoop.run(Sqoop.java:147) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234) at org.apache.sqoop.Sqoop.main(Sqoop.java:243) 17/03/14 21:09:01 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1659) at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107) at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:488) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615) at org.apache.sqoop.Sqoop.run(Sqoop.java:147) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234) at org.apache.sqoop.Sqoop.main(Sqoop.java:243)

[#4]

Are you using custom query here? Typically the error you are getting is because of a missing column name in the select clause of the query.

[#5]

I'm using:

• sqoop import --driver com.ibm.db2.jcc.DB2Driver --connect jdbc:db2://localhost:port/database?zeroDateTimeBehavior=convertToNull --username username --password password --table schema.tablename -hive-import -hive-table hive_sqoop1 --m 1