How to kill long running queries through the API

Looker Tips & Resources Technical Tips & Tricks
1

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.

2

Hey @mandy_chan

Hope you are well.

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

Will you please fix?

thanks

3 
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