LoginSignup
9
0
アイレット株式会社 新卒Advent Calendar 2023Day 2iret
@yokota1013inアイレット株式会社

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

Last updated at Posted at 2023-12-01

やりたいこと

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

メリット

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

デメリット

処理速度がAWS環境のものに比べて遅い。

必要なツール

  • Dcoker（もしくはRancher Desktop）
  • devcontainers（VSCodeの拡張機能）
    • こちらの機能を使う理由は後述するbucket_create.shをコンテナ起動のたびに実行するためです。

使用Image・バージョン

2023/12/1時点で最新のバージョンを選択しています。

ディレクトリ構成

ディレクトリ.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
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"
}
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）をインストール

  2. VSCodeに拡張機能のdevcontainersをインストール

  3. ファイルをディレクトリ構成通りに設置

  4. VSCodeで.devcontainerが直下にくるようにワークスペースを開く

  5. 写真の左下部分を押下
    ディレクトリ2.png

  6. 「コンテナーで再度開く」を押下
    手順1.png

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

実行手順

  1. http://localhost:8888/ にアクセス
  2. 左のタブからtest.ipynbを選択
  3. 右上のカーネル選択から「PySpark」もしくは「Python3(ipykernel)を選択
  4. 実行ボタンを押下
  5. ログにS3のデータが出力されていればOK
    手順2.png

データベース確認手順

  1. http://localhost:8080/ にアクセス

  2. ログイン方法（compose.yamlのenvironment参照）
    サーバー：env.db
    ユーザー： test_user
    パスワード: test_password

  3. 左タブのtest_databaseを選択

  4. test_tableにS3のデータが格納されていればOK

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

別のやり方

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

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
0