Apache Superset Unable to create datasets with snowflake database

Bug description

I'm unable to create datasets in Superset from a Snowflake Database. However, I can query that same table in the SQL Lab with no issues (i.e., select * from cortex.daily_fact works as expected). This issue may not be reproducible locally (because this is a private snowflake instance). However, I was able to get the error locally by running: 1. git clone https://github.com/apache/superset && cd superset 2. echo "snowflake-sqlalchemy" >> ./docker/requirements-local.txt 3. docker compose -f docker-compose-image-tag.yml up --build 4. Setup my snowflake connection 5. Click on Datasets >> + Dataset 6. Selecting the schema and table that I wanted to create a dataset from

Screenshots/recordings

SQL Lab - No Problems Creating the Dataset - Problem The Table in Snowflake

Superset version

4.0.2

Python version

3.10

Node version

Not applicable

Browser

Safari

Additional context

Error in Logs

superset_app          | 2024-10-14 20:43:00,837:DEBUG:superset.stats_logger:[stats_logger] (incr) DatabaseRestApi.table_metadata.error
superset_app          | 2024-10-14 20:43:00,837:ERROR:flask_appbuilder.api:'NullType' object is not callable
superset_app          | Traceback (most recent call last):
superset_app          |   File "/usr/local/lib/python3.10/site-packages/flask_appbuilder/api/__init__.py", line 110, in wraps
superset_app          |     return f(self, *args, **kwargs)
superset_app          |   File "/app/superset/views/base_api.py", line 127, in wraps
superset_app          |     raise ex
superset_app          |   File "/app/superset/views/base_api.py", line 121, in wraps
superset_app          |     duration, response = time_function(f, self, *args, **kwargs)
superset_app          |   File "/app/superset/utils/core.py", line 1470, in time_function
superset_app          |     response = func(*args, **kwargs)
superset_app          |   File "/app/superset/utils/log.py", line 255, in wrapper
superset_app          |     value = f(*args, **kwargs)
superset_app          |   File "/app/superset/databases/api.py", line 742, in table_metadata
superset_app          |     table_info = get_table_metadata(database, table_name, schema_name)
superset_app          |   File "/app/superset/databases/utils.py", line 67, in get_table_metadata
superset_app          |     columns = database.get_columns(table_name, schema_name)
superset_app          |   File "/app/superset/models/core.py", line 839, in get_columns
superset_app          |     return self.db_engine_spec.get_columns(
superset_app          |   File "/app/superset/db_engine_specs/base.py", line 1341, in get_columns
superset_app          |     cast(list[SQLAColumnType], inspector.get_columns(table_name, schema))
superset_app          |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 497, in get_columns
superset_app          |     col_defs = self.dialect.get_columns(
superset_app          |   File "/usr/local/lib/python3.10/site-packages/snowflake/sqlalchemy/snowdialect.py", line 723, in get_columns
superset_app          |     schema_columns = self._get_schema_columns(connection, schema, **kw)
superset_app          |   File "<string>", line 2, in _get_schema_columns
superset_app          |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 55, in cache
superset_app          |     ret = fn(self, con, *args, **kw)
superset_app          |   File "/usr/local/lib/python3.10/site-packages/snowflake/sqlalchemy/snowdialect.py", line 592, in _get_schema_columns
superset_app          |     type_instance = col_type(**col_type_kw)
superset_app          | TypeError: 'NullType' object is not callable

Versions

snowflake-sqlalchemy

root@806c33d8de11:/app# pip freeze | grep snowflake
snowflake-connector-python==3.12.2
snowflake-sqlalchemy==1.6.1

superset

root@806c33d8de11:/app# superset version
Loaded your LOCAL configuration at [/app/docker/pythonpath_dev/superset_config.py]
2024-10-14 20:50:08,651:INFO:superset.utils.logging_configurator:logging was configured successfully
2024-10-14 20:50:08,654:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
/usr/local/lib/python3.10/site-packages/flask_limiter/extension.py:293: UserWarning: Using the in-memory storage for tracking rate limits as no storage was explicitly specified. This is not recommended for production use. See: https://flask-limiter.readthedocs.io#configuring-a-storage-backend for documentation about configuring the storage backend.
  warnings.warn(
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Superset 4.0.2
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Checklist

[X] I have searched Superset docs and Slack and didn't find a solution to my problem.
[X] I have searched the GitHub issue tracker and didn't find a similar bug report.
[X] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

Comment From: dosubot[bot]

I found a similar closed issue regarding a database driver error when connecting with Snowflake from Superset. The suggested solution was to use the connection string format snowflake://{user}:{password}@{account}.{region}/{database} by dropping the parts after '?' ^[1].

In your case, the error message 'NullType' object is not callable suggests that there might be an issue with how the Snowflake data types are being mapped to SQLAlchemy types. This could be related to the snowflake-sqlalchemy library's handling of nullable columns, which might be causing a NullType object to be incorrectly instantiated.

You might want to check if there are any updates or patches for the snowflake-sqlalchemy library that address this issue. Additionally, reviewing the data type mappings and ensuring that the Snowflake columns are correctly defined and compatible with SQLAlchemy could help resolve the problem.

To continue talking to Dosu, mention @dosu.

Comment From: chrishronek

Per the dosubot's recommendation, I tried connecting to Snowflake with a URI connection string, and I'm still getting the same error when attempting to create the Dataset.

Comment From: rusackas

Anyone still facing this? It's been quiet for > 6 months.

Comment From: chrisvnimbus

Does any table in the same schema by any chance contain a Snowflake vector type column? I've experienced troubles with those in Superset, to the point that no tables in a schema with a vector-column table could be imported as a dataset. It might be on SQL Alchemy's end, since the vector columns are a semi-recent addition with the whole Cortex stuff.

Will do a proper reproducable test next week.

Comment From: rusackas

Tempted to close this as inactive, but let's give it one last chance. On master (ad coming in Superset 6.0) we have SQLParse replaced by SQLGlot. I'm not sure if that has any significant impact here (perhaps not), but maybe @betodealmeida has some input/guidance on our options here, or perhaps @villebro.