How to shut down/kill all queries in Hive

[#1]

Hi all,

I have been using Hive on Sandbox for the past few days. It was working fine up until yesterday when I noticed that my queries were taking an unusually long time to run or, more annoyingly, not running at all.

On further investigation, I checked the 'History' tab and noticed that there are a large number of queries which are still running.

I have been trying to terminate/kill the sessions without success (It will say "stopping" but never turns to killed). I have also tried rebooting and redeploying my VM.

Does anyone know how I can stop all running processes in Hive?

Thanks in advance.

[#2 · Accepted solution]

You need to get familiar with yarn command. You can kill any yarn application with yarn cli including hive, as long as it's hive on yarn. Run yarn kill appid to kill an app, if you just type yarn in terminal and hit enter you will see a list of available commands.

[#3]

Hi Artem - Apologies as this question might seem elementary but I am very new to Sandbox. Where can I access the terminal? Thank you.

[#4]

No apologies necessary, you've come to the right place. You need to open a shell to the sandbox either with an app like putty on windows or terminal on Mac. Then you need to type the following, it will ask for your password, password is hadoop, enter, same password again, then new password twice. When you are logged in you can execute yarn commands

ssh -P 2222 root@127.0.0.1

[#5]

Thanks for the quick response. I am going to use putty to ssh in - so is it correct to type into the Host Name on putty: my public DNS for sandbox:8080.

I have done that and logged in as root when prompted and then entered "hadoop" as the password. I am now getting an access denied message.

Any ideas? Thanks!

[#6]

@Maeve Ryan might be a good idea to go through this tutorial:

http://hortonworks.com/hadoop-tutorial/learning-the-ropes-of-the-hortonworks-sandbox/

[#7]

Issue is sorted now - now I finally know how to use putty! Thanks all

[#8]

@Maeve Ryan Try using root@sandbox.hortonworks.com -p 2222. You can also try port 4200 from a browser.

[#9]

Thanks Scott - my problem is sorted now!

[#10]

Hi all,

Has anyone a workaround for this problem ? I have exactly the same case.

I have similar issues on the Sandbox 2.5 (VirtualBox-5.1.12-112440-Win - HDP_2.5_virtualbox).

I killed the jobs with putty as root : yarn application -kill application_1482410373661_0002 but they are still visible on Ambari.

[root@sandbox ~]# yarn application -kill application_1482410373661_0002
16/12/24 12:26:40 INFO impl.TimelineClientImpl: Timeline service address: http://sandbox.hortonworks.com:8188/ws/v1/timeline/
16/12/24 12:26:40 INFO client.RMProxy: Connecting to ResourceManager at sandbox.hortonworks.com/172.17.0.2:8050
16/12/24 12:26:40 INFO client.AHSProxy: Connecting to Application History server at sandbox.hortonworks.com/172.17.0.2:10200
16/12/24 12:26:44 WARN retry.RetryInvocationHandler: Exception while invoking ApplicationClientProtocolPBClientImpl.getApplicationReport over null. Not retrying because try once and fail.
org.apache.hadoop.yarn.exceptions.ApplicationNotFoundException: Application with id 'application_1482410373661_0002' doesn't exist in RM.

I've found an issue corresponding :

Tez client keeps trying to talk to RM even if RM does not know about the application

https://issues.apache.org/jira/browse/TEZ-3156

This patch should be included as it was fixed for version 0.7.1

In the log (Ambary query) I can read 993 time :

INFO : Map 1: 0/1 Reducer 2: 0/2

The query is the proposed in the tutorial : ( http://fr.hortonworks.com/hadoop-tutorial/hello-world-an-introduction-to-hadoop-hcatalog-hive-and-pi...)

<code>SELECT truckid, avg(mpg) avgmpg FROM truck_mileage GROUP BY truckid;

Any idea how to clear the history and restart without the running state ?

Thanks in advance