Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Get sql records count

avatar
Explorer

I would like to get records count(select count(1) from table where col1=‘abc’) from a sql table. Could someone please help me with that ? 

1 ACCEPTED SOLUTION

avatar

In this case, you will need some extra steps. You could try something like:

You have an ExecuteSQLRecord or ExecuteSQL, configured with the Database Connection Pooling Service and the Record Writer (if using ExecuteSQLRecord).


Next, I would link the success queue to an ExtractText Processor. In this Processor I would add a new property called number_of_rows having the value " .* ". This will extract the value returned by count(*) and save it as an attribute in your FlowFile.


Once you did this, you can link the success queue to an RouteOnAttribute Processor. Here, you will define a new property named bigger_as_zero and use the NiFi's EL to validate if the attribute is bigger than zero or not.

 

If bigger, you will send that message to the processor in which you have linked this queue. Otherwise, meaning that the value is zero, you can route the unmatched queue to another ExecuteSQL/ExecuteSQLRecord. You could also apply NiFi's EL to check if the value is exactly zero, if that is what you are looking for and route the file accordingly.

NiFi's EL: https://nifi.apache.org/docs/nifi-docs/html/expression-language-guide.html

Everything mentioned above has to be done, in addition to what @SAMSAL already said, with the fact that you need to modify you running schedule from 0 seconds to a greater value. 0 means that you will constantly execute this SQL Record. Here you will have to modify the value based on your project's requirements.

View solution in original post

7 REPLIES 7

avatar
Super Guru

You can use ExecuteSQL pr ExecuteSQLRecord Processor for that. The first one will give you the result in avro format and the second one you can specify the format of the output by setting the record writer property.

avatar
Explorer

@SAMSAL I tried the above but it spits out multiple flowfiles.I just need the count(1) value

avatar
Super Guru

Can you please share the configuration for the executesql processor and any record writer you used. Also please share the flowfile output.

avatar
Explorer

nuxeonifi_0-1684515440053.png

I want the executeSQL to run just once and also if the count>0 then run another executeSQL to fetch records from another table

avatar

In this case, you will need some extra steps. You could try something like:

You have an ExecuteSQLRecord or ExecuteSQL, configured with the Database Connection Pooling Service and the Record Writer (if using ExecuteSQLRecord).


Next, I would link the success queue to an ExtractText Processor. In this Processor I would add a new property called number_of_rows having the value " .* ". This will extract the value returned by count(*) and save it as an attribute in your FlowFile.


Once you did this, you can link the success queue to an RouteOnAttribute Processor. Here, you will define a new property named bigger_as_zero and use the NiFi's EL to validate if the attribute is bigger than zero or not.

 

If bigger, you will send that message to the processor in which you have linked this queue. Otherwise, meaning that the value is zero, you can route the unmatched queue to another ExecuteSQL/ExecuteSQLRecord. You could also apply NiFi's EL to check if the value is exactly zero, if that is what you are looking for and route the file accordingly.

NiFi's EL: https://nifi.apache.org/docs/nifi-docs/html/expression-language-guide.html

Everything mentioned above has to be done, in addition to what @SAMSAL already said, with the fact that you need to modify you running schedule from 0 seconds to a greater value. 0 means that you will constantly execute this SQL Record. Here you will have to modify the value based on your project's requirements.

avatar
Super Guru

It seems like the ExecuteSQL is being executed continuously because its scheduled to run as such based on the Run Schedule. You have to set up the Run Schedule to the needed frequency by selecting CORN Driven Scheduling Strategy and set the CRON Time accordingly in the Run Schedule property.

avatar
Explorer

@SAMSAL  - it worked ! Thanks