数据迁移心得
前几天出差,去客户现场帮忙迁移数据,经过几天的奋战,终于将迁移数据自动化起来,并且可以日跑批操作,这里小编就跟大家分享下,这其中踩过的坑(也可能是实战经验不丰富导致)。
首先,荣小编我抱怨一下,不是自己熟悉的开发环境真的有些难过,给一台电脑,咱不说没有IDE,就连java都没有安装,连接数据库的工具也没有,唯一值得庆幸的是有xshell,但是完全不符合个人快捷键的喜好,没办法,想要开发高效,自己动手配置吧。单单是配置这些开发环境就整整牺牲了小编一上午的时间,还好后期开发有明显的提速。中午吃个饭,下午进入正题。
下午拿到迁移任务,发现一个库中有很多表,其中表有大有小,当时我沉默了几秒钟,感觉这个星期是交代在这里了。这里小编在那几秒钟的沉默中也把这个迁移流程想了一下,首先数据是存在关系型数据库中的,然后我们要通过sqoop将数据上传到HDFS中,然后用hive的外表去映射这些数据,最终建立有索引的内表来存储一份完整的数据。内表一般都是分区,分桶,且有索引的orc表,查询速度明显比外表快很多。那么接下来小编就将这其中的步骤,一点点的分析下。
10年积累的做网站、网站设计经验,可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你,你也不认识我。但先网站制作后付款的网站建设流程,更有高昌免费网站建设让你可以放心的选择与我们合作。
前期准备:
拿到一个库的数据时,我们首先分析下这里有哪些表比较大,哪些表比较小,将大表和小表分开,使用不同的迁移方法,一般都是客户提供每张表的数据条数,如果没有的话,只能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/库名/表名/分区名
- sqoop抽取数据的目录规划
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。
网站栏目:数据迁移心得
分享链接:http://scyanting.com/article/jjejog.html