Sqoop学习笔记

最新推荐文章于 2024-06-01 17:46:31 发布

怕被各位卷死

最新推荐文章于 2024-06-01 17:46:31 发布

阅读量97

点赞数 1

分类专栏：大数据学习笔记文章标签： hadoop sqoop 学习 Powered by 金山文档

本文链接：https://blog.csdn.net/weixin_45077811/article/details/128821474

版权

大数据学习笔记专栏收录该内容

20 篇文章 5 订阅

订阅专栏

1 Sqoop概述

传统的应用程序管理系统，即应用程序与使用RDBMS的关系数据库的交互，是产生大数据的来源之一。由RDBMS生成的这种大数据存储在关系数据库结构中的关系数据库服务器中。

当大数据存储和Hadoop生态系统的MapReduce，Hive，HBase，Cassandra，Pig等分析器出现时，他们需要一种工具来与关系数据库服务器进行交互，以导入和导出驻留在其中的大数据。在这里，Sqoop在Hadoop生态系统中占据一席之地，以便在关系数据库服务器和Hadoop的HDFS之间提供可行的交互。

Sqoop - “SQL到Hadoop和Hadoop到SQL”

Sqoop是一个用于在Hadoop和关系数据库服务器之间传输数据的工具。它用于从关系数据库（如MySQL，Oracle）导入数据到Hadoop HDFS，并从Hadoop文件系统导出到关系数据库。它由Apache软件基金会提供。

Sqoop如何工作？

下图描述了Sqoop的工作流程。

Sqoop导入

导入工具从RDBMS向HDFS导入单独的表。表中的每一行都被视为HDFS中的记录。所有记录都以文本文件的形式存储在文本文件中或作为Avro和Sequence文件中的二进制数据存储。

Sqoop导出

导出工具将一组文件从HDFS导出回RDBMS。给Sqoop输入的文件包含记录，这些记录在表中被称为行。这些被读取并解析成一组记录并用用户指定的分隔符分隔。

2 Sqoop安装

由于Sqoop是Hadoop的子项目，因此它只能在Linux操作系统上运行。按照以下步骤在您的系统上安装Sqoop。

1 安装java

2 安装Hadoop

3 Sqoop安装配置

1.下载

Sqoop 安装包下载地址：Sqoop 官网

选择自己集群合适的版本。

2.安装

tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /opt/module/

改个名称：

cd /opt/module
mv sqoop-1.4.7.bin__hadoop-2.6.0/ sqoop-1.4.7

3.添加环境变量

vi /etc/profile.d/my_env.sh

# 在文件末尾添加

#SQOOP_HOME
export SQOOP_HOME=/opt/module/sqoop-1.4.7
export PATH=$PATH:$SQOOP_HOME/bin

保存退出，使环境变量立即生效 source /etc/profile。

4.配置 Sqoop 环境变量文件

1.切换到 Sqoop 配置文件目录

cd $SQOOP_HOME/conf

2.复制 Sqoop 环境变量模板文件

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

3.编辑文件，指定相关路径

vim sqoop-env.sh

配置如下，根据自己的集群情况来看。

这里只指定了 HADOOP 和 Hive 的路径，注意删掉前面的符号 # ，否则会被认为是一行注释。

5. MySQL 驱动

拷贝 MySQL 驱动到 Sqoop 中的 lib 目录中。

cp /opt/software/mysql-connector-java-5.1.37-bin.jar $SQOOP_HOME/lib

6. 拷贝 Hive 文件

为了后续方便操作 Hive，我们需要将 Hive 的驱动放入 Sqoop 的 lib 目录中。

cp hive-3.1.2/lib/hive-common-3.1.2.jar sqoop-1.4.7/lib/

7.验证

输入 sqoop version，出现如下版本信息表示安装成功。

现在来测试功能是否可以正常使用：

展示 MySQL 中 sys 库下的所有表。

sqoop list-tables \
--connect jdbc:mysql://localhost:3306/sys \
--username root \
--password 000000

注意改成你自己的数据库连接信息。

能查出表数据并且无报错则正常。

至此，我们的 Sqoop 就已经安装完成啦。

8.去除警告信息

使用 Sqoop 时的一些警告信息真令人头大，我们可以手动去关闭掉这些警告信息。

# 切换到 Sqoop 目录
cd $SQOOP_HOME/bin

# 编辑文件
vi configure-sqoop

注释如下内容：

再次输入 sqoop version 查看：

瞬间就舒服多了，哈哈哈。

3 Sqoop基本原理

1.1、何为Sqoop？

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

1.2、为什么需要用Sqoop？

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

1.3、关系图

1.4、架构图

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

4 Sqoop常用方法

先在mysql中建一张表来使用

create table student(
    sid int primary key,
    sname varchar(16) not null,
    gender enum('女','男') not null default '男',
    age int not null
);

insert into student(sid,sname,gender,age) values
(1,'孙尚香','女',15),
(2,'貂蝉','女',16),
(3,'刘备','男',17),
(4,'孙二娘','女',16),
(5,'张飞','男',15),
(6,'关羽','男',18),

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

4.1.1、全表导入

命令：

sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--table student \
--target-dir /sqooptest/table_all \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by ','

MR的数据处理过程

查看/sqooptest/table_all目录下，生成了数据结果

查看hdfs的数据

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

数据结果如下

1,孙尚香,女,15
2,貂蝉,女,16
3,刘备,男,17
4,孙二娘,女,16
5,张飞,男,15
6,关羽,男,18

4.1.2、查询导入

sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--target-dir /sqooptest/select_test \
--num-mappers 1 \
--query 'select sname,gender from student where $CONDITIONS'

where语句中必须有 $CONDITIONS，表示将查询结果带回。如果query后使用的是双引号，则 $CONDITIONS前必须加转移符，防止shell识别为自己的变量。

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

数据结果如下

孙尚香,女
貂蝉,女
刘备,男
孙二娘,女
张飞,男
关羽,男

4.1.3、导入指定列

sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--table student \
--columns sid,sname,age \
--target-dir /sqooptest/column_test \
--num-mappers 1 \
--fields-terminated-by "|"

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

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

数据结果如下

1|孙尚香|15
2|貂蝉|16
3|刘备|17
4|孙二娘|16
5|张飞|15
6|关羽|18

4.1.4、where语句过滤

源表数据

sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--table student \
--where "sid>=6" \
--target-dir /sqooptest/wheretest \
-m 2

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

[root@single ~]# hdfs dfs -cat /sqooptest/wheretest/*
6,关羽,男,18
7,云中君,男,19
8,百里玄策,男,20
9,裴擒虎,男,17

4.1.5、①增量导入 append

sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--query "select sid,sname,gender from student where \$CONDITIONS" \
--target-dir /sqooptest/add1 \
--split-by sid \
-m 2 \
--incremental append \
--check-column sid \
--last-value 0

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

后面两句

–check-column sid

–last-value 0

结合使用的效果类似于where sid>0

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

--sid界限值是0-6
20/11/20 05:17:42 INFO tool.ImportTool: Incremental import based on column `sid`
20/11/20 05:17:42 INFO tool.ImportTool: Lower bound value: 0
20/11/20 05:17:42 INFO tool.ImportTool: Upper bound value: 6
--条件是where `sid` > 0 AND `sid` <= 6
20/11/20 05:17:48 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(sid), MAX(sid) FROM (select sid,sname,gender from student where `sid` > 0 AND `sid` <= 6 AND  (1 = 1) ) AS t1
--指定了两个maptask
20/11/20 05:17:48 INFO mapreduce.JobSubmitter: number of splits:2
--提示last-value即sid是6
20/11/20 05:18:06 INFO tool.ImportTool:  --incremental append
20/11/20 05:18:06 INFO tool.ImportTool:   --check-column sid
20/11/20 05:18:06 INFO tool.ImportTool:   --last-value 6

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

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

数据结果如下

1,孙尚香,女
2,貂蝉,女
3,刘备,男
4,孙二娘,女
5,张飞,男
6,关羽,男

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

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

再执行一次增量导入

sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--query "select sid,sname,gender,age from student where \$CONDITIONS" \
--target-dir /sqooptest/add1 \
-m 1 \
--incremental append \
--check-column sid \
--last-value 6

此时多了一个文件

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

数据结果如下

1,孙尚香,女
2,貂蝉,女
3,刘备,男
4,孙二娘,女
5,张飞,男
6,关羽,男
7,云中君,男,19
8,百里玄策,男,20
9,裴擒虎,男,17

4.1.5、②增量导入 lastmodified

先在mysql创建一张新表

create table orderinfo(
  oid int primary key,
  oName varchar(10) not null,
  oPrice double not null,
  oTime timestamp not null
);

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（合并）

sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--table orderinfo \
--target-dir /sqooptest/lastmod \
-m 1

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

数据结果如下

1,爱疯12,6500.0,2020-11-11 00:00:00.0
2,华为xpro,12000.0,2020-10-01 12:52:33.0
3,行李箱,888.8,2019-05-22 21:56:17.0
4,羽绒服,1100.0,2018-03-07 14:22:31.0

往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');

sqoop import \
--connect jdbc:mysql://single:3306/sqoop_test \
--username root \
--password kb10 \
--table orderinfo \
--target-dir /sqooptest/lastmod \
-m 1 \
--incremental lastmodified \
--check-column oTime \
--merge-key oid \
--last-value "2019-10-1 12:12:12"

执行后合并了文件

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

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

数据结果如下

1,爱疯12,6500.0,2020-11-11 00:00:00.0
2,华为xpro,12000.0,2020-10-01 12:52:33.0
3,行李箱,888.8,2019-05-22 21:56:17.0
4,羽绒服,1100.0,2018-03-07 14:22:31.0
5,帕拉梅拉,1333333.3,2020-04-07 12:23:34.0
7,枸杞,46.3,2019-10-05 11:11:11.0

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