RDSのmySQLにCSVを一括入力する(EC2経由)

この前の続き。RDSで立ち上げたmySQLサーバーに、大量のデータが入っているCSVファイルを一括で追加するまでの流れをまとめてみたいと思います。

photo by Mr. Alex Garcia - Double Cliche on flickr

目次

概要

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

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

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

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

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

構成図

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

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

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

1

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

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

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

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インスタンスのエンドポイントを確認しておいてください。

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とします。

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構文を使います。

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を指定します。

インポート時の警告

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

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監視メトリクスは以下のような感じになりました。メトリクスの見方は全て理解してしいないので、ひとまずデータだけ貼り付けておき、後で見返すようにしたいと思います。

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

CPUクレジット使用量は

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

という事だそうです。

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

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

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

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

Qiita
CloudWatchのRDS監視項目

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

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

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

まとめ

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