how to set call back for Databricks Statement Execution SQL API Query?

[Maulik]

I m using https://docs.databricks.com/api/workspace/statementexecution. using long running queries.my wait time is zero. queries might take 1 hour and I don't want to do pooling https://docs.databricks.com/api/workspace/statementexecution/getstatement the way they explain in above link. I m using queries with SQL api not using note book. do we have any way without pooling we can use some call back mechanism to get notify that your result is ready and then only I ask for result ?

[Kaniz_Fatma]

Hi @Maulik, When dealing with long-running queries in Databricks, there are a few strategies you can consider to optimize performance and avoid pooling.

Let’s explore some options:

1. Query Optimization:

   • Check the query plan to identify any inefficiencies in how the query is executed. Rewriting the query to use more efficient SQL constructs can significantly improve performance¹.
   • Consider using Delta Lake with dynamic file pruning for faster SQL queries².

2. Pooling:

   • While you mentioned that you don’t want to use pooling, it’s worth noting that Databricks Pools can help speed up data pipelines by acquiring VM instances in advance. Without pools, Databricks requests VMs from the cloud provider upon each cluster creation or auto-sc...³.

3. Callback Mechanism:

   • Unfortunately, Databricks doesn’t provide a built-in callback mechanism to notify when query results are ready. However, you can implement your own custom solution:
     • Run the query asynchronously.
     • Periodically check the query status using the Databricks REST API.
     • When the query completes, retrieve the results.

Remember to adjust the spark.databricks.hive.metastore.client.pool.size configuration parameter to o...⁴. If you encounter specific issues, consider monitoring network logs and checking connectivity to the Hive metastore.

Feel free to explore these options and adapt them to your specific use case. If you have further questions or need additional assistance, feel free to ask! 😊