You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by fredaffini <fr...@gmail.com> on 2019/02/26 13:30:42 UTC
Log Queries being executed by PutDatabaseRecord
Hello,
Iam new to NiFi and still learning. I would like to know if there is a way
to log the individual queries being executed against the database by
PutDatabaseRecord processor.
How can I accomplish that?
Regards,
Fred
--
Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/
Re: Log Queries being executed by PutDatabaseRecord
Posted by Andrew Grande <ap...@gmail.com>.
Could ve a good idea to log values at TRACE level then.
On Wed, Feb 27, 2019, 7:56 AM Matt Burgess <ma...@apache.org> wrote:
> True, at a DEBUG level we could output the record values, although for
> large flow files this will be quite verbose :) Also the point of the
> ?s is not necessarily to not show the values, but that we are
> technically only issuing one statement (i.e. PreparedStatement), and
> just the values change. This usually gives much better performance,
> and also when we execute them as a batch, we get more of a
> transactional capability (one flow file = one batch = one
> transaction).
>
> Please feel free to file a Jira to add record output to
> PutDatabaseRecord at a DEBUG level.
>
> Regards,
> Matt
>
> On Wed, Feb 27, 2019 at 7:05 AM Mike Thomsen <mi...@gmail.com>
> wrote:
> >
> > I could be mistaken, but I think that's standard JDBC behavior to not
> show the values. That said, yes it would be a fairly trivial improvement to
> add a dump of the record to a debug logger.
> >
> > On Wed, Feb 27, 2019 at 4:36 AM Fred Affini <fr...@gmail.com>
> wrote:
> >>
> >> Hi Matt and Phillip, thanks a lot for the help
> >>
> >> Setting PutDatabaseRecord log level to DEBUG (changing Bulletin Level
> in the
> >> GUI or insert the XML line Matt sent) almost gave me what I need, the
> log
> >> nos show:
> >>
> >> 10:32:55 CETDEBUGadcd1a7d-1000-1169-8aa8-92d8f2e891e5
> >> PutDatabaseRecord[id=adcd1a7d-1000-1169-8aa8-92d8f2e891e5] Executing
> query
> >> INSERT INTO SYSTEM.NIFI_INPUT_TEST (ID_BB_GLOBAL, BLOOMBERG_CODE,
> ID_ISIN,
> >> ID_CUSIP, ID_SEDOL, TICKER, ID_BB_COMPANY, ID_GRID, CRNCY, INSTYP,
> ISSUE_DT,
> >> CPN_TYP, CPN, MTY_TYP, MATURITY, WORKOUT_DT_BID) VALUES
> >> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); fieldIndexes: [0, 1, 2, 3, 4, 5, 6,
> 7, 8,
> >> 9, 10, 11, 12, 13, 14, 15]; batch index: 1; batch size: 1499
> >>
> >> I think the log should put the values in the parameters, looks like it
> is
> >> being logged before preparing the statement. I still have a way to
> figure it
> >> out, since it gives me the field index and I suppose that: *batch
> index: 1;
> >> batch size: 1499* means the first line of my file (that has 1499 lines)
> >>
> >> Should this be a request for improvement in the processor?
> >>
> >> Regards,
> >> Fred
> >>
> >>
> >>
> >> --
> >> Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/
>
Re: Log Queries being executed by PutDatabaseRecord
Posted by Matt Burgess <ma...@apache.org>.
True, at a DEBUG level we could output the record values, although for
large flow files this will be quite verbose :) Also the point of the
?s is not necessarily to not show the values, but that we are
technically only issuing one statement (i.e. PreparedStatement), and
just the values change. This usually gives much better performance,
and also when we execute them as a batch, we get more of a
transactional capability (one flow file = one batch = one
transaction).
Please feel free to file a Jira to add record output to
PutDatabaseRecord at a DEBUG level.
Regards,
Matt
On Wed, Feb 27, 2019 at 7:05 AM Mike Thomsen <mi...@gmail.com> wrote:
>
> I could be mistaken, but I think that's standard JDBC behavior to not show the values. That said, yes it would be a fairly trivial improvement to add a dump of the record to a debug logger.
>
> On Wed, Feb 27, 2019 at 4:36 AM Fred Affini <fr...@gmail.com> wrote:
>>
>> Hi Matt and Phillip, thanks a lot for the help
>>
>> Setting PutDatabaseRecord log level to DEBUG (changing Bulletin Level in the
>> GUI or insert the XML line Matt sent) almost gave me what I need, the log
>> nos show:
>>
>> 10:32:55 CETDEBUGadcd1a7d-1000-1169-8aa8-92d8f2e891e5
>> PutDatabaseRecord[id=adcd1a7d-1000-1169-8aa8-92d8f2e891e5] Executing query
>> INSERT INTO SYSTEM.NIFI_INPUT_TEST (ID_BB_GLOBAL, BLOOMBERG_CODE, ID_ISIN,
>> ID_CUSIP, ID_SEDOL, TICKER, ID_BB_COMPANY, ID_GRID, CRNCY, INSTYP, ISSUE_DT,
>> CPN_TYP, CPN, MTY_TYP, MATURITY, WORKOUT_DT_BID) VALUES
>> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); fieldIndexes: [0, 1, 2, 3, 4, 5, 6, 7, 8,
>> 9, 10, 11, 12, 13, 14, 15]; batch index: 1; batch size: 1499
>>
>> I think the log should put the values in the parameters, looks like it is
>> being logged before preparing the statement. I still have a way to figure it
>> out, since it gives me the field index and I suppose that: *batch index: 1;
>> batch size: 1499* means the first line of my file (that has 1499 lines)
>>
>> Should this be a request for improvement in the processor?
>>
>> Regards,
>> Fred
>>
>>
>>
>> --
>> Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/
Re: Log Queries being executed by PutDatabaseRecord
Posted by Mike Thomsen <mi...@gmail.com>.
I could be mistaken, but I think that's standard JDBC behavior to not show
the values. That said, yes it would be a fairly trivial improvement to add
a dump of the record to a debug logger.
On Wed, Feb 27, 2019 at 4:36 AM Fred Affini <fr...@gmail.com>
wrote:
> Hi Matt and Phillip, thanks a lot for the help
>
> Setting PutDatabaseRecord log level to DEBUG (changing Bulletin Level in
> the
> GUI or insert the XML line Matt sent) almost gave me what I need, the log
> nos show:
>
> 10:32:55 CETDEBUGadcd1a7d-1000-1169-8aa8-92d8f2e891e5
> PutDatabaseRecord[id=adcd1a7d-1000-1169-8aa8-92d8f2e891e5] Executing query
> INSERT INTO SYSTEM.NIFI_INPUT_TEST (ID_BB_GLOBAL, BLOOMBERG_CODE, ID_ISIN,
> ID_CUSIP, ID_SEDOL, TICKER, ID_BB_COMPANY, ID_GRID, CRNCY, INSTYP,
> ISSUE_DT,
> CPN_TYP, CPN, MTY_TYP, MATURITY, WORKOUT_DT_BID) VALUES
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); fieldIndexes: [0, 1, 2, 3, 4, 5, 6, 7,
> 8,
> 9, 10, 11, 12, 13, 14, 15]; batch index: 1; batch size: 1499
>
> I think the log should put the values in the parameters, looks like it is
> being logged before preparing the statement. I still have a way to figure
> it
> out, since it gives me the field index and I suppose that: *batch index: 1;
> batch size: 1499* means the first line of my file (that has 1499 lines)
>
> Should this be a request for improvement in the processor?
>
> Regards,
> Fred
>
>
>
> --
> Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/
>
Re: Log Queries being executed by PutDatabaseRecord
Posted by Fred Affini <fr...@gmail.com>.
Hi Matt and Phillip, thanks a lot for the help
Setting PutDatabaseRecord log level to DEBUG (changing Bulletin Level in the
GUI or insert the XML line Matt sent) almost gave me what I need, the log
nos show:
10:32:55 CETDEBUGadcd1a7d-1000-1169-8aa8-92d8f2e891e5
PutDatabaseRecord[id=adcd1a7d-1000-1169-8aa8-92d8f2e891e5] Executing query
INSERT INTO SYSTEM.NIFI_INPUT_TEST (ID_BB_GLOBAL, BLOOMBERG_CODE, ID_ISIN,
ID_CUSIP, ID_SEDOL, TICKER, ID_BB_COMPANY, ID_GRID, CRNCY, INSTYP, ISSUE_DT,
CPN_TYP, CPN, MTY_TYP, MATURITY, WORKOUT_DT_BID) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); fieldIndexes: [0, 1, 2, 3, 4, 5, 6, 7, 8,
9, 10, 11, 12, 13, 14, 15]; batch index: 1; batch size: 1499
I think the log should put the values in the parameters, looks like it is
being logged before preparing the statement. I still have a way to figure it
out, since it gives me the field index and I suppose that: *batch index: 1;
batch size: 1499* means the first line of my file (that has 1499 lines)
Should this be a request for improvement in the processor?
Regards,
Fred
--
Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/
Re: Log Queries being executed by PutDatabaseRecord
Posted by Phillip Grenier <ph...@nifi.rocks>.
Fred,
Sorry for the confusion, but I meant the log level on the PutDatabaseRecord
processor should be set to Debug.
Phillip
On Tue, Feb 26, 2019 at 11:32 AM Fred Affini <fr...@gmail.com>
wrote:
> Hi Phillip, thanks for the reply.
>
> All my relationships (success, failure, retry) from PutDatabaseRecord are
> point to an LogAttribute processor.
>
> On the properties of LogAttribute I have:
>
> Log Level = debug
> Attributes to Log = statement.type
>
> What I got on bulletin board is:
>
> 17:29:31 CET WARNING adcd1a7d-1000-1169-8aa8-92d8f2e891e5
> PutDatabaseRecord[id=adcd1a7d-1000-1169-8aa8-92d8f2e891e5] Failed to
> process
>
> StandardFlowFileRecord[uuid=3f28b6fb-fc0f-40a7-9270-db9870432d11,claim=StandardContentClaim
> [resourceClaim=StandardResourceClaim[id=1551198571000-5, container=default,
> section=5], offset=0, length=1049638],offset=0,name=file.csv,size=1049638]
> due to ORA-00001: unique constraint (SYSTEM.SYS_C007013) violated
>
> but not the actual insert query that violated the constraint.
>
> Any other tips?
>
> Thanks a lot,
> Fred
>
>
>
> --
> Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/
>
Re: Log Queries being executed by PutDatabaseRecord
Posted by Matt Burgess <ma...@apache.org>.
Fred,
Try adding the following line to conf/logback.xml (somewhere around
line 88 where the other special processor levels are set :P)
<logger name="org.apache.nifi.processors.standard.PutDatabaseRecord"
level="DEBUG"/>
Regards,
Matt
On Tue, Feb 26, 2019 at 11:32 AM Fred Affini <fr...@gmail.com> wrote:
>
> Hi Phillip, thanks for the reply.
>
> All my relationships (success, failure, retry) from PutDatabaseRecord are
> point to an LogAttribute processor.
>
> On the properties of LogAttribute I have:
>
> Log Level = debug
> Attributes to Log = statement.type
>
> What I got on bulletin board is:
>
> 17:29:31 CET WARNING adcd1a7d-1000-1169-8aa8-92d8f2e891e5
> PutDatabaseRecord[id=adcd1a7d-1000-1169-8aa8-92d8f2e891e5] Failed to process
> StandardFlowFileRecord[uuid=3f28b6fb-fc0f-40a7-9270-db9870432d11,claim=StandardContentClaim
> [resourceClaim=StandardResourceClaim[id=1551198571000-5, container=default,
> section=5], offset=0, length=1049638],offset=0,name=file.csv,size=1049638]
> due to ORA-00001: unique constraint (SYSTEM.SYS_C007013) violated
>
> but not the actual insert query that violated the constraint.
>
> Any other tips?
>
> Thanks a lot,
> Fred
>
>
>
> --
> Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/
Re: Log Queries being executed by PutDatabaseRecord
Posted by Fred Affini <fr...@gmail.com>.
Hi Phillip, thanks for the reply.
All my relationships (success, failure, retry) from PutDatabaseRecord are
point to an LogAttribute processor.
On the properties of LogAttribute I have:
Log Level = debug
Attributes to Log = statement.type
What I got on bulletin board is:
17:29:31 CET WARNING adcd1a7d-1000-1169-8aa8-92d8f2e891e5
PutDatabaseRecord[id=adcd1a7d-1000-1169-8aa8-92d8f2e891e5] Failed to process
StandardFlowFileRecord[uuid=3f28b6fb-fc0f-40a7-9270-db9870432d11,claim=StandardContentClaim
[resourceClaim=StandardResourceClaim[id=1551198571000-5, container=default,
section=5], offset=0, length=1049638],offset=0,name=file.csv,size=1049638]
due to ORA-00001: unique constraint (SYSTEM.SYS_C007013) violated
but not the actual insert query that violated the constraint.
Any other tips?
Thanks a lot,
Fred
--
Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/
Re: Log Queries being executed by PutDatabaseRecord
Posted by Phillip Grenier <ph...@nifi.rocks>.
Fred,
Set the log level to debug and it should show on the bulletin. If you are
using the "Use statement.type Attribute", use the LogAttribute processor to
report the sql attribute. Phillip https://nifi.rocks
On Tue, Feb 26, 2019 at 8:30 AM fredaffini <fr...@gmail.com>
wrote:
> Hello,
>
> Iam new to NiFi and still learning. I would like to know if there is a way
> to log the individual queries being executed against the database by
> PutDatabaseRecord processor.
>
> How can I accomplish that?
>
> Regards,
> Fred
>
>
>
> --
> Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/
>