RDSで立ち上げたmySQLサーバーに、大量のデータが入っているCSVファイルを一括で追加するまでの流れをまとめてみたいと思います。EC2からRDSに接続してmySQLを操作する方法については前回をご覧ください。

目次

概要

簡単に流れをまとめると、全体の流れは以下のようになります。

  1. EC2を立ち上げる
  2. RDSを立ち上げる
  3. EC2にCSVファイルを転送
  4. RDSのmySQLでEC2にあるCSVファイルを一括追加

1と2については、前回の記事をご覧ください。

EC2とRDSのセットアップ

今回は3と4の手順について、まとめたいと思います。

簡単にまとめると、踏み台サーバーのEC2にCSVファイルをSCPコマンドで転送し、RDSのmySQLでEC2にあるCSVファイルをLOAD DATA LOCAL INFILEで一括追加してみたいと思います。

構成図

構成図は前回と同じで、SSHのみを許可した踏み台サーバーのEC2とRDSを立ち上げます。

RDSに接続するEC2踏み台サーバー
RDSに接続するEC2踏み台サーバー ©

EC2にCSVファイルを転送する

EC2インスタンスが立ち上がったら、CSVファイルを転送します。転送にはSCPコマンドを使います。

bash
1
$ scp -P 22 -i AWS_Key.pem sample.csv ec2-user@12.34.567.89:/home/ec2-user/

コマンドのオプションは以下の内容を設定します。

mysqlのオプション 内容
-i EC2に接続する鍵
-P ポート(SSHなので22に指定)

エンドポイントは/home/ec2-user/と指定すると、EC2のルートディレクトリにCSVファイルが転送されます。転送後、EC2に入って、CSVファイルが保存されているか確認してみましょう。

bash
1
2
3
4
5
6
7
8
9
10
$ ssh -i AWS_Key.pem ec2-user@12.345.67.78
Last login: Tue Aug 30 01:12:34 2016 from abcd1234.hoge.ne.jp

__| __|_ )
_| ( / Amazon Linux AMI
___|\___|___|

https://aws.amazon.com/amazon-linux-ami/2016.03-release-notes/
[ec2-user@ip-10-0-0-4 ~]$ ls
sample.csv

lsコマンドで見てみると、ルートディレクトリにsample.csvが存在しているのが確認できました。

RDSに接続

続いて、RDSのmySQLに接続してみます。AWSコンソール > RDS > インスタンスからRDSインスタンスのエンドポイントを確認しておいてください。

bash
1
2
[ec2-user@ip-10-0-0-4 ~]$ mysql -h testDB.hoge12345.ap-northeast-1.rds.amazonaws.com -P 3306 -u yourName -p
Password : yourPassword

ここで、データベースとテーブルが作成されていない場合は、予め作っておきましょう。仮にデータベース名をscoreDB、テーブル名をtestScore2016とします。

bash
1
2
3
4
5
6
7
8
9
mysql> create database scoreDB;
mysql> show databases;
mysql> use hogeDB;
mysql> create table `testScore2016` (
`no` TINYINT UNSIGNED ,
`name` varchar(64) ,
`score` TINYINT UNSIGNED ,
PRIMARY KEY (`no`)
);

mySQLからCSVを一括追加

RDSに接続し、テーブルを作成したら、EC2にあるCSVファイルをインポートします。インポートにはLOAD DATA INFILE構文を使います。

bash
1
2
3
4
5
6
7
8
mysql> LOAD DATA LOCAL INFILE 'sample.csv'
REPLACE INTO TABLE testScore2016
CHARACTER SET utf8
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' ;

Query OK, 7323 rows affected (0.31 sec)
Records: 7323 Deleted: 0 Skipped: 0 Warnings: 0

実行した結果がQuery OKと出れば、CSVファイルの追加は完了です。また、CSVの一行目を読み込みの対象外としたい場合には、IGNORE 1 LINESを指定します。

文字セットを揃える

インポートするCSVファイルは、RDSで設定した文字コードと同じとなるuft-8で用意します。

インポート時の警告

mySQLにLOAD DATA INFILE構文を使って、データを投入するとエラーが出る場合があります。その時は、SHOW WARNINGS;でエラー内容を確認します。

bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> LOAD DATA LOCAL INFILE 'sample.csv'
REPLACE INTO TABLE info
CHARACTER SET utf8
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' ;

Query OK, 7323 rows affected (0.31 sec)
Records: 7323 Deleted: 0 Skipped: 0 Warnings: 2

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1261 | Row 2 doesn't contain data for all columns |
| Warning | 1261 | Row 2 doesn't contain data for all columns |
+---------+------+--------------------------------------------+

例えば、上記の場合では、CSVファイルの一部のデータに値が無いときに発生する警告です。今回はスペースを入れて対処しました。

RDS MySQLのシステムモニタリング

LOAD DATA INFILEした直後のmySQLのCloud Watch監視メトリクスは以下のような感じになりました。メトリクスの見方は全て理解してしいないので、ひとまずデータだけ貼り付けておき、後で見返すようにしたいと思います。

LOAD DATA INFILE直後のCloud Watch監視メトリクス 1頁
LOAD DATA INFILE直後のCloud Watch監視メトリクス 1頁 ©

上記の監視項目は以下のようなものとなっています。

メトリクス 内容 単位
Write Throughput 書き込みスループット MB/秒
Read Throughput 読み込みスループット MB/秒
Swap Usage スワップ使用量 MB
Write Latency 書き込みレイテンシ
Read Latency 読み込みレイテンシ
Network Receive Throughput ネットワーク受信スループット MB/秒
Network Transmit Throughput ネットワーク転送スループット MB/秒
CPU Credit Usage CPUクレジット使用量
CPU Credit Balance CPUクレジット残高

CPUクレジット使用量は

  • 負荷が高くなく CPU がベースライン性能にとどまっている場合、CPUクレジット残高が貯まっていく
  • 負荷が高まった場合、CPUクレジットを消費してバーストする
  • 1CPUクレジットで1分間バースト可能
  • CPUクレジット残高が無くなった場合は CPU 性能はベースライン性能にとどまる
  • 未使用のCPUクレジットは、最大24時間有効

という事だそうです。

dogmap.jp
dogmap.jp を t1.micro から t2.micro に変更してみました

続いて監視項目の2ページ目です。

LOAD DATA INFILE直後のCloud Watch監視メトリクス 2頁
LOAD DATA INFILE直後のCloud Watch監視メトリクス 2頁 ©

上記の監視項目は以下のようなものとなっています。

メトリクス 内容 単位
CPU Utilization DBインスタンスのCPU使用率
DB Connections データベース接続数
Free Storage Space 使用可能なストレージ・スペースの量 MB
Freeable Memory 使用可能なランダム・アクセス・メモリ領域 MB
Write Operations 1秒あたりの平均書き込みディスクI/O回数 回/秒
Read Operations 1秒あたりの平均読み込みディスクI/O回数 回/秒
Queue Depth ディスクアクセスを待っている未処理のI/Oリクエスト(読み書き)数
Replica Lag プライマリインスタンスからレプリカへの更新時の遅延 ミリ秒
Binary Log Disk Usage MySQLバイナリログのストレージ利用量 MB

各項目については以下が参考になりました。

Qiita
CloudWatchのRDS監視項目

Developpers.IO
CloudWatchグラフの確認方法と確認できるグラフ一覧(EC2/ELB/RDS)

Developpers.IO
Amazon Auroraの運用時に監視できる項目について

ちなみに、select後はこんな感じになりました。

SELECT直後のCloud Watch監視メトリクス 1頁
SELECT直後のCloud Watch監視メトリクス 1頁 ©
SELECT直後のCloud Watch監視メトリクス 2頁
SELECT直後のCloud Watch監視メトリクス 2頁 ©

まとめ

AWSのRDSで立ち上げたmySQLサーバーに、大量のデータが入っているCSVファイルを一括で追加しました。数千件のデータであれば、1秒未満で投入できるようです。