sqoop将数据从hive到mysql报错ERROR mapreduce.ExportJobBase: Export job failed!

报错如下20/08/14 10:57:43 INFO mapreduce.Job: Job job_1595919753289_4416 failed with state FAILED due to: Task failed task_1595919753289_4416_m_000000Job failed as tasks failed. failedMaps:1 failedReduces

H_sir_Y

6921人浏览 · 2020-08-14 11:10:58

H_sir_Y · 2020-08-14 11:10:58 发布

报错如下

20/08/14 10:57:43 INFO mapreduce.Job: Job job_1595919753289_4416 failed with state FAILED due to: Task failed task_1595919753289_4416_m_000000
Job failed as tasks failed. failedMaps:1 failedReduces:0 killedMaps:0 killedReduces: 0

20/08/14 10:57:43 INFO mapreduce.Job: Counters: 8
        Job Counters
                Failed map tasks=1
                Launched map tasks=1
                Rack-local map tasks=1
                Total time spent by all maps in occupied slots (ms)=4340
                Total time spent by all reduces in occupied slots (ms)=0
                Total time spent by all map tasks (ms)=4340
                Total vcore-milliseconds taken by all map tasks=4340
                Total megabyte-milliseconds taken by all map tasks=4444160
20/08/14 10:57:43 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead
20/08/14 10:57:43 INFO mapreduce.ExportJobBase: Transferred 0 bytes in 16.7826 seconds (0 bytes/sec)
20/08/14 10:57:43 WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead
20/08/14 10:57:43 INFO mapreduce.ExportJobBase: Exported 0 records.
20/08/14 10:57:43 ERROR mapreduce.ExportJobBase: Export job failed!
20/08/14 10:57:43 ERROR tool.ExportTool: Error during export:
Export job failed!
        at org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:444)
        at org.apache.sqoop.manager.MySQLManager.upsertTable(MySQLManager.java:142)
        at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:86)
        at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:112)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:146)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:182)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:233)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:242)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:251)
shell命令执行错误

查看hive执行日志发现如下报错

2020-08-14 10:57:42,004 ERROR [IPC Server handler 0 on 35343] org.apache.hadoop.mapred.TaskAttemptListenerImpl: Task: attempt_1595919753289_4416_m_000000_0 - exited : java.io.IOException: Can't export data, please check failed map task logs
	at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:121)
	at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:38)
	at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:146)
	at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
	at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:799)
	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:347)
	at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:174)
	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:1875)
	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:168)
Caused by: java.io.IOException: java.sql.BatchUpdateException: Column 'id' cannot be null
	at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:233)
	at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:46)
	at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.write(MapTask.java:670)
	at org.apache.hadoop.mapreduce.task.TaskInputOutputContextImpl.write(TaskInputOutputContextImpl.java:89)
	at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.write(WrappedMapper.java:112)
	at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:89)
	... 10 more
Caused by: java.sql.BatchUpdateException: Column 'id' cannot be null
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1163)
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1778)
	at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1262)
	at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
	at org.apache.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(AsyncSqlOutputFormat.java:231)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'id' cannot be null
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1756)
	... 3 more

2020-08-14 10:57:42,004 INFO [IPC Server handler 0 on 35343] org.apache.hadoop.mapred.TaskAttemptListenerImpl: Diagnostics report from attempt_1595919753289_4416_m_000000_0: Error: java.io.IOException: Can't export data, please check failed map task logs
	at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:121)
	at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:38)
	at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:146)
	at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
	at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:799)
	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:347)
	at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:174)
	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:1875)
	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:168)
Caused by: java.io.IOException: java.sql.BatchUpdateException: Column 'id' cannot be null
	at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:233)
	at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:46)
	at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.write(MapTask.java:670)
	at org.apache.hadoop.mapreduce.task.TaskInputOutputContextImpl.write(TaskInputOutputContextImpl.java:89)
	at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.write(WrappedMapper.java:112)
	at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:89)
	... 10 more
Caused by: java.sql.BatchUpdateException: Column 'id' cannot be null
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1163)
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1778)
	at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1262)
	at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
	at org.apache.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(AsyncSqlOutputFormat.java:231)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'id' cannot be null
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1756)
	... 3 more

2020-08-14 10:57:42,006 INFO [AsyncDispatcher event handler] org.apache.hadoop.mapreduce.v2.app.job.impl.TaskAttemptImpl: Diagnostics report from attempt_1595919753289_4416_m_000000_0: Error: java.io.IOException: Can't export data, please check failed map task logs
	at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:121)
	at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:38)
	at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:146)
	at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
	at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:799)
	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:347)
	at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:174)
	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:1875)
	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:168)
Caused by: java.io.IOException: java.sql.BatchUpdateException: Column 'id' cannot be null
	at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:233)
	at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:46)
	at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.write(MapTask.java:670)
	at org.apache.hadoop.mapreduce.task.TaskInputOutputContextImpl.write(TaskInputOutputContextImpl.java:89)
	at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.write(WrappedMapper.java:112)
	at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:89)
	... 10 more
Caused by: java.sql.BatchUpdateException: Column 'id' cannot be null
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1163)
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1778)
	at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1262)
	at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
	at org.apache.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(AsyncSqlOutputFormat.java:231)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'id' cannot be null
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1756)
	... 3 more

sqoop导出语句如下

/opt/bfd/sqoop/bin/sqoop export --connect {} --username={} --password={} --table mid_yyts_ticket_detail_2050 --update-key id --update-mode allowinsert --export-dir /user/hive/warehouse/ads.db/ads_yyts_ticket_detail_2050 --input-fields-terminated-by '\t' --input-null-string '\\\\N' --input-null-non-string '\\\\N' -m1
""".format(mysql_jdbc_url, mysql_user, mysql_passwd)