-
-
Save diriver63/b72a954fa0da4851d89e5086aa13c6e8 to your computer and use it in GitHub Desktop.
| # use https://github.com/lambci/docker-lambda to simulate a lambda environment | |
| docker run -it --rm --entrypoint bash -e ODBCINI=/opt/odbc.ini -e ODBCSYSINI=/opt/ lambci/lambda:build-python3.7 | |
| # download and install unixODBC | |
| # http://www.unixodbc.org/download.html | |
| curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.7.tar.gz -O | |
| tar xzvf unixODBC-2.3.7.tar.gz | |
| cd unixODBC-2.3.7 | |
| ./configure --sysconfdir=/opt --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/opt | |
| make | |
| make install | |
| cd .. | |
| rm -rf unixODBC-2.3.7 unixODBC-2.3.7.tar.gz | |
| # download and install ODBC driver for MSSQL 17 | |
| # https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017 | |
| curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo | |
| yum install e2fsprogs.x86_64 0:1.43.5-2.43.amzn1 fuse-libs.x86_64 0:2.9.4-1.18.amzn1 libss.x86_64 0:1.43.5-2.43.amzn1 | |
| ACCEPT_EULA=Y yum install msodbcsql17 --disablerepo=amzn* | |
| export CFLAGS="-I/opt/include" | |
| export LDFLAGS="-L/opt/lib" | |
| cd /opt | |
| cp -r /opt/microsoft/msodbcsql17/ . | |
| rm -rf /opt/microsoft/ | |
| # install pyodbc for use with python. | |
| # Notice the folder structure to support python 3.7 runtime | |
| # https://docs.aws.amazon.com/lambda/latest/dg/configuration-layers.html#configuration-layers-path | |
| mkdir /opt/python/ | |
| cd /opt/python/ | |
| pip install pyodbc -t . | |
| cd /opt | |
| cat <<EOF > odbcinst.ini | |
| [ODBC Driver 17 for SQL Server] | |
| Description=Microsoft ODBC Driver 17 for SQL Server | |
| Driver=/opt/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1 | |
| UsageCount=1 | |
| EOF | |
| cat <<EOF > odbc.ini | |
| [ODBC Driver 17 for SQL Server] | |
| Driver = ODBC Driver 17 for SQL Server | |
| Description = My ODBC Driver 17 for SQL Server | |
| Trace = No | |
| EOF | |
| # package the content in a zip file to use as a lambda layer | |
| cd /opt | |
| zip -r9 ~/pyodbc-layer.zip . | |
| # to test it locally: | |
| # unzip the content of your layer to your local environment, to /var/opt/ for example: | |
| unzip pyodbc-layer.zip -d /var/opt/ | |
| # In your local environment, have your lambda function handy at /var/task/lambda_function.py | |
| import pyodbc | |
| server = 'myserver' | |
| database = 'mydb' | |
| username = 'myuser' | |
| password = 'mypwd' | |
| def lambda_handler(event, context): | |
| # TODO implement | |
| cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) | |
| cursor = cnxn.cursor() | |
| cursor.execute("select @@version;") | |
| row = cursor.fetchone() | |
| while row: | |
| print(row[0]) | |
| row = cursor.fetchone() | |
| # Time to test | |
| docker run --rm -v /var/task:/var/task -v /var/opt:/opt lambci/lambda:python3.7 lambda_function.lambda_handler '{"some": "event"}' | |
| # useful links | |
| https://medium.com/devopslinks/aws-lambda-microsoft-sql-server-how-to-66c5f9d275ed | |
| https://stackoverflow.com/questions/47682991/aws-lambda-function-to-connect-to-sql-server-with-python | |
| https://gist.github.com/carlochess/658a98589709f46dbb3d20502e48556b |
I'm using python 3.8. I even tried 3.6
I have created layer using the zip file (https://github.com/alexanderluiscampino/lambda-layers/blob/master/pyodbc-9ef8961d-ce5b-4603-b397-03c9a6316eca.zip).
For python 38 you have to use the zip I just uploaded.
since these are compiled libs, they need to be specific.
Alternatively, you can build you own layer. Here is the code for it:
ARG VARIANT="3.8"
FROM lambci/lambda:build-python${VARIANT}
WORKDIR /root
RUN yum -y update
RUN curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz -O \
&& tar xvzf unixODBC-2.3.5.tar.gz \
&& cd unixODBC-2.3.5 \
&& ./configure --sysconfdir=/opt/python --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home \
&& make install \
&& cd .. \
&& mv /home/* . \
&& mv unixODBC-2.3.5 unixODBC-2.3.5.tar.gz /tmp
RUN curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo \
&& yum -y install freetds e2fsprogs openssl \
&& ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*
RUN export CFLAGS="-I/root/include" \
&& export LDFLAGS="-L/root/lib" \
&& pip install pyodbc adodbapi pyDes --upgrade -t .
RUN cp -r /opt/microsoft/msodbcsql .
RUN echo $'[ODBC Driver 17 for SQL Server]\nDriver = ODBC Driver 17 for SQL Server\nDescription = My ODBC Driver 17 for SQL Server\nTrace = No' > /root/odbc.ini
RUN echo $'[ODBC Driver 17 for SQL Server]\nDescription = Microsoft ODBC Driver 17 for SQL Server\nDriver = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1\nUsageCount = 1' > /root/odbcinst.ini
RUN mkdir -p /opt/python \
&& cp -r /root/* /opt/python \
&& mv /opt/python/lib /opt \
&& mv /opt/python/bin /opt \
&& cd /opt \
&& zip -r /pyodbc.zip .
This is for 3.8. You can change the ARG value at the top, but I have only used for 38 so far.
Thanks @alexanderluiscampino it worked.
Hi all; Got myself into a bit of a mess with this one. Does anyone have a fully working zip that you can upload?
Hi all; Got myself into a bit of a mess with this one. Does anyone have a fully working zip that you can upload?
Thanks @paulalex thats a huge help.
When building in CDK using the experimental PythonFunction, the imports are placed in a requirements.txt file in the lambda folder.
I have uploaded a zip file to Lambda in the console, and am adding the layer to the PythonFunction lambda in my CDK stack.
But if I add pyodbc to the requirements.txt file, the docker build receives this error in SAM gcc: error trying to exec 'cc1plus': execvp: No such file or directory
If i omit pyodbc from the requirements.txt file, I receive the other area of can't import module lambda-handler.py .... lib.so.2 no such file or directory
any help regarding using the CDK and the lambda PythonFunction construct would be greatly appreciated!
thanks @alexanderluiscampino
Here's a Python 3.9 version that's working for me. I've also published it on StackOverflow here.
Based on:
- Python 3.9
- AWS's base images for Lambda (b/c lambci is in maintenance mode and doesn't have python 3.9)
- unixODBC-2.3.11
- https://packages.microsoft.com/config/rhel/7 (instead of
rhel/6)
# Dockerfile
# See https://stackoverflow.com/a/72617192/58876
FROM amazon/aws-lambda-python:3.9
ENTRYPOINT []
WORKDIR /root
# Get development tools to enable compiling
RUN yum -y update
RUN yum -y groupinstall "Development Tools"
# Get unixODBC and install it
RUN yum -y install tar gzip
RUN curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.11.tar.gz -O
RUN tar xvzf unixODBC-2.3.11.tar.gz
WORKDIR /root/unixODBC-2.3.11
RUN ./configure --sysconfdir=/opt/python --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/root/unixODBC-install
RUN make install
RUN mv /root/unixODBC-install/bin /opt/bin
RUN mv /root/unixODBC-install/lib /opt/lib
WORKDIR /root
# Install msodbcsql
RUN curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
RUN yum -y install e2fsprogs openssl
RUN ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*
RUN rm -r /opt/microsoft/msodbcsql
# Install pyodbc
# Need "unixODBC-devel" to avoid "src/pyodbc.h:56:10: fatal error: sql.h: No such file or directory" during pip install
RUN yum -y install unixODBC-devel
RUN export CFLAGS="-I/opt/microsoft/msodbcsql17/include"
RUN export LDFLAGS="-L/opt/microsoft/msodbcsql17/lib"
RUN pip install pyodbc==4.0.32 adodbapi==2.6.2.0 pyDes==2.0.1 --upgrade --target /opt/python
# Add a requirements.txt file and enable this section to install other (non sql server) data-load requirements
# COPY requirements.txt /tmp/requirements.txt
# RUN pip install --requirement /tmp/requirements.txt --target /opt/python
# Create odbc.ini and odbcinst.ini
RUN echo $'[ODBC Driver 17 for SQL Server]\nDriver = ODBC Driver 17 for SQL Server\nDescription = My ODBC Driver 17 for SQL Server\nTrace = No' > /opt/python/odbc.ini
RUN so_file=$(ls /opt/microsoft/**/lib64/libmsodbcsql-*.so.* | grep msodbcsql17) && echo $'[ODBC Driver 17 for SQL Server]\nDescription = Microsoft ODBC Driver 17 for SQL Server\nDriver = '"$so_file"$'\nUsageCount = 1' > /opt/python/odbcinst.ini
# Generate the zipped file that can be uploaded as a Lambda Layer
WORKDIR /opt
RUN zip -r /layer.zip .# Build the image (this will take a while...)
$ docker build --platform=linux/amd64 -t mssql-lambda .
# Copy the zipped file to /tmp/layer.zip on your computer
$ docker run --platform=linux/amd64 --rm --volume /tmp:/tmp mssql-lambda cp /layer.zip /tmp/Upload /tmp/layer.zip from your computer to AWS as a Lambda Layer.
Create a Python 3.9 Lambda to test the layer:
import pyodbc
def lambda_handler(event, context):
driver = '{ODBC Driver 17 for SQL Server}'
server = 'XXX'
port = '1433'
database = 'XXX'
username = 'XXX'
password = 'XXX' # ideally put this in a SecretsManager secret instead of directly in the code
connection = pyodbc.connect(f'DRIVER={driver};SERVER={server},{port};DATABASE={database};UID={username};PWD={password}')
cursor = connection.cursor()
query = """
select @@version
"""
cursor.execute(query)
for row in cursor.fetchall():
print(row[0])
Hi,
I'm trying to configure Lambda with python runtime and I try to connect to a PG instance, I have unixodbc files and the odbc files for pg and I get the following error:
[unixODBC][Driver Manager]Can't open lib '/var/task/lib/psqlodbcw.so' : file not found
I tried both WSL and CentOS machine to get the files from,
the way I did it was: downloading the jar file for unixODBC and configuring it using the /var/task path then make and make install,
and I did the same for the postgresql-odbc: downloaded the jar file, configured it to the /var/task path then make and make install,
and then I had the following content:
inside lib I had this:
then I copied the entire /var/task folder to the windows machine, changed the ini file as specified above, and .ziped it and uploaded it to AWS Lambda using CDK and python.
odbcinst.ini is:
[PostgreSQL Unicode]
Description=Postgres odbc
Driver=/var/task/lib/psqlodbcw.so
UsageCount=1
and odbc.ini is:
[PostgreSQL Unicode]
Driver = PostgreSQL Unicode
Description = My ODBC Driver For PostgreSQL
Trace = No
also from within the lambda function itself, I export 2 env for the .ini files:
os.environ['ODBCINI']="/var/task"
os.environ['ODBCSYSINI']="/var/task"
for some reason its just not working :|
somebody knows how to fix it for pyodbc and postgres?
@hben-align I have never used pyodbc to connect to PG instances, it seems that your process is more or less correct, but with .so files its always hard to actually detect what might be wrong. Someone else more knowledgeable in that field might provide more help.
But I ask, why not using psycopg2 to connect to PG instance? Here is the already built layer for lambda. I use that layer in production on the daily, it's great!
@alexanderluiscampino I used psycopg2 (which I compiled myself) but this project was written using pyodbc for consistency (due to the compatibility with other engines)...
if I have no other choice I'll change the code to psycopg2, but I'm trying to avoid it for now because its a lot of work :\
btw, I've managed to get it working on a local ubuntu machine, but in lambda I still get the "[unixODBC][Driver Manager]Can't open lib '/var/task/lib/psqlodbcw.so' : file not found" error...
even when zipping the working dir from ubuntu
lol, printing the directory after the failure print out the file in the right path :\\
@alexanderluiscampino
I've managed to make it work (pyodbc with pg odbc)
I'll post here a comment with how I did it later :)
This method is for using pyodbc with pg odbc inside the task (not as a layer)
create a new ec2 machine with amazon linux 2.
then run:
sudo yum update
cd /home/ec2-user
download unixODBC and postgresql odbc
curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.11.tar.gz -O
curl https://ftp.postgresql.org/pub/odbc/versions/src/psqlodbc-13.02.0000.tar.gz -O
tar xzvf unixODBC-2.3.11.tar.gz
tar -zxvf psqlodbc-13.02.0000.tar.gz
compile unixODBC (install necessary packages)
cd /home/ec2-user/unixODBC-2.3.11/
sudo yum groupinstall "Development Tools"
./configure --sysconfdir=/var/task --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/var/task
sudo make
sudo make install
compile psqlodbc (install necessary packages)
cd /home/ec2-user/psqlodbc-13.02.0000/
sudo amazon-linux-extras enable postgresql9.6
sudo yum install -y postgresql-libs postgresql-server postgresql postgresql-devel
./configure --with-libpq=/var/task --with-unixodbc=/var/task --sysconfdir=/var/task --prefix=/var/task
sudo make
sudo make install
fill up nessesery libs (using root permissions)
sudo su
cd /var/task/lib
cp /lib64/libpq.so.5 .
cp /lib64/libdl.so.2 .
cp /lib64/libpthread.so.0 .
cp /lib64/libc.so.6 .
cp /lib64/libssl.so.10 .
cp /lib64/libcrypto.so.10 .
cp /lib64/libgssapi_krb5.so.2 .
cp /lib64/libldap_r-2.4.so.2 .
cp /lib64/ld-linux-x86-64.so.2 .
cp /lib64/libkrb5.so.3 .
cp /lib64/libcom_err.so.2 .
cp /lib64/libk5crypto.so.3 .
cp /lib64/libz.so.1 .
cp /lib64/libkrb5support.so.0 .
cp /lib64/libkeyutils.so.1 .
cp /lib64/libresolv.so.2 .
cp /lib64/liblber-2.4.so.2 .
cp /lib64/libsasl2.so.3 .
cp /lib64/libssl3.so .
cp /lib64/libsmime3.so .
cp /lib64/libnss3.so .
cp /lib64/libnssutil3.so .
cp /lib64/libplds4.so .
cp /lib64/libplc4.so .
cp /lib64/libnspr4.so .
cp /lib64/libselinux.so.1 .
cp /lib64/libcrypt.so.1 .
cp /lib64/librt.so.1 .
cp /lib64/libpcre.so.1 .
cd /var/task
zip -r /home/ec2-user/task.zip .
change .ini files as stated above
odbc.ini:
[PostgreSQL Unicode]
Driver=PostgreSQL
Description = My ODBC Driver For PostgreSQL
Trace = No
odbcinst.ini:
[PostgreSQL]
Description=Postgres odbc
Driver=/var/task/lib/psqlodbcw.so
UsageCount=1
now call the driver by using "PostgreSQL"
@hben-align : Can you please share the zip package you generated for postgresql for pyodbc?
Is anyone able to create lambda layer(python 3.9) for postgres pyodbc ?
I just created lambda layer zip for python3.8 and everything is working and tested! Thanks everyone in this gist and Tom! https://github.com/naingaungphyo/lambda_pyodbc_layer-python3.8
thanks for this!
any ideas about this? Running in WSL2 via Docker Desktop Windows
ERROR [16/25] RUN ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn* 0.7s
------
> [16/25] RUN ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*:
#19 0.405 Loaded plugins: ovl
#19 0.471 Resolving Dependencies
#19 0.472 --> Running transaction check
#19 0.472 ---> Package msodbcsql.x86_64 0:13.1.9.2-1 will be installed
#19 0.485 --> Processing Dependency: unixODBC >= 2.3.1 for package: msodbcsql-13.1.9.2-1.x86_64
#19 0.494 --> Processing Dependency: libodbcinst.so.2()(64bit) for package: msodbcsql-13.1.9.2-1.x86_64
#19 0.494 ---> Package mssql-tools.x86_64 0:17.10.1.1-1 will be installed
#19 0.495 --> Processing Dependency: msodbcsql17 >= 17.3.0.0 for package: mssql-tools-17.10.1.1-1.x86_64
#19 0.499 --> Running transaction check
#19 0.499 ---> Package msodbcsql17.x86_64 0:17.10.2.1-1 will be installed
#19 0.499 ---> Package unixODBC.x86_64 0:2.3.11-1.rh will be installed
#19 0.499 --> Processing Dependency: libltdl.so.7()(64bit) for package: unixODBC-2.3.11-1.rh.x86_64
#19 0.525 --> Finished Dependency Resolution
#19 0.526 Error: Package: unixODBC-2.3.11-1.rh.x86_64 (packages-microsoft-com-prod)
#19 0.526 Requires: libltdl.so.7()(64bit)
#19 0.526 You could try using --skip-broken to work around the problem
#19 0.689 You could try running: rpm -Va --nofiles --nodigest
------
executor failed running [/bin/sh -c ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*]: exit code: 1
nevermind. all good. If anyone else has the issue, i added
RUN yum -y install libtool-ltdl
before
RUN ACCEPT_EULA=Y yum -y install msodbcsql mssql-tools --disablerepo=amzn*
Hey guys, I created a layer as a .zip with pyodbc, unix and mssql odbc drivers that work on python 3.11 runtime. Uses mssqlodbc17 drivers.
thanks @alexanderluiscampino , saved my 1 day time
Here's a github repo with automated builds of the the ODBC 18 drivers for Python 3.11 and 3.12.
Prebuilt packages and Dockerfile for x86_64 and ARM64
https://github.com/davidcomerford/pyodbc-mssql-lambda-layer
@davidcomerford doing God's work. Thank you for this, been wanting to formalize this for ages now.
@davidcomerford doing God's work. Thank you for this, been wanting to formalize this for ages now.
You're very welcome
@davidcomerford - great thanks for this. I can use this as a lambda layer and it works fine. Using your https://github.com/davidcomerford/pyodbc-mssql-lambda-layer for Python 3.11.
But for some reason - I can't get the same drivers to work in a container lambda.
[ERROR] OperationalError: ('HYT00', '[HYT00] [unixODBC][Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
Ive also tried copying then (trying to mimic layer behaviour) but this has also failed:
FROM public.ecr.aws/lambda/python:3.11
COPY --from=builder /opt/ /opt/
COPY requirements.txt ./
RUN pip3 install -r requirements.txt -t /var/task
Really appreciate any ideas on how to get this to work in a lambda container :)
@mohaque123, the drivers loaded fine, that's how your getting that error message from them.
Your problem looks specific to the SQL Server it's failing to connect to. Check your server's ports are accessible from where ever the Lambda function is running from inside the VPC e.g. security groups, routing, NACLs, etc.
@davidcomerford - This has been a huge help. I've been able to use your zip and build it using the Dockerfile to create a lambda layer https://github.com/davidcomerford/pyodbc-mssql-lambda-layer/blob/main/Dockerfile.python.3.12.x86_64 for Python 3.12.
I am able to fully query the database using docker locally, but I am only able to query the version as in your example from the lambda function. A minimal query that should return the Top 1 from a column containing char(6) instead results in this error:
('HY000', '[HY000] [Microsoft][ODBC Driver 18 for SQL Server]Unicode conversion failed (22) (SQLGetData)')
I've seen recommendations to install glibc-gconv-extra but I've been unable to successfully edit your Dockerfile to accomplish this.
Any help would be greatly appreciated!
Hi @manush-7 , did you attempt to use it as a layer? The screenshot above looks like it is part of your deploy package. That will not work like that.
The layer code will go into /opt, when the AWS Lambda spins up.
what version of python are you using? Make use of the zip files on my repo. You can directly upload that as a layer.