More than 3 years have passed since last update.

AWS その2Advent Calendar 2020

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

Last updated at 2020-12-08Posted at 2020-12-02

概要

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を選択

デフォルトのインスタンスタイプを選択

インスタンスの詳細設定

ネットワークのVPCはVPNに接続されているものを選択
サブネットはプライベートサブネットを選択
そのほかはデフォルトの設定でOK

ストレージ

デフォルトの8GiBでOK

セキュリティグループ

ローカル側からのHTTPリクエストを許可するものを選択
とりあえず自分のPCからのHTTPリクエストだけを許可しました。

インスタンスの起動

「実行中」になるまで待ちます。

インスタンスに接続する

sshコマンドをコピーします。

bashでSSH接続します。

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

Ubuntuに接続したら環境整備

sudo apt update
sudo apt upgrade

Nginxのインストール

sudo apt install nginx

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

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のインストールと仮想環境の構築

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

Pythonの仮想環境で必要ライブラリのインストール

仮想環境に入ります。

source env/bin/activate

(env)
pip install uwsgi flask pyodbc

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

Flaskアプリ本体のファイル作成

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

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用のファイル作成

myapp/wsgi.py

from server import app
if __name__ == "__main__":
   app.run()

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で作成する

/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

サービスを起動

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

プロキシ設定

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

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ディレクトリの設定で処理されます。

これで環境は設定完了

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

動作確認

WebブラウザにIPアドレスを入力して下記が表示されればOK

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

pyodbcでSQL Serverと接続するコード

これでDBからデータを取得してJSONで返すAPIの完成

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の設定ファイル編集開始。

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名に変更

server {
   listen 80;
   server_name xxx.xxx.xxx.xxx;
   location / {
       include uwsgi_params;
       uwsgi_pass unix:/home/ubuntu/myapp/server.sock;
   }
}

Nginxを再起動させます。

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

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

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

参考「内部 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をオリジナルドメインに変更

server {
   listen 80;
   server_name xxx.xxx.xxx.xxx;
   location / {
       include uwsgi_params;
       uwsgi_pass unix:/home/ubuntu/myapp/server.sock;
   }
}