Sqoop基本原理

一、Sqoop基本原理

① Sqoop是什么

Sqoop(SQL-to-Hadoop)是一款开源的工具，主要用于在Hadoop(Hive)与传统的数据库(mysql、postgresql…)间进行数据的传递，可以将一个关系型数据库（例如 ： MySQL ,Oracle ,Postgres等）中的数据导入到Hadoop的HDFS中，也可以将HDFS的数据导出到关系型数据库中。

② Sqoop使用场景

我们通常把有价值的数据存储在关系型数据库系统中，以行和列的形式存储数据，以便于用户读取和查询。但是当遇到海量数据时，我们需要把数据提取出来，通过MapReduce对数据进行加工，获得更符合我们需求的数据。数据的导入和导出本质上是Mapreduce程序，充分利用了MR的并行化和容错性。为了能够和HDFS系统之外的数据库系统进行数据交互，MapReduce程序需要使用外部API来访问数据，因此我们需要用到Sqoop。

③ 关系图

④ 架构图

在 mapreduce 中主要是对 inputformat 和 outputformat 进行定制。

Sqoop工具接收到客户端的shell命令或者Java api命令后，通过Sqoop中的任务翻译器(Task Translator)将命令转换为对应的MapReduce任务，而后将关系型数据库和Hadoop中的数据进行相互转移，进而完成数据的拷贝。

二、Sqoop可用命令

对于不同的命令，有不同的参数，这里给大家列出来了一部分Sqoop操作时的常用参数，以供参考，需要深入学习的可以参看对应类的源代码，本文目前介绍常用的导入、导出的一些命令。

1. 公用参数：数据库连接

2. 公用参数：import

3. 公用参数：export

4. 公用参数：hive

5. 其余

三、Sqoop常用方法

先在mysql中建一张表来使用

插入几条数据

3.1、RDBMS => HDFS (导入重点)

3.1.1、全表导入

命令：

MR的数据处理过程：

查看/sqoop/table_all目录下的数据结果：

查看hdfs的数据

hdfs dfs -cat /sqoop/table_all/part-m-0000

数据结果如下:

3.1.3、导入指定列

注意:columns中如果涉及到多列，用逗号分隔，分隔时不要添加空格

hdfs dfs -cat /sqoop/table_all/part-m-0000

数据结果如下:

3.1.4、where语句过滤

源表数据:

得到了如下 “sid>=6” 的数据:

3.1.5、①增量导入 append

–split-by 和 -m 结合实现numberReduceTasks并行

后面两句
–check-column sid
–last-value 0
结合使用的效果类似于where sid>0

MR过程中部分关键信息如下

因为有两个maptask，所以会分成两份文件

hdfs dfs -cat /sqoop/table_all/part-m-*

数据结果如下:

此时往mysql中再添加几条数据，再进行一次增量导入

​insert into student(sid,sname,gender,age) values(7,'云中君','男',19),(8,'百里玄策','男',20),(9,'裴擒虎','男',17);

​再执行一次增量导入

此时多了一个文件

hdfs dfs -cat /sqooptest/add1/part-m-*

数据结果如下:

3.1.5、②增量导入 lastmodified

先在mysql创建一张新表:

insert into orderinfo(oid,oName,oPrice,oTime) values(1,'爱疯12',6500.0,'2020-11-11 00:00:00'),(2,'华为xpro',12000.0,'2020-10-1 12:52:33'),(3,'行李箱',888.8,'2019-5-22 21:56:17'),(4,'羽绒服',1100.0,'2018-3-7 14:22:31');

–incremental lastmodified修改和增加 此时搭配–check-column 必须为timestamp类型

使用lastmodified方式导入数据要指定增量数据是要–append（追加）还是要–merge-key（合并）

hdfs dfs -cat /sqooptest/lastmod/part-m-00000

数据结果如下:

往mysql的orderinfo表中新插入几条数据，然后增量导入:

insert into orderinfo(oid,oName,oPrice,oTime) values(5,'帕拉梅拉',1333333.3,'2020-4-7 12:23:34'),(6,'保温杯',86.5,'2017-3-5 22:52:16'),(7,'枸杞',46.3,'2019-10-5 11:11:11'),(8,'电动牙刷',350.0,'2019-9-9 12:21:41');

执行后合并了文件

从part-m-00000变成了part-r-00000

hdfs dfs -cat /sqooptest/lastmod/part-r-00000

数据结果如下:

发现只添加了两条记录，因为序号为6和8的记录的时间不在–last-value的范围内

3.2、RDBMS => HBase

先在hbase中建表:

hbase(main):007:0> create 'sqooptest:sqstudent','stuinfo'

使用sqoop开始导入数据:

–column-family stuinfo

指定列族为stuinfo

–hbase-create-table

若表不存在，则自动创建

–hbase-row-key sid

指定行键为sid

查看hbase表数据

​hbase(main):008:0> scan 'sqooptest:sqstudent'

ROW                         COLUMN+CELL                                                                    

1                          column=stuinfo:age, timestamp=1605958889301, value=15                          

1                          column=stuinfo:gender, timestamp=1605958889301, value=\xE5\xA5\xB3              

1                          column=stuinfo:sname, timestamp=1605958889301, value=\xE5\xAD\x99\xE5\xB0\x9A\x

                           E9\xA6\x99                                                                      

2                          column=stuinfo:age, timestamp=1605958889301, value=16                          

2                          column=stuinfo:gender, timestamp=1605958889301, value=\xE5\xA5\xB3              

2                          column=stuinfo:sname, timestamp=1605958889301, value=\xE8\xB2\x82\xE8\x9D\x89  

...

...

...                                                      

9                          column=stuinfo:age, timestamp=1605958892765, value=17                          

9                          column=stuinfo:gender, timestamp=1605958892765, value=\xE7\x94\xB7              

9                          column=stuinfo:sname, timestamp=1605958892765, value=\xE8\xA3\xB4\xE6\x93\x92\x

                           E8\x99\x8E                                                                      

9 row(s) in 0.1830 seconds

HBase中的数据没有数据类型，统一存储为字节码，是否显示具体的汉字只是前端显示问题，此处没有解决，因此gender和sname字段显示的都是字节码

3.3、RDBMS => Hive

3.3.1、导入普通表

将mysql中retail_db库下的orders表导入hive

导入过程日志会提示mapreduce过程等信息，导入成功日志最后会提示如下信息:

在hdfs的hive工作目录中生成文件

查看一下hive的orders表数据

3.3.2、导入分区表

执行结果

查询结果

3.4、Hive/Hdfs => RDBMS

先在mysql中建表

我们把刚才在hive中创建的sqstudent表数据再导出到mysql中

查看一下mysql中的数据结果

3.5、Sqoop Job

job参数说明:

创建job

• -和import之间有个空格。这里–空格之后表示给job添加参数，而恰好import又不需要–，所以这个空格很容易被忽略。

查看job:

​sqoop job --list

结果:

显示job:

结果

此时未配备密码，因此在过程中需要输入密码:

删除job

sqoop job --delete myjob

保存密码

保存的job里并没有保存明文的password，所以每次使用job，都会要求提供密码【-P交互式】。

而在使用Oozie等这些自动化调度的时候，是没有机会输入password的。所以需要保存password。

1、使用–password-file文件

文件存放在/home/user(主机名)下，文件的权限应该是400，支持本地文件和HDFS。

我们新建一个指定密码文件路径的job:

查看新建的job:

执行job：

sqoop job --exec pwdjob

执行过程如下，已经不需要输入密码：

查看执行结果

hdfs dfs -cat /sqooptest/pwdjob/part-m-00000

结果数据如下：

2、保存在sqoop元数据里

You can enable passwords in the metastore by setting sqoop.metastore.client.record.password to true in the configuration.

​配置文件在conf/sqoop-site.xml里，设置之后的第一次运行，需要提供密码。