第29回　LOAD DATA INFILE構文でテキストファイルからMySQLにデータをロードする

初期データを投入する際に、mysqldumpで出力した際に作られるようなSQLの形式になっている場合は、mysqlクライアントで実行することでロードすることができました。しかし、外部で用意されているデータはTSVやCSVといったデータで渡されることもあります。TSVやCSVの形式からSQLへ変換を行っても良いのですが、わざわざ変換するのも大変です。そこで今回は、LOAD DATA INFILE構文を用いてTSVやCSVといったテキストファイルからMySQLにデータをロードする方法を紹介したいと思います。

検証環境

今回は第23回　mysqlslapを使って負荷テストをしてみようで使用したCentOS7で実行しています。MySQLのバージョンは5.7.13を使用しています。

LOAD DATA INFILE構文

LOAD DATA INFILE構文とは、テキストファイルで用意されたデータをMySQLにロードするための構文です。実はこの構文はこの連載中に一度現れていて、第2回　MySQLにはじめてのデータを入れてみるで紹介した住所番号のCSVデータの読み込みのときにも利用していました。

ここではもう一度住所番号のCSVデータの読み込みを復習してみようと思います。CSVファイルデータはMySQLサーバ上でダウンロード済みであり、変換済みの状態であるとします。また、zipcodeデータベースは作成済みとします。zipcodeテーブルは以下のように改変し定義を行いました。第2回との違いはcreated_atというデータ作成時の時間も保存するカラムを用意してみました。

mysql> CREATE TABLE zipcode.zipcode2(
    ->        code varchar(12) NOT NULL,
    ->        old_zipcode varchar(5) NOT NULL,
    ->        zip_code varchar(7) NOT NULL,
    ->        prefecture_kana varchar(255) NOT NULL,
    ->        city_kana varchar(255) NOT NULL,
    ->        town_kana varchar(255) NOT NULL,
    ->        prefecture varchar(128) NOT NULL,
    ->        city varchar(128) NOT NULL,
    ->        town varchar(128) NOT NULL,
    ->        created_at datetime NOT NULL
    ->      ) DEFAULT CHARACTER SET= utf8mb4;
Query OK, 0 rows affected (0.01 sec)

続いて以下のようなコマンドで、ファイルからデータのロードを行いました。

mysql> SET character_set_server= utf8mb4;
mysql> SET NAMES utf8mb4;
mysql> use zipcode
mysql>  LOAD DATA INFILE '/var/lib/mysql-files/KEN_ALL_UTF8.CSV' INTO TABLE zipcode.zipcode2
    ->  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    ->  (code, old_zipcode, zip_code, prefecture_kana, city_kana, town_kana, prefecture, city, town, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy)
    ->  SET created_at = CURRENT_TIMESTAMP;
Query OK, 123948 rows affected (1.13 sec)
Records: 123948  Deleted: 0  Skipped: 0  Warnings: 0

ここで読み込み先ファイルを/tmp/ディレクトリ配下から/var/lib/mysql-files/に変更しているのは、MySQLのセキュリティ機構である--secure-file-privオプションが5.7系のrpmではデフォルトで設定されており、File_priv権限を持っていても設定されたディレクトリ以外のファイルを読み込む事ができなくなったためです。--secure-file-privで、どのディレクトリが指定されているか確認したい場合は、以下のようにグローバル変数を確認してみましょう。

mysql> SELECT @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)

続いてLOAD DATA INFILEに関する部分だけを抜き出して見ていきます。今回は見やすさの都合で4行に分割していますが、実際に入力する際には1行で入力して問題ありません。

mysql>  LOAD DATA INFILE '/var/lib/mysql-files/KEN_ALL_UTF8.CSV' INTO TABLE zipcode.zipcode2
    ->  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    ->  (code, old_zipcode, zip_code, prefecture_kana, city_kana, town_kana, prefecture, city, town, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy)
    ->  SET created_at = CURRENT_TIMESTAMP;

このSQLの1行目には、'/var/lib/mysql-files/KEN_ALL_UTF8.CSV'内にあるCSVデータをzipcodeデータベース内のzipcodeテーブルにロードするという事が書いてあります。

LOAD DATA INFILE '/var/lib/mysql-files/KEN_ALL_UTF8.CSV' INTO TABLE zipcode.zipcode2

2行目のFIELDS以降は、その際のデータの扱いに関して記述されています。FILEDS TERMINATED BY ','という指定は今回指定したファイルがカンマ区切りの文字列であることを指定しています。TSVを用いる場合には FIELDS TERMINATED BY '\t'を指定してください。続いてFILEDSのオプションのOPTIONALLY ENCLOSED BY '"'ではCSVのフィールドの中で文字列の要素が、"という文字に囲まれている事を示しています。

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

ここでCSVの中身を一度確認してみると、以下のようになっています。

$ head -n 1 /var/lib/mysql-files/KEN_ALL_UTF8.CSV
01101,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0

たとえば、この指定を外してロードすると、""のよう読み込まれてしまうので、ファイルにあった形で宣言して上げる必要があります。またFIELEDSオプションのOPTIONALLYは、CSV or TSVの全ての要素に"が含まれている場合は必要ありません。今回は数字型の文字があるために付けています。

3行目の(と)で囲まれた部分には、CSVファイル上のカラムがデータベースのどのカラムに該当するかの指定を行っています。@dummyとしているのはユーザ変数で、その場合は代入されますが、使われないため読み捨てられることになります。

(code, old_zipcode, zip_code, prefecture_kana, city_kana, town_kana, prefecture, city, town, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy)

次のようなSQL実行していると考えてもらうとわかりやすいと思います。

INSERT INTO zipcode.zipcode2 SET
  code = 01101,
  old_zipcode = '060  ',
  ...
  @dummy = 0,
  @dummy = 0;

最後の4行めで行われているSETですが、こちらはCSVデータではなくMySQLの関数や変数を使ってデータを入力したい場合に使われます。今回は現在の時間を設定するCURRENT_TIMESTAMPをセットしてみました。

SET created_at = CURRENT_TIMESTAMP

次のようなINSERT文を実行していると考えてもらえうとわかりやすいと思います。

INSERT INTO zipcode.zipcode2 SET
  ...
  created_at = CURRENT_TIMESTAMP;

以下のSQLの結果からも現在の時刻が設定されていることがわかります。

mysql> SELECT created_at FROM zipcode2 limit 1;
+---------------------+
| created_at          |
+---------------------+
| 2016-09-20 11:06:38 |
+---------------------+
1 row in set (0.00 sec)

ここまででざっと簡単な使い方を説明してきましたが、LOAD DATA INFILE構文の全体は次のようになります。（⁠参考:13.2.6 LOAD DATA INFILE 構文）

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [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,...]

最後に、上記で書かれているそれぞれのオプションの意味について、以下のように簡単な表を作成しました。

オプション名	説明
LOW_PRIORITY	テーブルレベルのロックのみを使用するストレージエンジン（MyISAM、MEMORY、MERGEなど）を使用している場合に他のクライアントから読み取られなくなるまでINSERTするのを待つ。
CONCURRENT	MyISAMを使用している時にコンカレントインサートを利用したい場合に使用する。
LOCAL	MySQLに接続しているクライアントのファイルをロードする時に使用する。クライアントでファイルをロードするため、サーバ側でのFile_priv権限が無くても使用することができる。
REPLACE	データベース内にある既存のプライマリーキーやユニークキーが衝突した時に、入力値によって置き換える。
IGNORE	データベース内にある既存のプライマリーキーやユニークキーが衝突した時に、入力値が破棄される。
PARTITION	データをロードするパーティションを明示的に定義する。何らかの原因でいずれかのパーティションに書き込みを行えなかった場合はエラーになる。
CHARACTER SET	読み込むファイルの文字コードを指定する。
FIELDS, COLUMNS	テキストファイル中のフィールドの区切り文字の指定（デフォルト値はタブ文字）や、フィールドを囲っている文字の指定（デフォルト値は空文字⁠）⁠、文字列のエスケープの指定（デフォルト値は`'\\'`）を行う。
LINES	行の開始（デフォルト値は空文字⁠）⁠、行の末尾（デフォルト値は`'\n`）を表す文字を変更する。
IGNORE number (LINES \\|ROWS)	ヘッダなどがある場合に飛ばす行数を指定する。
col_name_or_user_var	CSVのデータをどのカラムにロードしたいか指定する場合に使用する。
SET col_name = expr	CSVファイルにない値を入力したい場合に使う。等MySQLの関数を利用して入力をしたい場合等に利用する。

まとめ

今回は、LOAD DATA INFILE構文でテキストファイルからMySQLにデータをロードする方法を紹介しました。これにより手元にあるCSVやTSVといったテキストファイルをMySQLにロードして、便利に利用できるようになりました。テキストファイルからSQLへの変換をプログラム的に用意するのも良いのですが、ファイルの数が多い場合や何回もロードする場合など、変換をかける方が大変な場合もあります。そういった時に、LOAD DATA INFILE構文を使ってデータの投入を行ってみてはいかがでしょうか。