You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by João Marcos Polo Junior <jp...@gmail.com> on 2023/02/10 15:56:15 UTC
Fwd: PutSQL fragmented transaction error
I’m trying to create a flow (nifi 1.18) to query a database (ExecuteSQL),
transform it records to json (ConvertAvroToJson), split it into json
objects (SplitJson) and then perform the necessary actions into another
database (PutSQL). All json objects splitted from the same original
flowfile needs to be processed in a transaction and for that i’m using a
PutSQL with Fragmented Transactions set it to true.
First problem: I cant set the Transaction Timeout to more than “30 sec”
because my flowfiles (waiting in the upstream queue) dont ever get
processed and dont get to go to the failure connection. They stay stuck in
the upstream connection, get penalized, but never processed or redirected
to failure when the timeout (more than 30sec) reaches the end.
Second problem: I want to combine the Transaction Timeout attribute with
the Penalty Time, Yield Time or maybe Run Schedule but thats not working
either.
Is there a solution for these problem? Is there something I have to
configure in the DBCPConnectionPool for that to work?
Here’s a similar problem in version 1.12:
https://issues.apache.org/jira/browse/NIFI-8733
Thanks in advance!
Re: PutSQL fragmented transaction error
Posted by João Marcos Polo Junior <jp...@gmail.com>.
Thanks a lot for the response and help! That approach is very helpful.
Em seg., 13 de fev. de 2023 às 12:52, Matt Burgess <ma...@apache.org>
escreveu:
> As of Apache NiFi 1.13.0 [1], there are properties in
> PutDatabaseRecord that can be used to specify the operation type as
> well as the location of the "rows" in the input, to support the use
> case of nested/structured input. You can add a field to each record
> specifying the operation type ("insert", "update", "delete") and then
> set Statement Type in PutDatabaseRecord to Use Record Path, then set
> the Statement Type Record Path property to point at the field
> containing the statement type. So for example let's say you have
> "flat" records with fields x,y,stmt_type. You can set Statement Type
> Record Path to /stmt_type and it will check each record for that field
> and use its value as the statement type for handling that
> record/"row".
>
> Please let us know if you have any questions or issues.
>
> Regards,
> Matt
>
> [1] https://issues.apache.org/jira/browse/NIFI-8146
>
> On Fri, Feb 10, 2023 at 1:17 PM João Marcos Polo Junior
> <jp...@gmail.com> wrote:
> >
> > That's great to hear! Im will give it a try. What about performing
> different operations (such as insert for a set of records, updates for
> another set of record and the same for deletes) in a transaction? Is it
> possible with PutDatabaseRecord?
> >
> > Em sex., 10 de fev. de 2023 às 14:29, Matt Burgess <ma...@apache.org>
> escreveu:
> >>
> >> I agree with Chris here about using PutDatabaseRecord instead of the
> >> Split and PutSQL. PutDatabaseRecord will process all records in a
> >> FlowFile as a transaction, so in PutDatabaseRecord you can set an
> >> AvroReader (to read the records coming out of ExecuteSQL) and the
> >> statement type (such as INSERT) and it will process all those records
> >> as one transaction.
> >>
> >> Regards,
> >> Matt
> >>
> >> On Fri, Feb 10, 2023 at 12:04 PM João Marcos Polo Junior
> >> <jp...@gmail.com> wrote:
> >> >
> >> > Thanks for replying it. I'm afraid this approach do not work with
> transaction. How can I process all records in the same transaction?
> >> >
> >> > Em sex., 10 de fev. de 2023 às 13:05, Chris Sampson <
> chris.sampson@naimuri.com> escreveu:
> >> >>
> >> >> I don't use the database/SQL processors much, but see questions
> about these on the Apache NiFi Slack channels quite regularly - you might
> have better look using ExecuteSQLRecord (can output in Avro or JSON, etc.
> using your wanted RecordSetWriter Controller Service) then feed that
> through to a PutDatabaseRecord (can read Avro, JSON, etc. depending upon
> your configured RecordReader).
> >> >>
> >> >> If you want to change the data in between then consider other Record
> based processors such as UpdateRecord or QueryRecord.
> >> >>
> >> >> On Fri, 10 Feb 2023, 15:57 João Marcos Polo Junior, <
> jpolojunior@gmail.com> wrote:
> >> >>>
> >> >>> I’m trying to create a flow (nifi 1.18) to query a database
> (ExecuteSQL), transform it records to json (ConvertAvroToJson), split it
> into json objects (SplitJson) and then perform the necessary actions into
> another database (PutSQL). All json objects splitted from the same original
> flowfile needs to be processed in a transaction and for that i’m using a
> PutSQL with Fragmented Transactions set it to true.
> >> >>>
> >> >>> First problem: I cant set the Transaction Timeout to more than “30
> sec” because my flowfiles (waiting in the upstream queue) dont ever get
> processed and dont get to go to the failure connection. They stay stuck in
> the upstream connection, get penalized, but never processed or redirected
> to failure when the timeout (more than 30sec) reaches the end.
> >> >>>
> >> >>>
> >> >>>
> >> >>> Second problem: I want to combine the Transaction Timeout attribute
> with the Penalty Time, Yield Time or maybe Run Schedule but thats not
> working either.
> >> >>>
> >> >>> Is there a solution for these problem? Is there something I have to
> configure in the DBCPConnectionPool for that to work?
> >> >>>
> >> >>> Here’s a similar problem in version 1.12:
> https://issues.apache.org/jira/browse/NIFI-8733
> >> >>>
> >> >>>
> >> >>>
> >> >>> Thanks in advance!
> >> >>>
> >> >>>
>
Re: PutSQL fragmented transaction error
Posted by Matt Burgess <ma...@apache.org>.
As of Apache NiFi 1.13.0 [1], there are properties in
PutDatabaseRecord that can be used to specify the operation type as
well as the location of the "rows" in the input, to support the use
case of nested/structured input. You can add a field to each record
specifying the operation type ("insert", "update", "delete") and then
set Statement Type in PutDatabaseRecord to Use Record Path, then set
the Statement Type Record Path property to point at the field
containing the statement type. So for example let's say you have
"flat" records with fields x,y,stmt_type. You can set Statement Type
Record Path to /stmt_type and it will check each record for that field
and use its value as the statement type for handling that
record/"row".
Please let us know if you have any questions or issues.
Regards,
Matt
[1] https://issues.apache.org/jira/browse/NIFI-8146
On Fri, Feb 10, 2023 at 1:17 PM João Marcos Polo Junior
<jp...@gmail.com> wrote:
>
> That's great to hear! Im will give it a try. What about performing different operations (such as insert for a set of records, updates for another set of record and the same for deletes) in a transaction? Is it possible with PutDatabaseRecord?
>
> Em sex., 10 de fev. de 2023 às 14:29, Matt Burgess <ma...@apache.org> escreveu:
>>
>> I agree with Chris here about using PutDatabaseRecord instead of the
>> Split and PutSQL. PutDatabaseRecord will process all records in a
>> FlowFile as a transaction, so in PutDatabaseRecord you can set an
>> AvroReader (to read the records coming out of ExecuteSQL) and the
>> statement type (such as INSERT) and it will process all those records
>> as one transaction.
>>
>> Regards,
>> Matt
>>
>> On Fri, Feb 10, 2023 at 12:04 PM João Marcos Polo Junior
>> <jp...@gmail.com> wrote:
>> >
>> > Thanks for replying it. I'm afraid this approach do not work with transaction. How can I process all records in the same transaction?
>> >
>> > Em sex., 10 de fev. de 2023 às 13:05, Chris Sampson <ch...@naimuri.com> escreveu:
>> >>
>> >> I don't use the database/SQL processors much, but see questions about these on the Apache NiFi Slack channels quite regularly - you might have better look using ExecuteSQLRecord (can output in Avro or JSON, etc. using your wanted RecordSetWriter Controller Service) then feed that through to a PutDatabaseRecord (can read Avro, JSON, etc. depending upon your configured RecordReader).
>> >>
>> >> If you want to change the data in between then consider other Record based processors such as UpdateRecord or QueryRecord.
>> >>
>> >> On Fri, 10 Feb 2023, 15:57 João Marcos Polo Junior, <jp...@gmail.com> wrote:
>> >>>
>> >>> I’m trying to create a flow (nifi 1.18) to query a database (ExecuteSQL), transform it records to json (ConvertAvroToJson), split it into json objects (SplitJson) and then perform the necessary actions into another database (PutSQL). All json objects splitted from the same original flowfile needs to be processed in a transaction and for that i’m using a PutSQL with Fragmented Transactions set it to true.
>> >>>
>> >>> First problem: I cant set the Transaction Timeout to more than “30 sec” because my flowfiles (waiting in the upstream queue) dont ever get processed and dont get to go to the failure connection. They stay stuck in the upstream connection, get penalized, but never processed or redirected to failure when the timeout (more than 30sec) reaches the end.
>> >>>
>> >>>
>> >>>
>> >>> Second problem: I want to combine the Transaction Timeout attribute with the Penalty Time, Yield Time or maybe Run Schedule but thats not working either.
>> >>>
>> >>> Is there a solution for these problem? Is there something I have to configure in the DBCPConnectionPool for that to work?
>> >>>
>> >>> Here’s a similar problem in version 1.12: https://issues.apache.org/jira/browse/NIFI-8733
>> >>>
>> >>>
>> >>>
>> >>> Thanks in advance!
>> >>>
>> >>>
Re: PutSQL fragmented transaction error
Posted by João Marcos Polo Junior <jp...@gmail.com>.
That's great to hear! Im will give it a try. What about performing
different operations (such as insert for a set of records, updates for
another set of record and the same for deletes) in a transaction? Is it
possible with PutDatabaseRecord?
Em sex., 10 de fev. de 2023 às 14:29, Matt Burgess <ma...@apache.org>
escreveu:
> I agree with Chris here about using PutDatabaseRecord instead of the
> Split and PutSQL. PutDatabaseRecord will process all records in a
> FlowFile as a transaction, so in PutDatabaseRecord you can set an
> AvroReader (to read the records coming out of ExecuteSQL) and the
> statement type (such as INSERT) and it will process all those records
> as one transaction.
>
> Regards,
> Matt
>
> On Fri, Feb 10, 2023 at 12:04 PM João Marcos Polo Junior
> <jp...@gmail.com> wrote:
> >
> > Thanks for replying it. I'm afraid this approach do not work with
> transaction. How can I process all records in the same transaction?
> >
> > Em sex., 10 de fev. de 2023 às 13:05, Chris Sampson <
> chris.sampson@naimuri.com> escreveu:
> >>
> >> I don't use the database/SQL processors much, but see questions about
> these on the Apache NiFi Slack channels quite regularly - you might have
> better look using ExecuteSQLRecord (can output in Avro or JSON, etc. using
> your wanted RecordSetWriter Controller Service) then feed that through to a
> PutDatabaseRecord (can read Avro, JSON, etc. depending upon your configured
> RecordReader).
> >>
> >> If you want to change the data in between then consider other Record
> based processors such as UpdateRecord or QueryRecord.
> >>
> >> On Fri, 10 Feb 2023, 15:57 João Marcos Polo Junior, <
> jpolojunior@gmail.com> wrote:
> >>>
> >>> I’m trying to create a flow (nifi 1.18) to query a database
> (ExecuteSQL), transform it records to json (ConvertAvroToJson), split it
> into json objects (SplitJson) and then perform the necessary actions into
> another database (PutSQL). All json objects splitted from the same original
> flowfile needs to be processed in a transaction and for that i’m using a
> PutSQL with Fragmented Transactions set it to true.
> >>>
> >>> First problem: I cant set the Transaction Timeout to more than “30
> sec” because my flowfiles (waiting in the upstream queue) dont ever get
> processed and dont get to go to the failure connection. They stay stuck in
> the upstream connection, get penalized, but never processed or redirected
> to failure when the timeout (more than 30sec) reaches the end.
> >>>
> >>>
> >>>
> >>> Second problem: I want to combine the Transaction Timeout attribute
> with the Penalty Time, Yield Time or maybe Run Schedule but thats not
> working either.
> >>>
> >>> Is there a solution for these problem? Is there something I have to
> configure in the DBCPConnectionPool for that to work?
> >>>
> >>> Here’s a similar problem in version 1.12:
> https://issues.apache.org/jira/browse/NIFI-8733
> >>>
> >>>
> >>>
> >>> Thanks in advance!
> >>>
> >>>
>
Re: PutSQL fragmented transaction error
Posted by Matt Burgess <ma...@apache.org>.
I agree with Chris here about using PutDatabaseRecord instead of the
Split and PutSQL. PutDatabaseRecord will process all records in a
FlowFile as a transaction, so in PutDatabaseRecord you can set an
AvroReader (to read the records coming out of ExecuteSQL) and the
statement type (such as INSERT) and it will process all those records
as one transaction.
Regards,
Matt
On Fri, Feb 10, 2023 at 12:04 PM João Marcos Polo Junior
<jp...@gmail.com> wrote:
>
> Thanks for replying it. I'm afraid this approach do not work with transaction. How can I process all records in the same transaction?
>
> Em sex., 10 de fev. de 2023 às 13:05, Chris Sampson <ch...@naimuri.com> escreveu:
>>
>> I don't use the database/SQL processors much, but see questions about these on the Apache NiFi Slack channels quite regularly - you might have better look using ExecuteSQLRecord (can output in Avro or JSON, etc. using your wanted RecordSetWriter Controller Service) then feed that through to a PutDatabaseRecord (can read Avro, JSON, etc. depending upon your configured RecordReader).
>>
>> If you want to change the data in between then consider other Record based processors such as UpdateRecord or QueryRecord.
>>
>> On Fri, 10 Feb 2023, 15:57 João Marcos Polo Junior, <jp...@gmail.com> wrote:
>>>
>>> I’m trying to create a flow (nifi 1.18) to query a database (ExecuteSQL), transform it records to json (ConvertAvroToJson), split it into json objects (SplitJson) and then perform the necessary actions into another database (PutSQL). All json objects splitted from the same original flowfile needs to be processed in a transaction and for that i’m using a PutSQL with Fragmented Transactions set it to true.
>>>
>>> First problem: I cant set the Transaction Timeout to more than “30 sec” because my flowfiles (waiting in the upstream queue) dont ever get processed and dont get to go to the failure connection. They stay stuck in the upstream connection, get penalized, but never processed or redirected to failure when the timeout (more than 30sec) reaches the end.
>>>
>>>
>>>
>>> Second problem: I want to combine the Transaction Timeout attribute with the Penalty Time, Yield Time or maybe Run Schedule but thats not working either.
>>>
>>> Is there a solution for these problem? Is there something I have to configure in the DBCPConnectionPool for that to work?
>>>
>>> Here’s a similar problem in version 1.12: https://issues.apache.org/jira/browse/NIFI-8733
>>>
>>>
>>>
>>> Thanks in advance!
>>>
>>>
Re: PutSQL fragmented transaction error
Posted by João Marcos Polo Junior <jp...@gmail.com>.
Thanks for replying it. I'm afraid this approach do not work with
transaction. How can I process all records in the same transaction?
Em sex., 10 de fev. de 2023 às 13:05, Chris Sampson <
chris.sampson@naimuri.com> escreveu:
> I don't use the database/SQL processors much, but see questions about
> these on the Apache NiFi Slack channels quite regularly - you might have
> better look using ExecuteSQLRecord (can output in Avro or JSON, etc. using
> your wanted RecordSetWriter Controller Service) then feed that through to a
> PutDatabaseRecord (can read Avro, JSON, etc. depending upon your configured
> RecordReader).
>
> If you want to change the data in between then consider other Record based
> processors such as UpdateRecord or QueryRecord.
>
> On Fri, 10 Feb 2023, 15:57 João Marcos Polo Junior, <jp...@gmail.com>
> wrote:
>
>> I’m trying to create a flow (nifi 1.18) to query a database (ExecuteSQL),
>> transform it records to json (ConvertAvroToJson), split it into json
>> objects (SplitJson) and then perform the necessary actions into another
>> database (PutSQL). All json objects splitted from the same original
>> flowfile needs to be processed in a transaction and for that i’m using a
>> PutSQL with Fragmented Transactions set it to true.
>>
>> First problem: I cant set the Transaction Timeout to more than “30 sec”
>> because my flowfiles (waiting in the upstream queue) dont ever get
>> processed and dont get to go to the failure connection. They stay stuck in
>> the upstream connection, get penalized, but never processed or redirected
>> to failure when the timeout (more than 30sec) reaches the end.
>>
>>
>>
>> Second problem: I want to combine the Transaction Timeout attribute with
>> the Penalty Time, Yield Time or maybe Run Schedule but thats not working
>> either.
>>
>> Is there a solution for these problem? Is there something I have to
>> configure in the DBCPConnectionPool for that to work?
>>
>> Here’s a similar problem in version 1.12:
>> https://issues.apache.org/jira/browse/NIFI-8733
>>
>>
>>
>> Thanks in advance!
>>
>>
>>
>
Re: PutSQL fragmented transaction error
Posted by Chris Sampson <ch...@naimuri.com>.
I don't use the database/SQL processors much, but see questions about these
on the Apache NiFi Slack channels quite regularly - you might have better
look using ExecuteSQLRecord (can output in Avro or JSON, etc. using your
wanted RecordSetWriter Controller Service) then feed that through to a
PutDatabaseRecord (can read Avro, JSON, etc. depending upon your configured
RecordReader).
If you want to change the data in between then consider other Record based
processors such as UpdateRecord or QueryRecord.
On Fri, 10 Feb 2023, 15:57 João Marcos Polo Junior, <jp...@gmail.com>
wrote:
> I’m trying to create a flow (nifi 1.18) to query a database (ExecuteSQL),
> transform it records to json (ConvertAvroToJson), split it into json
> objects (SplitJson) and then perform the necessary actions into another
> database (PutSQL). All json objects splitted from the same original
> flowfile needs to be processed in a transaction and for that i’m using a
> PutSQL with Fragmented Transactions set it to true.
>
> First problem: I cant set the Transaction Timeout to more than “30 sec”
> because my flowfiles (waiting in the upstream queue) dont ever get
> processed and dont get to go to the failure connection. They stay stuck in
> the upstream connection, get penalized, but never processed or redirected
> to failure when the timeout (more than 30sec) reaches the end.
>
>
>
> Second problem: I want to combine the Transaction Timeout attribute with
> the Penalty Time, Yield Time or maybe Run Schedule but thats not working
> either.
>
> Is there a solution for these problem? Is there something I have to
> configure in the DBCPConnectionPool for that to work?
>
> Here’s a similar problem in version 1.12:
> https://issues.apache.org/jira/browse/NIFI-8733
>
>
>
> Thanks in advance!
>
>
>