Snowflake-sqlalchemy connector no longer working.

[nrav1360]

Previously, was able to connect the snowflake database to my superset instance. However, all of the charts I have (that used datasets from snowflake) now have errors that didn't exist before.

How to reproduce the bug

1. Install Superset via Docker.
2. Add snowflake driver by adding snowflake-sqlalchemy<=1.2.4 to requirements-local.txt.
3. Connect to snowflake using credentials.
4. Attempt to create a chart using an imported dataset from snowflake.

Expected results

A working chart.

Actual results

Errors involving a missing NamedTuple.

Screenshots

Environment

• browser type and version: Safari 15.1
• superset version: 2.0.0
• python version: Python 3.10.4
• node.js version: v12.22.9
• any feature flags active: None

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• [✔️ ] I have checked the superset logs for python stacktraces and included it here as text if there are any.
• [✔️ ] I have reproduced the issue with at least the latest released version of superset.
• [✔️ ] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

[wiktor2200]

It exists on 1.5.0 and 1.5.1 as well. It seems to be external bug: https://github.com/snowflakedb/snowflake-connector-python/issues/1206

[wiktor2200]

I've fixed it.

The problem was caused by external source (releasing of snowflake-connector-python ver. 2.7.10 https://github.com/snowflakedb/snowflake-connector-python/releases today).

For Superset <-> Snowflake snowflake-sqlalchemy 1.2.4 python package is used, which is quite old now (but it's only one version supported by Superset): https://superset.apache.org/docs/databases/snowflake/#snowflake and they are working on migration to new one.

Even having package version hardcoded: snowflake-sqlalchemy==1.2.4 in helm definitions didn't help. Becuase snowflake-sqlalchemy package has requirements defined in python script here: snowflake-connector-python<3.0.0: https://github.com/snowflakedb/snowflake-sqlalchemy/blob/666843d320315d2fd2b5c4129b929af20669008d/setup.py#L36, so it's downloading the newest 2.7.10 version anyways (fulfilling the condiition <3.0.0), but this version is not compatible with snowflake-sqlalchemy==1.2.4 and requires additional package: typing-extensions>=4.3.0.

So the fix here was to force install previous version of snowflake-connector-python==2.7.9 (which still <3.0.0), as well as typing-extensions==4.3.0 so all requirements were fulfilled and snowflake-sqlalchemy==1.2.4 doesn't download newest incompatible version.

typing-extensions==4.3.0
snowflake-connector-python==2.7.9
snowflake-sqlalchemy==1.2.4

[EamonKeane]

Thanks a lot @wiktor2200 ! I struggled through this issue. It appears that it's been partially fixed in 2.7.11.

https://github.com/snowflakedb/snowflake-connector-python/issues/1206#issuecomment-1213487028

Like you mention, it looks like the pinning for typing-extensions could be relaxed since it appears to work above v4. This script works for me. Is there a more elegant/maintainable way of creating a production image?

#!/usr/bin/env bash
cd /tmp || exit
git clone https://github.com/apache/superset.git --branch=2.0.0
echo "snowflake-sqlalchemy==1.2.4" > superset/requirements/requirements-local.txt
cd superset || exit
# linux sed
sed -i 's/typing-extensions>=3.10, <4/typing-extensions>=4.3, <5/' setup.py
sed -i 's/typing-extensions==3.10.0.0/typing-extensions==4.3.0/' requirements/base.txt
docker build --tag apache-superset:2.0.0-dev --target dev .

And also just to mention they appear to have upgraded to sqlalchemy 1.4, and so the latest version of snowflake driver (1.3.4) appears to work. So if typing-extensions was modified, the helm chart would work out of the box again I think.

#!/usr/bin/env bash
cd /tmp || exit
git clone https://github.com/apache/superset.git --branch=master
echo "snowflake-sqlalchemy==1.3.4" > superset/requirements/requirements-local.txt
cd superset || exit
git checkout 151795663bbdc8f52ab046a2dc4aa148b2efba51
sed -i 's/typing-extensions>=3.10, <4/typing-extensions>=4.3, <5/' setup.py
sed -i 's/typing-extensions==3.10.0.0/typing-extensions==4.3.0/' requirements/base.txt
docker build --tag apache-superset:2.0.0-dev --target dev .

[alldoami]

Can these reqs get considered?

typing-extensions==4.3.0
snowflake-connector-python==2.7.9
snowflake-sqlalchemy==1.2.4

After pinning these packages, my errors went away but Superset is requiring a different version for typing-extensions. I think this is the only way this will work with the right version of the snowflake packages.

pkg_resources.DistributionNotFound: The 'typing-extensions<4,>=3.10' distribution was not found and is required by apache-superset

[wiktor2200]

Hello @alldoami! Finally I've also used requirements without: typing-extensions, when I posted my first reply it worked, but then I changed some other dependencies and I just used these two:

snowflake-connector-python==2.7.9
snowflake-sqlalchemy==1.2.4

@EamonKeane it's better idea to just create Dockerfile and extend your image (it's recommended in docs here: https://hub.docker.com/r/apache/superset) in section: How to extend this image so you just clone pre-build docker (master or specific version) and then extend it by running (or anything you need)

RUN pip install \
         snowflake-connector-python==2.7.9 \
         snowflake-sqlalchemy==1.2.4

[alldoami]

yup I've been using my own docker image! I'll try rebuilding with just these two requirements instead of typing-extensions

[alldoami]

Hmm weird... I pinned just these two packages, but I am still getting

│ superset-init-db pkg_resources.DistributionNotFound: The 'typing-extensions<4,>=3.10' distribution was not found and is required by apache-superset                            │

Which version of typing-extensions are you using @wiktor2200 ?

[alldoami]

This is my Dockerfile:

FROM apache/superset
USER root
RUN mkdir -p /home/superset
ENV PYTHONPATH=/home/superset:$PYTHONPATH
VOLUME /home/superset
RUN pip install psycopg2-binary==2.9.1
RUN pip install snowflake-sqlalchemy>=1.2.4
RUN pip install auth0-python==3.19.0
RUN pip install sqlalchemy-databricks>=0.2.0
RUN pip install snowflake-connector-python==2.7.9
RUN pip install snowflake-sqlalchemy==1.2.4
USER superset

[wiktor2200]

Maybe you should try put all pip install commands with one RUN statement. I'm running this one:

RUN rm -rf /var/lib/apt/lists/* && \
    pip install \
        Authlib==1.0.1 \
        flask-oidc==1.3.0 \
        psycopg2-binary==2.9.1 \
        snowflake-connector-python==2.7.9 \
        snowflake-sqlalchemy==1.2.4 \
        redis==3.5.3

Maybe there are some dependencies which are not fully covered. What is more, I've noticed that order of installing packages could matter.

[alldoami]

I reordered the packages and pinned typing-extensions==3.10.0 but now get a pyarrow dependency problem, it looks like one of these packages is relying on pyarrow==9.0.0 but superset requires 6.0.1

[alldoami]

Looks like sqlalchemy-databricks had some higher pyarrow req, so this now works for me!

RUN pip install auth0-python==3.19.0
RUN pip install psycopg2-binary==2.9.1
RUN pip install snowflake-connector-python==2.7.9
RUN pip install snowflake-sqlalchemy==1.2.4
RUN pip install sqlalchemy-databricks==0.2.0
RUN pip install typing-extensions==3.10.0
RUN pip install databricks-sql-connector==2.0.2
RUN pip install pyarrow==6.0.1

[asemprini]

Just in case someone else finds this thread looking why snowflake connector is not working when installing with docker. I was getting this error:

superset_app | superset.databases.commands.exceptions.DatabaseTestConnectionFailedError: [SupersetError(message='(builtins.NoneType) None\n(Background on this error at: https://sqlalche.me/e/14/dbapi)', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Snowflake', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

The problem was I was trying to use the following connection string: https://superset.apache.org/docs/databases/snowflake/

In the end, using the above libraries mentioned by @wiktor2200, I was able to make it work but selecting the snowflake data source and not using the above connection string. Also, another comment: The DATABASE NAME, is not e.g. xy12345.us-east-2.aws, it is the ACTUAL database name like "my_db". And the ACCOUNT below is where you put xy12345.us-east-2.

Just in case this might help someone else with a similar issue.

[ggmblr]

Hi,

we are running into the same issues as everyone else here. Same error code as @asemprini right now.

Currently we have the python packages that @wiktor2200 posted inside our ./docker/requirements-local.txt file. But still it doesn't connect. Could someone that has a working connection explain in detail what files were changed and how.

Thanks in advance

[EamonKeane]

@ggmblr fwiw, this is what I have working, just extending the Dockerfile wasn't working for me. Putting this in a Makefile and then doing a docker build should work and lets you use the latest snowflkae-sqlalchemy version which has additional datatypes like Geography.

SHELL := /bin/bash
SUPERSET_COMMIT_HASH ?= c3f841713989634ef4ba522b6a89e04ff89e2c0d
SUPERSET_FOLDER ?= my_superset

.PHONY: superset
superset:
	rm -rf ${SUPERSET_FOLDER}
	git clone --single-branch https://github.com/apache/superset.git ${SUPERSET_FOLDER}
	cd ${SUPERSET_FOLDER} && \
	git checkout ${SUPERSET_COMMIT_HASH} && \
	echo "snowflake-sqlalchemy==1.4.1" > requirements/requirements-local.txt && \
	sed 's/typing-extensions>=3.10, <4/typing-extensions>=4.3, <5/' setup.py > /tmp/file.$$ && mv /tmp/file.$$ setup.py && \
	sed 's/typing-extensions==3.10.0.0/typing-extensions==4.3.0/' requirements/base.txt > /tmp/file.$$ && mv /tmp/file.$$ requirements/base.txt

[ggmblr]

Hey @EamonKeane , after running your makefile, which docker command do you execute?

Is it docker-compose -f docker-compose-non-dev.yml up ?

Also what are the changes in that commit that fixes the issues?

[EamonKeane]

@ggmblr

Command below. Newer versions of snowflake-sqlalchemy rely on newer versions of the typing-extensions library. The changes just bump that up so there's no conflicts. It doesn't break anything, I think at some point someone will bump them in the main superset repo and then none of this nonsense will be required.

.PHONY: build_image
build_image: superset
	@$(info docker version)
	@docker version
	@docker build -t superset-test --target dev ${SUPERSET_FOLDER}

[danbtl]

Quick note here for anyone trying to get Superset 2.0.1 running via docker-compose with the Snowflake connector:

Create a file docker/requirements-local.txt with the following content:

snowflake-connector-python==2.7.9
snowflake-sqlalchemy==1.2.4

Then restart the docker containers.

As mentioned by others here, it does not work out of the box with newer versions of snowflake-sqlalchemy.

[rusackas]

Sounds like this has all been resolved. If you're having similar problems in Superset 3.x, please open a new issue with updated context.