Connect Oracle from AWS Lambda

Question

I have a Lambda function that needs to use pandas, sqlalchemy, and cx_Oracle.

Installing and packaging all these libraries together exceeds the 250MB deployment package limit of AWS Lambda.

I would like to include only the .zip of the Oracle Basic Light Package, then extract and use it at runtime.

What I have tried

My project is structured as follows:

cx_Oracle-7.2.3.dist-info/
dateutil/
numpy/
pandas/
pytz/six-1.12.0.dist-info/
sqlalchemy/
SQLAlchemy-1.3.8.egg-info/
cx_Oracle.cpython-36m-x86_64-linux-hnu.so
instantclient-basiclite-linux.x64-19.3.0.0.0dbru.zip
main.py
six.py
template.yml

In main.py, I run the following:

import json, traceback, os

import sqlalchemy as sa
import pandas as pd

def main(event, context):
    try:
        unzip_oracle()
        return {'statusCode': 200,
                'body': json.dumps(run_query()),
                'headers': {'Content-Type': 'application/json', 'Access-Control-Allow-Origin':'*'}}
    except:
        em = traceback.format_exc()
        print("Error encountered. Error is: \n" + str(em))
        return {'statusCode': 500,
                'body': str(em),
                'headers': {'Content-Type': 'application/json', 'Access-Control-Allow-Origin':'*'}}  



def unzip_oracle():
    print('extracting oracle drivers and copying results to /var/task/lib')
    os.system('unzip /var/task/instantclient-basiclite-linux.x64-19.3.0.0.0dbru.zip -d /tmp')
    print('extraction steps complete')
    os.system('export ORACLE_HOME=/tmp/instantclient_19_3')


def get_db_connection():
    return sa.engine.url.URL('oracle+cx_oracle', 
        username='do_not_worry', password='about_any', 
        host='of_these', port=1521, 
        query=dict(service_name='details')
    )


def run_query():
    query_text = """SELECT * FROM dont_worry_about_it"""
    conn = sa.create_engine(get_db_connection())
    print('Connected')
    df = pd.read_sql(sa.text(query_text), conn)
    print(df.shape)
    return df.to_json(orient='records')

This returns the error:

sqlalchemy.exc.DatabaseError: (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". See https://oracle.github.io/odpi/doc/installation.html#linux for help (Background on this error at: http://sqlalche.me/e/4xp6)

What I have also tried

I've tried:

Adding

Environment: 
    Variables: 
      ORACLE_HOME: /tmp 
      LD_LIBRARY_PATH: /tmp

to template.yml and redeploying. Same error as above.

Adding os.system('export LD_LIBRARY_PATH=/tmp/instantclient_19_3') into the python script. Same error as above.
Many cp and ln things that were forbidden in Lambda outside of the /tmp folder. Same error as above.

One way that works, but is bad

If I make a folder called lib/ in the Lambda package, and include an odd assortment of libaio.so.1, libclntsh.so, etc. files, the function will work as expected, for some reason. I ended up with this:

<all the other libraries and files as above>
lib/
-libaio.so.1
-libclntsh.so
-libclntsh.so.10.1
-libclntsh.so.11.1
-libclntsh.so.12.1
-libclntsh.so.18.1
-libclntsh.so.19.1
-libclntshcore.so.19.1
-libipc1.so
-libmql1.so
-libnnz19.so
-libocci.so
-libocci.so.10.1
-libocci.so.11.1
-libocci.so.12.1
-libocci.so.18.1
-libocci.so.19.1
-libociicus.so
-libons.so

However, I chose these files through trial and error and don't want to go through this again.

Is there a way to unzip instantclient-basiclite-linux.x64-19.3.0.0.0dbru.zip in Lambda at runtime, and make Lambda see/use it to connect to an Oracle database?

Have you looked at this? https://docs.python.org/3/library/zipfile.html — Poojan, Oct 04 '19 at 19:26
@Poojan it looks like a Python library that will unzip the file, similar to what I'm doing with `os.system('unzip ...')`. I do not have an issue unzipping the file, I have an issue of cx_Oracle not seeing it. Does this address the problem? — user2752159, Oct 04 '19 at 20:56
Have you tried to `importlib.reload(sa)` after `unzip_oracle`? Because it probably relays on the env vars you update. Additionally, try to debug with `sys.path` and `sys.modules` if the driver is installed correctly. Probably `sys.path.insert(0, '/tmp')` may be helpful. Also, for updating current env vars use `os.environ['LD_LIBRARY_PATH'] = '/tmp/instantclient_19_3'` because `os.system('export ...` won't update the env for the current python run. — Yann, Oct 07 '19 at 17:12
Check [lambda layers](https://docs.aws.amazon.com/lambda/latest/dg/configuration-layers.html) — Nizam Mohamed, Oct 11 '19 at 22:36

qkhanhpro · Answer 1 · 2019-10-11T07:23:07.573

I am not by any means an expert at python but this line seems very strange

print('extracting oracle drivers and copying results to /var/task/lib')
os.system('unzip /var/task/instantclient-basiclite-linux.x64-19.3.0.0.0dbru.zip -d /tmp')
print('extraction steps complete')
os.system('export ORACLE_HOME=/tmp/instantclient_19_3')

Normally, you you will have very limited access to OS level API with Lambda. And even when you do, It can behave the way you do not expect It to do. ( Think as if : Who owns the "unzip" feature? File created by this command would be visible / invokable by who? )

I see you mentioned that you have no issue extracting the files which is also a bit strange

My only answer for you is that

1/ Try to "bring your own" tools ( Unzip, etc.. )

2/ Never try to do OS level calls. Like os.system('export ...') , Always use the full path

Looking again at your question, seems like the way you specify environment variable is conflicting

ORACLE_HOME: /tmp

should not it be

Environment: 
    Variables: 
      ORACLE_HOME: /tmp/instantclient_19_3
      LD_LIBRARY_PATH: /tmp/instantclient_19_3

Also, see: How to access an AWS Lambda environment variable from Python

Connect Oracle from AWS Lambda

1 Answers1