Skip to content

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

Closed
#20509
Closed
Snowflake Provider - Hook's support for not providing a region is broken when using SQLAlchemy#20032
#20509
Assignees
mik-lajharishkrao
Labels
area:providersgood first issuekind:bugThis is a clearly a bugprovider:snowflakeIssues related to Snowflake provider

Description

@mattpolzin
mattpolzin
opened on Dec 4, 2021

Apache Airflow Provider(s)

snowflake

Versions of Apache Airflow Providers

Versions 2.2.x (since 0a37be3).

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: 0a37be3#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

Activity

  1. mattpolzin
    added
    area:providers
    kind:bugThis is a clearly a bug
    on Dec 4, 2021

  2. boring-cyborg commented on Dec 4, 2021

    @boring-cyborg
    boring-cyborgBot
    on Dec 4, 2021

    Thanks for opening your first issue here! Be sure to follow the issue template!

  3. potiuk
    added
    good first issue
    on Dec 4, 2021

  4. potiuk commented on Dec 4, 2021

    @potiuk
    potiuk
    on Dec 4, 2021
    Member

    Why don't you submit a PR with a fix @mattpolzin ? Sounds lke an eaasy fix.

  5. mattpolzin commented on Dec 4, 2021

    @mattpolzin
    mattpolzin
    on Dec 4, 2021
    Author

    I appreciate the encouragement! I've got a lot of projects in progress right now so I don't think I'll find time to get set up here as well.

  6. potiuk commented on Dec 4, 2021

    @potiuk
    potiuk
    on Dec 4, 2021
    Member

    I marked it as good-first-issue, so maybe someone else will pick it up then !

  7. mik-laj
    self-assigned this
    on Dec 4, 2021
  8. mik-laj
    added
    provider:snowflakeIssues related to Snowflake provider
    on Dec 4, 2021

  9. harishkrao commented on Dec 5, 2021

    @harishkrao
    harishkrao
    on Dec 5, 2021
    Contributor

    Is this available to pick up and implement?

  10. potiuk
    assigned
    harishkrao
    and unassigned
    mik-laj
    on Dec 5, 2021

  11. potiuk commented on Dec 5, 2021

    @potiuk
    potiuk
    on Dec 5, 2021
    Member

    Sure @harishkrao . Assigned you.

  12. potiuk
    assigned
    mik-laj
    on Dec 5, 2021

  13. potiuk commented on Dec 5, 2021

    @potiuk
    potiuk
    on Dec 5, 2021
    Member

    But maybe @mik-laj would like to do it firsst - so agree between yourselves ;)

  14. 12 remaining items

  15. harishkrao commented on Dec 12, 2021

    @harishkrao
    harishkrao
    on Dec 12, 2021
    Contributor

    I narrowed down the scope of our issue here. Once I am inside the virtual environment, and I try to import the same SQLAlchemy package, I run into the same error:
    `root@66ecbec1a1f0:/opt/airflow# python
    Python 3.8.12 (default, Nov 17 2021, 17:26:17)
    [GCC 8.3.0] on linux
    Type "help", "copyright", "credits" or "license" for more information.

    from snowflake.sqlalchemy import URL
    Traceback (most recent call last):
    File "", line 1, in
    File "/usr/local/lib/python3.8/site-packages/snowflake/sqlalchemy/init.py", line 25, in
    from . import base, snowdialect
    File "/usr/local/lib/python3.8/site-packages/snowflake/sqlalchemy/base.py", line 17, in
    from .custom_commands import AWSBucket, AzureContainer, ExternalStage
    File "/usr/local/lib/python3.8/site-packages/snowflake/sqlalchemy/custom_commands.py", line 14, in
    from sqlalchemy.sql.roles import FromClauseRole
    ModuleNotFoundError: No module named 'sqlalchemy.sql.roles'
    `
    It looks like there is a bug with SQLAlchemy 1.3.24, which is a dependency for Flask-AppBuilder. See 1621 and 953.

    If you have some time spare, can one of you please try to import this package in your local venv and let me know if you face the same issue? @potiuk @mik-laj
    This package is not being used anywhere else in the entire airflow repository.

  16. mik-laj commented on Dec 13, 2021

    @mik-laj
    mik-laj
    on Dec 13, 2021
    Member

    @harishkrao See: snowflakedb/snowflake-sqlalchemy#234
    To use URL class, you need to run:

    python -m pip install snowflake-sqlalchemy==1.2.4 sqlalchemy==1.3.24

    Unfortunately, Snowflake incorrectly marked the version requirements in one release of snowflake-sqlalchemy and after fixing the problem, they didn't mark the release as yanked, so pip downloads invalid dependency set.

  17. mik-laj commented on Dec 13, 2021

    @mik-laj
    mik-laj
    on Dec 13, 2021
    Member

    I pushed a fix: #20245

  18. harishkrao commented on Dec 13, 2021

    @harishkrao
    harishkrao
    on Dec 13, 2021
    Contributor

    Thanks very much for letting me know and for pushing the fix @mik-laj :) I will use the above versions.
    Edit: It worked 👍 Thanks a lot!

  19. harishkrao commented on Dec 13, 2021

    @harishkrao
    harishkrao
    on Dec 13, 2021
    Contributor

    @mik-laj any suggestions to overcome celery dependency issues?
    Searched the forum and online and found no open bugs related to it. Happens when running sudo ./breeze initialize-local-virtualenv --python 3.8 and the virtual environment has the celery version of 5.2.1.

    from celery import Celery, Task, states as celery_states ModuleNotFoundError: No module named 'celery'

  20. mik-laj commented on Dec 13, 2021

    @mik-laj
    mik-laj
    on Dec 13, 2021
    Member
    AIRFLOW_VERSION=main
PYTHON_VERSION="$(python --version | cut -d " " -f 2 | cut -d "." -f 1-2)"
CONSTRAINT_URL="https://raw.githubusercontent.com/apache/airflow/constraints-${AIRFLOW_VERSION}/constraints-${PYTHON_VERSION}.txt"
pip install celery --constraint "${CONSTRAINT_URL}"

    See: https://airflow.apache.org/docs/apache-airflow/stable/installation/installing-from-pypi.html

  21. harishkrao commented on Dec 13, 2021

    @harishkrao
    harishkrao
    on Dec 13, 2021
    Contributor

    My apologies for asking again, I am contributing to this repo for the first time. Will follow this and install the dependencies. Thank you for the help and patience @mik-laj.

  22. harishkrao commented on Dec 15, 2021

    @harishkrao
    harishkrao
    on Dec 15, 2021
    Contributor

    Just an info - I am working on this, my laptop crashed, so I have to setup the environment in another laptop and continue, will get back soon.

  23. harishkrao commented on Dec 20, 2021

    @harishkrao
    harishkrao
    on Dec 20, 2021
    Contributor

    Environment is configured, working on this, will get back in a day or two.

  24. harishkrao commented on Dec 22, 2021

    @harishkrao
    harishkrao
    on Dec 22, 2021
    Contributor

    Made changes for the fix. Preparing the PR now.

    @mik-laj a couple points for clarification. I added a unit test for the fix and it passed. I tried using the snowflake.sqlalchemy.URL, but during running pytest, it caused issues with generating the URL with nested dict values for session_parameters, so I reverted it to use string format, as is currently being used in the mainline branch.

  25. mik-laj commented on Dec 26, 2021

    @mik-laj
    mik-laj
    on Dec 26, 2021
    Member

    @harishkrao Do you need help with preparing a PR?

  26. harishkrao commented on Dec 26, 2021

    @harishkrao
    harishkrao
    on Dec 26, 2021
    Contributor

    @mik-laj Thank you for offering help, had to attend to something else until now. I will follow the steps in the guide and get back to you soon.

  27. harishkrao commented on Dec 27, 2021

    @harishkrao
    harishkrao
    on Dec 27, 2021
    Contributor

    Just opened a PR, thank you for reviewing @mik-laj!

  28. potiuk
    closed this as completedin #20509on Dec 28, 2021

  29. new-php commented on Mar 25, 2025

    @new-php
    new-php
    on Mar 25, 2025

    After pulling my hair out for days with this exact Snowflake URL issue, this post was a lifesaver! That double-dot bug is so subtle but completely breaks everything. Here's what worked for me:

    Quick Fix: Confirmed rolling back to snowflake-provider 2.1.1 works as a temporary solution (pip install apache-airflow-provider-snowflake==2.1.1)

Alternative Approach: Found that explicitly setting region=us-west-2 in the connection extras (even though Oregon shouldn't need it) also resolves it

Pro Tip: For anyone setting up fresh, make sure to use the full connection string format:
account.user@xy12345.us-west-2.snowflakecomputing.com
(Yes, even for Oregon - seems counterintuitive but prevents issues)

    Question for OP: Did you find any other workarounds before discovering the provider version fix? I'm curious if modifying the hook directly would've worked.

    — DataPipesDreamer (Running Airflow 2.3 with Snowflake since 2021)

    P.S. For new users hitting this, don't forget to pip install apache-airflow [google,snowflake] with the latest constraints file - dependency management is half the battle!
    New chat

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

area:providersgood first issuekind:bugThis is a clearly a bugprovider:snowflakeIssues related to Snowflake provider

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions