Sqoop 抽数报错： java.io.FileNotFoundException: File does not exist
Sqoop 抽数报错： java.io.FileNotFoundException: File does not exist
一、错误详情
2019-10-17 20:04:49,080 INFO [IPC Server handler 20 on 45158] org.apache.hadoop.mapred.TaskAttemptListenerImpl: Diagnostics report from attempt_1567429685851_474405_m_000001_0: Error: java.lang.RuntimeException:
java.io.FileNotFoundException: File does not exist: /user/hive/warehouse/bgda_hw_stg.db/rs_isdbirthremind_onelife_bak/51424d15ec50cdca-216a29380000000b_1863633016_data.0.
1 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | 2019-10-17 20:04:49,080 INFO [IPC Server handler 20 on 45158] org.apache.hadoop.mapred.TaskAttemptListenerImpl: Diagnostics report from attempt_1567429685851_474405_m_000001_0: Error: java.lang.RuntimeException: <br>java.io.FileNotFoundException: File does not exist: /user/hive/warehouse/bgda_hw_stg.db/rs_isdbirthremind_onelife_bak/51424d15ec50cdca-216a29380000000b_1863633016_data.0. at org.apache.hadoop.hdfs.server.namenode.INodeFile.valueOf(INodeFile.java:66) at org.apache.hadoop.hdfs.server.namenode.INodeFile.valueOf(INodeFile.java:56) at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.getBlockLocationsInt(FSNamesystem.java:2157) at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.getBlockLocations(FSNamesystem.java:2127) at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.getBlockLocations(FSNamesystem.java:2040) at org.apache.hadoop.hdfs.server.namenode.NameNodeRpcServer.getBlockLocations(NameNodeRpcServer.java:583) at org.apache.hadoop.hdfs.server.namenode.AuthorizationProviderProxyClientProtocol.getBlockLocations(AuthorizationProviderProxyClientProtocol.java:94) at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolServerSideTranslatorPB.getBlockLocations(ClientNamenodeProtocolServerSideTranslatorPB.java:377) at org.apache.hadoop.hdfs.protocol.proto.ClientNamenodeProtocolProtos$ClientNamenodeProtocol$2.callBlockingMethod(ClientNamenodeProtocolProtos.java) at org.apache.hadoop.ipc.ProtobufRpcEngine$Server$ProtoBufRpcInvoker.call(ProtobufRpcEngine.java:617) at org.apache.hadoop.ipc.RPC$Server.call(RPC.java:1073) at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2278) at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2274) 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:1924) at org.apache.hadoop.ipc.Server$Handler.run(Server.java:2272) at org.apache.sqoop.mapreduce.CombineFileRecordReader.initNextRecordReader(CombineFileRecordReader.java:165) at org.apache.sqoop.mapreduce.CombineFileRecordReader.nextKeyValue(CombineFileRecordReader.java:71) at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:562) 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:144) at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64) at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:793) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341) at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164) 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:1924) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)Caused by: java.io.FileNotFoundException: File does not exist: /user/hive/warehouse/bgda_hw_stg.db/rs_isdbirthremind_onelife_bak/51424d15ec50cdca-216a29380000000b_1863633016_data.0. at org.apache.hadoop.hdfs.server.namenode.INodeFile.valueOf(INodeFile.java:66) at org.apache.hadoop.hdfs.server.namenode.INodeFile.valueOf(INodeFile.java:56) |
二、解决方法
表名过长，重新修改表名 rs_isdbirthremind_onelife_bak 为 rs_isdbirremd_onelife_bak (缩短表名)
再次抽数，即正常了 ~
三、sqoop 抽数常见错误
日期字段类型不匹配：
hive 里面存储的是 datetime 类型，但是结果库 MySQL 里面设计的是 date 类型
处理方案： 修改为一致即可 (统一为 date，或者统一为 datetime)
字段长度不够：
数据类型 Mysql 结果库里设置的字段（decimal 类型）最大长度为 5，结果数据里面最大数值为 999999，存不进去 则会报错
处理方案：调整 mysql 结果库的字段长度
字段对应错误：
1 | --columns 指定的是mysql结果库里面的表字段，而不是hive里面的字段信息，所以--columns指定的字段名一定要和mysql中的表字段保持一致！！！ |
1 2 3 4 5 6 7 8 9 10 11 | sqoop export \--connect jdbc:mysql://10.11.22.33:3306/report \--username root \--password 1234\--table rs_isd_birth_remind_onelife \--export-dir /user/hive/warehouse/bgda_hw_stg.db/rs_isdbirremd_onelife_bak \--columns t00salesno,contactsId,isdname,birthTime,needBirthRemind,createUser,createTime,updateUser,updateTime \--fields-terminated-by '\001' \--lines-terminated-by '\n' \--input-null-string '\\N' \--input-null-non-string '\\N' |
抽取的数据中含有特殊字符
目前我的处理方案是通过 regexp_replace 函数将特殊字符替换掉: regexp_replace(table.content,'@ ','a-')
其中的特殊字符指 @ 。