nifi hive controller service failing daily

[#1]

I am running a hive controller for puthivql processor.

It is failing daily with error

'failed to process sesion due to org.apache,nifi.processor.exception.ProcessorException: org.apache.commons.dbcp.SQLNestedException: cannot create PoolableConnectionFactory'

When I restart the controller service, it works till next day

Could you please help me how to overcome this error or is there a way to restart controller services automatically or scheduled restart of controller services.

Suggestions are highly appreciated.

[#2 · Accepted solution]

@Mark

Method1:-
Add the validation query in your Hive Connection Pool controller service as

Validation query

select current_timestamp

(or)

select 1

This Validation query used to validate connections before returning them. When a borrowed connection is invalid, it gets dropped and a new valid connection will be returned.

Method2:-
By using restApi we can stop the processor and disable/enable the connection pool controller service as we can schedule this

Script:-
1.Stop the HiveQL Processor

2.Disable Hive Connection Pool

3.Enable Hive Connection Pool

4.Start HiveQL Processor

I have answered similar kind of issue in this link with all detailed explanation about restapi commands, please use the link as reference.
_

If the Answer helped to resolve your issue, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.

[#3]

@Shu

I already have a validation query and have max wait time to 60 sec, it did not help.

I will check using the rest api

[#4]

@Shu Wanted to know, to add the "Validation query" is really mandatory to have to run the controller service or not?