利用sqoop实现mysql、hdfs、hive、hbase数据互导

从关系型数据库(mysql,oracle)中通过sqoop迁移到Hadoop(HDFS,Hive,Hbase)时，使用import
从Hadoop(HDFS,Hive,Hbase)中通过sqoop迁移到关系型数据库(mysql,oracle)时，使用export

1.利用sqoop实现mysql数据和hdfs数据互导

1.1mysql——>hdfs：

准备一张表：score

create table score(
    sid int primary key auto_increment,
    student_id int not null,
    course_id int not null,
    score int not null
);

 往表中插入一些数据(初始数据共有60条)。

insert into score(student_id,course_id,score) values
(1,1,60),
(1,2,59),
...;

1.1.1将mysql上的score表导入到hdfs上 

在命令行输入以下命令：

sqoop import \
--connect jdbc:mysql://single:3306/kb10 \
--username root \
--password root \
--table score \
--target-dir /kb10/mysql_hdfs \
--num-mappers 1 \
--fields-terminated-by '\t'

每行是啥意思，解释一下：

hdfs上会显示以下结果：

也可以使用命令查看一下此文件中的内容：

hdfs dfs -cat /kb10/mysql_hdfs/part-m-00000 

1.1.2写整条query查询语句，并将查询结果导入到hdfs上

查询所有的student_id,course_id,score

在命令行输入以下命令：

sqoop import \
--connect jdbc:mysql://single:3306/kb10 \
--username root \
--password root \
--target-dir /kb10/mysql_hdfs \
--delete-target-dir \
--num-mappers 1    \
--fields-terminated-by '\t' \
--query 'select student_id,course_id,score from score where score>85 and $CONDITIONS;'

 hdfs上会显示以下结果：

也可以使用命令查看一下此文件中的内容：

hdfs dfs -cat /kb10/mysql_hdfs/part-m-00000 

1.2.3将mysql中筛选查询后的结果导入到hdfs上

筛选出学生student_id=1的所有数据

sqoop import \
--connect jdbc:mysql://single:3306/kb10 \
--username root \
--password root \
--table score \
--target-dir /kb10/mysql_hdfs \
--delete-target-dir \
--num-mappers 1    \
--fields-terminated-by '\t' \
--where "student_id=1"

 hdfs上会显示以下结果： 

也可以使用命令查看一下此文件中的内容：

hdfs dfs -cat /kb10/mysql_hdfs/part-m-00000 

1.2.4将mysql指定的列导入到HDFS上

sqoop import \
--connect jdbc:mysql://single:3306/kb10 \
--username root \
--password root \
--table score \
--target-dir /kb10/mysql_hdfs \
--delete-target-dir \
--num-mappers 1    \
--fields-terminated-by '\t' \
--columns student_id,course_id,score

  hdfs上会显示以下结果： 

也可以使用命令查看一下此文件中的内容：

hdfs dfs -cat /kb10/mysql_hdfs/part-m-00000 

1.2.5将查询语句导入到hdfs上，并设置mapper数为2，并且是追加的方式

这里需要注意的是incremental append和delete-target-dir不能同时使用，并且incremental append只支持新增不支持更新，也就是说导入的表中只有新增的数据。

sqoop import \
--connect jdbc:mysql://single:3306/kb10 \
--username root \
--password root \
--query "select sid,student_id, course_id,score from score where \$CONDITIONS" \
--target-dir /kb10/mysql_hdfs \
--split-by sid \
-m 2 \
--check-column sid \
--incremental append \
--last-value 0

这里的last-value就会变成60

  hdfs上会显示以下结果：  

 

也可以使用命令查看一下此文件中的内容：

hdfs dfs -cat /kb10/mysql_hdfs/part-m-00001

hdfs dfs -cat /kb10/mysql_hdfs/part-m-00002

 

下面往score表中插入5条数据。

insert into score(student_id,course_id,score) values(1,10,83),(2,10,83),(3,10,83),(4,1,83),(5,10,83);

sqoop import \
--connect jdbc:mysql://single:3306/kb10 \
--username root \
--password root \
--query "select sid,student_id, course_id,score from score where \$CONDITIONS" \
--target-dir /kb10/mysql_hdfs \
-m 1 \
--check-column sid \
--incremental append \
--last-value 60

 last-value变成了65。说明这5条数据已追加进去了。

  hdfs上会显示以下结果： 

也可以使用命令查看一下此文件中的内容：

hdfs dfs -cat /kb10/mysql_hdfs/part-m-00003

1.2.6将查询语句导入到hdfs上，并设置mapper数为1，并且是以最后改动模式(incremental lastmodified)

需要注意的是，此模式必须与time一起使用。

所以我们需要换一张表，因为score中没有时间字段。创建一张名为lmtest表。

create table lmtest(
    id int auto_increment primary key,
    name varchar(20),
    time timestamp
);

向表中插入几条数据。

insert into lmtest(name) value('cat'),('dog'),('monkey'),('rabbit'),('pig');

在命令行中输入以下命令：

sqoop import \
--connect jdbc:mysql://single:3306/kb10 \
--username root \
--password root \
--query "select id,name,time from lmtest where \$CONDITIONS" \
--target-dir /kb10/lm1121 \
--split-by id \
-m 1 \
--check-column time \
--incremental lastmodified \
--merge-key id \
--last-value "2020-11-21 16:02:35"

 

使用以下命令可以查看此文件中的内容：

hdfs dfs -cat /kb10/lm1121/part-m-00000

再向此表中插入几条数据。

insert into lmtest(name) value('apple'),('banana'),('pear');

在命令行中输入以下命令：

sqoop import \
--connect jdbc:mysql://single:3306/kb10 \
--username root \
--password root \
--query "select id,name,time from lmtest where \$CONDITIONS" \
--target-dir /kb10/lm1121 \
--split-by id \
-m 1 \
--check-column time \
--incremental lastmodified \
--merge-key id \
--last-value "2020-11-21 16:18:25.0"

可以使用以下命令查看文件中的内容：

hdfs dfs -cat /kb10/lm1121/part-r-00000

1.2hdfs——>mysql：

依然使用1.1中的score表。

1.先在mysql上创建一张新表：hdfs_mysql

create table hdfs_mysql (
        student_id int not null,
	course_id int not null,
	score int not null
	) ;

2.然后用sqoop将hdfs上的数据(part-m-00000)导出到mysql上:

sqoop export \
--connect jdbc:mysql://single:3306/kb10 \
--username root \
--password root \
--table hdfs_mysql \
--num-mappers 1 \
--export-dir /kb10/mysql_hdfs/part-m-00000 \
--input-fields-terminated-by '\t'

以下表示数据已成功导出。

3.可以在mysql中使用以下命令来验证：

select * from hdfs_mysql;

2.利用sqoop实现mysql数据和hbase数据互导

2.1mysql——>hbase

1.先在hbase上创建一张表mysql_hbase:

1.1先创建表空间(表空间已创建好，名为kb10)。创建表空间命令如下：

create_namespace 'kb10'

1.2再创建表mysql_hbase：

create 'kb10:mysql_hbase' ,'info','score'

可以查看一下刚刚创建的表,扫描表数据：

scan 'kb10:mysql_hbase' 

输入以下命令完成mysql中的数据导入到hbase:

sqoop import \
--connect jdbc:mysql://single:3306/kb10 \
--username root \
--password root \
--table score  \
--hbase-table kb10:mysql_hbase \
--column-family score  \
--hbase-create-table \
--hbase-row-key sid \
--hbase-bulkload

回到hbase中，通过以下命令验证是否已成功导入：

scan 'kb10:mysql_hbase' 

并且在hdfs上以下路径可以查看到刚刚这个数据文件已转移到hdfs中。这是因为--hbase-bulkload起了作用。因为我们刚刚在sqoop时，根本就没有指定输出路径。

2.2hbase——>mysql

这里需要注意的是，hbase导出到mysql无法直接进行，需要通过hive的中间作用来完成。

这里需要介绍一个小东西——如何将hive和hbase相连（互相拷jar包）
1.将hive110/lib下的hive-hbase-handler-1.1.0-cdh5.14.2.jar 拷贝到hbase120/lib下
先切换路径至/opt/software/hadoop/hive110/lib
cp hive-hbase-handler-1.1.0-cdh5.14.2.jar /opt/software/hadoop/hbase120/lib/
2.将hbase120/lib下的5个jar包拷贝到hive110/lib下
先切换路径至/opt/software/hadoop/hbase120/lib
cp hbase-client-1.2.0-cdh5.14.2.jar /opt/software/hadoop/hive110/lib/
cp hbase-hadoop2-compat-1.2.0-cdh5.14.2.jar /opt/software/hadoop/hive110/lib/
cp hbase-hadoop-compat-1.2.0-cdh5.14.2.jar /opt/software/hadoop/hive110/lib/
cp hbase-it-1.2.0-cdh5.14.2.jar /opt/software/hadoop/hive110/lib/
cp hbase-server-1.2.0-cdh5.14.2.jar /opt/software/hadoop/hive110/lib/

1.在hive中创建一张外部表hbase_hive，并将hbase中的mysql_hbase数据映射到hive中的此表里：

create external table hbase_hive(sid int,student_id int,course_id int,score int)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties ("hbase.columns.mapping" = ":key,score:student_id,score:course_id,score:score")
tblproperties ("hbase.table.name" = "kb10:mysql_hbase");

可以进hive中验证一下数据是否已成功映射：

2.在hive中创建内部表hbase_hive_in

create table hbase_hive_in(sid int,student_id int,course_id int,score int);

3.将外部表hbase_hive导入到内部表hbase_hive_in中：

insert overwrite table hbase_hive_in select * from hbase_hive;

4.在mysql中创建一张新的空表hive_mysql:

create table hive_mysql(
    sid int primary key,
    student_id int not null,
    course_id int not null,
    score int not null
);

5.通过sqoop导出到mysql中：

sqoop export \
--connect jdbc:mysql://single:3306/kb10 \
--username root \
--password root \
-m 1 \
--table hive_mysql \
--export-dir /opt/software/hadoop/hive110/warehouse/hbase_hive_in/000000_0 \
--input-null-string "\\\\N" \
--input-null-non-string "\\\\N" \
--input-fields-terminated-by "\\01" \
--input-lines-terminated-by "\\n"

验证一下是否已成功导出到mysql中：

3.利用sqoop实现mysql和hive之间数据互导

使用score表

3.1将mysql中的前30条数据（score表中一共有65条数据）导入到hive中：

执行以下命令：

sqoop import \
--connect jdbc:mysql://single:3306/kb10 \
--username root \
--password root \
--query 'select student_id,course_id,score from score where $CONDITIONS LIMIT 30' \
--target-dir /kb10/mysql_hive \
--delete-target-dir \
-m 1 \
--fields-terminated-by '\t'

可以使用以下命令来验证是否导入成功：

hdfs dfs -cat /kb10/mysql_hive/part-m-00000

3.2.将hive的数据导出到mysql中

1.创建一张hive表student（如果hive中有有效表就不用了创建啦）。

create external table student(
sid int,
sname string,
birth date,
gender string
)
row format delimited 
fields terminated by '\t'
;

2.往student.txt中插入一些数据。

3.把student.txt上传至hdfs上。

hdfs dfs -put student.txt /kb10

4.将hdfs上的student.txt数据放到student表中：

load data inpath '/kb10/student.txt' into table student;

5.通过以下语句查询数据是否已插入student表：

select * from student;

6.将hive中的数据放到hdfs下：

insert overwrite directory '/kb10/ss' select * from student;

7.这里需要注意的是，hdfs中的数据没有分隔符：

我们需要将此文件下载下来，给他添加一个分隔符，命令如下：

hdfs dfs -get /kb10/ss/000000_0

然后更改此文件，加一个逗号分隔符。

然后将此文件覆盖到hdfs上：

hdfs dfs -put -f 000000_0 /kb10/ss/000000_0

8.在mysql中创建一张空表hive_mysql2:

create table hive_mysql2
(
sid int primary key,
sname varchar(20),
birth date,
gender enum('女','男')
);

9.使用sqoop实现从hive导出到mysql:

sqoop export \
--connect jdbc:mysql://single:3306/kb10 \
--username root \
--password root \
--table hive_mysql2 \
--num-mappers 1 \
--export-dir /kb10/ss/000000_0 \
--input-fields-terminated-by ','