Thread
#prefect-community
    a

    Arkady Kleyner

    2 years ago
    hello team prefect! We are reviewing snowflake task and are finding a number of limitations. Having to connect each time creates quite a bit of overhead and we are unable to run multisql statements as per https://docs.snowflake.com/en/user-guide/python-connector-api.html#
    Zachary Hughes

    Zachary Hughes

    2 years ago
    Hi Arkady, thank you for the feedback! That's a solid point-- do you mind creating a Github issue for the improvements you'd like? That way we can track and prioritize enhancements.
    a

    Arkady Kleyner

    2 years ago
    sure
    Braun Reyes

    Braun Reyes

    2 years ago
    keep in mind that creating a connection each time does fall inline with tasks being idempotent units of execution. However you can certainly look into just using the task decorator in a function to get what you need.
    Jeremiah

    Jeremiah

    2 years ago
    We have a roadmap item that we are hoping to address this quarter which will make it possible to share objects like connections among different tasks, as long as you use an executor that supports a shared memory space. Hope to update with more details soon!
    a

    Arkady Kleyner

    2 years ago
    @Braun Reyes thanks - agree, we are working on reducing overhead for tasks and connect/disconnect times. As per @Jeremiah it would help to have shared object for connection among different tasks.
    @Jeremiah second part of this is more important as it allows multiple sql statements to be executed on a single call. Current implementation uses snowflake
    executed = cursor.execute(query, params=data
    )
    line 101 in task code. Based on that call only a single sql statement will execute. Snowflake API allows to pass multiple sql statements as part of a single call.
    Jeremiah

    Jeremiah

    2 years ago
    Hi @Arkady Kleyner - we don’t use Snowflake at Prefect, so I’m not too familiar with their Python API (these tasks were written by members of the Prefect community!). However, it sounds like what you’re describing would certainly be an improvement. To make the change immediately, you can just copy / paste this code into your own custom task class and adjust that line as necessary. If it turns out to be straightforward, we’d definitely welcome that change as a PR so others could benefit as well!