Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How to test NIFI DBCPConnectionPool periodically and send Email Notification

Solved Go to solution

How to test NIFI DBCPConnectionPool periodically and send Email Notification

New Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions

Re: How to test NIFI DBCPConnectionPool periodically and send Email Notification

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.

2 REPLIES 2

Re: How to test NIFI DBCPConnectionPool periodically and send Email Notification

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.

Re: How to test NIFI DBCPConnectionPool periodically and send Email Notification

New Contributor

Works great. Thanks

Don't have an account?
Coming from Hortonworks? Activate your account here