AWSのRDSのmySQLにCSVを一括入力する(EC2経由)|おちゃカメラ。

本日限定

Amazonタイムセール開催中

セール商品を見に行く

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

概要

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

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

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

EC2とRDSのセットアップ

EC2からRDSに接続してmySQLを操作。EC2踏み台サーバー構築メモ
https://tea3.github.io/p/aws-ec2-to-rds-connection/

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

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

構成図

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

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

$ 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

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

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

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頁 ©

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

メトリクス	内容	単位
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頁

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

メトリクス	内容	単位
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