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.

[NIFI]Problem on Athena queries via NiFI

Solved Go to solution

[NIFI]Problem on Athena queries via NiFI

Explorer

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

Accepted Solutions
Highlighted

Re: [NIFI]Problem on Athena queries via NiFI

Cloudera Employee

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
Highlighted

Re: [NIFI]Problem on Athena queries via NiFI

Cloudera Employee

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

Highlighted

Re: [NIFI]Problem on Athena queries via NiFI

Explorer

Great! thanks!

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