nifi-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Matt Burgess <mattyb...@gmail.com>
Subject Re: ExecuteSQL question: how do I stop long running queries
Date Sat, 30 Sep 2017 00:11:59 GMT
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 <jfarbota@payoff.com> 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.
> 
> jfarbota@payoff.com
> 
> 
>> On Fri, Sep 29, 2017 at 4:13 PM, More, Vikram (CONT) <Vikram.More@capitalone.com>
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:mattyb149@gmail.com] 
>> Sent: Friday, September 29, 2017 6:48 PM
>> To: users@nifi.apache.org
>> 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) <Vikram.More@capitalone.com>
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.
> 

Mime
View raw message