I am trying to understand how does a tool like Atscale(data wrangling tool) connect to Hive metastore? There is a weird behavior in the log files of Atscale, when you try to wrangle data through Atscale, it is connecting and disconnecting from hive metastore every few seconds.... is this normal?
when a user submits a query when is the connection made to hive metastore and when does it ends? and how often can this happen?
in case of Atscale, there could be aggregates, not sure how this works..
"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.