azureazure-functionssnowflake-cloud-data-platformdbconnection

Azure functions - Pooling dbconnection for snowflake python connector


I have an azure functions application(python) which connects to snowflake database(creates a dbconnection object), executes query and returns the results and it has been working fine. The only challenge is time it takes to return the query. It creates the dbconnection object everytime when it is invoked.

Question: Is it possible to pool the connection using functions app ( considering the functions app is stateless)


Solution

  • The short answer is YES, here is my sample code of Azure Function with HTTP trigger as below.

    import logging
    
    import azure.functions as func
    
    def inc(n):
        while True:
            n += 1
            yield n
    
    count = inc(0)
    
    def main(req: func.HttpRequest) -> func.HttpResponse:
        logging.info('Python HTTP trigger function processed a request.')
    
        return func.HttpResponse(f"Count: {count.__next__()}!")
    

    I declared a global variable count with the value of a generator inc(0) outside the function main, then I can get an increased count response as the figure below.

    enter image description here

    It's the same as my another answer for the SO thread Azure use python flask framework for function app.

    So I think it's indeed to realize a connection pool in Azure Functions, such as using snowflakedb/snowflake-sqlalchemy to create a connection pool for Snowflake database in Python outside the function main of Azure Function, as the code below by refering to the SQLAlchemy document Connection Pooling.

    import logging
    import azure.functions as func
    from sqlalchemy import create_engine
    import sqlalchemy.pool as pool
    
    engine = create_engine(
        'snowflake://{user}:{password}@{account}/'.format(
            user='<your_user_login_name>',
            password='<your_password>',
            account='<your_account_name>',
        )
    )
    
    def getconn():
        connection = engine.connect()
        return connection
    
    mypool = pool.QueuePool(getconn, max_overflow=10, pool_size=5)
    
    def main(req: func.HttpRequest) -> func.HttpResponse:
        try:
            conn = mypool.connect()
            # Do the code what you want, such as
            # cursor = conn.cursor()
            # cursor.execute("select foo")
            ....
        finally:
            conn.close()
        return func.HttpResponse(f"......")
    

    The explaination for the code above is that as below.

    1. Azure Functions for Python start up to compile the source code from top to bottom and load its bytecode to memory, then the mypool object will be invoked from memory following the main function be triggered until mypool removed from memory after idle long time.
    2. Next time to trigger the main function after idle, a new mypool object will be created and continous to do as above.