利用sqoop把MySQL数据导入Hive

最新推荐文章于 2024-04-15 17:43:47 发布

即心即佛

最新推荐文章于 2024-04-15 17:43:47 发布

阅读量284

点赞数

本文链接：https://blog.youkuaiyun.com/qq_43024380/article/details/113554282

版权

大数据专栏收录该内容

5 篇文章

订阅专栏

环境准备

1. 安装Hadoop
2. 安装Hive

安装Sqoop

cd /opt/install
wget https://mirror.bit.edu.cn/apache/sqoop/1.4.7/sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /opt/bigdata
cd /opt/bigdata/sqoop-1.4.7.bin__hadoop-2.6.0/conf/
cp sqoop-env-template.sh sqoop-env.sh（不要忘了执行source命令）

vim sqoop-env.sh

export HADOOP_HOME=/opt/bigdata/hadoop-2.6.0-cdh5.7.0
export HADOOP_MAPRED_HOME=$HADOOP_HOME
export HADOOP_COMMON_HOME=$HADOOP_HOME
export HADOOP_HDFS_HOME=$HADOOP_HOME
export YARN_HOME=$HADOOP_HOME

export HIVE_HOME=/opt/bigdata/apache-hive-2.3.8-bin
export HIVE_CONF_DIR=${HIVE_HOME}/conf

MySQL数据导入Hive

0. MySQL中有数据库fps，fps数据库中有person表
1. 在Hive创建数据fps
2. 执行以下命令
sqoop import --connect jdbc:mysql://192.168.8.140:3306/fps?useSSL=false  --username root --password xxxxxx--table person --hive-import --hive-overwrite --hive-table fps.person

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:990)
	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:342)
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2188)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2221)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2016)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776)
	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:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
	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.db.DBInputFormat.getConnection(DBInputFormat.java:216)
	... 11 more
Caused by: java.net.ConnectException: 拒绝连接 (Connection refused)
	at java.net.PlainSocketImpl.socketConnect(Native Method)
	at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:476)
	at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:218)
	at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:200)
	at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:394)
	at java.net.Socket.connect(Socket.java:606)
	at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:301)
	... 27 more

解决方案：
数据库连接必须使用真实IP，即使Sqoop和MySQL在同一台机器也不能使用localhost或127.0.0.1。
sqoop import --connect jdbc:mysql://192.168.8.140:3306/fps?useSSL=false --username root --password xxxxxx–table person --hive-import --hive-overwrite --hive-table fps.person