Mysql 上亿级数据导入Hive思路分享

时间:2024-04-06 19:15:56

Mysql 上亿级数据导入Hive思路分享

前提条件

数据库容量上亿级别,索引只有id,没有创建时间索引

达到目标:

  • 把阿里云RDS Mysql表数据同步到hive中,按照mysql表数据的创建时间日期格式分区,每天一个分区方便查询
  • 每天运行crontab定时的增量备份数据,还是依据自增的id

遇到的问题:

  • 没法建立创建时间的索引,不能按时间范围去查询,那样会严重影响线上数据库的性能?
    只能按照id的方式去增量的读取索引,存储到临时表,然后在转储到正式表,动态的写入时间分区
     
  • 使用sqoop直接导入hive?还是把数据导入到hdfs以內建表的形式把数据写入到指定的临时表?
    如果直接使用sqoop hive import 不支持,query 语句,没法自定义抽取字段,没办法按照id范围去锁定部分数据,
    使用hive import只能全量同步表而且id条件只能配到各个地方,我个人感觉query比较适合我灵活一些所以就使用
    sqoop导入hdfs內建表的方式来同步数据
  • 读取和写入一次要分配,不能一次读取太多,影响线上数据库的性能,线上数据库用的阿里云RDS?
    1. 首先查询mysql max(id),和 hive max(id),计算差值后分批去加载,查询hive max 没有直接连接hive,而是使用了一个迂回策略,
       使用python调用系统命令行执行hive -e 查询最大值,并写入到本地文件系统,然后查询本地文件系统最大值
    2.我这里是按每次300万一次,单次差值小于300万执行一次加载
    3.用sqoop分4个map任务去执行,300万数据大概需要1-2分钟左右读取到本地
    4. mysql中需要5秒左右查询min,max,id 确定本次数据分割的id范围,
    5.单个map任务大概需要15秒左右来读取和发送数据到sqoop,sqoop到hdfs內建表很快不会有压力这里就不写了
     
  • hive表初次创建同步的时候需要从临时表重建动态分区到正式表,数据跨度太大,一天天重建太耗时间?
    步骤一:数据刚开始同步的时候先不开启转正式表并动态分区,先把全量的数据同步到hive的临时表中,同步完成后,把全量的临时表转到正式表,动态写入分区数据

    步骤二:数据已经全量同步进来了,此时创建crontab任务,定时调用同步脚本,把增量的数据插入到临时表,然后把指定时间的数据转入正式分区,此步骤会把当前id最新的数据同步过来,范围是从上次同步的id到今天最大的id,日期有昨天有今天,这边正式表筛选出昨天的全部数据就行。