[Feature Request] Support async execution

[zhaorui2022]

We are currently using

databricks-sql-python/examples/README.md

Line 34 in a5b1ab0

- **`query_cancel.py`** shows how to cancel a query assuming that you can access the `Cursor` executing that query from a different thread. This is necessary because `databricks-sql-connector` does not yet implement an asynchronous API; calling `.execute()` blocks the current thread until execution completes. Therefore, the connector can't cancel queries from the same thread where they began.

to fetch statement ID when executing queries using sqlalchemy. However, the UUID is not returned at the very end of the query execution. And if the query fails, it never returns the statement ID. Is it possible to support async execution so that we can get the statement ID asap.

[MeinAccount]

Is there a timeline for getting proper async support? We would love that to speed up our Databricks workflows! There was clearly some work going on by susodapop (wo seems to have left Databricks) in #322 , #325 and Branch peco-1263-staging -- all of which has stalled 7 months ago.

[bkyryliuk]

+1 this affects databricks usability in the external systems as it is not possible to navigate to the query when it is running, failed or times out and makes debugging much more complex than it should be

[hayescode]

@rcypher-databricks @yunbodeng-db @andrefurlan-db @jackyhu-db @benc-db @kravets-levko

There is a community desire for async execution for various reasons. #82 and #176

My use case is OpenAI parallel tool calling which can generate multiple queries, but are executed sequentially due to lack of async support.

[gopalldb]

We are actively working on this.

[jprakash-db]

@zhaorui2022 cc @deeksha-db We have added support for Async execution from v3.7.0

[MeinAccount]

I don't feel like the API is appropriately async. When running a Merge-Statement the call to Cursor.execute_async will wait for the Statement to complete, only then returning to my Python code. I have traced this back to the inclusion of getDirectResults=ttypes.TSparkGetDirectResults(...) in ThriftBackend.executeCommand. Removing this parameter makes the execution actually run async. I have had no luck with changing maxRows or maxBytes (neither 0 nor 1)

[jprakash-db]

@MeinAccount We have fixed the issue and it is available in the latest release of the PySQL Connector v3.7.3

[mnussbaum-eq]

So is it possible to execute a query using await, are there query functions that are defined with async def ...? Having native python async methods would be really helpful towards integrating properly with the rest of the Python ecosystem