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

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、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コマンド使用時のデータ改行コードはCRLFLFに対応しています。従って、それ以外のコードでは改行と解釈されず、全てのデータが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コマンドで対応している改行コードはLFCRLF
  • 区切りコードだけでなく、改行コードも確認しよう
  • COPYで意図しない挙動が発生したら、COPYコマンドのオプションを1つずつ外して挙動の違いを確認しよう

それでは、また。