Support Questions
Find answers, ask questions, and share your expertise

Nifi QueryDatabaseTable processor returns 0 if not record found

hi Guys,

I am using QueryDatabaseTable to query the table for incremental load. Everything works fine if the load is there, all my downstream processors get executed, however, the problem comes when if I haven't got the incremental load.

Following are the details of my job.

  • Get the incremental load.
  • if the load is present create a file on a server.
  • other ETL jobs get triggers based on this file.

If there is no data for today, the processor returns 0 flow-data and so my downstream processors which create file doesn't get executed and so whole flow (not nifi) which depends on the file fails.

Is there a way, I can return empty/null values if there are no records present? or any other way I can continue my downstream processors?

6 REPLIES 6

Super Guru
@Gaurang Shah

For this case you can use Monitor Activity processor by forking Querydatabase table processors success relation,

  1. if you got the load then create a file on a server,
  2. if load is not present then monitor Activity processor will create the file that you want to create on the server.

Example:-

64469-monitoractivity.png

As you can see in the above flow i have forked the success relation from Querydatabase table processor to both UpdateAttribute and MonitorActivity processors.

So if the load is present from QueryDatabasetable processor then you are able to create file on server, if load is not present then Monitor Activity processor needs to configure as how much time it needs to look for flowfile and determines the flow not having load.

Configs:-

64470-config-monitoractivity.png

As my monitor activity processor will wait for 1 min if no flowfiles are feed to the processor then it sends inactivity message with flowfile content as

Lacking activity as of time: 2018/03/04 14:25:06; flow has been inactive for 1 minutes

So once you got this inactive flowfile from Monitor activity processor then feed inactive relationship to your create file on the server processor.

if you want to create file on the server with specific name then by using UpdateAttribute you can change the name of the file.

As you can change the configs of Monitor Activity processors properties Threshold duration how long you want to wait and determine the flow is inactive.

Now we are creating a file even if we haven't got any load from query database table processor.

Monitor Activity reference:-

https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.5.0/org.apache...

@Shu Thanks, it might just work. In my case, QueryDatabase table is Cron scheduled and runs only once a day at the specific time. How do I tell my monitorActivity processor to start watching for time after my QueryDatabase has triggered?

also, if files is not available for two days continuous, this won't work. I can't keep on sending messages as it will create file every 5 minute.

Super Guru

@Gaurang Shah

You can schedule Monitor Activity processor by using Cron Schedule.

Schedule Monitor Activity processor after 2-3 mins than Query database table processor (or) this scheduling depends on how much time QueryDatabase table processor is taking to load incremental data.

If my Query database table processor is completing to load with in 1 min then you need to schedule Monitor Activity 1 min after Query database table processor and if your threshold duration is 1 minute then you need to run Monitor Activity continue 2 mins in row, first minute is for watching and second minute to trigger message.

Example:-

QueryDatabaseTable processor scheduled at 2:00AM daily and Monitor Activity is scheduled at 2:01AM daily and you are running threshold duration of 1 min.

Then monitor activity configs would be

64471-cron-monitoractivity.png

I'm running monitor activity 2 minutes i.e 2:01AM(to watch for file) 2:02AM(to trigger message) because my threshold duration is 1 min, if your threshold duration is 5 minutes then you need to run processor for 6 mins(5mins to watch +1 min to trigger message)

64472-monitoractivity.png

Change threshold duration and cron schedule as per your needs.

@Gaurang Shah

Shu’s proposed approach should work, however, it assumes no activity means zero records. Is that always true? You could take a similar approach using UpdateAttribute to store number of records, preset the attribute to be zero. Then use RouteOnAttribute based on a value condition on the attribute, which you can handle to go to your step to create a file no matter what.

@Constantin Stanca

yes, no activity for me is 0 records.

could you please explain this approach in details.

@Constantin Stanca

could you please explain the approach in detail.