从关系型数据库(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 ','