Serverless, AWS Lambda, PYODBC, UNIXODBC, Docker, Oracle InstantClient, Python

When is a server not a server? I don't really know, but I wanted to automate rote DBA tasks using python and lambda without a server. If you want to do this, first you must create a server. Oh well...

Create your own EC2 of the same type as the AWS Lambda public images - free tier is just fine. It needs to look like lambda serverless servers https://docs.aws.amazon.com/lambda/latest/dg/current-supported-versions.html

Servers (even serverless servers that are servers when they're not) use drivers. Download the oracle instant client libraries for linux x86-64 for basic and ODBC and put them in our home dir

https://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html

wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz

You need docker and the docker daemon:

sudo yum install docker.x86_64
sudo usermod -a -G docker ec2-user   (you might not be ec2 user)

Start the daemon

sudo yum install git
git clone https://github.com/tianon/cgroupfs-mount
sudo mv cgroupfs-mount/cgroupfs-mount /usr/local/bin/
sudo /usr/local/bin/cgroupfs-mount
sudo dockerd &

We now need/want to use Serverless. I guess at this point you might think is Lambda already serverless why do I need serverless? Because it makes the process a bit easier is the short answer as you will see further down.

curl -o- https://raw.githubusercontent.com/creationix/nvm/v0.32.0/install.sh | bash
. ~/.nvm/nvm.sh

nvm -ls-remote

Check out what versions are there, I like to pick the second last LTS to be safe-ish

nvm install v8.12.0

Now we're kindof going to do stuff in here a little bit (https://read.iopipe.com/the-right-way-to-do-serverless-in-python-e99535574454 ) What a great article - Read it or this wont make sense.

npm install -g serverless
mkdir ~/my-serverless-driver-project
cd ~/my-serverless-driver-project
sls create -n my-serverless-driver-project -t aws-python
sls deploy

You'll get the following as it's a new instance

"Serverless Error ---------------------------------------
  ServerlessError: AWS provider credentials not found. Learn how to set up AWS provider credentials in our docs here: <http://bit.ly/aws-creds-setup>.
"

Do an AWS configure to set yourself up https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-started.html

sls invoke -f hello

OK Basics are done, good on you. You can also see why serverless is sortof handy for setting some stuff on for you already if you go into the console - you can see it uploads files etc without any hassals.

vim httprequest.py
"
import os
import pyodbc
def handler(event, context):
    connection = pyodbc.connect('DRIVER={Oracle12};DBQ=some.server.com.au:1521/mydb;Uid=jared;Pwd=mypass')
    st='OK'
    #st=os.popen('ldd -v libsqora.so.18.1').read()
    #st=os.popen('/var/task/bin/dltest /var/task/libsqora.so.18.1').read()
    for root, dirs, files in os.walk("."):
        for filename in files:
            st = st+ filename + '\n'
    return {"content": st}
"

Note above I have left in troubleshooting code if there are library issues. This may be very important.

update the functions section of your serverless.yml

"
functions:
  hello:
    handler: handler.hello
  httprequest:
    handler: httprequest.handler"

Install more stuff...!

These are required to build python libs

sudo yum install gcc
sudo yum install gcc-c++

Let's get some drivers shall we? This was the hardest part to get right

tar xvzf unixODBC-2.3.5.tar.gz
cd unixODBC-2.3.5
./configure  --sysconfdir=/var/task  --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE  --prefix=/home
sudo make install
cp -R /home/bin/ /home/ec2-user/my-serverless-driver-project/
#cp -R /home/include/ /home/ec2-user/my-serverless-driver-project/
cp  /home/lib/* /home/ec2-user/my-serverless-driver-project/
cd /home/ec2-user/my-serverless-driver-project/
(yes this is silly but I'm using it just to pip install python)

We can create pyodbc if we get some odbc dev drivers - these odbc drivers arent required for the lambda

sudo yum install unixODBC-devel
sudo pip install pyodbc
cp /usr/local/lib64/python2.7/site-packages/pyodbc.so /home/ec2-user/my-serverless-driver-project/

Let's get some more drivers... Oracle this time. Dear readers are there smaller drivers I can use???

cd ~
unzip instantclient-basic-linux.x64-18.3.0.0.0dbru.zip
unzip instantclient-odbc-linux.x64-18.3.0.0.0dbru.zip

cp instantclient_18_3/* /home/ec2-user/my-serverless-driver-project/

Delete a bunch to keep the lambda under the file limit

rm /home/ec2-user/my-serverless-driver-project/liboramysql18.so
rm /home/ec2-user/my-serverless-driver-project/libclntsh.so
rm /home/ec2-user/my-serverless-driver-project/ucp.jar
rm /home/ec2-user/my-serverless-driver-project/libocci.so

A system driver needs to be copied accross

cp /lib64/libaio.so.1 /home/ec2-user/my-serverless-driver-project/

UnixODBC needs to know where your driver is

vim /home/ec2-user/my-serverless-driver-project/odbcinst.ini
[Oracle12]
Description     = Oracle ODBC driver for Oracle 12c
Driver          = /var/task/libsqora.so.18.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =

sls deploy
sls invoke -f httprequest

(Or if you're feeling lazy sls deploy && sls invoke -f httprequest )

As you could imagine I didn't nail this the first time around, so take a look at the hashed out commands to see if the libraries are there inside lambda. I am really shaking my head a bit about the implementation of UnixODBC and the sheer size of the Oracle drivers that make it painful in the lambda world. I also highly recommend using serverless-python-requirements but it won't work for this example due to DB drivers. Perhaps there's a way to get that working with docker.