search

apache / superset

Apache Superset is a Data Visualization and Data Exploration Platform
https://superset.apache.org/
Apache License 2.0
63.08k stars 13.97k forks source link

Can't connect to Oracle DB #20371

Open edgui-appolonicorreia opened 2 years ago

edgui-appolonicorreia commented 2 years ago

I'm having an issue to connect to an Oracle DB. I've already followed the steps to create a requirements-local.txt file into docker/ cx_Oracle package, but still not working.

How to reproduce the bug

  1. touch ./docker/requirements-local.txt
  2. echo "cx_Oracle" >> ./docker/requirements-local.txt
  3. docker-compose build --force-rm
  4. docker-compose up

Expected results

I was expecting to be able to connect into a Oracle database.

Actual results

Actually I got this error message: ERROR: (builtins.NoneType) None (Background on this error at: http://sqlalche.me/e/13/dbapi)

Screenshots

image

Environment

(please complete the following information):

Checklist

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

Additional context

I'm using an AWS Oracle instance to test. I've checked the logs of the superset_worker superset_worker_beat containers, and seems that the cx-Oracle lib was successfully installed. image

The superset_app container log: image

I'm using the following string connection: oracle://<username>:<password>@<hostname>:<port> from https://superset.apache.org/docs/databases/oracle/

cofin commented 2 years ago

I'm working through a similar problem now. Did you install the Oracle instant client into the Docker container or system you are running superset from? cx_oracle requires either the instant client or full Oracle client. The recently released python-oracledb no longer has this requirement, so installation should get easier in the near future.

I'm using the following in my Docker container to install the latest SQL client:


RUN mkdir -p /usr/share/oracle/network/admin \
    && cd /usr/share/oracle \
    && apt-get update -y \
    && apt-get install -y --no-install-recommends git ca-certificates wget build-essential unzip curl libaio1 libaio-dev \
    && rm -rf /var/lib/apt/lists/* \
    && apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false \
    && wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basiclite-linuxx64.zip \
    && unzip instantclient-basiclite-linuxx64.zip \
    && rm -f instantclient-basiclite-linuxx64.zip \
    && cd  /usr/share/oracle/instantclient* \
    && rm -f *jdbc* *occi* *mysql* *README *jar uidrvci genezi adrci \
    && echo  /usr/share/oracle/instantclient* > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig \
    && echo 'local_xe=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xepdb1)))' > /usr/share/oracle/network/admin/tnsnames.ora```
cofin commented 2 years ago

One additional note, in case it helps.

I had to connect using a TNS formatted string instead of the easy connect format: oracle+cx_oracle://username:password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_or_scan_address)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db_service_name)))

EDIT: Updated connect string to reference the Oracle driver correctly.

edgui-appolonicorreia commented 2 years ago

One additional note, in case it helps.

I had to connect using a TNS formatted string instead of the easy connect format: cx_oracle+oracle://username:password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_or_scan_address)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db_service_name)))

Unfortunately didn't work! I got the same issue.

pikachurus commented 2 years ago

in my case helped at first install oracleclient 11.2, then remove config /etc/ld.so.conf.d/oracle-instantclient.conf (in container) and install oracleclient 21.6

docker exec [container-id] dpkg -i oracle-instantclient11.2-basic_11.2.0.4.0-2_amd64.deb docker exec [container-id] rm /etc/ld.so.conf.d/oracle-instantclient.conf docker exec [container-id] dpkg -i oracle-instantclient-basic_21.6.0.0.0-2_amd64.deb

and i don't know why

arsenyyyyy commented 2 years ago

in case anyone is interested, it might help: just install instantclient following this instructions: https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html#oracle-instant-client-rpms

edgui-appolonicorreia commented 2 years ago

I'm working through a similar problem now. Did you install the Oracle instant client into the Docker container or system you are running superset from? cx_oracle requires either the instant client or full Oracle client. The recently released python-oracledb no longer has this requirement, so installation should get easier in the near future.

I'm using the following in my Docker container to install the latest SQL client:

RUN mkdir -p /usr/share/oracle/network/admin \
    && cd /usr/share/oracle \
    && apt-get update -y \
    && apt-get install -y --no-install-recommends git ca-certificates wget build-essential unzip curl libaio1 libaio-dev \
    && rm -rf /var/lib/apt/lists/* \
    && apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false \
    && wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basiclite-linuxx64.zip \
    && unzip instantclient-basiclite-linuxx64.zip \
    && rm -f instantclient-basiclite-linuxx64.zip \
    && cd  /usr/share/oracle/instantclient* \
    && rm -f *jdbc* *occi* *mysql* *README *jar uidrvci genezi adrci \
    && echo  /usr/share/oracle/instantclient* > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig \
    && echo 'local_xe=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xepdb1)))' > /usr/share/oracle/network/admin/tnsnames.ora```

I tried that approach, adding those lines at the Dockerfile and running docker compose -f docker-compose-non-dev.yml up --build, but I got a new error message (hahahahahahaahahaha) when I tried to connect to the DB:

superset_app          | 127.0.0.1 - - [16/Jul/2022:17:48:23 +0000] "GET /health HTTP/1.1" 200 2 "-" "curl/7.74.0"
superset_app          | CommandException
superset_app          | Traceback (most recent call last):
superset_app          |   File "/app/superset/databases/commands/test_connection.py", line 77, in run
superset_app          |     engine = database.get_sqla_engine()
superset_app          |   File "/app/superset/utils/memoized.py", line 50, in __call__
superset_app          |     value = self.func(*args, **kwargs)
superset_app          |   File "/app/superset/models/core.py", line 401, in get_sqla_engine
superset_app          |     raise self.db_engine_spec.get_dbapi_mapped_exception(ex)
superset_app          |   File "/app/superset/models/core.py", line 399, in get_sqla_engine
superset_app          |     return create_engine(sqlalchemy_url, **params)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/__init__.py", line 525, in create_engine
superset_app          |     return strategy.create(*args, **kwargs)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/strategies.py", line 61, in create
superset_app          |     entrypoint = u._get_entrypoint()
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/url.py", line 172, in _get_entrypoint
superset_app          |     cls = registry.load(name)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 277, in load
superset_app          |     raise exc.NoSuchModuleError(
superset_app          | sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:cx_Oracle.oracle
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 "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1516, in full_dispatch_request
superset_app          |     rv = self.dispatch_request()
superset_app          |   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1502, in dispatch_request
superset_app          |     return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 89, in wraps
superset_app          |     return f(self, *args, **kwargs)
superset_app          |   File "/app/superset/views/base_api.py", line 113, in wraps
superset_app          |     raise ex
superset_app          |   File "/app/superset/views/base_api.py", line 110, in wraps
superset_app          |     duration, response = time_function(f, self, *args, **kwargs)
superset_app          |   File "/app/superset/utils/core.py", line 1533, in time_function
superset_app          |     response = func(*args, **kwargs)
superset_app          |   File "/app/superset/utils/log.py", line 244, in wrapper
superset_app          |     value = f(*args, **kwargs)
superset_app          |   File "/app/superset/views/base_api.py", line 83, in wraps
superset_app          |     return f(self, *args, **kwargs)
superset_app          |   File "/app/superset/databases/api.py", line 709, in test_connection
superset_app          |     TestConnectionDatabaseCommand(item).run()
superset_app          |   File "/app/superset/databases/commands/test_connection.py", line 124, in run
superset_app          |     raise DatabaseTestConnectionDriverError(
superset_app          | superset.databases.commands.exceptions.DatabaseTestConnectionDriverError: Could not load database driver: BaseEngineSpec
superset_app          | 2022-07-16 17:48:25,194:WARNING:superset.views.base:CommandException
superset_app          | Traceback (most recent call last):
superset_app          |   File "/app/superset/databases/commands/test_connection.py", line 77, in run
superset_app          |     engine = database.get_sqla_engine()
superset_app          |   File "/app/superset/utils/memoized.py", line 50, in __call__
superset_app          |     value = self.func(*args, **kwargs)
superset_app          |   File "/app/superset/models/core.py", line 401, in get_sqla_engine
superset_app          |     raise self.db_engine_spec.get_dbapi_mapped_exception(ex)
superset_app          |   File "/app/superset/models/core.py", line 399, in get_sqla_engine
superset_app          |     return create_engine(sqlalchemy_url, **params)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/__init__.py", line 525, in create_engine
superset_app          |     return strategy.create(*args, **kwargs)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/strategies.py", line 61, in create
superset_app          |     entrypoint = u._get_entrypoint()
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/url.py", line 172, in _get_entrypoint
superset_app          |     cls = registry.load(name)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 277, in load
superset_app          |     raise exc.NoSuchModuleError(
superset_app          | sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:cx_Oracle.oracle
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 "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1516, in full_dispatch_request
superset_app          |     rv = self.dispatch_request()
superset_app          |   File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1502, in dispatch_request
superset_app          |     return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
superset_app          |   File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 89, in wraps
superset_app          |     return f(self, *args, **kwargs)
superset_app          |   File "/app/superset/views/base_api.py", line 113, in wraps
superset_app          |     raise ex
superset_app          |   File "/app/superset/views/base_api.py", line 110, in wraps
superset_app          |     duration, response = time_function(f, self, *args, **kwargs)
superset_app          |   File "/app/superset/utils/core.py", line 1533, in time_function
superset_app          |     response = func(*args, **kwargs)
superset_app          |   File "/app/superset/utils/log.py", line 244, in wrapper
superset_app          |     value = f(*args, **kwargs)
superset_app          |   File "/app/superset/views/base_api.py", line 83, in wraps
superset_app          |     return f(self, *args, **kwargs)
superset_app          |   File "/app/superset/databases/api.py", line 709, in test_connection
superset_app          |     TestConnectionDatabaseCommand(item).run()
superset_app          |   File "/app/superset/databases/commands/test_connection.py", line 124, in run
superset_app          |     raise DatabaseTestConnectionDriverError(
superset_app          | superset.databases.commands.exceptions.DatabaseTestConnectionDriverError: Could not load database driver: BaseEngineSpec

And I used this syntax string connection: cx_oracle+oracle://username:password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_or_scan_address)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db_service_name)))

Any guesses?

cofin commented 2 years ago

It looks like I gave you the incorrect connect string format. According to the docs, it's oracle+cx_oracle instead of cx_oracle+oracle. Sorry about that.

Here is the documentation in case you need to adjust additional options.

Jika97 commented 2 years ago

Hello,

I have the same issue on an Apache Superset 2.0.0 install on Ubuntu 20.04 via docker-compose.

My Oracle database is on another server. I installed everything cx_Oracle and Oracle Instant Client on the server, but I have the following error:

ERROR: (builtins.NoneType) None (Background on this error at: https://sqlalche.me/e/14/dbapi)

My SQL ALCHEMY URI : oracle+cx_oracle://user:password%3A%29%29@X.X.X.X:1521/?service_name=SERVICE_NAME

When I run a small python connection script, however I manage to connect to the database and get my data.

import cx_Oracle
from sqlalchemy import create_engine

connection = cx_Oracle.connect(user="user", password="password:)!",
                               dsn="x.x.x.x/SERVICENAME")

cursor = connection.cursor()
cursor.execute("""
        SELECT id2, ub
        FROM data
        """,
)
for id2, ub in cursor:
    print("Values:", id2, ub)

Could someone help me, please ?

nmabhinandan commented 1 year ago

any updates?

edgui-appolonicorreia commented 1 year ago

any updates?

Unfortunately not :/

There is a long time since I tested it . I need to try it again.

fabiomilson commented 1 year ago

how wonderful apache is.... in my case it was because there was the @ character in the password :)

Example: cx_oracle+oracle://username:pass@word@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_or_scan_address)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db_service_name)))

Due to "@" the connection does not work...

----------- EDIT -------------- Using escape character %40 instead of @ is working

Narender-007 commented 1 year ago

hello everyone ,

i got this error ERROR: (builtins.NoneType) None [SQL: (cx_Oracle.DatabaseError) DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help (Background on this error at: https://sqlalche.me/e/14/4xp6)] (Background on this error at: https://sqlalche.me/e/14/dbapi)

please can you help it anyone.

amarnath-tiwari-nagarro commented 1 year ago

hello everyone ,

i got this error ERROR: (builtins.NoneType) None [SQL: (cx_Oracle.DatabaseError) DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help (Background on this error at: https://sqlalche.me/e/14/4xp6)] (Background on this error at: https://sqlalche.me/e/14/dbapi)

please can you help it anyone.

same error for me as well. Can anybody help on this.

antiwari001 commented 1 year ago

Found a solution to above problem, used docker image apache/superset to build another custom container and that resolved this issue. PSB Dockerfile which is working fine.

FROM apache/superset USER root RUN pip install cx_Oracle RUN apt update RUN apt -y install alien libaio1 wget RUN wget https://download.oracle.com/otn_software/linux/instantclient/185000/oracle-instantclient18.5-basiclite-18.5.0.0.0-3.x86_64.rpm RUN alien -i oracle-instantclient18.5-basiclite-18.5.0.0.0-3.x86_64.rpm RUN sh -c 'echo /usr/lib/oracle/18.5/client64/lib/ > /etc/ld.so.conf.d/oracle.conf' RUN ldconfig USER superset

Narender-007 commented 1 year ago

Found a solution to above problem, used docker image apache/superset to build another custom container and that resolved this issue. PSB Dockerfile which is working fine.

FROM apache/superset USER root RUN pip install cx_Oracle RUN apt update RUN apt -y install alien libaio1 wget RUN wget https://download.oracle.com/otn_software/linux/instantclient/185000/oracle-instantclient18.5-basiclite-18.5.0.0.0-3.x86_64.rpm RUN alien -i oracle-instantclient18.5-basiclite-18.5.0.0.0-3.x86_64.rpm RUN sh -c 'echo /usr/lib/oracle/18.5/client64/lib/ > /etc/ld.so.conf.d/oracle.conf' RUN ldconfig USER superset

image

i am added that thing but getting same error

mtk12 commented 1 year ago

pip install cx_Oracle mkdir -p /opt/oracle cd /opt/oracle wget https://download.oracle.com/otn_software/linux/instantclient/1920000/instantclient-basic-linux.x64-19.20.0.0.0dbru.zip unzip instantclient-basic-linux.x64-19.20.0.0.0dbru.zip apt-get install -y --no-install-recommends libaio1 sh -c "echo /opt/oracle/instantclient_19_20 > /etc/ld.so.conf.d/oracle-instantclient.conf" ldconfig

Running These commands worked for me

hundag20 commented 1 year ago

I'm working through a similar problem now. Did you install the Oracle instant client into the Docker container or system you are running superset from? cx_oracle requires either the instant client or full Oracle client. The recently released python-oracledb no longer has this requirement, so installation should get easier in the near future.

I'm using the following in my Docker container to install the latest SQL client:

RUN mkdir -p /usr/share/oracle/network/admin \
    && cd /usr/share/oracle \
    && apt-get update -y \
    && apt-get install -y --no-install-recommends git ca-certificates wget build-essential unzip curl libaio1 libaio-dev \
    && rm -rf /var/lib/apt/lists/* \
    && apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false \
    && wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basiclite-linuxx64.zip \
    && unzip instantclient-basiclite-linuxx64.zip \
    && rm -f instantclient-basiclite-linuxx64.zip \
    && cd  /usr/share/oracle/instantclient* \
    && rm -f *jdbc* *occi* *mysql* *README *jar uidrvci genezi adrci \
    && echo  /usr/share/oracle/instantclient* > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig \
    && echo 'local_xe=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xepdb1)))' > /usr/share/oracle/network/admin/tnsnames.ora```

Thanks this worked for me

foretony5211 commented 1 year ago

I'm working through a similar problem now. Did you install the Oracle instant client into the Docker container or system you are running superset from? cx_oracle requires either the instant client or full Oracle client. The recently released python-oracledb no longer has this requirement, so installation should get easier in the near future. I'm using the following in my Docker container to install the latest SQL client:

RUN mkdir -p /usr/share/oracle/network/admin \
    && cd /usr/share/oracle \
    && apt-get update -y \
    && apt-get install -y --no-install-recommends git ca-certificates wget build-essential unzip curl libaio1 libaio-dev \
    && rm -rf /var/lib/apt/lists/* \
    && apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false \
    && wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basiclite-linuxx64.zip \
    && unzip instantclient-basiclite-linuxx64.zip \
    && rm -f instantclient-basiclite-linuxx64.zip \
    && cd  /usr/share/oracle/instantclient* \
    && rm -f *jdbc* *occi* *mysql* *README *jar uidrvci genezi adrci \
    && echo  /usr/share/oracle/instantclient* > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig \
    && echo 'local_xe=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xepdb1)))' > /usr/share/oracle/network/admin/tnsnames.ora```

Thanks this worked for me

thanks for you , this does not worked for me , because i use oracle 11.2 , so I change the instantclient url from version 21 to 19 ， it worked for me

mkdir -p /usr/share/oracle/network/admin \
    && cd /usr/share/oracle \
    && apt-get update -y \
    && apt-get install -y --no-install-recommends git ca-certificates wget build-essential unzip curl libaio1 libaio-dev \
    && rm -rf /var/lib/apt/lists/* \
    && apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false \
    && wget https://download.oracle.com/otn_software/linux/instantclient/1921000/instantclient-basiclite-linux.x64-19.21.0.0.0dbru.zip \
    && unzip instantclient-basiclite-linux.x64-19.21.0.0.0dbru.zip \
    && rm -f instantclient-basiclite-linux.x64-19.21.0.0.0dbru.zip \
    && cd  /usr/share/oracle/instantclient* \
    && rm -f *jdbc* *occi* *mysql* *README *jar uidrvci genezi adrci \
    && echo  /usr/share/oracle/instantclient* > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig \
    && echo 'local_xe=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xepdb1)))' > /usr/share/oracle/network/admin/tnsnames.ora
crcjwice123 commented 10 months ago

Found a solution to above problem, used docker image apache/superset to build another custom container and that resolved this issue. PSB Dockerfile which is working fine.

FROM apache/superset USER root RUN pip install cx_Oracle RUN apt update RUN apt -y install alien libaio1 wget RUN wget https://download.oracle.com/otn_software/linux/instantclient/185000/oracle-instantclient18.5-basiclite-18.5.0.0.0-3.x86_64.rpm RUN alien -i oracle-instantclient18.5-basiclite-18.5.0.0.0-3.x86_64.rpm RUN sh -c 'echo /usr/lib/oracle/18.5/client64/lib/ > /etc/ld.so.conf.d/oracle.conf' RUN ldconfig USER superset

this approach works for me, I entered the container and ran these commands.

mayasirode commented 8 months ago

i had the same issue connecting a docker based superset to a oracle database.

with this very good step by step tutorial i installed a superset instance by scratch and with the following steps (after the installation) my connection to oracle db worked:

cd superset
. superset_env/bin/activate`
sudo pip install cx_Oracle
sudo apt update
sudo  apt -y install alien libaio1 wget
sudo wget https://download.oracle.com/otn_software/linux/instantclient/2113000/oracle-instantclient-basiclite-21.13.0.0.0-1.x86_64.rpm
sudo alien -i oracle-instantclient-basiclite-21.13.0.0.0-1.x86_64.rpm` 
sudo sh -c 'echo /usr/lib/oracle/21.13/client64/lib/ > /etc/ld.so.conf.d/oracle.conf'
sudo ldconfig

the connection string in superset was:

oracle+cx_oracle://USRER:PASSWORD@HOST:PORT/SERVICE_NAME

thank you very much to @shantanukhond for the tutorial and @antiwari001 for the solution to the oracle problem!!

rusackas commented 8 months ago

it seems cx_oracle has been moved and renamed to python-oracledb - has anyone tried this to make sure it works? If it does, someone should probably update the docs page accordingly. https://github.com/oracle/python-oracledb/

Nicoautoxp commented 7 months ago

i had the same issue connecting a docker based superset to a oracle database.

with this very good step by step tutorial i installed a superset instance by scratch and with the following steps (after the installation) my connection to oracle db worked:

cd superset
. superset_env/bin/activate`
sudo pip install cx_Oracle
sudo apt update
sudo  apt -y install alien libaio1 wget
sudo wget https://download.oracle.com/otn_software/linux/instantclient/2113000/oracle-instantclient-basiclite-21.13.0.0.0-1.x86_64.rpm
sudo alien -i oracle-instantclient-basiclite-21.13.0.0.0-1.x86_64.rpm` 
sudo sh -c 'echo /usr/lib/oracle/21.13/client64/lib/ > /etc/ld.so.conf.d/oracle.conf'
sudo ldconfig

the connection string in superset was:

oracle+cx_oracle://USRER:PASSWORD@HOST:PORT/SERVICE_NAME

thank you very much to @shantanukhond for the tutorial and @antiwari001 for the solution to the oracle problem!!

Following this i also sucessfully connected to my oracledb. I did have to use the long connection string from @cofin and manually install the gevent python library

vranjan25 commented 6 months ago

Add the below in Dockerfile

Install Oracle Instant Client

RUN mkdir -p /opt/oracle \ && wget --no-check-certificate --no-cookies --header "Cookie: oraclelicense=accept-securebackup-cookie" https://download.oracle.com/otn_software/linux/instantclient/214000/instantclient-basic-linux.x64-21.4.0.0.0dbru.zip \ && unzip instantclient-basic-linux.x64-21.4.0.0.0dbru.zip -d /opt/oracle \ && rm -f instantclient-basic-linux.x64-21.4.0.0.0dbru.zip \ && apt-get update \ && apt-get install -y libaio1 \ && echo "/opt/oracle/instantclient_21_4" > /etc/ld.so.conf.d/oracle-instantclient.conf \ && ldconfig

Note: make sure you include wget & unzip package before oracle client. You may reuse the step

RUN mkdir -p ${PYTHONPATH} superset/static requirements superset-frontend apache_superset.egg-info requirements \ && useradd --user-group -d ${SUPERSET_HOME} -m --no-log-init --shell /bin/bash superset \ && apt-get update -qq && apt-get install -yqq --no-install-recommends \ build-essential \ curl \ default-libmysqlclient-dev \ libsasl2-dev \ libsasl2-modules-gssapi-mit \ libpq-dev \ libecpg-dev \ libldap2-dev \ wget \ unzip \ && touch superset/static/version_info.json \ && chown -R superset:superset ./ \ && rm -rf /var/lib/apt/lists/

vranjan25 commented 6 months ago

I'm having an issue to connect to an Oracle DB. I've already followed the steps to create a requirements-local.txt file into docker/ cx_Oracle package, but still not working.

How to reproduce the bug

  1. touch ./docker/requirements-local.txt
  2. echo "cx_Oracle" >> ./docker/requirements-local.txt
  3. docker-compose build --force-rm
  4. docker-compose up

Expected results

I was expecting to be able to connect into a Oracle database.

Actual results

Actually I got this error message: ERROR: (builtins.NoneType) None (Background on this error at: http://sqlalche.me/e/13/dbapi)

Screenshots

image

Environment

(please complete the following information):

  • browser type and version: Brave
  • superset version: latest
  • any feature flags active: cx_Oracle driver
  • Database: AWS Oracle

Checklist

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

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

Additional context

I'm using an AWS Oracle instance to test. I've checked the logs of the superset_worker superset_worker_beat containers, and seems that the cx-Oracle lib was successfully installed. image

The superset_app container log: image

I'm using the following string connection: oracle://<username>:<password>@<hostname>:<port> from https://superset.apache.org/docs/databases/oracle/

See below the steps I posted

eudson commented 6 months ago

@vranjan25 am not sure what I might be doing wrong but I have been trying this for a while now. I gave up months ago and resorted to do it directly o the container but it is not sustanable because every time I have to rebuild the container I need to manually repeat the process. Here is how my docker-compose and Dockerfile look like. Let me know if am doing something wrong.

Dockerfile

FROM apache/superset:${TAG:-latest}

USER root

RUN apt-get update \
    && apt-get install -y wget zip \
    && apt-get install -y libaio1 \
    && mkdir /opt/oracle \
    && cd /opt/oracle/ \
    && wget https://download.oracle.com/otn_software/linux/instantclient/2112000/instantclient-basic-linux.x64-21.12.0.0.0dbru.zip \
    && unzip instantclient-basic-linux.x64-21.12.0.0.0dbru.zip \
    && sh -c "echo /opt/oracle/instantclient_21_12 > /etc/ld.so.conf.d/oracle-instantclient.conf" \
    && ldconfig

USER superset

docker-compose.yml

  superset:
    env_file:
      - path: docker/.env # default
        required: true
      - path: docker/.env-local # optional override
        required: false
    # image: *superset-image
    build: 
      context: .
      dockerfile: Dockerfile
    container_name: superset_app
    command: ["/app/docker/docker-bootstrap.sh", "app-gunicorn"]
    user: "root"
    restart: unless-stopped
    ports:
      - 8080:8088
    depends_on: *superset-depends-on
    volumes: *superset-volumes
vivekkranjan commented 6 months ago

Use the below in the Dockerfile. You don't need any change in docker-compose.yml

SQLALCHEMY: oracle+cx_oracle://user_name:password@your_db

# Install Oracle Instant Client
RUN mkdir -p /opt/oracle \
    && wget --no-check-certificate --no-cookies --header "Cookie: oraclelicense=accept-securebackup-cookie" https://download.oracle.com/otn_software/linux/instantclient/2340000/instantclient-basic-linux.x64-23.4.0.24.05.zip \
    && unzip instantclient-basic-linux.x64-23.4.0.24.05.zip -d /opt/oracle \
    && rm -f instantclient-basic-linux.x64-23.4.0.24.05.zip \
    && apt-get update \
    && apt-get install -y libaio1 \
    && echo "/opt/oracle/instantclient_23_4" > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig \
    && mkdir -p /opt/oracle/instantclient_23_4/network/admin \
    && touch /opt/oracle/instantclient_23_4/network/admin/listener.ora \
    && touch /opt/oracle/instantclient_23_4/network/admin/tnsnames.ora \
    && touch /opt/oracle/instantclient_23_4/network/admin/sqlnet.ora \
    && echo "LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=your_host)(PORT=your_port))))" > /opt/oracle/instantclient_23_4/network/admin/listener.ora \
    && echo "your_db=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=your_host)(PORT=your_port))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=your_service_name)))" > /opt/oracle/instantclient_23_4/network/admin/tnsnames.ora \
    && echo "NAMES.DIRECTORY_PATH=(TNSNAMES)" > /opt/oracle/instantclient_23_4/network/admin/sqlnet.ora
cjbj commented 6 months ago

Alternatively, try the new version of cx_Oracle (now called python-oracledb) since it doesn't need Instant Client. Install steps are at https://cjones-oracle.medium.com/steps-to-use-apache-superset-and-oracle-database-ae0858b4f134

sachin-bureau commented 5 months ago

hey @cjbj were you able to get this connected with the python-oracledb , I was trying to connect ADW using its wallets to connects by unzipping the wallet in $TNS_ADMIN/tnsnames.ora but unable to achieve the connection

cjbj commented 5 months ago

[@sachin-bureau I updated this comment with my findings]

To use Thin mode with mTLS and Oracle Autonomous Database (e.g ADB-S, ATP, ADW, whatever SEO acronym you know it as!), extract the ewallet.pem file from the wallet ZIP file and grab one of the connect descriptors from the wallet tnsnames.ora file. Nothing else is needed. If you are following the steps in my blog, put this connect descriptor in the $HOME/superset/tnsnames.ora, and put the .pem file where ever you like; I chose to move it to the directory $HOME/pemdir/. Your SQLAlchemy URI in the Superset "Connect a database" pane would be of the form oracle://cj:mypassword@cjdb. Before creating the connection, navigate to the Advanced tab -> Other -> Engine Parameters box and enter the extra SQLAlchemy config_args like:

{
    "connect_args": {
    "config_dir": "/home/opc/superset",
    "wallet_location": "/home/opc/pemdir",
    "wallet_password": "mywalletpassword"
    }
}

To save reading the docs: "config_dir" is where the tnsnames.ora file is. (You don't need to set TNS_ADMIN if you use this). "wallet_location" is where the .pem file is. "wallet_password" is the password you created when you downloaded the wallet from Oracle Cloud.

Alternatively you could use python-oracledb "Thick" mode by adding a call to oracledb.init_oracle_client() to superset_config.py. (Don't forget to edit sqlnet.ora and change its WALLET_LOCATION DIRECTORY path). Your SQLAlchemy URI would be of the form oracle://mydbusername:mydbpassword@mydb and you wouldn't need to set any Engine Parameters.

Also consider changing the database to use 1-way TLS so you don't need a wallet file in the first place!

General install info is here, and connection steps here.

sachin-bureau commented 5 months ago

@cjbj i was able to connect with thick client here is a quick DIY blog: https://medium.com/@tripathi.sachinxx/connect-oracle-adw-with-superset-f1796fe534b7

cjbj commented 5 months ago

@sachin-bureau nice work. Earlier I also appended the steps on my blog https://cjones-oracle.medium.com/steps-to-use-apache-superset-and-oracle-database-ae0858b4f134 so the info is in one place.

yalcinarslan commented 2 months ago

@cjbj Thanks for your blog post. I followed your blog to connect Oracle 11gR2. When I try to test the connection getting the below error. How can we handle it?

ERROR: (builtins.NoneType) None [SQL: (oracledb.exceptions.OperationalError) DPY-6005: cannot connect to database (CONNECTION_ID=J3dEWf7vjzFS+8nWuTbKyQ==). DPY-3010: connections to this database server version are not supported by python-oracledb in thin mode Help: https://python-oracledb.readthedocs.io/en/latest/user_guide/troubleshooting.html#dpy-3010 (Background on this error at: https://sqlalche.me/e/14/e3q8)] (Background on this error at: https://sqlalche.me/e/14/dbapi)

cjbj commented 2 months ago

@yalcinarslan Use "Thick" mode by calling oracledb.init_oracle_client() in $HOME/superset/superset_config.py For details about what parameters to pass, see https://python-oracledb.readthedocs.io/en/latest/user_guide/initialization.html#enabling-python-oracledb-thick-mode

Or upgrade your database.

AzaZPPL commented 1 month ago

For those using the Docker Compose setup and wanting to switch to the new oracledb instead of cx_Oracle with Instant Client, here are the steps:

  1. Create requirements_local.txt in the docker folder.
  2. Add oracledb to this file to download the latest python-oracledb.
  3. Confirm the downloaded version or check here. For example, if it's 8.3.0, write: 
    import sys
import oracledb
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb
  4. Append this code to docker/pythonpath_dev/superset_config.py.
  5. Run docker-compose up -d to start the setup.

If your connection uses a service name, convert the connection string. For example, change: oracle://user:password@192.168.1.201:1521/myservicename to oracle://user:password@192.168.1.201:1521/?service_name=myservicename.

With thanks to the write up of @cjbj.