More than 1 year has passed since last update.

【RDS&MySQL】CSVファイルをRDSのテーブルに取り込む方法(scp/load data/utf-8)

Posted at 2023-02-04

概要

ローカルにあるCSVファイルをRDSのテーブルへ取り込む方法を備忘も兼ねてまとめました。取り込むにあたって発生したエラー2件のことも記載しています。

前提

踏み台サーバー（EC2インスタンス)へSSH接続してからRDSへアクセスする手法を取る。
RDSはmysql8を利用しています。EC2やRDSのもろもろ設定は完了している前提。

Mac環境

機種ID：MacBookPro18,3
macOS：Monterey
チップ：Apple M1 Pro
メモリ：16GB

実行手順

ローカルにあるCSVを踏み台サーバーへコピーする
SSHトンネルで踏み台サーバーからRDSにアクセスする
RDSでデータベース・テーブルを作成する
CSVをRDSのテーブルへ取り込む
【エラー対応①】文字コード設定（utf-8関係）のエラー
【エラー対応②】800カラムあるCSVを取り込んだ際のエラー

ローカルにあるCSVを踏み台サーバーへコピーする

scpコマンドを使って、踏み台サーバーにCSVファイルをコピーします。以下の場合は直下に秘密鍵もCSVもある状態で実行していますが、異なる場合はディレクトリを指定してあげてください。

scp -i sample_ssh.pem sample_data.csv ec2-user@11.222.3.444:

SSHトンネルで踏み台サーバーからRDSにアクセスする

以下のコマンドでSSH接続します。

ssh -i sample_ssh.pem ec2-user@11.222.3.444

以下のコマンドでRDSインスタンスへアクセスします。
- エンドポイントはRDSの画面から確認できますのでコピーしてきましょう。
- 以下はport3306、ユーザー名がrootの場合です。RDS作成時に設定したものを入力しましょう。

mysql -h xxxx-xxxx-mysql8.xxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -P 3306 -u root -p

Passwordの入力画面になるので、RDS作成時に取得しているパスワードを入力します。
Welcome to the MySQL monitor.と頭に表示され、mysql>というプロンプト画面になったらアクセス完了です。

RDSでデータベース・テーブルを作成する

以下はターミナルから実行していますが、DBeaverなどのSQLクライアントソフトウェアアプリケーションを使って作成するのもOKです。（もちろんその際は上記のアクセス設定を事前に行う必要があります）
まずは以下コマンドでデータベースを作成します。

create database sample_db;

以下コマンドで作成しているデータベースを表示できます。

show databases;
+----------------------+
| Database             |
+----------------------+
| sample_data_20230101 |
| information_schema   |
| mysql                |
| performance_schema   |
| sample_db            |
| sys                  |
+----------------------+
6 rows in set (0.05 sec)

以下コマンドで作成したsample_dbを選択します。

use sample_db;

選択していない場合は以下のエラーが表示されます。

ERROR 1046 (3D000): No database selected

以下コマンドでテーブルを作成します。この時、テーブル定義書を参考にデータ型やバイト（桁数）、NOT NULL値などもあらかじめ決めておくと良いですね。（後でALTERコマンドで変えることも可能です）

create table samplelist (
sample_no INT NOT NULL,
sample_cd CHAR(2),
sample_nm INT
);

最後の以下のように)の前に,を入れると、ERRORになるので要注意。よくやってしまってしまう汗

create table samplelist (
sample_no INT NOT NULL,
sample_cd CHAR(2),
sample_nm INT, #ここです
);

↓こんなエラーが出ます

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 5

ちなみに、int(x)と指定してあげても、MySQL 8.0.19 からが表示されない仕様になったようです。DBeaverなどのツールからGUI的に明示的に変えてあげることは出来ましたが。
作成したテーブルの確認は以下コマンドで可能。

show tables;
+---------------------+
| Tables_in_sample_db |
+---------------------+
| list                |
| samplelist          |
+---------------------+
2 rows in set (0.00 sec)

作成したテーブルのカラムの確認は以下コマンド。

show columns from samplelist;
+-----------------------+---------+------+-----+---------+-------+
| Field                 | Type    | Null | Key | Default | Extra |
+-----------------------+---------+------+-----+---------+-------+
| sample_no             | int     | NO   |     | NULL    |       |
| sample_cd             | CHAR(2) | YES  |     | NULL    |       |
| sample_nm             | int     | YES  |     | NULL    |       |

作成したテーブルのデータ（レコード）確認は以下コマンド。
- まだ何も入っていない場合はemptyと出ます。

select * from samplelist;
Empty set (0.00 sec)

作成したテーブルのデータ（レコード）を選定して確認したい場合は以下コマンド。
- データを取り込む手順（CSVを取り込む方法）は次のステップで解説します

select sample_no from samplelist;
+------------+
| sample_no  |
+------------+
| 1232133151 |
| 1233456778 |
| 1234567891 |
| 1023455678 |
+------------+
4 rows in set (0.00 sec)

レコード（データ）を消す時は以下コマンド。ちゃんと消していいか確認してから実行しましょう。

DELETE FROM samplelist;

テーブルごと消す時は以下コマンド。ちゃんと消していいか確認してから実行しましょう。

DROP TABLE samplelist;

CSVをRDSのテーブルへ取り込む

以下のLOAD DATA 文でCSVファイルを作成したテーブルsamplelistに取り込むことができます。下記の場合は直下にあるCSVですが、ディレクトリに保存している場合はそちらを指定してあげましょう。

load data local infile "sample_data.csv " into table samplelist fields
terminated by ',' optionally enclosed by '"';

これにより、CSVデータがテーブルに取り込みができました。中身を確認する場合は上述したコマンドで確認してみましょう。
LOAD DATA構文の詳細は以下から：
- 参考：13.2.6 LOAD DATA INFILE 構文
ちなみに、かなり高速にテキストファイルを読み込めますね。30万レコードあっても数秒で終わりました。
CSV取り込み時にデータ型のエラーなどが起きた場合は、warningの数が表示されています。その時は直後に以下コマンドを実行して警告内容を確認しましょう。（直後に打たないと消えてしまうので要注意）

show warnings;

【エラー対応①】文字コード設定（utf-8関係）のエラー

CSVファイルを取り込んだときに表示された第1のエラー。

mysql ERROR 1300 (HY000): Invalid utf8mb4 character string: ''

文字コード設定が問題かな？と思って以下を一応確認。

mysql> show variables like 'character\_set\_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb3 |
| character_set_connection | utf8mb3 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb3 |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8mb3 |
+--------------------------+---------+
7 rows in set (0.03 sec)

ただ、この辺いじる前に以下記事を発見。確かに以前からCSVファイルの文字化けは悩みの種。同じような対応を以前別件でしたなぁ〜と思って念の為以下の手順で実行したら解決。
- CSVファイルを「開く」からNumbersで読み込む→ファイルメニューの「書き出し」でCSVを選択→「utf-8」を選択してエクスポートしたものを、再度読み込むと、無事にOKが表示。助かった。
- 参考：MySQL関連_備忘録

【エラー対応②】800カラムあるCSVを取り込んだ際のエラー

800カラム近くあるCSVを取り込んだ時、以下のエラーメッセージが返ってきました。

SQLエラー [1118] [42000]: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

ROW_FORMAT=DYNAMICやROW_FORMAT=COMPRESSEDをつけてSQL実行する解決したという記事もあったのですが、自分の場合は同じエラーでした。my.cnfのinnodb_file_format設定を変える必要があるという記事もありましたが、RDSの場合はパラメータグループからいじってあげる必要がありそう。mysql8で作成したRDSでしたが、パラメータグループにinnodb_file_formatが存在していない...うーむ、これは解決できず。そのまま打ち込むのではなく、必要なカラムだけ選定して取り込むことにしました。
- どなたか対応方法をご存知でしたら教えてくださいm(__)m

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