本文介绍使用Sqoop从MySQL抽取数据到Hive的方法，包括完全抽取和基于时间戳的增量抽取。并详细演示了增量抽取的具体操作。
目录
一、Sqoop 简介
Sqoop 是一个在 Hadoop 与结构化数据存储（如关系数据库）之间高效传输大批量数据的工具。它在 2012 年 3 月被成功孵化，现在已是 Apache 的顶级项目。Sqoop 有 Sqoop1 和Sqoop2 两代，Sqoop1 最后的稳定版本是 1.4.6，Sqoop2 最后版本是 1.99.6。需要注意的是，1.99.6 与 1.4.6 并不兼容，而且截止目前为止，1.99.6 并不完善，不推荐在生产环境中部署。
1. Sqoop1
Sqoop1 的架构图如下所示。
第一代 Sqoop 的设计目标很简单：
- 在企业级数据仓库、关系数据库、文档系统和 Hive、HDFS 之间导入导出数据。
- 基于客户端的模型。
- 连接器使用厂商提供的驱动。
- 没有集中的元数据存储。
- 只有 Map 任务，没有 Reduce 任务，数据传输和转化都由 Mappers 提供。
- 可以使用 Oozie 调度和管理 Sqoop 作业。
Sqoop1 是用 Java 开发的，完全客户端驱动，严重依赖于 JDBC，可以使用简单的命令行命令导入导出数据。例如，从 MySQL 向 HDFS 导入数据：
sqoop import --connect jdbc:mysql://localhost/testdb --table PERSON --username test --password ****上面这条命令形成一系列任务：
- 生成 SQL 代码。
- 执行 SQL 代码。
- 生成 Map 作业。
- 执行 Map 作业。
- 数据传输到 HDFS。
从 HDFS 向 MySQL 导出数据：
sqoop export --connect jdbc:mysql://localhost/testdb --table CLIENTS_INTG --username test --password **** --export-dir /user/localadmin/CLIENTS上面这条命令形成一系列任务：
- 生成 Map 作业。
- 执行 Map 作业。
- 从 HDFS 的 /user/localadmin/CLIENTS 路径传输数据。
- 生成 SQL 代码。
- 向 CLIENTS_INTG 插入数据。
Sqoop1 有许多简单易用的特性，如可以在命令行指定直接导入至 Hive、HDFS 或 HBase。连接器可以连接大部分流行的数据库：Oracle、SQLServer、MySQL、Teradata、PostgreSQL等。
Sqoop1 的主要问题包括：
- 繁多的命令行参数。
- 不安全的连接方式（直接在命令行写密码等）。
- 没有元数据存储，只能本地配置和管理，使复用受限。
2. Sqoop2
Sqoop2 的架构图如下所示。
Sqoop2 体系结构比 Sqoop1 复杂得多，被设计用来解决 Sqoop1 的问题。
（1）易用性
Sqoop1 需要客户端的安装和配置，而 Sqoop2 是在服务器端安装和配置。这意味着连接器只在一个地方统一配置，由管理员角色管理，操作员角色使用。类似地，只需要在一台服务器上配置 JDBC 驱动和数据库连接。Sqoop2 还有一个基于 Web 的服务：前端是命令行接口（CLI）和浏览器，后端是一个元数据知识库。用户可以通过 Web 接口进行导入导出，避免的错误选项和繁冗的步骤。此外，Sqoop2 还在服务器端整合了 Hive 和 HBase。Oozie 通过 REST API 管理 Sqoop 任务，这样当安装一个新的 Sqoop 连接器后，无需在 Oozie 中安装它。
（2）可扩展性
在 Sqoop2 中，连接器不再受限于 JDBC 词汇（必须指定 database、table 等），它甚至可以定义自己使用的词汇。例如，Couchbase 不需要指定表名，只需在执行充填或卸载操作时重载它。通用的功能将从连接器中抽取出来，使之只负责数据传输。在 Reduce 阶段实现通用功能，确保连接器可以从将来的功能性开发中受益。连接器不再需要提供与其它系统整合等下游功能，因此，连接器的开发者不再需要了解所有 Sqoop 支持的特性。
（3）安全性
当前，用户是通过执行‘sqoop’命令运行 Sqoop。Sqoop 作业的安全性主要由对执行 Sqoop 的用户信任所决定。Sqoop2 将作为基于应用的服务，通过按不同角色连接对象，支持对外部系统的安全访问。为了进一步安全，Sqoop2 不再允许生成代码、请求直接访问 Hive 或 HBase，也不对运行的作业开放访问所有客户端的权限。Sqoop2 将连接作为一级对象，包含证书的连接一旦生成，可以被不同的导入导出作业多次使用。连接由管理员生成，被操作员使用，因此避免了最终用户的权限泛滥。此外，连接还可以被限制只能进行某些基本操作（如导入导出）。通过限制同一时间打开连接的总数和一个禁止连接的选项来管理资源。
二、CDH 5.7.0 中的 Sqoop
CDH 5.7.0 中的 Sqoop 既包含 Sqoop1 又包含 Sqoop2，Sqoop1 的版本是 1.4.6，Sqoop2 的版本是 1.99.5。当前的 Sqoop2 还缺少 Sqoop1 的某些特性，因此 Cloudera 的建议是，只有当 Sqoop2 完全满足需要的特性时才使用它，否则继续使用 Sqoop1。CDH 5.7.0 中的 Sqoop1 和 Sqoop2 的特性区别如下表所示。
| 特性 | Sqoop1 | Sqoop2 |
| 所有主要 RDBMS 的连接器 | 支持 | 不支持 变通方案：使用的通用的 JDBC 连接器，它已经在 Microsoft SQL Server、PostgreSQL、MySQL 和 Oracle 数据库上测试过。 这个连接器应该可以在任何 JDBC 兼容的数据库上使用，但性能比不上 Sqoop1 的专用连接器。 |
| Kerberos 整合 | 支持 | 不支持 |
| 数据从 RDBMS 传输到 Hive 或 Hbase | 支持 | 不支持 变通方案：用下面两步方法。 1. 数据从 RDBMS 导入 HDFS 2. 使用适当的工具或命令（如 Hive 的 LOAD DATA 语句）手工把数据导入 Hive 或 Hbase。 |
| 数据从 Hive 或 Hbase 传输到 RDBMS | 不支持 变通方案：用下面两步方法。 1. 从 Hive 或 Hbase 抽出数据到 HDFS（文本文件或 Avro 文件） 2. 使用 Sqoop 将上一步的输出导入 RDBMS | 不支持 变通方案如 Sqoop1。 |
三、使用 Sqoop 抽取数据
在本示例中使用 Sqoop1 从 MySQL 库抽取数据到 Hive。从源抽取数据导入数据仓库（本示例的 RDS）有两种方式，可以从源把数据抓取出来（拉），也可以请求源把数据发送（推）到数据仓库。影响选择数据抽取方式的一个重要因素是操作型系统的可用性和数据量，这基于是抽取整个数据还是仅仅抽取自最后一次抽取以来的变化数据。考虑以下两个问题：
- 需要抽取哪部分源数据加载到数据仓库？有两种方式，完全抽取和变化数据捕获。
- 数据抽取的方向是什么？有两种方式，拉模式（从数据仓库去拉）和推模式（通过源去推）。
1. 完全抽取和变化数据捕获
如果数据量很小并且易处理，一般来说采取完全源数据抽取，将所有的文件记录或所有的数据库表数据抽取至数据仓库。这种方式适合引用类型的源数据，比如邮政编码，引用型源数据通常是维度表的源。如果源数据量很大，抽取全部数据是不可行的，那么只能抽取变化的源数据，即自最后一次抽取以来变化的数据。这种数据抽取模式称为变化数据捕获（Change Data Capture，CDC），通常被用于抽取操作型系统的事务数据，比如销售订单。
CDC 大体可以分为两种，一种是侵入式的，另一种是非侵入式的。所谓侵入式的是指 CDC 操作会给源系统带来性能的影响。只要 CDC 操作以任何一种方式执行了 SQL 语句，就可以认为是侵入式的 CDC，常用的四种 CDC 方法中有三种是侵入性的。这四种方法是：基于时间戳的 CDC、基于触发器的 CDC、基于快照的 CDC、基于日志的 CDC。下表总结了四种 CDC 方案的特点。
|
| 时间戳方式 | 快照方式 | 触发器方式 | 日志方式 |
| 能区分插入/更新 | 否 | 是 | 是 | 是 |
| 周期内，检测到多次更新 | 否 | 否 | 是 | 是 |
| 能检测到删除 | 否 | 是 | 是 | 是 |
| 不具有侵入性 | 否 | 否 | 否 | 是 |
| 支持实时 | 否 | 否 | 是 | 是 |
| 需要 DBA | 否 | 否 | 是 | 是 |
| 不依赖数据库 | 是 | 是 | 否 | 否 |
2. 从源拉数据或源来推数据
如果想让数据源只是简单的等待数据仓库来抽取，那么可以使用拉模式。但是必须确认，在数据仓库抽取数据时，源数据必须是可用的而且已经准备好了数据。如果抽取数据的实时性非常重要，或者希望数据源一旦准备好数据就立即发送，那么应该使用由数据源推数据的抽取模式。如果数据源是受到保护并且是禁止访问的，则只能使用数据源推数据的方式。
下表中汇总了本示例中维度表和事实表用到的源数据表及其抽取模式。
| 源数据表 | 数据仓库RDS表 | 抽取模式 |
| customer | customer | 整体、拉取 |
| product | product | 整体、拉取 |
| sales_order | sales_order | 基于时间戳的 CDC、拉取 |
3. 覆盖导入
对于 customer、product 这两个表采用整体拉取的方式抽数据。ETL 通常是按一个固定的时间间隔，周期性定时执行的，因此对于整体拉取的方式而言，每次导入的数据需要覆盖上次导入的数据。Sqoop 中提供了 hive-overwrite 参数实现覆盖导入。hive-overwrite 的另一个作用是提供了一个幂等操作的选择。所谓幂等操作指的是其任意多次执行所产生的影响均与一次执行的影响相同。这样在导入失败或修复 bug 后可以再次执行该操作，而不用担心重复执行会对系统造成数据混乱。
具体命令如下：
sqoop import --connect jdbc:mysql://cdh1:3306/source?useSSL=false --username root --password mypassword --table customer --hive-import --hive-table rds.customer --hive-overwrite
sqoop import --connect jdbc:mysql://cdh1:3306/source?useSSL=false --username root --password mypassword --table product --hive-import --hive-table rds.product --hive-overwrite4. 增量导入
Sqoop 提供增量导入模式用于只导入比已经导入行新的行。下表的参数用来控制增量导入。
| 参数 | 描述 |
| --check-column (col) | 在确定应该导入哪些行时，指定被检查的列。列不应该是CHAR/NCHAR/VARCHAR/VARNCHAR/LONGVARCHAR/LONGNVARCHAR 数据类型。 |
| --incremental (mode) | 指定 Sqoop 怎样确定哪些行是新行，有效值是 append 和 lastmodified。 |
| --last-value (value) | 指定已经导入数据的被检查列的最大值 |
Sqoop 支持两种类型的增量导入：append 和 lastmodified，可以使用 --incremental 参数指定增量导入的类型。当被导入表的新行具有连续递增的行 id 值时，应该使用 append 模式。指定行 id 为 --check-column 的列。Sqoop 导入那些被检查列的值比 --last-value 给出的值大的数据行。
Sqoop 支持的另一个表修改策略叫做 lastmodified 模式。当源表的数据行可能被修改，并且每次修改都会更新一个 last-modified 列为当前时间戳时，应该使用 lastmodified 模式。那些被检查列的时间戳比 --last-value 给出的时间戳新的数据行被导入。
在增量导入的最后，后续导入使用的 --last-value 会被打印出来。当执行后面的导入时，应该用这种方式指定 --last-value 参数的值，以确保只导入新的或修改过的数据。可以通过一个增量导入的保存作业自动执行这个过程，这是适合重复执行增量导入的方式。
5. 示例实操
有了对 Sqoop 增量导入的基本了解，下面看一下如何在本示例中使用它抽取数据。对于 sales_order 这个表采用基于时间戳的 CDC 拉取方式抽数据。这里假设源系统中销售订单记录一旦入库就不再改变，或者可以忽略改变，也就是说销售订单是一个随时间变化单向追加数据的表。sales_order 表中有两个关于时间的字段，order_date 表示订单时间，entry_date 表示订单数据实际插入表里的时间，在后面讨论“迟到的事实”时就会看到两个时间可能不同。那么用哪个字段作为 CDC 的时间戳呢？设想这样的场景，一个销售订单的订单时间是 2015 年 1 月 1 日，实际插入表里的时间是 2015 年 1 月 2 日，ETL 每天 0 点执行，抽取前一天的数据。如果按 order_date 抽取数据，条件为 where order_date >= '2015-01-02' AND order_date < '2015-01-03'，则 2015 年 1 月 3 日 0 点执行的 ETL 不会捕获到这个新增的订单数据。因此应该以 entry_date 作为 CDC 的时间戳。
下面测试一下增量导入：
1. 建立 sqoop 增量导入作业
sqoop job --create myjob_1 \
-- \
import \
--connect "jdbc:mysql://cdh1:3306/source?useSSL=false&user=root&password=mypassword" \
--table sales_order \
--columns "order_number, customer_number, product_code, order_date, entry_date, order_amount" \
--where "entry_date < current_date()" \
--hive-import \
--hive-table rds.sales_order \
--incremental append \
--check-column entry_date \
--last-value '1900-01-01'说明：在作业中使用的 --where 参数，是为了只导入前一天的数据。
2. 查看此时作业中保存的 last-value
sqoop job --show myjob_1 | grep last.value结果如下图所示。
可以看到，last-value 的值为初始的'1900-01-01'。
3. 首次执行作业
sqoop job --exec myjob_1因为 last-value 的值为'1900-01-01'，所以这次会导入全部数据，结果如下图所示。
4. 查看此时作业中保存的 last-value
sqoop job --show myjob_1 | grep last.value结果如下图所示。
可以看到，last-value 的值为当前最大值'2016-06-30 05:20:47'
5. 源库增加两条数据
SET @customer_number := floor(1 + rand() * 6);
SET @product_code := floor(1 + rand() * 2);
SET @order_date := from_unixtime(unix_timestamp('2016-07-03') + rand() * (unix_timestamp('2016-07-04') - unix_timestamp('2016-07-03')));
SET @amount := floor(1000 + rand() * 9000);
INSERT INTO sales_order
VALUES (101,@customer_number,@product_code,@order_date,@order_date,@amount);
SET @customer_number := floor(1 + rand() * 6);
SET @product_code := floor(1 + rand() * 2);
SET @order_date := from_unixtime(unix_timestamp('2016-07-04') + rand() * (unix_timestamp('2016-07-05') - unix_timestamp('2016-07-04')));
SET @amount := floor(1000 + rand() * 9000);
INSERT INTO sales_order
VALUES (102,@customer_number,@product_code,@order_date,@order_date,@amount);
COMMIT;上面的语句向 sales_order 插入了两条记录，一条是 7 月 3 日的，另一条是 7 月 4 日的，如下图所示。
6. 再次执行 sqoop 作业
因为 last-value 的值为'2016-06-30 05:20:47'，所以这次只会导入 entry_date 比'2016-06-30 05:20:47'大的数据。
sqoop job --exec myjob_17. 查看此时作业中保存的 last-value
sqoop job --show myjob_1 | grep last.value结果如下图所示。
可以看到，last-value 的值已经变为'2016-07-03 22:45:46'
8. 在 hive 的 rds 库里查询
select * from sales_order order by order_number desc;结果如下图所示，可以看到 rds.sales_order 表中只新增了一条数据，7 月 4 日的记录被作业中的 where 过滤掉。
至此介绍了使用 Sqoop 抽取数据的一般方法。Sqoop 有众多的命令行参数，具体可参考官方文档，链接地址如下：Sqoop User Guide (v1.4.6)
扫一扫
1583
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
