大量レコードをレンタルサーバーのMySQLへ一括インポートのコツ

レンタルサーバーに対してレコード数の多いCSVファイルを一括インポートするのは、
決して楽な作業ではありませんが「load data LOCAL infile」を利用すると、
レンタルサーバーであっても大量のデータのインポートが行え便利です。
ここではXSERVERに対して国税庁の公開している「法人の基本３情報のデータ(法人番号)」を、
実際にインポートしてみてハマった実例からご紹介します。

本投稿は以下で実際にインポートを行った際のメモになります。

「基本３情報」データのMySQLインポート（国税庁法人番号公表サイトデータ）

1 大量レコードをレンタルサーバーのMySQLへ一括インポートのコツ
2 最終的には以下の様なPHPからSQLを発行し実行

大量レコードをレンタルサーバーのMySQLへ一括インポートのコツ

レンタルサーバーを利用していてストレスとなるのが大量のデータインポートです。
SSHなどに慣れている方であれば、
mysqldumpなどを使ってサクッと処理してしまうかもしれません。

XSERVERもSSHには対応しているので、
それでも良さそうだったのですが、接続がうまくいかず今回は一旦断念しました。

mysqldumpで新たにCSVファイルからインポートするには、
一工夫が必要になったりするかもしれません。（やったことない）

そこで、誰でもできる方法で大量のCSVを、
レンタル共有サーバー上のMySQLに取りこめないかと試行錯誤をしました。

CSVファイルのphpAdmin経由のインポートをそのままインポートツールで行うと、
データのサイズによっては時間がかかり過ぎて、
途中でエラー画面が出てしまうかもしれません。よく出会う症状でしょう。

Web取り込み時のタイムアウト・「load data LOCAL infile」を利用したCSV取込

そこで今回はPHPのプログラムを利用してインポートを実施しました。

ただ地味に手こずる事と思います。
人それぞれの環境によってできたりできなかったりと障壁はそれぞれだと思います。
ですが、私の環境でひとまずうまくいったという方法を共有いたします。

load data LOCAL infileの構文ヘルプはかなり長いが要確認

MySQLには「LOAD DATA INFILE 構文」があり、
非常に高速にCSVファイルのインポートを行ってくれます。

エラー内容が確認しにくかったりと苦労する部分もありますが、
どうしても大きなCSVファイルをインポートしなければならない場合には非常に便利です。

（公式）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.6 LOAD DATA INFILE 構文

phpMyAdminからのSQL発行は、利用できない（模様）

今回はPHPでプログラムを書いてインポートを行っています。
phpMyAdmin側から実行すると以下の様なエラーメッセージがでてうまく取り込めませんでした。

#1148 – The used command is not allowed with this MariaDB version

エラーメッセージの内容としてはMySQLバージョンによって対応していないと読み取れます。
MySQLは5.5がレンタルサーバー上で稼働しています。（XSERVSERでは5.7を推奨しています）

MySQL5.7のphpMyAdminで実行した場合は、以下の様なメッセージに変わりました。

#1148 – The used command is not allowed with this MySQL version

ただ、このメッセージが出ている同じ環境ですが、
今回のPHPによる処理では無事取り込めています。

ヘルプを読む限りは、phpMyAdminでは「load data LOCAL infile」は、
サポートされないという事が原因のようです。

MySQLで「load data LOCAL infile」が利用可能か確認する

インポートする際に、データのエンコードと合わせる

現在利用しているMySQLのデフォルトエンコードは「UTF-8」にて設定しています。

国税庁の「法人の基本３情報のデータ(法人番号)」のデータは、
以下の通り２つのエンコードに対応しています。

ファイル形式・文字コード

　ファイル形式は、CSV及びXML形式の２種類、文字コードは「Shift-JIS（JIS第一・第二水準）」と「Unicode（JIS第一～第四水準）」の２種類に対応しています。
ファイル形式との組合せで「CSV・Shift-JIS」、「CSV・Unicode」及び「XML・Unicode」の３種類のファイルを用意しています。

（概要）基本３情報ダウンロード｜国税庁法人番号公表サイト

尚、「法人の基本３情報のデータ(法人番号)」はWebAPIを提供しています。
通常は自DBに対してデータのインポートを行わずWebAPI経由で参照すれば十分であるはずです。
そこを訳あって今回は自DBに取り込む必要があり実施しています。

「Shift-JIS」と「Unicode」に対応していると書かれていますが、
実際、「Shift-JIS」がMySQLで言うところの「sjis」なのか「cp932」なのか分からず悩み、
また「Unicode」が「UTF-8」なのか「UTF-16」なのか分からず、苦戦しました。

とりあえずのところインポートの実行前に「set character_set_XXX」にて、
エンコードの指定を行うのですが、

ダウンロードファイルが「Shift-JIS」なら「sjis」「cp932」のどちらも取り込めました。
ダウンロードファイルが「Unicode」なら「UTF8mb4」を指定する事で取り込むことができました。

MySQLのクライアントとサーバー側の双方のエンコードを一時的に変更するSQLを実行し、
データのエンコードを正しく解釈されるように処理しています。

set character_set_connection = UTF8mb4;
set character_set_results = UTF8mb4;
set character_set_client = UTF8mb4;
set character_set_server = UTF8mb4;
set character_set_database = UTF8mb4;

これは現在の接続時のみのエンコード指定として有効なもので、
再接続時には無効になり、デフォルトエンコードに戻されます。

SQL実行時に都度、セットする必要があります。

SQLの組み立て時にヒアドキュメント構文は避けた方が無難

PHPからSQLを組み立てる際に便利なのがヒアドキュメント構文で、
複数行のテキストを一括して変数に格納する事ができます。

以下はダメだった例

		$tmp =<<<tmp
set character_set_connection = UTF8mb4;
set character_set_results = UTF8mb4;
set character_set_client = UTF8mb4;
set character_set_server = UTF8mb4;
set character_set_database = UTF8mb4;
 load data LOCAL infile '{$dirpath}{$filename}' into table gov_nta_houjin_bangou_{$enc} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' 
 LINES STARTING BY '' TERMINATED BY ". "'\\n'" . " IGNORE 0 LINES 
 (sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId, 
 prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate, 
 latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);
tmp;

$tmp =<<<tmp

set character_set_connection = UTF8mb4;

set character_set_results = UTF8mb4;

set character_set_client = UTF8mb4;

set character_set_server = UTF8mb4;

set character_set_database = UTF8mb4;

load data LOCAL infile '{$dirpath}{$filename}' into table gov_nta_houjin_bangou_{$enc} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY ''

LINES STARTING BY '' TERMINATED BY ". "'\\n'" . " IGNORE 0 LINES

(sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId,

prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate,

latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);

tmp;

ヒアドキュメント構文を利用してSQLを組み立ててSQLを実行しても、
通常は意識することなく問題なく利用できます。

ですが、今回「load data LOCAL infile」を上記のようにすると、
どうにもうまく動かなかったのに悩みました。

各行の末尾には「\r\n」の改行コードが実際には付加されています。（Windowsの場合）
デバッグ実行した際などにはその改行コードの確認もできますが、
どうも、その改行コードが邪魔しているのか、
ヒアドキュメント構文を止めて、単独でSQLを組み立てていくとうまくいきました。

$tmp .= " load data LOCAL infile '{$dirpath}{$filename}' into table gov_nta_houjin_bangou_{$enc} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' ";
$tmp .= " LINES STARTING BY '' TERMINATED BY ". "'\\n'" . " IGNORE 0 LINES ";
$tmp .= " (sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId, prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate, latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);";

$tmp .= " load data LOCAL infile '{$dirpath}{$filename}' into table gov_nta_houjin_bangou_{$enc} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' ";

$tmp .= " LINES STARTING BY '' TERMINATED BY ". "'\\n'" . " IGNORE 0 LINES ";

$tmp .= " (sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId, prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate, latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);";

無用な躓きを減らす意味でもこの場合ヒアドキュメントはさせた方が無難かもしれません。

また以下の通りSQLも単独で発行した方が無難です。

「load data LOCAL infile」SQLの発行は単独で行う

上記のエンコード指定とSQLの発行を、同時に発行した場合には私はうまく取り込めませんでした。
以下の様なSQLを組み立てたの場合です。（SQLは上記と同じです）

以下はダメだった例

		$tmp =<<<tmp
set character_set_connection = UTF8mb4;
set character_set_results = UTF8mb4;
set character_set_client = UTF8mb4;
set character_set_server = UTF8mb4;
set character_set_database = UTF8mb4;
 load data LOCAL infile '{$dirpath}{$filename}' into table gov_nta_houjin_bangou_{$enc} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' 
 LINES STARTING BY '' TERMINATED BY ". "'\\n'" . " IGNORE 0 LINES 
 (sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId, 
 prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate, 
 latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);
tmp;

$tmp =<<<tmp

set character_set_connection = UTF8mb4;

set character_set_results = UTF8mb4;

set character_set_client = UTF8mb4;

set character_set_server = UTF8mb4;

set character_set_database = UTF8mb4;

load data LOCAL infile '{$dirpath}{$filename}' into table gov_nta_houjin_bangou_{$enc} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY ''

LINES STARTING BY '' TERMINATED BY ". "'\\n'" . " IGNORE 0 LINES

(sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId,

prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate,

latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);

tmp;

このSQLを変数に格納し、

$con = new mysqli($host, $user, $pass, $db);
$msql_result = $con->query($tmp);

としてしまう様なアプローチです。

mysqliクラスのquery()はSQLの一括実行には対応していると認識していますが、
うまくいきませんでした。

PHP: mysqli::query – Manual

ちなみに、mysql_query()関数では、
複数のSQLの一括実行はサポートされない事が明記されています。

mysql_query() は、ひとつのクエリを送信します (複数クエリの送信はサポートしません)。送信先は、link_identifier で指定したサーバー上にある、現在アクティブなデータベースです。

（抜粋引用）PHP: mysql_query – Manual

SQL一括発行を止めて、それぞれを別々に実行するようにすると、動作しました。
少なくとも「load data LOCAL infile」は単独実行が無難でかと思います。

以下は実行例の抜粋

$con->query(" set character_set_connection = {$enc};");
$con->query(" set character_set_server = {$enc};");
$con->query(" set character_set_database = {$enc};");

$con->query(" set character_set_client = {$enc};");
$con->query(" set character_set_results = {$enc};");

（中略）

//ヒアドキュメント構文は無用な\nが挿入されここでは使用しない。
$sql = " load data LOCAL infile '{$_SERVER['DOCUMENT_ROOT']}{$dir}{$filename}' into table gov_nta_corpnum_{$enc} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' ";
$sql .= " LINES STARTING BY '' TERMINATED BY ". "'\\n'" . " IGNORE 0 LINES ";
$sql .= " (sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId, prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate, latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);";

$msql_result = $con->query($sql);

（続く）

$con->query(" set character_set_connection = {$enc};");

$con->query(" set character_set_server = {$enc};");

$con->query(" set character_set_database = {$enc};");

$con->query(" set character_set_client = {$enc};");

$con->query(" set character_set_results = {$enc};");

（中略）

//ヒアドキュメント構文は無用な\nが挿入されここでは使用しない。

$sql = " load data LOCAL infile '{$_SERVER['DOCUMENT_ROOT']}{$dir}{$filename}' into table gov_nta_corpnum_{$enc} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' ";

$sql .= " LINES STARTING BY '' TERMINATED BY ". "'\\n'" . " IGNORE 0 LINES ";

$sql .= " (sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId, prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate, latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);";

$msql_result = $con->query($sql);

（続く）

SQL特殊文字のエスケープ処理は忘れずに

上のSQLでもエスケープはしていますが、MySQLの特殊文字は以下があります。
「’」や「”」を文字列として扱うには、エスケープする必要があります。

表 9.1 特殊文字エスケープシーケンス

エスケープシーケンスシーケンスが表す文字

\0 ASCII NUL (0x00) 文字。

\' 単一引用符 (「'」) 文字。

\" 二重引用符 (「"」) 文字。

\b バックスペース文字。

\n 改行 (ラインフィード) 文字。

\r 復帰改行文字。

\t タブ文字。

\Z ASCII 26 (Ctrl+Z)。表に続いて記されている注釈を参照してください。

\\ バックスラッシュ (「\」) 文字。

\% 「%」文字。表に続いて記されている注釈を参照してください。

\_ 「_」文字。表に続いて記されている注釈を参照してください。

（抜粋引用）MySQL :: MySQL 5.6 リファレンスマニュアル :: 9.1.1 文字列リテラル

エスケープシーケンス	シーケンスが表す文字
`\0`	ASCII NUL (`0x00`) 文字。
`\'`	単一引用符 (「`'`」) 文字。
`\"`	二重引用符 (「`"`」) 文字。
`\b`	バックスペース文字。
`\n`	改行 (ラインフィード) 文字。
`\r`	復帰改行文字。
`\t`	タブ文字。
`\Z`	ASCII 26 (Ctrl+Z)。表に続いて記されている注釈を参照してください。
`\\`	バックスラッシュ (「`\`」) 文字。
`\%`	「`%`」文字。表に続いて記されている注釈を参照してください。
`\_`	「`_`」文字。表に続いて記されている注釈を参照してください。

今回のSQLで言えば、以下の様な部分です。
PHP側でSQLを組み立てた際に、特殊文字のエスケープが必要でした。

以下は一部抜粋。

FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘\“‘ ESCAPED BY ”
LINES STARTING BY ” TERMINATED BY “. “‘\\n‘” . ” IGNORE 0 LINES (sequenceNumber, ….

MySQLWorkBenchを利用して直接ローカルに対して、
SQLを発行した場合には、エスケープする必要がなかったので、
PHPから実行する場合には、この点は気づきにくいかもしれません。

取込後のデータベースファイルサイズには注意を

今回の国税庁の「法人の基本３情報のデータ(法人番号)」を自前のDBでホストするとなると、
2018/7/6時点におけるデータでは、
4,841,895レコードにもなり、データベース容量も1.5Gに達しています。

460万レコード・1.5GBテーブル・「load data LOCAL infile」を利用したCSV取込

正直、レンタルサーバー上ではちょっと重たいかなという状況です。

大きなファイルをDBにインポートする際には、
ご契約のレンタルサーバーの制限にもご注意ください。

XSERVERでは、上限目安は1Gとされていますが、
一応、1.5Gは取りこむことはできています。（別途制限を受ける可能性もあります）

最終的には以下の様なPHPからSQLを発行し実行

詳細は以下でご紹介していますが、実行したPHPをご紹介しておきます。

データインポートSQL実行文(load data LOCAL infile)・法人の基本３情報のデータ(法人番号)

<?php
$db = 'db';
$host = 'host';
$user = 'user';
$pass = 'pass';
$dir = "/dev/ntagojp_unicode/";   //csvをアップロードしたディレクトリ

$con = new mysqli($host, $user, $pass, $db);
$enc = 'utf8mb4';  //sjis, cp932などエンコードを指定

$con->query(" set character_set_connection = {$enc};");
$con->query(" set character_set_server = {$enc};");
$con->query(" set character_set_database = {$enc};");

$con->query(" set character_set_client = {$enc};");
$con->query(" set character_set_results = {$enc};");

$data_date = date("Ymd");
$data_date = "20180629";		//適宜上書きで日付指定
$pref = array( 0 => array('01', "hokkaido"));		//47都道府県＋海外データを適宜指定
$filename = "{$pref[0][0]}_{$pref[0][1]}_all_{$data_date}.csv";

//ヒアドキュメント構文は無用な\nが挿入されここでは使用しない。
$sql = " load data LOCAL infile '{$_SERVER['DOCUMENT_ROOT']}{$dir}{$filename}' into table gov_nta_corpnum_{$enc} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' ";
$sql .= " LINES STARTING BY '' TERMINATED BY ". "'\\n'" . " IGNORE 0 LINES ";
$sql .= " (sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId, prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate, latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);";

$msql_result = $con->query($sql);
if( $msql_result === false ){
	$msg = "SQLの実行に失敗しました";
}else{
	$msg = "インポート終了";
}
$cnt_sql = "SELECT prefectureName, count(prefectureCode) FROM gov_nta_corpnum_utf8mb4 GROUP BY prefectureCode;";
$cntresult = $con->query($cnt_sql);
if($cntresult->num_rows > 0){
	while ($row = $cntresult->fetch_assoc()) {
		$arr[] = $row;
	}
}else{
	$arr = false;
}
echo $msg . '<br/>' .print_r($arr, true ) . '<br/><code>' . $sql . '</code>' ;

<?php

$db = 'db';

$host = 'host';

$user = 'user';

$pass = 'pass';

$dir = "/dev/ntagojp_unicode/"; //csvをアップロードしたディレクトリ

$con = new mysqli($host, $user, $pass, $db);

$enc = 'utf8mb4'; //sjis, cp932などエンコードを指定

$con->query(" set character_set_connection = {$enc};");

$con->query(" set character_set_server = {$enc};");

$con->query(" set character_set_database = {$enc};");

$con->query(" set character_set_client = {$enc};");

$con->query(" set character_set_results = {$enc};");

$data_date = date("Ymd");

$data_date = "20180629"; //適宜上書きで日付指定

$pref = array( 0 => array('01', "hokkaido")); //47都道府県＋海外データを適宜指定

$filename = "{$pref[0][0]}_{$pref[0][1]}_all_{$data_date}.csv";

//ヒアドキュメント構文は無用な\nが挿入されここでは使用しない。

$sql = " load data LOCAL infile '{$_SERVER['DOCUMENT_ROOT']}{$dir}{$filename}' into table gov_nta_corpnum_{$enc} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' ";

$sql .= " LINES STARTING BY '' TERMINATED BY ". "'\\n'" . " IGNORE 0 LINES ";

$msql_result = $con->query($sql);

if( $msql_result === false ){

$msg = "SQLの実行に失敗しました";

}else{

$msg = "インポート終了";

}

$cnt_sql = "SELECT prefectureName, count(prefectureCode) FROM gov_nta_corpnum_utf8mb4 GROUP BY prefectureCode;";

$cntresult = $con->query($cnt_sql);

if($cntresult->num_rows > 0){

while ($row = $cntresult->fetch_assoc()) {

$arr[] = $row;

}

}else{

$arr = false;

}

echo $msg . '<br/>' .print_r($arr, true ) . '<br/><code>' . $sql . '</code>' ;

色々と躓きつつも「load data LOCAL infile」でCSVのインポートが利用できると、
非常に膨大なデータをレンタルサーバー上のMySQLにもサクッと取り込むことができます。

地味に苦労はしますが、一回でも自分の環境に合ったできる方法が見つかれば、
後はそのソースの編集だけで、流用ができるようになって便利なハズです。

少し、チャレンジしてみてはいかがでしょうか。

本サイトで公開しているツールについては、
十分にテストは行っておりますが個人で作成している為、
潜在的なバグがないとは言い切れません。
その為、ツールを用いた結果については十分検証の上ご利用ください。
当サイトおよび、管理人は如何なる損害もその責を負いません。

当サイト内のコンテンツおよび画像において、
出典・引用の外部著作権者の明記がないものは、
すべて管理人による著作物です。
当サイトでご紹介しておりますコンテンツの著作権の放棄は致しません。

サイト内コンテンツを引用される際にはご連絡は不要です。
ただし、出典元として当サイト(個別記事)へのリンクをお願いいたします。
申し訳ございませんが、無断転載、複製をお断りさせて頂いております。

コンテンツを有益であると感じていただけましたら非常に光栄です。
ありがとうございます。

大量レコードをレンタルサーバーのMySQLへ一括インポートのコツ

大量レコードをレンタルサーバーのMySQLへ一括インポートのコツ

load data LOCAL infileの構文ヘルプはかなり長いが要確認

phpMyAdminからのSQL発行は、利用できない（模様）

インポートする際に、データのエンコードと合わせる

ファイル形式・文字コード

SQLの組み立て時にヒアドキュメント構文は避けた方が無難

「load data LOCAL infile」SQLの発行は単独で行う

SQL特殊文字のエスケープ処理は忘れずに

取込後のデータベースファイルサイズには注意を

最終的には以下の様なPHPからSQLを発行し実行

シェア頂けると新機能開発の励みになります！！

Message コメントをキャンセル