MYSQLのDBに大容量のＣＳＶをUPしたいです。

質問

さくらインターネットのＭＹＳＱＬのＤＢに大容量（約１３ＭＢ）のＣＳＶファイルをアップロードする方法を教えて下さい。

下記(1)～(3)のようにいくつかの方法を試しましたが、結果的に全件をアップロードができていません。

(1)最初にレンタルサーバーのさくらインターネットでサーバーを借りましたが、phpMyAdminにＣＳＶファイルをアップロードしようしました。

kuin_mstという名前のテーブルを作成後、このテーブルを選択してインポート画面からインポートするＣＳＶファイルを選択し、設定として「ファイルの文字セット」をutf8に、
「インポートするファイルの形式」のＣＳＶ形式のフィールド区切り記号をカンマに、フィールド囲み記号を空白に指定しました。
そして、実行を押すと、しばらくして「Internet Explorerではこのページは表示できません。」が表示されました。

これに対してＦ５キーを押して再実行すると元の画面に戻り、kuin_mstテーブルの件数を見ると、122915件中58114件がアップロードされてました。

他のインポートするファイルの形式の「LOAD DATA する CSV」や「ＳＱＬ」も試しましたが、結果として何もアップロードされませんでした。

考察：１３ＭＢのデータを３回分に分割してこの作業を行えば目的が達成できるように思いますが、結果として入れるべき全件のデータがアップロードされたのかを検証する方法が分かりません。

(2)さくらはSSHが使えるのでpoderosaをダウンロードしてサーバーにアクセスしましたが、ＳＱＬのみ記述されたtest1.txtをアップロードするコマンドを次のように投入したら
テーブルの削除や作成は成功しエラーが発生しませんでした。

コマンド
「mysql -h mysql○○○.db.sakura.ne.jp -u アカウント名 -p ＤＢ名 < test1.txt」

しかし、このtest1.txt内に「LOAD DATA INFILE './KEN_ALL.CSV' INTO TABLE kuin_mst FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';」
の一行を加えて実行すると「ERROR 1045 (28000) at line 2513: Access denied for user 'ユーザー名'@'%' (using password: YES)」というエラーが発生します。

このエラーを調べると「コンソールからデータベース接続する時にパスワードが間違っている」と説明しているサイトがありましたが、何度確認しても間違えていませんし、間違えていたら「LOAD DATA ～」の行を
追加していない状態の処理で同じエラーが出るはずです。

ちなみに、自宅ＰＣのＷＥＢサーバーで、「LOAD DATA ～」を追加したtest1.txtをコマンドプロンプトで実行するとアップロードが正常に行われるのでＣＳＶファイル自体は問題がないはずです。

※KEN_ALL.CSVファイルは、郵便番号-住所データ（utf-8保存で約13MB）でレコードはカンマ区切り（例として1レコード抜粋→640941,ホッカイドウ,サッポロシチュウオウク,アサヒガオカ,北海道,札幌市中央区,旭ケ丘）
となっています。
※test1.txtとKEN_ALL.CSVの両ファイルは/home/アカウント名の階層に置いてこの処理を実行しました。

結果として何が原因でアップロードができないのかがよくわかりませんでした。

(3)ＢＩＧＤＵＭＰという大容量ファイルを分割してＤＢにアップロードできるツールを見つけたので、いろいろと説明があるサイトを参考にブラウザにて実行したらKEN_ALL.CSVファイルがFilenameの欄に表示されました。
同画面でstart import1をクリックしましたが、「You have to specify $csv_insert_table when using a CSV file. 」というメッセージが出てきました。

結果として、この意味がよくわかりませんでしたが、カンマ区切りのみのＣＳＶファイルだけをアップロードすることはできないのかもと思いました。

以上、悪戦苦闘しても解決できないのでどうかアドバイスを下さい。
宜しくお願いします。

■環境
UNIX系 OS FreeBSD
Apache 1.3.x
PHP 5.2.17
phpMyAdmin 3.3.10.3

MySQL 5.1
MySQL 文字コード UTF-8

bigdump ver.0.34b

回答No.5

MAXIMAX · ベストアンサー

#2 です。

Windows 形式のファイルになっていて改行コードが失敗の原因だとしたら・・・・・なのですが、LOAD DATA の最後にある '\n' を '\r\n' にしてあげればたぶんロードできるようになりますよ。
これでもロードできないのであれば、他の原因かもしれません・・・・・・・。

お礼

ご回答ありがとうございます。

'\n' を '\r\n' に変えてコマンドを投入してみましたが、「LOAD: Command not found.」がでました。
色々と調べているとＭＹＳＱＬにログインできましたので、そこで次のコマンドを投入したらキレイに全件アップロードできました。

LOAD DATA LOCAL INFILE 'ぱす/KEN_ALL.csv' INTO TABLE ＤＢ名.kuin_mst character set 'utf8' FIELDS TERMINATED BY ',';

皆さまには解決するまでお付き合いいただき感謝しています。
中でも一番多く回答をしてくれたMAXIMAXさんをベストアンサーとさせていただきます。

ありがとうございました。

回答No.6

nora1962

自宅環境が分からないのでなんとも言えません。

自宅PCがwindowsなら「FileZilla_3.5.1_win32-setup.exe」がいいでしょう。

お礼

度々のご回答ありがとうございます。

windowsなので教えていただいたものを使ってみます。
成功しましたが、教えていただいた事は無駄にいたしません。

本当にありがとうございましたm(__)m

回答No.4

tomaju

> 「ERROR 1045 (28000) at line 2513: Access denied for user 'ユーザー名'@'%' (using password: YES)」というエラーが発生します。

ユーザーにFILE権限がないと LOAD DATA INFILE に失敗するそうです。

grant file on *.* to ユーザー名@localhost;

で権限を与えるとよさそうです。

LOAD DATA INFILEにはFILE権限
http://d.hatena.ne.jp/kaishitaeiichi/20101114/1289751855

お礼

ご回答ありがとうございます。

test1.txtの中のLOAD DATA文の直前に「grant file on *.* to ユーザー名@localhost;」を
入れましたが、やはり「Access denied for user 'angelito'@'%' (using password: YES)」というエラーが出てしまいます。
ＭＹＳＱＬにログインしてからgrant文を実行すればよいのかと思い、
「mysql -u ユーザー名 -p」を実行してみましたが、
「Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)」が出てしまいます。

この解決法を調べるとmy.cnfをいじらなければいけない様な感じがありますが、レンタルサーバーなのでいじれないように思えます。

回答No.3

nora1962

phpMyAdminはphp経由で一旦ファイルで受けてからロードするのでその制限を受けている可能性はあります。
逃げ方はすでに指摘されていますがSCPかSFTPなどでファイル全体をサーバー側に送ってしまいLOAD DATAするのが確実だと思います。
改行コードはサーバー上ならhexdump | less で0D 0A になっているか0Aなのか判別できます。

お礼

ご回答ありがとうございます。
さくらはSSHが使えるので、SCPとSFTPを調べたらSFTPの方がよさそうだと思い、FileZillaをダウンロードすることにしました。
しかし、よく分からないのが最新のダウンロードファイルには下記の５つがあるのですが、
どれをダウンロードすればよいのでしょうか？
私のＰＣのｏｓはVISTAです。

FileZilla_3.5.1_x86_64-linux-gnu.tar.bz2

FileZilla_3.5.1_win32.zip

FileZilla_3.5.1_win32-setup.exe

FileZilla_3.5.1_src.tar.bz2

FileZilla_3.5.1_powerpc-apple-darwin9.app.tar.bz2

回答No.2

MAXIMAX

#1 です。それがエラーの原因かどうかはさておき、なのですが・・・・・・。

簡単に見分ける方法として、CSV ファイルを Windows 上のメモ帳で開いて、ちゃんと改行されて見えるなら、改行コードは'\r\n'になっています。UNIX の '\n' 形式を Windows 上のメモ帳で開くと、改行されずに全部の行がつながって見えます。バイナリエディタでみたり、改行コードの表示に対応したエディアなどで調べるのもひとつの手ですが・・・・・・。

UNIX 上でなら、vi でファイルを開いて、行の最後に ^M が表示されているようなら、Windows 形式の改行になっています(全部の vi でそうなるか、ちょっと自信がありませんが)。

-----
改行コードについて、Windows 上でのテキストファイルは、通常'\r\n'形式が標準ですが、UNIX では '\n' 形式が標準です。Windows 形式のファイルを UNIX で扱うと、この'\r' も一文字文のデータとして扱われることが多く、誤動作の原因になります。このため、両方で扱うテキストについて、改行コードはきちんと把握しておく必要があります。

ただし、SCP などを使って転送する場合、テキストモードだと改行コードの自動変換などが起きることもありますので、間でファイルのやりとりするときも、気を付けないと意図せず改行コードが変わることがあります。

お礼

度々ありがとうございます。

CSV ファイルを Windows 上のメモ帳で開いて、ちゃんと改行されてみえます。
改行コードにも意識してSFTPあたりを使ってアップロードしてみます。

回答No.1

MAXIMAX

IE からのインポートだと、もしかするとインポートの処理時間がかさむと IE がタイムアウト起こして中止されてしまうのかもしれません。

エラーの内容自体はたしかにちょっと「？」な感じなのですが、実際にエラーの出ている行はなにもおかしなことは見当たらない、ということでしょうか？？

状況から考えると、LOAD DATA で正常にインポートできるサーバとサクラのサーバの間で、なにか環境に違いがあるのではないかな、と思います。一応かかれてはいなかったのでちょっと質問させていただきたいのですが、CSV などで気になるのは、改行コードくらいかな・・・・と思うのですが、CSV は Windows 形式の改行(実態は '\r\n')ではなく、UNIX 形式の改行(実態も '\n' だけ)になってはいますでしょうか？LOAD DATA の最後の '\n' を '\r\n' で試してみてはいかがでしょうか。

お礼

ご回答ありがとうございます。
実際にエラーの出ている行はなにもおかしなことは見当たらないです。

ＣＳＶの改行コードについて調べようとエディッタ（Apsaly）でＣＳＶファイルを開いていろいろと表示
形式を変えてみましたが、Windows 形式の改行が'\r\n'となっているのも確認できませんでしたし、
サーバーの中にあるＣＳＶファイルをメモ帳で開くをして開いても改行コードがみれませんでした。

初歩的な質問で申し訳ありませんが、改行コードの実態の確認方法を教えて下さいm(__)m