一小时教你熟练运用sqoop完成数据在不同数据库之间的迁移

sqoop实现数据的互导

全量导出(首次部分列所有行)
增量导出 allowinsert只导出新增的
更新(不能导出新增数据,只能导入原有更新后的数据)updateonl

import与export

sqoop export：将数据从hadoop（先将数据从hive，hbase导入hdfs） 导入到关系型数据库（mysql,Oracle）
sqoop import:将数据从关系型数据库（mysql,Oracle）导入到hadoop

文件格式

orcfile/rcfile/parquetfile/textfile
普通查询：orcfile速度非常快
聚合查询：差不多，但orcfile还是稍快一些
另外两个差不多

运用实例

1.hive=>mysql，hive数据导出到mysql里

从hive表到RDBMS表的直接导出
不建议生产环境使用，因为当hive表记录较大时，或者RDBMS有多个分区表时，无法做精细的控制
建议使用hive=>hdfs=>mysql
从hive表导出到HDFS时，可以进一步堆数据进行字段筛选、字段加工、数据过滤操作
使得hdfs上的数据更“接近”或等于将来实际要导入RDBMS表的数据
从HDFS导入RDBMS时，也是将一个“小数据集”与目标表中的数据作对比，会提高速度
(1)hive=>hadoop（在hive里操作）
hive：shop
hadoop:/kb10/shop1118 自动生成数据文件/kb10/shop1118/000000_0

insert overwrite directory '/kb10/shop1118'
row format delimited
fields terminated by ','
stored as textfile
select shopid,shopname,
contact.mobile mobile,
concat_ws('',address) address,
volumn['2020'] vol2020
from shop;

(2)hadoop=>mysql
#先在mysql新建一个表

create table hive_shop(
		id int,
		name varchar(50),
		mobile varchar(20),``
		address varchar(100),
		vol2020 int
		);

（3）导出sqoop到mysql；回车默认是提交符，需要增加\转义符
linux下执行操作

sqoop export \
--connect jdbc:mysql://master:3306/kb10 \
--username root \
--password root \
--table hive_shop \
--columns id,name,mobile,address,vol2020 \
--fields-terminated-by ',' \
--export-dir '/kb10/shop1118/000000_0'

2.mysql=>hbase

(1)先在hbase上建表及列簇
create ‘kb10:test’,‘score’,‘info’

(2)导入数据: mysql的表score
=>
hbase的表kb10:test 列簇：score

sqoop import \
--connect jdbc:mysql://master:3306/kb06mysqltestdb \
--username root \
--password root \
--table score \
--hbase-table kb10:test \
--column-family score \
--split-by sid \
-m 1 \
--hbase-create-table \
--hbase-row-key sid \
--hbase-bulkload
		
sqoop import \
--connect jdbc:mysql://master:3306/kb06mysqltestdb \ --使用mysql的数据库
--username root \									 
--password root \
--table score \										 --使用数据库下的表
--hbase-table kb10:test \							 --传入到hbase的那张表
--column-family score \								 --传到表中的哪一个列簇
--split-by sid \
-m 1 \												 --分区
--hbase-create-table \								 --表不存在就创建表，一般先在hbase上建好
--hbase-row-key sid \								 --将row与mysql字段名相连
--hbase-bulkload									 用sqoop完成路径的转移与合并

3.hbase=>hive

把hbase的表"kb10:test"的score列簇数据导入到hive的表hbase_student中
hive下操作

!!!只能创建外部表

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

4.mysql=>hadoop

#–incremental append 只支持新增不支持更新
#–table TABLE_NAME 或–query “select …”
#–split-by 和-m 结合使用实现numberReduceTasks并行
#–check-column 和 --last-value 结合实现–check-column:where sid>10

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

sqoop import \
--connect jdbc:mysql://master:3306/kb06mysqltestdb \      #连接mysql数据库
--username root \
--password root \
--query "select sid,student_id,course_id,   				
score from score where \$CONDITIONS" 			 #用select语句提取表中
									#部分字段的数据，where后面的字段表示引用，意思是从sid>10处开始新增
--target-dir /kb10/mysqlscore1122 \					#目标hadoop文件夹
--split-by sid \								#以sid的hash值对2取余，创建两个分区（就是把数据分成两个文件）
-m 2 \
--check-column sid \
--incremental append \
--last-value 0									#value设为0意思是第一次全表导入

#结束后在/kb10/mysqlscore1122下会生成2个文件，2就是-m设置过的数量
last value=60

#结束后后有一个last value出来，下一次增量导入就重新设置value的值，实现增量导入						
	
insert into score(student_id,course_id,score)
values(9,1,80),(7,2,80),(6,3,80),(3,1,80),(4,1,80);
sqoop import \
--connect jdbc:mysql://master:3306/kb06mysqltestdb \
--username root \
--password root \
--query "select sid,student_id,course_id,score from score where \$CONDITIONS" \
--target-dir /kb10/mysqlscore1122 \
--split-by sid \
-m 2 \
--check-column sid \
--incremental append \
--last-value 60
#结束后又生成两个文件，只存放新增的数据