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 <mattyb...@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
> <jpolojun...@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 <mattyb...@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
> >> <jpolojun...@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.samp...@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, <
> jpolojun...@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!
> >> >>>
> >> >>>
>
