AWS aurora severless 構築からpythonのpandas.DataFrameを取得するまで

AWSのAurora Severlessを使いAPIを組もうかと思ったのですが、素人過ぎてデータのやり取りすらままならんかったので、その工程を残しました。
執筆時点 2021/04/17

# Aurora severless 構築

1. AmazonRDSのコンソールを開きます
1. 「データベースを作成」を開始
    1. データベース作成方法: 標準作成
    1. エンジンのオプション： Amazon Aurora
    1. エディション: MySQL との互換性を持つ Amazon Aurora
    1. **キャパシティータイプ：サーバーレス**
    1. バージョン：Aurora(MySQL)-5.6.10a
    1. DB クラスター識別子: お好みのDB名（ここではtest-db1)
    1. マスターユーザー名: admin (本番はここもadmin以外にすべきか？）
    1. マスターパスワード： お好きなパスワード
    1. キャパシティの設定： 最小Aurora キャパシティユニット 1, 最大Aurora キャパシティユニット 1
        1. **スケーリングの追加設定：「数分間アイドル状態のままの場合コンピューティング性能を一時停止する」にチェック、30分のアイドルで１時停止（開発用につくるため開発時間外の費用を抑えるため）**
    1. VPC: デフォルトVPC
    1. サブネットグループ: デフォルトvpc-〇〇
    1. 既存のVPCセキュリティグループ: default
    1. **追加の接続設定：Data APIにチェック**
1. データベースを作成を押す

ここでDataAPIにチェックを入れたのは、Aurora Severlessとの交信にはこのDataAPIが必要になるためです。結構大切なオプションなのに、デフォルトでチェックが入っていないので注意。

# Aurora severlessのリソースARNをメモする
1. AmazonRDSのコンソールを開きます
1. データベースを開く
1. 作成したDBをダブルクリック

![RDS · AWS Console - Google Chrome 2021-04-17 20.52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254837/325c8fb0-bd88-2d09-a832-82704cfb29b5.png)

# シークレットの作成
DBにアクセスする際、このシークレットを使ってユーザやパスワードの代わりにするらしい。
1. Secrets Managerを開く
1. 「新しいシークレットを保存する」を選択
1. auroraのために設定した「ユーザ名」「パスワード」を記入
1. DBインスタンスに先程作成したauroraを選択
1. 次へ
1. シークレットの名前や説明を適当につける。今回は名前を「test-db1-admin-secret」とした
1. 次へ
1. 自動ローテーションは無効のまま、次へ
1. シークレットを作成する

![Secrets Manager - Google Chrome 2021-04-17 20.56.1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254837/b3479551-177f-de47-6306-38cbfa376045.png)

シークレットができたら、出来上がったシークレットを開き、シークレットのARNをメモする
![Secrets Manager - Google Chrome 2021-04-17 21.04.4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254837/3ca8b93a-8222-8b15-0e53-291339084c16.png)


# データベースに接続してクエリディタを開く
データベースを操作するためのエディタを開きます

1. AmazonRDSに戻る
1. 左側「データベース」タブを選択
1. 作成したauroraDBを選択
1. 右上「アクション」→「クエリ」を選択
     ![RDS · AWS Console - Google Chrome 2021-04-17 21.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254837/5ae77373-214d-06a8-efb4-48fa2b0676df.png)
1. 以下を確認/入力
    1. データベースインスタンスが作成したものになっていること
    1. データベースユーザ名に「Secrets Manager ARNと接続する」を選択
    1. 前項「シークレットを作成」で作ったシークレットARNを写す
1. 「データベースに接続」します

![RDS · AWS Console - Google Chrome 2021-04-17 21.14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254837/30a19b29-3228-de1c-d655-1c929bb46059.png)

# データベースにテーブルやデータを追加
作ったままのauroraには何もデータが入っていないので、テスト用のDBとテーブルを作成し、データも追加します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254837/8db829c3-8456-053f-3f0f-2184f9c65262.png)

1. とりあえず、デフォルトでエディタに表記されているクエリ`select * from information_schema.tables;`を実行してみる
    1. この時点ではDBが休止状態に入っている可能性があり、エラーが返ってくるかもしれません。数分経ってからもう一度実行してみましょう。
1. 実行できたら、DBを追加するために`create database mydb;`を実行。`mydb`はDB名なので好みで変更可
    1. `show databases;`を実行して、dbが作成できていることを確認
1. データテーブルを追加します`create table mydb.sample_data (id int, name varchar(20), dt datetime);`を実行。
    1. `show tables from mydb`で作成できたことを確認
1. `insert into mydb.sample_data (id, name, dt) values (100, 'inten', now());`を実行。適当なデータを挿入する。
    1. 自分で複数挿入すると面白いので推奨です。`select * from mydb.sample_data;`で入力値を確認できます。

僕は最終的に次のようなテーブルを作成しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254837/4a3cb436-7643-d83b-06af-0b2c3789f481.png)

# AWS CLI用にIAMユーザを作成
いよいよpythonを使った接続です。

しかし、このためにはAWS CLI用のユーザの作成を先に済ます必要があります。
AWS CLIは今までコンソールで行っていたことを、ローカルのPCなどの他の環境でも行うためのツールです。
pythonでAWSのサービスに接続する際も間接的にAWS CLIを使ってAWSのサービスとやり取りを行っているようです。
awsコンソールにIAMが必要なように、AWS CLIにもIAMが必要です。

1. awsコンソールでIAMを開きます
1. ユーザを追加を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254837/b0b3a138-db0e-345b-b9e5-52738344d0ed.png)
1. 「プログラムによるアクセス」にチェックを入れ、ユーザ名を記入する
![IAM Management Console - Google Chrome 2021-04-17 .png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254837/28f51df6-4f55-3c41-b7bc-36631939eae7.png)
1. 権限を付与する
    1. 権限はそれぞれ方針があると思います。「AdministratorAccess」あたりを付けるのが楽ですが、注意は必要です。
1. 確認する
1. シークレットアクセスキーを保存する
    1. ここで得られるシークレットアクセスキーはこの機会を逃すと二度と取得できません。念の為csvもダウンロードしておきましょう。
![IAM Management Console - Google Chrome 2021-04-17 .png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254837/c10b2d19-8e4f-bb2a-1f75-f6eea129734d.png)

# AWS CLIのインストールと設定

1. `pip install awscli`
1. `aws configure`を実行。以下のように入力

```
AWS Access Key ID [None]: [作成したIAMのアクセスキー]
AWS Secret Access Key [None]: [作成したIAMのシークレットアクセスキー]
Default region name [None]: ap-northeast-1
Default output format [None]: json
```
これでpythonがAWS CLIを使ってauroraと接続するための準備が整いました。

# pydataapiを使ってpythonで接続

ここからは[pydataapi](https://github.com/koxudaxi/py-data-api)を使って、ローカル環境からauroraにアクセスしてみましょう。

1. `pip install pydataapi`
1. 次のコードをコピー/編集して、実行する

```python
from pydataapi import DataAPI, Result

database = 'mydb'
resource_arn = 'aurora severlessのリソースARN'
secret_arn = 'aurora severlessのユーザ情報のシークレットARN'
data_api = DataAPI(resource_arn=resource_arn, secret_arn=secret_arn, database=database)
result: Result = data_api.execute('select * from sample_data')
print(result.all())
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254837/88d4c79d-ca64-af95-b6de-3c71dc6f857a.png)

きちんと、auroraDBに格納した値が見れたと思います。

`data_api_execute.execute`の引数のクエリを書き換えれば`select`以外の命令も実行できます。

[pydataapi](https://github.com/koxudaxi/py-data-api)には**SQLAlchmemy**と接続する方法も用意されていますからORMが使いたい方もこれでOK!

ここから先は通常のRDB同様に操作可能です。

また、単にpandas.DataFrameをRDBから読み込みたいだけの場合

```python
from sqlalchemy.engine import create_engine
engine = create_engine(
    'mysql+pydataapi://',
    connect_args={
        'resource_arn': resource_arn,
        'secret_arn': secret_arn,
        'database': database}
)
pd.read_sql("select * from sample_data", engine, parse_dates=['dt'])
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254837/c0db5d89-a19e-8398-138b-c3b602640d62.png)

のように、`read_sql`関数にdataapiとsqlalchemyで作られたコネクション`engine`を渡すことで簡単にRDBからDataFrameを取得できます。

以上。