Amazon Athena, BigQuery, SnowflakeにおけるDATE型の範囲の違い
この記事で言いたいことは次のツイートに集約されています。
ことの発端
Amazon AuroraのデータをPARQUET形式で出力されていたものをAmazon Athenaでクエリしていたのですが、BigQueryに移行することになり、そのままロードしようとしたところDATE型のカラムでエラーが発生しました。
調べてみると、そのカラム（Birthday）にはマイナス日付（例：-0001-12-30）が入っていたのが原因でした。結局マイナス日付はダンプしたときのバグだったようで解消されましたが、他にも 0000-12-30 や 9999-02-27 みたいな値が入っていてそれらは問題なくクエリできていました。
誕生日なのに明らかにおかしい日付が入っているのは目をつむるとして、それぞれのDWHでDATE型の範囲がどのように定義されているのかが気になったので調べてみました。
BigQuery
BigQueryのDATE型は 0001-01-01 から 9999-12-31 と決められています。
もしも日付データを文字列で持っていて、DATE型に変換するときは SAFE_CAST 関数を使うと変換に失敗したときに NULL を返してくれます。
Snowflake
SnowflakeのDATE型はとれる範囲はドキュメントに書かれておらず、マイナス日付の挙動については記載はありませんでした。
年は1582と9999の間をとることを推奨するとのことでした。
マイナス日付をいれると、なぜか 2001年に変換されました。
Amazon Athena
Amazon AthenaおよびPrestoにはDATE型の範囲については記載がありませんでした。
マイナス日付も問題なくクエリできてしまいました。
マイナスの日付とは何か？
マイナス日付というのは紀元前の日付を表すものだそうです。
こちらの記事によると、各RDBにおいてもDATE型の範囲は異なり、Oracleは紀元前の場合はマイナスで表現するとのことです。
まとめ
各種DWHおよびRDBにおけるDATE型の範囲について調べてみました。日付にマイナスを入れること自体は要件によりますがありえる話だといえます。
ただ、今回のケースだと誕生日のカラムなので明らかにマイナスの値が入るのはおかしいため、前処理で除外するべきだといえるでしょう。
Discussion