【kettle & postgresql】temporary file size exceeds temp_file_limit解决办法
使用kettle从postgresql拉取数据时,可能会因为数据量过大,出现“ERROR: temporary file size exceeds temp_file_limit(16777216kB)”的报错
这个临时文件大小限制是postgresql的参数temp_file_limit设置的(参考:PostgreSQL配置参数temp_file_limit_wx5bcd90b347f01的技术博客_51CTO博客),其参数含义是
指定一个进程可以占用的最大的磁盘空间。该磁盘空间可以用于temporary files(sort及hash使用)或者持有cursor的storage file。试图超过该限制的transaction会被取消。
在没有权限修改此参数的情况下,如果从pg取数的SQL只是简单的select ... from ... where 语句,我们可以通过设置【文本文件输出】中的“分拆...每一行”设置每个文件放多少行数据,解决临时文件过大的问题。
为何此种方式能生效,可参考
数据库游标及其优点 - 苏晓敏 - 博客园 (cnblogs.com)
(89条消息) mysql的查询原理_数据库SQL SELECT查询的工作原理_曹阳明的博客-CSDN博客
问题的关键点在生成的【物理存储文件】,通过设置每个文件的存储行数,即将结果进行了切分后,物理存储文件(temp_file_limit)没有超过限制,那么有很大一个可能是这种情况下kettle对select添加了LIMIT offset,row_count的处理
SELECT * FROM xxx LIMIT OFFSET,ROW_COUNT
(待求证这种方式是否会进行执行计划优化)
但是当取数的SQL中存在join,那此方法就不管用了,要取得有join操作的sql的结果,必须获取到整个sql的结果然后取偏移的数据,但是输出结果的时候就超出了临时文件大小限制,任务就失败了,解决办法就是增加where条件,或者缩小where条件取数的数量,使得结果不超出限制。比如原来时间范围是1年,现在改为1个月,或者采用更精确的做法:根据数据表每行数据占用的存储空间来估计最大的行数,从而设置where条件。
下图为这种处理方式的一个示例,airflow任务会调用kettle取数脚本,通过将取数时间分段拆分成多个任务(缩小where条件的能获取到的数据量),解决了全量取数时数据量超出pg临时文件大小限制的问题。