まず、MySQLからRedshiftへのコピーなんて「Amazon Data Pipeline」を利用しろよと思われますが、そんなお金はない（※調べてみるとクソ安かった）。
なので、Embulkを利用して MySQLに格納されているデータをRedshiftへコピーしてみました。
そしてタイトル通り『文字行文字（\r\n）・タブ文字(\t)が消える』のような現象が起きました・・・。
各バージョン
- embulk (0.9.4 java)
- embulk-output-redshift (0.8.0)
まずはその事象を実際に確認してみます。
事象の確認
確認用テーブル
単純な３つのカラムを持つ構造です。
"description"列に改行を含む値を入れていきます。
create table product( id int primary key auto_increment, name varchar(255), description varchar(255) ); insert into product(name, description) values ('商品１', 'これは商品１です。\r\nこれは商品１です。');
まずはMySQLで改行が格納されていることを確認。
想定通り"description"列のレコードに改行が入っています。
mysql> select * from product\G *************************** 1. row *************************** id: 1 name: 商品１ description: これは商品１です。 これは商品１です。
EmbulkでRedshiftへコピー
下記の設定ファイルを利用します。
ファイル名: mysql_to_redshift.yml
in: type: mysql user: root password: XXXXX database: sampledb host: 127.0.0.1 query: | select * from product out: type: redshift host: xxxxx.yyyyy.ap-northeast-1.redshift.amazonaws.com user: sample_user password: XXXXX database: sampledb table: product aws_access_key_id: XXXXXXXXXX aws_secret_access_key: XXXXXXXXXXXXXXX iam_user_name: sample_user s3_bucket: sample-bucket s3_key_prefix: temp/redshift delete_s3_temp_file: false mode: replace
そしていつも通りに実行。
$ embulk run mysql_to_redshift.yml
Redshift側で改行の確認
psqlコマンドで確認
sampledb=# select * from product; id | name | description ----+--------+---------------------------------------- 1 | 商品１ | これは商品１です。rnこれは商品１です。
「改行(\r\n)」が「rn」になっている・・・。
SQLWorkbenchで確認
やはり改行を表現できていない。
原因
なぜこうなってしまうのかをRedshiftへ挿入されるまでの流れを追って確認してみる。
Redshiftへの挿入の流れ
- MySQLのレコードをTSV形式で出力
- 出力されたTSVファイルをS3へ配置
- S3に配置されたTSVファイルをRedshiftへCOPY
簡単な流れだがどこかで意図しない処理が実行されていると思われる。
S3に配置されたファイル
EmbulkがS3に配置するTSVファイルを見てみることにする。
COPYコマンドによって参照されるファイル
GZIPで圧縮されているため、内容を確認するには解凍する必要があります。 ファイルの中身は、各値はタブ文字で区切りであるTSVファイルの形式となっている。 TSVファイルに置き換えた上でRedshiftにcopyする仕組みとなっているらしい。
$ gzip -d b5b538cf-de9e-4bdf-947b-1a97d3975a5a.gz $ cat b5b538cf-de9e-4bdf-947b-1a97d3975a5a 1 商品１ これは商品１です。\r\nこれは商品１です。
COPY時に指定されるオプション
下記のオプションが指定されてRedshiftにcopyされます。
GZIP DELIMITER '\t' NULL '\\N' ESCAPE TRUNCATECOLUMNS ACCEPTINVCHARS STATUPDATE OFF COMPUPDATE OFF
まず、RedshiftにCOPYする際の改行方法は「\r\n」を記述するだけでよいのだろうか？ その答えは下のスライドに書かれていた。
RedshiftへのCOPYを使っての改行の表現は「\r\n」ではなく「\\r\n」と記述する必要があるらしい。
なぜ「\r\n」は挿入後「rn」となっていのか
それははCOPYの際に指定された"ESCAPEオプション"の仕業
解決
「embulk-output-jdbc」には改行についてのオプションは存在していないので、ソースコードを修正して対応する。
「org.embulk.output.postgresql.AbstractPostgreSQLCopyBatchInsert」クラスを修正する
改行文字・タブ文字のエスケープも修正している。
修正前
private void setEscapedString(String v) throws IOException{ for (char c : v.toCharArray()) { String s; switch (c) { case '\\': s = "\\\\"; break; case '\n': s = "\\n"; break; case '\t': s = "\\t"; break; case '\r': s = "\\r"; break; case 0: s = ""; break; default: s = String.valueOf(c); } writer.write(s); } }
修正後
private void setEscapedString(String v) throws IOException{ for (char c : v.toCharArray()) { String s; switch (c) { case '\\': s = "\\\\"; break; case '\n': s = "\\\n"; // 修正 "\r"は削除 break; case '\t': s = "\\\t"; // 修正 break; case 0: s = ""; break; default: s = String.valueOf(c); } writer.write(s); } }
後はビルドをして、プラグインとして読み込ませる。
Embulkのプラグイン自作・反映の記事は他にたくさんあるのでここでは特にふれない。
改行が正常に挿入されているかを確認
改行が正常に反映されている。✌('ω')✌
まとめ
RedshiftへCOPY時のエスケープに関するスライドありがたや〜。 「embulk-output-jdbc」のgemにも反映されるといいですね（OSSに詳しい人お願いします。）
