sqoop导入hdfs和hive(追加/增量/覆盖)

提前准备mysql数据

DROP TABLE IF EXISTS `sqooptest`;
CREATE TABLE `sqooptest` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `sqooptest` VALUES ('1', 'zhangsan', '18');
INSERT INTO `sqooptest` VALUES ('2', 'lisi', '19');
INSERT INTO `sqooptest` VALUES ('3', 'wangwu', '20');
INSERT INTO `sqooptest` VALUES ('4', 'zhaoliu', '21');
INSERT INTO `sqooptest` VALUES ('5', 'tianqi', '22');
INSERT INTO `sqooptest` VALUES ('6', 'luba', '23');
INSERT INTO `sqooptest` VALUES ('7', 'sunjiu', '24');
INSERT INTO `sqooptest` VALUES ('8', 'gaoshi', '25');
INSERT INTO `sqooptest` VALUES ('9', 'xiaoshiyi', '26');
INSERT INTO `sqooptest` VALUES ('10', 'wushier', '27');

1 导入hdfs不指定存储位置与分隔符

bin/sqoop import \
--connect jdbc:mysql://node03:3306/test \
--username root \
--password 123456 \
--table sqooptest \
--m 1

这种不指定文件存储位置的导入会导入到/user/root/表名下,默认是以逗号作为分隔,但是如果文件夹存在就会报错,可以添加–delete-target-dir 这样上传的时候如果文件存在就会先删除

2 指定存储位置与分隔符

bin/sqoop import \
--connect jdbc:mysql://node03:3306/test \
--username root \
--password 123456 \
--table sqooptest \
--m 1 \
--delete-target-dir \
--target-dir /user/mytable \
--fields-terminated-by '.'

指定了–delete-target-dir,所以导入之前如果有数据会删除,也就相当于覆盖

3 增量导入hdfs

hdfs上面导入如果存在文件就报错,指定了–delete-target-dir会先删除再导入,所以无法做到增量导入,增量导入如下:

bin/sqoop import \
--connect jdbc:mysql://node03:3306/test \
--username root \
--password 123456 \
--m 1 \
--table sqooptest \
--target-dir /user/increase \
--incremental append \
--check-column id \
--last-value 5

上方执行两次,第二次就是增量导入

如上图所示,会提示check-column 中的id此次导入后的最后一个值是10

注意此种方式不能与–delete-target-dir一起使用会报错,再说做增量还要有就删除,也不合理,只是不能与这个参数使用,但是第一次执行没有/user/increase也没关系会自动创建.
上方的导入是追加,可以根据指定的字段进行合并,也就是执行upsert有就更新,没有就插入

bin/sqoop import \
--connect jdbc:mysql://node03:3306/test \
--username root \
--password 123456 \
--m 1 \
--table sqooptest \
--target-dir /user/increase2 \
--incremental lastmodified \
--check-column create_date \
--last-value '2020-05-05' \
--merge-key id

因为此种导入的check-column需要一个date或者datetime类型,所以我临时添加了一列create_date

上方的’merge-key id’的意思是根据id去更新数据,所以执行了第一次sqoop

数据已经进入后我更改id=10的数据为id=11

再执行第二次

此种hdfs增量导入关键参数

--incremental append 
--check-column id 
--last-value 5

如果--incremental append那就根据id > 5取出数据再追加写入.

--incremental lastmodified 
--check-column create_date 
--last-value '2020-05-05' 
--merge-key id

如果是--incremental lastmodified那就根据create_date>=2020-05-05取出数据(注意--incremental append不包含last-value,但是--incremental lastmodified
包含last-value)再根据merge-key重复就覆盖,唯一就追加写入,此种模式下的check-column必须是date或者datetime类型
还有一种是

--incremental lastmodified 
--check-column create_date 
--last-value '2020-05-05' 
--append

我测试发现与–incremental append 没有区别,都是追加写入

4 导入到hive表追加写入

创建hive表

create table sqooptest.sqooptest(id int,name string,age int)
row format delimited fields terminated by '\001';

bin/sqoop import \
--connect jdbc:mysql://node03:3306/test \
--username root \
--password 123456 \
--table sqooptest \
--m 1 \
--fields-terminated-by '\001' \
--hive-import \
--hive-table sqooptest.sqooptest \
--target-dir /user/root/test \
--delete-target-dir 

以上方式为追加写入,如果执行两次,hive中查询数据就是两份
需要注意,如果不指定target-dir 默认还是会先导入到/user/root/目录下,
但是导入hive表的操作结束后会删除临时文件,但是我碰到过会出现不删除的情况,所以为了以防万一,还是需要指定–delete-target-dir

bin/sqoop import \
--connect jdbc:mysql://node03:3306/test \
--username root \
--password 123456 \
--table sqooptest \
--m 1 \
--fields-terminated-by '\001' \
--hive-import \
--hive-database sqooptest \
--target-dir /user/root/test \
--delete-target-dir 

上方我没有指定--hive-table 而是指定了--hive-database此时如果hive中没有sqooptest表,就会自动创建,但是如果有就会追加写入.注意如果已经存在sqooptest表并且分隔符为’,’,导入不会报错,但是查询出来全部为null

5 指定条件导入hive表

bin/sqoop import \
--connect jdbc:mysql://node03:3306/test \
--username root \
--password 123456 \
--table sqooptest \
--m 1 \
--fields-terminated-by '\001' \
--hive-import \
--hive-database sqooptest \
--target-dir /user/root/test \
--delete-target-dir \
--where "id>5"

添加了--where "id>5"条件

指定查询sql

bin/sqoop import \
--connect jdbc:mysql://node03:3306/test \
--username root \
--password 123456 \
--m 1 \
--fields-terminated-by '\001' \
--hive-import \
--hive-table sqooptest \
--hive-database sqooptest \
--target-dir /user/root/test \
--delete-target-dir \
--query 'select * from sqooptest union all select * from sqooptest where $CONDITIONS'

上方不再指定mysql table ,添加了sql查询--qurey其中sql中有一个where $CONDITIONS 这是因为通过这种方式,必须要包含where $CONDITIONS 如果有过滤条件可以where id=1 and $CONDITIONS不这样写,导入的时候回报错

6 hive的追加写入/增量写入/覆盖

hive的追加写入就是5的方式,多次导入就是追加
增量写入(upsert)是通过3的方式--incremental lastmodified只是指定的target-dir为hive表所对应的目录
hive的覆盖:
先删除hive的sqooptest表再创建,执行

bin/sqoop import \
--connect jdbc:mysql://node03:3306/test \
--username root \
--password 123456 \
--table sqooptest \
--m 1 \
--fields-terminated-by '\001' \
--hive-import \
--hive-table sqooptest.sqooptest \
--target-dir /user/root/test \
--delete-target-dir \
--hive-overwrite

只是基于4的命令添加了一个 --hive-overwrite
执行结束

执行第二次结果与上图一样,证明是覆盖导入.

其他常用参数

–null-string ‘xxx’ 指定mysql中字符串类型为null替换成xxx值
–null-non-string ‘xxx’ 指定mysql中非字符串类型为null替换成xxx值