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](https://qiita.com/mima_ita/items/9a5ab3b45c7575776b06#sqlserver%E3%82%92%E7%9B%B4%E6%8E%A5%E6%93%8D%E4%BD%9C)

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

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

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

- [pyodbc and unixODBC for MSSQL as a lambda layer](https://gist.github.com/diriver63/b72a954fa0da4851d89e5086aa13c6e8)
- [AWS Lambda + Microsoft SQL Server — How to.. - Faun - Medium](https://medium.com/faun/aws-lambda-microsoft-sql-server-how-to-66c5f9d275ed)

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

### 必要なファイルの用意

まず、pyodbcに必要なライブラリをコンパイルするための環境を作成
現時点では、LambdaのランタイムはAmazonLinux(無印)なので、その環境でコンパイルする。
[AWS Lambda ランタイム - AWS Lambda](https://docs.aws.amazon.com/ja_jp/lambda/latest/dg/lambda-runtimes.html)

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

[unixODBC](http://www.unixodbc.org/download.html)のダウンロード、インストール

```terminal
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](https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017)

```terminal
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](https://docs.aws.amazon.com/ja_jp/lambda/latest/dg/configuration-layers.html)

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

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

```terminal
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
```

### 動作確認

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

```terminal
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に接続され、バージョンが取得できた。

```terminal
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](https://gist.github.com/diriver63/b72a954fa0da4851d89e5086aa13c6e8)

#### Lambdaに環境変数を追加

odbcinstは、デフォルトで`/etc`配下を見に行くようで、Lambdaだとそれでは困ります。
[RStudioのODBC設定でハマった話 - Qiita](https://qiita.com/mininobu/items/045b5fea9cfb9f1ba3da)

odbc.iniファイルの位置を環境変数を使って変更する必要がありました。
![2019-10-25_10h50_20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1434/6d325177-959c-a287-45b1-29ff9f4d3f20.png)

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

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

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

```terminal
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にデプロイするためにファイルを固める。(先程作った動作確認用のファイルは消しておく)

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

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

Layerとしてライブラリをデプロイした場合、環境変数のパスはこんな感じになるかと思います。
![2019-10-25_11h14_33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1434/cd55e2e7-92c6-3925-a75d-923d2bbdc619.png)

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

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

お疲れ様でした。