目录
一、概述
Sqoop是apache旗下的一款 ”Hadoop和关系数据库之间传输数据”的工具。
导入数据:将MySQL,Oracle导入数据到Hadoop的HDFS、HIVE、HBASE等数据存储系统。
导出数据:从Hadoop的文件系统中导出数据到关系数据库。
二、Sqoop的工作机制
-
将导入和导出的命令翻译成mapreduce程序实现;
-
在翻译出的mapreduce中主要是对inputformat和outputformat进行定制。
三、Sqoop1与Sqoop2架构对比
sqoop在发展中的过程中演进出来了两种不同的架构.架构演变史
Sqoop1架构:
版本号为1.4.x为sqoop1
在架构上:sqoop1使用sqoop客户端直接提交的方式
访问方式:CLI控制台方式进行访问
安全性:命令或脚本中指定用户数据库名及密码
Sqoop2架构:
版本号为1.99x为sqoop2
在架构上:sqoop2引入了sqoop server,对connector实现了集中的管理
访问方式:REST API、 JAVA API、 WEB UI以及CLI控制台方式进行访问
sqoop1与sqoop2比较:
四、Sqoop安装部署
Sqoop安装很简单,解压好进行简单的修改就可以使用:
第一步:下载安装包
http://archive.cloudera.com/cdh5/cdh/5/sqoop-1.4.6-cdh5.14.2.tar.gz
第二步:上传并解压
将我们下载好的安装包上传到node03服务器的/xsluo/soft路径下,然后进行解压
cd /xsluo/soft/
tar -zxf sqoop-1.4.6-cdh5.14.2.tar.gz -C /xsluo/install/
第三步:修改配置文件
更改sqoop的配置文件:
cd /xsluo/install/sqoop-1.4.6-cdh5.14.2/conf
mv sqoop-env-template.sh sqoop-env.sh
vim sqoop-env.sh
#Set path to where bin/hadoop is available
export HADOOP_COMMON_HOME=/xsluo/install/hadoop-2.6.0-cdh5.14.2
#Set path to where hadoop-*-core.jar is available
export HADOOP_MAPRED_HOME=/xsluo/install/hadoop-2.6.0-cdh5.14.2
#set the path to where bin/hbase is available
export HBASE_HOME=/xsluo/install/hbase-1.2.0-cdh5.14.2
#Set the path to where bin/hive is available
export HIVE_HOME=/xsluo/install/hive-1.1.0-cdh5.14.2
第四步:添加两个必要的jar包
sqoop需要两个额外依赖的jar包,将以下两个jar包添加到sqoop的lib目录下:
第五步:配置sqoop的环境变量,添加如下内容:
sudo vim /etc/profile
export SQOOP_HOME=/xsluo/install/sqoop-1.4.6-cdh5.14.2
export PATH=:$SQOOP_HOME/bin:$PATH
让环境变量生效:
source /xsluo/profile
五、Sqoop的数据导入
1. 列举出所有的数据库
-
命令行查看帮助
bin/sqoop list-databases --help
-
列出node03主机所有的数据库
bin/sqoop list-databases --connect jdbc:mysql://node03:3306/ --username root --password 123456
-
查看某一个数据库下面的所有数据表
bin/sqoop list-tables --connect jdbc:mysql://node03:3306/hive --username root --password 123456
2. 准备表数据
在mysql中有一个库userdb中三个表:emp, emp_add和emp_conn
-
表emp:
id | name | deg | salary | dept |
---|---|---|---|---|
1201 | gopal | manager | 50,000 | TP |
1202 | manisha | Proof reader | 50,000 | TP |
1203 | khalil | php dev | 30,000 | AC |
1204 | prasanth | php dev | 30,000 | AC |
1205 | kranthi | admin | 20,000 | TP |
-
表emp_add:
id | hno | street | city |
---|---|---|---|
1201 | 288A | vgiri | jublee |
1202 | 108I | aoc | sec-bad |
1203 | 144Z | pgutta | hyd |
1204 | 78B | old city | sec-bad |
1205 | 720X | hitec | sec-bad |
-
表emp_conn:
id | phno | |
---|---|---|
1201 | 2356742 | gopal@tp.com |
1202 | 1661663 | manisha@tp.com |
1203 | 8887776 | khalil@ac.com |
1204 | 9988774 | prasanth@ac.com |
1205 | 1231231 | kranthi@tp.com |
-
建表语句如下:
CREATE DATABASE /*!32312 IF NOT EXISTS*/`userdb` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `userdb`;
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`id` INT(11) DEFAULT NULL,
`name` VARCHAR(100) DEFAULT NULL,
`deg` VARCHAR(100) DEFAULT NULL,
`salary` INT(11) DEFAULT NULL,
`dept` VARCHAR(10) DEFAULT NULL,
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`is_delete` BIGINT(20) DEFAULT '1'
) ENGINE=INNODB DEFAULT CHARSET=latin1;
INSERT INTO `emp`(`id`,`name`,`deg`,`salary`,`dept`) VALUES (1201,'gopal','manager',50000,'TP'),(1202,'manisha','Proof reader',50000,'TP'),(1203,'khalil','php dev',30000,'AC'),(1204,'prasanth','php dev',30000,'AC'),(1205,'kranthi','admin',20000,'TP');
DROP TABLE IF EXISTS `emp_add`;
CREATE TABLE `emp_add` (
`id` INT(11) DEFAULT NULL,
`hno` VARCHAR(100) DEFAULT NULL,
`street` VARCHAR(100) DEFAULT NULL,
`city` VARCHAR(100) DEFAULT NULL,
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`is_delete` BIGINT(20) DEFAULT '1'
) ENGINE=INNODB DEFAULT CHARSET=latin1;
INSERT INTO `emp_add`(`id`,`hno`,`street`,`city`) VALUES (1201,'288A','vgiri','jublee'),(1202,'108I','aoc','sec-bad'),(1203,'144Z','pgutta','hyd'),(1204,'78B','old city','sec-bad'),(1205,'720X','hitec','sec-bad');
DROP TABLE IF EXISTS `emp_conn`;
CREATE TABLE `emp_conn` (
`id` INT(100) DEFAULT NULL,
`phno` VARCHAR(100) DEFAULT NULL,
`email` VARCHAR(100) DEFAULT NULL,
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`is_delete` BIGINT(20) DEFAULT '1'
) ENGINE=INNODB DEFAULT CHARSET=latin1;
INSERT INTO `emp_conn`(`id`,`phno`,`email`) VALUES (1201,'2356742','gopal@tp.com'),(1202,'1661663','manisha@tp.com'),(1203,'8887776','khalil@ac.com'),(1204,'9988774','prasanth@ac.com'),(1205,'1231231','kranthi@tp.com');
3. 导入数据库表数据到HDFS
-
使用sqoop命令导入、导出数据前,要先启动hadoop集群
-
下面的命令用于从MySQL数据库服务器中的emp表导入HDFS。
bin/sqoop import --connect jdbc:mysql://node03:3306/userdb --password 123456 --username root --table emp --m 1
-
为了验证在HDFS导入的数据,请使用以下命令查看导入的数据
hdfs dfs -ls /user/hadoop/emp
4. 导入到HDFS指定目录
-
在导入表数据到HDFS使用Sqoop导入工具,我们可以指定目标目录。
-
使用参数 --target-dir来指定导出目的地,
-
使用参数--delete-target-dir来判断导出目录是否存在,如果存在就删掉
bin/sqoop import --connect jdbc:mysql://node03:3306/userdb --username root --password 123456 --delete-target-dir --table emp --target-dir /sqoop/emp --m 1
-
查看导出的数据
hdfs dfs -text /sqoop/emp/part-m-00000
它会用逗号(,)分隔emp表的数据和字段。
5. 导入到HDFS指定目录并指定字段之间的分隔符
bin/sqoop import --connect jdbc:mysql://node03:3306/userdb --username root --password 123456 --delete-target-dir --table emp --target-dir /sqoop/emp2 --m 1 --fields-terminated-by '\t'
-
查看文件内容
hdfs dfs -text /sqoop/emp2/part-m-00000
6. 导入关系表到Hive
第一步:拷贝jar包
-
将我们mysql表当中的数据直接导入到hive表中的话,我们需要将hive的一个叫做hive-exec-1.1.0-cdh5.14.0.jar的jar包拷贝到sqoop的lib目录下
cp /xsluo/install/hive-1.1.0-cdh5.14.0/lib/hive-exec-1.1.0-cdh5.14.2.jar /xsluo/install/sqoop-1.4.6-cdh5.14.2/lib/
第二步:准备hive数据库与表
-
将我们mysql当中的数据导入到hive表当中来
hive (default)> create database sqooptohive;
hive (default)> use sqooptohive;
hive (sqooptohive)> create external table emp_hive(id int,name string,deg string,salary int ,dept string) row format delimited fields terminated by '\001';
注意:hive中的默认的分隔符是‘\001’,是一种特由的分隔符,使用的是ascii编码的值,键盘是打不出来的。
第三步:开始导入
bin/sqoop import --connect jdbc:mysql://node03:3306/userdb --username root --password 123456 --table emp --fields-terminated-by '\001' --hive-import --hive-table sqooptohive.emp_hive --hive-overwrite --delete-target-dir --m 1
第四步:hive表数据查看
select * from emp_hive;
7. 导入关系表到Hive并自动创建Hive表
-
我们也可以通过命令来将我们的mysql的表直接导入到hive表当中去
bin/sqoop import --connect jdbc:mysql://node03:3306/userdb --username root --password 123456 --table emp_conn --hive-import -m 1 --hive-database sqooptohive;
通过这个命令,我们可以直接将我们mysql表当中的数据以及表结构一起导入到hive当中去
8. 将MySQL表数据导入到HBase当中去
-
先要开启hbase集群(启动hbase,需要先启动hadoop和zookeeper)
第一步:修改sqoop配置文件
-
sqoop导入导出HBase的数据,需要修改sqoop的配置文件sqoop-env.sh
cd /xsluo/install/sqoop-1.4.6-cdh5.14.2/conf
vim sqoop-env.sh
内容如下
#Set path to where bin/hadoop is available
export HADOOP_COMMON_HOME=/xsluo/install/hadoop-2.6.0-cdh5.14.2
#Set path to where hadoop-*-core.jar is available
export HADOOP_MAPRED_HOME=/xsluo/install/hadoop-2.6.0-cdh5.14.2
#set the path to where bin/hbase is available
export HBASE_HOME=/xsluo/install/hbase-1.2.0-cdh5.14.2
#Set the path to where bin/hive is available
export HIVE_HOME=/xsluo/install/hive-1.1.0-cdh5.14.2
第二步:在mysql当中创建数据库以及数据库表并插入数据
-
创建数据库表
CREATE DATABASE IF NOT EXISTS library;
USE library;
CREATE TABLE book(
id INT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT,
NAME VARCHAR(255) NOT NULL,
price VARCHAR(255) NOT NULL);
-
插入数据
INSERT INTO book(NAME, price) VALUES('Lie Sporting', '30');
INSERT INTO book (NAME, price) VALUES('Pride & Prejudice', '70');
INSERT INTO book (NAME, price) VALUES('Fall of Giants', '50');
第三步:将mysql表当中的数据导入HBase表当中去
-
执行以下命令,将mysql表当中的数据导入到HBase当中去
bin/sqoop import \
--connect jdbc:mysql://node03:3306/library \
--username root \
--password 123456 \
--table book \
--columns "id,name,price" \
--column-family "info" \
--hbase-create-table \
--hbase-row-key "id" \
--hbase-table "hbase_book" \
--num-mappers 1 \
--split-by id
第四步:HBase当中查看表数据
-
进入hbase的shell客户端,通过scan查看数据
hbase(main):057:0> scan 'hbase_book'
ROW COLUMN+CELL
1 column=info:name, timestamp=1550634017823, value=Lie Sporting
1 column=info:price, timestamp=1550634017823, value=30
2 column=info:name, timestamp=1550634017823, value=Pride & Prejudice
2 column=info:price, timestamp=1550634017823, value=70
3 column=info:name, timestamp=1550634017823, value=Fall of Giants
3 column=info:price, timestamp=1550634017823, value=50
9. 导入表数据子集
-
我们可以导入表的使用Sqoop导入工具,"where"子句的一个子集。它执行在各自的数据库服务器相应的SQL查询,并将结果存储在HDFS的目标目录。
-
where子句的语法如下。
--where <condition>
-
按照条件进行查找,通过--where参数来查找表emp_add当中city字段的值为sec-bad的所有数据导入到hdfs上面去
bin/sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root --password 123456 --table emp_add \
--target-dir /sqoop/emp_add -m 1 --delete-target-dir \
--where "city = 'sec-bad'"
10. SQL语句查找导入HDFS
-
我们还可以通过 –query参数来指定我们的sql语句,通过sql语句来过滤我们的数据进行导入
bin/sqoop import \
--connect jdbc:mysql://node03:3306/userdb --username root --password 123456 \
--delete-target-dir -m 1 \
--query 'select phno from emp_conn where 1=1 and $CONDITIONS' \
--target-dir /sqoop/emp_conn
-
查看hdfs数据内容
hdfs dfs -text /sqoop/emp_conn/part*
注意:
使用sql语句来进行查找是不能加参数--table
并且必须要添加where条件,
并且where条件后面必须带一个$CONDITIONS 这个字符串,
并且这个sql语句必须用单引号,不能用双引号
11. 增量导入
在实际工作当中,数据的导入,很多时候都是只需要导入增量数据即可,并不需要将表中的数据全部导入到hive或者hdfs当中去,肯定会出现重复的数据的状况,所以我们一般都是选用一些字段进行增量的导入,为了支持增量的导入,sqoop也给我们考虑到了这种情况并且支持增量的导入数据
增量导入是仅导入新添加的表中的行的技术。
增量导入数据分为两种方式:
-
一是基于递增列的增量数据导入(Append方式)。
-
二是基于时间列的数据增量导入(LastModified方式)。
用于Sqoop导入命令增量选项的核心参数:
param | 含义 |
---|---|
--incremental | 用来指定一些列,这些列在增量导入时用来检查这些数据是否作为增量数据进行导入,和关系型数据库中的自增字段及时间戳类似. 注意:这些被指定的列的类型不能使任意字符类型(在关系数据库中),如char、varchar等类型都是不可以的,同时–check-column可以去指定多个列 |
--check-column | 用来指定增量导入的模式,两种模式分别为Append和Lastmodified |
--last-value | 指定上一次导入中检查列指定字段最大值,即会导入比lastvalue指定值大的数据记录 |
注意:上面三个参数都必须添加!
--incremental <mode>
--check-column <column name>
--last-value <last check column value>
方式一:Append方式
需求:
-
把MySQL数据库中emp表当中字段id大于1202的所有数据导入到HDFS
-
注意:增量导入的时候,一定不能加参数--delete-target-dir否则会报错
bin/sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table emp \
--incremental append \
--check-column id \
--last-value 1202 \
-m 1 \
--target-dir /sqoop/increment
-
查看数据内容
hdfs dfs -text /sqoop/increment/part*
[hadoop@node01 bin]$ hdfs dfs -text /sqoop/increment/part*
1203,khalil,php dev,30000,AC,2020-09-02 12:52:03.0,2020-09-02 12:52:03.0,1
1204,prasanth,php dev,30000,AC,2020-09-02 12:52:03.0,2020-09-02 12:52:03.0,1
1205,kranthi,admin,20000,TP,2020-09-02 12:52:03.0,2020-09-02 12:52:03.0,1
增量导入时使用 --where 参数来进行控制数据的选取会更加精准:
bin/sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table emp \
--incremental append \
--where "create_time > '2018-06-17 00:00:00' and is_delete='1' and create_time < '2018-06-17 23:59:59'" \
--target-dir /sqoop/incement2 \
--check-column id \
--m 1
需求:把MySQL数据库emp表中id大于1202的数据增量导入到hive表
sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table emp \
--incremental append \
--check-column id \
--last-value 1202 \
--hive-import \
--hive-database sqooptohive
-m 1
需求:把MySQL数据库emp表中id小于等于1202的数据增量同步到hive表
sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table emp \
--incremental append \
--check-column id \
--where "id <= 1202" \
-m 1 \
--hive-import \
--hive-table sqooptohive.emp \
设置where条件,通过判断条件可以判断减少的数据和增加的数据,控制更加灵活。
sqoop import \
--connect jdbc:mysql://192.168.200.100:3306/yang \
--username root \
--P \
--table appendTest \
--hive-import \
-m 1 \
--incremental append \
--where "age>30"
--check-column id \
--last-value 0
方式二:LastModify方式
基于lastModify的方式,要求原表中有time字段,它能指定一个时间戳,让Sqoop把该时间戳之后的数据导入至Hive,当然我们可以指定merge-key参数为id,表示将后续新的记录与原有记录合并。
执行下面语句来增量导入到hive:
sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table emp \
-m 1 \
--hive-table sqooptohive.emp \
--incremental lastmodified \
--check-column create_time \
--last-value "2020-09-02 12:52:03"
结果报错:
--incremental lastmodified option for hive imports is not supported. Please remove the parameter --incremental lastmodified
Sqoop 不支持 mysql转hive时使用 *lastmodified* 模式进行增量导入,但mysql转HDFS时可以支持该方式,使用append方式导入:
六、Sqoop的数据导出
1. 将数据从HDFS把文件导出到RDBMS数据库
导出前,目标表必须存在于目标数据库中。
默认操作是从将文件中的数据使用INSERT语句插入到表中
更新模式下,是生成UPDATE语句更新表数据
数据是在HDFS当中的如下目录/sqoop/emp,数据内容如下
1201,gopal,manager,50000,TP,2018-06-17 18:54:32.0,2018-06-17 18:54:32.0,1
1202,manisha,Proof reader,50000,TP,2018-06-15 18:54:32.0,2018-06-17 20:26:08.0,1
1203,khalil,php dev,30000,AC,2018-06-17 18:54:32.0,2018-06-17 18:54:32.0,1
1204,prasanth,php dev,30000,AC,2018-06-17 18:54:32.0,2018-06-17 21:05:52.0,0
1205,kranthi,admin,20000,TP,2018-06-17 18:54:32.0,2018-06-17 18:54:32.0,1
第一步:创建mysql表
CREATE TABLE `emp_out` (
`id` INT(11) DEFAULT NULL,
`name` VARCHAR(100) DEFAULT NULL,
`deg` VARCHAR(100) DEFAULT NULL,
`salary` INT(11) DEFAULT NULL,
`dept` VARCHAR(10) DEFAULT NULL,
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`is_delete` BIGINT(20) DEFAULT '1'
) ENGINE=INNODB DEFAULT CHARSET=utf8;
第二步:执行导出命令
通过sqoop来实现数据的导出,将hdfs的数据导出到mysql当中去
bin/sqoop export \
--connect jdbc:mysql://node03:3306/userdb \
--username root --password 123456 \
--table emp_out \
--export-dir /sqoop/emp \
--input-fields-terminated-by ","
第三步:验证mysql表数据
2. 将数据从HBase导出到MySQL
-
将hbase_book这张表当中的数据导出到mysql当中来
-
注意:sqoop不支持我们直接将HBase当中的数据导出,所以我们可以通过以下的转换进行导出
Hbase→hive外部表→hive内部表→通过sqoop→mysql
第一步:创建hive外部表
-
进入hive客户端,创建hive外部表,映射hbase当中的hbase_book表
CREATE EXTERNAL TABLE course.hbase2mysql (id int,name string,price int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" =
":key,info:name, info:price"
)
TBLPROPERTIES( "hbase.table.name" = "hbase_book",
"hbase.mapred.output.outputtable" = "hbase2mysql");
第二步:创建hive内部表并将外部表数据插入到内部表当中来
-
进入hive客户端,执行以下命令,创建hive内部表,并将外部表的数据插入到hive的内部表当中来
CREATE TABLE course.hbase2mysqlin(id int,name string,price int);
第三步:外部表数据插入内部表
-
进入hive客户端执行以下命令,将hive外部表数据插入到hive内部表当中来
insert overwrite table course.hbase2mysqlin select * from course.hbase2mysql;
第四步:清空mysql表数据
-
进入mysql客户端,执行以下命令,将mysql表数据清空
TRUNCATE TABLE book;
第五步:执行sqoop导出hive内部表数据到
sqoop export -connect jdbc:mysql://node03:3306/library -username root -password 123456 -table book -export-dir /user/hive/warehouse/course.db/hbase2mysqlin --input-fields-terminated-by '\001' --input-null-string '\\N' --input-null-non-string '\\N';