sqoop中数据从Mysql到Hive(Hbase)

一、定义

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

也可以将HDFS的数据导进到关系型数据库中。

sqoop将导入或导出命令翻译成mapreduce程序来实现。

二、使用

RDBMS到HDFS（默认是覆盖）

创建mysql表

create table user(
userid int primary key not null,
name varchar(20),
age int
);

insert into user values 
(1,'zs',15),
(2,'ls',18),
(3,'ww',16);

create table scores(
scid int primary key not null,
userid int not null,
project varchar(20),score int not null
);

insert into scores values
(1,1,'chinese',80),
(2,1,'math',90),
(3,2,'chinese',88),
(4,2,'math',78),
(5,3,'chinese',69),
(6,3,'math',98);

sqoop导入默认是覆盖

全部导入

把mysql的某张表全部导入hdfs

sqoop import \
--connect jdbc:mysql://192.168.98.135:3306/mmm \
--driver com.mysql.jdbc.Driver \（可以不写，已经导入到sqoop的lib目录下）
--table user \
--username root \
--password root \
--target-dir /mytmp/datas \
-m 1

查询导入

query使用

select语句后加where 1=1 and $CONDITIONS
–split-by 后面用数据类型是字符串的列 可能出现数据倾斜，加一列盐,比如concat(a.userid,’-’,u.name) as hs
可以看到复杂的sql语句也可以查询导入

sqoop import \
--connect jdbc:mysql://192.168.98.135:3306/mmm \
--query "
select * from (
select a.*,u.name ,concat(a.userid,'-',u.name) as hs 
from 
(select userid ,
max(case project when 'chinese' then score else 0 end ) 'chinese' ,
max(case project when 'math' then score else 0 end ) 'math' 
from scores
group by userid) a 
inner join user u 
on a.userid=u.userid ) s where 1=1 and \$CONDITIONS" \
--username root \
--password root \
--delete-target-dir \
--split-by hs \
--target-dir /mytmp/datas \
-m 3

导入后查询hdfs结果

导入指定列

–columns 要加的列名

sqoop import \
--connect jdbc:mysql://192.168.98.135:3306/mmm \
--table user \
--username root \
--password root \
--delete-target-dir \
--target-dir /mytmp/datas \
--columns userid,name \
-m 1

sqoop关键字导入

–where “条件”

sqoop import \
--connect jdbc:mysql://192.168.98.135:3306/mmm \
--table user \
--username root \
--password root \
--delete-target-dir \
--target-dir /mytmp/datas \
--where "name='ls'" \
-m 1

其他

create table orders(ordid int primary key not null,orddate date not null );
insert into orders values (1,'2020-11-10'),(2,'2020-11-10'),(3,'2020-11-10');
insert into orders values (4,'2020-11-9'),(5,'2020-11-9'),(6,'2020-11-9');

+-------+------------+
| ordid | orddate    |
+-------+------------+
|     1 | 2020-11-10 |
|     2 | 2020-11-10 |
|     3 | 2020-11-10 |
|     4 | 2020-11-09 |
|     5 | 2020-11-09 |
|     6 | 2020-11-09 |
+-------+------------+

把每天的数据放到1个新的文件夹 --incremental append
指定递增的列 --check-column orddate \
–last-value ‘2020-11-09’ 是9号最后时间也就是10号开始

sqoop import \
--connect jdbc:mysql://192.168.98.135:3306/mmm \
--driver com.mysql.jdbc.Driver \
--table orders \
--username root \
--password root \
--incremental append \
--check-column orddate \
--last-value '2020-11-09' \
--target-dir /mytmp/orders \
-m 1

查看到的就是10号的数据
hadoop dfs -cat /mytmp/orders/part-m-00000

insert into orders values (7,'2020-11-11'),(8,'2020-11-11'),(9,'2020-11-11');

last-value ‘2020-11-10’ 是9号最后时间也就是11号开始

sqoop import \
--connect jdbc:mysql://192.168.98.135:3306/mmm \
--driver com.mysql.jdbc.Driver \
--table orders \
--username root \
--password root \
--incremental append \
--check-column orddate \
--last-value '2020-11-10' \
--target-dir /mytmp/orders \
-m 1

多出来的part-m-00001就是11号的数据
hadoop dfs -cat /mytmp/orders/part-m-00001

工作中就可以把下面写进shell脚本，设置每天去收集昨天的数据

tm=`date -d"2 day ago ${date}" +%Y-%m-%d`
sqoop import \
--connect jdbc:mysql://192.168.98.135:3306/mmm \
--driver com.mysql.jdbc.Driver \
--table orders \
--username root \
--password root \
--incremental append \
--check-column orddate \
--last-value "$tm" \
--target-dir /mytmp/orders \
-m 1

RDBMS到Hive（默认是追加）

该过程分为两步，第一步将数据导入到HDFS，第二步将导入到HDFS的数据迁移到Hive仓库，第一步默认的临时目录是/user/atguigu/表名

默认是追加

–hive-import 必须写的依赖
–hive-database mydemo 导到哪个数据库，不写就是默认default库
–hive-table user 导到哪个表

sqoop import \
--connect jdbc:mysql://192.168.98.135:3306/mmm \
--table user \
--username root \
--password root \
--hive-import \
--hive-database mydemo \
--hive-table user \
-m 1

覆盖

–hive-overwrite \ 加上后就会覆盖之前的数据

sqoop import \
--connect jdbc:mysql://192.168.98.135:3306/mmm \
--table user \
--username root \
--password root \
--hive-import \
--hive-overwrite \
--hive-database mydemo \
--hive-table user \
-m 1

指定分区

在hive中建一个分区表

create table orderinfos(
orderid string,
userid string
)
partitioned by(orddate string)
row format delimited 
fields terminated by '\t';

加一个静态分区

use mydemo;alter table orderinfos add partition(orddate='20201111');

在MySQL创建一张表

create table orders1(
orderid int primary key not null,
userid int not null,
orddate date not null,
money float
);

insert into orders1 values
(1,1,'2020-11-10',100),
(2,2,'2020-11-10',500),
(3,3,'2020-11-11',150);

–target-dir /hive110/warehouse/mydemo.db/orderinfos/orddate=20201111 放到hdfs的路径
–hive-partition-key “orddate” 按照哪个字段分区
–hive-partition-value “20201111” 字段具体值

sqoop import \
--connect jdbc:mysql://192.168.98.135:3306/mmm \
--query "select orderid,userid from orders1 where orddate='2020-11-11' and \$CONDITIONS" \
--username root \
--password root \
--target-dir /hive110/warehouse/mydemo.db/orderinfos/orddate=20201111 \
--hive-import \
--hive-database mydemo \
--hive-table orderinfos \
--hive-partition-key "orddate" --hive-partition-value "20201111" \
--fields-terminated-by "\t" \
-m 1

查好看结果