[MySQL] テーブルにファイルをインポートする – mysqlimport編

  • このエントリーをはてなブックマークに追加
  • LINEで送る

MySQLへCSVなどのテキスト形式のデータファイルを一括でインポートします。
今回はLinuxなどのコマンドとして提供されているmysqlimportを利用します。通常MySQLをインストールした際に自動的に付いてきますのですぐに実行することが可能です。

$ mysqlimport -u username -p --local dbname tablename.tsv

シェルスクリプト内で実行する場合はこちらが手軽ですね。また大抵の場合、INSERT文を大量に発行するよりも高速にデータを入れることができます。

- Sponsored Link -

環境準備

テスト用テーブル

ここではサンプルとして書籍の情報を扱うDBとテーブルを作成し、データをインポートします。

/* DB作成 */
CREATE DATABASE book;
USE book;

/* テーブル作成 */
CREATE TABLE bookshelf(  -- 書棚テーブル
  id    int,
  title varchar(128),
  price int,

  PRIMARY KEY(id)
);

サンプルデータ

インポートするためのテキストファイルをタブ区切り形式で用意します。文字コードはUTF-8、改行コードはLFにしてあります(Windows上で動作しているMySQLへインポートする場合はCRLF)。またmy.cnfなどで文字コードの設定を変更している場合はそれに合わせます。1行目に各カラムのタイトルは付けません。

今回は1月28日時点のAmazonのコミック売上げランキングから抜き出してきました。どうでも良いですが0円の書籍はKindleのお試し版みたいですねw typoではありませんw

$ cat bookshelf.tsv
1   鬼滅の刃 19 484
2   戦争は女の顔をしていない1   990
3   若林くんが寝かせてくれない1  0
4   ゆるキャン△1 0
5   とんでもスキルで異世界放浪メシ5    614
6   異世界迷宮でハーレムを5    614
7   ダーウィンズゲーム1  0
8   幼女戦記17  614
9   若林くんが寝かせてくれない2  0
10  NEW GAME!10 792

サンプルデータをダウンロード

注意点として、mysqlimportコマンドを利用する場合はテーブル名とインポートするファイル名を同じ名前にする必要があります。拡張子は自由に設定することができます。

インポートする

実行

mysqlimportコマンドでインポートする場合は以下のように入力します。

$ mysqlimport -u foo -p --local book bookshelf.tsv
Enter password: 
book.bookshelf: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

各オプションは以下の通り。

  • -uの後にユーザー名を指定
  • -pを指定するとEnterを押した直後にパスワードを聞かれます。
    • パスワードをオプションに続けて直接書くと.bash_historyなどのログに記録されてしまうので止めた方が良いです。
  • --localは所定のディレクトリ以外の場所のファイルをインポートする場合に必要です。詳細は後述します。
  • データベース名(book)を指定
  • 最後にインポートするファイルのパス

インポートの実行にはFILE権限が必要です。

確認

MySQLへログインして正常にデータが入っているか確認しておきます。

$ mysql -u foo -p
Enter password:

mysql> use book;
mysql> select * from bookshelf;
+----+------------------------------------------------+-------+
| id | title                                          | price |
+----+------------------------------------------------+-------+
|  1 | 鬼滅の刃 19                                    |   484 |
|  2 | 戦争は女の顔をしていない1                      |   990 |
|  3 | 若林くんが寝かせてくれない1                    |     0 |
|  4 | ゆるキャン△1                                 |     0 |
|  5 | とんでもスキルで異世界放浪メシ5                |   614 |
|  6 | 異世界迷宮でハーレムを5                        |   614 |
|  7 | ダーウィンズゲーム1                           |     0 |
|  8 | 幼女戦記17                                     |   614 |
|  9 | 若林くんが寝かせてくれない2                    |     0 |
| 10 | NEW GAME!10                           |   792 |
+----+------------------------------------------------+-------+
10 rows in set (0.00 sec)

大丈夫そうですね。

CSVをインポート

カラムをカンマ(,)で区切ったファイル形式の方のCSVをインポートします。

サンプルデータ

先ほどのファイルをカンマ区切りに変換しました。また文字列のカラムはダブルコーテーションで囲うことにします。

$ cat bookshelf.csv 
1,"鬼滅の刃 19",484
2,"戦争は女の顔をしていない1",990
3,"若林くんが寝かせてくれない1",0
4,"ゆるキャン△1",0
5,"とんでもスキルで異世界放浪メシ5",614
6,"異世界迷宮でハーレムを5",614
7,"ダーウィンズゲーム1",0
8,"幼女戦記17",614
9,"若林くんが寝かせてくれない2",0
10,"NEW GAME!10",792

サンプルデータをダウンロード

実行

このCSVファイルをインポートするにはmysqlimportにオプションを2つ追加するだけです。

$ mysqlimport -u foo -p --local --fields-terminated-by="," --fields-enclosed-by='"' book bookshelf.csv

追加されたオプションは以下の通り。

  • --fields-terminated-byで区切り文字を指定(デフォルトはタブ)
  • --fields-enclosed-byは文字列などを囲う記号を指定(デフォルトは未指定)

タブ区切りもCSV?

余談ですが最近のCSVはタブや半角スペースで区切った物もCSV(Character-Separated Values)と呼ぶので、最初に取り上げたタブ区切りテキストもCSVではあるのですが、それはまた別の話w

類似したフォーマットとして、タブで区切られた tab-separated values (TSV)や、欧文間隔 (いわゆる半角スペース) で区切られた space-separated values (SSV) などがあり、これらをまとめて character-separated values (CSV)、delimiter-separated values (DSV) とも呼ばれることも多い。
Comma-Separated Values – Wikipedia

オプション

ヘッダ行をスキップ –ignore-lines

1行目にカラム名がある場合、このままだとカラム名までもデータとして取り込まれるか、テーブルのデータ型と合わない場合は弾かれてしまいます。

$ cat bookshelf.tsv
ID  タイトル    価格
1   鬼滅の刃 19 484
2   戦争は女の顔をしていない1   990
3   若林くんが寝かせてくれない1  0

--ignore-linesオプションでカラム名が書かれたヘッダ行をスキップすることができます。

$ mysqlimport -u foo -p --local --ignore-lines=1 book bookshelf.tsv

正確には--ignore-lines=はファイルの先頭から指定した行数をスキップするオプションですので、ファイルの途中からインポートするといったことにも応用できます。

エスケープ

カラムの中に区切り文字など処理場まずい文字が登場する場合にはバックスラッシュ(円記号)でエスケープしてやります。

$ cat bookshelf.csv 
1,鬼滅の刃\,19,484

$ mysqlimport -u foo -p --local --fields-terminated-by="," book bookshelf.csv

この場合はダブルコーテーションなどで囲い--fields-enclosed-byオプションを指定するやり方でも解決できます。

$ cat bookshelf.csv 
1,"鬼滅の刃,19",484

$ mysqlimport -u foo -p --local --fields-terminated-by="," --fields-enclosed-by='"' book bookshelf.csv

ホスト/ポートを指定 -h, –port

デフォルトだとlocalhostの3306番のポートへ接続しますが、-hまたは--hostオプションでホストを、--portオプションでポートを指定することができます。

$ mysqlimport -u foo -p --local -h db1.example.com --port 13306  book bookshelf.csv

サービスの規模が大きくなってくるとDBサーバは別に建てることが多いですし、手元にあるデータを遠隔地にあるサーバへ直接インポートしたい時にも重宝します。

注意点としては、事前にMySQLへログイン元のホストでユーザーを新規作成しておく必要があります。MySQLはログイン元のホストが異なると、ユーザー名が同じでも全く別のユーザーとして扱われます。ユーザー作成後にGRANTもお忘れなく。

AUTO INCREMENTの扱い –columns

特定のカラムを自動採番したい場合、データファイル上はその列を空にしておきます。

CREATE TABLE bookshelf(
  id    int AUTO INCREMENT,  -- NEW!
  title varchar(128),
  price int,

  PRIMARY KEY(id)
);

idがあった箇所を消しておくだけです。

$ cat bookshelf.tsv
    鬼滅の刃 19 484
    戦争は女の顔をしていない1   990
    若林くんが寝かせてくれない1  0
(中略)

もしカラムを自体が無かったことにしてしまうと、以下のように何だかよくわからないことになってしまいます。

$ cat bookshelf.tsv
鬼滅の刃 19 484
戦争は女の顔をしていない1   990
(中略)

$ mysqlimport -u foo -p --local book bookshelf.tsv
Enter password: 
book.bookshelf: Records: 10  Deleted: 0  Skipped: 0  Warnings: 20

$ mysql -u foo -p
mysql> use book;
mysql> select * from bookshelf;
+----+-------+-------+
| id | title | price |
+----+-------+-------+
|  1 | 484   |  NULL |
|  2 | 990   |  NULL |
(中略)
|  9 | 0     |  NULL |
| 10 | 792   |  NULL |
+----+-------+-------+

もしどうしてもデータファイル上からカラムを取り払ってしまいたい場合は、--columnsで指定することで回避できます。

$ cat bookshelf.tsv
鬼滅の刃 19 484
戦争は女の顔をしていない1   990
(中略)

$ mysqlimport -u foo -p --local --columns=title,price book bookshelf.tsv
Enter password: 
book.bookshelf: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

テーブルを空にしてからインポート –delete

--deleteオプションを指定すると、テーブルのデータを削除してからインポートしてくれます。

$ mysqlimport -u foo -p --local --delete book bookshelf.csv

これはTRUNCATE bookshelfではなく、DELETE FROM bookshelfのようでAUTO INCREMENTの値はリセットされません。

全テーブルをロックしてからインポート

インポートする際に
--lock-tables

トラブルシューティング

–localオプションを付けていない

MySQL5.7.16以降から特定のディレクトリ以外にあるファイルをインポートしようとすると、以下のようなエラーとなります。

mysqlimport: Error: 1290, The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, when using table: bookshelf

MySQLのシステム変数secure_file_privで指定されているディレクトリ以外にあるファイルを気軽にインポート出来なくする制約のようです。Linux環境の多くは以下の通り/var/lib/mysql-files/がデフォルトで割り当てられています。

mysql> SHOW GLOBAL VARIABLES LIKE 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

解決策としては--localオプションを付けて実行するか、secure_file_privで指定されているディレクトリにデータファイルを置く必要があります。

Warningsの具体的な内容を確認したい

どうもmysqlimportを利用した際のWarningsの内容はどこにも出力されないようです。もしWarningsの値も確認したい場合はLOAD DATA INFILE構文を利用する必要があります。

設定も確認したんですけどね。どなたかご存知でしたら教えてくださいませ。

mysql> SHOW GLOBAL VARIABLES LIKE 'log_warnings';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 2     |
+---------------+-------+

mysql> SHOW GLOBAL VARIABLES LIKE 'log_error';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+

参考ページ

- Sponsored Link -

ご質問やリクエストなどお気軽に。メールアドレスの入力は任意です。書き込みが反映されるまで時間がかかります。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください