SQL Serverのデータを社内用にAWS EC2+Ubuntu+Python+Flask+uWSGI+Nginxで公開

# 概要
AWSとVPNで接続されている環境でRDSのSQL ServerのデータをWebで公開したり、JSON形式で提供できる環境を構築します。とりあえず動くものを一から構築します。実際には、Dockerを使った手法が簡単ですが、ここでは一から構築します。

# 環境
* Ubuntu18.04
* Nginx
* Python3.6.9
* click==7.1.2
* Flask==1.1.2
* pyodbc==4.0.30
* uWSGI==2.0.19.1

# 手順
## EC2でインスタンスを起動
Ubuntu18.04を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/208266/52df23e7-c8c6-5048-3e6a-da861927007b.png)
## デフォルトのインスタンスタイプを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/208266/3333a8de-faf4-d998-97f6-7c36228c1bd5.png)
## インスタンスの詳細設定
ネットワークのVPCはVPNに接続されているものを選択
サブネットはプライベートサブネットを選択
そのほかはデフォルトの設定でOK
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/208266/00db9275-5206-a02f-612f-3078f10b2582.png)
## ストレージ
デフォルトの8GiBでOK
## タグ
必要ならタグを設定
## セキュリティグループ
ローカル側からのHTTPリクエストを許可するものを選択
とりあえず自分のPCからのHTTPリクエストだけを許可しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/208266/b2f7cbb6-a8d4-e489-dc8d-23da7f23f8a0.png)
## インスタンスの起動
「実行中」になるまで待ちます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/208266/a22b7dde-66bc-d11a-3f5a-9dae827a91f5.png)
## インスタンスに接続する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/208266/4d65456e-6901-f800-d95a-2f27fe430de2.png)
sshコマンドをコピーします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/208266/f8f2fe59-39e5-5e25-a434-6c87e751ffda.png)
bashでSSH接続します。

```sh
ssh -i "xxxx.pem" ubuntu@xxx.xxx.xxx.xxx
```

## Ubuntuに接続したら環境整備

```sh
sudo apt update
sudo apt upgrade
```

## Nginxのインストール

```sh
sudo apt install nginx
```

## SQL Serverに接続するための準備

```sh
sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
exit
sudo apt update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
sudo ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
sudo apt install unixodbc-dev
```

## Pythonのインストールと仮想環境の構築

```sh
sudo apt install python3-pip python3-venv python3-dev
mkdir myapp
cd myapp
python3 -m venv env
```

## Pythonの仮想環境で必要ライブラリのインストール
仮想環境に入ります。

```sh
source env/bin/activate
```

```sh
(env)
pip install uwsgi flask pyodbc
```

`pip freeze`で指定したライブラリが表示されればOKです。

## Flaskアプリ本体のファイル作成
定番のものです。
ファイルは`vi server.py`で作成します。
（vi操作：`i`で入力、`escキー`でコマンドモードに戻り、`:wq`で保存）

```python:myapp/server.py
from flask import Flask
app = Flask(__name__)
@app.route("/")
def hello():
    return "<h1>Hello</h1>"
if __name__ == "__main__":
    app.run(host='0.0.0.0')
```

## uWSGI用のファイル作成

```python:myapp/wsgi.py
from server import app
if __name__ == "__main__":
   app.run()
```

```ini:myapp/server.ini
[uwsgi]
module = wsgi:app
master = true
processes = 1
socket = server.sock
chmod-socket = 666
vacuum = true
die-on-term = true
touch-reload = server.py
```

このファイルは`sudo vi /etc/systemd/system/myapp.service`で作成する

```systemd:/etc/systemd/system/myapp.service
[Unit]
Description=uWSGI instance to serve myapp
After=network.target
[Service]
User=ubuntu
Group=www-data
WorkingDirectory=/home/ubuntu/myapp
Environment="PATH=/home/ubuntu/myapp/env/bin"
ExecStart=/home/ubuntu/myapp/env/bin/uwsgi --ini server.ini
[Install]
WantedBy=multi-user.target
```

## サービスを起動

```sh
sudo systemctl start myapp
sudo systemctl enable myapp
sudo systemctl status myapp #起動確認
```

## プロキシ設定
`/etc/nginx/sites-available/myapp`という名前で下記ファイルを作成
`xxx.xxx.xxx.xxx`にはEC2で起動したプライベートIPアドレスを入力

```/etc/nginx/sites-available/myapp
server {
   listen 80;
   server_name xxx.xxx.xxx.xxx;
   location / {
       include uwsgi_params;
       uwsgi_pass unix:/home/ubuntu/myapp/server.sock;
   }
}
```

NginxはHTTPリクエストに対して、最初にどのサーバーで処理するかを判断します。
その判断に上記の`server_name`を用います。
リクエストヘッダのHostフィールドと`server_name`が一致した場合に該当したserverディレクトリの設定で処理されます。

## これで環境は設定完了

```sh
sudo ln -s /etc/nginx/sites-available/myapp /etc/nginx/sites-enabled
sudo nginx -t # 状態を確認できます
sudo systemctl restart nginx # 再起動させます
```

## 動作確認
WebブラウザにIPアドレスを入力して下記が表示されればOK
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/208266/5ad2b997-702d-9fb5-8ead-aa7f10290183.png)

NginxのWelcomeページが表示された場合は作成した設定ファイルにミスがある可能性大です。

## pyodbcでSQL Serverと接続するコード
これでDBからデータを取得してJSONで返すAPIの完成

```python:server.py
from flask import Flask
import pandas as pd
import pyodbc

app = Flask(__name__)
@app.route("/")
def hello():
    #DB接続
    server = 'xxx.xxx.xxx.xxx'
    database = 'DB name'
    username = 'user name'
    password = 'xxxxxxxxxxxxxx'
    cnxn = pyodbc.connect('DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
    cursor = cnxn.cursor()
    query = "SELECT * FROM table-name WHERE column-name = 'xxxx';"
    df = pd.read_sql(query, cnxn)
    cursor.close()
    return df.iloc[0].to_json()
if __name__ == "__main__":
    app.run(host='0.0.0.0')
```

## ALB経由にする場合
EC2インスタンスが稼働しているVPCでALBを`スキーム：内部`向け、`HTTP:80`で作成。
ALBのセキュリティグループはクライアントからのHTTPリクエストを受けられる設定にする。
EC2インスタンスはALBからのHTTPリクエストを受けられるように設定を変更する。
EC2インスタンスのNginxの設定を下記のとおり変更する。

まずはデフォルトのNginxの設定ファイル編集開始。

```sh
sudo vi /etc/nginx/nginx.conf
```

ALBのDNS名が長いため下記の変更が必要

```/etc/nginx/nginx.conf
# 一部抜粋しています
http {

        ##
        # Basic Settings
        ##

        sendfile on;
        tcp_nopush on;
        tcp_nodelay on;
        keepalive_timeout 65;
        types_hash_max_size 2048;
        # server_tokens off;

        server_names_hash_bucket_size 128; # これを追記！
        # server_names_hash_bucket_size 64;
        # server_name_in_redirect off;
```

`/etc/nginx/sites-available/myapp`にALBのDNS名を設定
`xxx.xxx.xxx.xxx`をALBのDNS名に変更

```/etc/nginx/sites-available/myapp
server {
   listen 80;
   server_name xxx.xxx.xxx.xxx;
   location / {
       include uwsgi_params;
       uwsgi_pass unix:/home/ubuntu/myapp/server.sock;
   }
}
```

Nginxを再起動させます。

```sh
sudo systemctl restart nginx # 再起動させます
```

これでブラウザからALBのDNS名でアクセスして、先ほどと同じ表示がされればOKです。

## オリジナルドメインでhttps化

[参考「内部 ALB の HTTPS 通信を試してみた」](https://dev.classmethod.jp/articles/internal-alb-https/)

- まずはRoute53でドメインを購入（詳細は割愛）
- ACMで証明書を発行（詳細は割愛）
- Route53で購入したドメインのAレコードに既存ALBを設定
- ALBのリスナーのHTTP:80を削除
- ALBのリスナーにHTTPS:443を追加（ACMの証明書を設定、ターゲットをEC2に設定）
- ALBのセキュリティグループにHTTPSを許可
- Nginxの設定を変更

`/etc/nginx/sites-available/myapp`にALBのオリジナルドメイン名を設定
`xxx.xxx.xxx.xxx`をオリジナルドメインに変更

```/etc/nginx/sites-available/myapp
server {
   listen 80;
   server_name xxx.xxx.xxx.xxx;
   location / {
       include uwsgi_params;
       uwsgi_pass unix:/home/ubuntu/myapp/server.sock;
   }
}
```

Nginxを再起動させます。

```sh
sudo systemctl restart nginx # 再起動させます
```

これでブラウザからhttpsのオリジナルドメインでアクセスして、先ほどと同じ表示がされればOKです。

## まとめ
一からの環境構築は大変ですね。
SQLAlchemyやMySQL、Dockerを使えばもっと簡単にできると思います。