How to fix ambari Hive View 1.5 /2.0 'Result fetch timed out' issue ?

[#1]

While accessing Ambari 2.5 or 2.4 Hive Views (1.5.0/2.0) we see the following error in the log:

ERROR [ambari-client-thread-62] ContainerResponse:537 - Mapped exception to response: 500 (Internal Server Error) org.apache.ambari.view.hive2.utils.ServiceFormattedException
        at org.apache.ambari.view.hive2.client.NonPersistentCursor.getNextRows(NonPersistentCursor.java:132)
        at org.apache.ambari.view.hive2.client.NonPersistentCursor.fetchIfNeeded(NonPersistentCursor.java:119)
        at org.apache.ambari.view.hive2.client.NonPersistentCursor.getDescriptions(NonPersistentCursor.java:84)
        at org.apache.ambari.view.hive2.resources.jobs.ResultsPaginationController.request(ResultsPaginationController.java:145)
        at org.apache.ambari.view.hive2.resources.jobs.JobService.getResults(JobService.java:361)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

Also we notice the following kind of error "Result fetch timed out" in the Ambari UI Hive 1.5 view while running some hive queries that are fetching results from a large table.

"trace":"java.util.concurrent.TimeoutException: deadline passed
java.util.concurrent.TimeoutException: deadline passed
    at akka.actor.dsl.Inbox$InboxActor$$anonfun$receive$1.applyOrElse(Inbox.scala:117)
    at scala.PartialFunction$AndThen.applyOrElse(PartialFunction.scala:189)
    at akka.actor.Actor$class.aroundReceive(Actor.scala:467)
    at akka.actor.dsl.Inbox$InboxActor.aroundReceive(Inbox.scala:62)
    at akka.actor.ActorCell.receiveMessage(ActorCell.scala:516)
    at akka.actor.ActorCell.invoke(ActorCell.scala:487)
    at akka.dispatch.Mailbox.processMailbox(Mailbox.scala:238)
    at akka.dispatch.Mailbox.run(Mailbox.scala:220)
    at akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(AbstractDispatcher.scala:397)
    at scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
    at scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
    at scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
    at scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)","message":"Result fetch timed out","status":500}

This is basically a timeout error "Result fetch timed out" which indicates that HiveView is having default timeout value set in the ambari.properties that is not suitable for the kind of query we are running. As it is not able to fetch the result from hive within that mentioned time period.

Set the following properties in "/etc/ambari-server/conf/ambari.properties"

views.ambari.request.read.timeout.millis=300000
views.request.read.timeout.millis=300000

Here we are setting the value to 5 minutes. It can be increased / decreased further based on the observations and the time taken to process the long running queries.

.

From Ambari 2.4 this new parameter is added that can be used to define the Hive Instance specific settings.

views.ambari.hive.<HIVE_VIEW_INSTANCE_NAME>.result.fetch.timeout=300000

Example:

views.ambari.hive.AUTO_HIVE_INSTANCE.result.fetch.timeout=300000

Here above is the most important ambari.properties for the Hive View 1.5/2.0.

https://github.com/apache/ambari/blob/release-2.4.0/contrib/views/hive-next/src/main/java/org/apache...

**NOTE:** After making the changes inside the "ambari.properties" ambari-server needs to be restarted.

.

.

[#2]

In many cases the issue is caused by the pushdown optimization. You can disable the push down optimization using the following command:

set hive.optimize.ppd=false;

[#3]

@Ivan Georgiev

Thank you for sharing the parameter.