sqoop1.4.6离线部署于hadoop2.6之上与hive导入导出数据

本文主要是介绍sqoop1.4.6离线部署于hadoop2.6之上与hive导入导出数据，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1) .下载最新的sqoop1.4.6安装包

sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar

2) . 解压到/usr/local，跟hadoop同一级别

# tar -xzvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /usr/local
# mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha sqoop1.4

3) 添加环境变量

# vi /etc/profile
export SQOOP_HOME=/usr/local/sqoop1.4
export PATH=.:$HADOOP_HOME/bin:$JAVA_HOME/bin:$HIVE_HOME/bin:$SQOOP_HOME/bin:$PATH
# source /etc/profile

4) 编辑配置文件

-# cp sqoop-env-template.sh sqoop-env.sh

-# vi sqoop-env.sh
export HADOOP_COMMON_HOME=/usr/local/hadoop2.6/
export HADOOP_MAPRED_HOME=/usr/local/hadoop2.6/
export HBASE_HOME=/usr/local/hbase1.1
export HIVE_HOME=/usr/local/hive1.2
export ZOOCFGDIR=/usr/local/zk3.4/conf

-# vi sqoop-site.xml
<property><property><name>sqoop.metastore.client.autoconnect.username</name><value>root</value><description>The username to bind to the metastore.</description></property><property><name>sqoop.metastore.client.autoconnect.password</name><value>123456</value><description>The password to bind to the metastore.</description></property>

5) 复制需要的类

# cp $HADOOP_HOME/share/hadoop/common/hadoop-common-2.6.0.jar $SQOOP_HOME/lib
# cp /root/Downloads/mysql-connector-java-5.1.39-bin.jar $SQOOP_HOME/lib

6) Hive元数据切换至mysql中

a). 创建hive元数据库

mysql> create database hive;
mysql> CREATE USER 'hive' IDENTIFIED BY 'mysql';
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'hive'@'%' WITH GRANT OPTION;
mysql> flush privileges;

b). hive-site.xml编辑

 <!-- hive的元数据存放于mysql中--><property><name>hive.metastore.local</name><value>true</value></property><property><name>javax.jdo.option.ConnectionURL</name><value>jdbc:mysql://192.168.220.20:3306/hive?useSSL=false&amp;characterEncoding=UTF-8</value></property><property><name>javax.jdo.option.ConnectionDriverName</name><value>com.mysql.jdbc.Driver</value></property><property><name>javax.jdo.option.ConnectionUserName</name><value>hive</value></property><property><name>javax.jdo.option.ConnectionPassword</name><value>mysql</value>
</property>

c).把mysql的驱动包放到hive的lib中

# cp /root/Downloads/mysql-connector-java-5.1.39-bin.jar /usr/local/hive1.2/lib

d).关闭mysql的SSL功能

编辑my.cnf配置文件，在mysqld后面添加一句：skip_ssl
mysql> show variables like '%ssl%';

7) Mysql创建测试表

mysql> create database test;
mysql> use test;mysql>  create table smq_mysql(id int,name varchar(50));mysql> insert into smq_mysql values(1,'a1');mysql> insert into smq_mysql values(2,'a2');mysql> commit;

8) Sqoop连接mysql

[root@master conf]# sqoop list-tables --connect jdbc:mysql://192.168.220.20:3306/test --username root --password 123456

9) Sqoop创建表

[root@master conf]# sqoop create-hive-table --connect jdbc:mysql://192.168.220.20:3306/test --username root --password 123456 --table smq_mysql --hive-table test.smq_mysql  --fields-terminated-by ',' --hive-overwrite

10) Sqoop导入hive

[root@master conf]# sqoop import --connect jdbc:mysql://192.168.220.20:3306/test --username root --password 123456 --table smq_mysql --hive-table test.smq_mysql --hive-import --fields-terminated-by ',' --hive-overwrite -m 1

11) Sqoop导出hive

[root@master bin]# hadoop fs -ls /user/hive/warehouse
[root@master bin]# hadoop fs -ls /user/root/.Trash/Current/user/hive/warehouse
mysql> create table exp_smq_mysql as select * from smq_mysql where 1=2;
[root@master ~]# sqoop export -connect jdbc:mysql://192.168.220.20:3306/test -username root -password 123456  -table exp_smq_mysql -export-dir /user/hive/warehouse/test.db/smq_mysql

这篇关于sqoop1.4.6离线部署于hadoop2.6之上与hive导入导出数据的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---