Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

[NIFI]Problem on Athena queries via NiFI

avatar
Contributor

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

Alexandros_0-1591216996995.png

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!

 

1 ACCEPTED SOLUTION

avatar
Expert Contributor

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.

View solution in original post

2 REPLIES 2

avatar
Expert Contributor

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.

avatar
Contributor

Great! thanks!