How to obtain a query profile programatically?

[guizsantos]

Hi everyone! Does anyone know if there is a way to obtain the data used to create the graph showed in the "Query profile" section? Particularly, I am interested in the rows produced by the intermediary query operations. I can see there is "Download" button which effectively downloads a descriptive JSON file of the graph data, which is exactly what I want to obtain. 

However, performing this step manually is simply not possible. I've searched through the Databricks REST API docs but it seems the query profile is not provided in any endpoint.

[raphaelblg · Accepted solution]

Hello @guizsantos ,

The DBSQL query profile is generated from the spark plans and execution logs, spark plans can be gathered through the EXPLAIN SQL command. However, it's important to note that the full query profile as seen in the UI is not currently retrievable through the Databricks REST API.

If you're interested in having this feature available in Databricks, I encourage you to share your idea in the Databricks Ideas Portal.

[guizsantos]

Hey @raphaelblg , thanks for you input!

I understand that some info may be obtained by the `EXPLAIN` command, however, the output is not very clear on its meaning and definetely does not provide what is most interesting to us, which is the rows processed/generated by the query intermediate operations. I tried going through the Spark execution logs as well but they are very scattered and I was not able to find a way to gather those programatically as well.

So, I will submit the request in the ideas portal, thanks for the reference.

[artsheiko]

@guizsantos, 

Query history list api provides metrics, see include_metrics 

an executed query definition may be seen using query history system table