Microsoft SQL ServerにODBCで接続するためのLambda Layerを作成する方法

AWS Lambda から pyodbc を使って SQL Server に接続する案件があり、若干つまづいたので備忘録的なメモを残すことにしました。

# 環境

## Lambda
- ランタイムは Python 3.9
- アーキテクチャは x86_64

## pyodbc
- unixODBC-2.3.11 （投稿時の最新）

# TL;TR

- ほぼこれ（ありがたや。。。）
https://gist.github.com/diriver63/b72a954fa0da4851d89e5086aa13c6e8?permalink_comment_id=4199559#gistcomment-4199559

- lambdaの環境変数にiniファイルまでのパスを設定する

- soファイルのバージョンに注意

# 手順（ざっくりと）

適当なディレクトリを作成

```
$ mkdir pyodbc-layer
$ cd pyodbc-layer
```

Dockerfileを作成

```
$ vi Dockerfile
```

https://gist.github.com/diriver63/b72a954fa0da4851d89e5086aa13c6e8?permalink_comment_id=4199559#gistcomment-4199559

コードは上記gistのほぼコピペですが、最小限のインストールになるよう少し修正しました。
私は「ODBC Driver 17 for SQL Server」が必要だったので、iniファイルには17を指定しています。

``` Dockerfile
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=/home
RUN make install
WORKDIR /root
RUN mv /home/* .
RUN mv unixODBC-2.3.11 unixODBC-2.3.11.tar.gz /tmp

# 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 cp -r /opt/microsoft/msodbcsql .

# Install pyodbc
RUN yum -y install unixODBC-devel
RUN export CFLAGS="-I/root/include"
RUN export LDFLAGS="-L/root/lib"
RUN pip install pyodbc -t .

# Create odbc.ini and odbcinst.ini
RUN echo -e "[ODBC Driver 17 for SQL Server]\n\
Driver = ODBC Driver 17 for SQL Server\n\
Description = My ODBC Driver 17 for SQL Server\n\
Trace = No\n\
" >> /root/odbc.ini

RUN echo -e "[ODBC Driver 17 for SQL Server]\n\
Description=Microsoft ODBC Driver 17 for SQL Server\n\
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1\n\
UsageCount=1\n\
" >> /root/odbcinst.ini

# Generate the zipped file that can be uploaded as a Lambda Layer
RUN mkdir -p /opt/python
RUN cp -r /root/* /opt/python
RUN mv /opt/python/lib /opt
RUN mv /opt/python/bin /opt
WORKDIR /opt
RUN zip -r /pyodbc.zip .
```

イメージをビルド
```
$ docker build --platform=linux/amd64 -t mssql-lambda .
```

zipに固めて取得
```
$ docker run --platform=linux/amd64 --rm --volume /tmp:/local-folder mssql-lambda cp /pyodbc.zip /local-folder/

$ mv /tmp/pyodbc.zip ./
```

できたzipファイルをLambda Layerへデプロイすれば、準備完了です。

# 環境変数を設定する必要がある

作成したLayerを対象のLambda Functionに追加した後、ODBCの設定ファイル「odbc.ini」「odbcinst.ini」までのパスを通す必要があります。
（未指定だとデフォルトで /etc の下を見に行くようです）

Dockerfileの指定だと以下の場所にiniファイルを以下の場所へ作成しており、

```
/root/odbc.ini
/root/odbcinst.ini
```

lambdaレイヤとしてまとめる際に以下の場所へ移しています。
```
RUN cp -r /root/* /opt/python

WORKDIR /opt
RUN zip -r /pyodbc.zip .
```

なので、layerとしてコードが展開された時のパスを考慮すると、環境変数が以下ののように設定が必要になります。

|  Key  |  Value  |
| ---- | ---- |
|  ODBCINI  |  /opt/python/odbc.ini  |
|  ODBCSYSINI  |  /opt/python  |

# soファイルが見つからない

私の場合はlambdaの実行時に以下のエラーが発生しました。

```
[ERROR] Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1' : file not found (0) (SQLDriverConnect)")
Traceback (most recent call last):
  File "/var/task/lambda_function.py", line 23, in lambda_handler
    connection = pyodbc.connect(f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}')
```

コンテナの中に入って、実際にインストールされたバージョンを確認してみます。

```
$ docker run -it mssql-lambda bash
```
```
bash-4.2# pwd
/opt
bash-4.2# ls microsoft/msodbcsql17/lib64/
libmsodbcsql-17.10.so.1.1
```

iniファイルの中で指定していたバージョンが変わっているようなので、Dockerfileを修正してzipファイルを作成し直しました。


# 動作確認

``` handler.py
import pyodbc

def lambda_handler(event, context):
    driver = '{ODBC Driver 17 for SQL Server}'
    server = 'XXX'
    port = '1433'
    database =  'XXX'
    username = 'XXX'
    password = 'XXX'
    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])
```

レイヤーをバージョンアップした結果、バージョン情報が出力されることが確認できました！