Summary(提炼):

在玩Sqoop时候最重要的是搞清楚到底是数据导入还是导出，抓不住这点你就是迷迷糊糊的(不是我）

import == 是mysql导入数据到Hive，HDFS，HBase

export == 刚好相反

对于Sqoop我的建议是记住一两个常用的数据导入方式(通过HCatlog将数据从mysql导入到Hive)

Linux中手动进入Hive二代客户端的命令：

beeline

!connect jdbc:hive2://hadoop01:10000 (注意!和connect中间可以没有空格)

Tips:Swoop是在Linux中直接执行的

数据导入

数据导入-MySQL到HDFS

指定分隔符

将数据从MySQL的userdb数据库的表tmp导出数据到HDFS上，

并指定存储位置为/sqoop/result2以及分割符为\t

1
2
3
4
5
6
7
sqoop import \
--connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password 123456 \
--target-dir /sqoop/result2 \
--fields-terminated-by '\t' \
--table emp --m 1

–target-dir 表示目标目录的路径, 即: 导入到的HDFS的路径(子目录必须不存在, 会自动创建).
–table 表示数据源表, 即: MySQL的表名
–m MapReduce的任务数, 1表示1个分区, 即: 最终结果在1个文件中.

指定任务并行度（maptask个数）

注意：指定任务并行度时，指定几个MapTask则最终会得到几个文件，且需要有主键，如果没有主键需要手动指定分割列。

1
2
3
4
5
6
7
8
sqoop import \
--connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password 123456 \
--target-dir /sqoop/result3 \
--fields-terminated-by '\t' \
--split-by id \
--table emp --m 2

上述的命令最后是 –m 2, 即两个MapReduce, 最终结果会到2个文件中, 但是因为上述表 没有主键(特点: 唯一, 非空),
且如果我们没有自己手动指定 分割列, 则上述的代码会报错. 加入 –split-by 切割字段即可, 具体如下:建议切割字段最好是id列, 或者是数字列.

数据导入-Mysql 到Hive

这也是以后会用的比较多的点

先在hive中创建test数据库。

用Sqoop在hive中建表

1
2
3
4
5
6
sqoop create-hive-table \
--connect jdbc:mysql://hadoop01:3306/userdb \
--table emp_add \
--username root \
--password 123456 \
--hive-table test.emp_add_hive

注意这种方式建表，只是创建表的结构，具体数据并没有在hive中插入。

往数据表中导入数据

1
2
3
4
5
6
7
8
sqoop import \
--connect jdbc:mysql://hadoop01:3306/userdb \
--table emp_add \
--username root \
--password 123456 \
--hive-table test.emp_add_hive \
--hive-import \
--m 1

上述两种方法可以合并为一句话：(哈哈有点坑爹，都这时候了早点说不行吗？)

1
2
3
4
5
6
7
8
sqoop import \
--connect jdbc:mysql://hadoop01:3306/userdb \
--table emp_add \
--username root \
--password 123456 \
--hive-database test \
--hive-import \
--m 1

全量导入

Mysql到Hive之全量导入※

在hive中建表（存储格式为ORC），注意中间不要有换行，一整行敲完。

1
2
3
4
5
6
7
8
9
createtable test.emp_hive(
	id int,
	name string,
	deg string,
	salary int,
	dept string
) 
row format delimited fields terminated by '\t' 
stored as orc;

将数据导入到Hive中※

这种方式导入不会成功，(因为Hive表示orc存储格式，使用原生的API虽然成功但是hive中没有数据)

1
2
3
4
5
6
7
8
9
sqoop import \
--connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password 123456 \
--table emp \
--fields-terminated-by '\t' \
--hive-database test \
--hive-table emp_hive \
--m 1

使用HCatlogAPI导入到HIVE中※

1
2
3
4
5
6
7
8
9
sqoop import \
--connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password 123456 \
--table emp \
--fields-terminated-by '\t' \
--hcatalog-database test \
--hcatalog-table emp_hive \
--m 1

Sqoop条件导入数据※

where方式主要过滤的是行

1
2
3
4
5
6
7
sqoop import \
--connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password 123456 \
--where "id >= 1203" \
--target-dir /sqoop/result4 \
--table emp --m 1

query方式, 主要过滤的是行和列方式比较灵活

要注意以下几点：

  1. 因为有SQL语句了, 所以不能写 —table
  2. query后边的SQL语句要用单引号包裹, 不能是双引号.
  3. query后边的SQL语句必须跟 where 查询条件.哪怕是写个where 1=1 也行，这是相当于不过滤
  4. query后边的SQL语句的最后必须跟上 $CONDITIONS 表示SQL语句结束.
1
2
3
4
5
6
7
sqoop import \
--connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password 123456 \
--query 'select id,name,salary from emp where id >= 1203 and $CONDITIONS;' \
--target-dir /sqoop/result5 \
--m 1

Sqoop原生API 和HCatalog之间的区别是什么?

1.数据格式支持

//这是实际中使用HCatalog的主要原因，否则还是原生的灵活一些.
Sqoop方式支持的数据格式较少;
HCatalog支持的数据格式多，包括RCFile, ORCFile, CSV, JSON和SequenceFile等格式。

2.数据覆盖

Sqoop方式允许数据覆盖，HCatalog不允许数据覆盖，每次都只是追加。

3.字段名匹配

Sqoop方式比较随意，不要求源表和目标表字段相同(字段名称和个数都可以不相同)，它抽取的方式是将字段按顺序插入，
比如: 目标表有3个字段，源表有一个字段，它会将数据插入到Hive表的第一个字段，其余字段为NULL。
但是HCatalog不同，源表和目标表字段名需要相同，字段个数可以不相等，如果字段名不同，抽取数据的时候会报
NullPointerException错误。HCatalog抽取数据时，会将字段对应到相同字段名的字段上，哪怕字段个数不相等。

总结:

HCatalog方式较之于Hive原生API, 优点是: 支持的数据格式更多一些, 例如: CSV, Orc, Json, SequenceFile等.
缺点是: 没有Hive原生API灵活, 例如: 它支持追加, 不支持覆盖, 且导入时要求 目标表字段名 和 原表字段名要一致.

增量导入

可以是导入到Hive数据表中，也可以导入到HDFS文件系统中。

关于增量导入的3个核心参数:
–check-column： //以哪一列的值作为增量的基准
–last-value： //指定上一次这一列的值是什么
–incremental： //指定增量的方式

Append模式

Append模式:
要求：
必须有一列自增的值，按照 自增的int值 进行判断
特点：
只能导入增加的数据，无法导入更新的数据

先全量导入表的数据

1
2
3
4
5
6
sqoop import \
--connect jdbc:mysql://192.168.88.80:3306/userdb \
--username root \
--password 123456 \
--target-dir /sqoop/appendresult \
--table emp --m 1

向MySQL数据表中添加数据

1
2
insert into `userdb`.`emp` (`id`, `name`, `deg`, `salary`, `dept`) values ('1206', 'allen', 'admin', '30000', 'tp');
insert into `userdb`.`emp` (`id`, `name`, `deg`, `salary`, `dept`) values ('1207', 'woon', 'admin', '40000', 'tp');

修改MySQL数据表中的数据

1
update emp set name='liuyifei', salary=80000 where id = 1201;

增量导入, 看是否会导入新增数据, 更新数据

1
2
3
4
5
6
7
8
9
sqoop import \
--connect jdbc:mysql://192.168.88.80:3306/userdb \
--username root \
--password 123456 \
--target-dir /sqoop/appendresult \
--table emp --m 1 \
--check-column id \
--last-value 1205 \
--incremental append

结论：Append模式只会导入增量数据，并不会导入更新数据

Lastmodified模式

不仅会导入新增数据, 还会导入修改数据,该模式要求,必须有一个动态修改的时间戳.

时间戳是createtime和updatetime，这也是后端程序员设计数据库时要考虑的两个字段。

要求：
必须包含动态时间变化这一列，按照数据变化的时间进行判断.
特点：
既导入新增的数据, 也导入更新的数据

创建数据表

在MySQL的userdb数据库中,创建数据表,该表有动态时间戳这一列, 用于记录该列值 添加或者修改的时间

1
2
3
4
5
create table customertest(
	id int,
	name varchar(20),
	last_mod timestamp default current_timestamp on update current_timestamp
);

添加数据

具体的添加数据的动作, 建议:不要批量执行,而是逐条执行, 因为MySQL的效率很高, 你要是批量执行了, 可能他们修改时间都一样

1
2
3
4
5
insert into customertest(id,name) values(1,'neil');
insert into customertest(id,name) values(2,'jack');
insert into customertest(id,name) values(3,'martin');
insert into customertest(id,name) values(4,'tony');
insert into customertest(id,name) values(5,'eric');

全量导入表的数据

1
2
3
4
5
6
sqoop import \
--connect jdbc:mysql://192.168.88.80:3306/userdb \
--username root \
--password 123456 \
--target-dir /sqoop/lastmodifiedsult \
--table customertest --m 1

往MySQL中插入数据

在MySQL中, 往 userdb数据库的 customertest 表中 新增1条数据.

1
insert into customertest(id,name) values(6,'james');

在MySQL中修改数据

在MySQL中, 往 userdb数据库的 customertest 表中 修改1条数据.

1
update customertest set name = 'LiuYiFei' where id = 1;

增量导入

采用lastmodified模式, 增量导入, 看是否会导入 新增数据, 更新数据. 

1
2
3
4
5
6
7
8
9
10
sqoop import \
--connect jdbc:mysql://192.168.88.80:3306/userdb \
--username root \
--password 123456 \
--target-dir /sqoop/lastmodifiedsult \
--table customertest --m 1 \
--check-column last_mod \
--last-value ' 2022-09-26 16:13:29' \
--incremental lastmodified \
--merge-key id
  1. merge-key这种模式是进行了一次完整的mapreduce操作，即: id一样的数据, 是同一条数据. 重复会覆盖,不重复会新增.
  2. 因此最终我们在lastmodifiedresult文件夹下可以发现id=1的name已经得到修改，同时新增了id=6的数据

结论：Append模式会导入增量数据，而且会导入更新数据

全量导出

将数据从Hadoop生态体系导出到RDBMS数据库导出前,目标表必须存在于目标数据库中。

导出数据-HDFS到MySQL

Sqoop导出数据的最大的特点是, (MySQL的)表需要我们手动创建

在mysql中创建数据库

1
2
3
4
5
6
7
create table employee ( 
   id int not null primary key, 
   name varchar(20), 
   deg varchar(20),
   salary int,
   dept varchar(10)
);

导出数据

从 HDFS的 /sqoop/result1 中 导出数据到 MySQL的 employee表中

1
2
3
4
5
6
sqoop export \
--connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password 123456 \
--export-dir /sqoop/result1 \
--table employee --m 1

导出数据-Hive到MySQL

清空MySQL的 userdb数据库的 employee表的数据

1
truncate table employee;

数据全量导出

1
2
3
4
5
6
7
8
9
sqoop export \
--connect jdbc:mysql://192.168.88.80:3306/userdb \
--username root \
--password 123456 \
--table employee \
--hcatalog-database test \
--hcatalog-table emp_hive \
--input-fields-terminated-by '\t' \
--m 1

注意，如果Hive中的表底层是使用ORC格式存储的，那么必须使用hcatalog API进行操作。

UpdateOnly增量导出

增量导出两种模式解释:
updateonly： //只增量导出更新(修改)的数据
allowinsert： //既导出更新的数据，也导出新增的数据

– update-key，更新标识，即根据某个字段进行更新，例如id，可以指定多个更新标识的字段，多个字段之间用逗号分隔。

– updatemod，指定updateonly（默认模式），仅仅更新已存在的数据记录，不会插入新纪录。

在HDFS中创建文件准备导出

在HDFS文件系统中/sqoop/updateonly/目录的下创建一个文件updateonly_1.txt

1
hadoop fs -mkdir -p /sqoop/updateonly/

通过Hue修改txt文件

1
2
3
1201,gopal,manager,50000
1202,manisha,preader,50000
1203,kalil,php dev,30000

创建MySQL数据库

1
2
3
4
5
6
CREATE TABLE updateonly ( 
   id INT NOT NULL PRIMARY KEY, 
   name VARCHAR(20), 
   deg VARCHAR(20),
   salary INT
);

全量导出

1
2
3
4
5
6
sqoop export \
--connect jdbc:mysql://hadoop01:3306/userdb \
--username root \
--password 123456 \
--table updateonly \
--export-dir /sqoop/updateonly/updateonly_1.txt

将updateonly_1.txt删除新建文件updateonly_2.txt

1
2
3
4
1201,gopal,manager,1212
1202,manisha,preader,1313
1203,kalil,php dev,1414
1204,allen,java,1515

增量导出

1
2
3
4
5
6
7
8
sqoop export \
--connect jdbc:mysql://192.168.88.80:3306/userdb \
--username root \
--password 123456 \
--table updateonly \
--export-dir /sqoop/updateonly/updateonly_2.txt \
--update-key id \
--update-mode updateonly

– update-key，更新标识，即根据某个字段进行更新，例如id，可以指定多个更新标识的字段，多个字段之间用逗号分隔。

– updatemod，指定updateonly（默认模式），仅仅更新已存在的数据记录，不会插入新纪录。

AllowInsert增量导出

– update-key，更新标识，即根据某个字段进行更新，例如id，可以指定多个更新标识的字段，多个字段之间用逗号分隔。

– updatemod，指定allowinsert，更新已存在的数据记录，同时插入新纪录。实质上是一个insert & update的操作。

  1. 在MySQL中创建表, 用于存储 导出的数据.
1
2
3
4
5
6
CREATE TABLE allowinsert ( 
id INT NOT NULL PRIMARY KEY, 
name VARCHAR(20), 
deg VARCHAR(20),
salary INT
);
  1. 先全量导入上述的数据, 即: /sqoop/updateonly/updateonly_1.txt
1
2
3
4
5
6
sqoop export \
--connect jdbc:mysql://192.168.88.80:3306/userdb \
--username root \
--password 123456 \
--table allowinsert \
--export-dir /sqoop/updateonly/updateonly_1.txt
  1. 增量导出 HDFS的 /sqoop/updateonly/updateonly_2.txt文件数据到 MySQL的 updateonly表中.
1
2
3
4
5
6
7
8
sqoop export \
--connect jdbc:mysql://192.168.88.80:3306/userdb \
--username root \
--password 123456 \
--table allowinsert \
--export-dir /sqoop/updateonly/updateonly_2.txt \
--update-key id \
--update-mode allowinsert