[sqoop]hive3.1.2 hadoop3.1.1安装sqoop1.4.7

参考:

Hadoop3.2.4+Hive3.1.2+sqoop1.4.7安装部署_hadoop sqoop安装_alicely07的博客-CSDN博客

一、安装

1、解压

tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz  -C /home/data_warehouse/module
mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop-1.4.7

2、配置文件 

sqoop-env.sh 末尾添加

#指定Hadoop安装目录
export HADOOP_COMMON_HOME=/home/data_warehouse/module/hadoop-3.1.3
export HADOOP_MAPRED_HOME=/home/data_warehouse/module/hadoop-3.1.3
#指定Hive安装目录
export HIVE_HOME=/home/data_warehouse/module/hive-3.1.2
#指定ZooKeeper安装目录
export ZOOKEEPER_HOME=/opt/module/zookeeper
export ZOOCFGDIR=/opt/module/zookeeper/conf
export HCAT_HOME=/home/data_warehouse/module/hive-3.1.2/hcatalog

环境变量

先创建目录，解决警告:accumulo does not exist!

[root@ck3 /home/data_warehouse/module/sqoop-1.4.7]# mkdir accumulo

#SQOOP_HOME
export SQOOP_HOME=/home/data_warehouse/module/sqoop-1.4.7
export PATH=$PATH:$SQOOP_HOME/bin
export ACCUMULO_HOME=$SQOOP_HOME/accumulo

3、jar包

（1）添加jdbc驱动包：将MySQL数据库的JDBC驱动包mysql-connector-java-5.1.37.jar添加到Sqoop安装目录的lib目录中。

（2）删除Sqoop安装目录的lib目录中的commons-lang3-3.4.jar，并添加commons-lang-2.6.jar(在$HADOOP_HOME/share/hadoop/yarn/timelineservice/lib/commons-lang-2.6.jar下可以找到)

（3）其他包

需在sqoop-1.4.7-bin_hadoop2.6.0/lib下添加 hive-*.jar、datanucleus-*.jar、derby-10.14.1.0.jar、javax.jdo-3.2.0-m3.jar

三、测试

1、测试Sqoop是否能够成功连接数据库

sqoop list-databases --connect jdbc:mysql://ck3:3306/ --username root --password 123456

2、mysql到HDFS

# mysql -uroot -p123456
mysql> create database company;
mysql> create table company.staff(id int(4) primary key not null auto_increment, name varchar(255), sex varchar(255));
mysql> insert into company.staff(name, sex) values('Thomas', 'Male');
mysql> insert into company.staff(name, sex) values('Catalina', 'FeMale');

sqoop import \
--connect jdbc:mysql://ck3:3306/company \
--username root \
--password 123456 \
--table staff  \
--target-dir /user/hive/warehouse/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t"

3、hive到hdfs

1）在hive中建表

create table test.stu(
    name string,
    id string
)
row format delimited
fields terminated by "\t"
;
insert into table stu values("haha",1),("heihei",2);
select distinct name from stu;

2）在mysql中建表

create table test.stu_mysql(
name varchar(255),
id varchar(255)
);

3）将hive表导入到Mysql对应的表

sqoop export \
--connect jdbc:mysql://ck3:3306/test \
--username root \
--password 123456 \
--table stu_mysql \
--columns "name,id" \
--export-dir /user/hive/warehouse/test.db/stu \
--input-fields-terminated-by "\t"
;