hive往mysql迁移数据sqoop报错注意事项

最近在帮同事迁移数据的时候sqoop老是报错，错误也不明显，在网上搜了一圈也没找，然后自己根据错误一步一步进行了排查，最终找到了原因，看到数据、表字段类型以及迁移表名是才恍悟，在做数据同步时一定要心细一些，不然问题五花八门
错误如下：
[root@master ~]# sqoop export --connect “jdbc:mysql://172.23.20.142:3306/tds?useUnicode=true&characterEncoding=utf-8” --username root --password ‘yzf123!@#’ --table ods_jfzx_qy_kzb --export-dir /usr/hive/warehouse/bi.db/cx_11/000000_0 --input-fields-terminated-by ‘\001’
Warning: /opt/cloudera/parcels/CDH-5.14.0-1.cdh5.14.0.p0.24/bin/…/lib/sqoop/…/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
19/10/09 14:46:47 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.14.0
19/10/09 14:46:47 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
19/10/09 14:46:47 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
19/10/09 14:46:47 INFO tool.CodeGenTool: Beginning code generation
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-5.14.0-1.cdh5.14.0.p0.24/jars/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-5.14.0-1.cdh5.14.0.p0.24/lib/hbase/lib/phoenix-4.14.0-cdh5.14.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
19/10/09 14:46:48 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM ods_jfzx_qy_kzb AS t LIMIT 1
19/10/09 14:46:48 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM ods_jfzx_qy_kzb AS t LIMIT 1
19/10/09 14:46:48 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/cloudera/parcels/CDH/lib/hadoop-mapreduce
注: /tmp/sqoop-root/compile/d1ad934cb3f9a4a51947085efa2be4bc/ods_jfzx_qy_kzb.java使用或覆盖了已过时的 API。
注: 有关详细信息, 请使用 -Xlint:deprecation 重新编译。
19/10/09 14:46:48 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/d1ad934cb3f9a4a51947085efa2be4bc/ods_jfzx_qy_kzb.jar
19/10/09 14:46:48 INFO mapreduce.ExportJobBase: Beginning export of ods_jfzx_qy_kzb
19/10/09 14:46:48 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
19/10/09 14:46:49 INFO Configuration.deprecation: mapred.reduce.tasks.speculative.execution is deprecated. Instead, use mapreduce.reduce.speculative
19/10/09 14:46:49 INFO Configuration.deprecation: mapred.map.tasks.speculative.execution is deprecated. Instead, use mapreduce.map.speculative
19/10/09 14:46:49 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
19/10/09 14:46:49 INFO client.RMProxy: Connecting to ResourceManager at master/172.24.10.2:8032
19/10/09 14:46:51 INFO input.FileInputFormat: Total input paths to process : 1
19/10/09 14:46:51 INFO input.FileInputFormat: Total input paths to process : 1
19/10/09 14:46:51 INFO mapreduce.JobSubmitter: number of splits:3
19/10/09 14:46:51 INFO Configuration.deprecation: mapred.map.tasks.speculative.execution is deprecated. Instead, use mapreduce.map.speculative
19/10/09 14:46:51 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1570518235280_0482
19/10/09 14:46:51 INFO impl.YarnClientImpl: Submitted application application_1570518235280_0482
19/10/09 14:46:51 INFO mapreduce.Job: The url to track the job: http://master:8088/proxy/application_1570518235280_0482/
19/10/09 14:46:51 INFO mapreduce.Job: Running job: job_1570518235280_0482
19/10/09 14:46:55 INFO mapreduce.Job: Job job_1570518235280_0482 running in uber mode : false
19/10/09 14:46:55 INFO mapreduce.Job: map 0% reduce 0%
19/10/09 14:46:59 INFO mapreduce.Job: map 100% reduce 0%
19/10/09 14:46:59 INFO mapreduce.Job: Job job_1570518235280_0482 failed with state FAILED due to: Task failed task_1570518235280_0482_m_000001
Job failed as tasks failed. failedMaps:1 failedReduces:0

19/10/09 14:46:59 INFO mapreduce.Job: Counters: 12
Job Counters
Failed map tasks=1
Killed map tasks=2
Launched map tasks=3
Rack-local map tasks=3
Total time spent by all maps in occupied slots (ms)=63184
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=3949
Total vcore-milliseconds taken by all map tasks=3949
Total megabyte-milliseconds taken by all map tasks=8087552
Map-Reduce Framework
CPU time spent (ms)=0
Physical memory (bytes) snapshot=0
Virtual memory (bytes) snapshot=0
19/10/09 14:46:59 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead
19/10/09 14:46:59 INFO mapreduce.ExportJobBase: Transferred 0 bytes in 10.2864 seconds (0 bytes/sec)
19/10/09 14:46:59 INFO mapreduce.ExportJobBase: Exported 0 records.
19/10/09 14:46:59 ERROR tool.ExportTool: Error during export:
Export job failed!
at org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:439)
at org.apache.sqoop.manager.SqlManager.exportTable(SqlManager.java:931)
at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:80)
at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:99)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
通过以上错误看不出来具体错误，查看yarn jobhistory 日志，错误如下：
Error: java.io.IOException: Can’t export data, please check failed map task logs at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:122) at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39) 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: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:1917) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158) Caused by: java.lang.RuntimeException: Can’t parse input data: ‘628350606008631296’ at ods_jfzx_qy_kzb.__loadFromFields(ods_jfzx_qy_kzb.java:1051) at ods_jfzx_qy_kzb.parse(ods_jfzx_qy_kzb.java:914) at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:89) … 10 more Caused by: java.lang.NumberFormatException: For input string: “628350606008631296” at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) at java.lang.Integer.parseInt(Integer.java:583) at java.lang.Integer.valueOf(Integer.java:766) at ods_jfzx_qy_kzb.__loadFromFields(ods_jfzx_qy_kzb.java:963) … 12 more

报的是字段数据类型问题，查了下生产表数据类型以及测试环境表数据类型，在结合提示发现创建表时数据类确实是String类型，而生产上是bigint，测试是string，一大粗心；
执行sqoop脚本时一定要 检查好库名，端口，ip，表名等（我这边表名字就写错了，应该是ods_jfzx_qy_kzb1 而不是ods_jfzx_qy_kzb，因为表结构不一样）；
sqoop export
–connect “jdbc:mysql://172.23.20.142:3306/tds?useUnicode=true&characterEncoding=utf-8”
–username root
–password ‘yzf123!@#’
–table ods_jfzx_qy_kzb1
–export-dir /usr/hive/warehouse/bi.db/cx_11/000000_0
–input-fields-terminated-by ‘\001’
–input-null-string ‘\N’ --input-null-non-string ‘\N’
–m 1

在sqoop迁移数据的时候hive表存储只能是text，其他格式有问题(具体需要待验证)，列的分隔符需要注意默认的是 ‘\001’
分隔符参考：

HIVE-默认分隔符的（linux系统的特殊字符）查看，输入和修改

最后就是hive，mysql表结构顺序，字段大小等保持一直！！！
sqoop相关整理记录

案例：
1、在生厂上查看要导出的数据表结构
desc bi.cx_11
2、导出数据上传到测试环境，在测试环境创建相应表
drop table if exists bi.cx_11;
CREATE TABLE bi.cx_11(
id string,
qyid bigint,
kjnd int,
kjqj int,
batch string,
create_time string,
qymc string,
declare_sta string,
declare_date string,
last_declare_sta_date string,
dec_tax_type string,
declare_type string,
is_make_account string,
remark string,
is_exist_fp_info string,
last_custom_date string,
dzgs_id bigint)
row format delimited
fields terminated by ‘\001’

本地加载数据：
load data local inpath ‘/data/cx_11/000000_0’ overwrite into table bi.cx_11;

sqoop eval
–connect jdbc:mysql://xx.xx.xx.xx:3306/tds
–username root
–password ‘xxxxx!@#’
-e “truncate table ods_jfzx_qy_kzb”

sqoop export
–connect “jdbc:mysql://xx.xx.xx.xx:3306/tds?useUnicode=true&characterEncoding=utf-8”
–username root
–password ‘xxxxx!@#’
–table ods_jfzx_qy_kzb1
–export-dir /usr/hive/warehouse/bi.db/cx_11/000000_0
–input-fields-terminated-by ‘\001’
–input-null-string ‘\N’ --input-null-non-string ‘\N’
–m 1