page_adsence

2014年11月20日木曜日

MySQLからMongoDBへのデータ移行

MySQLからMongoDBへの移行案件があったので、その時に調べたことをメモ。
今回の移行案件は、ただ単純にMySQLのテーブルをMongoDBのコレクション(MySQLでいうテーブルのこと)に変えるというわけではなかった。
MySQLの4テーブルを条件によってJOINし、その結果をMongoDBのコレクションとして保存するというもので、
データの移行件数はおおよそ3億件～4億件。
文字コードはEUC-JP

まず移行方法に関して検討してみた。
過去に行った移行案件では、基本的にPHPを介して移行するといったイメージ。
今回はPHPは介さずにMySQL→CSVやTSV→mongoimportといった手順で出来るように色々と検討してみた。

まず、MySQLでの出力形式だが、mongoimportの仕様により、区切り文字(カンマ)、囲み文字(ダブルクォーテーションが)、改行(CRLF)に関しては自動的に決まる。
エスケープに関しては、移行データにどういった文字が入っているかによる。

・日本語あり
・TEXT型あり
・改行あり
・入力チェックは最低限(セキュリティ的な部分のみ)
・NULLもあり

こういった場合のデータが含まれていると、普通の文字でエスケープすると、
入力データ内部に存在している可能性が高く、うまく移行出来ない可能性が高くなってしまう。

ちなみに今回試しに移行してみたデータは

・日本語あり
・TEXT型あり
・入力チェックは最低限
・改行コードも混在(LFとCRLF)
・NULLもあり
・HTMLのタグも入っている

なんでこんなデータが存在しているのかというと、テストデータとして以前作成したものをそのまま利用しているからである。
こういった場合でもきちんとエスケープ出来る可能性を探した結果、
最終的に行きついたのが以下のSQLになる。

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY "\Z" LINES TERMINATED BY "\r\n";

とりあえず"\Z"ならそうそう入ってこないであろうという推測の元、エスケープを行ってみた。
いろいろと加工は必要だが、とりあえずコマンドのみで目視する必要なく置換出来るのではないかと思う。
なお、エスケープに使える文字は以下の通り(MySQL公式サイトから抜粋)

Escape SequenceCharacter Represented by Sequence
\0An ASCII NUL (0x00) character.
\'A single quote (“'”) character.
\"A double quote (“"”) character.
\bA backspace character.
\nA newline (linefeed) character.
\rA carriage return character.
\tA tab character.
\ZASCII 26 (Control+Z). See note following the table.
\\A backslash (“\”) character.
\%A “%” character. See note following the table.
\_A “_” character. See note following the table.

とりあえずファイルの出力自体は上記の方法でやってみた。
11111,11111,"Test","テストメッセージ^Z
^Z
<link test_id=^Z"1^Z">",^ZN

NULLや改行部分もエスケープされてしまい、意図した感じにはなっていないが、ググってみるとこのような感じにならざるを得ないっぽい。
ということで、ここからmongoimport出来るまで置換処理を行っていく。

1.まず^ZNとなっているNULLを置換(^ZはCtrl+V Ctrl+Zの順番で入力すると出せる)
sed -i -e 's/^ZN,/"NULL",/g' test.csv

2.ダブルクォーテーションのエスケープ文字を修正
sed -i -e 's/^Z"/""/g' test.csv

3.LF、CRLFが混ざっているため、一度全てLFに変えてからCRLFに戻す(^MはCtrl+V Ctrl+Mの順番で入力すると出せる)
sed -i -e 's/^Z^M//g' test.csv

sed -i -e 's/^M//g' test.csv

sed -i -e 's/$/\r/' test.csv

4.文字コードを変換(mongoはUTF-8じゃないと読み込まないため)
iconv -f EUCJP -t UTF8 test.csv > test2.csv
※nkfコマンドは作業サーバに入っていなかったためiconvで代用

以上で変換作業は完了。
あとはmongoimportコマンドでインポート処理を行うだけ。
出力したCSVにはヘッダ行がないので、別で用意してやる。
$ vi header.txt
id
field_nameA
field_nameB
field_nameC
field_nameD

で、インポートコマンドは以下のような感じ。
mongoimport -d データベース名 -c コレクション名 --type csv --file test.csv --fieldFile header.txt


mongoimportでの注意点
1.csvの囲み文字は必ずダブルクォーテーションであること。シングルクォーテーションだと改行を含んだデータがある場合に、うまく取り込まれない。
2.元々のデータにダブルクォーテーションがあった場合は、""と記述することで、文中のダブルクォーテーションをエスケープすることが出来る。
3.改行コードは必ずCRLFである必要がある。空白行があった場合にLFだとうまく取り込まれない。
時刻:
ラベル: ,