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.

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.
	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结果库的字段长度

 

字段对应错误：

--columns 指定的是mysql结果库里面的表字段，而不是hive里面的字段信息，所以--columns指定的字段名一定要和mysql中的表字段保持一致！！！

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-' )

其中的特殊字符指 @ 。