airflow: Snowflake Provider - Hook's support for not providing a region is broken when using SQLAlchemy

Apache Airflow Provider(s)

snowflake

Versions of Apache Airflow Providers

Versions 2.2.x (since https://github.com/apache/airflow/commit/0a37be3e3cf9289f63f1506bc31db409c2b46738).

Apache Airflow version

2.2.1

Operating System

Debian GNU/Linux 10 (buster)

Deployment

Other 3rd-party Helm chart

Deployment details

Bitnami Airflow Helm chart @ version 8.0.2

What happened

When connecting to Snowflake via SQLAlchemy using the Snowflake Hook, I get an error that the URL is not valid because my Snowflake instance is in US West 2 (Oregon) which means I don’t provide a region explicitly. Snowflake’s documentation says:

If the account is located in the AWS US West (Oregon) region, no additional segments are required and the URL would be xy12345.snowflakecomputing.com

The error is that xy12345..snowflakecomputing.com is not a valid URL (note the double-dot caused by the lack of a region).

What you expected to happen

I expect the connection to be successful.

How to reproduce

You can use the default snowflake connection if you have one defined and see this problem with the following one-liner:

python -c 'from airflow.providers.snowflake.hooks.snowflake import SnowflakeHook; SnowflakeHook().get_sqlalchemy_engine().connect()'

Anything else

Fortunately I imagine the fix for this is just to leave the region URL component out when region is None here: https://github.com/apache/airflow/commit/0a37be3e3cf9289f63f1506bc31db409c2b46738#diff-2b674ac999a5b938fe5045f6475b0c5cc76e4cab89174ac448a9e1d41a5c04d5R215.

Using version 2.1.1 of the Snowflake provider with version 2.2.1 of Airflow is currently a viable workaround so for now I am just avoiding the update to the provider.

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