oracle 迁移到clickhouse 45亿条数据

原文链接：http://www.520mwx.com/view/90469

45亿数据迁移记录

• 背景

  • 数据库数据量日益增加，逐渐开始显得很是臃肿，日常查询统计的时候，仅仅是count(1) 查询下总数，耗费的时间也在500s左右，而且之前的orcle数据库，前期建立的时候，也未考虑太多，未进行索引，分表，等优化。后面鉴于种种考虑，以及后期的规划，准备将数据迁移至clickhouse(oracle -> clickhouse)。
  • clickhouse 相关背景
    • ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。
    • 相关介绍
  • 迁移相关考虑
    • 数据库现有数据45亿
    • 每天还有新数据持续入库-数据量 3000万/天

• 资源

  • 服务器资源 CPU 16C；内存 48G；硬盘 500G磁盘左右，总共3台

• 准备

  • 开始查看相关的数据迁移工具
    • datax 阿里推出的一个，但是相对而言，社区不是很活跃。相关链接
    • waterdrop 社区活跃，作者很用心。再次感谢在使用过程中给与我的帮助 相关链接

• 过程-时间-思考

  1. 选择方式，与迁移工具

  ---

  • 首先，选择截断数据日期，进行之前的数据迁移。
  • 最开始看的datax 的相关，这里说下datax的clickhouse官方还没提供指定的插件，所有无法直接到达clickhouse。
  • 尝试 oracle -> datax -> mysql -> clickhouse 使用 clickhouse 的insert into ,clickhouse 对mysql 有很好的支持。
    • 尝试失败，mysql 数据迁移过去以后，一旦到达亿级别，数据库统计无结果。考虑过，使用datax分任务，分表，但是数据已经45亿，分表量太小。花费时间过长。
  • 尝试 oracle -> datax -> hdfs -> waterdrop -> clickhouse 尝试使用 datax 迁移数据到hdfs,然后使用 waterdrop 在把数据从 hdfs 到 clickhouse 。
    • 这个是可行的，但是在这期间，我在github 上面发现了其他网友扩展的datax 的 clickhouse 的插件，毕竟这个方案还需要 hdfs 做一步的中转。
  • 尝试 oracle -> datax -> clickhouse
    • 在网上查看相关文档，发现有朋友在github 提供了 datax 的 clickhouse 的插件。参考地址
    • 尝试相关插件，发现可行。

  ---

  2. clickhouse 建表，查看相关优化

  ---

  • 了解 clickhouse 的相关表引擎，为迁移建表准备。之前的文档有相关说明。
    • 着重了解了我们需要使用的表引擎，最后选定引擎 MergeTree(合并树引擎) Distributed(分布式引擎)
    • MergeTree 后续需要持续落入数据。并且对数据按照时间进行合并，优化效率。参考链接
    • Distributed clickhouse 公司这边建立的是集群，而且分布式引擎，可以并行处理数据，最后在总表会合，效率高。参考链接

  ---

  3. 准备shell脚本，依次执行脚本，顺序执行

  ---

  • 这里需要说明，为什么要依次执行？
    • datax 那里可以进行份job，一起执行，但是我这里，没有选择并发处理多任务。前期测试的时候，我这边设置了datax 的一个优化参数线程数 channel 为15 ，同时执行多个任务。但是这样大数据量并发读写操作，给oracle 的服务器，以及迁移的服务器带来了几个问题。
      • 多个任务同时读取oracle ，导致oracle那边出现了阻塞现象，有的任务已经开始拉取，有的任务迟迟不见开始。
      • 过多的任务，启动过多的线程，导致linux迁移的服务器最后经常出现cpu 锁死，任务极度不稳定，进行奔溃，迁移失败。
  • datax 支持where 条件，准备sql 的where 条件，进行数据库的 用创建时间进行分割数据，使每个任务的job 拉取数据量保持平衡，过滤一部分无效数据。

  ---

  4. 建立临时表，每个任务的时间分区，对应一个datax 的配置。防止数据拉取过程中某个任务失败。

  ---

  • 我这边建立clickhouse 的临时job表，建立了14个， 每个集群所在的节点，是7个，每个datax 在两台服务器分别顺序执行7个任务。
  • 中间会有任务失败，所有最后拉取完毕以后，查看每个job 的日志，失败的，进行清表，二次拉取。
  • 全部成功以后，使用 clickhouse 的 insert into 插入之前的建立的正式表，进行数据合并，至此45亿数据拉取成功。

• 总结

  • 迁移过程中，主要耗费时间在前期的工具，以及方案备选，因为之前没做过此类事情，所以查看每个工具，而后选择测试的方案也不相同。

• linux 脚本

echo '开始释放缓存'

echo 3 > /proc/sys/vm/drop_caches

echo '缓存清理完毕，执行第4次任务'

python2 /home/datax/bin/datax.py --jvm="-Xms3G -Xmx3G" /home/datax/job/job_clickhouse4.json >/home/datax/log/job_clickhouse4.log 2>&1

echo '第4次任务执行完毕'

echo '开始睡眠'

sleep 1h

echo '结束睡眠'

echo '开始释放缓存'

echo 3 > /proc/sys/vm/drop_caches

echo '缓存清理完毕，执行第5次任务'

python2 /home/datax/bin/datax.py --jvm="-Xms3G -Xmx3G" /home/datax/job/job_clickhouse5.json >/home/datax/log/job_clickhouse5.log 2>&1

echo '第5次任务执行完毕'

job 与 临时表 后面添加后缀 _数字 以方便区分。与任务日志对应。 而这里的睡眠是因为，clickhosue 拉取数据以后，就会整理文件，整理文件会耗费内存以及性能，进行睡眠，先让他进行整理。

datax的多job 

clickhouse 的分表