【Tips】Redshiftでフォーマットが不正な日付データを無理やりCOPYする
こんにちは。DA事業本部の春田です。
表題の件について、簡単にシェアさせてください。
問題点
データベースを運用していると、時として入力時のバリデーションが効かず、不正な型のデータが入ってしまうことがあるかと思います。かといってそのデータがユーザーが入力したものである場合は、データ自体に変更を加えることは避けたいところです。
今回のケースは、新しくソートキーを設定したテーブルへデータを移行している最中に起こりました。日付型のデータにユーザーが入力した 43-03-16 というデータが既に入ってしまっていたために、古いテーブルをS3へ UNLOAD 後、新しいテーブルへ COPY する際に失敗していました。吐き出されたエラーは以下の通りです。
Invalid Date Format - length must be 10 or more
対処
「不正データ含まれる＝カラム値のフォーマットが統一されていない」という仮定を置き、 COPY のコマンドに dateformat 'auto' を追加する方針で考えます。公式ドキュメントの通りこのパラメータを指定すると、ソースデータの日付形式または時間形式を自動的に認識して変換してくれます。しかし今回のデータ 43-03-16 の場合だと、上の対応を加えたとしても以下のエラーが吐き出されます。
Error converting text to date
この場合、上の対応に加えてRedshiftデフォルトの datestyle を一時的に変更する必要があります。デフォルトはISOのMDY、すなわちISO形式の「月・日・年」という順番になっているので、 43 を「月」と誤認識して変換できないようですね。 datestyle を ISO,YMD に設定してから COPY を実行します。
show datestyle; --> ISO, MDY set datestyle to 'ISO,YMD';
これで無事にCOPYできたか……？と思いきや、 43-03-16 が 2043-03-16 に変換されてしまっていました。回りくどくて申し訳ないのですが、結論を言えば 43-03-16 に対しても dateformat 'YYYY-MM-DD' を指定すればそのまま取り込めます。
COPY には dateformat 'auto' というパラメータがありますが、 datestyle を変更してまで不正データを取り込もうとすると、変に加工されてしまうことがある点にご注意ください。
