Hi @PJ,
Atscale probably is using a JDBC connection.
"it is connecting and disconnecting from hive metastore every few seconds.... is this normal?"
Yes, can be normal as a JDBC connection is only established when a call is made to the target (Hive) to avoid narrowing network bandwidth for other calls, among other things to increase performance. Also, "Atscale" probably is doing some intermediate calls of some internal routines it may have.
"when a user submits a query when is the connection made to hive metastore and when does it ends? "
The connection should be established instantly but once again that depends of the caller app. The same for ending the connection, however for the closing of a connection the settings of the JDBC are also taken in account (Timeout property etc.).
"and how often can this happen?"
Depends of the number of calls the app does to Hive, Hive is just waiting to be called by third-party tools, in other words, Hive doesn't initiate connections to third-party tools.
Hope it helps!
Gonçalo