Cannot connect to Oracle database with NNE enabled

[dougbot01]

Describe the bug
When connecting to Oracle database, an exception is thrown:

-[2025-02-10 11:24:05,284] ERROR {datahub.ingestion.run.pipeline:741} - Ingestion pipeline threw an uncaught exception: (oracledb.exceptions.DatabaseError) DPY-4011: the database or network closed the connection
Help: https://python-oracledb.readthedocs.io/en/latest/user_guide/troubleshooting.html#dpy-4011
(Background on this error at: https://sqlalche.me/e/14/4xp6)

The page referencing the error says this is common if the database has Native Network Encryption (NNE) enabled.

To Reproduce
Steps to reproduce the behavior:

1. Create an ingestion recipe for Oracle database, where the database has NNE enabled
2. Run the ingestion from datahub CLI with command "datahub ingest -c oracle_config.yaml"
3. The ingestion fails with the error above

Expected behavior
There should be an option to enable thick mode as described in python-oracledb documentation

Screenshots
N/A

Desktop (please complete the following information):

• OS: Windows 10, but should apply to all OS
• Browser: N/A
• Version: DataHub CLI version: 0.15.0.5

Additional context
At first, I thought I might be able to enable thick mode, using the "options" setting in the recipe. However, enabling thick mode in this way only appears to be an option with sqlalchemy 2.0. Since the current version of datahub is using sqlalchemy 1.4, I believe an additional option is required in the recipe to enable thick_mode by making a call to oracledb.init_oracle_client(lib_dir="\path\to\oracle\client") before the connection is established.

This doesn't appear to be documented in the sqlalchemy 1.4, but I have tested it on my local machine and found that this works. I found the code for this in a blog article, and see similar code to use oracledb in sqlalchemy 1.4 within the datahub source here.

[dougbot01]

I am willing to work on a pull request for this, but will probably need some help figuring out the best approach.

I was thinking of another pydantic field in the oracle source recipe, like thick_mode that defaults to False but can be set to True. A second field for lib_dir may also be required, that must exist when thick_mode: True.

I can probably figure that out in the Oracle source file. However, I'm not sure what file the engine is created in, so could use advice on that.

[dtsek]

Hello @dougbot01 , we were investigating the same issue and we agree with your investigation. This was probably introduced in #11607 with the replacement of cx_Oracle.

Thanks for working on that!

[dougbot01]

Closing this issue as code was merged into master