ファイルを利用したAurora MySQLのデータ入出力
検証環境
-
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クライアント
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」をクリック
ちなみにアップロードしたファイルが配置される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へアップロード
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,
参考
PHP & JavaScript Room データのインポート・エクスポート ファイル読込
PHP & JavaScript Room データのインポート・エクスポート ファイル出力
Author And Source
この問題について(ファイルを利用したAurora MySQLのデータ入出力), 我々は、より多くの情報をここで見つけました https://qiita.com/nkrk/items/7a0ecd6b7f6becfae4b6著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .