Steps to use Apache Superset and Oracle Database

This post shows how to set up Apache Superset to use python-oracledb instead of cx_Oracle. Superset is an “open-source modern data exploration and visualization platform”. Its data access layer uses the older SQLAlchemy 1.4 release which defaults to Oracle’s older cx_Oracle driver. However one small code snippet lets python-oracledb be used, which makes installation easy since no Oracle Instant Client is required.

Photo by Jake Hills on Unsplash

Machine setup

My environment was an Oracle Linux 8 compute instance running in Oracle Cloud, so the first few steps are specific to this. At the machine level I needed to allow HTTP access to the Superset default port, 8088. From my instance’s compute console I followed the Subnet name link, then the Default security list link and the ‘Add Ingress Rule’ link. For testing, I created a CIDR entry for 0.0.0.0/0 for source ‘All’ and destination ‘8088’. Out of habit I then logged into a console via ssh opc@xxx.xxx.xxx.xx (using my public IP address) and disabled the firewall, though this may not be strictly necessary:

sudo firewall-cmd --permanent --zone=public --add-port=8088/tcp
sudo firewall-cmd --reload

I tested the port was open by running

python3 -m http.server 8088

And then I was able to access http:/xxx.xxx.xxx.xx:8088/ from an external web browser.

Installing Apache Superset

Now the Superset install instructions were useful. The basic instructions I followed were: https://superset.apache.org/docs/installation/pypi

In a terminal window I installed the required Linux packages:

sudo dnf install gcc gcc-c++ libffi-devel openssl-devel cyrus-sasl-devel openldap-devel python3.11 python3.11-pip python3.11-setuptools python3.11-wheel python3.11-devel

I created a Python virtual environment and installed the python-oracledb and superset packages:

python3.11 -m venv venv
python3.11 -m pip install --upgrade setuptools pip
python3.11 -m pip install --upgrade oracledb 
python3.11 -m pip install --upgrade apache-superset

I created a working directory:

mkdir $HOME/superset

I set my secret key:

echo "SECRET_KEY = 'YOURSECRETKEY'" > $HOME/superset/superset_config.py

Now comes the trick. To make Superset use the newer python-oracledb library instead of the old cx_Oracle, I added the snippet mentioned in the python-oracledb documentation:

echo 'import sys
import oracledb
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb
import cx_Oracle' >> $HOME/superset/superset_config.py

(If, for whatever reason, you wanted to use python-oracledb Thick mode, this is where you would also call oracledb.init_oracle_client()).

Continuing the Superset installation instructions:

export SUPERSET_CONFIG_PATH=$HOME/superset/superset_config.py
export FLASK_APP=superset
superset db upgrade
superset fab create-admin

The latter command prompted me to create usernames and passwords.

I then loaded samples:

superset load_examples

Not all were installed due to some Superset buglet (which gave sqlite3.OperationalError: too many SQL variables) , but the first few didn’t have a problem.

The last configuration step was:

superset init

Next I created a tnsnames.ora file for my database connection string, since a network alias is arguably the easiest thing to use in SQLAlchemy 1.4 when the connection string has extra options:

echo 'cjdb = (description=(address=(protocol=tcps)(port=1521)(host=xxx.oraclecloud.com))(connect_data=(service_name=xxxx.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))' > $HOME/superset/tnsnames.ora

Finally I then started the server:

export FLASK_APP=superset
export SUPERSET_CONFIG_PATH=$HOME/superset/superset_config.py
export TNS_ADMIN=$HOME/superset
superset run -h 0.0.0.0 -p 8088 --with-threads --reload --debugger

The -h option was necessary for me to be able to access the Superset server from external machines.

Enabling the data source

From a browser I logged into Superset using the credentials created previously by the superset fab create-admin command.

I then navigated via the top-right “+” menu, then “Data” and then “Connect database”:

I chose ‘Oracle’ from the SUPPORTED DATABASES dropdown:

This menu option won’t be visible unless you installed python-oracledb and added the driver name-mapping snippet to superset_config.py, as described earlier.

I then entered my SQLAlchemy URI, using the “old” cx_Oracle format like oracle://mydbusername:mydbpassword@cjdb. Note the connection alias cjdb matches that in my tnsnames.ora file.

If any future version of Superset uses SQLAlchemy 2, then the URI syntax will be slightly different, see here.

I can then navigate to the SQL Lab area and select my Oracle data source:

For example to query the locations table in my database schema:

That’s it. If you have any questions about using Apache Superset with Oracle Database, ask on the Superset project.

Addendum — Connecting Superset to Oracle Autonomous Database

After initial publication of this blog, a user asked me for help connecting to Oracle Autonomous Database using an mTLS connection. Unlike 1-way TLS which needs no extra configuration in the application (meaning you simply need to follow the steps described earlier), using mTLS needs you to download a wallet zip file from the Oracle ADB console. Apache Superset then needs to be configured to find the wallet. For reference, our general documentation on connecting python-oracledb to Oracle Autonomous Database is here.

Start by downloading the wallet from the ADB Console. You can find this under the ‘Database connection’ button and then using ‘Download client credentials (Wallet)’. If you are using the default Thin mode of python-oracledb, you will need to remember the wallet password that you will now be prompted to create. (Reminder: if your database is configured to use 1-way TLS, you don’t need to do any of this).

Connecting Superset using python-oracledb Thin mode with mTLS

To use the default Thin mode of python-oracledb, these extra steps augment the general instructions of this blog post.

1. Unzip the downloaded wallet into $HOME/superset/ Technically you just need tnsnames.ora, and ewallet.pem. The other files can be deleted.
2. Continue with the Superset initialization as above. When you are creating your connection in Superset, use one of the network aliases from tnsnames.ora in your SQLAlchemy URI connection string, similar to: oracle://mydbusername:mydbpassword@mydb_high
3. But before Clicking the ‘Connect’ button, navigate to the Advanced tab -> Other -> Engine Parameters text field. In here, add connection arguments like:

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

The config_dir parameter is the directory containing the tnsnames.ora file (with this set, you no longer need to set the TNS_ADMIN environment variable). The wallet_location is the directory where the ewallet.pem file is located. The wallet_password is the password you created when downloading the wallet (this is/should be different to your database user password).

Now you can click ‘Connect’.

Connecting Superset using python-oracledb Thick mode with mTLS

If you are using python-oracledb in Thick mode i.e. you added a call to oracledb.init_oracle_client() to $HOME/superset/superset_config.py , then these extra steps augment the general instructions of this blog post.

1. Unzip the downloaded wallet into $HOME/superset/ Technically you just need tnsnames.ora, sqlnet.ora, and cwallet.sso. The other files can be deleted.

2. Edit sqlnet.ora and set the DIRECTORY path to the expansion of $HOME/superset:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/wherever/superset")))
SSL_SERVER_DN_MATCH=yes

3. Continue with the Superset initialization as before. When you are creating your connection in Superset, use one of the network aliases from tnsnames.ora in your SQLAlchemy URI connection string, similar to: oracle://mydbusername:mydbpassword@mydb_high