使用sqoop将数据从Hive导入MySQL出现的问题

使用sqoop将数据从Hive导入MySQL出现无法连接的问题

前言

在搭建hadoop分布式集群时，将workers文件(hadoop 3.x)或者slaves文件(hadoop2.x)中的localhost删除，并添加了slave1节点，最终master节点只做为namenode节点使用，不作为datanode。作者使用的hadoop版本分别是hadoop3.1.3和hadoop2.7.1。

问题

我们先启动hadoop集群、以及MySQL服务

service mysql start
start-all.sh #如果已经在~/.bashrc中设置过hadoop路径则可以直接使用

之后根据 大数据案例-步骤三：Hive、MySQL、HBase数据互导中的步骤，运行hive进行操作，创建临时表user_action,在MySQL数据库dblab中创建新表usser_action,之后执行命令

#这里记得更换为自己的密码
cd /usr/local/sqoop
./bin/sqoop export --connect jdbc:mysql://localhost:3306/dblab --username root --password hadoop --table user_action --export-dir '/user/hive/warehouse/dblab.db/user_action' --fields-terminated-by '\t'; #导入命令

错误

发现导出数据失败了，我们打开mapreduce的任务日志，网站，打开失败的mapreduce日志，报错如下：

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)atorg.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:767)atorg.apache.hadoop.mapred.MapTask.run(MapTask.java:341)atorg.apache.hadoop.mapred.LocalContainerLauncher$EventHandler.runSubtask(LocalContainerLauncher.java:380)
at org.apache.hadoop.mapred.LocalContainerLauncher$EventHandler.runTask(LocalContainerLauncher.java:301)atorg.apache.hadoop.mapred.LocalContainerLauncher$EventHandler.access$200(LocalContainerLauncher.java:187)atorg.apache.hadoop.mapred.LocalContainerLauncher$EventHandler$1.run(LocalContainerLauncher.java:230)atjava.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
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:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989)
at com.mysql.jdbc.MysqlIO.(MysqlIO.java:341)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2251)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:806)
at com.mysql.jdbc.JDBC4Connection.(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:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
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.(AsyncSqlRecordWriter.java:76)
at org.apache.sqoop.mapreduce.ExportOutputFormat$ExportRecordWriter.(ExportOutputFormat.java:95)
at org.apache.sqoop.mapreduce.ExportOutputFormat.getRecordWriter(ExportOutputFormat.java:77)
… 12 more
Caused by: java.net.ConnectException: Connection refused (Connection refused)
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
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 com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
at com.mysql.jdbc.MysqlIO.(MysqlIO.java:300)
… 30 more

解决办法

查找后发现是连接不上MySQL服务器所导致，这里尝试过修改MySQL的默认回收时间，参考链接
，结果还是无法导入，之后又尝试了另一篇文章中的办法，发现还是无法连接mysql服务器，最后在链接中的描述中，想到可能是在导入数据的过程中，其他节点没有权限访问mysql服务器，然后就更改了slaves(workers)中节点的配置，将localhost添加进去：

然后再进行导入操作，最后发现居然导入成功了，成果如下：

最终终于完成了整个实习作业，祝各位好运。

参考教材 http://dblab.xmu.edu.cn/post/bigdata3/