kettle实现MySQL数据库向hive数据仓库大数据同步经验分享

一、同步性能测试

1、测试环境信息

kettle：版本8.2，carte独立部署，内存参数-Xms1024m -Xmx4096m

mysql数据库：8核CPU，CPU主频2.20GHz，16G内存，版本号5.7.25

大数据环境采用CDH，包含HDFS、hive等组件

CDH集群服务器（共4台）：8核CPU，CPU主频2.20GHz，16G内存，CDH版本5.14.4

2、性能测试结果

全量同步

3000万xxx_log表数据，14个字段，从MySQL数据库同步导入hive仓库，第一次同步耗时290秒，平均10.34万条/秒

第二次同步耗时186秒，平均16.13万条/秒，472天数据按天动态分区，加载到hive分区表耗时179秒

增量同步

3000万xxx_log表历史数据，14个字段，每天100万增量数据，从MySQL数据库按天增量同步到hive仓库，按天静态分区，同步耗时8.689秒，平均11.51万条/秒，加载到hive分区表耗时1.142秒(与同步并行执行)，总耗时8.689秒

二、性能优化点

1、不能直接将MySQL数据库查询出来的数据写入hive仓库，需要先导出到HDFS文件，再通过hive的load命令导入hive仓库。因为hive数据仓库插入数据很慢，插入1条数据可能要10来秒钟；而通过load方式加载HDFS数据文件的方式导入数据，数据量即使有几千万条也只要几百毫秒时间。

2、导出到HDFS文件，字段要选择最小宽度，可以提升几倍性能。因为默认未选择最小宽度，从MySQL中导出的数据存到HDFS文件时每个字段可能会填充空格值使字段长度达到MySQL表结构定义的最大长度值，特别是varchar类型的字段空格很多，这样导出的数据量就很大，都是空格撑的，严重影响同步性能和HDFS存储性能。

3、一般大数据表需要用到hive分区，日志表数据全量数据一般按天动态分区，增量数据按天静态分区。动态分区会用到hive计算引擎，需要对大数据平台做性能优化，比如选择hive on spark执行引擎并进行相应的性能参数优化；静态分区很简单也很快，直接load到指定分区表就可以了。

4、hive动态分区需要优化的配置参数：

在hive配置中调整“Spark 执行程序最大 Java 堆栈大小”参数spark.executor.memory，配置2GB以上

在hive配置中调整“Spark 执行程序内存开销”参数spark.yarn.executor.memoryOverhead，配置2GB以上

5、如果同步到hive的数据表不用分区，增量同步时直接往已有的HDFS文件append方式写入数据就可以了，不用再执行load加载

三、kettle关键配置

在kettle可视化开发工具spoon界面上配置转换或作业

1、数据同步可视化配置：

2、数据库配置

MySQL数据库配置：

需要将MySQL驱动包jar文件传到kettle的lib目录下

hive数据库配置：

多个转换和作业文件可以共享数据库配置，只需在右边主对象树上选择数据库，再右键菜单选择共享

3、CDH插件配置

编辑data-integration/plugins/pentaho-big-data-plugin/plugin.properties配置文件，配置指定Hadoop版本对应的文件夹名，如下：
active.hadoop.configuration=cdh514

将Hadoop master服务器上的配置文件拷贝到对应的文件夹，替换对应配置文件：
hive-site.xml
core-site.xml
hdfs-site.xml
mapred-site.xml
yarn-site.xml

如果core-site.xml里面配置的是主机名，还需修改本地hosts文件配置，加上主机名域名映射配置

kettle配置Hadoop集群，新增集群：

4、上传HDFS配置

在以上界面“内容”标签页，设置分隔符为分号，封闭符为双引号，追加方式、头部、尾部等都不勾选

在“字段”标签页，点击“获取字段”按钮获取字段，再点击“最小宽度”按钮去掉字段左右两端空格

5、导入hive仓库配置

hive已建表不分区配置：

hive动态分区配置：