[NIFI]Problem on Athena queries via NiFI

[#1]

Hi! I have a DBCPConnectionPool on NiFi 1.11.3 with this configuration:

Normally it works ok, but sometimes the query get stuck on a PutSQL, no errors for minutes and then a timeout (very basic queries)

I was trying some configurations on the pool but it seems I didn't quite understand the best way to troubleshoot this, I was thinking in each processor add a retry+counter+error.

But don't know where to set the time out for queries from start of it and no per connection. Maybe a way is to not keep connections open until the query starts, how should I set the pool to generate the connection on request from a processor and terminate it before execution.

I think it's a Athena problem with pools because I have no problem with another databases like postgres and mysql.

Any ideas??

Thank you!

[#2 · Accepted solution]

I can see that Validation Query field is empty hence you can define a validation query (e.g. select 1). Nifi will not know if the database went down or if the connection is timeout. NiFi DB connection pool simply gave a connection to a processor. If DB goes down or connection timeout, that processor would eventually time out the connection. Hence if you have defined a Validation Query in your DBCP CS, then next time if processor check for a connection from DB CS, and if Validation Query is configured, the connection will be validated and if good, handed off to requesting processor and If its bad then it will try to establish a new connection again.

[#3]

Great! thanks!