Sqoop导出至Mysql遇到的问题

一、软件版本
mysql:5.7
sqoop:1.4.6-cdh5.7.0

二、数据需求
需要将hdfs上的accounts文件夹(/loudacre/accounts)数据导入到mysql数据库loudacre中的accouts数据表
accounts文件夹截图如下：

/loudacre/accounts.png

accounts表结构

accounts表结构.png

三、解决过程
1、执行脚本

sqoop export \
--connect jdbc:mysql://192.168.1.101:3306/loudacre \
--username ****** \
--password ****** \
--table accounts \
--export-dir /loudacre/accounts \
--fields-terminated-by ',' \
--m 1 

2、报错内容

2019-10-28 22:56:36,185 ERROR [main] org.apache.sqoop.mapreduce.TextExportMapper: Exception: 
java.lang.RuntimeException: Can't parse input data: '\N'
    at accounts_backup.__loadFromFields(accounts_backup.java:691)
    at accounts_backup.parse(accounts_backup.java:584)
    at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:89)
    at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
    at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)

3、问题解决过程
a:查看原始文件内容

1,2008-10-23 16:05:05.0,\N,Donald,Becton,2275 Washburn Street,Oakland,CA,94660,5100032418,2014-03-18 13:29:47.0,2014-03-18 13:29:47.0
2,2008-11-12 03:00:01.0,\N,Donna,Jones,3885 Elliott Street,San Francisco,CA,94171,4150835799,2014-03-18 13:29:47.0,2014-03-18 13:29:47.0
3,2008-12-21 09:19:50.0,\N,Dorthy,Chalmers,4073 Whaley Lane,San Mateo,CA,94479,6506877757,2014-03-18 13:29:47.0,2014-03-18 13:29:47.0
4,2008-11-28 00:08:09.0,\N,Leila,Spencer,1447 Ross Street,San Mateo,CA,94444,6503198619,2014-03-18 13:29:47.0,2014-03-18 13:29:47.0
5,2008-11-15 23:06:06.0,\N,Anita,Laughlin,2767 Hill Street,Richmond,CA,94872,5107754354,2014-03-18 13:29:47.0,2014-03-18 13:29:47.0

b:分析过程
（1）文本数据有空字段'\N'，
（2）调整脚本

sqoop export \
--connect jdbc:mysql://192.168.1.101:3306/loudacre \
--username ****** \
--password ****** \
--table accounts \
--export-dir /loudacre/accounts \
--input-null-non-string ‘\\N’ \
--fields-terminated-by ',' \
--m 1 

（3）报错依然存在
（4）继续调整脚本

sqoop export \
--connect jdbc:mysql://192.168.1.101:3306/loudacre \
--username root \
--password 123456 \
--table accounts_backup \
--export-dir /loudacre/accounts \
--input-null-string "\\\\N" \
--input-null-non-string "\\\\N" \
--fields-terminated-by ',' \
--m 1 \

（5）导入成功
共导入129,761条记录

部分记录截图.png

四、参考资料
1、http://www.itkeyword.com/doc/77196095554496x732/sqoop-export-is-failing-when-i-have-n-as-data