MySQL 到 Hadoop：Sqoop 数据迁移 ETL

ETL：Extract-Transform-Load 数据迁移过程

ETL，即 Extract-Transform-Load，数据的 抽取-清洗转换-加载 的一个数据迁移的过程，通常这种迁移是从一些易搭建，使用简单的关系型数据库（例如 MySQL）出发的，将大量的数据从类似这样的数据库中迁移到一些像 Hadoop 这样的高可用的分布式系统的数据仓库（warehouse，例如 HBase）中，进行高效的数据分析，这也是数据迁移的一个重要的目的之一。通常数据迁移的具有如下的过程：

首先，会通过一些数据迁移的技术（例如 Sqoop）从原始数据库中拉取数据存放在一个过渡仓库——ODS（Operational Data Store，可操作数据存储） 当中，然后再进行转换和清洗，最终将数据放到 DW（Data Warehouse，数据仓库） 当中，然后分布式系统上不同的 DM（Data Mart，数据集市） 根据应用需求从数据仓库中独立出一部分数据，称为部门数据或者主题数据（subjectarea），进行大数据分析处理，最终得到分析结果的报表。

一、Extract 数据抽取

---

数据的抽取是针对原始数据库的数据进行分析和抽取的一个过程，其中对数据的分析调研过程非常重要，通过调研确定不同的数据类型（结构化，非结构化或者是手工数据），从而才能够对原始数据库进行数据抽取时采取对应有效的抽取方法，例如对于非结构化的数据来说，通常可以采用 Sqoop 来进行数据抽取，而对于一些非结构化的数据来说，可能需要使用其他一些数据接口来实现。

再者，如果DW（Data Warehouse，数据仓库） 的 DBMS 与源数据库的类型相同的话（例如都是 MySQL），则可以直接通过 Sqoop 使用 query 选项进行 SQL 查询直接访问。

接下来，再通过相应的方法进行数据抽取。

1. ODS：Operational Data Store - 可操作数据存储

从原始数据库例如 MySQL 中将数据拉取出来，首先存储在 ODS（Operational Data Store，可操作数据存储） 中作为从原始数据库到数据仓库之间的过渡。

ODS 的数据结构通常和原始数据库中的逻辑结构保持一致，以减少操作的复杂性，提高 ETL 的整体效率。但是在数据的物理结构上和业务系统的原始数据结构不相同。对于原始数据库来说，ODS 始终倾听，跟进并为使用者提供目标数据库的实时状态，通过增量更新保持数据同步，新增的数据通过原始数据库的时间戳标识来避免冲突。

通过将数据抽取到 ODS 中，并保持相同的数据结构，从个人理解来看，可以将 ETL 的效率瓶颈，即 “T” 数据清洗和转换阶段和原始数据库的数据抽取这两个工作分离开来，使得原始数据库的数据抽取不会受到数据清晰和转换的效率瓶颈的限制。这样不仅使得 ODS 可以实时的跟进原始数据库的状态，并且进而有能力从原始数据库方面接管一部分数据查询和报表生成的工作（因为 ODS 和原始数据库的数据是同步的），相对于存放汇总数据的数据仓库来说提供更加明细的数据查询。

因此，ODS 的数据周期一般也较短，数据最终都会流入 DW（Data Warehouse，数据仓库） 之中。

2. DW：Data Warehouse - 数据仓库

事实上，数据仓库往往分为两个部分：

• ODS（Operational Data Store，可操作数据存储）
• DW（Data Warehouse，数据仓库）

数据从 ODS 出发，通过一定的业务规则要求的计算和整合，最终得到的相对汇总的数据存放到 DW 中。DW 存放的是历史数据，不同于 ODS 的实时同步，DW 的转换过程往往会耗费大量的时间，这也是 ETL 数据迁移的瓶颈所在，通常是经过一段时间进行一次转换，存储到 DW 当中，并且数据将长期存放，不可修改，历史数据将会长期保留，并在此基础上进行增量更新。

在整个数据仓库中将会存放所有各种系统的数据，以及不同时期的历史数据，用于各种业务的数据分析或者决策管理。因此，转换过程往往需要较高的效率，并且得到的数据要有较高的质量以及准确性，唯一性的保证，以用于数据分析，所以往往需要进行监控和定期审查。此外，在构建数据仓库的时候进行根据宏观主题进行一定的结构组织，从而方便数据集市面向应用领域进行数据独立和查询。

3. DM：Data Mart - 数据集市

数据集市是最终面向具体应用的一个独立于数据仓库的数据库，针对性强，结构清晰，每一个数据集市都会对应一个具体的应用，只关心具体应用所需的数据。最终进行的各项指标的数据分析都将建立在这些数据集市上。

二、Transform 数据清洗和转换

---

事实上这是一个清洗+转换（Cleaning and Transform） 的过程。

1. 数据清洗

在数据从原始数据仓库抽取出来去到 ODS 的过程中，会对原始数据进行数据的清理，目的是去除以下三种数据：

• 不完整数据：部分字段缺失
• 错误数据：数据格式问题，即因业务层面的逻辑判断问题向数据库输入了错误的数据，例如错误的日期格式，错误的数据类型等
• 重复数据：记录重复

2. 数据转换

数据转换根据具体需求具体设计，但是通常会涉及到三个方面的转换：

• 不一致的数据转换：对来自不同系统具有相同意义的数据进行统一
• 数据颗粒度的转换：根据具体的数据分析要求，对原始的明细数据进行颗粒度的转换，从而提高数据分析的效率和准确性
• 根据需求转换计算：根据具体的指标或者业务需求进行数据的转换计算

三、Load 数据加载

---

根据预先定义好的数据仓库模型，将经过数据清洗和转换的数据加载到数据仓库之中，是构建数据仓库最重要的一步，分为：

• 全量加载
• 增量加载

四、数据迁移方法

数据迁移根据数据类型不同以及实时性要求的不同，采用的方法也不同，Canal 可以通过伪装成一个 MySQL 的结点实现实时 CRUD 的数据迁移，Streamsets 则可以实现增量同步，而历史数据的同步则可以通过 Sqoop 或者是 DataX 来完成。而对于一些非结构化的数据，则可能需要其他一些数据接口来实现数据迁移。

这里，我们简单介绍以下 Sqoop 的迁移方法。

1.Sqoop

Sqoop，这个名字其实可以看成是 SQL 和 Hadoop 的合并简称。Sqoop 拥有一个可扩展的框架，可以向任何支持批量数据传输的外部存储系统（例如 Hadoop）中进行数据的导入，通过在这个框架下的一个模块化的组件 —— Sqoop 连接器（connector） 进行操作。这样的一个连接器可以支持大多数常用的关系型数据库系统（例如 MySQL），并且还有一个通用的 JDBC 连接器，可以连接支持 JDBC 的数据库。

例如，现在将从一个 MySQL 数据库把一个表中的所有数据导入到 Hadoop 上的 Hive 中，则此时 Sqoop 将会通过 JDBC 来检查将要导入到 Hive 中的表，检索出所有的列，即字段，得到字段对应的 SQL 数据类型，映射为 Java 中对应的数据类型。MapReduce 应用中将会把这些字段保存在对应的数据类型中，然后通过 MapReduce 任务来完成数据的导入，最终保存在 Hadoop 分布式文件系统上面的一个目录下。此时 Hive 可以直接将数据拉取到依赖的数据库中（可能也是一个 MySQL 数据库）。

sqoop [generic args] [import args] [hive args]

Sqoop 通用参数（generic args）：

参数	作用
--connect	指定 JDBC 连接，字符串类型
--driver	指定 JDBC 驱动
--password	密码
--username	用户名

导入数据相关参数（import args）：

参数	作用
--append	增量导入
--fetch-size	一次从数据库中读取的数据量，通过设置这个值， 执行多次 Sqoop 来避免大量 IO 和内存溢出的问题
--import-all-tables	导入数据库中的全部表，通常结合 --exclude-tables使用
--exclude-tables	不包含的表

Sqoop 和 Hive 的相关参数（hive args）：

参数	作用
--hive-home <dir>	指定 Hive 的家目录，即重写目标机器上的 $HIVE_HOME
--hive-import	表名这个Sqoop是将表导入 Hive
--hive-table	指定导入的 Hive 的表名
--create-hive-table	创建对应的新表，但如果该表已存在，则报错，导入失败
--hive-drop-import-delims	去掉默认分隔符，默认列分隔符为 \001，行分隔符为 \n，可以防止数据内容中因包含换行符导致导入数据和源数据不一致的问题
--hive-overwrite	当对应的表在 Hive 中已存在时，可以通过这个参数命令重写已存在的表，不会报错

对于 Sqoop 来说，最主要的作用就是在关系型数据库和 Hadoop 的分布式文件系统之间，例如和 eHive 数据库，进行数据传输。

1.1 MySQL -> Hive

假如，此时要将 MySQL 中的数据导入到 Hive，根据导入的表数量的不同，有两种导入选项：

• improt：单表导入
• import-all-tables：全库导入，可以指定哪些表不进行导入

1.1.1 import

对这两个导入选项来说，都可以使用通用参数（顾名思义）以及对应的 Hive 参数，不同的是，import 需要对指定导入的表的名称 --table TABLE_NAME，同时可以指定导入时在分布式文件系统上使用的中间目录 --target-dir DIR_NAME，同时，为了使得下次导入时不会因为目录已存在而报错，可以通过 --delete-target-dir 来删除已存在的目标目录。例如：

sqoop import \
--connect jdbc:mysql://your_ip_addr/database_name?useUnicode=true&characterEncoding=UTF-8 \
--username your_username \
--password your_password \
--table source_table_in_mysql \
--delete-target-dir \
--target-dir specified_target_dir \
--database hive_database_name \
--hive-import \
--hive-table specified_hive_table_name \
# --create-hive-table, this argument will create new table if not exists, else error
# --hive-overwrite, this argument will overwrite exist table, else do nothing
--driver com.mysql.jdbc.Driver \
-m 1

即，将 database_name 中的 source_table_in_mysql 这张表通过指定的编码方式导入到指定的数据库 hive_database_name 的 specified_hive_table_name 上，并且告诉 Sqoop 删除已存在的中间目录防止冲突。

1.1.2 import-all-tables

同样在可以指定通用的参数和 hive 的相关参数之外，可以指定特定的参数 --warehouse-dir 以及 --exclude-tables，指定中间目录以及被除外的表（不导入）：

sqoop import \
--connect jdbc:mysql://your_ip_addr/database_name?useUnicode=true&characterEncoding=UTF-8 \
--username your_username \
--password your_password \
--table source_table_in_mysql \
--warehouse /your/warehouse/dir \
--database hive_database_name \
--hive-import \
--create-hive-table \
# --hive-overwrite, this argument will overwrite exist table, else do nothing
--driver com.mysql.jdbc.Driver \
-m 1

不过，同样涉及到中间目录的删除问题，因此可以：

hadoop dfs -rm -r /your/warehouse/dir

注意，-rm -r 是危险命令，因此请确认中间目录是否为重要目录。

关于增量导入

对于一个应用或者某项指标来说，往往需要定时进行数据分析，因此需要定时执行数据迁移。Sqoop 提供了 append 模式 进行增量导入，添加新行。通过 --incremental-append 这个参数选项来激活这个模式。此外，还可以通过 --incremental-lastmodified 进行基于时间的增量导入，此时将更新表的状态到原始数据库最后一次修改的状态，除了增删行之外，对于现有行的修改也会同步，需要通过 --check-column 指定对应的列，来记录最近的一次更新时间。

关于一致性保证

Sqoop 通过 MapReduce 任务来向 HDFS 进行数据导入，因此可以通过在导入过程中禁止运行任何对表造成修改的进程来保证数据的一致性（所以这也是 ODS 的意义所在，使得在耗费较长时间的转换过程中，原始数据库的 CRUD 不会受到影响）。

1.2 Hive -> MySQL

1.2.1 export

通过 export 导出参数可以将 hive 中的数据导出到 mysql 上：

sqoop export \
--username username \
--password password \ 
--table table_name_in_mysql \
--export-dir /path/to/your/table/on/HDFS \
-m 1