(图片来源于网络,侵删)
一、Sqoop概述
【1】简介
- Apache Sqoop是在
Hadoop生态体系
和RDBMS体系
之间传送数据
的一种工具,来自于Apache软件基金会提供 - 核心的功能有两个:
导入、迁入
导出、迁出
导入数据:MySQL,Oracle 导入数据到 Hadoop 的 HDFS、HIVE、HBASE 等数据存储系统
导出数据:从 Hadoop 的文件系统中导出数据到关系数据库 MySQL等 Sqoop 的本质还是一个命令行工具,和 HDFS,Hive 相比,并没有什么高深的理论 - Sqoop工作机制:
是将导入或导出命令翻译成MapReduce程序来实现,在翻译出的MapReduce中主要是对InputFormat和OutputFormat进行定制 - Hadoop生态系统包括:HDFS、Hive、HBase等
RDBMS体系包括:Mysql、Oracle、DB2等
Sqoop可以理解为:“SQL 到 Hadoop 和 Hadoop 到SQL
”
二、Sqoop安装
前提概述:将来Sqoop在使用的时候有可能会跟那些系统或者组件打交道?
HDFS, MapReduce, YARN, ZooKeeper, Hive, HBase, MySQL
Sqoop就是一个工具, 只需要在一个节点上进行安装即可
1.上传Sqoop包和mysql驱动包
2.解压
tar -zxf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C ../servers/
3.配置环境变量
vim /etc/profile/sqoop.sh
添加如下内容↓↓↓ (记得 source /etc/profile
)
export SQOOP_HOME=/export/servers/sqoop-1.4.6.bin__hadoop-2.0.4-alpha
export PATH=$PATH:$SQOOP_HOME/bin
4.进入sqoop的conf目录,配置sqoop-env.sh
1.cp sqoop-env-template.sh sqoop-env.sh
2.vim sqoop-env.sh
export HADOOP_COMMON_HOME=/export/servers/hadoop
export HADOOP_MAPRED_HOME=/export/servers/hadoop
export HIVE_HOME=/export/servers/hive
export ZOOKEEPER_HOME=/export/servers/zookeeper
export ZOOCFGDIR=/export/servers/zookeeper
export HBASE_HOME=/export/servers/hbase
5.加入mysql的jdbc驱动包
`cp /hive/lib/mysql-connector-java-5.1.32.jar $SQOOP_HOME/lib/`
6.验证安装是否成功
sqoop-version 或者 sqoop version
三、Sqoop使用场景及常用参数
业务需求分析
Sqoop可以在HDFS/Hive和关系型数据库之间进行数据的导入导出,其中主要使用了import
和export
这两个工具。这两个工具非常强大,提供了很多选项帮助我们完成数据的迁移和同步。比如,下面两个潜在的需求:
- 业务数据存放在关系数据库中,如果数据量达到一定规模后需要对其进行分析或同统计,单纯使用关系数据库可能会成为瓶颈,这时可以将数据从业务数据库数据导入(import)到Hadoop平台进行离线分析
- 对大规模的数据在Hadoop平台上进行分析以后,可能需要将结果同步到关系数据库中作为业务的辅助数据,这时候需要将Hadoop平台分析后的数据导出(export)到关系数据库
这里,我们介绍Sqoop完成上述基本应用场景所使用的import和export工具,通过一些简单的例子来说明这两个工具是如何做到的
工具通用选项
import 和 export工具有些通用的选项,如下表所示:
选项 | 含义说明 |
---|---|
--connect <jdbc-uri> | 指定JDBC连接字符串 |
--connection-manager <class-name> | 指定要使用的连接管理器类 |
--driver <class-name> | 指定要使用的JDBC驱动类 |
--hadoop-mapred-home <dir> | 指定$HADOOP_MAPRED_HOME路径 |
--help | 打印用法帮助信息 |
--password-file | 设置用于存放认证的密码信息文件的路径 |
-P | 从控制台读取输入的密码 |
--password <password> | 设置认证密码 |
--username <username> | 设置认证用户名 |
--verbose | 打印详细的运行信息 |
--connection-param-file <filename> | 可选,指定存储数据库连接参数的属性文件 |
数据导入工具import
import工具,是将HDFS平台外部的结构化存储系统中的数据导入到Hadoop平台,便于后续分析;我们先看一下import工具的基本选项及其含义,如下表所示:
选项 | 含义说明 |
---|---|
--append | 将数据追加到HDFS上一个已存在的数据集上 |
--as-avrodatafile | 将数据导入到Avro数据文件 |
--as-sequencefile | 将数据导入到SequenceFile |
--as-textfile | 将数据导入到普通文本文件(默认) |
--boundary-query <statement> | 边界查询,用于创建分片(InputSplit) |
--columns <col,col,col…> | 从表中导出指定的一组列的数据 |
--delete-target-dir | 如果指定目录存在,则先删除掉 |
--direct | 使用直接导入模式(优化导入速度) |
--direct-split-size <n> | 分割输入stream的字节大小(在直接导入模式下) |
--fetch-size <n> | 从数据库中批量读取记录数 |
--inline-lob-limit <n> | 设置内联的LOB对象的大小 |
-m,--num-mappers <n> | 使用n个map任务并行导入数据 |
-e,--query <statement> | 导入的查询语句 |
--split-by <column-name> | 指定按照哪个列去分割数据 |
--table <table-name> | 导入的源表表名 |
--target-dir <dir> | 导入HDFS的目标路径 |
--warehouse-dir <dir> | HDFS存放表的根路径 |
--where <where clause> | 指定导出时所使用的查询条件 |
-z,--compress | 启用压缩 |
--compression-codec <c> | 指定Hadoop的codec方式(默认gzip) |
--null-string <null-string> | 如果指定列为字符串类型,使用指定字符串替换值为null的该类列的值 |
--null-non-string <null-string> | 如果指定列为非字符串类型,使用指定字符串替换值为null的该类列的值 |
下面,我们通过实例来说明,在实际中如何使用这些选项
【1】全量导入mysql表数据到HIVE
方式一:先复制表结构到hive中再导入数据
1.将关系型数据的表结构复制到hive中
将mysql中的emp_add表的结构复制到hive中
sqoop create-hive-table \
--connect jdbc:mysql://node01:3306/userdb \
--table emp_add \
--username root \
--password hadoop \
--hive-table hivedatabase.emp_add_sp
注: --table emp_add为mysql中的数据库userdb中的表。
--hive-table emp_add_sp为hive中hivedatabase库中新建的表名称
2.将MySQL数据库中整个表数据导入到Hive表
将MySQL数据库 userdb 中 emp_add 表的数据导入到Hive表中
sqoop import \
--connect jdbc:mysql://node01:3306/userdb \
--table emp_add \
--username root \
--password hadoop \
--hive-table emp_add \
--hive-import
方式二:直接复制表结构数据到hive中
将MySQL数据库 userdb 中 emp_conn 表的结构以及数据导入到Hive的hivedatabase库中
sqoop import \
--connect jdbc:mysql://node01:3306/userdb \
--username root \
--password hadoop \
--table emp_conn \
--hive-import \
--m 1 \
--hive-database hivedatabase
【2】导入表数据子集
导入表数据子集(where过滤)
--where可以指定从关系数据库导入数据时的查询条件;
它执行在数据库服务器相应的SQL查询,并将结果存储在HDFS的目标目录
sqoop import \
--connect jdbc:mysql://node01:3306/userdb \
--table emp_add --m 1 \
--where "city ='sec-bad'" \
--username root \
--password hadoop \
--target-dir /wherequery
导入表数据子集(query查询)
注意事项:
使用query sql语句来进行查找不能加参数--table ;
并且必须要添加where条件;
并且where条件后面必须带一个$CONDITIONS 这个字符串;
并且这个sql语句必须用单引号,不能用双引号;
sqoop命令中,--split-by id 通常配合-m 10参数使用;用于指定根据哪个字段进行划分并启动多少个maptask。
sqoop import \
--connect jdbc:mysql://node01:3306/userdb \
--username root \
--password hadoop \
--target-dir /wherequery2 \
--query 'select id,name,deg from emp WHERE id>1203 and $CONDITIONS' \
--split-by id \
--fields-terminated-by '\t' \
--m 2
Append模式增量导入
sqoop import \
--connect jdbc:mysql://node01:3306/userdb \
--table emp --m 1 \
--username root \
--password hadoop \
--target-dir /appendresult \
--incremental append \
--check-column id \
--last-value 1205
注:
--check-column (col)
用来指定一些列,这些列在增量导入时用来检查这些数据是否作为增量数据进行导入,和关系型数据库中的自增字段及时间戳类似
这些被指定的列的类型不能使任意字符类型,如char、varchar等类型都是不可以的,同时-- check-column可以去指定多个列
--incremental (mode)
append:追加,比如对大于last-value指定的值之后的记录进行追加导入;
lastmodified:最后的修改时间,追加last-value指定的日期之后的记录
--last-value (value)
指定自从上次导入后列的最大值(大于该指定的值),也可以自己设定某一值
Lastmodified模式增量导入
append模式:
sqoop import \
--connect jdbc:mysql://node01:3306/userdb \
--table customertest \
--username root \
--password hadoop \
--target-dir /lastmodifiedresult \
--check-column last_mod \
--incremental lastmodified \
--last-value "2019-05-28 18:42:06" \
--m 1 \
--append
merge-key模式:
sqoop import \
--connect jdbc:mysql://node01:3306/userdb \
--username root \
--password hadoop \
--table customertest \
--target-dir /lastmodifiedresult \
--check-column last_mod \
--incremental lastmodified \
--last-value "2019-05-28 18:42:06" \
--m 1 \
--merge-key id
注:
lastmodified模式去处理增量时,会将大于等于last-value值的数据当做增量插入
数据导出工具export
选项 | 含义说明 |
---|---|
--validate <class-name> | 启用数据副本验证功能,仅支持单表拷贝,可以指定验证使用的实现类 |
--validation-threshold <class-name> | 指定验证门限所使用的类 |
--direct | 使用直接导出模式(优化速度) |
--export-dir <dir> | 导出过程中HDFS源路径 |
-m,--num-mappers <n> | 使用n个map任务并行导出 |
--table <table-name> | 导出的目的表名称 |
--call <stored-proc-name> | 导出数据调用的指定存储过程名 |
--update-key <col-name> | 更新参考的列名称,多个列名使用逗号分隔 |
--update-mode <mode> | 指定更新策略,包括:updateonly(默认)、allowinsert |
--input-null-string <null-string> | 使用指定字符串,替换字符串类型值为null的列 |
--input-null-non-string <null-string> | 使用指定字符串,替换非字符串类型值为null的列 |
--staging-table <staging-table-name | 在数据导出到数据库之前,数据临时存放的表名称 |
--clear-staging-table> | 清除工作区中临时存放的数据 |
--batch | 使用批量模式导出 |
默认模式导出HDFS数据到mysql
sqoop export \
--connect jdbc:mysql://node01:3306/userdb \
--username root \
--password hadoop \
--table employee \
--export-dir /emp/emp_data
--input-fields-terminated-by '\t'
注:
--input-fields-terminated-by '\t'
指定文件中的分隔符
--columns
选择列并控制它们的排序。当导出数据文件和目标表字段列顺序完全一致的时候可以不写;
否则以逗号为间隔选择和排列各个列。没有被包含在–columns后面列名或字段要么具备默认值,
要么就允许插入空值,否则数据库会拒绝接受sqoop导出的数据,导致Sqoop作业失败
--export-dir 导出目录,在执行导出的时候,必须指定这个参数,
同时需要具备--table或--call参数两者之一,--table是指的导出数据库当中对应的表,--call是指的某个存储过程
--input-null-string --input-null-non-string
如果没有指定第一个参数,对于字符串类型的列来说,“NULL”这个字符串就回被翻译成空值,
如果没有使用第二个参数,无论是“NULL”字符串还是说空字符串也好,对于非字符串类型的字段来说,这两个类型的空串都会被翻译成空值;
比如:
--input-null-string "\\N" --input-null-non-string "\\N"
更新导出(updateonly模式)
-- update-key,更新标识,即根据某个字段进行更新,
例如id,可以指定多个更新标识的字段,多个字段之间用逗号分隔
-- updatemod,指定updateonly(默认模式),仅仅更新已存在的数据记录,不会插入新纪录
更新导出:
sqoop export \
--connect jdbc:mysql://node01:3306/userdb \
--username root \
--password hadoop \
--table updateonly \
--export-dir /updateonly_2/ \
--update-key id \
--update-mode updateonly
更新导出(allowinsert模式)
-- update-key,更新标识,即根据某个字段进行更新,
例如id,可以指定多个更新标识的字段,多个字段之间用逗号分隔
-- updatemod,指定updateonly(默认模式),仅仅更新已存在的数据记录,不会插入新纪录
更新导出:
sqoop export \
--connect jdbc:mysql://node01:3306/userdb \
--username root --password hadoop \
--table allowinsert \
--export-dir /allowinsert_2/ \
--update-key id \
--update-mode allowinsert