topic Re: Can we get the actual query execution plan programmatically after a query is executed? Apart fro in Get Started Discussions

Can we get the actual query execution plan programmatically after a query is executed? Apart from UI

Ak_0926 — Fri, 22 Mar 2024 10:24:30 GMT

Let's say i have run a query and it showed me results. we can find the respective query execution plan on the UI. Is there any way we can get that execution plan through programmatically or through API?

Re: Can we get the actual query execution plan programmatically after a query is executed? Apart fro

Walter_C — Sat, 23 Mar 2024 14:50:45 GMT

You can obtain the query execution plan programmatically using the EXPLAIN statement in SQL. The EXPLAIN statement displays the execution plan that the database planner generates for the supplied statement. The execution plan shows how the table(s) referenced by the statement will be scanned — by plain sequential scan, index scan, etc. — and if multiple tables are referenced, what join algorithms will be used to bring together the required rows from each input table.

Here is an example of how you can use it:

# Spark SQL
query = "SELECT * FROM table"
plan = spark.sql(f"EXPLAIN {query}")
plan.show(truncate=False)

This will return a DataFrame with a single row and column that contains the execution plan as a string.

EXPLAIN command will only provide the logical and physical plans. It will not provide the runtime details like how much time each stage took, how much data was read, etc. For that level of detail, you would need to parse the Spark UI or logs.

Re: Can we get the actual query execution plan programmatically after a query is executed? Apart fro

Danny_Lee — Sun, 24 Mar 2024 02:29:33 GMT

The EXPLAIN docs show some extra functionality: https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-explain.html

EXPLAIN [ EXTENDED | CODEGEN | COST | FORMATTED ] statement