Amazon Redshift: COPY対象データの改行コードにご用心

こんにちは、yokatsukiです。

タイトルでモロバレなのですが、S3からRedshiftへデータロード中に奇妙な現象に遭遇しました。

気付いてしまえばどうという話でも無かったのですが、ネット上にそのものズバリの事が書かれていなかったので、原因と対処を自分の備忘録を兼ねて共有します。

COPY成功、でも0件？

RedshiftにCOPYコマンドでデータロードするために、以下の形式のファイルをS3に格納しました。

• データ件数9426件
• タブ区切り(TSV)
• UTF-8
• 未圧縮
• 先頭行見出し

データのイメージは以下の様な感じです。Superstoreサンプルを使って雰囲気をお伝えします。

オーダー優先度 割引率 単価 顧客ID 出荷モード 顧客セグメント 製品名 製品の基本マージン オーダー日 出荷日 数量 新規注文 売上 オーダーID
指定なし 0.01 2.88 2 通常航空便 法人 Avery 49 0.36 2012/05/28 2012/05/30 2 5.9 88525
高 0.01 2.84 3 速達航空便 法人 SANFORD Liquid Accent? Tank-Style Highlighters 0.54 2010/07/07 2010/07/08 4 13.01 88522
指定なし 0.03 6.68 3 速達航空便 法人 Xerox 1968 0.37 2011/07/27 2011/07/28 7 49.92 88523
指定なし 0.01 5.68 3 通常航空便 法人 Acme? Preferred Stainless Steel Scissors 0.56 2011/07/27 2011/07/28 7 41.64 88523
指定なし 0 205.99 3 速達航空便 法人 V70 0.59 2011/07/27 2011/07/27 8 1446.67 88523
中 0.09 55.48 3 速達航空便 法人 Xerox 194 0.37 2011/11/09 2011/11/11 37 2011.67 88524
低 0.08 120.97 3 配達用トラック 法人 Canon S750 Color Inkjet Printer 0.38 2013/07/01 2013/07/08 12 1451.37 88526
指定なし 0.02 500.98 5 配達用トラック 自営業 Global Troy? Executive Leather Low-Back Tilter 0.6 2010/12/13 2010/12/15 12 6362.85 90193

このファイルの内容を、Redshift上にあらかじめ作成したテーブルへCOPYコマンドを使用してロードしました。 (環境はOS X El Capitan上のSQL Workbench/Jを使用しています)

COPY xxxxxx
FROM 's3://xxxxxx/xxxxxx.tsv'
CREDENTIALS
'aws_access_key_id=xxxxxxxx;aws_secret_access_key=xxxxxxxx'
DELIMITER '\t'
IGNOREHEADER 1;

その結果…。

Warnings:
Load into table 'xxxxxx' completed, 0 record(s) loaded successfully.

0 rows affected
COPY executed successfully

Execution time: 2.3s

エラーが発生することなく、またデータが1件も入ることなく無事？にコマンドが終了しました。

エラーが発生したのであれば、Redshiftのテーブルstl_load_errorsを確認すれば原因を調べることができるのですが、この場合はロードに成功しているのでエラーメッセージは出力されていません。困りましたね…。

原因と対処

原因はタイトルの通り、改行コードにありました。実はこのTSVファイル、改行コードがCRでした。

後で確認して分かったのですが、COPYコマンド使用時のデータ改行コードはCRLFとLFに対応しています。従って、それ以外のコードでは改行と解釈されず、全てのデータが1行と解釈されていました。

更に運の悪い？事に、今回はヘッダ付きということで先頭行をスキップするIGNOREHEADER 1を追加していました。これで結果的に全てのデータをスキップしていた事になる訳です。これなら確かに1件もデータが入るはずはありませんね。

trコマンドで改行コードをLFに変換し、別ファイルとして保存します。

$ tr \\r \\n <xxxxxx.tsv >yyyyyy.tsv

変換したTSVファイルで再びCOPYコマンドを実行したところ、今度は無事データロードに成功しました。

Warnings:
Load into table 'xxxxxx' completed, 9426 record(s) loaded successfully.

0 rows affected
COPY executed successfully

Execution time: 3.17s

まとめ

• COPYコマンドで対応している改行コードはLFとCRLF
• 区切りコードだけでなく、改行コードも確認しよう
• COPYで意図しない挙動が発生したら、COPYコマンドのオプションを1つずつ外して挙動の違いを確認しよう

それでは、また。