Connect Oracle ADW with Superset

Step-by-step approach to connect Oracle Autonomous Data Warehouse (ADW) to Apache Superset using the python-oracledb thick client on an Oracle Cloud Infrastructure (OCI) compute instance.
This is for databases configured to use mTLS. If, instead the DB uses 1-way TLS then a wallet isn’t needed.

Prerequisites :

1. An ADW instance with wallet downloaded
2. An OCI compute instance with SSH access where superset is running

Step 1: Transfer Your Wallet to the OCI Instance

Send your wallet file to your Oracle Cloud Infrastructure (OCI) instance using scp. Think of it as teleporting your valuable data to the cloud!

scp -i /path/to/your/private_key Wallet_XYZ.zip opc@<your_instance_public_ip>:~/

Step 2: SSH into Your OCI Instance

Step 3: Unzip the Wallet File

unzip Wallet_XYZ.zip -d $HOME/wallet

command will unzip the file and create a directory with several important files:

1. cwallet.sso
2. ewallet.p12
3. ewallet.pem
4. keystore.jks
5. ojdbc.properties
6. sqlnet.ora
7. tnsnames.ora
8. truststore.jks

The two most important files here are:

sqlnet.ora:

It configures Oracle Net Services, securing connections and controlling network session behaviors.
Key Parameters:

WALLET_LOCATION:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="$HOME/wallet")))

SSL_SERVER_DN_MATCH :

SSL_SERVER_DN_MATCH = yes

tnsnames.ora:

• Provides aliases for easy database connections.

Step 4: Set TNS_ADMIN Environment Variable
This tells your system where to find the network configuration files.

export TNS_ADMIN=$HOME/wallet

Step 5: Install Oracle Instant Client

echo 'export LD_LIBRARY_PATH=/usr/lib/oracle/19.8/client64:$LD_LIBRARY_PATH' >> ~/.bashrc
echo 'export TNS_ADMIN=$HOME/superset' >> ~/.bashrc
source ~/.bashrc

Step 6: Fetch Your ADW Credentials
Make sure you have your username and password handy. For this example, we’ll use admin and admin.

Step 7: Test the Connection

sqlplus admin/admin@cji

Run this command to check if everything is working smoothly. The cji is fetched from tnsnames.ora.

Step 8: Configure Superset

mkdir -p $HOME/superset
cat <<EOL > $HOME/superset/superset_config.py
SECRET_KEY = '$(python3 -c "import secrets; print(secrets.token_urlsafe(32))")'
import sys
import oracledb
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb
import cx_Oracle
oracledb.init_oracle_client(lib_dir="/usr/lib/oracle/23.4/client64")  # Adjust the path if necessary
EOL

Step 9: Run Superset
Make sure port 8088 is open to access Superset from your browser.

export FLASK_APP=superset
export SUPERSET_CONFIG_PATH=$HOME/superset/superset_config.py
export TNS_ADMIN=$HOME/wallet
nohup /home/opc/venv/bin/python3.11 /home/opc/venv/bin/superset run -h 0.0.0.0 -p 8088 --with-threads --reload --debugger > $HOME/superset/superset.log 2>&1 &

Step 10: Log into Superset and Add a New Database Connection

Go to Data -> Databases -> + Database and choose Oracle. Use the following SQLAlchemy URI:

oracle://admin:admin@cji

Again, cji is from tnsnames.ora.

Step 11: Explore Your Data!
🎉 Voila! You’re all set up to dive deep into your data with Superset. Happy exploring!