Redshiftで値の途中に引用符等を含むデータを取り込む

RedshiftにS3のCSVテキストファイルからデータをCOPYする際、データに引用符などの文字が入っているとエラーになってしまう問題の解決方法を紹介しています。
2018.10.10

"の入った文字列でエラーになった

Redshiftでs3にあるCSVテキスト1ファイルからデータをCOPYする際に、データに"(ダブルクォーテーション)が含まれているとエラーになってしまっていたので、その対処法と、COPYのパラメータについて調べてみました。

失敗した例

S3に置いてあるファイルに以下のようなレコードがあるとき、

0001|aaa "bbb" ccc

失敗していたCOPYは以下のようなものでした。

COPY quotation_test
FROM 's3://xxxxxxxxx/xxxx/xxxx'
     ACCESS_KEY_ID '〜〜〜〜'
     SECRET_ACCESS_KEY '〜〜〜〜'
     SESSION_TOKEN '〜〜〜〜'
     FORMAT AS CSV
     DELIMITER '|'
 ;

FORMAT AS CSVで、デリミタとして|を指定しています。 失敗の原因を調べるためにSTL_LOAD_ERRORSを見てみると

select * from stl_load_errors;
==> Invalid quote formatting for CSV

と出ていました。 どうやら"が原因のようです。

失敗した原因

デリミタは|に設定しているので、"はなんら影響を及ぼさないように見えますが、実はFORMAT AS CSVを指定している時にはQUOTEというパラメータのデフォルト値として"が使われています。

QUOTEパラメータとは

QUOTEパラメータはFORMAT AS CSVが設定された時に同時に設定されるパラメータで、デフォルト値は"です。 QUOTEとして"が設定されている時の挙動は以下のようになります。

  • 各カラムの値が"で囲まれている場合、それを除去する
  • カラムの値を囲う用途以外の"が発見されたらエラーとする

この2番目がまさに今回のエラーの原因となっていました。 QUOTEに設定された文字は、値を囲うための専用文字として確保されてしまうようです。 なお、ややこしいのですが、 値が"で囲まれていない分には全く構わない ので、全ての値を囲う必要はありません。2

対処法1:元データを編集

FORMAT AS CSVを指定した時にも、"をエスケープする方法があるので、それを使います。

  • その値全体を"で囲う
  • ""(ダブルクォーテーション2つ)のように、QUOTE文字を2つ並べる

とすればOKです。
ポイントは、値の中に"を登場させたい場合は、 必ず、その値全体が"で囲われていなければならない という点です。 ただ""に直しても相変わらずエラーになってしまうので注意が必要です。

元データを編集してCOPY可能にした例

0001|"aaa ""bbb"" ccc"
COPY quotation_test
FROM 's3://xxxxxxxxx/xxxx/xxxx'
     ACCESS_KEY_ID '〜〜〜〜'
     SECRET_ACCESS_KEY '〜〜〜〜'
     SESSION_TOKEN '〜〜〜〜'
     FORMAT AS CSV
     DELIMITER '|'
 ;
id message
0001 aaa "bbb" ccc

これで無事COPYが成功します。

対処法2:COPYパラメータで対処

上記では元データを編集しましたが、現実には元データは編集できなかったり、もう大量にデータが溜まっていてそれを全て修正するのは大変(手間というより、データを書き換えてしまう恐れなど)なことがほとんどかと思います。 なので、COPYのパラメータを修正してこのデータに対処します。

FORMATを指定しない

実は対処法はとても簡単で、FORMAT AS CSVの指定をなくせば大丈夫です。
"に特別な意味を与えていたQUOTEパラメータの設定は、FORMAT AS CSVに付随して有効になったものですので、それを解除します。 FORMATを指定しない場合の挙動は以下です。

  • DELIMITER文字をカラム区切りとする
    • DELIMITER文字のデフォルト値は|

デリミタ文字を見つけて、そこで分割するだけ。非常にシンプルです。

COPYのパラメータを編集してCOPY可能にした例

0001|aaa "bbb" ccc
COPY quotation_test
FROM 's3://xxxxxxxxx/xxxx/xxxx'
     ACCESS_KEY_ID '〜〜〜〜'
     SECRET_ACCESS_KEY '〜〜〜〜'
     SESSION_TOKEN '〜〜〜〜'
     DELIMITER '|'                  --デフォルト値なので実は不要
 ;
id message
0001 aaa "bbb" ccc

単純に|で区切っただけですので、単純明快です。

FORMAT指定ナシに出来ないか考えよう

FORMAT AS CSVを指定するとQUOTEが設定され、上記のようなハマりポイントになりがちです。 しかし、QUOTEの仕様が少し分かりづらいながらも、FORMAT AS CSVが指定されているケースは少なくないように思います。
その原因は「FORMATに何か指定しないといけないのではないか?」というのが主な原因だと思うのですが、いかがでしょうか?

もし「デリミタを指定して、それでカラムを区切りたいだけ」と考えているなら、実は「FORMATを指定せずDELMIITERを指定する」が正解です。 CSVという名前がいかにもそれっぽいのですが、QUOTEのことを忘れると痛い目を見ます。

もちろん、QUOTEが有効になることで想定外データを検知しやすくなるなどの側面もあると思いますので、使い勝手とはトレードオフですが、何にしてもCOPYのパラメータなどの仕様はしっかり理解しておくに越したことはありません。

まとめ

RedshiftのCOPYでFORMAT AS CSVを指定している時に、"でエラーになってしまうことを起点に、QUOTEパタメータの挙動と、FORMATを指定しないときの挙動を説明しました。

「デリミタを指定してテキストファイルを読みたい」というだけなら、FORMATを指定しなくても良いというのは、私としては盲点だったので勉強になりました。

誰かのご参考になれば幸いです。


  1. ここでの「CSVテキスト」という表記は、「何らかのデリミタ文字で区切られた整形済みテキスト」くらいの意味で使っています。 
  2. だからこそ、このような仕組みが働いていることに気づかないことが多い