EC2からRDSにcsvファイルをアップロードしてデータテーブルを作成してみた


この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので十分ご注意ください。

こんにちは。株式会社協栄情報クラウド事業本部のきおかです。

今回はAmazon RDSにMySQLでデータテーブルを作成する際に、Amazon EC2インスタンス内のローカルファイルからテーブルを作成する方法をご紹介します。今回はcsvファイルをロードして、データベース内にテーブルを作ります。

 

 

 

構成図

やりたいことはシンプルなので構成もシンプルです。
構成図

 

 

利用するAWSサービスと当記事での略語

サービス名 略語
Amazon Virtual Private Cloud (VPC) VPC
セキュリティグループ SG
Identity and Access Management (IAM) IAM
Amazon Elastic Compute Cloud (EC2) EC2
Amazon Relational Database Service (RDS) RDS
RDS for MySQL MySQL
AWS Command Line Interface (AWS CLI) CLI
AWS Key Management Service (AWS KMS) KMS

 

 

 

準備

名前はわかりやすくユニークであるようにつけましょう。

また、下記の表に書いている「設定項目」は設定項目を網羅しているわけではなく、デフォルト表示でないものと設定が必要なものを記載しています。

 

 

VPC作成

AZ パブリックサブネット プライベートサブネット
1個 1個 1個

 

 

セキュリティグループ作成

名前 タイプ ソース
SG-EC2 SSH MYIP
SG-RDS MySQL/Aurora SG-EC2

 

 

IAM作成

EC2のAMIをAmazon Linux 2023にして、セッションマネージャーで操作したいので、以下のポリシーをアタッチします。
EC2にセッションマネージャーで接続する方法はこちらのAWS公式ドキュメントで紹介されています。

ロール名
IRL-EC2
ポリシー名
AmazonSSMManagedInstanceCore

 

 

EC2作成

設定項目 設定値
名前とタグ test-EC2
AMI Amazon Linux 2023 (64ビット x86)
インスタンスタイプ t2.micro
キーペア 自分が作成したもの
ネットワーク 作成したVPC
サブネット パブリックサブネット
パブリックIPの割り当て 有効化
セキュリティグループ SG-EC2
IAMインスタンスプロフィール IRL-EC2

 

 

EC2セットアップ

bash -i
sudo su ec2-user
cd /

# デフォルトでインストールされているパッケージを最新のバージョンに更新する。-yは、すべてのプロンプトに自動的に"yes"で答えてもらうためのオプションです。
sudo dnf update -y

# RHEL9(※)のパッケージをインストール
sudo dnf -y localinstall  https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm

# インストールしたパッケージからmysqlをインストール
sudo dnf install mysql

※RHEL9 (Red Hat Enterprise Linux 9): Red Hat社が提供するエンタープライズ向けのLinuxディストリビューションの一つ。Amazon Linux 2023がFedora34, 35, 36のコンポーネントが含まれています。また、MySQLに慣れていたので何とかMySQLをインストールするためにRHEL9のパッケージインストールで対応しましたが、MySQLの派生版であるMariaDBでやった方がいいかもですね。MariaDBでの記事作成も考えます。

 

 

RDS作成

標準作成を選択してください。

設定項目 設定値 備考
エンジンタイプ MySQL (MySQL 8.0.32)
テンプレート 無料利用枠 今回はマルチAZ構成にはしないので、このハンズオン内では無料で使えるであろう無料利用枠を選択。
可用性と耐久性 単一のDBインスタンス 無料利用枠を選択すると自動的または強制的に「単一のDBインスタンス」が選択されます。
DBインスタンス識別子 test01-db
マスターユーザー名 admin MySQLにログインする際に使用する名前。
マスターパスワード 任意のものを設定
DBインスタンスクラス バースト可能クラス(db.t3.micro) 無料利用和鵜を選択すると、「db.t2.micro」か「db.t3.micro」を選べます。MySQLであれば、どちらも750時間/1か月は無料で利用できます
ストレージタイプ 汎用SSD(gp3) 選択できるもののうち、最も安くコスパもいいものを選択しました。
ストレージ割り当て 20GiB 無料利用枠で使える20GBに設定しました。
ストレージの自動スケーリング 「ストレージの自動スケーリングを有効にする」のチェックを外す
コンピューティングリソース 「EC2コンピューティングリソースに接続」を選択
EC2インスタンス test-EC2
DBサブネットグループ 自動セットアップ
VPCセキュリティグループ SG-RDS
データベース認証 「パスワード認証」を選択
最初のデータベース名 test01
バックアップ 「自動バックアップを有効にします」のチェックを外す
AWS KMS キー 作成したものを選択 データの暗号化のために使用します。

 

 

EC2からRDSに入る

$ mysql -h RDSエンドポイント -P 3306 -u admin -p
-> RDS構築時に設定したマスターパスワードを入力

 

 

csvファイルをロードする用のテーブルを予め作っておく

# RDS構築時に作成したデータベースを確認
mysql> show databases;

# RDS構築時に作成設定したデータベースtest01に入る
mysql> use test01;

# テーブルを確認(未作成なのでemptyのはず)
mysql> show tables;

# csvファイルをロードする用のテーブルを作成
mysql> CREATE TABLE 作りたいテーブル名 ( カラム1 データタイプ オプション, カラム2 データタイプ オプション, ..., カラムN データタイプ オプション);

# 下記のような成功コードが出たら作成完了です
Query OK, 0 rows affected (0.03 sec)

下記に私のテーブル作成例を記載しました。

mysql> CREATE TABLE infra_terms (インフラ用語 varchar(255) NOT NULL, ジャンル varchar(255) NOT NULL, 説明 text(3000) NOT NULL);

 

 

csvファイル作成

私はWindowsを使っていたので、メモ帳で作成しました。保存する際に、UTF-8コードで保存して、その後scpコマンドでEC2インスタンスに送ってください。

csvのフォーマットは以下の通りです。
カラムはmysqlでテーブルを作成する際に作るので、実際はcsvファイルには記載しません。

(カラム1,カラム2,...,カラムN)
データ1-1,データ1-2,データ1-3
...
データn-1,データn-2,データn-3

私はインフラ用語のクイズアプリを作りたいので、以下のようにしました。

...
SLA,災害,SLA(Service Level Agreement)は、サービスの提供事業者とその利用者の間で結ばれる、サービスのレベル(定義、範囲、内容、達成目標等)に関する合意サービス水準、サービス品質保証など、サービスの定義や通信速度、利用停止時間の上限などの保証項目を定め、そのサービス品質がSLAの保証値を下回った場合には利用料金の減額などが行われます。
...

 

 

 

条件確認

mysql操作

# データベースを表示
mysql> show databases;

# mysqlデータベースを選択
mysql> use mysql;

# ユーザーを表示
mysql> select User, Host from user;

# 設定されているユーザー情報全てを見やすく表示
# \Gはテーブルを縦に表示して見やすく表示するためのオプションです。
mysql> select * from user\G;

上記コードで、adminrdsadminとその他mysql.sysなどがあることを確認します。最後のSQL文を実行することで、rdsadminには全ての権限が設定されており、adminにはShutdown_privFile_privSuper_privCreate_Table_Spaceのみ権限がないことがわかります。rdsadminはAWS側がRDS内の更新などを行う際に使うものらしく、我々はログインできません。また、与えられていない権限はほかのユーザーに付与することはできないので、ルートユーザーであるadminにFile権限がなく、ほかのユーザーに付与して解決することもできません。

 

LOAD FILEを実行したいのですが、FILE権限が与えられていないのでこのままだと実行できません。
念のため、LOAD DATAを実行してみます。

mysql> LOAD DATA LOCAL INFILE 'csvのパス' INTO TABLE 先ほど作成したテーブル名 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

案の定、エラーが出ました。

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

 

 

 

EC2からRDSにcsvファイルをアップロードしてデータテーブルを作成

調査

何としてもLOAD DATAを実行したいです。こちらのStackOverflowの記事に、--local-infile=1のフラグを付ければいいとありました。MySQLの公式ドキュメントを見るとより詳しく書かれていました。

 

以下、抜粋です。

”mysql クライアントの場合、ローカルデータロード機能は、MySQL クライアントライブラリにコンパイルされたデフォルトで決定されます。 明示的に無効または有効にするには、--local-infile=0 または --local-infile[=1]オプションを使用します。”

 

 

 

実行

ということで、mysqlにログインする際にオプションで--local-infile=1を追加します。

$ mysql -h RDSエンドポイント -P 3306 -u admin -p --local-infile=1
-> RDS構築時に設定したマスターパスワードを入力

mysqlログイン後、

# 先ほどのLOAD DATAクエリを実行
mysql> LOAD DATA LOCAL INFILE 'csvのパス名' INTO TABLE 先ほど作成したテーブル名 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

# 下記のような成功コードが出たら完了です
Query OK, 115 rows affected (0.02 sec)
Records: 115  Deleted: 0  Skipped: 0  Warnings: 0

下記クエリでどんなテーブルになったか確認します。

# テーブル内のデータ全てを見やすく表示
mysql> select * from 先ほど作成したテーブル名\G;

csvで作成したように、適切なカラムに適切なデータがそれぞれ格納されていれば、csvファイルからのテーブル作成は完了です。

 

 

 

余談

CLIで以下を入力すると、デフォルトの設定値を見ることができて、その中に--local-infileの値も見ることができます。

$ mysql --help

一般的なコマンドヘルプの後に、以下のカラムから設定が表示されます。

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}           Value (after reading options)
----------------------------------------- --------------------------------

この中にlocal-infileがあり、FALSE(つまり0)がありました。

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}           Value (after reading options)
----------------------------------------- --------------------------------
...
local-infile                               FALSE
...

また、この中には以下のようなものもあり、変更次第で色々楽しめそうです。

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}           Value (after reading options)
----------------------------------------- --------------------------------
...
prompt                                     mysql>
...

 

 

 

まとめ

今回は、EC2からRDSにcsvファイルをアップロードしてデータテーブルを作成してみました。

デフォルト設定のままLOAD DATAクエリを実行しようとすると、File権限が与えられておらず、エラーが出ました。

ログイン時に–local-infileの値を1にすることでLOAD DATAを一時的に実行することができました。

次回はLAMP環境かNode.jsを使って、RDSのDBインスタンスからデータを取ってきてクイズを出してくれるWEBアプリを作ってみたいと思います。

ご精読ありがとうございました。

 

Last modified: 2023-05-18

Author