Hello. Thanks for the answer. With no luck, I looked at generatefetchtable, but it shares the same code. It’s too bad as it comes with handy features to avoid overloading the database when you have to fetch huge tables (as I’m in oracle 11g, the rownum trick to limit result returned by database seems awful).
Anyway, I’ll try to figure it out . Thanks. Aurélien DEHAY Big Data Architect +33 616 815 441 [email protected] 2 rue Hennape - 92735 Nanterre Cedex – France [Faurecia_inspiring_mobility_logo-RVB_150] From: Boris Tyukin [mailto:[email protected]] Sent: mardi 24 avril 2018 17:04 To: [email protected] Subject: Re: QueryDatabaseTable date/timestamp and Oracle. IMHO A lot of stuff with Oracle requires custom tuning and unfortunately, a lot of generic processors build to serve all JDBC databases would not have knowledge of Oracle intricacies. I found out the same thing with Kafka JDBC connector that did crazy wrong stuff with data coming from Oracle. My solution was to build my own pipeline, using ExecuteSQL and persisting timestamps in MySql table on a side. You will have more control that way and you can add your own audit and logging. It was quite easy with NiFi and I was in full control of Oracle queries, executed by NiFi. Boris On Tue, Apr 24, 2018 at 6:04 AM, DEHAY Aurelien <[email protected]<mailto:[email protected]>> wrote: Hello. I'm using Oracle 11g with nifi 1.6.0. I'm trying to use QueryDatabaseTable to fetch newly modified/created rows from a table with a few million records. The field I use as maximum value column is "sysmodtime", a DATE field. When I put something like 2018-04-23 00:00:00.0 in "initial.maxvalue.sysmodtime", the query times out. In a sql command line, it takes several minutes to complete. The query launched with nifi use as where clause: sysmodtime > timestamp '2018-04-23 00:00:00.0'. With to_date, like in "select count(*) from hpsm.probsummarym1 where sysmodtime > to_date('2016-01-01 00:00:00', 'YYYY-MM-DD HH24;MI:SS')", it takes seconds. The explanation seems about conversion between DATE type to TIMESTAMP type for every rows, which obviously takes time. Is there a workaround? I saw https://issues.apache.org/jira/browse/NIFI-2323 but it does not really helps. Thanks for any answer/pointer. Aurélien DEHAY Big Data Architect +33 616 815 441 [email protected]<mailto:[email protected]> 2 rue Hennape - 92735 Nanterre Cedex - France This electronic transmission (and any attachments thereto) is intended solely for the use of the addressee(s). It may contain confidential or legally privileged information. If you are not the intended recipient of this message, you must delete it immediately and notify the sender. Any unauthorized use or disclosure of this message is strictly prohibited. Faurecia does not guarantee the integrity of this transmission and shall therefore never be liable if the message is altered or falsified nor for any virus, interception or damage to your system. This electronic transmission (and any attachments thereto) is intended solely for the use of the addressee(s). It may contain confidential or legally privileged information. If you are not the intended recipient of this message, you must delete it immediately and notify the sender. Any unauthorized use or disclosure of this message is strictly prohibited. Faurecia does not guarantee the integrity of this transmission and shall therefore never be liable if the message is altered or falsified nor for any virus, interception or damage to your system.
