topic Re: api/2.0/sql/history/queries endpoint does not return query execution time in Warehousing & Analytics

api/2.0/sql/history/queries endpoint does not return query execution time

Octavian1 — Fri, 22 Dec 2023 15:07:21 GMT

Hi,

I cannot see the query execution time in the response to the "api/2.0/sql/history/queries" request.

Basically, I get only the following fields:
{
"next_page_token":...,
"has_next_page":...,
"res":[
{
"query_id":...,
"status":..,
"query_text":...,
"query_start_time_ms":...,
"execution_end_time_ms":...,
"query_end_time_ms":...,
"user_id":...,
"user_name":...,
"spark_ui_url":...,
"endpoint_id":...,
"rows_produced":...,
"lookup_key":...,
"executed_as_user_id":...,
"executed_as_user_name":...,
"is_final":..,
"channel_used":{
"name":...,
"dbsql_version":...
},
"plans_state":...,
"statement_type":...,
"warehouse_id":...,
"duration":...,
"canSubscribeToLiveQuery":...
},
...
]
}
There is an execution_end_time_ms (which is equal to the query_end_time_ms), but no execution time (which is part of the duration), as it can be seen in Query History UI, see screenshot attached.
Is there another way to get the query execution time?

Thank you!

Re: api/2.0/sql/history/queries endpoint does not return query execution time

Yeshwanth — Thu, 01 Feb 2024 06:51:03 GMT

Hi @Octavian1

As per the API documentation, this API should ideally fetch the execution time and the duration too. What do you see in the response? Can you attach the response here?

Re: api/2.0/sql/history/queries endpoint does not return query execution time

feiyun0112 — Thu, 01 Feb 2024 07:48:07 GMT

you can get time from metrics

"metrics": { "total_time_ms": 1000, "read_bytes": 1024, "rows_produced_count": 100000, "compilation_time_ms": 1000, "execution_time_ms": 1000, "read_remote_bytes": 1024, "write_remote_bytes": 1024, "read_cache_bytes": 1024, "spill_to_disk_bytes": 1024, "task_total_time_ms": 100000, "read_files_count": 1, "read_partitions_count": 1, "photon_total_time_ms": 1000, "rows_read_count": 10000, "result_fetch_time_ms": 100000, "network_sent_bytes": 1024, "result_from_cache": false, "pruned_bytes": 1024, "pruned_files_count": 1, "provisioning_queue_start_timestamp": 1595357087200, "overloading_queue_start_timestamp": 1595357087200, "query_compilation_start_timestamp": 1595357087200, "metadata_time_ms": 0, "planning_time_ms": 0, "query_execution_time_ms": 0, "planning_phases": [ {} ] },

Re: api/2.0/sql/history/queries endpoint does not return query execution time

Yeshwanth — Fri, 02 Feb 2024 07:06:41 GMT

Spot on @feiyun0112

So this confirms that the API is working as expected right?

Re: api/2.0/sql/history/queries endpoint does not return query execution time

feiyun0112 — Fri, 02 Feb 2024 12:26:24 GMT

@Yeshwanth wrote:
Spot on @feiyun0112
So this confirms that the API is working as expected right?

yes, you can compare data with ui

Re: api/2.0/sql/history/queries endpoint does not return query execution time

Octavian1 — Wed, 14 Feb 2024 08:59:26 GMT

The response I am getting is the one given in the first post (have tried again right now). I do not get any "metrics" JSON in the response, although I am calling the api/2.0/sql/history/queries endpoint as hinted.

To get those metrics maybe it is needed a special setup, some extra params in the request or other requirements on the workspace?

Thanks!

Re: api/2.0/sql/history/queries endpoint does not return query execution time

Octavian1 — Wed, 14 Feb 2024 09:11:20 GMT

Got it, to get the metrics you've got to call with the include_metrics param set to true:

api/2.0/sql/history/queries?include_metrics=true