0

I am writing (hopefully) a simply AWS Lambda that will do an RDS Oracle SQL SELECT and email the results. So far I have been using the Lambda Management Console, but all the examples I've run across talk about making a Lambda Deployment Package. So my first question is can I do this from the Lambda Management Console?

Next question I have is what to import for the Oracle DB API? In all the examples I have seen, they download and build a package with pip, but that would then seem to imply using a Deployment Package (see above). Trying to import any of these modules listed in the examples simply give "No module named "...

After writing the above I dug into the boto3 API referrence and came up with:

import boto3
client = boto3.client('rds-data')

But it gives the error: Unknown service: 'rds-data'.

So I'm still lost.

As you can probably tell, I'm new to the Lambda environment. Any suggestions or examples would be greatly appreciated. Thanks.

wdtj
  • 4,554
  • 3
  • 17
  • 20

4 Answers4

2

This is an update of the solution using the 18c Oracle client libraries. If it wasn't for main solution it would have taken me a lot longer to get my code working. This will hopefully help anyone that follows. (an aside - I tried getting it working with the instantclient_19_3 but went round in circles for a day, and then tried with instantclient_18_5 and it worked)

Files downloaded and used

This then gave these files in the zip (lambda_function.py is my python source code) zip contents

Graham Racher
  • 21
  • 5
  • The 19.3 & 18.5 ZIP packages are almost identical; what was the problem – Christopher Jones Sep 03 '19 at 03:53
  • Thinking about it now the issue may have been caused by the fact the original solution said you don't need all the Oracle instant client files, and so I was removing or renaming some of the files; certainly in my 19.3 attempt. With the 18.5 attempt after some tinkering I ended up not removing or renaming any of the Oracle instant client files. When I get a chance I will try again with a clean untampered 19.3 zip – Graham Racher Sep 04 '19 at 09:34
1

Apparently, AWS Lambda is using an older version of boto3, which does not have rds-data yet.

So I'm afraid you will have to create a deployment package containing a more recent version of boto3.

One way to do this, would be to:

Create your lambda handler file (in this case named index.py).

def my_handler(event, context):
    client = boto3.client('rds-data')
    print(client)
    # do stuff

    return "hello world"

Add a requirements.txt file in the same folder, which will contain something like:

awscli >= 1.16.118
boto3 >= 1.9.108

Now run this (depending on the setup on your computer, you can use pip instead of pip3) in the directory/folder of your index and requirement file:

pip3 install -r requirements.txt -t . 
zip -r somezipname .

Next, upload this zip and change your handler 'entry point' to index.my_handler. The code should now run without errors.

Hieron
  • 439
  • 3
  • 10
  • Got this working through to the point that we could connect to the database. At that point I get the error ERROR: invalid cluster id. After discussing this with Amazon support I get the impression that rds_data is only supported for Aurora. Trying cx_Oracle next. Thanks anyway. – wdtj Mar 11 '19 at 14:23
1

older version of boto3 does not support rds-data. but you can deploy package with zip folder. i recommend you to use import cx-oracle for that install cx-oracle using pip and upload zip packages. check this [How can I access Oracle from Python?

Primit
  • 825
  • 7
  • 13
  • Trying this (using the process suggested by Hieron) and your reference. It works fine when I run it locally, but when I try and run it as a Lambda I get "Unable to import module 'index': No module named 'cx_Oracle'" – wdtj Mar 11 '19 at 14:25
  • may be this will help you https://stackoverflow.com/questions/40741282/cannot-use-requests-module-on-aws-lambda – Primit Mar 12 '19 at 04:58
1

After much groaning and gnashing of teeth I have come up with a successful solution.

rds_data (as confirmed by AWS Support) is only supporting Aurora Databases. Wish the AWS documents mentioned this. 8{(>

Thanks to the answers above as well as Jason Landrey for hints as to the solution.

In order to access RDS/Oracle, you need to use cx_Oracle. But wait, there's more.

cx_Oracle is not in the standard Lambda environment, so you need to bring your own. My development environment is on Windows, but the Lambda environment is Linux. So, you need to download and install in your packaging directory I got mine from https://pypi.org/project/cx-Oracle/#files. Install locally with:

pip install cx_Oracle-7.1.2-cp37-cp37m-manylinux1_x86_64.whl -t .

You will see several file appear in . Then you need to find a Linux system and download /lib64/libaio.so.1.0.1 and call it libaio.so.1 in your packaging directory.
And then you need to download both Oracle instant client basic and SDK packages from http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html.

Create a zip file with all these items (including your own Python source). In doing so, rename Oracle instant client files libclntsh.so.11.1 to libclntsh.so and libocci.so.11.1 to libocci.so.

Upload the zip to a S3 bucket as the direct deploy is limited to 66mb and this zip is a bit larger.

Create a Lambda with the appropriate IAM permissions and VPC access, install the package and it should be good to go.

I found that if you don't include all the instant client files you start getting Oracle errors about missing timezone and NLS information.

List of zip contents (for me, YMMV):

    7996693  08/24/2013 12:30   libnnz11.so
          0  03/11/2019 16:10   cx_Oracle-7.1.1.data/
          0  03/11/2019 16:10   cx_Oracle-7.1.1.data/data/
          0  03/11/2019 16:10   cx_Oracle-7.1.1.data/data/cx_Oracle-doc/
          0  03/11/2019 16:10   cx_Oracle-7.1.1.dist-info/
       1325  03/13/2019 12:35   Email.py
       1805  02/19/2019 21:11   cx_Oracle-7.1.1.data/data/cx_Oracle-doc/LICENSE.txt
        163  02/19/2019 21:11   cx_Oracle-7.1.1.data/data/cx_Oracle-doc/README.txt
        851  02/19/2019 21:11   cx_Oracle-7.1.1.dist-info/METADATA
        628  02/19/2019 21:12   cx_Oracle-7.1.1.dist-info/RECORD
        109  02/19/2019 21:12   cx_Oracle-7.1.1.dist-info/WHEEL
         10  02/19/2019 21:11   cx_Oracle-7.1.1.dist-info/top_level.txt
    2270301  02/19/2019 21:11   cx_Oracle.cpython-37m-x86_64-linux-gnu.so
       2140  03/13/2019 14:21   getSecrets.py
       5560  03/12/2019 08:48   libaio.so.1
   53865194  08/24/2013 12:30   libclntsh.so
  118738042  08/24/2013 12:30   libociei.so
       7633  03/13/2019 16:39   scheduleReports.py
wdtj
  • 4,554
  • 3
  • 17
  • 20
  • If you're using a Wheel, then you shouldn't need the Oracle SDK header files since you aren't building any code. You can also delete JDBC libs, Java jars, and libocci. The Oracle doc somewhere has a list of the Instant Client files needed by C applications like cx_Oracle. Do you really need to use such an old version of Instant Client? – Christopher Jones Mar 14 '19 at 01:14
  • I have tried removing libocci but get errors about not finding TZ and NLS files. – wdtj Mar 14 '19 at 17:41
  • That sounds like you removed libociei.so, which is needed. Table 2-1 in the [documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnoci/instant-client.html#GUID-6895DB45-97AA-4738-9959-BD677D610186) lists the required libraries for OCI applications e.g. cx_Oracle or node-oracledb. If space is an issue, consider using the Basic Light package (see table 2.3) – Christopher Jones Mar 15 '19 at 05:06
  • Aaah, thanks for the tip. Guess I didn't see that list since we're still on 11g. I'll update my solution. – wdtj Mar 15 '19 at 16:02
  • Oracle client libraries 12.2 and 18c will connect to 11.2 DB. The new libraries do have useful improvements. – Christopher Jones Mar 16 '19 at 22:54