question Re: Get sql records count in Support Questions

Get sql records count

nuxeo-nifi — Fri, 19 May 2023 06:41:20 GMT

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 ?

Re: Get sql records count

SAMSAL — Fri, 19 May 2023 12:23:03 GMT

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.

Re: Get sql records count

nuxeo-nifi — Fri, 19 May 2023 16:12:01 GMT

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

Re: Get sql records count

SAMSAL — Fri, 19 May 2023 16:40:12 GMT

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

Re: Get sql records count

nuxeo-nifi — Fri, 19 May 2023 17:00:03 GMT

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

Re: Get sql records count

SAMSAL — Fri, 19 May 2023 18:14:37 GMT

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.

Re: Get sql records count

cotopaul — Mon, 22 May 2023 13:55:29 GMT

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.

Re: Get sql records count

nuxeo-nifi — Tue, 23 May 2023 15:47:33 GMT

@SAMSAL - it worked ! Thanks