sqoop 从 hive 导到mysql遇到的问题

最新推荐文章于 2024-09-24 20:42:12 发布
最新推荐文章于 2024-09-24 20:42:12 发布
阅读量1.8w 收藏 8
点赞数 2
分类专栏： sqoop hive
hive 同时被 2 个专栏收录
10 篇文章 1 订阅
订阅专栏
sqoop
8 篇文章 0 订阅
订阅专栏

环境

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
驰驰的老爸
关注
专栏目录
sqoop导出到mysql数据不一致_Sqoop export 数据表字段不一致问题
weixin_28867991的博客
02-10 2408
今天从hdfs中导数据到mysql，遇到如下错误。com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'is_removed' at row 1 at最终发现是因为hdfs中数据表字段是46个，而本地数据表字段是48个，比hdfs中多了两个，可能导致字段映射错误，把别的字段误解析成is_remo...
sqoop mysql 拒绝连接_Sqoop 连接MySQL报错：拒绝连接 (Connection refused)
weixin_39849671的博客
01-19 3430
MarsJ老师，看你的Sqoop视频，按照你视频里的步骤一步步实现的，但是准备用sqoop连接mysql时出现错误。困惑了很久，还请老师解惑，给予指导。@MarsJlog：[root@bigdata opt]# ls -lt /opt/sqoop-1.4.6/lib/mysql-connector-java-5.1.45-bin.jar-rwxrwxrwx. 1 root root 999810 ...
解决 Sqoop 导入 Hive 时时间字段精度丢失问题
最新发布
m0_70691645的博客
09-24 1021
在 Sqoop 导入数据时，可以指定 Hive 中的目标表字段类型。例如，可以在 Sqoop 导入时明确将 MySQL 的。通过正确处理时间字段类型映射，可以避免 Hive 中精度丢失的问题，从而确保导入的数据精确和可用。参数指定字段的类型映射，强制 MySQL 的。： 可以在 Sqoop 命令中使用。类型，这样可以保留完整的时间信息。字段转换为 Hive 支持的。字段映射为 Hive 中的。
将hive中的表使用sqoop导出到mysql数据库中（报错与问题解决）
JJBOOM425的博客
11-22 2657
今天在hue上将数据统计了一下，然后需要导出到mysql中给别人进行展示。由于是第一次在这里导出数据，所以出了一些问题，这里总结一下解决思路与解决办法。 一、首先先在mysql中创建导出的表 这里要确认字段属性与hive中的一致。 CREATE TABLE `unperceived` ( `humanid` varchar(50) NOT NULL, `credentialsnum...
sqoop增量导入及导入hive中分隔符自定义指定
u010215845的博客
01-14 5233
核心参数 –check-column  用来指定一些列，这些列在增量导入时用来检查这些数据是否作为增量数据进行导入，和关系型数据库中的自增字段及时间戳类似.  注意:这些被指定的列的类型不能使任意字符类型，如char、varchar等类型都是不可以的，同时–check-column可以去指定多个列–incremental  用来指定增量导入的模式，两种模式分别为Append和Lastm
sqoop 从 hive 导到mysql遇到的问题.docx
01-12
在将数据从 Hive 导入到 MySQL 时，Sqoop 可能会遇到一些问题。例如，在描述文件中，Sqoop 的版本为 1.4.4，Hadoop 的版本为 1.0.0，Hive 的版本为 0.11.0。 Sqoop 在连接 MySQL 时，需要指定 JDBC 连接器的路径，...
sqoop mysql date_Sqoop_ 从 hive 导到mysql常遇九问题总结(转)
weixin_31720227的博客
01-19 587
以前以为版本不同，遇到的问题就不同，后来发现，无论是新版本，还是老版本，遇到的问题大部分都是相同的。下面解决问题的方法仅供借鉴1.拒绝连接的错误表现是什么？2.表不存在该如何解决？3.null字段填充符该如何指定？环境hive 版本hive-0.11.0sqoop 版本 sqoop-1.4.4.bin__hadoop-1.0.0从hive导到mysqlmysql 表mysql> desc c...
sqoop从mysql到hive的时间字段问题
01-07
一、问题发现与分析 ...经过对比：sqoop在转换MySQL的datatime字段类型为hive的string时会出现问题：默认先转为对应时间戳，再转换为北京市区时间，就会使时间多8小时。 解决办法有两个： 1、在sql里将时间字段转换为
2、sqoop导入（RMDB-mysql、sybase到HDFS-hive）
05-29
在本主题中，我们将深入探讨 Sqoop 的导入功能，特别是如何将数据从 MySQL 和 Sybase 数据库导入到 HDFS，并进一步加载到 Hive 表中。 ### 一、Sqoop 导入介绍 1. **语法结构**： Sqoop 导入数据的基本语法如下：...
sqoop到数据到mysql报错_hadoop sqoop 同步hive数据到mysql报错 classnotfound
weixin_29893445的博客
01-19 314
今天在CDH5.4.4 环境下，用sqoop 导出mysql数据，然后再导入hive，成功用sqoop同步hive数据到mysql失败，求大神答疑：1.hive下 有一个表 testa mysql下有一个表testa 结构一样2.版本 hadoop2.6 CDH5.4.4 sqoop1.4.5-CDH5.4.4 JDK1.7 MySQL 5.53.导数命令为sqoop ex...
sqoop从hive导入到外部数据库mysql报错：Job failed as tasks failed. failedMaps:1 failedReduces:0 killedMaps:0 kill
luzhiji的博客
03-25 526
我的hive的这个表的数据格式和mysql表里的多不上，导致出错。查找日志然而也并没有真正找得到解决的办法，经过调整hive的表的格式，然后就好了。这个困扰了我很久的问题终于找到了。后来我终于找到原因了。
sqoop到数据到mysql报错_Sqoop 导出 hive 数据到 MySQL 报错?
weixin_34593927的博客
01-19 347
可以连接到mysql查到里面有哪些表[hadoop@nn1 ~]$ sqoop list-tables --connect jdbc:mysql://127.0.0.1:3306/etl_db --username etl --password 000000……20/06/28 21:14:53 INFO manager.MySQLManager: Preparing to use a MySQL...
sqoop导出hive表到mysql报错
ChengYanan的博客
05-31 807
hive表使用sqoop导入mysql报错，查询原因是分区字段出现了NULL，而分区字段又是mysql表中的主键。 查看HDFS上出现一个date=__HIVE_DEFAULT_PARTITION__的分区，就是分区字段为NULL的那些数据记录 使用hdfs命令，手动删除date=__HIVE_DEFAULT_PARTITION__的分区后再次执行sqoop脚本，发现出现另外一个错误： 执行MSCK REPAIR TABLE命令： hive> MSCK REPAIR TABLE ads.ads_i
sqoop从hive导入mysql时死锁问题
qq_42000536的博客
10-05 237
新来了一批数据源洗成指标要从hive导入到mysql中.生成的指标key是由数据源+产品编号+数据表名+维度的md5的前8位拼接而成.例:指标key是baidu1001monthreportas55fef6ret 这样大约30个字符到45个字符之前不定.
hive 把mysql语句执行_Sqoop将Hive表中数据导入MySQL中，进程死住，不执行该怎么办...
weixin_42324688的博客
02-15 228
[hadoopUser@secondmgt ~]$ sqoop-export --connect jdbc:mysql://secondmgt:3306/transport --username hive --password hive --table jtxx --export-dir /hive/warehouse/hive.db/temptrans --input-fields-termin...
sqoop导hive数据到mysql卡死在INFO mapreduce.Job: map 100% reduce 0%
大黄_sama
02-22 3058
       用sqoop将hive里面的数据导出到mysql里去，一种情况是mapreduce 程序卡住，INFO mapreduce.Job: map 100% reduce 0%       另一种情况是：报这个错误：java.sql.SQLException: Incorrect string value: '\xF0\x9F\x90\xBB' for column       经...
sqoop从hive导出到mysql卡在一直卡在INFO mapreduce.Job: Running job,改容量不行试试这个方法。
hjjhjji的博客
12-04 771
1-1.用$hadoop job -list查看进程，当时本人遇到已经是011了，初始是001.1.如果之前可以运行，大概率排除配置问题，先考虑是否进程过多一直在排队，($代表是命令)1-2.用$hadoop job -kill杀掉进程，再运行试一下。1-3.如果还不行就再重启一次，大概率就解决了。
Sqoop 导出mysql 死锁
wd1llc38的博客
02-23 604
https://www.cnblogs.com/-courage/p/14024701.html
sqoop 从 hive 导出到 mysql 指定条件怎么写
06-08
要使用 Sqoop 从 Hive 导出到 MySQL 并指定条件，可以使用以下命令： ``` sqoop export \ --connect jdbc:mysql://mysql_server:port/sqoop_export \ --username <username> \ --password <password> \ --table ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值