数据迁移心得

发布时间：2020-05-29 06:23:19 作者：原生zzy
来源：网络阅读：664

前几天出差，去客户现场帮忙迁移数据，经过几天的奋战，终于将迁移数据自动化起来，并且可以日跑批操作，这里小编就跟大家分享下，这其中踩过的坑（也可能是实战经验不丰富导致）。
首先，荣小编我抱怨一下，不是自己熟悉的开发环境真的有些难过，给一台电脑，咱不说没有IDE，就连java都没有安装，连接数据库的工具也没有，唯一值得庆幸的是有xshell，但是完全不符合个人快捷键的喜好，没办法，想要开发高效，自己动手配置吧。单单是配置这些开发环境就整整牺牲了小编一上午的时间，还好后期开发有明显的提速。中午吃个饭，下午进入正题。
下午拿到迁移任务，发现一个库中有很多表，其中表有大有小，当时我沉默了几秒钟，感觉这个星期是交代在这里了。这里小编在那几秒钟的沉默中也把这个迁移流程想了一下，首先数据是存在关系型数据库中的，然后我们要通过sqoop将数据上传到HDFS中，然后用hive的外表去映射这些数据，最终建立有索引的内表来存储一份完整的数据。内表一般都是分区，分桶，且有索引的orc表，查询速度明显比外表快很多。那么接下来小编就将这其中的步骤，一点点的分析下。

前期准备：

拿到一个库的数据时，我们首先分析下这里有哪些表比较大，哪些表比较小，将大表和小表分开，使用不同的迁移方法，一般都是客户提供每张表的数据条数，如果没有的话，只能selecct count(*) from table; 将这些表的数据查出来，不仅便于区分大小表，而且对后期数据核对有较大的帮助。

1. 从关系型数据库将数据迁移到HDFS中

这里迁移数据小编是用的sqoop，虽然sqoop比较慢，但是学习成本相对较低，而且便于批量的生成语句，对开发要求没那么高。首先先测试一个sqoop是否可以成功的迁移数据，然后编写脚本批量的生成sqoop语句，最后调用这些语句，后台并行的迁移数据。这里小编先说说使用sqoop的几个小窍门：

如果集群的资源比较充分，一般新集群是没有什么生产任务的，我们的sqoop语句中可以加入：
```
-m 这个参数可以设置为>1 ，表示并行多个map去抽取数据。
--split-by 当然-m 参数设置大于后，要同时设置这个参数，表示以表中的哪一个字段去分map并行。
```
这里选取--split-by 尽量使用表中比较分散的字段，保证每一个map任务抽取的数据量都大致相同。
如果表的数据量比较大，比如超过亿条，我们这里就需要将这个表分成多个sqoop去抽取：

--query : 指定where后的条件，抽取部分数据
这样的好处是：如果只有一个sqoop任务，抽取了90%的数据后，发现sqoop任务挂了，那么本次抽取失败，不仅耗时，而且数据没有抽取到。分多个sqoop任务，不仅可并行，而且每个任务的数据量也不大，如果有任务挂了，只需要抽象抽取那个where条件下的数据即，并且对于找错也有极大的帮助。
分区字段的选取也同样重要，这里一般都是使用日期作为where的后的条件，保证每个sqoop任务分的的数据量相差无几。
- sqoop抽取数据的目录规划
```
#小表目录规划
/tmp/库名/表名
#大表的目录规划
/tmp/库名/表名/分区名
```
query语句：在sqoop命令中，我们编写查询语句去抽取数据时，切记不要：

-- ×
select * from table;
-- √
select 字段1,字段2.... from table;
不然可能会导致sqoop抽取速度变慢，甚至可能导致没有抽取到数据。
当我们注意了以上的内容后，就可以编写脚本批量的生成每张表的sqoop语句了，根据库名.表名，获取关系型数据库中表的元数据，最后将sqoop组装起来。最后在编写任务脚本，定时执行这些sqoop语句。
实际数据分享：
这里小编测试过，数据量比较大时，多sqoop和单sqoop的耗时：
以600G数据为例：
- 多sqoop 并行抽取数据耗时：3~4小时。
- 单sqoop 抽取数据耗时：12小时以上。
- 单sqoop && （-m 1）抽取5千万条数据，大概是27分钟。

2. 建立外表映射

说白了就是将抽取到的数据，在hive中通过外表的方式映射出来，其实这里没什么难的，主要是看客户如果要求，可能是外表单独一个库，或者外表的名称统一是：表名_ext。但是切记，不要手动的去编写建表语句，如果表有百张以上，心态容易炸，这里可以使用关系型数据库的元数据，生成hive的建表语句的，这里我们与MySQL为例：

SELECT CONCAT('create table ', TABLE_NAME, '(', substring(column_info, 1, length(column_info) - 1), ')', ' comment ', '"', TABLE_COMMENT, '"', ';')
FROM (SELECT TABLE_NAME, TABLE_COMMENT, group_concat(CONCAT(COLUMN_NAME, ' ', DATA_TYPE, ' comment ', '"', COLUMN_COMMENT, '"')) AS column_info
FROM (SELECT t1.TABLE_NAME, CASE WHEN t2.TABLE_COMMENT = NULL THEN t1.TABLE_NAME ELSE t2.TABLE_COMMENT END AS TABLE_COMMENT, COLUMN_NAME, CASE WHEN DATA_TYPE = 'varchar' THEN 'string' WHEN DATA_TYPE = 'int' THEN 'int' WHEN DATA_TYPE = 'tinyint' THEN 'tinyint' WHEN DATA_TYPE = 'decimal' THEN 'double' WHEN DATA_TYPE = 'datetime' THEN 'string' WHEN DATA_TYPE = 'timestamp' THEN 'string' WHEN DATA_TYPE = 'float' THEN 'double' WHEN DATA_TYPE = 'double' THEN 'double' WHEN DATA_TYPE = 'bigint' THEN 'bigint' END AS DATA_TYPE, CASE WHEN COLUMN_COMMENT = NULL THEN COLUMN_NAME ELSE COLUMN_COMMENT END AS COLUMN_COMMENT
FROM COLUMNS t1 JOIN TABLES t2 ON t1.TABLE_NAME = t2.TABLE_NAME
WHERE t1.TABLE_NAME = 't_app_equipment_status'
) t3
GROUP BY TABLE_NAME, TABLE_COMMENT
) t4;

网上这样的例子很多，这里小编就不在介绍。当外表建立好之后，最好核对一下数据量的大小，对比下关系型数据库中表的数据和hive中的数据是否相同，这样验证了sqoop这一环节是否有数据丢失的情况。
遇到的坑：
当大表我们在分区抽取时，是无法直接映射成为外表的，我们需要建立范围分区表，将表的分区目录映射到各个分区上。

3. 建立高效的内表

其实这一步就是将，外表的数据，insert到一张和外表字段相同的经过优化的内表中，这张内表一般都是分区分桶，建立索引，或者基于闪存的表，反正就是查询的速度大大提高的一张表，也叫做业务表。
小编这里用的是一种基于闪存的高效查询的，企业内部开发的一种表结构。小编这里介绍一下如何确定分桶字段：分桶的好处是
（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
那么如果确定分桶字段呢，一般的如果有主键的表就使用主键作为分桶字段，如果没有主键的表，找几个比较分散的字段使用：

 select count(distinct feild) from table;

找出数据最大的那个字段作为分桶字段。具体的分桶数，这里建议是一个质数，因为如果是一个非质数，那么可能导致分桶不均匀，因为如果分桶数是9的话，那么字段值如果为18、27都会分到一个桶中，可能会导致桶“热点”。

4. 外表insert 到内表中

这个过程是耗时仅次于sqoop的过程，由于我们的内表建立的分区，那么在这个步骤中我们需要使用hive的动态分区插入，插入语句一般都是：

    insert into table_1 partition(par_field) select field1.field2...from table_2 ;

这里需要注意的是select 最后一个字段一定要是分区字段。
当我们insert 后，需要核对下是否所有的数据全部insert成功，此时：外表数据量=内表数据量=关系型数据库数据量。

5. 日增量数据的处理

当我们完成数据迁移后，其实外表相当于一个中转站，仅仅是将数据中转到内表中，如果我们确保了内表中有一份完整的数据，此时可以将外表的数据清空，这也是为什么我们将外表的location 设置为/tmp下的原因，清空外表数据后，将日增数据抽取到外表的location地址上，然后全量的将外表数据insert到内表中，就保证了日增量数据的成功导入到hive。