You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by "Jens M. Kofoed" <jm...@gmail.com> on 2021/03/02 07:40:15 UTC
Count records where value match x
Hi
I'm using the SiteToSiteStatusReportingTask to monitor NIFI flows and I
would like to calculate how many connections has BackPressure Enabled and a
bunch more calculations. The end results of all the statistics has to be in
one final flowfile so a final result can be sent to another system.
First I use a PartitionRecord, to split the records by componentType so I
gets all Connections in one flowfile. Using a CalculateRecordStats with a
property with the value of "/isBackPressureEnabled" will give 2 resuts:
recordStats.isBackPressureEnabled.false = 2451
recordStats.isBackPressureEnabled.true = 2
But I can't figure out how to make calculations based on specific fields
where values has to match a criteria.
Using a QueryRecords I have added 2 more fields to the records.
SELECT *, (queuedBytes*100/backPressureBytesThreshold) AS
queuedBytesProcent, (queuedCount*100/backPressureObjectThreshold) AS
queuedCountProcent FROM FLOWFILE
I would like to calculate how many connections has a queuedBytesProcent >
60 and how many has a queuedCountProcent > 75.
And later on I would like to make calculation based on different
"parentPath".
I can use the QueryRecords to make multiple outputs depending of different
statements and next use a CalculateRecordStats to calculate the result. But
here I will end up with multiple flowfiles and not one final result.
Is it possible to do it in one straight flow?
kind regards
Jens M. Kofoed
Re: Count records where value match x
Posted by "Jens M. Kofoed" <jm...@gmail.com>.
Hi Mark
Thanks for pointing me in the direction of where clauses inside a count. I
found this which is working.
select count(case when queuedCountProcent > 50 then 1 else null end) as
count_queuedCountProcent,
count(case when queuedBytesProcent > 50 then 1 else null end) as
count_queuedBytesProcent,
count(case when isBackPressureEnabled = 'true' then 1 else null
end) as count_isBackPressureEnabled
FROM FLOWFILE
kind regards
Jens
Den ons. 3. mar. 2021 kl. 06.44 skrev Jens M. Kofoed <jmkofoed.ube@gmail.com
>:
> Hi Mark
>
> Many thanks for you replay. I have never used a "where" clauses inside a
> count in a select statement so I tried it. But it doesn't work all 4 fields
> has the same value which is equal to the total amount of records.
> if it had worked it would have been very very nice.
>
> Thanks
> Jens M. Kofoed
>
> Den tir. 2. mar. 2021 kl. 16.04 skrev Mark Payne <ma...@hotmail.com>:
>
>> Jens,
>>
>> I think you should be able to use a query like:
>>
>> SELECT
>> COUNT( (queuedBytes*100/backPressureBytesThreshold) > 60) AS
>> overSixtyPercentBytes,
>> COUNT( (queuedCount*100/backPressureObjectThreshold) > 60) AS
>> overSixtyPercentObjects,
>> COUNT( (queuedBytes*100/backPressureBytesThreshold) > 75) AS
>> overSeventyFivePercentBytes,
>> COUNT( (queuedCount*100/backPressureObjectThreshold) > 75) AS
>> overSeventyFivePercentObjects
>> FROM FLOWFILE
>>
>> Unless I’m misunderstanding what you’re after?
>>
>> Thanks
>> -Mark
>>
>> > On Mar 2, 2021, at 2:40 AM, Jens M. Kofoed <jm...@gmail.com>
>> wrote:
>> >
>> > Hi
>> >
>> > I'm using the SiteToSiteStatusReportingTask to monitor NIFI flows and I
>> would like to calculate how many connections has BackPressure Enabled and a
>> bunch more calculations. The end results of all the statistics has to be in
>> one final flowfile so a final result can be sent to another system.
>> >
>> > First I use a PartitionRecord, to split the records by componentType so
>> I gets all Connections in one flowfile. Using a CalculateRecordStats with a
>> property with the value of "/isBackPressureEnabled" will give 2 resuts:
>> > recordStats.isBackPressureEnabled.false = 2451
>> > recordStats.isBackPressureEnabled.true = 2
>> >
>> > But I can't figure out how to make calculations based on specific
>> fields where values has to match a criteria.
>> > Using a QueryRecords I have added 2 more fields to the records.
>> > SELECT *, (queuedBytes*100/backPressureBytesThreshold) AS
>> queuedBytesProcent, (queuedCount*100/backPressureObjectThreshold) AS
>> queuedCountProcent FROM FLOWFILE
>> >
>> > I would like to calculate how many connections has a queuedBytesProcent
>> > 60 and how many has a queuedCountProcent > 75.
>> > And later on I would like to make calculation based on different
>> "parentPath".
>> >
>> > I can use the QueryRecords to make multiple outputs depending of
>> different statements and next use a CalculateRecordStats to calculate the
>> result. But here I will end up with multiple flowfiles and not one final
>> result.
>> >
>> > Is it possible to do it in one straight flow?
>> >
>> > kind regards
>> > Jens M. Kofoed
>>
>>
Re: Count records where value match x
Posted by "Jens M. Kofoed" <jm...@gmail.com>.
Hi Mark
Many thanks for you replay. I have never used a "where" clauses inside a
count in a select statement so I tried it. But it doesn't work all 4 fields
has the same value which is equal to the total amount of records.
if it had worked it would have been very very nice.
Thanks
Jens M. Kofoed
Den tir. 2. mar. 2021 kl. 16.04 skrev Mark Payne <ma...@hotmail.com>:
> Jens,
>
> I think you should be able to use a query like:
>
> SELECT
> COUNT( (queuedBytes*100/backPressureBytesThreshold) > 60) AS
> overSixtyPercentBytes,
> COUNT( (queuedCount*100/backPressureObjectThreshold) > 60) AS
> overSixtyPercentObjects,
> COUNT( (queuedBytes*100/backPressureBytesThreshold) > 75) AS
> overSeventyFivePercentBytes,
> COUNT( (queuedCount*100/backPressureObjectThreshold) > 75) AS
> overSeventyFivePercentObjects
> FROM FLOWFILE
>
> Unless I’m misunderstanding what you’re after?
>
> Thanks
> -Mark
>
> > On Mar 2, 2021, at 2:40 AM, Jens M. Kofoed <jm...@gmail.com>
> wrote:
> >
> > Hi
> >
> > I'm using the SiteToSiteStatusReportingTask to monitor NIFI flows and I
> would like to calculate how many connections has BackPressure Enabled and a
> bunch more calculations. The end results of all the statistics has to be in
> one final flowfile so a final result can be sent to another system.
> >
> > First I use a PartitionRecord, to split the records by componentType so
> I gets all Connections in one flowfile. Using a CalculateRecordStats with a
> property with the value of "/isBackPressureEnabled" will give 2 resuts:
> > recordStats.isBackPressureEnabled.false = 2451
> > recordStats.isBackPressureEnabled.true = 2
> >
> > But I can't figure out how to make calculations based on specific fields
> where values has to match a criteria.
> > Using a QueryRecords I have added 2 more fields to the records.
> > SELECT *, (queuedBytes*100/backPressureBytesThreshold) AS
> queuedBytesProcent, (queuedCount*100/backPressureObjectThreshold) AS
> queuedCountProcent FROM FLOWFILE
> >
> > I would like to calculate how many connections has a queuedBytesProcent
> > 60 and how many has a queuedCountProcent > 75.
> > And later on I would like to make calculation based on different
> "parentPath".
> >
> > I can use the QueryRecords to make multiple outputs depending of
> different statements and next use a CalculateRecordStats to calculate the
> result. But here I will end up with multiple flowfiles and not one final
> result.
> >
> > Is it possible to do it in one straight flow?
> >
> > kind regards
> > Jens M. Kofoed
>
>
Re: Count records where value match x
Posted by Mark Payne <ma...@hotmail.com>.
Jens,
I think you should be able to use a query like:
SELECT
COUNT( (queuedBytes*100/backPressureBytesThreshold) > 60) AS overSixtyPercentBytes,
COUNT( (queuedCount*100/backPressureObjectThreshold) > 60) AS overSixtyPercentObjects,
COUNT( (queuedBytes*100/backPressureBytesThreshold) > 75) AS overSeventyFivePercentBytes,
COUNT( (queuedCount*100/backPressureObjectThreshold) > 75) AS overSeventyFivePercentObjects
FROM FLOWFILE
Unless I’m misunderstanding what you’re after?
Thanks
-Mark
> On Mar 2, 2021, at 2:40 AM, Jens M. Kofoed <jm...@gmail.com> wrote:
>
> Hi
>
> I'm using the SiteToSiteStatusReportingTask to monitor NIFI flows and I would like to calculate how many connections has BackPressure Enabled and a bunch more calculations. The end results of all the statistics has to be in one final flowfile so a final result can be sent to another system.
>
> First I use a PartitionRecord, to split the records by componentType so I gets all Connections in one flowfile. Using a CalculateRecordStats with a property with the value of "/isBackPressureEnabled" will give 2 resuts:
> recordStats.isBackPressureEnabled.false = 2451
> recordStats.isBackPressureEnabled.true = 2
>
> But I can't figure out how to make calculations based on specific fields where values has to match a criteria.
> Using a QueryRecords I have added 2 more fields to the records.
> SELECT *, (queuedBytes*100/backPressureBytesThreshold) AS queuedBytesProcent, (queuedCount*100/backPressureObjectThreshold) AS queuedCountProcent FROM FLOWFILE
>
> I would like to calculate how many connections has a queuedBytesProcent > 60 and how many has a queuedCountProcent > 75.
> And later on I would like to make calculation based on different "parentPath".
>
> I can use the QueryRecords to make multiple outputs depending of different statements and next use a CalculateRecordStats to calculate the result. But here I will end up with multiple flowfiles and not one final result.
>
> Is it possible to do it in one straight flow?
>
> kind regards
> Jens M. Kofoed