sqoop导出hive到mysql问题

hive导出到mysql报错日志如下,验证mysql用户名密码无误
 
[hadoop@hdfszz-1 ~]$ mysql -uhive -hlocalhost -P3306 -phive
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1768
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hive               |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.04 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sogoulog       |
| testsqoop      |
+----------------+
2 rows in set (0.00 sec)

mysql> 
 
 
[hadoop@hdfszz-1 bin]$ ./sqoop export --connect jdbc:mysql://localhost:3306/test --username hive --password hive --table sogoulog --export-dir /user/hive/warehouse/sogoulog/date=20151001
Warning: /home/hadoop/app/sqoop-1.4.5-cdh5.4.5/bin/../../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /home/hadoop/app/sqoop-1.4.5-cdh5.4.5/bin/../../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /home/hadoop/app/sqoop-1.4.5-cdh5.4.5/bin/../../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /home/hadoop/app/sqoop-1.4.5-cdh5.4.5/bin/../../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
15/11/10 13:46:23 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5-cdh5.4.5
15/11/10 13:46:23 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
15/11/10 13:46:23 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
15/11/10 13:46:23 INFO tool.CodeGenTool: Beginning code generation
15/11/10 13:46:24 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `sogoulog` AS t LIMIT 1
15/11/10 13:46:24 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `sogoulog` AS t LIMIT 1
15/11/10 13:46:24 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /home/hadoop/app/hadoop-2.6.0-cdh5.4.5
Note: /tmp/sqoop-hadoop/compile/058ea889bcaef571b2de384aa3ae50b1/sogoulog.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
15/11/10 13:46:34 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/058ea889bcaef571b2de384aa3ae50b1/sogoulog.jar
15/11/10 13:46:34 INFO mapreduce.ExportJobBase: Beginning export of sogoulog
15/11/10 13:46:35 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
15/11/10 13:46:35 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
15/11/10 13:46:38 INFO Configuration.deprecation: mapred.reduce.tasks.speculative.execution is deprecated. Instead, use mapreduce.reduce.speculative
15/11/10 13:46:38 INFO Configuration.deprecation: mapred.map.tasks.speculative.execution is deprecated. Instead, use mapreduce.map.speculative
15/11/10 13:46:38 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
15/11/10 13:46:44 INFO input.FileInputFormat: Total input paths to process : 1
15/11/10 13:46:44 INFO input.FileInputFormat: Total input paths to process : 1
15/11/10 13:46:44 INFO mapreduce.JobSubmitter: number of splits:4
15/11/10 13:46:44 INFO Configuration.deprecation: mapred.map.tasks.speculative.execution is deprecated. Instead, use mapreduce.map.speculative
15/11/10 13:46:45 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1445932567696_0020
15/11/10 13:46:46 INFO impl.YarnClientImpl: Submitted application application_1445932567696_0020
15/11/10 13:46:46 INFO mapreduce.Job: The url to track the job: http://hdfszz-1:8088/proxy/app ... 0020/
15/11/10 13:46:46 INFO mapreduce.Job: Running job: job_1445932567696_0020
15/11/10 13:46:55 INFO mapreduce.Job: Job job_1445932567696_0020 running in uber mode : false
15/11/10 13:46:55 INFO mapreduce.Job:  map 0% reduce 0%
15/11/10 13:47:06 INFO mapreduce.Job: Task Id : attempt_1445932567696_0020_m_000001_0, Status : FAILED
Error: 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:647)
        at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:767)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
        at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)
        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:1671)
        at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
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:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:348)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2391)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2428)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2213)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:797)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at org.apache.sqoop.mapreduce.db.DBConfiguration.getConnection(DBConfiguration.java:302)
        at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.<init>(AsyncSqlRecordWriter.java:78)
        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.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:345)
        at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
        at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
        at java.net.Socket.connect(Socket.java:589)
        at java.net.Socket.connect(Socket.java:538)
        at java.net.Socket.<init>(Socket.java:434)
        at java.net.Socket.<init>(Socket.java:244)
        at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:257)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:298)
        ... 26 more

fish - Hadooper

赞同来自: 天热不下雨

jdbc:mysql://localhost:3306 这个链接写得有问题。   记住sqoop是个只有map的mapreduce任务,不同的map在不同的机器上执行,他们看到的都是jdbc:mysql://localhost:3306这个链接,每台机器都往localhost 3306连,但是,mysql server并不是架设在每台机器上啊。

yanglei

赞同来自:

“Caused by: java.net.ConnectException: Connection refused”   一般是hostname, ip, 或者端口引发的问题。试着从这几个方面去调查看看。

天热不下雨

赞同来自:

./sqoop export --connect jdbc:mysql://120.24.84.220:3306/test --username hive --password hive --table sogoulog --export-dir /user/hive/warehouse/sogoulog/date=20151001 --input-fields-terminated-by "|"   在导出的时候会报Can't parse input data: 'www.jctrans.com/supply/vtpx632.html' 数据是这样的 [hadoop@hdfszz-1 bin]$ hadoop fs -text /user/hive/warehouse/sogoulog/date=20151001/*|grep  'www.jctrans.com/supply/vtpx632.html' 15/11/10 20:07:04 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable 14:01:03|1459451571325533|[KOPER]|7|2|www.jctrans.com/supply/vtpx632.html       15/11/10 19:47:43 INFO mapreduce.Job: Task Id : attempt_1445932567696_0038_m_000003_2, Status : FAILED Error: java.io.IOException: Can't export data, please check failed map task 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:145)         at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)         at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787)         at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)         at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)         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:1671)         at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158) Caused by: java.lang.RuntimeException: Can't parse input data: 'www.jctrans.com/supply/vtpx632.html'         at sogoulog.__loadFromFields(sogoulog.java:464)         at sogoulog.parse(sogoulog.java:382)         at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)         ... 10 more Caused by: java.util.NoSuchElementException         at java.util.ArrayList$Itr.next(ArrayList.java:854)         at sogoulog.__loadFromFields(sogoulog.java:459)         ... 12 more

fish - Hadooper

赞同来自:

看起来跟代码实现有关系。sogoulog.java:459这个代码中是什么?

祝俊胜

赞同来自:

 [main] WARN  org.apache.hadoop.util.NativeCodeLoader  - Unable to load native-hadoop library for your platform... using builtin-java classes where applicable Exception has occurred during processing command  Exception: org.apache.sqoop.common.SqoopException Message: GENERIC_HDFS_CONNECTOR_0007:Invalid input/output directory - Unexpected exception Stack trace:          at  org.apache.sqoop.client.request.ResourceRequest (ResourceRequest.java:137)            at  org.apache.sqoop.client.request.ResourceRequest (ResourceRequest.java:187)            at  org.apache.sqoop.client.request.JobResourceRequest (JobResourceRequest.java:113)            at  org.apache.sqoop.client.request.SqoopResourceRequests (SqoopResourceRequests.java:170)            at  org.apache.sqoop.client.SqoopClient (SqoopClient.java:477)            at  org.apache.sqoop.shell.StartJobFunction (StartJobFunction.java:64)            at  org.apache.sqoop.shell.SqoopFunction (SqoopFunction.java:51)            at  org.apache.sqoop.shell.SqoopCommand (SqoopCommand.java:148)            at  org.apache.sqoop.shell.SqoopCommand (SqoopCommand.java:110)            at  org.codehaus.groovy.tools.shell.Command$execute (null:-1)            at  org.codehaus.groovy.runtime.callsite.CallSiteArray (CallSiteArray.java:45)            at  org.codehaus.groovy.tools.shell.Command$execute (null:-1)            at  org.codehaus.groovy.tools.shell.Shell (Shell.groovy:101)            at  org.codehaus.groovy.tools.shell.Groovysh (Groovysh.groovy:-1)            at  sun.reflect.NativeMethodAccessorImpl (NativeMethodAccessorImpl.java:-2)            at  sun.reflect.NativeMethodAccessorImpl (NativeMethodAccessorImpl.java:57)            at  sun.reflect.DelegatingMethodAccessorImpl (DelegatingMethodAccessorImpl.java:43)            at  java.lang.reflect.Method (Method.java:606)            at  org.codehaus.groovy.reflection.CachedMethod (CachedMethod.java:90)            at  groovy.lang.MetaMethod (MetaMethod.java:324)            at  groovy.lang.MetaClassImpl (MetaClassImpl.java:1208)            at  org.codehaus.groovy.runtime.ScriptBytecodeAdapter (ScriptBytecodeAdapter.java:130)            at  org.codehaus.groovy.tools.shell.Groovysh (Groovysh.groovy:254)            at  sun.reflect.NativeMethodAccessorImpl (NativeMethodAccessorImpl.java:-2)            at  sun.reflect.NativeMethodAccessorImpl (NativeMethodAccessorImpl.java:57)            at  sun.reflect.DelegatingMethodAccessorImpl (DelegatingMethodAccessorImpl.java:43)            at  java.lang.reflect.Method (Method.java:606)            at  org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite$PogoCachedMethodSiteNoUnwrapNoCoerce (PogoMetaMethodSite.java:207)            at  org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite (PogoMetaMethodSite.java:56)            at  org.codehaus.groovy.runtime.callsite.AbstractCallSite (AbstractCallSite.java:161)            at  org.codehaus.groovy.tools.shell.Groovysh (Groovysh.groovy:153)            at  org.codehaus.groovy.tools.shell.Shell (Shell.groovy:119)            at  org.codehaus.groovy.tools.shell.Shell$leftShift$0 (null:-1)            at  org.codehaus.groovy.tools.shell.ShellRunner (ShellRunner.groovy:94)            at  org.codehaus.groovy.tools.shell.InteractiveShellRunner (InteractiveShellRunner.groovy:-1)            at  sun.reflect.NativeMethodAccessorImpl (NativeMethodAccessorImpl.java:-2)            at  sun.reflect.NativeMethodAccessorImpl (NativeMethodAccessorImpl.java:57)            at  sun.reflect.DelegatingMethodAccessorImpl (DelegatingMethodAccessorImpl.java:43)            at  java.lang.reflect.Method (Method.java:606)            at  org.codehaus.groovy.reflection.CachedMethod (CachedMethod.java:90)            at  groovy.lang.MetaMethod (MetaMethod.java:324)            at  groovy.lang.MetaClassImpl (MetaClassImpl.java:1208)            at  org.codehaus.groovy.runtime.ScriptBytecodeAdapter (ScriptBytecodeAdapter.java:130)            at  org.codehaus.groovy.runtime.ScriptBytecodeAdapter (ScriptBytecodeAdapter.java:150)            at  org.codehaus.groovy.tools.shell.InteractiveShellRunner (InteractiveShellRunner.groovy:123)            at  sun.reflect.NativeMethodAccessorImpl (NativeMethodAccessorImpl.java:-2)            at  sun.reflect.NativeMethodAccessorImpl (NativeMethodAccessorImpl.java:57)            at  sun.reflect.DelegatingMethodAccessorImpl (DelegatingMethodAccessorImpl.java:43)            at  java.lang.reflect.Method (Method.java:606)            at  org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite$PogoCachedMethodSiteNoUnwrapNoCoerce (PogoMetaMethodSite.java:207)            at  org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite (PogoMetaMethodSite.java:56)            at  org.codehaus.groovy.runtime.callsite.AbstractCallSite (AbstractCallSite.java:153)            at  org.codehaus.groovy.tools.shell.ShellRunner (ShellRunner.groovy:58)            at  org.codehaus.groovy.tools.shell.InteractiveShellRunner (InteractiveShellRunner.groovy:-1)            at  sun.reflect.NativeMethodAccessorImpl (NativeMethodAccessorImpl.java:-2)            at  sun.reflect.NativeMethodAccessorImpl (NativeMethodAccessorImpl.java:57)            at  sun.reflect.DelegatingMethodAccessorImpl (DelegatingMethodAccessorImpl.java:43)            at  java.lang.reflect.Method (Method.java:606)            at  org.codehaus.groovy.reflection.CachedMethod (CachedMethod.java:90)            at  groovy.lang.MetaMethod (MetaMethod.java:324)            at  groovy.lang.MetaClassImpl (MetaClassImpl.java:1208)            at  org.codehaus.groovy.runtime.ScriptBytecodeAdapter (ScriptBytecodeAdapter.java:130)            at  org.codehaus.groovy.runtime.ScriptBytecodeAdapter (ScriptBytecodeAdapter.java:150)            at  org.codehaus.groovy.tools.shell.InteractiveShellRunner (InteractiveShellRunner.groovy:82)            at  java_lang_Runnable$run (null:-1)            at  org.codehaus.groovy.runtime.callsite.CallSiteArray (CallSiteArray.java:45)            at  org.codehaus.groovy.runtime.callsite.AbstractCallSite (AbstractCallSite.java:108)            at  org.codehaus.groovy.runtime.callsite.AbstractCallSite (AbstractCallSite.java:112)            at  org.codehaus.groovy.tools.shell.Groovysh (Groovysh.groovy:585)            at  org.apache.sqoop.shell.SqoopShell (SqoopShell.java:156)   Caused by: Exception: java.lang.IllegalArgumentException Message: java.net.UnknownHostException: masters

wangxiaolei

赞同来自:

java.net.UnknownHostException: masters 机器上都配置了masters的ip映射吗?

要回复问题请先登录注册