sqoop 从 hive 导到mysql常遇九问题总结

本帖最后由 desehawk 于 2015-4-21 22:18 编辑
导读

以前以为版本不同，遇到的问题就不同，后来发现，无论是新版本，还是老版本，遇到的问题大部分都是相同的。下面解决问题的方法仅供借鉴
1.拒绝连接的错误表现是什么？
2.表不存在该如何解决？
3.null字段填充符该如何指定？

环境

hive 版本hive-0.11.0
sqoop 版本 sqoop-1.4.4.bin__hadoop-1.0.0

从hive导到mysql

mysql 表：

mysql> desc cps_activation;

+————+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| day | date | NO | MUL | NULL | |
| pkgname | varchar(50) | YES | | NULL | |
| cid | varchar(50) | YES | | NULL | |
| pid | varchar(50) | YES | | NULL | |
| activation | int(11) | YES | | NULL | |
+————+————-+——+—–+———+—————-+
6 rows in set (0.01 sec)

hive表

hive> desc active;
OK
id int None
day string None
pkgname string None
cid string None
pid string None
activation int None

测试链接成功

[hadoop@hs11 ~]sqoop list-databases –connect jdbc:mysql://localhost:3306/ –username root –password admin
Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
13/08/20 16:42:26 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/08/20 16:42:26 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
information_schema
easyhadoop
mysql
test
[hadoop@hs11 ~]$ sqoop list-databases –connect jdbc:mysql://localhost:3306/test –username root –password admin
Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
13/08/20 16:42:40 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/08/20 16:42:40 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
information_schema
easyhadoop
mysql
test
[hadoop@hs11 ~]$ sqoop list-tables –connect jdbc:mysql://localhost:3306/test –username root –password admin
Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
13/08/20 16:42:54 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/08/20 16:42:54 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
active

[hadoop@hs11 ~]$  sqoop create-hive-table –connect jdbc:mysql://localhost:3306/test –table active –username root –password admin –hive-table test
Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
13/08/20 16:57:04 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/08/20 16:57:04 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
13/08/20 16:57:04 INFO tool.BaseSqoopTool: delimiters with –fields-terminated-by, etc.
13/08/20 16:57:04 WARN tool.BaseSqoopTool: It seems that you’ve specified at least one of following:
13/08/20 16:57:04 WARN tool.BaseSqoopTool:    –hive-home
13/08/20 16:57:04 WARN tool.BaseSqoopTool:    –hive-overwrite
13/08/20 16:57:04 WARN tool.BaseSqoopTool:    –create-hive-table
13/08/20 16:57:04 WARN tool.BaseSqoopTool:    –hive-table
13/08/20 16:57:04 WARN tool.BaseSqoopTool:    –hive-partition-key
13/08/20 16:57:04 WARN tool.BaseSqoopTool:    –hive-partition-value
13/08/20 16:57:04 WARN tool.BaseSqoopTool:    –map-column-hive
13/08/20 16:57:04 WARN tool.BaseSqoopTool: Without specifying parameter –hive-import. Please note that
13/08/20 16:57:04 WARN tool.BaseSqoopTool: those arguments will not be used in this session. Either
13/08/20 16:57:04 WARN tool.BaseSqoopTool: specify –hive-import to apply them correctly or remove them
13/08/20 16:57:04 WARN tool.BaseSqoopTool: from command line to remove this warning.
13/08/20 16:57:04 INFO tool.BaseSqoopTool: Please note that –hive-home, –hive-partition-key,
13/08/20 16:57:04 INFO tool.BaseSqoopTool:    hive-partition-value and –map-column-hive options are
13/08/20 16:57:04 INFO tool.BaseSqoopTool:    are also valid for HCatalog imports and exports
13/08/20 16:57:04 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/08/20 16:57:05 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `active` AS t LIMIT 1
13/08/20 16:57:05 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `active` AS t LIMIT 1
13/08/20 16:57:05 WARN hive.TableDefWriter: Column day had to be cast to a less precise type in Hive
13/08/20 16:57:05 INFO hive.HiveImport: Loading uploaded data into Hive

1、拒绝连接

[hadoop@hs11 ~]$ sqoop export –connect jdbc:mysql://localhost/test –username root –password admin –table test –export-dir /user/hive/warehouse/actmp
Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
13/08/21 09:14:07 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/08/21 09:14:07 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/08/21 09:14:07 INFO tool.CodeGenTool: Beginning code generation
13/08/21 09:14:07 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/08/21 09:14:07 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/08/21 09:14:07 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /home/hadoop/hadoop-1.1.2
Note: /tmp/sqoop-hadoop/compile/0b5cae714a00b3940fb793c3694408ac/test.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/08/21 09:14:08 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/0b5cae714a00b3940fb793c3694408ac/test.jar
13/08/21 09:14:08 INFO mapreduce.ExportJobBase: Beginning export of test
13/08/21 09:14:09 INFO input.FileInputFormat: Total input paths to process : 1
13/08/21 09:14:09 INFO input.FileInputFormat: Total input paths to process : 1
13/08/21 09:14:09 INFO util.NativeCodeLoader: Loaded the native-hadoop library
13/08/21 09:14:09 WARN snappy.LoadSnappy: Snappy native library not loaded
13/08/21 09:14:10 INFO mapred.JobClient: Running job: job_201307251523_0059
13/08/21 09:14:11 INFO mapred.JobClient: map 0% reduce 0%
13/08/21 09:14:20 INFO mapred.JobClient: Task Id : attempt_201307251523_0059_m_000000_0, Status : FAILED
java.io.IOException: com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.ConnectException
MESSAGE: Connection refused

STACKTRACE:

java.net.ConnectException: Connection refused
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:351)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:213)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:200)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
at java.net.Socket.connect(Socket.java:529)
at java.net.Socket.connect(Socket.java:478)
at java.net.Socket.<init>(Socket.java:375)
at java.net.Socket.<init>(Socket.java:218)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:271)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2771)
at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:185)
at org.apache.sqoop.mapreduce.db.DBConfiguration.getConnection(DBConfiguration.java:294)
at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.<init>(AsyncSqlRecordWriter.java:76)
at org.apache.sqoop.mapreduce.ExportOutputFormat$ExportRecordWriter.<init>(ExportOutputFormat.java:95)
at org.apache.sqoop.mapreduce.ExportOutputFormat.getRecordWriter(ExportOutputFormat.java:77)
at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.<init>(MapTask.java:628)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:753)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1149)
at org.apache.hadoop.mapred.Child.main(Child.java:249)

** END NESTED EXCEPTION **

Last packet sent to the server was 1 ms ago.
at org.apache.sqoop.mapreduce.ExportOutputFormat.getRecordWriter(ExportOutputFormat.java:79)
at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.<init>(MapTask.java:628)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:753)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1149)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.ConnectException
MESSAGE: Connection refused

mysql 用户权限问题
mysql> show grants;
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root’@'%’ IDENTIFIED BY PASSWORD ‘*4ACFE3202A5FF5CF467898FC58AAB1D615029441′ WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> create table test (mkey varchar(30),pkg varchar(50),cid varchar(20),pid varchar(50),count int,primary key(mkey,pkg,cid,pid) );
alter ignore table cps_activation add unique index_day_pkgname_cid_pid (`day`,`pkgname`,`cid`,`pid`);
Query OK, 0 rows affected (0.03 sec)

2. 表不存在
===========
[hadoop@hs11 ~]$ sqoop export –connect jdbc:mysql://10.10.20.11/test –username root  –password admin –table test –export-dir /user/hive/warehouse/actmp
Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
13/08/21 09:16:26 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/08/21 09:16:26 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/08/21 09:16:26 INFO tool.CodeGenTool: Beginning code generation
13/08/21 09:16:27 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/08/21 09:16:27 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/08/21 09:16:27 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /home/hadoop/hadoop-1.1.2
Note: /tmp/sqoop-hadoop/compile/74d18a91ec141f2feb777dc698bf7eb4/test.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/08/21 09:16:28 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/74d18a91ec141f2feb777dc698bf7eb4/test.jar
13/08/21 09:16:28 INFO mapreduce.ExportJobBase: Beginning export of test
13/08/21 09:16:29 INFO input.FileInputFormat: Total input paths to process : 1
13/08/21 09:16:29 INFO input.FileInputFormat: Total input paths to process : 1
13/08/21 09:16:29 INFO util.NativeCodeLoader: Loaded the native-hadoop library
13/08/21 09:16:29 WARN snappy.LoadSnappy: Snappy native library not loaded
13/08/21 09:16:29 INFO mapred.JobClient: Running job: job_201307251523_0060
13/08/21 09:16:30 INFO mapred.JobClient:  map 0% reduce 0%
13/08/21 09:16:38 INFO mapred.JobClient: Task Id : attempt_201307251523_0060_m_000000_0, Status : FAILED
java.io.IOException: Can’t export data, please check task tracker logs
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
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:764)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1149)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: java.util.NoSuchElementException
at java.util.AbstractList$Itr.next(AbstractList.java:350)
at test.__loadFromFields(test.java:252)
at test.parse(test.java:201)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
… 10 more

导出数据到MySQL，当然数据库表要先存在，否则会报错
此错误的原因为sqoop解析文件的字段与MySql数据库的表的字段对应不上造成的。因此需要在执行的时候给sqoop增加参数，告诉sqoop文件的分隔符，使它能够正确的解析文件字段。hive默认的字段分隔符为’\001′
===========

3. null字段填充符需指定

没有指定null字段分隔符，导致错位。
[hadoop@hs11 ~]$ sqoop export –connect jdbc:mysql://10.10.20.11/test –username root  –password admin –table test –export-dir /user/hive/warehouse/actmp –input-fields-tminated-by ‘\001′
Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
13/08/21 09:21:07 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/08/21 09:21:07 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/08/21 09:21:07 INFO tool.CodeGenTool: Beginning code generation
13/08/21 09:21:07 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/08/21 09:21:07 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/08/21 09:21:07 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /home/hadoop/hadoop-1.1.2
Note: /tmp/sqoop-hadoop/compile/04d183c9e534cdb8d735e1bdc4be3deb/test.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/08/21 09:21:08 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/04d183c9e534cdb8d735e1bdc4be3deb/test.jar
13/08/21 09:21:08 INFO mapreduce.ExportJobBase: Beginning export of test
13/08/21 09:21:09 INFO input.FileInputFormat: Total input paths to process : 1
13/08/21 09:21:09 INFO input.FileInputFormat: Total input paths to process : 1
13/08/21 09:21:09 INFO util.NativeCodeLoader: Loaded the native-hadoop library
13/08/21 09:21:09 WARN snappy.LoadSnappy: Snappy native library not loaded
13/08/21 09:21:10 INFO mapred.JobClient: Running job: job_201307251523_0061
13/08/21 09:21:11 INFO mapred.JobClient:  map 0% reduce 0%
13/08/21 09:21:17 INFO mapred.JobClient:  map 25% reduce 0%
13/08/21 09:21:19 INFO mapred.JobClient:  map 50% reduce 0%
13/08/21 09:21:21 INFO mapred.JobClient: Task Id : attempt_201307251523_0061_m_000001_0, Status : FAILED
java.io.IOException: Can’t export data, please check task tracker logs
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
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:764)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1149)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: java.lang.NumberFormatException: For input string: “665A5FFA-32C9-9463-1943-840A5FEAE193″
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.Integer.parseInt(Integer.java:458)
at java.lang.Integer.valueOf(Integer.java:554)
at test.__loadFromFields(test.java:264)
at test.parse(test.java:201)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
… 10 more
===========

4.成功

[hadoop@hs11 ~]$ sqoop export –connect jdbc:mysql://10.10.20.11/test –username root  –password admin –table test –export-dir /user/hive/warehouse/actmp –input-fields-terminated-by ‘\001′ –input-null-string ‘\\N’ –input-null-non-string ‘\\N’
Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
13/08/21 09:36:13 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/08/21 09:36:13 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/08/21 09:36:13 INFO tool.CodeGenTool: Beginning code generation
13/08/21 09:36:13 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/08/21 09:36:13 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/08/21 09:36:13 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /home/hadoop/hadoop-1.1.2
Note: /tmp/sqoop-hadoop/compile/e22d31391498b790d799897cde25047d/test.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/08/21 09:36:14 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/e22d31391498b790d799897cde25047d/test.jar
13/08/21 09:36:14 INFO mapreduce.ExportJobBase: Beginning export of test
13/08/21 09:36:15 INFO input.FileInputFormat: Total input paths to process : 1
13/08/21 09:36:15 INFO input.FileInputFormat: Total input paths to process : 1
13/08/21 09:36:15 INFO util.NativeCodeLoader: Loaded the native-hadoop library
13/08/21 09:36:15 WARN snappy.LoadSnappy: Snappy native library not loaded
13/08/21 09:36:16 INFO mapred.JobClient: Running job: job_201307251523_0064
13/08/21 09:36:17 INFO mapred.JobClient:  map 0% reduce 0%
13/08/21 09:36:23 INFO mapred.JobClient:  map 25% reduce 0%
13/08/21 09:36:25 INFO mapred.JobClient:  map 100% reduce 0%
13/08/21 09:36:27 INFO mapred.JobClient: Job complete: job_201307251523_0064
13/08/21 09:36:27 INFO mapred.JobClient: Counters: 18
13/08/21 09:36:27 INFO mapred.JobClient: Job Counters
13/08/21 09:36:27 INFO mapred.JobClient:    SLOTS_MILLIS_MAPS=13151
13/08/21 09:36:27 INFO mapred.JobClient:    Total time spent by all reduces waiting after reserving slots (ms)=0
13/08/21 09:36:27 INFO mapred.JobClient:    Total time spent by all maps waiting after reserving slots (ms)=0
13/08/21 09:36:27 INFO mapred.JobClient:    Rack-local map tasks=2
13/08/21 09:36:27 INFO mapred.JobClient:    Launched map tasks=4
13/08/21 09:36:27 INFO mapred.JobClient:    SLOTS_MILLIS_REDUCES=0
13/08/21 09:36:27 INFO mapred.JobClient: File Output Format Counters
13/08/21 09:36:27 INFO mapred.JobClient:    Bytes Written=0
13/08/21 09:36:27 INFO mapred.JobClient: FileSystemCounters
13/08/21 09:36:27 INFO mapred.JobClient:    HDFS_BYTES_READ=1519
13/08/21 09:36:27 INFO mapred.JobClient:    FILE_BYTES_WRITTEN=234149
13/08/21 09:36:27 INFO mapred.JobClient: File Input Format Counters
13/08/21 09:36:27 INFO mapred.JobClient:    Bytes Read=0
13/08/21 09:36:27 INFO mapred.JobClient: Map-Reduce Framework
13/08/21 09:36:27 INFO mapred.JobClient:    Map input records=6
13/08/21 09:36:27 INFO mapred.JobClient:    Physical memory (bytes) snapshot=663863296
13/08/21 09:36:27 INFO mapred.JobClient:    Spilled Records=0
13/08/21 09:36:27 INFO mapred.JobClient:    CPU time spent (ms)=3720
13/08/21 09:36:27 INFO mapred.JobClient:    Total committed heap usage (bytes)=2013790208
13/08/21 09:36:27 INFO mapred.JobClient:    Virtual memory (bytes) snapshot=5583151104
13/08/21 09:36:27 INFO mapred.JobClient:    Map output records=6
13/08/21 09:36:27 INFO mapred.JobClient:    SPLIT_RAW_BYTES=571
13/08/21 09:36:27 INFO mapreduce.ExportJobBase: Transferred 1.4834 KB in 12.1574 seconds (124.9446 bytes/sec)
13/08/21 09:36:27 INFO mapreduce.ExportJobBase: Exported 6 records.

———-

5. mysql字符串长度定义太短，存不下

java.io.IOException: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column ‘pid’ at row 1
at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.close(AsyncSqlRecordWriter.java:192)
at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.close(MapTask.java:651)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:766)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1149)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column ‘pid’ at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2983)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:882)
at org.apache.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(AsyncSqlOutputFormat.java:233)

———————-
6.日期格式问题

mysql date日期格式，hive中字符串必须是yyyy-mm-dd, 我原来使用yyyymmdd,报下面的错误。

13/08/21 17:42:44 INFO mapred.JobClient: Task Id : attempt_201307251523_0079_m_000000_1, Status : FAILED
java.io.IOException: Can’t export data, please check task tracker logs
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
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:764)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1149)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: java.lang.IllegalArgumentException
at java.sql.Date.valueOf(Date.java:138)
at cps_activation.__loadFromFields(cps_activation.java:308)
at cps_activation.parse(cps_activation.java:255)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
… 10 more

———————-
7. 字段对不上或字段类型不一致

Caused by: java.lang.NumberFormatException: For input string: “06701A4A-0808-E9A8-0D28-A8020B494E37″
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.Integer.parseInt(Integer.java:458)
at java.lang.Integer.valueOf(Integer.java:554)
at test.__loadFromFields(test.java:264)
at test.parse(test.java:201)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
… 10 more

desehawk · 发表于 2015-4-21 22:15:01

本帖最后由 desehawk 于 2015-4-21 22:18 编辑
进一步补充：

问题提示：

[hadoop@namenode conf]$ sqoop
/home/hadoop/sqoop-1.4.4/sqoop-1.4.4.bin__hadoop-1.0.0/conf/sqoop-env.sh: line 29: export: `/home/hadoop/hbase/hbase-0.94.12': not a valid identifier
/home/hadoop/sqoop-1.4.4/sqoop-1.4.4.bin__hadoop-1.0.0/conf/sqoop-env.sh: line 32: export: `/home/hadoop/hive-0.11.0': not a valid identifier
Warning: /usr/lib/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: $HADOOP_HOME is deprecated.

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/sqoop/Sqoop
Caused by: java.lang.ClassNotFoundException: org.apache.sqoop.Sqoop
at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
Could not find the main class: org.apache.sqoop.Sqoop. Program will exit.

解决：

核查了sqoop环境的设置，在网上找了好久，原来原因是在设置好sqoop相关环境之后，需要将sqoop-1.4.4.jar包拷贝到$HADOOP_HOME/lib中。

[hadoop@namenode sqoop-1.4.4.bin__hadoop-1.0.0]$ cp sqoop-1.4.4.jar  /home/hadoop/hadoop/hadoop-1.2.1/lib/
重新运行 sqoop help问题解决。
###########################################################

使用sqoop将hive中的表数据导入到mysql数据库表中，错误解决

[hadoop@Master bin]$ sqoop export --connect jdbc:mysql://localhost:3306/test --username dyh --password 000000 --table users --export-dir /user/hive/warehouse/users/part-m-00000 --input-fields-terminated-by '\0001'
Warning: /usr/lib/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: $HADOOP_HOME is deprecated.

13/12/12 19:50:38 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/12/12 19:50:38 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/12/12 19:50:38 INFO tool.CodeGenTool: Beginning code generation
13/12/12 19:50:38 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `users` AS t LIMIT 1
13/12/12 19:50:38 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `users` AS t LIMIT 1
13/12/12 19:50:38 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/hadoop
Note: /tmp/sqoop-hadoop/compile/9731783979d46a3414a9f86d700bec33/users.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/12/12 19:50:39 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/9731783979d46a3414a9f86d700bec33/users.jar
13/12/12 19:50:39 INFO mapreduce.ExportJobBase: Beginning export of users
13/12/12 19:50:41 INFO input.FileInputFormat: Total input paths to process : 1
13/12/12 19:50:41 INFO input.FileInputFormat: Total input paths to process : 1
13/12/12 19:50:41 INFO util.NativeCodeLoader: Loaded the native-hadoop library
13/12/12 19:50:41 WARN snappy.LoadSnappy: Snappy native library not loaded
13/12/12 19:50:42 INFO mapred.JobClient: Running job: job_201312051716_0034
13/12/12 19:50:43 INFO mapred.JobClient:  map 0% reduce 0%
13/12/12 19:50:51 INFO mapred.JobClient:  map 25% reduce 0%
13/12/12 19:50:53 INFO mapred.JobClient:  map 50% reduce 0%
13/12/12 19:50:58 INFO mapred.JobClient: Task Id : attempt_201312051716_0034_m_000002_0, Status : FAILED
java.io.IOException: java.sql.SQLException: Access denied for user 'dyh'@'localhost' (using password: YES)
      at org.apache.sqoop.mapreduce.ExportOutputFormat.getRecordWriter(ExportOutputFormat.java:79)
      at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.<init>(MapTask.java:628)
      at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:753)
      at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
      at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
      at java.security.AccessController.doPrivileged(Native Method)
      at javax.security.auth.Subject.doAs(Subject.java:396)
      at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1136)
      at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: java.sql.SQLException: Access denied for user 'dyh'@'localhost' (using password: YES)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927)
      at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1709)
      at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252)
      at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2486)
      at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2519)
      at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304)
      at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
      at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
      at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
      at java.sql.DriverManager.getConnection(DriverManager.java:582)
      at java.sql.DriverManager.getConnection(DriverManager.java:185)
      at org.apache.sqoop.mapreduce.db.DBConfiguration.getConnection(DBConfiguration.java:294)
      at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.<init>(AsyncSqlRecordWriter.java:76)
      at org.apache.sqoop.mapreduce.ExportOutputFormat$ExportRecordWriter.<init>(ExportOutputFormat.java:95)
      at org.apache.sqoop.mapreduce.ExportOutputFormat.getRecordWriter(ExportOutputFormat.java:77)
      ... 8 more

13/12/12 19:50:58 INFO mapred.JobClient: Task Id : attempt_201312051716_0034_m_000003_0, Status : FAILED
java.io.IOException: java.sql.SQLException: Access denied for user 'dyh'@'localhost' (using password: YES)
      at org.apache.sqoop.mapreduce.ExportOutputFormat.getRecordWriter(ExportOutputFormat.java:79)
      at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.<init>(MapTask.java:628)
      at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:753)
      at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
      at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
      at java.security.AccessController.doPrivileged(Native Method)
      at javax.security.auth.Subject.doAs(Subject.java:396)
      at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1136)
      at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: java.sql.SQLException: Access denied for user 'dyh'@'localhost' (using password: YES)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927)
      at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1709)
      at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252)
      at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2486)
      at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2519)
      at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304)
      at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
      at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
      at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
      at java.sql.DriverManager.getConnection(DriverManager.java:582)
      at java.sql.DriverManager.getConnection(DriverManager.java:185)
      at org.apache.sqoop.mapreduce.db.DBConfiguration.getConnection(DBConfiguration.java:294)
      at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.<init>(AsyncSqlRecordWriter.java:76)
      at org.apache.sqoop.mapreduce.ExportOutputFormat$ExportRecordWriter.<init>(ExportOutputFormat.java:95)
      at org.apache.sqoop.mapreduce.ExportOutputFormat.getRecordWriter(ExportOutputFormat.java:77)
      ... 8 more

13/12/12 19:51:01 INFO mapred.JobClient:  map 75% reduce 0%
13/12/12 19:51:06 INFO mapred.JobClient: Task Id : attempt_201312051716_0034_m_000003_1, Status : FAILED
java.io.IOException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
      at org.apache.sqoop.mapreduce.ExportOutputFormat.getRecordWriter(ExportOutputFormat.java:79)
      at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.<init>(MapTask.java:628)
      at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:753)
      at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
      at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
      at java.security.AccessController.doPrivileged(Native Method)
      at javax.security.auth.Subject.doAs(Subject.java:396)
      at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1136)
      at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
      at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:357)
      at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2482)
      at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2519)
      at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304)
      at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
      at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
      at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
      at java.sql.DriverManager.getConnection(DriverManager.java:582)
      at java.sql.DriverManager.getConnection(DriverManager.java:185)
      at org.apache.sqoop.mapreduce.db.DBConfiguration.getConnection(DBConfiguration.java:294)
      at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.<init>(AsyncSqlRecordWriter.java:76)
      at org.apache.sqoop.mapreduce.ExportOutputFormat$ExportRecordWriter.<init>(ExportOutputFormat.java:95)
      at org.apache.sqoop.mapreduce.ExportOutputFormat.getRecordWriter(ExportOutputFormat.java:77)
      ... 8 more
Caused by: java.net.ConnectException: Connection refused
      at java.net.PlainSocketImpl.socketConnect(Native Method)
      at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
      at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
      at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
      at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
      at java.net.Socket.connect(Socket.java:519)
      at java.net.Socket.connect(Socket.java:469)
      at java.net.Socket.<init>(Socket.java:366)
      at java.net.Socket.<init>(Socket.java:209)
      at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:259)
      at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:307)
      ... 26 more

13/12/12 19:51:10 INFO mapred.JobClient:  map 100% reduce 0%
13/12/12 19:51:11 INFO mapred.JobClient: Job complete: job_201312051716_0034
13/12/12 19:51:11 INFO mapred.JobClient: Counters: 19
13/12/12 19:51:11 INFO mapred.JobClient: Job Counters
13/12/12 19:51:11 INFO mapred.JobClient:    SLOTS_MILLIS_MAPS=42036
13/12/12 19:51:11 INFO mapred.JobClient:    Total time spent by all reduces waiting after reserving slots (ms)=0
13/12/12 19:51:11 INFO mapred.JobClient:    Total time spent by all maps waiting after reserving slots (ms)=0
13/12/12 19:51:11 INFO mapred.JobClient:    Rack-local map tasks=6
13/12/12 19:51:11 INFO mapred.JobClient:    Launched map tasks=7
13/12/12 19:51:11 INFO mapred.JobClient:    Data-local map tasks=1
13/12/12 19:51:11 INFO mapred.JobClient:    SLOTS_MILLIS_REDUCES=0
13/12/12 19:51:11 INFO mapred.JobClient: File Output Format Counters
13/12/12 19:51:11 INFO mapred.JobClient:    Bytes Written=0
13/12/12 19:51:11 INFO mapred.JobClient: FileSystemCounters
13/12/12 19:51:11 INFO mapred.JobClient:    HDFS_BYTES_READ=759
13/12/12 19:51:11 INFO mapred.JobClient:    FILE_BYTES_WRITTEN=129632
13/12/12 19:51:11 INFO mapred.JobClient: File Input Format Counters
13/12/12 19:51:11 INFO mapred.JobClient:    Bytes Read=0
13/12/12 19:51:11 INFO mapred.JobClient: Map-Reduce Framework
13/12/12 19:51:11 INFO mapred.JobClient:    Map input records=3
13/12/12 19:51:11 INFO mapred.JobClient:    Physical memory (bytes) snapshot=327516160
13/12/12 19:51:11 INFO mapred.JobClient:    Spilled Records=0
13/12/12 19:51:11 INFO mapred.JobClient:    CPU time spent (ms)=5180
13/12/12 19:51:11 INFO mapred.JobClient:    Total committed heap usage (bytes)=127205376
13/12/12 19:51:11 INFO mapred.JobClient:    Virtual memory (bytes) snapshot=2333941760
13/12/12 19:51:11 INFO mapred.JobClient:    Map output records=3
13/12/12 19:51:11 INFO mapred.JobClient:    SPLIT_RAW_BYTES=661
13/12/12 19:51:11 INFO mapreduce.ExportJobBase: Transferred 759 bytes in 30.8765 seconds (24.5818 bytes/sec)
13/12/12 19:51:11 INFO mapreduce.ExportJobBase: Exported 3 records.

问题描述：

将数据从hive中导出到mysql过程中，报如上错误，但最终的执行结果没有问题，数据都能正常的导入到mysql数据库中，只是报异常太让人不爽了。

需要说明的是，同样的一条命令（如上），有时不会出现如下错误：

“java.io.IOException: java.sql.SQLException: Access denied for user 'dyh'@'localhost' (using password: YES)”

这个错是有时有，有时无，经分析发现，我在执行这条命令的时候，没有加 "-m" 参数，默认该作业会使用4个map任务来执行，当遇到数据库与map

不在同一台机器上时，就会报错，因数据量很少，只有几条，所以仅仅一个map任务（与数据库在同一台的map任务）就可以全部完成，所以才会出现报错，但结果没问题。

解决办法：

将jdbc:mysql://localhost:3306/test  中的localhost 改为ip地址即可。