前回、DAOを使用してMySQLへのCSV取込にチャレンジしましたが失敗。。。
ということで別の手法にチャレンジします。
作業の流れは以下の通りとします。
- 日本郵便のサイトよりCSVファイル（圧縮ファイル）をダウンロード
- ダウンロードした圧縮ファイルを解凍
- 文字コードをサーバの仕様にあわせてに変換
- MySQLへファイルを取り込む
といった感じでしょうか。
それでは順を追ってやってみます。
まずはCSVファイルをダウンロード。その前に・・・
自分のホームディレクトリで作業をしても別によいのですが、せっかくなので一時ファイル置き場的な役割を持つ/tmpに移動して作業するとしましょうか。
cd /tmp
で簡単に移動できます。
でファイルのダウンロードですが、これは
wget ファイルのアドレス
と入力することで可能です。
今回の場合だと
wget http://www.post.japanpost.jp/zipcode/dl/oogaki/lzh/ken_all.lzh
と入力することでカレントディレクトリ（今滞在しているディレクトリ：今の場合先ほど移動した「/tmp」）にファイルをダウンロードすることができます。
続いては、ご覧の通りLZH形式で圧縮されているファイルを解凍してやる必要がありますが、これは
lha ファイル名
で解凍することが可能です。
ちなみに自分の場合「ｌha」コマンドがインストールされておらず作業ができなかったので、まずはコマンドのインストールから。
ターミナルで
apt-get update
apt-get install lha
と入力すればコマンドのインストールは可能ですので。
インストールができた、または元から入っている場合はそのまま
lha ken_all.lzh
にて解凍することができ、「ken_all.csv」が現れます。
ファイルが解凍できたら必要に応じて文字コードの変換を行います。元々のファイルの文字コードはShift-JISですが、自分のサーバはサーバ本体もMySQLもUTF-8を使用していますのでこのままでは文字化けしてしまいます。
よってShift-JISからUTF-8への変換が必要なのですがこの場合
iconv -f ○○ -t ×× < 元のファイル名 > 変換後のファイル名
と入力することで変換を行います。
iconv -f sjis -t utf8 < ken_all.csv > ken_all_utf8.csv
となりますが、お使いの環境に合わせて「-t utf8」の部分や変換後のファイル名は修正してください。
ここで注意というか後々自分が嵌った点だったのですが、MySQLにファイルを取り込む際にパーミッションの関係で取り込みができない！という現象が起こり得ますので、ディレクトリ及びファイルのパーミッションを変更しておきます。
上でも書いたとおり、/tmpは一時的な作業用ディレクトリとして使う場合が多く重要なファイルを放置しておく場所ではないので（というかそういう使い方をしないといけなくなりますが）ディレクトリのパーミッションは「777（読み：可=4 + 書き：可=2 + 実行：可=1かつ所有者以外の誰でもできる）」にしておきましょう。
なぜなら後ほどMySQLがファイルの取り込みを行うにあたって、MySQLを動作させているユーザは「mysql」であるのに対してファイルの所有者がダウンロードしたユーザになってしまい、mysqlユーザがゲストとして作業するためにはこうしないとファイルを読み込むことができないのです。
よって
chmod 777 /tmp/
chmod 777 /tmp/ken_all_utf8.csv
と入力してパーミッションを変更しておきましょう。ちなみに/tmpのパーミッションは一度変えてしまえば自分で再びパーミッション変更を行わない限り777がキープされますが、ファイルはダウンロードするたびに「644」になってしますので注意が必要です。逆の言い方をすればダウンロードするたびに「chmod 777 /tmp/ken_all_utf8.csv」を実行しないといけないということですね。
続いていよいよMySQLにファイルを取り込むSQLを作成します。これは
load data local infile 'ファイルのフルパス' into table テーブル名 fields terminated by ',' enclosed by '"'
という構文となります。
※MySQL5.1系ではこれで動きましたが、5.5系をご使用の場合は/etc/mysql/my.cnfのmysqlセクションに「local-infile=1」を追記することで動くようになります。
後半の「fields terminated by ','」はカンマで区切られた=CSVファイルを表し、「enclosed by '"'」は各フィールドの値が"で囲まれていることを表します。
ダウンロードしたCSVファイルをテキストモードで開いてみると各フィールドの値が"で囲まれているのが分かると思いますが、こういった書き方ではなく値だけが記入されたファイルを取り込む場合enclosed以下は省略して構いません。
問題なければこれで一気にデータを取り込むことができます。
取り込みだけを見ると10秒もかかっていません。
DAOなんかとは比べるべくも無いぐらいの高速ぶりですな。
これらの作業にログ出力を加えてスクリプトに纏めたものやデータ取込用のSQL、更に取込先テーブルを作成するSQLなどを書き連ねたテキストファイルを置いておきますので、興味のある方はダウンロードして中身をご覧ください。
そうそう、作成したスクリプト「yubin_import.sh」に対して実行権限を付与しないと動かせないので
chmod 700 スクリプトのフルパス
にてパーミッション変更を行いましょう。
あとは
スクリプトのフルパス
をターミナルで入力して、設定間違いなどが無ければダウンロードから取り込みまで一発で行ってくれます。
更にテキストファイルの下のほうに記載したのですが、cronに登録することで定期的に自動実行もできるようになります。
ここまでできれば完璧ですね！