More than 3 years have passed since last update.

ファイルを利用したAurora MySQLのデータ入出力

Last updated at 2021-06-25Posted at 2021-06-25

#検証環境

Aurora MySQL
- バージョン：Aurora 2.07.2（MySQL 5.7）
- キャパシティタイプ：プロビジョニング済み（サーバーレスでないAuroraという意味）
- 日本語データも扱えるようにをAurora Serverless MySQL(5.6) で日本語データを扱えるようにするを参考に文字コード関連のパラメーターの設定値を utf8mb4 にカスタマイズした
- Aurora MySQLへの接続は暗号化必須としている
Aurora MySQLに接続するクライアント
- OS：Cloud9で提供されるAmazon Linux2のEC2インスタンス
- MySQLクライアント：Amazon Linux2に標準インストールされているMariaDBライブラリのMySQLクライアント

Cloud9について

ブラウザで利用できる統合開発環境を提供するAWSサービス。
コードの実行やデータの保管にはEC2インスタンスを利用する。
EC2インスタンスに対するターミナルもそなえている。
EC2インスタンスへの接続手段はSSH経由とSystemsManagerのセッションマネージャー経由とが選択できる。
Cloud9の詳細はこちら

システム構成図

よりセキュアな環境で検証を実施したかったのでCloud9もプライベートサブネットに配置することにした。
Cloud9をプライベートサブネットに配置する場合はEC2インスタンスへの接続手段はSystemsManagerしか選択できないということでSystemsManagerを選択している。

#テスト用に用意したDB、テーブル

リソース名

DB名	テーブル名
test	tb1_Customer

テーブル構成

列名	データ型	NOT NULL	PRIMARY KEY
UserID	Char(4)	Y	Y
FirstName	Varchar(16)	Y
LastName	Varchar(16)	Y
Sex	Char(1)
Age	Int

テーブルのCREATE文

create table tb1_Customer
  (
   UserID char(4) not null,
   FirstName varchar(16) not null,
   LastName varchar(16) not null,
   Sex char(1),
   Age int,
   primary key (UserID)
  );

#ファイルを使ったデータ入力
検証した方式

方式	インプットファイルの形式	インプットファイルの配置先	結果
LOAD DATA LOCAL INFILE文	テキストファイル	Cloud9のEC2インスタンス	OK
SQLファイルを利用	INSERT文を記述したSQLファイル	Cloud9のEC2インスタンス	OK
LOAD DATA FROM S3文	テキストファイル	S3	OK

##1. LOAD DATA LOCAL INFILE文を使ったデータ入力
###1-1. ファイルを用意
以下のCSVファイルをUTF-8で作成。

U001,花子,山田,f,20
U002,太郎,山田,m,30
U003,太郎,渋谷,m,25

###1-2. Cloud9のEC2インスタンスにファイルをアップロード

Cloud9のIDEで「File」 > 「Upload Local Files」をクリック

ポップアップの「Select Files」をクリック
ポップアップにてアップロードしたいローカルファイルを選択後、「開く」をクリック
Cloud9のファイル一覧にアップロードしたファイルが追加される

ちなみにアップロードしたファイルが配置されるEC2のディレクトリは、Cloud9のターミナルのデフォルトのカレントディレクトリの /home/ec2-user/environment となる

###1-3. LOAD DATA LOCAL INFILE文を実行

MySQL [test]> LOAD DATA LOCAL INFILE '/home/ec2-user/environment/test-data1.csv' INTO TABLE tb1_Customer FIELDS TERMINATED BY ',';            
Query OK, 3 rows affected, 3 warnings (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 3

レコードを参照してみるときちんと入っている。

MySQL [test]> SELECT * FROM tb1_Customer;
+--------+-----------+----------+------+------+
| UserID | FirstName | LastName | Sex  | Age  |
+--------+-----------+----------+------+------+
| U001   | 花子      | 山田     | f    |   20 |
| U002   | 太郎      | 山田     | m    |   30 |
| U003   | 太郎      | 渋谷     | m    |   25 |
+--------+-----------+----------+------+------+
3 rows in set (0.01 sec)

##2. SQLファイルを使ったデータ入力
###2-1. ファイルを用意
以下のSQLファイルをUTF-8で作成。

INSERT INTO tb1_Customer (UserID,FirstName,LastName,Sex,Age) VALUES ('U004','愛子','佐藤','f',40);
INSERT INTO tb1_Customer (UserID,FirstName,LastName,Sex,Age) VALUES ('U005','一平','高橋','m',20);
INSERT INTO tb1_Customer (UserID,FirstName,LastName,Sex,Age) VALUES ('U006','一郎','鈴木','m',40);

###2-2. Cloud9のEC2インスタンスにファイルをアップロード
手順1-2と同じなので詳細は割愛。

###2-3. データを登録
\. 「対象ファイル」 でSQLファイルの内容を実行。

MySQL [test]> \. test-data2.sql
Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

レコードを参照してみるときちんと入っている。

MySQL [test]> SELECT * FROM tb1_Customer WHERE UserID IN ('U004', 'U005', 'U006');
+--------+-----------+----------+------+------+
| UserID | FirstName | LastName | Sex  | Age  |
+--------+-----------+----------+------+------+
| U004   | 愛子      | 佐藤     | f    |   40 |
| U005   | 一平      | 高橋     | m    |   20 |
| U006   | 一郎      | 鈴木     | m    |   40 |
+--------+-----------+----------+------+------+
3 rows in set (0.00 sec)

##3. LOAD DATA FROM S3文を使ったデータ入力
LOAD DATA FROM S3文は、Aurora MySQLでサポートされるSQL。Aurora Serverlessでは利用不可なので注意。

###3-1. S3へのアクセスをAuroraに許可する
以下の作業が必要になる。

CSVファイル配置用のS3作成
AuroraがS3にアクセスするためのサービスロール作成
サービスロールをAuroraに追加
Auroraのパラメーター aws_default_s3_role の設定値をサービスロールのARNに変更
AuroraからS3へのアウトバウンド接続を許可するように設定

詳細はAmazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロードを参照。

###3-2. ファイルを用意
以下のSQLファイルをUTF-8で作成。

U007,美奈子,田中,f,20
U008,太郎,平岡,m,25
U009,航平,木村,m,25

###3-3. ファイルをS3へアップロード

S3のコンソールから対象のバケットを選択し、「アップロード」をクリック
「ファイルを追加」をクリックし、ポップアップからアップロードするローカルファイルを選択して「開く」をクリック。最後に「アップロード」をクリック
S3バケットにファイルが追加されたことを確認

###3-4. LOAD DATA FROM S3文を実行

LOAD DATA FROM S3文のシンタクスは以下のようになっている。

LOAD DATA FROM S3 [FILE | PREFIX | MANIFEST] 'S3-URI'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

詳細はこちらを参照。

実際に実行したLOAD DATA FROM S3文は以下。

MySQL [test]>  LOAD DATA FROM S3 's3://bucket-name/test-data3.csv' INTO TABLE tb1_Customer COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n';                                                                                                             
Query OK, 3 rows affected, 3 warnings (0.12 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 3

レコードを参照してみるときちんと入っている。

MySQL [test]> SELECT * FROM tb1_Customer WHERE UserID IN ('U007', 'U008', 'U009');
+--------+-----------+----------+------+------+
| UserID | FirstName | LastName | Sex  | Age  |
+--------+-----------+----------+------+------+
| U007   | 美奈子    | 田中     | f    |   20 |
| U008   | 太郎      | 平岡     | m    |   25 |
| U009   | 航平      | 木村     | m    |   25 |
+--------+-----------+----------+------+------+
3 rows in set (0.00 sec)

#ファイルへのデータ出力

方式	インプットファイルの形式	インプットファイルの配置先	結果
クライアントコマンドを利用	テキストファイル	Cloud9のEC2インスタンス	OK
SELECT INTO OUTFILE S3文	テキストファイル	S3	OK

##1. LOAD DATA LOCAL INFILE文を使ったデータ出力

###1-1. クライアントコマンドを利用してデータをファイルに出力
Auroraに未接続の状態で以下のMySQLコマンドを実行

$ mysql -h <Aurora MySQLのホスト名> -P <Aurora MySQLのポート番号> -u <DB接続ユーザー名> --ssl-ca=<証明書ファイルの絶対パス> -p test -e "select * from tb1_Customer;" > output1.csv

MySQLコマンドの内容

-e "sql" > <ファイル名> で実行したSQLの結果を指定したファイル名のファイルに出力できる。ファイルは事前に作成しておく必要はない
-pオプションでSQLを実行したいテーブルのDBを指定
--ssl-caオプションはAurora接続を暗号化する場合のみ必要

###1-2. ファイルにデータが出力されたことを確認
Cloud9のEC2インスタンスにファイルが追加され、レコードが格納されている。

##2. SELECT INTO OUTFILE S3文を使ったデータ出力

SELECT INTO OUTFILE S3文もLOAD DATA FROM S3文と同様に、Aurora MySQLでサポートされるSQLで、Aurora Serverlessでは利用不可。

###2-1. S3へのアクセスをAuroraに許可する
LOAD DATA FROM S3文を使ったデータ入力の際と手順は同じ。

###2-2. SELECT INTO OUTFILE S3文を実行

SELECT INTO OUTFILE S3文のシンタクスは以下。

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
        [HIGH_PRIORITY]
        [STRAIGHT_JOIN]
        [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
        [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
        [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
        [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
         [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
INTO OUTFILE S3 's3_uri'
[CHARACTER SET charset_name]
    [export_options]
    [MANIFEST {ON | OFF}]
    [OVERWRITE {ON | OFF}]

export_options:
    [FORMAT {CSV|TEXT} [HEADER]]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
]

詳細はこちらを参照。

実際には以下のSQLを実行。

MySQL [test]> SELECT * FROM tb1_Customer INTO OUTFILE S3 's3://bucket-name/outfile1' FORMAT CSV COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n';
Query OK, 9 rows affected (0.17 sec)

###2-3. S3に出力されたデータを確認

S3のコンソールから対象のバケットのオブジェクトを確認すると、ファイル outfile1.part_00000 が追加されている。

ファイルをクリックし、「ダウンロード」をクリック

ダウンロードしたファイルを開くと以下の内容となっている。

"U001","花子","山田","f",20,
"U002","太郎","山田","m",30,
"U003","太郎","渋谷","m",25,
"U004","愛子","佐藤","f",40,
"U005","一平","高橋","m",20,
"U006","一郎","鈴木","m",40,
"U007","美奈子","田中","f",20,
"U008","太郎","平岡","m",25,
"U009","航平","木村","m",25,

#参考
https://qiita.com/IysKG213/items/994e9f4ad12ff8aee322

PHP & JavaScript Room データのインポート・エクスポートファイル読込
 PHP & JavaScript Room データのインポート・エクスポートファイル出力

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up