Unable to connect to Oracle database with provider on MWAA

Question

I am currently using MWAA (Airflow version: 2.0.2) to build the data pipeline. cx_Oracle throws the exception while I am trying to connect to Oracle database (Oracle 10g) with the provider.

1. Execute after install apache-airflow[oracle]==2.0.2

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"

2. Upload plugins.zip with instant_client_12_1 directory within, and overwrite the get_conn function of OracleHook to execute init_oracle_client function that specify the directory of instant client

cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "libmql1.so: cannot open shared object file: No such file or directory"

but the file do exist under the folder (as below)

-rw-rw-r-- 1 martin martin    29404 Jun 23 07:30 adrci
-rw-rw-r-- 1 martin martin      450 Jun 23 07:30 BASIC_LITE_README
-rw-rw-r-- 1 martin martin    43944 Jun 23 07:30 genezi
lrwxrwxrwx 1 martin martin       21 Jun 23 07:35 libclntshcore.so -> libclntshcore.so.12.1
-rw-rw-r-- 1 martin martin  6990875 Jun 23 07:30 libclntshcore.so.12.1
lrwxrwxrwx 1 martin martin       17 Jun 23 07:35 libclntsh.so -> libclntsh.so.12.1
-rw-rw-r-- 1 martin martin 58793741 Jun 23 07:30 libclntsh.so.12.1
-rw-rw-r-- 1 martin martin  1768370 Jun 23 07:30 libipc1.so
-rw-rw-r-- 1 martin martin   544150 Jun 23 07:30 libmql1.so
-rw-rw-r-- 1 martin martin  6213011 Jun 23 07:30 libnnz12.so
lrwxrwxrwx 1 martin martin       15 Jun 23 07:35 libocci.so -> libocci.so.12.1
-rw-rw-r-- 1 martin martin  2576030 Jun 23 07:30 libocci.so.12.1
-rw-rw-r-- 1 martin martin  6005681 Jun 23 07:30 libociicus.so
-rw-rw-r-- 1 martin martin   156353 Jun 23 07:30 libocijdbc12.so
-rw-rw-r-- 1 martin martin   337137 Jun 23 07:30 libons.so
-rw-rw-r-- 1 martin martin   118491 Jun 23 07:30 liboramysql12.so
-rw-rw-r-- 1 martin martin  3692096 Jun 23 07:30 ojdbc6.jar
-rw-rw-r-- 1 martin martin  3698857 Jun 23 07:30 ojdbc7.jar
-rw-rw-r-- 1 martin martin   227410 Jun 23 07:30 uidrvci
-rw-rw-r-- 1 martin martin    71202 Jun 23 07:30 xstreams.jar

I don't think it is possible to update environment variables like LD_LIBRARY_PATH on MWAA, any ideas? Thanks.

Answer 1

I have solved the issue by following this instruction

1. Download Oracle instant client and put it into plugins folder.
2. Execute following Docker commands (I was using Docker Desktop on Windows)

docker pull amazonlinux
docker run -it amazonlinux:latest /bin/bash
yum -y install libaio
cd /lib64
rm libaio.so.1
ln -s ./libaio.so.1.0.1 libaio.so.1 # This is critical for me to change the symbol link to relative path

3. Execute the following commands from Powershell (as I was using Docker Desktop on Windows)

docker container ls # copy the container ID that you created on step#2
docker cp {docker ID}:/lib64/libaio.so.1 C:\dir\of\oracle\instant\client\under\plugins
docker cp {docker ID}:/lib64/libaio.so.1.0.0 C:\dir\of\oracle\instant\client\under\plugins
docker cp {docker ID}:/lib64/libaio.so.1.0.1 C:\dir\of\oracle\instant\client\under\plugins

4. Create __ init __.py under plugins folder, or append following codes if you already have one

from airflow.plugins_manager import AirflowPlugin
import os
# the [instantclient] below depends on your folder structure
os.environ["LD_LIBRARY_PATH"]='/usr/local/airflow/plugins/instantclient'
os.environ["DPI_DEBUG_LEVEL"]="64"

class EnvVarPlugin(AirflowPlugin):                
    name = 'env_var_plugin' 

5. Zip the plugins to plugins.zip
6. Upload plugins.zip to S3
7. Update MWAA to use the updated plugins.zip

DONE~

Answer 2

The problem can be solved by building airflow docker with oracle client. Please check the thread