Connect Apache Superset in podman container to remote Oracle DB

[jimfellows-NOAA]

Trying to get an oracle connection in Apache Superset in containerized environment. Have instantclient installed inside superset container, but connection is still hanging.

[jimfellows-NOAA]

Superset Oracle connection string: oracle://USER:PW@HOST:PORT (https://superset.apache.org/docs/databases/oracle)

Existing dockerfile config with cx_Oracle and instantclient installed:

FROM apache/superset
# Switching to root to install the required packages
USER root

# if you prefer Postgres, you may want to use `psycopg2-binary` instead
RUN pip install sqlalchemy-trino && \
        pip install sqlalchemy-drill && \
        pip install psycopg2 && \
        pip install cx_Oracle

# add config file to python path
RUN export PYTHONPATH=$PYTHONPATH:/app/conf/superset_config.py

RUN mkdir /opt/oracle

WORKDIR /opt/oracle

RUN wget https://download.oracle.com/otn_software/linux/instantclient/211000/instantclient-basic-linux.x64-21.1.0.0.0.zip && \
        unzip instantclient-basic-linux.x64-21.1.0.0.0.zip

ENV LD_LIBRARY_PATH=/opt/oracle/instantclient_21_1

RUN apt-get update -y && \
        apt-get install libaio1 -y

WORKDIR /app

# Switching back to using the `superset` user
#USER superset

Going to add in SQLPlus installation next for testing from command line, but right now connection just spins. Likely an oracle instantclient config issue, need to confirm that the container can ping host (it should be able to).

Trying to follow cx_Oracle's installation instructions: https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html

Also helpful: https://blogs.oracle.com/opal/docker-for-oracle-database-applications-in-nodejs-and-python-part-1 https://stackoverflow.com/questions/55823744/how-to-fix-cx-oracle-databaseerror-dpi-1047-cannot-locate-a-64-bit-oracle-cli https://stackoverflow.com/questions/64046595/unable-to-use-amancevice-superset-docker-image-to-use-oracle-db-as-datasource https://stackoverflow.com/questions/58992954/install-oracle-instant-client-into-docker-container-for-python-cx-oracle

[jimfellows-NOAA]

Same firewall issue as https://github.com/nwfsc-fram/boatnet/issues/2353

for reference, revised instantclient loading into Superset dockerfile:

FROM apache/superset
# Switching to root to install the required packages
USER root
# Example: installing the MySQL driver to connect to the metadata database
# if you prefer Postgres, you may want to use `psycopg2-binary` instead
RUN pip install sqlalchemy-trino && \
        pip install sqlalchemy-drill && \
        pip install psycopg2 && \
        pip install cx_Oracle

# add config file to python path
RUN export PYTHONPATH=$PYTHONPATH:/app/conf/superset_config.py

RUN mkdir -p /opt/oracle

WORKDIR /opt/oracle

RUN wget https://download.oracle.com/otn_software/linux/instantclient/211000/instantclient-basic-linux.x64-21.1.0.0.0.zip && \
        unzip instantclient-basic-linux.x64-21.1.0.0.0.zip && \
        wget https://download.oracle.com/otn_software/linux/instantclient/211000/instantclient-sqlplus-linux.x64-21.1.0.0.0.zip && \
        unzip instantclient-sqlplus-linux.x64-21.1.0.0.0.zip

ENV ORACLE_HOME=/opt/oracle/instantclient_21_1
ENV LD_LIBRARY_PATH=$ORACLE_HOME
ENV PATH = $ORACLE_HOME:$PATH

#RUN sh -c "echo /opt/oracle/instantclient_21_1 > /etc/ld.so.conf.d/oracle-instantclient.conf" && ldconfig

# ENV LD_LIBRARY_PATH=/opt/oracle/instantclient_21_1

RUN apt-get update -y && \
        apt-get install libaio1 -y && \
        apt-get install iputils-ping -y

WORKDIR /app

# Example: installing a driver to connect to Redshift
# Find which driver you need based on the analytics database
# you want to connect to here:
# https://superset.apache.org/installation.html#database-dependencies
# RUN pip install sqlalchemy-redshift
# Switching back to using the `superset` user
#USER superset