【小ネタ】 Redshift用CSVファイルのダブルクォーテーション変換コマンド

こんにちは、八木です。

S3からRedshiftにデータを取り込むには、COPYコマンドを使うことが推奨されています。
この際、よく困るのがCSVファイルのフォーマットです。

以下のようなCSVファイルの場合をCOPYコマンドでロードしたいとします。

"user_id","first_name","last_name","age","profile"
"goat0613","優成","八木",24,"こんにちは。"ヤギ"です。"
"tarotaro","太郎","山田",23,"ご飯食べたい。"
"misato","ミサト","葛城",29,"サービス、サービスぅ!"
"seyakate","平次","服部",17,"せやかて工藤"

この際、COPYコマンドでエラーが発生します。

COPY <schema_name>.<table_name>
FROM 's3://<bucket-name>/user.csv'
iam_role '<redshift-iam-role-arn>'
FORMAT CSV
IGNOREHEADER 1;

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

指示に従って原因を調査してみると、QUOTEがダメだよ！と言ってます。

select error_message from sys_load_error_detail order by start_time desc limit 1;

Invalid quote formatting for CSV

COPYコマンドでFORMATをCSVにする際、QUOTEパラメータを指定します。デフォルトではダブルクォートが設定されるため、今回のクエリではQUOTEは"です。
このクォート文字がCSVのフィールドの値に含まれているため、エラーが発生しています。
つまり問題はCSVファイルの"こんにちは。"ヤギ"です。"の部分です。　　

"user_id","first_name","last_name","age","profile"
"goat0613","優成","八木",24,"こんにちは。"ヤギ"です。"
"tarotaro","太郎","山田",23,"ご飯食べたい。"
"misato","ミサト","葛城",29,"サービス、サービスぅ!"
"seyakate","平次","服部",17,"せやかて工藤"

このエラーを回避するには、クォート文字を変更するか、CSVフィールド内の文字列をエスケープする必要があります。
クォート文字を変更するには、CSVファイルを大幅に書き換える必要があるため、大変です。素直にエスケープすると良いでしょう。

エスケープ方法は簡単で、CSVフィールド内の該当文字の前にクォート文字を入れるだけです。つまり、クォート文字が"であれば、"こんにちは。"ヤギ"です。"を"こんにちは。""ヤギ""です。"にします。

ということで、フィールド内の"を""に変更していきましょう。

数行、十数行のファイルであれば、手作業で変更できるかもしれませんが、何万行もある実際のデータでは途方もない時間がかかります。こんな作業をライフワークにしたくはありません。ということでシェルコマンドで処理します。

まず該当部分（フィールド内の"）があるかを確認します。
正規表現を使って、前後どちらにもコンマがないダブルクォーテーションを検索します。

$ grep '[^,]"[^,]' user.csv
"goat0613","優成","八木",24,"こんにちは。"ヤギ"です。"

一行だけヒットしました。

続いて変換です。

$ sed -i '' 's/\([^,]\)"\([^,]\)/\1""\2/g' user.csv

このコマンドでは、"を""に変換しています。

確認すると、意図通りに変換できていることがわかります。

$ cat user.csv
"user_id","first_name","last_name","age","profile"
"goat0613","優成","八木",24,"こんにちは。""ヤギ""です。"
"tarotaro","太郎","山田",23,"ご飯食べたい。"
"misato","ミサト","葛城",29,"サービス、サービスぅ!"
"seyakate","平次","服部",17,"せやかて工藤"

変換したデータをS3にアップロードし直し、Redshiftでロードしてみると、無事成功しました。

COPY user
FROM 's3://<bucket-name>/user.csv'
iam_role '<redshift-iam-role-arn>'
FORMAT CSV
IGNOREHEADER 1;

書くたびに頑張って思い出す正規表現ですが、そろそろマスターしていきたいところです。

以上、八木でした！

参考リンク

COPY - Amazon Redshift
データ形式パラメータ - Amazon Redshift
【Linux】正規表現を駆使してsedコマンドで置換してみた