Unable to use Pinot with create_async_engine of Sqlalchemy

[akshayu2411]

We were using pinot in our backend fastapi code using Sqlalchemy. We used create_engine from sqlalchemy to create connections with pinot.

But now we have a usecase of making async calls to pinot, so as to make multiple calls at the same time and then await on the results.

In order to do so, we tried the following code:

import asyncio
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.future import select
from sqlalchemy.orm import sessionmaker

# Pinot DB URL 
PINOT_DB_URL =  "Pinot DB URL"

# Creating the async engine
async_engine = create_async_engine(PINOT_DB_URL, echo=True)

# Creating an async session factory
AsyncSessionLocal = sessionmaker(
   bind=async_engine,
   class_=AsyncSession,
   expire_on_commit=False
)

async def run_pinot_async_example():
   # Creating an async session
   async with AsyncSessionLocal() as session:
       async with session.begin(): 
           result = await session.execute(select('*').select_from('sample_table').limit(10))
           
           # Fetch and print all results
           rows = result.fetchall()
           for row in rows:
               print(row)

# Running the async query
async def main():
   await run_pinot_async_example()

if __name__ == "__main__":
   asyncio.run(main())

But this code is giving the following error:

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

How can we create Sqlalchemy to create multiple async calls instead of using external libraries like asyncio and httpx as used in the examples https://github.com/python-pinot-dbapi/pinot-dbapi/blob/master/examples/pinot_async.py.

[xiangfu0]

Python client is using http client to query pinot. And it's using httpx for async request.

[xiangfu0]

You meant need to write your own logic if you don't want to use httpx

[akshayu2411]

Yes, we could have used httpx but wanted to use sqlalchemy for our usecase. And in order to make multiple pinot calls in parallel using in a single API in fastAPI, create_async_engine could have helped.