Jeremy, Your best bet from the client side (especially if you kill the connection at the DB) is to provide a Validation Query in your DBCPConnectionPool (I can't remember when that feature became available). Some drivers are notorious for not noticing a connection has been severed, and that connection may be reused when it should not be. The Validation Query seems to alleviate this problem.
Vikram, If you are doing incremental fetches then QueryDatabaseTable and GenerateTableFetch should help you get there, that is exactly their intended purpose. The former will generate SQL to do incremental fetches and then execute those statement(s), where the latter simply generates the SQL, and you can send the statements downstream to an ExecuteSQL processor. Definitely agree with Jeremy that the latest is best when it comes to NiFi versions and RDBMS processors, the community is continually working hard on improving the existing processors as well as adding new features to support various RDBMS use cases. Regards, Matt > On Sep 29, 2017, at 7:59 PM, Jeremy Farbota <[email protected]> wrote: > > Vikram, > > From my experience, the later version of NiFi the better for DB operations. > > For long running queries, I kill the process on the db side. > > If NiFi has a processor hung up (in general), we've not found a way to kill > it other than restarting the service. > > Would like to know if there's a better way. Recently had some PutSQL > processor (1.3.0) that hung up after I killed it on the db side in prod. Had > to restart the service which was a bit of a paid in prod. > > Kindly, > > Jeremy > > > Jeremy Farbota > Software Engineer, Data > Payoff, Inc. > > [email protected] > > >> On Fri, Sep 29, 2017 at 4:13 PM, More, Vikram (CONT) >> <[email protected]> wrote: >> Thanks Matt for quick response. We are running simple queries (without any >> joins or sub-query). For use case I am working, we have to get all rows >> (kind of history) from source table as an initial one time fetch and then >> later do incremental fetch . Haven’t tried out QueryDatabaseTable and >> GenerateTableFetch , will check how they work. >> >> Although for some of the source tables, we have to fetch all rows on daily >> basis. ? >> >> >> >> Which version of NiFi would work best for RDBMS processors, I’ll check with >> platform folks if we can go version upgrade. >> >> >> >> Thanks, Appreciate your help >> >> >> >> From: Matt Burgess [mailto:[email protected]] >> Sent: Friday, September 29, 2017 6:48 PM >> To: [email protected] >> Subject: Re: ExecuteSQL question: how do I stop long running queries >> >> >> >> Vikram, >> >> >> >> I'm not at my computer right now so I'm shooting from the hip, but depending >> on how complex your query is (meaning if it is very simple), take a look at >> QueryDatabaseTable and GenerateTableFetch, if you are looking to get all >> rows (versus incremental fetching), you can omit the maximum value column >> and they act very much like ExecuteSQL. Having said that, even if these are >> a better choice for your use case, I highly recommend upgrading your NiFi >> version if possible, as there have been many improvements to all the RDBMS >> processors in order to help handle use cases like yours. >> >> >> >> Regards, >> >> Matt >> >> >> >> >> On Sep 29, 2017, at 6:31 PM, More, Vikram (CONT) >> <[email protected]> wrote: >> >> Hi , >> >> >> >> I am using ExecuteSQL processor to pull from operational database and for >> some of the tables it keeps running for more than 24 hrs, >> >> >> >> 1] During a long-running query from a database (e.g. Oracle) being execute >> by an 'ExecuteSql' process, is there a way to check the progress - say by >> seeing files, row counts, or whatever? We have some queries that take a >> while and can't tell if a process is a success until it succeeds. >> >> >> >> 2] Even after I stop the ExecuteSQL processor, it still continues to run. I >> have to stop and restart nifi service to kill/terminate the query session or >> follow-up the dba’s to kill the session from user I am querying. Can I find >> the session in nifi and terminate it? >> >> >> >> I am using NiFi version – 1.1.0 , any suggestion would be appreciated >> >> >> >> Thanks & Regards, >> >> Vikram More >> >> >> >> >> >> The information contained in this e-mail is confidential and/or proprietary >> to Capital One and/or its affiliates and may only be used solely in >> performance of work or services for Capital One. The information transmitted >> herewith is intended only for use by the individual or entity to which it is >> addressed. If the reader of this message is not the intended recipient, you >> are hereby notified that any review, retransmission, dissemination, >> distribution, copying or other use of, or taking of any action in reliance >> upon this information is strictly prohibited. If you have received this >> communication in error, please contact the sender and delete the material >> from your computer. >> >> The information contained in this e-mail is confidential and/or proprietary >> to Capital One and/or its affiliates and may only be used solely in >> performance of work or services for Capital One. The information transmitted >> herewith is intended only for use by the individual or entity to which it is >> addressed. If the reader of this message is not the intended recipient, you >> are hereby notified that any review, retransmission, dissemination, >> distribution, copying or other use of, or taking of any action in reliance >> upon this information is strictly prohibited. If you have received this >> communication in error, please contact the sender and delete the material >> from your computer. >
