Amazon Redshift: COPY対象データの二重引用符にご用心

はじめに

データアナリティクス事業本部のおざわです。

あまりないケースと思いますが、以下のブログ記事によく似た現象に遭遇しましたので共有したいと思います。

COPY成功、でも0件

S3に置いたCSVをCOPYコマンドでロードしたがテーブルにデータがない。と連絡がありました。ざっと各種ログを見た感じでは、特に異常はありません。ロードに使用したデータは以下のようなものです。

• データ件数 約8千件
• カンマ区切り(CSV)
• UTF-8
• 未圧縮
• 先頭行見出し

CSV

以下、雰囲気を感じてもらうためのサンプルCSVです。本件と全く関係ないですが、最近気になっているdbtのサンプルデータから stripe_payments.csv をお借りしています。

"ID","ORDERID","PAYMENTMETHOD","STATUS","AMOUNT","CREATED""
"1","1","credit_card","success","1000","2018-01-01""
"2","2","credit_card","success","2000","2018-01-02""
"3","3","coupon","success","100","2018-01-04""
"4","4","coupon","success","2500","2018-01-05""
...略...

COPYコマンド

Copyコマンドは以下のようなものです。

COPY payment (
    "ID", 
    "ORDERID",
    "PAYMENTMETHOD",
    "STATUS",
    "AMOUNT",
    "CREATED"
)
FROM 's3://my-dq-test-bucket-12345/payments.csv'
IAM_ROLE 'arn:aws:iam::<ACCOUNT_ID>:role/my-dq-test-redshift-role'
IGNOREHEADER 1
DELIMITER ','
FORMAT AS CSV
;

結果

Info: Load into table 'payment' completed, 0 record(s) loaded successfully.
Returned rows: 0

検証と原因

CSVをよく見ると各行の末尾に二重引用符（ダブルクォート）が1つ余計に入っています。どういう理由でこうなってしまったのか不明ですが、試しに以下のようなCSVを作成しました。ヘッダー行とデータの先頭1行だけ残し、ヘッダー行の余計な二重引用符を削除しています。

"ID","ORDERID","PAYMENTMETHOD","STATUS","AMOUNT","CREATED"
"1","1","credit_card","success","1000","2018-01-01""

再度COPYコマンドを実行してみた結果は以下エラーとなり、sys_load_error_detailにはerror_messageに Invalid quote formatting for CSV と出ていました。

ERROR: Load into table 'payment' failed. Check 'sys_load_error_detail' system table for details.

原因

今回のCOPYコマンドは、オプションにIGNOREHEADER 1を指定しているため先頭1行目をスキップします。また、問題のCSVは全行で最後のカラムに二重引用符が1つ余計に入っていましたので、途中の改行なども無視されてCSV全体が1行のヘッダーとして認識されていました。

試してみる

ということでIGNOREHEADER 1を付けて先頭行と最終行の末尾に二重引用符が1つ余計に入っていれば、途中がどうなっていても無視されるようです。現実にはありえませんが、以下のようなCSVファイルでもCOPYコマンドの結果は成功になるはずです。

"ID","ORDERID","PAYMENTMETHOD","STATUS","AMOUNT","CREATED""

くらにゃんです。

 /\_/\  
( o.o ) 
 > ^ <

もしかしてこれでもCOPYコマンド成功してしまうの？


"4","4","coupon","success","2500","2018-01-05""

成功

Info: Load into table 'payment' completed, 0 record(s) loaded successfully

おわりに

Redshiftとしては、指示どおりにヘッダー行を無視してロードしたけどデータはなかったよ！ということでCOPY結果は成功になっていました。COPYコマンドでRedshiftにデータをロードする際は、改行コードと二重引用符にはご用心ください。