S3→Glue→MySQLをローカル環境で実行（コピペで完了！）

### やりたいこと
S3上のファイルを読み取りGlueを経由してMySQLのデータを更新する処理をpandasを使ってローカルで実装していきます。

### メリット
本来AWS環境でかかるはずの利用料金を無くすことができる。

### デメリット
処理速度がAWS環境のものに比べて遅い。

### 必要なツール
- Dcoker（もしくはRancher Desktop）
- [devcontainers](https://marketplace.visualstudio.com/items?itemName=ms-vscode-remote.remote-containers)（VSCodeの拡張機能）
    - こちらの機能を使う理由は後述する`bucket_create.sh`をコンテナ起動のたびに実行するためです。

### 使用Image・バージョン
2023/12/1時点で最新のバージョンを選択しています。
- [localstack==3.0.2](https://hub.docker.com/layers/localstack/localstack/3.0.2/images/sha256-73698e485240939490134aadd7e429ac87ff068cd5ad09f5de8ccb76727c13e1?context=explore)
- [glue==4.0.0](https://hub.docker.com/layers/amazon/aws-glue-libs/glue_libs_4.0.0_image_01/images/sha256-dbb856b644ec3c3ffc960c245e2ba7b252799015d925d2ddb0a12d05e9d1d65b?context=explore)
- [MySQL:8.0.35-debian](https://hub.docker.com/layers/library/mysql/8.0.35-debian/images/sha256-fd8f47c32de2993a704627bffca9b64495c156ec6e85e0af4074cf908830a794?context=explore)
- [phpmyadmin==5.2.1](https://hub.docker.com/layers/library/phpmyadmin/5.2.1/images/sha256-6357fe28aa1b8279cb466f1fa64cbb7b85f6eefd88cd2af5e15d19bf25634495?context=explore)

### ディレクトリ構成
   
![ディレクトリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3624645/d4e81fc9-01fe-301c-0a49-a90802d9bc4c.png)


## ソースコード

```compose.yaml
version: '3.8'
services:
  env.localstack:
    container_name: localstack
    image: localstack/localstack:3.0.1
    ports:
      - "127.0.0.1:4566:4566" 
      - "127.0.0.1:4510-4559:4510-4559"
    environment:
      - SERVICES=s3
      - AWS_DEFAULT_REGION=ap-northeast-1
      - AWS_DEFAULT_OUTPUT=json
      - AWS_ACCESS_KEY_ID=test
      - AWS_SECRET_ACCESS_KEY=test
    volumes:
      - "/var/run/docker.sock:/var/run/docker.sock"
    networks:
      - env.network
      
  env.glue:
    container_name: glue
    image: amazon/aws-glue-libs:glue_libs_4.0.0_image_01
    volumes:
      - ../:/home/glue_user/workspace/
    environment:
      - DISABLE_SSL=true
      - AWS_REGION=ap-northeast-1
      - AWS_OUTPUT=json
      - AWS_ACCESS_KEY_ID=test
      - AWS_SECRET_ACCESS_KEY=test
      - ENDPOINT_URL=http://env.localstack:4566
    ports:
      - 4040:4040
      - 8888:8888
    networks:
      - env.network
    command: /home/glue_user/jupyter/jupyter_start.sh

  env.db:
    platform: linux/x86_64
    container_name: db
    image: mysql:8.0.35-debian
    environment:
      MYSQL_DATABASE: test_database
      MYSQL_USER: test_user
      MYSQL_PASSWORD: test_password
      MYSQL_ROOT_PASSWORD: test_password
      TZ: 'Asia/Tokyo'
    ports:
      - "3307:3306"
    networks:
      - env.network

  env.phpmyadmin:
    container_name: phpmyadmin
    image: phpmyadmin:5.2.1
    depends_on:
      - env.db
    environment:
      - PMA_ARBITRARY=1
      - PMA_HOSTS='env.db'
    ports:
      - 8080:80
    networks:
      - env.network

networks:
  env.network:
    name: env.network
```

```jsonc:devcontainer.json
{
    // Dockerイメージビルド時の引数
    "dockerComposeFile": "./compose.yaml",
    // 表示されるDevコンテナの名称
    "name": "docker",

    "service": "env.glue",

    // コンテナにログインするユーザ
    "remoteUser": "glue_user",

    // 起動時ディレクトリ
    "workspaceFolder": "/home/glue_user/workspace",

    // 作成時実行コマンド
    "postCreateCommand": "sh /home/glue_user/workspace/.devcontainer/shell/pip_install.sh",

    // 起動時実行コマンド
    "postStartCommand": "sh /home/glue_user/workspace/.devcontainer/shell/bucket_create.sh"
}
```

```python:test.ipynb
import pandas as pd
from sqlalchemy import create_engine
import pymysql
import boto3
import os

# s3に接続
s3 = boto3.client(
    service_name="s3",
    aws_access_key_id=os.environ["AWS_ACCESS_KEY_ID"],
    aws_secret_access_key=os.environ["AWS_SECRET_ACCESS_KEY"],
    region_name="ap-northeast-1",
    endpoint_url=os.environ["ENDPOINT_URL"]
)

# s3からファイル取得
obj = s3.get_object(Bucket="s3-bucket", Key="test_data.csv")
df = pd.read_csv(obj["Body"], header=0)
print(df)

# データベースに接続
pymysql.install_as_MySQLdb()

# compose.yamlのenvironment参照
db_user = "test_user"
db_pw = "test_password"
db_name = "test_database"

# compose.yamlの39行目参照
db_host = "env.db"

db_access = f"mysql://{db_user}:{db_pw}@{db_host}/{db_name}?charset=utf8"

# ("mysql://<user>:<password>@<host>/<database>?charset=utf8")
engine = create_engine(db_access)

# mysqlにデータ格納
# (<table_name>,con=engine, if_exists="append", index=False)
df.to_sql("test_table", con=engine, if_exists="append", index=False)
```

↑
拡張子は`.py`でも動作しますが今回は`.ipynb`で実行します。
`.py`の実行方法は最後に記述しています。


```bucket_create.sh
#!/bin/bash

# バケット作成
aws s3 mb s3://s3-bucket/ --endpoint-url=http://localstack:4566
# バケットをs3にアップロード
aws s3 cp ./data s3://s3-bucket/ --recursive --endpoint-url=http://localstack:4566
```
↑
コンテナを起動するたびに実行されます。
無料版localstackのS3はバケットのデータを保持できないのでコンテナが終了するたびにデータが消えてしまいます。なので起動のタイミングでdataディレクトリにあるファイルをS3に格納する処理を行っています。


```pip_install.sh
#!/bin/bash

pip3 install sqlalchemy==1.4.39
```

↑
コンテナを作成する最初の１回のみ実行されます。
Glue環境にないライブラリでインストールしたい場合はここに追記していきます。

```test_data.csv
name,birthday,age
藤平 寛之,1956/4/23,67
菅田 靖彦,1983/2/24,40
多賀 秀実,1978/5/11,45
森本 宙子,1988/5/7,35
小口 柚香,2006/1/14,17
谷川 佐知子,1977/4/27,46
杉田 未央,1971/3/30,52
土屋 栄美,1977/12/7,45
小畑 昌之,1987/3/8,36
三井 華絵,1976/11/15,47
```

## 実行方法

### 環境構築手順

1.  Dcoker（もしくはRancher Desktop）をインストール
1.  VSCodeに拡張機能のdevcontainersをインストール
1. ファイルをディレクトリ構成通りに設置
1. VSCodeで`.devcontainer`が直下にくるようにワークスペースを開く
1. 写真の左下部分を押下
![ディレクトリ2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3624645/78444015-9cab-a656-084b-f4d5f6e28368.png)


1. 「コンテナーで再度開く」を押下
![手順1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3624645/8042eb3e-ba02-7cb0-d169-566f9e88fc46.png)


1. ターミナルの最後に「upload: data/test_data.csv to s3://s3-bucket/test_data.csv  」が出力されていればOK

### 実行手順

1. http://localhost:8888/ にアクセス
1. 左のタブからtest.ipynbを選択
1. 右上のカーネル選択から「PySpark」もしくは「Python3(ipykernel)を選択
1. 実行ボタンを押下
1. ログにS3のデータが出力されていればOK
![手順2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3624645/9f56cd70-2522-54f9-de29-f113db3e8be2.png)


### データベース確認手順

1. http://localhost:8080/ にアクセス
1. ログイン方法（compose.yamlのenvironment参照）
    サーバー：`env.db`
    ユーザー： `test_user`
    パスワード: `test_password`

1.   左タブのtest_databaseを選択
1.   test_tableにS3のデータが格納されていればOK

※port番号が求められた場合は`3307`を入力してください

### 別のやり方
http://localhost:8888/ にアクセスしてWeb上で実行するのではなくVSCode上で実行したい方向けです。

1. pythonの拡張機能をインストール
1. 実行ファイルの拡張子を`.ipynb`から`.py`に変更
1. 実行コマンドを/usr/local/bin/python3で呼び出す。
    Python3(ipykernel)と同一
1. 実行コマンドをglue-spark-submitで呼び出す。
    PySparkと同一