Sqoop

一、 Sqoop安装

\1) 将Sqoop安装包上传到虚拟机并进行解压：

[root@cent71 module]# tar -zxf sqoop-1.4.7.tar.gz -C /opt/software/

\2) 修改配置文件sqoop-env.sh：

[root@cent71 conf]# cp sqoop-env-template.sh sqoop-env.sh

#Set path to where bin/hadoop is available

export HADOOP_COMMON_HOME=/opt/software/hadoop-2.7.0

#Set path to where hadoop-*-core.jar is available

export HADOOP_MAPRED_HOME=/opt/software/hadoop-2.7.0

#set the path to where bin/hbase is available

#export HBASE_HOME=

#Set the path to where bin/hive is available

export HIVE_HOME=/opt/software/apache-hive-2.1.1-bin

\3) 将mysql驱动复制到sqoop的lib中：

[root@cent71 module]# cp mysql-connector-java-5.1.39.jar /opt/software/sqoop-1.4.7.bin__hadoop-2.6.0/lib/

\4) 设置好环境变量，就可以在任意路径下执行sqoop指令了。

export SQOOP_HOME=/opt/software/sqoop-1.4.7.bin__hadoop-2.6.0

export PATH=$PATH:$SQOOP_HOME/bin

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# source /etc/profile

\5) 测试能否连接到mysql：

\6) 如果不行的话应该是只可以localhost连接

\7) 修改mysql数据库的user表

\8) Update mysql.user set host=”%” where user=”root”;

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# bin/sqoop list-databases –connect jdbc:mysql://cent71:3306/ --username root --password 123456

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

二、 Sqoop导入

​

1 Mysql导入到HDFS

a) 执行导入指令：

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# bin/sqoop import --connect jdbc:mysql://cent71:3306/mybase --username root --password 123456 --target-dir /sqoopresult --table stu --num-mappers 1

–connect：指定连接的关系数据库

–username：指定连接数据库的用户名

–password：指定连接数据库的密码

–target-dir：指定导入到HDFS的目录

–table：代表要进行导入数据操作的mysql源数据库表名

–num-mappers：指定map任务个数（或-m），必填

注意：sqoop中，可以直接在控制行输入上述命令，也可以使用配置文件进行导入导出。如果使用配置文件，可以创建文件conf2：

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# touch myconf/conf2

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# vi myconf/conf2

import

–connect

jdbc:mysql://cent71:3306/mybase

–username

root

–password

123456

–target-dir

/sqoopresult4

–table

stu

–m

1

然后执行指令：

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# bin/sqoop --options-file myconf/conf2

即可。

在写文件conf2时需要注意：参数值和项不能在同一行，写的时候每个隔开一行即可。

b) 查看文件内容：

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# hadoop fs -cat /sqoopresult/part-m-00000

62131222,连鑫栋,软件2班

62131223,李庆,软件2班

2 Mysql导入到Hive

a) 将hive-common包加入到sqoop的lib中：

[root@cent71 lib]# cp hive-common-2.1.1.jar /opt/software/sqoop-1.4.7.bin__hadoop-2.6.0/lib/

b) 在hive中创建表stu2：

hive> create table stu2 like stu;

c) 创建option文件：

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# cat myconf/conf3

import

–connect

jdbc:mysql://cent71:3306/mybase

–username

root

–password

123456

–table

stu

–hive-table

sqoop.stu2

–hive-import

–m

1

–fields-terminated-by

\t

d) 执行文件：

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# bin/sqoop --options-file myconf/conf3

e) 在hive中查看：

hive> select * from stu2;

问题注意：

Sqoop导入到Hive中的步骤是这样的：先导入到HDFS上，再load data到hive中，因此，如果这个过程中出错了，导致第二步没有运行成功，HDFS上就会多了一个路径/user/root/*；第二次再导入时因为已经存在这个路径了，就会报错。所以第二次尝试时应该先删掉此路径。

3 MySQL表数据子集导入

可以使用Sqoop提供的—where和—query参数，先进行数据过滤，再将满足条件的数据进行导入。

–where语句：

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# cat myconf/conf2

import

–connect

jdbc:mysql://cent71:3306/mybase

–username

root

–password

123456

–target-dir

/sqoopresult5

–table

stu

–num-mappers

1

–where

stuno=‘100001’

–columns

stuno,name,bjname

查看结果：

[root@cent72 ~]# hadoop fs -cat /sqoopresult5/part-m-00000

100001,test,testbj

100001,test,testbj

100001,test,testbj

–query主要针对复杂的数据过滤，后面可以添加SQL语句，更方便高效地导入数据：

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# vi myconf/conf2

import

–connect

jdbc:mysql://cent71:3306/mybase

–username

root

–password

123456

–target-dir

/sqoopresult6

–m

1

–query

select stuno,name from stu WHERE bjname=‘计算1班’ AND $CONDITIONS

作用就是先查出子集，然后将子集进行导入。$CONDITIONS相当于一个占位符，动态地接收经过滤后的子集数据，然后让每个Map任务执行查询的结果并进行数据导入。

查看结果：

[root@cent72 ~]# hadoop fs -cat /sqoopresult6/part-m-00000

62131201,高俊杰

62131202,安耀楠

62131203,耿源

62131204,刘乐妍

使用时需要注意以下几点：

\1) 如果没有指定“—m 1”，那么在指令中必须要添加—split-by参数，值为表中唯一的字段（如id），作用是针对多副本map任务并行执行查询结果并进行数据导入；

\2) –query餐宿后的查询语句中如果已经使用了WHERE关键字，那么在连接$CONDITIONS占位符前必须使用AND关键字，否则必须使用WHERE关键字连接；

\3) –query参数后的查询语句中的$CONDITIONS不能省略，并且如果查询语句使用双引号进行包装，则必须使用$CONDITIONS，这样可以避免shell将其视为Shell变量。

三、 Sqoop导出

1 将hive表的数据导出到mysql表中。

\1) 在mysql中创建表

\2) 导出指令：

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# bin/sqoop export

–connect jdbc:mysql://cent71:3306/mybase

–username root

–password 123456

–table stu

–export-dir /user/hive/warehouse/sqoop.db/stu

–fields-terminated-by ‘\t’

bin/sqoop export --connect jdbc:mysql://master:3306/smbms --username root --password 123456 --table smbms_user1 --export-dir /sqoopresult/part-m-00000 --fields-terminated-by ‘,’

2 Hive中有中文字符，导入到mysql后中文乱码的问题：

\1) Mysql中修改默认编码字符为utf-8：

mysql> show variables like ‘%char%’;

在/etc/my.conf文件中，增加如下配置：

[mysqld]中增加character-set-server=utf8

再增加如下配置：

[mysql]

default-character-set=utf8

[client]

default-character-set=utf8

重启mysql服务，再进入mysql客户端：

mysql> show variables like ‘%char%’;

±-------------------------------------±---------------------------+

| Variable_name | Value |

±-------------------------------------±---------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

| validate_password_special_char_count | 1 |

±-------------------------------------±---------------------------+

9 rows in set (0.01 sec)

\2) 重新创建库mybase，创建表stu：

mysql> create table stu(stuno varchar(50),name varchar(50),bjname varchar(50));

\3) 执行导入的时候要指明使用utf-8：

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# bin/sqoop export --connect "jdbc:mysql://cent71:3306/mybase

?useUnicode=true&characterEncoding=UTF-8" --username root --password 123456 --table stu --export-dir /user/hive/warehouse/sqoop.db/stu --fields-terminated-by ‘\t’

或者导入的时候使用option-file，在myconf/下创建conf1，内容如下：

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# cat myconf/conf1

export

–connect

jdbc:mysql://cent71:3306/mybase

–username

root

–password

123456

–table

stu

–export-dir

/user/hive/warehouse/sqoop.db/stu

–fields-terminated-by

\t

然后执行以下指令即可：

[root@cent71 sqoop-1.4.7.bin__hadoop-2.6.0]# bin/sqoop --options-file myconf/conf1

\4) 再在mysql中查询发现中文已经可以正常显示了：

mysql> select * from stu;

±---------±-------------±--------------+

| stuno | name | bjname |

±---------±-------------±--------------+

| 62131222 | 连鑫栋 | 软件2班 |

| 62131223 | 李庆 | 软件2班 |

3 从HDFS向mysql导出数据：

\1) 在HDFS上上传文件/zodiac.txt

\2) Mysql中创建表zodiac

\3) 编写导出文件：

[root@cent71 myconf]# cat conf1

export

–connect

jdbc:mysql://cent71:3306/mybase

–username

root

–password

123456

–table

zodiac

–export-dir

/zodiac.txt

–fields-terminated-by

,

\4) 导出：

[root@cent71 myconf]# sqoop --options-file conf1

\5) 查看结果：

mysql> select * from zodiac;

±-------±-----------±-------±-----+

| name | birth | gender | code |

±-------±-----------±-------±-----+

| lucy | 1984-9-10 | F | 30 |

| mary | 2001-12-24 | F | 20 |

| edward | 1981-2-12 | M | 10 |

| bob | 2004-10-10 | M | 50 |

| sara | 1974-4-5 | F | 2 |

±-------±-----------±-------±-----+

5 rows in set (0.00 sec)

\1.