Session_parameter AUTOCOMMIT is not set in Snowflake connection

[Remmertmuller]

Apache Airflow Provider(s)

snowflake

Versions of Apache Airflow Providers

apache-airflow-providers-amazon==7.1.0
apache-airflow-providers-celery==3.1.0
apache-airflow-providers-cncf-kubernetes==5.1.1
apache-airflow-providers-common-sql==1.3.3
apache-airflow-providers-docker==3.4.0
apache-airflow-providers-elasticsearch==4.3.3
apache-airflow-providers-ftp==3.3.0
apache-airflow-providers-google==8.8.0
apache-airflow-providers-grpc==3.1.0
apache-airflow-providers-hashicorp==3.2.0
apache-airflow-providers-http==4.1.1
apache-airflow-providers-imap==3.1.1
apache-airflow-providers-jdbc==3.3.0
apache-airflow-providers-microsoft-azure==5.1.0
apache-airflow-providers-microsoft-mssql==3.3.2
apache-airflow-providers-mysql==4.0.0
apache-airflow-providers-odbc==3.2.1
apache-airflow-providers-oracle==3.6.0
apache-airflow-providers-postgres==5.4.0
apache-airflow-providers-redis==3.1.0
apache-airflow-providers-sendgrid==3.1.0
apache-airflow-providers-sftp==4.2.1
apache-airflow-providers-slack==7.2.0
apache-airflow-providers-snowflake==4.0.2
apache-airflow-providers-sqlite==3.3.1
apache-airflow-providers-ssh==3.4.0

Apache Airflow version

2.5.1

Operating System

Debian GNU/Linux 11 (bullseye)

Deployment

Other Docker-based deployment

Deployment details

No response

What happened

We use the SnowflakeHook to execute stored procedures and then fetch the result for further logic. On the hook we call the get_first() method from the DbApiHook. When upgrading the Snowflake provider to version 4.0.2 we got the same errors as explained in this #issue.

It is made possible to configure session_parameters on the connection or the hook but these are not being set when the connection is made or they overwritten when the run() method on the SnowflakeHook is executed.

What you think should happen instead

I would like to see that session_parameters (in my case AUTOCOMMIT) are set when creating the connection or instantiating the hook. And that when AUTOCOMMIT is set in the session_parameters, then the run() method on the hook should not set it back to the default value False.

How to reproduce

First I tested putting a autocommit session parameter on the connection. I made a snowflake connection with in the extra's field {"session_parameters": {"AUTOCOMMIT": "True"}}. Then when I run the following task:

@task(task_id="snowflake_test_autocommit")
def snowflake_test_autocommit():
    hook = SnowflakeHook("snowflake_autocommit")
    return hook.get_first(sql="SELECT '*** statement successfully executed'")

I see in the logs that autocommit is set to False:

[2023-03-22, 10:35:36 UTC] {{cursor.py:727}} INFO - query: [ALTER SESSION SET autocommit=False]

The same happens when setting the session parameter on instantiating the hook:

@task(task_id="snowflake_test_autocommit_on_hook")
def snowflake_test_autocommit_on_hook():
    hook = SnowflakeHook("snowflake", session_parameters={"AUTOCOMMIT": True})
    return hook.get_first(sql="SELECT '*** statement successfully executed'")

Anything else

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.

[potiuk]

Yep. Good idea. It would be nice to add it as a feature to not override the autocommit session parameter. simply checking if it is defined and override it with the value of the AUTOCOMMIT - specifically for snowflake, because it is snowflake-specific.

Would you maybe want to contribute it? Does not seem like a big change and it is the best way to make sure it will be implemented quickly (I understand you would like to use it to migrate to latest provider) - otherwise it will have to wait for someone to pick it up (marked it as good-first-issue).

[Remmertmuller]

Okay! Yes I am willing to contribute.

There will be three places where autocommit can be configured, so I suggest the following precedence:

1. value given to run() => if run is called with autocommit explicitly set to True or False, then that is what it should do.
2. value from the session parameter from instantiating the hook.
3. value from the session parameter from the connection.
   If autocommit is not set in any of these three places, default behaviour remains False.

When scanning the code I could not find where any of the session parameters are set. Session parameters are given to connect() on the SnowflakeConnection, but this function does not do anything with session parameters. However, this change will still be needed because we need to make sure that the default value from run() does not overwrite autocommit from the session parameters anyway.

[potiuk]

I guess debugging/deeging deeply is needed. My suggestoin is to run through the tests that are written for the operator with the debugger step-by-step and finding out where to fix it. This is the best way to self-learn. If you follow the CONTRIBUTING.rst there are instructions on how to setup the dev env, your favourite IDEs etc. and starting from running/debugging existing unit tests (which we have plenty) is the best way to start.