跨库数据迁移利器 —— Sqoop
一、sqoop 基本命令
1. 查看所有命令
# sqoop help
2. 查看某条命令的具体使用方法
# sqoop help 命令名
二、sqoop 与 mysql
1. 查询mysql所有数据库
通常用于 sqoop 与 mysql 连通测试:
sqoop list-databases \ --connect jdbc:mysql://hadoop001:3306/ \ --username root \ --password root
2. 查询指定数据库中所有数据表
sqoop list-tables \ --connect jdbc:mysql://hadoop001:3306/mysql \ --username root \ --password root
三、sqoop 与 hdfs
3.1 mysql数据导入到hdfs
1. 导入命令
示例:导出 mysql 数据库中的 help_keyword
表到 hdfs 的 /sqoop
目录下,如果导入目录存在则先删除再导入,使用 3 个 map tasks
并行导入。
注:help_keyword 是 mysql 内置的一张字典表,之后的示例均使用这张表。
sqoop import \ --connect jdbc:mysql://hadoop001:3306/mysql \ --username root \ --password root \ --table help_keyword \ # 待导入的表 --delete-target-dir \ # 目标目录存在则先删除 --target-dir /sqoop \ # 导入的目标目录 --fields-terminated-by '\t' \ # 指定导出数据的分隔符 -m 3 # 指定并行执行的 map tasks 数量
日志输出如下,可以看到输入数据被平均 split
为三份,分别由三个 map task
进行处理。数据默认以表的主键列作为拆分依据,如果你的表没有主键,有以下两种方案:
- 添加
-- autoreset-to-one-mapper
参数,代表只启动一个map task
,即不并行执行; - 若仍希望并行执行,则可以使用
--split-by <column-name>
指明拆分数据的参考列。
2. 导入验证
# 查看导入后的目录 hadoop fs -ls -r /sqoop # 查看导入内容 hadoop fs -text /sqoop/part-m-00000
查看 hdfs 导入目录,可以看到表中数据被分为 3 部分进行存储,这是由指定的并行度决定的。
3.2 hdfs数据导出到mysql
sqoop export \ --connect jdbc:mysql://hadoop001:3306/mysql \ --username root \ --password root \ --table help_keyword_from_hdfs \ # 导出数据存储在 mysql 的 help_keyword_from_hdf 的表中 --export-dir /sqoop \ --input-fields-terminated-by '\t'\ --m 3
表必须预先创建,建表语句如下:
create table help_keyword_from_hdfs like help_keyword ;
四、sqoop 与 hive
4.1 mysql数据导入到hive
sqoop 导入数据到 hive 是通过先将数据导入到 hdfs 上的临时目录,然后再将数据从 hdfs 上 load
到 hive 中,最后将临时目录删除。可以使用 target-dir
来指定临时目录。
1. 导入命令
sqoop import \ --connect jdbc:mysql://hadoop001:3306/mysql \ --username root \ --password root \ --table help_keyword \ # 待导入的表 --delete-target-dir \ # 如果临时目录存在删除 --target-dir /sqoop_hive \ # 临时目录位置 --hive-database sqoop_test \ # 导入到 hive 的 sqoop_test 数据库,数据库需要预先创建。不指定则默认为 default 库 --hive-import \ # 导入到 hive --hive-overwrite \ # 如果 hive 表中有数据则覆盖,这会清除表中原有的数据,然后再写入 -m 3 # 并行度
导入到 hive 中的 sqoop_test
数据库需要预先创建,不指定则默认使用 hive 中的 default
库。
# 查看 hive 中的所有数据库 hive> show databases; # 创建 sqoop_test 数据库 hive> create database sqoop_test;
2. 导入验证
# 查看 sqoop_test 数据库的所有表 hive> show tables in sqoop_test; # 查看表中数据 hive> select * from sqoop_test.help_keyword;
3. 可能出现的问题
如果执行报错 java.io.ioexception: java.lang.classnotfoundexception: org.apache.hadoop.hive.conf.hiveconf
,则需将 hive 安装目录下 lib
下的 hive-exec-**.jar
放到 sqoop 的 lib
。
[root@hadoop001 lib]# ll hive-exec-* -rw-r--r--. 1 1106 4001 19632031 11 月 13 21:45 hive-exec-1.1.0-cdh5.15.2.jar [root@hadoop001 lib]# cp hive-exec-1.1.0-cdh5.15.2.jar ${sqoop_home}/lib
4.2 hive 导出数据到mysql
由于 hive 的数据是存储在 hdfs 上的,所以 hive 导入数据到 mysql,实际上就是 hdfs 导入数据到 mysql。
1. 查看hive表在hdfs的存储位置
# 进入对应的数据库 hive> use sqoop_test; # 查看表信息 hive> desc formatted help_keyword;
location
属性为其存储位置:
这里可以查看一下这个目录,文件结构如下:
3.2 执行导出命令
sqoop export \ --connect jdbc:mysql://hadoop001:3306/mysql \ --username root \ --password root \ --table help_keyword_from_hive \ --export-dir /user/hive/warehouse/sqoop_test.db/help_keyword \ -input-fields-terminated-by '\001' \ # 需要注意的是 hive 中默认的分隔符为 \001 --m 3
mysql 中的表需要预先创建:
create table help_keyword_from_hive like help_keyword ;
五、sqoop 与 hbase
本小节只讲解从 rdbms 导入数据到 hbase,因为暂时没有命令能够从 hbase 直接导出数据到 rdbms。
5.1 mysql导入数据到hbase
1. 导入数据
将 help_keyword
表中数据导入到 hbase 上的 help_keyword_hbase
表中,使用原表的主键 help_keyword_id
作为 rowkey
,原表的所有列都会在 keywordinfo
列族下,目前只支持全部导入到一个列族下,不支持分别指定列族。
sqoop import \ --connect jdbc:mysql://hadoop001:3306/mysql \ --username root \ --password root \ --table help_keyword \ # 待导入的表 --hbase-table help_keyword_hbase \ # hbase 表名称,表需要预先创建 --column-family keywordinfo \ # 所有列导入到 keywordinfo 列族下 --hbase-row-key help_keyword_id # 使用原表的 help_keyword_id 作为 rowkey
导入的 hbase 表需要预先创建:
# 查看所有表 hbase> list # 创建表 hbase> create 'help_keyword_hbase', 'keywordinfo' # 查看表信息 hbase> desc 'help_keyword_hbase'
2. 导入验证
使用 scan
查看表数据:
六、全库导出
sqoop 支持通过 import-all-tables
命令进行全库导出到 hdfs/hive,但需要注意有以下两个限制:
- 所有表必须有主键;或者使用
--autoreset-to-one-mapper
,代表只启动一个map task
; - 你不能使用非默认的分割列,也不能通过 where 子句添加任何限制。
第二点解释得比较拗口,这里列出官方原本的说明:
- you must not intend to use non-default splitting column, nor impose any conditions via a
where
clause.
全库导出到 hdfs:
sqoop import-all-tables \ --connect jdbc:mysql://hadoop001:3306/数据库名 \ --username root \ --password root \ --warehouse-dir /sqoop_all \ # 每个表会单独导出到一个目录,需要用此参数指明所有目录的父目录 --fields-terminated-by '\t' \ -m 3
全库导出到 hive:
sqoop import-all-tables -dorg.apache.sqoop.splitter.allow_text_splitter=true \ --connect jdbc:mysql://hadoop001:3306/数据库名 \ --username root \ --password root \ --hive-database sqoop_test \ # 导出到 hive 对应的库 --hive-import \ --hive-overwrite \ -m 3
七、sqoop 数据过滤
7.1 query参数
sqoop 支持使用 query
参数定义查询 sql,从而可以导出任何想要的结果集。使用示例如下:
sqoop import \ --connect jdbc:mysql://hadoop001:3306/mysql \ --username root \ --password root \ --query 'select * from help_keyword where $conditions and help_keyword_id < 50' \ --delete-target-dir \ --target-dir /sqoop_hive \ --hive-database sqoop_test \ # 指定导入目标数据库 不指定则默认使用 hive 中的 default 库 --hive-table filter_help_keyword \ # 指定导入目标表 --split-by help_keyword_id \ # 指定用于 split 的列 --hive-import \ # 导入到 hive --hive-overwrite \ 、 -m 3
在使用 query
进行数据过滤时,需要注意以下三点:
- 必须用
--hive-table
指明目标表; - 如果并行度
-m
不为 1 或者没有指定--autoreset-to-one-mapper
,则需要用--split-by
指明参考列; - sql 的
where
字句必须包含$conditions
,这是固定写法,作用是动态替换。
7.2 增量导入
sqoop import \ --connect jdbc:mysql://hadoop001:3306/mysql \ --username root \ --password root \ --table help_keyword \ --target-dir /sqoop_hive \ --hive-database sqoop_test \ --incremental append \ # 指明模式 --check-column help_keyword_id \ # 指明用于增量导入的参考列 --last-value 300 \ # 指定参考列上次导入的最大值 --hive-import \ -m 3
incremental
参数有以下两个可选的选项:
-
append:要求参考列的值必须是递增的,所有大于
last-value
的值都会被导入; -
lastmodified:要求参考列的值必须是
timestamp
类型,且插入数据时候要在参考列插入当前时间戳,更新数据时也要更新参考列的时间戳,所有时间晚于last-value
的数据都会被导入。
通过上面的解释我们可以看出来,其实 sqoop 的增量导入并没有太多神器的地方,就是依靠维护的参考列来判断哪些是增量数据。当然我们也可以使用上面介绍的 query
参数来进行手动的增量导出,这样反而更加灵活。
八、类型支持
sqoop 默认支持数据库的大多数字段类型,但是某些特殊类型是不支持的。遇到不支持的类型,程序会抛出异常 hive does not support the sql type for column xxx
异常,此时可以通过下面两个参数进行强制类型转换:
- --map-column-java<mapping> :重写 sql 到 java 类型的映射;
- --map-column-hive <mapping> : 重写 hive 到 java 类型的映射。
示例如下,将原先 id
字段强制转为 string 类型,value
字段强制转为 integer 类型:
$ sqoop import ... --map-column-java id=string,value=integer
参考资料
更多大数据系列文章可以参见 github 开源项目: 大数据入门指南
上一篇: Linux中两台主机配置互信关系
下一篇: Java考题知识点