Cannot create a Dataset of an Exasol Database View

[ApfelPresse]

Hey,
we tried to connect to our Exasol Database and followed the instructions on this page
https://superset.apache.org/docs/databases/exasol
Well, this did not work quite well, because of a missing dependency unixodbc-dev, which is needed by sqlalchemy-exasol.
We solved that with the steps below, but now we cannot create a Dataset of a Database View (Tables are working)

Expected results

A Dataset of an Exasol View

Actual results

It is not possible to create a Dataset of an Exasol Database View, but Tables are working

superset_app | Traceback (most recent call last):
superset_app | File "/app/superset/datasets/commands/create.py", line 51, in run
superset_app | dataset.fetch_metadata(commit=False)
superset_app | File "/app/superset/connectors/sqla/models.py", line 1503, in fetch_metadata
superset_app | new_columns = self.external_metadata()
superset_app | File "/app/superset/connectors/sqla/models.py", line 667, in external_metadata
superset_app | database=self.database, table_name=self.table_name, schema_name=self.schema,
superset_app | File "/app/superset/connectors/sqla/utils.py", line 47, in get_physical_table_metadata
superset_app | raise NoSuchTableError
superset_app | sqlalchemy.exc.NoSuchTableError: ()
superset_app | 2021-09-01 09:30:18,077:WARNING:superset.datasets.commands.create)
superset_app | Traceback (most recent call last):
superset_app | File "/app/superset/datasets/commands/create.py", line 51, in run
superset_app | dataset.fetch_metadata(commit=False)
superset_app | File "/app/superset/connectors/sqla/models.py", line 1503, in fetch_metadata
superset_app | new_columns = self.external_metadata()
superset_app | File "/app/superset/connectors/sqla/models.py", line 667, in external_metadata
superset_app | database=self.database, table_name=self.table_name, schema_name=self.schema,
superset_app | File "/app/superset/connectors/sqla/utils.py", line 47, in get_physical_table_metadata
superset_app | raise NoSuchTableError
superset_app | sqlalchemy.exc.NoSuchTableError: ()
superset_app | Error creating model DatasetRestApi: Dataset could not be created.
superset_app | Traceback (most recent call last):
superset_app | File "/app/superset/datasets/commands/create.py", line 51, in run
superset_app | dataset.fetch_metadata(commit=False)
superset_app | File "/app/superset/connectors/sqla/models.py", line 1503, in fetch_metadata
superset_app | new_columns = self.external_metadata()
superset_app | File "/app/superset/connectors/sqla/models.py", line 667, in external_metadata
superset_app | database=self.database, table_name=self.table_name, schema_name=self.schema,
superset_app | File "/app/superset/connectors/sqla/utils.py", line 47, in get_physical_table_metadata
superset_app | raise NoSuchTableError
superset_app | sqlalchemy.exc.NoSuchTableError: ()
superset_app |
superset_app | The above exception was the direct cause of the following exception:
superset_app |
superset_app | Traceback (most recent call last):
superset_app | File "/app/superset/datasets/api.py", line 251, in post
superset_app | new_model = CreateDatasetCommand(g.user, item).run()
superset_app | File "/app/superset/datasets/commands/create.py", line 65, in run
superset_app | raise DatasetCreateFailedError() from ex
superset_app | superset.datasets.commands.exceptions.DatasetCreateFailedError: Dataset could not be created.
superset_app | 2021-09-01 09:30:18,079:ERROR:superset.datasets.api:Error creating model DatasetRestApi: Dataset could not be created.
superset_app | Traceback (most recent call last):
superset_app | File "/app/superset/datasets/commands/create.py", line 51, in run
superset_app | dataset.fetch_metadata(commit=False)
superset_app | File "/app/superset/connectors/sqla/models.py", line 1503, in fetch_metadata
superset_app | new_columns = self.external_metadata()
superset_app | File "/app/superset/connectors/sqla/models.py", line 667, in external_metadata
superset_app | database=self.database, table_name=self.table_name, schema_name=self.schema,
superset_app | File "/app/superset/connectors/sqla/utils.py", line 47, in get_physical_table_metadata
superset_app | raise NoSuchTableError
superset_app | sqlalchemy.exc.NoSuchTableError: ()
superset_app |
superset_app | The above exception was the direct cause of the following exception:
superset_app |
superset_app | Traceback (most recent call last):
superset_app | File "/app/superset/datasets/api.py", line 251, in post
superset_app | new_model = CreateDatasetCommand(g.user, item).run()
superset_app | File "/app/superset/datasets/commands/create.py", line 65, in run
superset_app | raise DatasetCreateFailedError() from ex
superset_app | superset.datasets.commands.exceptions.DatasetCreateFailedError: Dataset could not be created.

Screenshots

How to reproduce the bug

Steps we did to install the exasol odbc driver:

First, we added the pip dep to the requirements-local.txt, as describes in the documentation (only 2.0.10 is working properly)

cat <<EOT >> ./docker/requirements-local.txt
sqlalchemy-exasol==2.0.10
EOT

Then we modified the Dockerfile, to add the missing dependency unixodbc-dev

cat <<EOT >> Dockerfile
FROM apache/superset:latest-dev
USER root
RUN apt-get update && apt-get install -y unixodbc-dev

RUN wget https://www.exasol.com/support/secure/attachment/135973/EXASOL_ODBC-7.1.0.tar.gz -O exasol_odbc.tar.gz
RUN tar -xzf exasol_odbc.tar.gz
RUN mv EXASOL_ODBC-7.* exasol_odbc
CMD /app/docker/docker-ci.sh
EOT

Configured the odbc.ini

cat <<EOT >> .odbc.ini
[ODBC Data Sources]
exasolution-uo2214lv2_64 = unixODBC 2.2.14 or later, libversion 2 (64bit)

[exasolution-uo2214lv2_64]
DRIVER = /app/EXASOL_ODBC-7.0.9/lib/linux/x86_64/libexaodbc-uo2214lv2.so
EXAHOST = <HOST>:8563
EXAUID = ETL_USER
EXAPWD = <PASS>
[exasolution-uo2214lv2_64-debug]
DRIVER = /app/EXASOL_ODBC-7.0.9/lib/linux/x86_64/libexaodbc-uo2214lv2.so
EXAHOST = <HOST>:8563
EXAUID = ETL_USER
EXAPWD = <PASS>
EXALOGFILE = /home/ubuntu/test/EXASOL_ODBC-7.0.9/exaodbc.log
LOGMODE = Debug Comm
EOT

Connection String

exa+pyodbc://USER:XXXXXXXXXX@IP:PORT/SCHEMA?driver=EXAODBC

Environment

we used the latest superset version and we use Exasol 7.0.10

• superset version: latest
• python version: we did not change anything
• node.js version: we did not change anything

[ApfelPresse]

I found a workaround, but that's not how it meant to be

added some lines

superset/connectors/sqla/models.py:667

def external_metadata(self) -> List[Dict[str, str]]:
  table_name = str(self.table_name)
  schema = str(self.schema)
  self.sql = f"SELECT * FROM {schema}.{table_name}"
  print(self.sql)
  if self.sql:
     return get_virtual_table_metadata(dataset=self)
  return get_physical_table_metadata(database=self.database, table_name=self.table_name, schema_name=self.schema,)

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[lkwg82]

Anybody interested?

[betodealmeida]

@hughhhh is working on an Exasol ticket, maybe he can take a look?

[Nicoretti]

Hi all,

(@ApfelPresse, @lkwg82, @betodealmeida, @hughhhh)

I am the new maintainer of the sqlalchemy-exasol which is used in apache-superset.

A colleague of mine (@tkilias) and I started digging a bit into this issue, to ensure this is not caused by sqlalchemy-exasol.
As of now, it is still not a 100% clear to us what the root cause is, but it surely looks like sqlalchemy-exasol is part of the problem.

While trying to reproduce and analyzing the issue, we could figure out the following things:

• The currently pinned version (>=2.1.0, <2.2.0) of sqlalchemy-exasol in the setup.py does not to work properly for adding any datasets with exasol.

• The following combination of versions seem to work properly for adding datasets with exasol.

  apache-superset==1.5.0[exasol]
  sqlalchemy-exasol==2.0.10
  

  @ApfelPresse from what I could see it also works for your view based scenario.
  (Can you validate, that it is working for your scenario too?)

• All version of sqlalchemy-exasol > 2.0.10, seem to have an issue when trying to add datasets from exasol.

  Until we can identify the root cause of this, it would make sense to:

  • Pin the sqlalchemy-exasol dependency to the version 2.0.10
  • Document it as known issue

  I am happy to create a PR with those changes, if you think, this is reasonable from the apache-superset project point of view.

  @lkwg82, @betodealmeida, @hughhhh would you want me to also create a tracking issue for this in the apache-superset project?

I hope this helps.

best
Nico

[Nicoretti]

As addition to my previous comment, @tkilias mentioned to me that it is likely not clear enough:

1. This issue is fixed by fix(dataset): create ES-View dataset raise exception #16623 #16624 (which is part of apache-superset 1.5.0
2. There is another Issue mentioned by @ApfelPresse and validated by us, that sqlalchemy-exasol > 2.0.10 does not work properly with apache superset

[rusackas]

If I'm understanding correctly it sounds like this issue is either fixed, or is out of our hands. Closing this as stale either way since it's been silent for so long, and we're trying to steer toward a more actionable Issues backlog. If people are still encountering this in current versions (currently 3.x) please open a new Issue or a PR to address the problem (or we can re-open this if the context is more helpful than a fresh issue).