09-Sqoop

YuPangZa

于 2023-09-05 16:13:03 发布

阅读量220

点赞数 1

分类专栏：大数据文章标签： sqoop hadoop hive

本文链接：https://blog.csdn.net/qq_43819048/article/details/132690737

版权

大数据专栏收录该内容

30 篇文章 0 订阅

订阅专栏

一、Sqoop

1、理论

Sqoop 是一个数据库数据导入导出工具
Flume 是一个日志数据抽取工具

Sqoop的核心设计思想是利用MapReduce加快数据传输速度。也就是说Sqoop的导入和导出功能是通过基于Map Task（只有map）的MapReduce作业实现的。所以它是一种批处理方式进行数据传输，难以实现实时的数据进行导入和导出。

MySQL: 关系型数据库 （Oracle, SQL Server, DB2 )  -- 业务数据
HDFS:  海量数据存储（文件类型的）分布式文件存储系统
Hive:  数据分析工具（将SQL翻译为MR)
HBase:  非关系型数据库（NoSQL)  学习的Redis

Sqoop 是一个数据导入导出工具，可以将MySQL的数据导入到HDFS，Hive,Hbase中。
也可以将HDFS,Hive 中的数据导出到mysql。

Sqoop 底层也是使用的MapReduce 中的Map

2、安装

上传，解压：

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

重命名：
mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop

也可以使用如下命令：--strip-components 1 解压后获取一层里面的内容
mkdir /opt/installs/sqoop && tar -xzvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /opt/installs/sqoop --strip-components 1

重命名，并且开始配置环境变量：

/etc/profile

shift + g 直接到最后一行

export SQOOP_HOME=/opt/installs/sqoop
export PATH=$PATH:$SQOOP_HOME/bin

source /etc/profile

在finalshell中假如你进行ctrl + s 窗口就不动了。

修改配置文件：

进入到/opt/installs/sqoop/conf

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

修改这个可执行脚本中的变量

拷贝一个mysql8.0的驱动包

将这个驱动包，放入到 sqoop 的 lib 目录下

3、使用一下

sqoop help

1) 展示一个数据库中的所有数据库

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

如果出现拒绝连接的情况：
1）驱动包导入错了    mysql8.0 需要 8.0驱动包
2）IP写错了   不要连接我的，连不上
3）mysql 没启动  肯定连不上

通过这个类，找是在哪个jar包中：commons-lang.xxx .jar

find /opt/installs -name commons-lang-2.6.jar

拷贝一个到 sqoop的lib 下

cp /opt/installs/hive/lib/commons-lang-2.6.jar /opt/installs/sqoop/lib/

再次执行以上的连接命令：

2）可以将命令写在一个文件中，执行该文件：

a.conf

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

sqoop --options-file a.conf

这个a.conf 中不要使用 \ ，不要将语句写成一行。前面也不要写sqoop

3）连接超时

修改一下mysql的配置：

mysql的连接超时时间，默认是8小时，可以修改为1天。修改我们的 /etc/my.cnf 中的文件即可：

wait_timeout=31536000
interactive_timeout=31536000

添加完之后，要重启mysql服务

systemctl restart mysqld

4、import 命令

先说一些通用的参数：

展示一个数据库中的所有表：

sqoop list-tables   \
  --connect jdbc:mysql://bigdata01:3306/sqoop  \
  --driver com.mysql.cj.jdbc.Driver  \
  --username root  \
  --password 123456

创建一个数据库sqoop,并且导入数据：

CREATE TABLE emp(
    empno        INT PRIMARY KEY,
    ename        VARCHAR(50),
    job        VARCHAR(50),
    mgr        INT,
    hiredate    DATE,
    sal        DECIMAL(7,2),
    comm        DECIMAL(7,2),
    deptno        INT
) ;
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

import 导入命令的参数：

将mysql的emp这个表的数据导入到hdfs上：

sqoop import  \
  --connect jdbc:mysql://bigdata01:3306/sqoop  \
  --driver com.mysql.cj.jdbc.Driver  \
  --username root  \
  --password 123456 \
  --table emp \
  --target-dir /home  \
  --delete-target-dir

1、一定要记得的是 Sqoop的执行脚本，底层使用的MR中的map，所以输出文件的名字都是part-m 开头

2、为什么是4个文件呢，原因是sqoop 底层默认开启了4个map任务，所以有四个输出，可以自己修改map任务的数量

3、数据切割为什么不均匀？

不是按照条数平均切割的，而是按照主键，以及主键的值进行切割的。

继续操作：

将mysql的数据导入到HDFS

1、如果想将一个表中的数据，只导入一部分，可以加where 条件

sqoop import  \
  --connect jdbc:mysql://bigdata01:3306/sqoop  \
  --driver com.mysql.cj.jdbc.Driver  \
  --username root  \
  --password 123456 \
  --table emp \
  --target-dir /home  \
  --columns 'ename,job'   \
  --where  'empno < 7788'  \
  --delete-target-dir

2、Sqoop 也支持SQL语句

qoop import  \
  --connect jdbc:mysql://bigdata01:3306/sqoop  \
  --driver com.mysql.cj.jdbc.Driver  \
  --username root  \
  --password 123456 \
  --query 'select * from emp where empno < 7788 and $CONDITIONS'  \
  --target-dir /home  \
  --delete-target-dir \
  --split-by empno

如下坑：
1、query的时候，sql语句中必须含有and $CONDITIONS
2、query的时候，必须使用split-by
3、指定split-by的时候，查询结果必须包含这个列，否则报：无法识别的列

假如需要将结果展示到一个文件中，指定map数量为 1
-m 1
如果使用了m 1 ,也就是说我的map只有一个，只有一个，就意味着不需要分割为4份了，所以也就不需要指定分割字段是什么了。
sqoop import  \
  --connect jdbc:mysql://bigdata01:3306/sqoop  \
  --driver com.mysql.cj.jdbc.Driver  \
  --username root  \
  --password 123456 \
  --query 'select * from emp where empno < 7788 and $CONDITIONS'  \
  --target-dir /home  \
  --delete-target-dir \
  -m 1

假如不添加 split-by 

When importing query results in parallel, you must specify --split-by.
Try --help for usage instructions.

如果你使用--query 编写了一个SQL语句，表名，列名，where条件都不需要写了，因为都在SQL语句中了
，但是一定要注意的是 SQL语句中必须添加 $CONDITIONS ，而这个字段，必须跟split-by 一起使用，才行。

假如你的表中没有主键，就导入hdfs的话，会报错：
先将emp表中的主键给删除掉：

sqoop import  \
  --connect jdbc:mysql://bigdata01:3306/sqoop  \
  --driver com.mysql.cj.jdbc.Driver  \
  --username root  \
  --password 123456 \
  --table emp \
  --target-dir /home  \
  --columns 'ename,job'   \
  --where  'empno < 7788'  \
  --delete-target-dir

修改一下：

sqoop import  \
  --connect jdbc:mysql://bigdata01:3306/sqoop  \
  --driver com.mysql.cj.jdbc.Driver  \
  --username root  \
  --password 123456 \
  --table emp \
  --target-dir /home  \
  --columns 'ename,job'   \
  --where  'empno < 7788'  \
  --delete-target-dir  \
   -m 1

如果没有主键，map任务就不知道按照哪个字段切割数据了，所以要么指定一个字段，要么就只生成一个文件。
所以   --split-by  和  -m 1   二选一即可

MySQL数据导入到Hive

sqoop import --connect jdbc:mysql://bigdata01:3306/sqoop \
--username root \
--password 123456 \
--table emp \
--hive-import \
--hive-overwrite \
--hive-table "emp2" \
--hive-database yhdb \
-m 1

缺少Jar包：

cp /usr/local/hive/lib/hive-exec-3.1.2.jar /usr/local/sqoop/lib/   ---此包暂时不拷贝
cp /opt/installs/hive3.1.2/lib/hive-common-3.1.2.jar /opt/installs/sqoop/lib/

数据库必须存在，否则报错！！！
hive中的表，不需要事先创建好，导入的时候会自行创建。

如果出现以上问题，就将emp文件夹删除即可。

增量导入数据：

1、什么是全量，什么是增量

全量导入：一次性将一个数据库的所有数据，导入到另一个地方。

增量导入：每次导入的时候只需要将新增的数据导入到另一个地方即可。

一般的做法是：第一次全量导入，后面每个一段时间进行一次增量导入。

2、实现增量导入的两种方案

1）使用where 条件进行增量导入，必须有一个时间字段。

假如今天想导入昨天全天的数据：

select * from emp where dt>='2023-09-04 00:00:00' and dt <='2023-09-04 23:59:59'

本质上其实就是每天执行的where条件的数据不一样而已
可以配合我们的shell脚本一起使用

创建一个表
create table sales_order(
   orderId int primary key,
   order_date date
)

import.sh

#!/bin/bash
# 获取昨天的日期
# yesterday=`date -d "1 days ago" "+%Y-%m-%d"`
yesterday=$1
sqoop import --connect jdbc:mysql://192.168.52.12:3306/sqoop \
--username root --password 123456 \
--query "select * from sales_order where order_date='${yesterday}' and \$CONDITIONS" \
--target-dir /home/dt=${yesterday} \
--delete-target-dir \
-m 1 \
--fields-terminated-by '\t'

这个脚本放在哪里无所谓，需要给定权限 chmod 777 import.sh

使用的时候 ./import.sh 2022-08-08

2）可以使用last-value 这样一个小的功能实现增量导入

sqoop import --connect jdbc:mysql://bigdata01:3306/sqoop \
--username root \
--password 123456 \
--table sales_order \
--driver com.mysql.cj.jdbc.Driver \
--target-dir /home/sales_order/dt=202306 \
-m 1 \
--check-column orderId \
--incremental append \
--last-value 0 \
--fields-terminated-by '\t'

sqoop import --connect jdbc:mysql://bigdata01:3306/sqoop \
--username root \
--password 123456 \
--table sales_order \
--driver com.mysql.cj.jdbc.Driver \
--target-dir /home/sales_order/dt=202306 \
--split-by orderId \
-m 1 \
--check-column orderId \
--incremental append \
--last-value 4 \
--fields-terminated-by '\t'

使用last-value 实现增量导入
优点：不需要依赖日期字段
缺点：每次导入之后都要记录last-value的值。

5、Sqoop的export

导入export 就是从大数据平台 --> 关系型数据库中

5.1、从hdfs 导出到mysql

先在hdfs上创建一些数据：

a.txt

1,18
2,20
3,30

上传至hdfs

hdfs dfs -put a.txt /home

mysql中创建数据库：

CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

编写Sqoop的脚本文件：

sqoop export --connect jdbc:mysql://bigdata01:3306/sqoop \
--username root \
--password 123456 \
--table user \
--export-dir '/home/a.txt' \
--input-fields-terminated-by ',' \
--columns 'id,age'  \
-m 1

假如从hdfs导出到mysql的时候，出现了列的顺序不一致问题，可以通过追加--columns 来解决

5.2、从hive导出到mysql

先创建一个hive表，表中必须有数据：

create  table if not exists par3(
 `id` int,
 `age` int
)
row format delimited 
fields terminated by ',';

load data local inpath '/root/a.txt' into table par3;

在mysql中创建数据库：

CREATE TABLE `user3` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

编写Sqoop脚本：

sqoop export \
--connect jdbc:mysql://bigdata01:3306/sqoop \
--username root \
--password 123456 \
--table user3 \
--hcatalog-database default \
--hcatalog-table par3 \
-m 1

假如出现了这个错误：

只需要在sqoop-env.sh 中添加 export HCAT_HOME=/opt/installs/hive3.1.2/hcatalog 即可。

二、Sqoop中的Job任务

创建job任务好处：
1、可以将经常使用的导入导出脚本，编写为job任务，每次执行一下命令即可
2、可以帮助我们记录last-value,从而更好的利用last-value进行增量导入

job命令中有哪些参数：

usage: sqoop job [GENERIC-ARGS] [JOB-ARGS] [-- [<tool-name>] [TOOL-ARGS]]

Job management arguments:
   --create <job-id>            Create a new saved job
   --delete <job-id>            Delete a saved job
   --exec <job-id>              Run a saved job
   --help                       Print usage instructions
   --list                       List saved jobs
   --meta-connect <jdbc-uri>    Specify JDBC connect string for the
                                metastore
   --show <job-id>              Show the parameters for a saved job
   --verbose                    Print more information while working

创建一个job任务：

sqoop job --create jjoobb -- import  --connect jdbc:mysql://bigdata01:3306/sqoop \
--username root \
--password 123456 \
--table user \
--delete-target-dir \
--target-dir '/sqoopdata/user' \
-m 1

如果出现上面的关于Json的错误，请进行如下修复：

1）在sqoop/lib 下，拷贝一个 java-json的jar包：

2） sqoop-env.sh 中添加 export HCAT_HOME=/opt/installs/hive3.1.2/hcatalog

3）删除 sqoop中的lib中 hive-exec-3.1.2.jar 这个jar包(可选)。

以上操作完成后，继续创建job任务，如果创建好了之后，就可以执行了

sqoop job --exec jjoobb

要输入mysql的密码。

总结：

sqoop job --create  jod的名字
sqoop job --list
sqoop job --delete job的名字
sqoop job --exec job的名字

三、使用Job任务实现增量导入

通过last-value的增量导入代码，实现一个job任务：

sqoop job --create auto_import -- import --connect jdbc:mysql://bigdata01:3306/sqoop \
--username root \
--password 123456 \
--table sales_order \
--driver com.mysql.cj.jdbc.Driver \
--target-dir /home/sales_order/dt=20230625 \
--split-by orderId \
-m 1 \
--check-column orderId \
--incremental append \
--last-value 0 \
--fields-terminated-by '\t'

执行该脚本：

sqoop job --exec auto_import

在数据库中新增两条数据，再次执行这个任务，就能看到增量导入的效果。

四、从mysql导入hdfs的时候可以指定在hdfs上存储格式

sqoop import --connect jdbc:mysql://bigdata01:3306/sqoop \
--username root \
--password 123456 \
--table user \
--delete-target-dir \
--target-dir '/sqoopdata/user2' \
-m 1 \
--fields-terminated-by '\t' \
--null-string '\\N' \
--null-non-string '0' \
--as-parquetfile

--null-string '\\N' \
--null-non-string '0' \

假如我们的mysql中的数据有null值如何处理？
就看有null值的字段是什么类型的，如果是string类型，就导出成 \N
如果是数值类型的，就导入变为 0

-as-parquetfile    将mysql数据导出到hdfs的时候，是parquet类型的

假如在hdfs上有一个文件，创建一个hive表，指定这个表的数据的位置就是这个文件所在的位置。
请问：hive中有数据吗？ 答案是有数据！！！！

现在创建一个表，字段跟数据的文件字段数量和类型都一样，最重要的是数据类型和存储位置一定要要分析的文件一样。

create external table users(
id int,
age int
)
row format delimited
fields terminated by '\t'
stored as parquet
location '/sqoopdata/user2'

YuPangZa

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
09-Sqoop

2、为什么是4个文件呢，原因是sqoop 底层默认开启了4个map任务，所以有四个输出，可以自己修改map任务的数量。1、一定要记得的是 Sqoop的执行脚本，底层使用的MR中的map，所以输出文件的名字都是part-m 开头。现在创建一个表，字段跟数据的文件字段数量和类型都一样，最重要的是数据类型和存储位置一定要要分析的文件一样。假如从hdfs导出到mysql的时候，出现了列的顺序不一致问题，可以通过追加--columns 来解决。在数据库中新增两条数据，再次执行这个任务，就能看到增量导入的效果。
复制链接

扫一扫