-
-
辰哥 管理员
发表于2019-12-31 17:15
楼主
最近总收到这样的反馈：“两百万的数据迁移，竟然花了1个多小时啊！”；“抽取的数据量不大，为啥会花这么长时间呢？”；“我想要1000w的数据十秒内跑完，没法达到这要求”。为了解决这个“世纪”难题，我们汲取了众多项目案例经验，并经过不断完善，终于总结提炼出以下几个调优妙计。现在分享给大家，让更多的人去了解，并为以后的项目实施工作拓展新思路。
表输入输出优化
使用表输入+表输出组件数据抽取，明明数据量只有几十万，为什么耗时却要半个小时呢？可能是你忽略了表输出组件中“目标表设置”页面的功能。
对于表输出组件的“更新方式”作用肯定都知道，但是【直接路径插入】、【截断表】、【批量大小】的作用大家知道吗？这三个设置就是数据写入提升效率的关键所在。
直接路径插入 ：直接路径加载把数据直接插入到要修改的段的高水位(HWM)以上，从而生成了最少量的undo（只生成数据字典的undo，不生成块中数据的undo），且不通过高速缓存，因此它的性能比常规插入要好。
截断表：默认情况下，清空表使用delete方式，勾选该选项后使用truncate方式清空表数据，truncate快速清理，但是不可回滚
批量大小：从源库中一条一条的取数据，取出来先存在内存中，存够了之后一次性把这些数据插入到目标库中，如果设置太小就要频繁访问目标数据库，如果太大一直存在内存中可能内存溢出。
最佳设置：勾选【直接路径插入】、【截断表】，批量大小：5000。 设置后，同库oracle数据抽取，可以达到10000条/s，oracle->oracle异库数据抽取，可以达到5000/s，大大提升了运行耗时，如下图设置：
数据分区大数据量抽取
对于这种大表结构的表，可以对库表进行分区，通过每个分区并行执行，可以优化数据抽取耗时比。
例如库表是按月进行分区，那么交换任务中使用：SQL输入+表输出组件，通过【SQL输入】组件将分区中的数据查询出来，最后存入数据库表中，如下了图：
根据不同区分创建多个交换任务，然后在交换任务流中利用分支任务，连接多个交换任务，以实现并行的效果，如下图：
如果表输入+表输出不能满足效率时，可以利用一些数据库自带语法。例如：星环数据库的bulkload、mysql的load data infile等（不同数据库的文本写入命令不同，具体命令试数据库而定）。
可以将数据通过平面输出组件生成文本文件，再利用SQL组件将数据写入，如下图所示：
异构数据库大批量数据迁移
若上面提供的通用方法仍不能解决此问题，继续往下看，针对不同数据库大数据量迁移，提供了相应的组件和方法。
1、其他库->oracle
将大数据写入oracle库，要想高效率写入，可使用oracle装载组件，该组件利用sqlldr命令行工具进行文本数据的快速装载，对于大数据量的处理比insert的效率高很多。具体设置如下：
使用组件：表输入+平面文件输出+oracle装载，库表通过【平面文件输出】将该表数据以文件方式导出，再通过【oracle装载】将数据写入oracle库中。
表输入组件：选择相应库表，然后连接后置组件平面文件输出。
平面文件输出：拾取相应的字段，设置【导出文件路径】（指的服务器所在的路径），【字符集】、【列分隔符】、【文本限定符】、【首行为字段名称】，然后连接后置组件oracle装载。
Oracle装载：
1）文件设置：服务器文件从本地选择，选择的文件第一行必须是字段，列分割符是选择的文件中每一列之间的分割符，文本限定符是某一行某一列的文本被包裹的符号，比如"XX"。这里设置与平面文件输出的文件设置一样即可。
2）字段列表：选择表是将前文选择的服务器文件里的数据插入到选择的表中，
选择不同服务器文件后需要点刷新按钮，刷新映射字段选项，映射字段里的选项就是文件的每个列头名，映射字段添加了每个映射关系不能重复的验证，将数据库表的字段名与文件中的列名一一对应。
3）设置：只有选择加载方式为数据追加时，执行并行加载的勾选框才能勾选，且勾选了执行并行加载之后会默认勾选直接路径方式。
批量大小，缓冲区大小，最大错误数只能输入数字。
结果对比：
写入数据的组件 | 耗时（表情况：42个字段，105w行，297M的数据） |
平面文件输入组件 | 2分37秒 |
oracle装载组件 | 56秒 |
oracle装载组件（直接路径方式） | 13秒 |
注意事项
使用Oracle卸载前需要在产品服务器上安装sqlldr（注意环境变量配置），sqlldr本身不支持连接远程数据库 所以要在安装的时候要装oracle客户端。
1、其他库->greenplum
目前使用表输入组件+表输出组件将数据迁移到Greenplum（以下简称GP）库，效率太低不能满足项目需求,我们可以采用GP库自带的gpload快速装载的方式迁移表数据。
gpload是GP库使用可读外部表和GP并行文件服务系统（gpfdist）装载数据的一个命令包装。其允许我们通过配置控制文件的方式来设置装载数据的格式，然后gpload按照YAML文件格式装载控制文件，创建外部表，执行insert、update、merger等操作将数据装载到目标数据库表中。具体设置如下：
使用组件：表输入+平面文件输出+Greenplum装载，库表通过【平面文件输出】将该表数据以文件方式导出，再通过【greenplum装载】将数据写入GP库中。
表输入组件：选择相应库表，然后连接后置组件平面文件输出。
平面文件输出：拾取相应的字段，设置【导出文件路径】（指的服务器所在的路径），【字符集】、【列分隔符】、【文本限定符】、【首行为字段名称】，然后连接后置组件greenplum装载。
Greenplum卸载：选择GP库表，然后设置【数据文件】（设置成前置组件导出文件的目录，假如有多个TXT文件要导入时，文件名可写成*.txt），【字符集】、【字段分隔符】、【更新方式】等，注意【字符集】、【字段分隔符】设置要与前置组件设置保持一致。
注意事项：
1、使用Greenplum卸载前需要在产品服务器上安装gpload；
安装gpload参考地址：
http://192.168.1.200/wiki/pages/viewpage.action?pageId=399507482
2、需要GP库所有的节点都可以访问文本文件的服务器。因为gpload使用的是gpfdist文件系统，该文件系统会随机将文件发送给某个GP节点
2、其它库->mysql
mysql有一个高效导入方法，那就是load data infile语句，可以将一个文本文件以很高的速度读入表中。具体设置如下：
使用组件：表输入+平面文件输出+SQL组件，库表通过【平面文件输出】输出文件，在利用LOAD DATA语法将数据写入mysql库中。
表输入组件：选择相应的数据库表，连接后置组件平面文件输出。
平面文件输出：拾取相应的字段，设置【导出文件路径】（指的服务器所在的路径），【字符集】、【列分隔符】、【文本限定符】、【首行为字段名称】，然后连接后置组件SQL组件。
SQL组件：选择mysql库，SQL语句：load data infile 'e:\\123.txt' into table tablename fields terminated by ',' lines terminated by '\n'
如果觉得上面的方法不太好，可以看看备选方案。
Mysql备选方案：表输出写入MySQL时通过批量插入语法实现，需同时修改数据库参数
：bulk_insert_buffer_size(默认8M)为100M及以上，批量插入可降低INSERT吞吐量，大大提升插入速度
星环库同样提供了高效写入的语句，具体设置如下：
使用组件：表输入+平面文件输出+shell组件，gp库表数据通过平面文件输出将该表数据以文件方式导出，创建外表，然后利用bulkload将数据写入。
表输入：选择相应的GP库表，连接后置组件平面文件输出。
平面文件输出：设置拾取相应的字段，设置【导出文件路径】（指的服务器所在的路径），【字符集】等，连接后置组件shell组件。
Shell组件：
source /root/TDH-Client/init.sh
kinit -kt /root/TDH-Client/userKeytab/hdfs.keytab hdfs@TDH
hdfs dfs -mkdir -p /file/data1/test/ck_his_all
hdfs dfs -put /home/data1/ck_his_all.txt /file/data1/test/ck_his_all/
echo '开始插入数据'
cd /root/TDH-Client/sql-bulkload-bin
sh SQL-bulkload.sh
数据迁移至星环库也有提供了备选方案。
星环备选方案：
表输出组件已支持写入星环ORC事务表，通过BATCHINSERT语法（非标准SQL语法，主要用于ETL处理）实现，插入速度与单条INSERT相比有着质的飞跃。
最后，再给大家几个小建议，赶紧记下来吧：
1、优化无止境，我们不提倡过度优化，满足项目各项需求即可；
2、多表连接速度较慢时，检查下有无索引或索引是否生效，正确的使用索引，可以提升连接效率；
3、海量数据，建议分区存储，可以减少数据扫描，加快查询、连接等。
如果以上方法都不能解决Etl性能问题，请联系产品二部集成线----关淼苗
