Apache Drill查询Oracle时TIMESTAMPDIFF等日期函数报错如何解决

问题根因 #

Drill的JDBC存储插件默认会将查询中的函数、过滤条件尽可能下推到后端数据源（此处为Oracle）执行，以提升查询性能，但Drill不会自动做跨数据源的函数语法转换：

• 你使用的Drill专属TIMESTAMPDIFF函数Oracle原生不支持，直接下发会报ORA-00904错误
• Oracle原生的SYSDATE函数无法通过Drill的SQL语法校验，直接写会被Drill拒绝执行

解决方案 #

推荐两种适配方案，可根据你的场景选择：

方案1：复用原有Oracle逻辑（性能最优，推荐） #

使用Drill提供的sql_expr()函数包裹Oracle原生语法，Drill不会解析该函数内的内容，会直接将表达式下发给Oracle执行，完全兼容你原有的PL/SQL逻辑，过滤操作全部在Oracle侧完成，性能最优。

修改后的查询示例：

SELECT i.id, i.status, status_text, i.kunnr, i.bukrs, i.belnr, i.gjahr, i.event, i.sndprn, i.createdate, i.executedate, i.tstamp,v.typ_text,i.docnum,i.description,i.* 
FROM SchemaNAME.IN_JOB i JOIN SchemaNAME.VSTATUS_INJOB v ON i.id=v.id 
WHERE sql_expr("i.createdate > SYSDATE - 30.5")
ORDER BY i.createdate DESC;

方案2：使用标准SQL语法（跨数据源兼容性好） #

如果你不想写数据源专属语法，可以先在Drill侧计算出时间阈值，再和日期字段做比较，Drill会先将CURRENT_TIMESTAMP - 30.5 * INTERVAL '1' DAY解析为固定时间值再下发给Oracle，不会涉及双方不识别的函数，适配性更强。

修改后的查询示例：

SELECT i.id, i.status, status_text, i.kunnr, i.bukrs, i.belnr, i.gjahr, i.event, i.sndprn, i.createdate, i.executedate, i.tstamp,v.typ_text,i.docnum,i.description,i.* 
FROM SchemaNAME.IN_JOB i JOIN SchemaNAME.VSTATUS_INJOB v ON i.id=v.id 
-- 如需筛选最近30.5天数据，直接用Drill侧计算的时间阈值比较即可
WHERE i.createdate > CURRENT_TIMESTAMP - 30.5 * INTERVAL '1' DAY
ORDER BY i.createdate DESC;

补充说明：你之前使用的TIMESTAMPDIFF写法存在参数顺序错误，标准语法为TIMESTAMPDIFF(单位, 较早的时间, 较晚的时间)，如需获取当前时间与创建时间的天数差，正确写法为TIMESTAMPDIFF(DAY, i.createdate, CURRENT_TIMESTAMP) >=30，但该写法依然会因为Oracle不支持TIMESTAMPDIFF函数报错，因此不推荐使用。

内容的提问来源于stack exchange，提问作者Christoph Schüpfer