Apache Drill Timestampdiff on Oracle DB-CodePudding

Hey everyone im relativly new to Apache Drill and im having troubles converting my Oracle specific sql scripts (pl/sql) to Drill based querys. For example i have a Scripts who checks for processed data in the last X Days.

In this script im using the the sysdate function.

Here is my old script:

SELECT i.id,i.status,status_text,i.kunnr,i.bukrs,i.belnr,i.gjahr,event,i.sndprn,i.createdate,executedate,tstamp,v.typ_text,i.docnum,i.description, i.* 
FROM in_job i JOIN vstatus_injob v ON i.id= v.id
WHERE 1=1 
AND i.createdate > sysdate - 30.5
order by i.createdate desc;

When i looked up in terms of drill specific Datetime Diff functions i found "TIMESTAMPDIFF".

So here is my "drillified" script:

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 TIMESTAMPDIFF(DAY, CURRENT_TIMESTAMP, i.createdate) >=30

And the Error that is returned reads like this:

DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.

By further inspection i can see the Oracle specific error that reads:

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "TIMESTAMPDIFF": invalid ID

So now my question: I thought apache drill replaces the function "TIMSTAMPDIFF" at runtime. But from what i can see in the logs its more like that Drill Hands over the Function Call "TIMESTAMPDIFF" to the Oracle database. If thats true, how could i change my script to calculate the time difference (in days) and compare it to an int (ie 30 in the script).

If i use sysdate like above Apache Drill jumps in and says it doesnt know "sysdate". How would you guyes handle that?

Thanks in advance and so long

CodePudding user response：

I have found a solution...

Just in Case someone (or even me in the future) is having a similar problem.

{
  "queryType": "SQL",
  "query": "select to_char(SELECT CURRENT_TIMESTAMP - INTERVAL XX MONTH FROM (VALUES(1)),'dd.MM.yy')"
}

With some to_char and the use of the CURRENT_TIMESTAMP - Interval Function Calls i can get everything i needed.

I took the query above packed it into an Grafana Variable, named it "timeStmpDiff" and then queried everything with an json Api Call to my Drill instance.

Basically:

"query" : "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 ${Schema}.IN_JOB i JOIN ${Schema}.VSTATUS_INJOB v ON i.id=v.id WHERE i.createdate >= '${timeStmpDiff}' order by i.createdate desc"

You can, of course query it in on go with an subselect. But because i use grafana it made sense to me to bundle that in a Variable.