LambdaでMicrosoft SQL Server にODBC経由で接続する+Layerとして動作させる方法

やったこと

• PythonでSQL Server接続にpymssqlを使っていたが、問題があることがわかったので、pyodbcを使うことにした。
• ライブラリ群はLambda Layerに置き、Lambda Functionからimportできるようにした。

環境

• AWS Lambda + Layer
• Python3.7

pymssqlで問題になったこと

• WHERE句に空文字を指定できない
• INSERTで空文字を挿入できない

他にも色々ありそう
Pythonで色々なデータベースを操作する - Qiita

ODBC経由での接続(pyodbc)に変更

ライブラリを差し替えるだけ、のはずが大ハマリしたので方法を残す。

How To

ほぼ下記のサイト通り。だけど、肝心のLambda Layerでの動作手順が記載されていないので後述。

• pyodbc and unixODBC for MSSQL as a lambda layer
• AWS Lambda + Microsoft SQL Server — How to.. - Faun - Medium

以下に詳細な手順を再現します。

必要なファイルの用意

まず、pyodbcに必要なライブラリをコンパイルするための環境を作成
現時点では、LambdaのランタイムはAmazonLinux(無印)なので、その環境でコンパイルする。
AWS Lambda ランタイム - AWS Lambda

docker run -it --entrypoint bash -e ODBCINI=/opt/odbc.ini -e ODBCSYSINI=/opt/ lambci/lambda:build-python3.7

unixODBCのダウンロード、インストール

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

MSSQL 17のODBCドライバをダウンロード、インストール
Installing the Microsoft ODBC Driver for SQL Server on Linux and macOS - SQL Server | Microsoft Docs

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/

Python3.7の環境下でpyodbcをインストール。
※以下のフォルダ構成は、Python3.7でサポートされています
AWS Lambda レイヤー - AWS Lambda

mkdir /opt/python/
cd /opt/python/
pip install pyodbc -t .

libmsodbcsql.soのバージョンは頻繁に変わるようなので、pip installされた中身を良く確認する。
/opt/msodbcsql17/etc/odbcinst.ini をコピーすれば良い。

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.4.so.2.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

動作確認

コンテナの中で引き続き作業する。動作確認用のプログラムを設置。

vi /opt/python/function.py

fuinction.py

import pyodbc

driver = '{ODBC Driver 17 for SQL Server}'
sqlServer = 'FIXME'
sqlDatabase = 'FIXME'
sqlPort = 1433
sqlUsername = 'FIXME'
sqlPassword = 'FIXME'

def test_pyodbc():
    print(pyodbc.drivers())
    print('Attempting Connection...')

    conn = pyodbc.connect(f"DRIVER={driver};SERVER={sqlServer};PORT={sqlPort};DATABASE={sqlDatabase};UID={sqlUsername};PWD={sqlPassword}");
    print('Connected!!!')

    cursor = conn.cursor()
    cursor.execute("select @@version;")
    row = cursor.fetchone()
    while row:
        print(row[0])
        row = cursor.fetchone()

if __name__ == "__main__":
    test_pyodbc()

SQL Serverに接続され、バージョンが取得できた。

python /opt/python/function.py
['ODBC Driver 17 for SQL Server']
Attempting Connection...
Connected!!!
Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64)
        Nov 30 2018 12:57:58
        Copyright (C) 2017 Microsoft Corporation
        Developer Edition (64-bit) on Linux (Ubuntu 16.04.5 LTS)

Labda Functionとして動作確認

以下の記事で、このコンテナの中でさらにLambdaとして動作確認する手順が最後に記載されています。
私はWSL環境でやっていたのでできなかったのと、実際のLambda環境があったのでそちらでやりました。
pyodbc and unixODBC for MSSQL as a lambda layer

Lambdaに環境変数を追加

odbcinstは、デフォルトで/etc配下を見に行くようで、Lambdaだとそれでは困ります。
RStudioのODBC設定でハマった話 - Qiita

odbc.iniファイルの位置を環境変数を使って変更する必要がありました。

以上、Lambda Functionでの動作確認でした。

Lambda Layerとしてデプロイし、Lambda FunctionからSQL Serverに接続する

Layerとして動作させるためには、まだ必要なファイルがあるので、それをコピーしてくる。
まずは、依存関係を確認

bash-4.2# ldd /opt/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1
        linux-vdso.so.1 =>  (0x00007fffa0330000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f0d6ec6d000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f0d6ea65000)
        libodbcinst.so.2 => /usr/lib64/libodbcinst.so.2 (0x00007f0d6e84b000)
        libkrb5.so.3 => /usr/lib64/libkrb5.so.3 (0x00007f0d6e562000)
        libgssapi_krb5.so.2 => /usr/lib64/libgssapi_krb5.so.2 (0x00007f0d6e315000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007f0d6df90000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f0d6dc8e000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f0d6da78000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f0d6d85c000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f0d6d48f000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f0d6f284000)
        libk5crypto.so.3 => /usr/lib64/libk5crypto.so.3 (0x00007f0d6d274000)
        libcom_err.so.2 => /usr/lib64/libcom_err.so.2 (0x00007f0d6d071000)
        libkrb5support.so.0 => /usr/lib64/libkrb5support.so.0 (0x00007f0d6ce62000)
        libcrypto.so.10 => /var/lang/lib/libcrypto.so.10 (0x00007f0d6ca03000)
        libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f0d6c800000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f0d6c5e7000)
        libselinux.so.1 => /usr/lib64/libselinux.so.1 (0x00007f0d6c3c6000)
        libz.so.1 => /lib64/libz.so.1 (0x00007f0d6c1b0000)

この中と追加で必要なのが、下記2ファイル

• /usr/lib64/libodbc.so.2
• /usr/lib64/libodbcinst.so.2

上記を、/opt/lib/にコピーする

Layerにデプロイするためにファイルを固める。(先程作った動作確認用のファイルは消しておく)

cd /opt
zip -r9 ~/pyodbc-layer.zip .

• zipをLambda Layerへデプロイする
• 対象のLambda Functionにレイヤーを追加

Layerとしてライブラリをデプロイした場合、環境変数のパスはこんな感じになるかと思います。

これでようやく、Lambda Function側から、import pyodbcでSQL Serverに接続できます。

もし、Can't open libなどのエラーが出る場合は、依存関係が解決できてなかったり、ファイルが参照できていなかったりするので、Layerのデプロイに問題がないか、/opt下がどうなっているか、Lambda Functionのプログラム内からディレクトリツリーをデバッグで確認してみたりするとよいです。

お疲れ様でした。