SNOW-299559: Feature request: support async queries (sqlalchemy 1.4.0)

[shahargl]

What is the current behavior?

SQLAlchemy 1.4.0 introduced the asyncio extension that add the ability to run queries in paralell.

The following code should create async engine:

engine = create_async_engine('snowflake://shahar:[email protected]/TEST?cache
   ...: _column_metadata=True&client_session_keep_alive=True&warehouse=TEST')

But instead it throws the following error:

----> 1 engine = create_async_engine(u, echo=True)

~/git/tmp/lib/python3.9/site-packages/sqlalchemy/ext/asyncio/engine.py in create_async_engine(*arg, **kw)
     46     kw["future"] = True
     47     sync_engine = _create_engine(*arg, **kw)
---> 48     return AsyncEngine(sync_engine)
     49 
     50 

~/git/tmp/lib/python3.9/site-packages/sqlalchemy/ext/asyncio/engine.py in __init__(self, sync_engine)
    536     def __init__(self, sync_engine: Engine):
    537         if not sync_engine.dialect.is_async:
--> 538             raise exc.InvalidRequestError(
    539                 "The asyncio extension requires an async driver to be used. "
    540                 f"The loaded {sync_engine.dialect.driver!r} is not async."

InvalidRequestError: The asyncio extension requires an async driver to be used. The loaded 'snowflake' is not async.

What is the desired behavior?

The engine will be created successfully

How would this improve snowflake-sqlalchemy?

It'll support running async queries

References, Other Background

https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html

[TRManderson]

I'd be happy to provide asyncio SQLAlchemy support as an external contributor, but that would require asyncio support for the underlying connection, which doesn't look like it's coming any time soon snowflakedb/snowflake-connector-python#38

[shahargl]

Any updates?

[dbfreem]

Any update on this? Asyncio support would be a nice feature to add.

[alex-linx]

+1 We are also using snowflake and this has a significant negative affect on performance. We have to rely on workaround with multiprocessing/multithreading etc. The alternative of using snowflake connector directly means losing all the benefits of an ORM, which is unacceptable

[Viciooo]

bump

[David-Salazar]

bump

[sfc-gh-dszmolka]

hi folks, thanks for all the feedback. Prerequisite I guess would be the Snowflake PythonConnector to support asyncio (snowflakedb/snowflake-connector-python#38) first.

[clbarnes]

The snowflake connector does not necessarily need to be asyncio-compatible. The connector is thread safe https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-api which means you can wrap any IO-bound sync calls in asyncio.to_thread. It's not as good as the connector supporting async directly but it's not a bad start.

It looks like we're not far off native async support, too snowflakedb/snowflake-connector-python#38 (comment)