How to use python-oracledb with AWS glue

[pavanprabhu2000]

Summary:- we are pulling data from a oracledb performing a few operations and finally writing it back to oracledb in aws glue.

While writing back to the oracledb we had to use the update statements for few tables.

So Glue supports direct insert into the table but it doesn't support update statements directly. So we moved to pyhton-oracledb module so that we will be able to make changes to the dataframe and convert them and use simple conditional statements to update the table.

Isssue:- I tried using the normal thin mode but it was not supported so i had to go with thick mode

Made this glue script in which we have already placed the oracle-instant-client in an s3 bucket.I am pulling it into a tmp directory and unzipping all the contents

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
from awsglue.dynamicframe import DynamicFrameCollection
from awsglue.dynamicframe import DynamicFrame
import oracledb as cx_Oracle

import os
import boto3
import subprocess
import platform

s3 = boto3.client('s3')
bucket_name = 'mybucket'
object_key = 'path_To_My_Instantclient/instantclient-basiclite-linuxx64.zip'
local_file_path = '/tmp/instantclient-basiclite-linuxx64.zip'


s3.download_file(bucket_name, object_key, local_file_path) --copying the bucket to tmp dir

subprocess.run(['unzip', '/tmp/instantclient-basiclite-linuxx64.zip', '-d', '/tmp/instantclient'], check=True). --unzipping

print(platform.architecture())

os.environ['LD_LIBRARY_PATH'] = '/tmp/instantclient/instantclient_21_13' --setting up os path


cx_Oracle.init_oracle_client(lib_dir='/tmp/instantclient/instantclient_21_13'). --calling the lib

# Check if the directory exists
if os.path.isdir('/tmp/instantclient/instantclient_21_13'):
    print("Directory '/tmp/instantclient' exists")

# Check if the file exists
if os.path.isfile('/tmp/instantclient/instantclient_21_13/libnnz21.so'):
    print("File 'libnnz21.so:' exists")

# Check file permissions
print("File permissions:", oct(os.stat('/tmp/instantclient/instantclient_21_13/libnnz21.so').st_mode)[-3:])

Still i get this error telling the file not exist but when i check it by manually unzipping i am able to see the file

Error Category: RESOURCE_NOT_FOUND_ERROR; DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "libnnz21.so: cannot open shared object file: No such file or directory". See https://python-oracledb.readthedocs.io/en/latest/user_guide/initialization.html for help

I got this link was a bit confusing Unable to connect oracle database using cx_oracle from AWS Glue

Any idea would really help Thank you in advance

[cjbj]

• What was the problem with Thin mode?
• On Linux, LD_LIBRARY_PATH must be set before any executable process starts
• On Linux, with the Instant Client libraries as downloaded from Oracle, you can't/shouldn't/don't want to pass lib_dir to init_oracle_client() (else you will get the error about libnnz that you get). See the doc).
• I suspect some people are hacking Instant Client with patchelf (like this). Then using lib_dir will likely work.

[pavanprabhu2000]

i have tried this method
but it still didn't work for me this

kinda stuck here