You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by "More, Vikram (CONT)" <Vi...@capitalone.com> on 2017/09/29 22:31:45 UTC
ExecuteSQL question: how do I stop long running queries
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.
Re: ExecuteSQL question: how do I stop long running queries
Posted by Matt Burgess <ma...@gmail.com>.
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 <jf...@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) <Vi...@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) <Vi...@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.
>
Re: ExecuteSQL question: how do I stop long running queries
Posted by Jeremy Farbota <jf...@payoff.com>.
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
[image: Payoff, Inc.]
*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.
>
RE: ExecuteSQL question: how do I stop long running queries
Posted by "More, Vikram (CONT)" <Vi...@capitalone.com>.
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) <Vi...@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.
Re: ExecuteSQL question: how do I stop long running queries
Posted by Matt Burgess <ma...@gmail.com>.
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) <Vi...@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.