閲覧ありがとうございます。
本記事は、S3バケットに保存したcsvテーブルをAmazon Aurora MySQLに取り込んでみた際の手順を記事にしたものです。
基本情報
使用するAmazon Aurora MySQLのエンジンバージョン: 8.4.mysql_aurora.8.4.7
使用するEC2インスタンスのAMI: Amazon Linux 2023 kernel-6.18 AMI
構成: 下記参照
今回は、EC2⇔Auroraの接続に関しては割愛いたします。EC2、Auroraをそれぞれ構築し、接続が確立されていることを前提としています。
また、S3 Gateway Endpointが作成・設定済みであることを前提としています。
EC2にはSSM Session Managerで接続します。
事前準備として、S3バケットの作成・インサートしたいcsvファイルの格納を済ませてください。
AuroraからS3バケットにアクセスするための準備
手順1: AuroraからS3バケット内のオブジェクトにアクセスするためのIAMロールを作成する
①まずはIAMロールにアタッチするIAMポリシーを作成します。
IAMポリシーの作成画面にて以下を貼り付け、<S3バケットのARN>部分をcsvファイルを格納したS3バケットのARNに変換してください。
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "s3import",
"Action": [
"s3:GetObject",
"s3:ListBucket"
],
"Effect": "Allow",
"Resource": [
"<S3バケットのARN>",
"<S3バケットのARN>/*"
]
}
]
}
イイ感じのポリシー名を付けてIAMポリシーを作成してください。
②先ほど作成したIAMポリシーをアタッチしたIAMロールを作成する
「カスタム信頼ポリシー」を選択し、以下を入力してください。
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "rds.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}
ポリシーは①で作成したポリシー名を選択し、こちらもイイ感じのロール名をつけて作成してください。
手順2: DBに作成したIAMロールをアタッチする
DBのクラスターを選択した状態で、「接続とセキュリティ」メニューにある「IAM ロールの管理」セクションまでスクロールします
「追加するIAMロールの選択」タブから手順1で作成したIAMロールを選択し、「ロールの追加」を押下してください。
手順3: カスタムパラメータグループを作成する
DBが「S3を参照する際にデフォルトで使うIAMロール」が手順1で作成したIAMロールになるよう、パラメータグループを設定します。
後述のAurora MySQL の LOAD DATA FROM S3 構文には AWS_IAM_ROLE オプションが存在しないため、パラメータグループの設定が必須となります。
「パラメータグループの作成」ボタンより、パラメータグループの作成人進んでください。
各項目の設定では、
-
エンジンのタイプ: MySQL
-
パラメータグループファミリー: DBの「エンジンバージョン」に合わせる
※DBの「設定」タブから確認可能です
-
タイプ: DB Cluster Parameter Group
「DB Parameter Group」を選択しないよう注意してください。
手順4: DBのパラメータグループを作成したパラメータグループに設定する
データベースのコンソール画面より、該当のDBクラスターを選択し、「変更」ボタンを押下してください。
「追加設定」までスクロールし、パラメータグループをデフォルトのものから先ほど作成したパラメータグループに変更します。
設定が完了したら、DBインスタンスを再起動します。
データベースのコンソールに戻り、クラスターインスタンスを選択した状態で「アクション」メニューより「再起動」を押下します。
再起動を行わないと、パラメータグループの変更が反映されずIAM Roleの設定エラーが発生します。設定後は必ず再起動を行ってください。
手順5: Auroraにアタッチしたセキュリティグループのアウトバウンド設定を確認する
AuroraからS3への通信には、カスタムTCP/ポート443(HTTPS)を利用します。
データベースのコンソールより、クラスターインスタンスの詳細から「接続とセキュリティ」タブを開きます。
「セキュリティグループのルール」セクションから、Auroraに設定されたセキュリティグループを開きます。
セキュリティグループの「アウトバウンドルール」にて、カスタムTCP(もしくはすべてのプロトコル)、ポート443(HTTPS)、送信先 0.0.0.0/0が許可されていることを確認してください。
DBに取り込むデータと対応する列名を持つテーブルを作成する
手順1: サーバ(EC2)からAuroraに接続する
EC2に接続し、ターミナルで以下を実行してください。
mysql -h <Auroraのライターエンドポイント> -P 3306 -u <DBユーザ名> -p --ssl
Auroraのライターエンドポイントは以下から確認できます。
①データベースのコンソールよりDBクラスターを選択し、「接続とセキュリティ」>「エンドポイント」を選択してください。
②「ライター」のエンドポイントをコピーします。
コマンドを実行すると、コマンド内で記載したユーザのパスワードを聞かれますので入力してください(パスワードがコマンド履歴に残らないためこの方法がおすすめです)
下記のような画面になればアクセス成功です！
手順2: 取り込む CSV の列構造に合わせてテーブルを作成する
今回取り込むcsvファイルは以下のような架空の従業員情報を記載したダミーデータです。
※姓名も含め架空のデータであり実際の人物と一切関係はありません。
①まずは存在するデータベースの名前を調べる
以下のコマンドを実行して、使用するデータベースの名前を確認します。
SHOW DATABASES;
②使用するデータベースを指定する
以下のコマンドを実行して、使用するデータベースを指定します。
USE データベース名;
③任意の名前でcsvに対応する列名を持つテーブルを新規作成
サンプルのcsvの場合、以下のようなコマンドになります。
以下の場合、テーブル名は「employees」となります。
CREATE TABLE IF NOT EXISTS `employees` (
`employee_id` VARCHAR(20) NOT NULL COMMENT '社員ID',
`name` VARCHAR(50) NOT NULL COMMENT '氏名',
`branch_office` VARCHAR(50) NOT NULL COMMENT '所属営業所',
`vehicle_type` VARCHAR(30) NOT NULL COMMENT '車両区分',
`experience_years` INT NOT NULL COMMENT '経験年数',
`work_start_time` TIME NOT NULL COMMENT '勤務予定開始時刻',
`work_end_time` TIME NOT NULL COMMENT '勤務予定終了時刻',
`personality` VARCHAR(50) DEFAULT NULL COMMENT '性格傾向',
`specialty` VARCHAR(50) DEFAULT NULL COMMENT '得意業務',
`leave_date` DATE DEFAULT NULL COMMENT '休暇予定日',
`driver_license` VARCHAR(100) DEFAULT NULL COMMENT '保有免許',
PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='従業員マスタ';
日本語による文字化けを防ぐため、デフォルトの文字コードをutf8mb4に設定しています。
手順3: テーブルが正常に作成できたか確認する
以下のコマンドを実行し、DB内のテーブルを表示します。
SHOW TABLES;
下記のように表示されればOKです！
※画像では他のテーブルも作成されてしまっていますが無視してくださいm(__)m
作成したテーブルにS3からデータをインサートする
手順1: 使用するDBユーザにS3のアクセスを許可する
GRANT AWS_LOAD_S3_ACCESS TO 'username'@'%';
上記のusernameを実際の値に置き換えてください。
Aurora MySQL 8.0以降では、セキュリティ仕様が強化されており、DBユーザがS3からのデータロード機能を利用するために AWS_LOAD_S3_ACCESSという専用の権限を明示的に付与されていることが実行条件になっているようです。
手順2: S3に格納したcsvデータをインサートする
サンプルのcsvデータの場合、以下のようなコマンドを使ってS3から指定したテーブルへデータをインサートすることができます。
LOAD DATA FROM S3 's3-ap-northeast-1://your-bucket-name/filename.csv'
INTO TABLE `employees`
CHARACTER SET cp932
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
employee_id,
name,
branch_office,
vehicle_type,
experience_years,
work_start_time,
work_end_time,
personality,
specialty,
leave_date,
driver_license
);
s3-ap-northeast-1://your-bucket-name/filename.csvについて、your-bucket-nameはcsvデータを格納したS3バケット名、filename.csvはインサートしたいcsvファイル名に変換してください。
filename.csvは日本語でも問題ありませんでした。
INTO TABLEのあとにインサート先となるテーブル名を指定します。
前述のとおり、Aurora MySQL の LOAD DATA FROM S3 構文には AWS_IAM_ROLE というオプションが存在しないため、Auroraのパラメータグループが設定されていない(もしくは変更が反映されていない)と、ここでエラーが発生します。
手順3: データが意図通りに取り込めているかを確認
以下のコマンドを実行し、取り込まれた件数を確認してください。
SELECT COUNT(*) FROM `employees`;
※employeesは任意のテーブル名に変換して実行してください。
以下のコマンドを実行し、csvファイルのデータと相違が無いことを確認してください。
SELECT * FROM `employees` LIMIT 5;
※employeesは任意のテーブル名に変換して実行してください。
上記の結果が想定通りになっていれば、S3に格納したcsvファイルからのインサートは完了です！
お疲れ様でした。
ここまでお読みいただき、ありがとうございました！