sqoop导入mysql数据到Hive的各种方案

mysql数据导入数据仓库Hive的各种方案

采用sqoop向hive中导入原始数据形成ODS层，之后可以在原始数据的基础上进行增量备份数据（定时同步）或者通过canal解析binlog（实时同步）日志进行同步数据。

1.sqoop向hive中导数据的原理

sqoop在向hive中导入数据时，是先将数据上传到hdfs中，然后创建表，最后再将hdfs中的数据load到表目录下。

我们采用sqoop直接导入hive并自动创建hive表，产生的是hive内部表。

可以通过查看表的位置信息识别（desc formatted table_name;），

内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse）,由hive自身管理
外部表的存储位置可以自定义，由HDFS管理，
删除内部表会直接删除元数据（metadata）及存储数据；
删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除。

2.sqoop导入hive的具体种类

sqoop导入数据到hive其实细分很多种情况：

全量备份（初次导入原始数据）

1.先将数据导入hdfs中，然后在hive中自己建内部表，将数据load进hive中（load完之后hdfs中的数据就会消失）
2.先将数据导入hdfs，然后自己建hive外部表，设置location hdfs的路径，做数据关联（源数据路径不变）
3.将数据直接导入已创建好的hive表中（需要你手动建表）
4.将数据导入hive中，hive表不存在，导入的时候自动创建hive表
5.表如果过多，可以考虑使用import-all-tables工具一次性全库导入，导入的同时自动生成表，但是必须满足以下条件：
    5.1.每个表必须具有主键或使用--autoreset-to-one-mapper选项。
    5.2.会导入每张表的所有列。
    5.3.使用默认拆分列，不能使用WHERE条件

增量备份（后续在原始数据的基础上做数据同步）

1.Append方式，以唯一id为依据，在指定值的基础上导入新数据
2.lastModify方式，以最后修改时间为依据，同步指定时间节点之后修改的数据

3.sqoop导入的一些参数说明

　  --hive-import          插入数据到hive当中，使用hive的默认分隔符
　　--hive-overwrite  重写插入
　　--create-hive-table  建表，如果表已经存在，该操作会报错！
　　--hive-table [table]  设置到hive当中的表名
　　--hive-drop-import-delims  导入到hive时删除 \n, \r, and \01 
　　--hive-delims-replacement  导入到hive时用自定义的字符替换掉 \n, \r, and \01 
　　--hive-partition-key     hive分区的key
　　--hive-partition-value   hive分区的值
　　--map-column-hive       类型匹配，sql类型对应到hive类型
	--direct 是为了利用某些数据库本身提供的快速导入导出数据的工具，比如mysql的mysqldump
　　性能比jdbc更好，但是不知大对象的列，使用的时候，那些快速导入的工具的客户端必须的shell脚本的目录下
	--columns <列名>   	指定列  
	-z, –compress	打开压缩功能
	–compression-codec < c >	使用Hadoop的压缩，默认为gzip压缩
	–fetch-size < n >	从数据库一次性读入的记录数
	–as-avrodatafile	导入数据格式为avro
	–as-sequencefile	导入数据格式为sqeuqncefile
	–as-textfile	导入数据格式为textfile
	--as-parquetfile 	导入数据格式为parquet
	--query 'select * from test_table where id>10 and $CONDITIONS' \	（$CONDITIONS必须要加上就相当于一个配置参数，sql语句用单引号，用了SQL查询就不能加参数--table ）
	--target-dir /sqoop/emp/test/ \ 		（指定导入的目录，若不指定就会导入默认的HDFS存储路径：/user/root/XXX.）
	--delete-target-dir  	(如果指定目录存在就删除它，一般都是用在全量导入，增量导入的时候加该参数会报错）
	--fields-terminated-by '\n' \		（指定字段分割符为','）
	--null-string '\\N' \		（string类型空值的替换符（Hive中Null用\n表示））
	--null-non-string '\\N' \		（非string类型空值的替换符）
	--split-by id \	（根据id字段来切分工作单元实现哈希分片，从而将不同分片的数据分发到不同 map 任务上去跑，避免数据倾斜。）
	-m 3 	（使用3个mapper任务，即进程，并发导入）
	 一般RDBMS的导出速度控制在60~80MB/s，每个 map 任务的处理速度5~10MB/s 估算，即 -m 参数一般设置4~8，表示启动 4~8 个map 任务并发抽取。

4.实际导入代码，已亲测，可直接复制执行（记得换自己的数据库连接）

# 全量备份（将数据导入到HDFS指定目录）
sqoop import --connect jdbc:mysql://11.25.57.18:3306/usercenter_test \
--username root --password 123456 \
--query 'select * from useradmin where id>10 and $CONDITIONS' \
--target-dir /user/root/test/ \
--delete-target-dir \
--fields-terminated-by '\t' \
--hive-drop-import-delims \
--null-string '\\N' \
--null-non-string '\\N' \
--split-by id \
-m 1


全量备份(导入已有的hive表)
sqoop import --connect jdbc:mysql://11.25.57.18:3306/usercenter_test \
--username root --password 123456 --table useradmin \
--hive-import \
--hive-database test \
--hive-table useradmin \
--fields-terminated-by '\t' \
--hive-overwrite \
--null-string '\\N' \
--null-non-string '\\N' \
--split-by id \
-m 1 


全量备份(hive表不存在，导入时自动创建hive表)
sqoop import --connect jdbc:mysql://11.25.57.18:3306/usercenter_test \
--username root --password 123456 --table users \
--hive-import \
--hive-database test1 \
--create-hive-table \
--fields-terminated-by '\t' \
--null-string '\\N' \
--null-non-string '\\N' \
--split-by id \
-m 1 


全库导入
sqoop import-all-tables "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://11.25.57.18:3306/usercenter_test \
--username root \
--password 123456 \
--hive-import \
--hive-overwrite \
--hive-database original_usercenter_test \
--fields-terminated-by '\t' \
--exclude-tables  'drop_table' \
--as-parquetfile 

#--exclude-tables  'drop_table' \	(此参数可以 exclude掉不需要import的表(多个表逗号分隔))
#--create-hive-table \  (不能和as-parquetfile共用)
#--direct \		(只支持文本格式，不能和as-parquetfile共用)
#--as-textfile \


增量备份lastmodified模式不支持直接导入Hive表（但是可以使用导入HDFS的方法，只不过--target-dir设置成Hive table在HDFS中的关联位置即可）
sqoop import --connect jdbc:mysql://11.25.57.18:3306/usercenter_test \
--username root --password 123456 --table useradmin \
--target-dir /user/hive/warehouse/test.db/useradmin \
--null-string '\\N' \
--null-non-string '\\N' \
--split-by id \
-m 1 \
--fields-terminated-by '\t' \
--hive-drop-import-delims \
--incremental lastmodified \
--check-column CreateTime \
--last-value '2019-06-04 14:29:01' \
--append



增量备份append模式，可以增量的导入hive表
sqoop import --connect jdbc:mysql://11.25.57.18:3306/usercenter_test \
--username root --password 123456 --table useradmin \
--hive-import \
--hive-database test \
--hive-table useradmin \
--hive-drop-import-delims \
--fields-terminated-by '\t' \
--null-string '\\N' \
--null-non-string '\\N' \
--incremental append \
--check-column id \
--last-value 18 \
--split-by id \
-m 1