How to kill long running queries through the API

[mandy_chan]

Knowledge Drop

Last tested: Apr 8, 2020
 

To kill a long running query through the API, you can first get a list of all running queries through the `all_running_queries` endpoint and write a script where if the running time is greater than threshold, then `kill_query`.

Example code here: Kill Long Running Queries

If you want to kill any long running queries that are not part of a scheduled report, the all_running_queries endpoint has a source field and you can exclude the source in the example script.

This content is subject to limited support.                

[skywalker]

Hey @mandy_chan 

Hope you are well.

The lonk to Kill Long Running Queries  does not work any more,

Will you please fix?

thanks

[hgoscar]

import looker_sdk
from datetime import datetime

#load the connection parameters
sdk = looker_sdk.init40("looker.ini")

queries = sdk.all_running_queries() #load all running queries 

threshold = 60 * 10  # Threshold for Query termination - currently set for 10mins

results=[]
exceptions=[]
sources_to_exclude = ['PDT Regenerator', 'alerts', 'regenerator'] # job wont terminate queries from these sources 


for query in queries: 
    query_created_at = datetime.strptime(query.created_at.split('.')[0].replace('T',' '),'%Y-%m-%d %H:%M:%S')
    source = query.source
    running_time = (datetime.utcnow() - query_created_at).total_seconds() 
    if running_time > threshold and source not in sources_to_exclude: 
        results.append(query.query.id) 
        try:
            sdk.kill_query(query.query_task_id)
            print('Killed query task id' + query.query_task_id)        
        except: 
            exceptions.append(query.query_task_id) 

Credits : https://gist.github.com/jayozer/96d42c9b12eecfd2b3d95eb70198ee04