Sqoop 简介
1. Sqoop是什么
Sqoop(sql to hadoop)是apache旗下的一款 ”Hadoop和关系数据库之间传输数据”的工具,它可以将Mysql、Oracle数据导入到HDFS、Hive、HBase等数据存储系统,也可以从Hadoop文件系统中到处数据到关系型数据库。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ICKKbG6E-1590070520349)(Sqoop 简介.assets/1590067412280.png)]
2. Sqoop的工作机制
它的工作机制是将sqoop的指定的指令转换为mapreduce程序实现的
3. 基本架构
sqoop在发展的过程中演进出了两种不同的架构
3.1 sqoop1
- 版本号为1.4.x为sqoop1
- 在架构上:sqoop1使用sqoop客户端直接提交的方式
- 访问方式:CLI控制台方式进行访问
- 安全性:命令或脚本中指定用户数据库名及密码
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e8ENTO6r-1590070520351)(Sqoop 简介.assets/1590068110562.png)]
3.2 sqoop2
-
版本号为1.99x为sqoop2
-
在架构上:sqoop2引入了sqoop server,对connector实现了集中的管理
-
访问方式:REST API、 JAVA API、 WEB UI以及CLI控制台方式进行访问
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6iS7hVli-1590070520352)(Sqoop 简介.assets/1590068088315.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R0jEgsDy-1590070520354)(Sqoop 简介.assets/1590068136439.png)]
4. 安装部署
这个可以百度,比较简单。
5. 基本的使用
5.1 常用命令
-
命令行查看帮助文档
sqoop list-databases --help
-
列出nodeX上mysql数据库中所有的数据库名称
sqoop list-databases --connect jdbc:mysql://nodeX:3306/ --username root --password 123456
-
查看某一个数据库下面的所有数据表
sqoop list-tables --connect jdbc:mysql://nodeX:3306/ --username root --password 123456
5.2 导入数据
5.2.1 导入数据
-
在MySQL数据库服务器中创建一个数据库userdb, 然后在创建一张表 emp,添加点测试数据到表中
-
从MySQL数据库服务器中的userdb数据库下的emp表导入HDFS上
sqoop import --connect jdbc:mysql://nodeX:3306/userdb --username root --password 123456 --table emp --m 1
-
提交之后,会运行一个MR程序,最后查看HDFS上的目录看是否有数据生成
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KS15eeHs-1590070520356)(Sqoop 简介.assets/1590068506397.png)]
5.2.2 导入数据库表数据到HDFS指定目录
-
在导入表数据到HDFS使用Sqoop导入工具,我们可以指定目标目录。
-
使用参数 ==–target-dir==来指定导出目的地,
-
使用参数==–delete-target-dir==来判断导出目录是否存在,如果存在就删掉
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-buWyVjhI-1590070520357)(Sqoop 简介.assets/1590068594092.png)]
5.2.3 导入数据库表数据到HDFS指定目录并且指定数据字段的分隔符
-
这里使用参数
- –fields-terminated-by 分隔符
sqoop import --connect jdbc:mysql://node1:3306/userdb --username root --password 123456 --delete-target-dir --table emp --target-dir /sqoop/emp1 --fields-terminated-by '#' --m 1
-
提交查看HDFS上的目录看是否有数据生成
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rDklB6IW-1590070520359)(Sqoop 简介.assets/1590068708722.png)]
5.2.4 导入关系表到Hive中
-
(1) 将我们mysql表当中的数据直接导入到hive表中的话,需要将hive的一个叫做hive-exec-1.2.2.jar包拷贝到sqoop的lib目录下
cp /opt/bigdata/hive/lib/hive-exec-1.2.2.jar /opt/bigdata/sqoop/lib/
-
(2) 准备hive数据库与表
-
在hive中创建一个数据库和表
create database sqooptohive; create external table sqooptohive.emp_hive(id int,name string,deg string,salary double ,dept string) row format delimited fields terminated by '\001';
-
-
(3) 把mysql表数据导入到hive表中
sqoop import --connect jdbc:mysql://node1:3306/userdb --username root --password 123456 --table emp --fields-terminated-by '\001' --hive-import --hive-table sqooptohive.emp_hive --hive-overwrite --m 1
-
(4) 执行完成了查看hive中表的数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UistotYB-1590070520360)(Sqoop 简介.assets/1590068881516.png)]
5.2.5 导入数据库表数据到hive中(并自动创建hive表)
sqoop import --connect jdbc:mysql://node1:3306/userdb --username root --password 123456 --hive-database sqooptohive --hive-table emp1 --table emp --hive-import -m 1 ;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OXplBb03-1590070520361)(Sqoop 简介.assets/1590068968132.png)]
5.2.6 sql语句查找导入hdfs
sqoop import \--connect jdbc:mysql://node1:3306/userdb \--username root --password 123456 --table emp \--target-dir /sqoop/emp_where -m 1 --delete-target-dir \--where "dept = 'TP'"
5.2.7 导入数据库表数据到hive中(并自动创建hive表)
sqoop import \
--connect jdbc:mysql://node1:3306/userdb \
--username root --password 123456 \
--target-dir /sqoop/emp_sql -m 1 --delete-target-dir \
--query 'select * from emp where salary >30000 and $CONDITIONS'
5.2.8 增量导入
在实际工作当中,数据的导入很多时候都是只需要导入增量数据即可,并不需要将表中的数据全部导入到hive或者hdfs当中去,肯定会出现重复的数据的状况,所以我们一般都是选用一些字段进行增量的导入,为了支持增量的导入,sqoop也给我们考虑到了这种情况并且支持增量的导入数据
-
增量导入是仅导入新添加的表中的行的技术。
-
需要添加 ==‘incremental’, ‘check-column’, 和 ‘last-value’==选项来执行增量导入。
--incremental <mode> --check-column <column name> --last value <last check column value>
-
第一种增量导入实现
-
基于递增列的增量数据导入(Append方式)
- 注意:这里不能加上 --delete-target-dir 参数,添加就报错
sqoop import \ --connect jdbc:mysql://node1:3306/userdb \ --username root \ --password 123456 \ --table emp \ --incremental append \ --check-column id \ --last-value 1202 \ -m 1 \ --target-dir /sqoop/increment1
-
-
第二种增量导入实现
-
基于时间列的增量数据导入(LastModified方式)
-
此方式要求原有表中有time字段,它能指定一个时间戳
sqoop import \ --connect jdbc:mysql://node1:3306/userdb \ --username root \ --password 123456 \ --table user \ --target-dir /sqoop/incement2 \ --incremental lastmodified \ --check-column createTime \ --last-value '2019-09-01 10:16:00' \ --m 1
-
-
5.3 导出数据
- 将数据从HDFS把文件导出到RDBMS数据库
- 导出前,目标表必须存在于目标数据库中。
- 默认操作是从将文件中的数据使用INSERT语句插入到表中
- 更新模式下,是生成UPDATE语句更新表数据
- 导出前,目标表必须存在于目标数据库中。
sqoop export \
--connect jdbc:mysql://node1:3306/userdb \
--username root --password 123456 \
--table fromhdfs \
--input-fields-terminated-by " " \
--export-dir /user/hive/warehouse/hive_source
6. 其他使用
-
Sqoop作业
-
将事先定义好的数据导入导出任务按照指定流程运行
-
语法
sqoop job (generic-args) (job-args) [-- [subtool-name] (subtool-args)]
-
创建作业
sqoop job \ --create myjob \ -- import \ --connect jdbc:mysql://node1:3306/userdb \ --username root \ --password 123456 \ --table emp \ --target-dir /sqoop/myjob \ --delete-target-dir \ --m 1
- 验证作业
sqoop job --list
-
查看作业
sqoop job --show myjob
-
执行作业
-
用于执行保存的作业
sqoop job --exec myjob
-
解决sqoop需要输入密码的问题
- vi /opt/bigdata/sqoop/conf/sqoop-site.xml
<property> <name>sqoop.metastore.client.record.password</name> <value>true</value> <description>If true, allow saved passwords in the metastore. </description> </property>
-
-
删除作业
-
用于删除保存作业
sqoop job --delete myjob
-
7.扩展
7.1 Sqoop导入导出Null存储一致性问题
-
- Hive中的Null在底层是以“\N”来存储,而MySQL中的Null在底层就是Null,为了保证数据两端的一致性。在导出数据时采用–input-null-string和–input-null-non-string两个参数。导入数据时采用–null-string和–null-non-string。
7.2 Sqoop导入时出现部分Map任务成功部分失败,解决方式:staging-table或者设置map数量为1个(不推荐)
sqoop export --connect jdbc:mysql://192.168.137.10:3306/user_behavior --username root --password 123456 --table app_cource_study_report --columns watch_video_cnt,complete_video_cnt,dt --fields-terminated-by "\t" --export-dir "/user/hive/warehouse/tmp.db/app_cource_study_analysis_${day}" --staging-table app_cource_study_report_tmp --clear-staging-table --input-null-string '\N'
7.3 Sqoop在导入数据的时候数据倾斜
sqoop 抽数的并行化主要涉及到两个参数:num-mappers:启动N个map来并行导入数据,默认4个;split-by:按照某一列来切分表的工作单元。
``