How to test NIFI DBCPConnectionPool periodically and send Email Notification

[#1]

Hi, Currently we are using NIFI 2.1, and one workflow is to running SQL from DBCPConnectionPool every 10 minutes to extract data, then do the following logic.

Due to the source is the MySQL database in another data center, sometimes the network connection is broken due to network or firewall issues. We would like to have email notification when this kind of thing happens in NIFI.

Right now, our first Executor SQL processor, when there is a network connection issue to the source MySQL DB, the only error coming out is in the bulletin or log, which hardly monitored by a human.

I wonder if we can design something in the NIFI processor, or it is natively available in NIFI, will trigger Email notification when the DBCP connection pool is broken.

Thanks

[#2 · Accepted solution]

It sounds like you're using ExecuteSQL as a source processor, meaning there are no incoming connections. In that case no flow file will be created. Instead, use GenerateFlowFile upstream from ExecuteSQL. You can schedule GenerateFlowFile for 10 minutes and set the content of the flow file to the SQL statement you want to execute. Then in ExecuteSQL you can schedule for 0 sec (as fast as possible, since the upstream processor runs every 10 mins) and remove the SQL Select Query property. At that point ExecuteSQL will expect the flow file content to contain SQL (which it will) and will execute that. If an error occurs, the flow file should be routed to failure, and you can use a PutEmail processor or something downstream for notification.

[#3]

Works great. Thanks