Amazon Athenaでtimestampのデータ型を参照したら「HIVE_BAD_DATA…」のエラーが出たときの対処

#Amazon Athena

荒木智仁

2023.03.13

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部の荒木です。

初めてAthenaを触ったのですが、S3に保管したCSVファイルをAthenaから読み込んだときに以下のようなエラーが出たので原因等についてまとめたいと思います。

HIVE_BAD_DATA: Error parsing field value '2021-09-29 13:27:21.000' for field 0: For input string: "2021-09-29 13:27:21.000"

本題

原因

今回事象が発生した際の条件等は以下になります。
timestamp型のデータが必ずしも読み込めなくなるわけでなく、ある条件で事象が発生すると思われます。

S3に保管したCSVファイルを読み込む
CSVファイル内の全てのデータが"で囲まれている
日付に関するデータがjava.sql.Timestamp(YYYY-MM-DD HH:MM:SS.fff)の形式である

上記の内容を踏まえ今回のエラーについては以下のことが原因かと思います。

CSVデータ用のAthenaテーブルを作成&データが"で囲まれていたため、SerDeにOpenCSVSerDeを指定していた
OpenCSVSerDeを使用する場合、日付のデータはUNIXのTIMESTAMP数値(1579059880000)を使用する必要がある
日付データにjava.sql.Timestampを使用する場合はLazySimpleSerDeを使用する必要がある

詳細

今回作成したAthenaテーブルのDDLとのサンプルデータは以下です。

"time_test"
"2021-09-20 13:27:21.000"
"2021-09-21 13:27:21.000"
"2021-09-22 13:27:21.000"
"2021-09-23 13:27:21.000"
"2021-09-24 13:27:21.000"

CREATE EXTERNAL TABLE IF NOT EXISTS cm_araki_test.test_tbl2 (
TIME_TEST TIMESTAMP
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ('field.delim' = ',' , 'escapeChar' = '//' , 'quoteChar'= '"' )
LOCATION 's3://cm-araki/Athena/location/test/' 
TBLPROPERTIES ( 'skip.header.line.count' = '1' , 'write.compression' = 'NONE' , 'has_encrypted_data' = 'FALSE' )

今回全てのCSVデータが"で囲まれていたためOpenCSVSerDeを指定していましたが、日付データにjava.sql.Timestampを使用する場合はLazySimpleSerDeを使用する必要があるということですね。。

データに二重引用符 (") で囲まれた値が含まれる場合は、Athena で OpenCSV SerDe を使用して値を逆シリアル化できます。データに二重引用符 (") で囲まれた値が含まれていない場合は、SerDe の指定を省略できます。この場合、Athena はデフォルトの LazySimpleSerDe を使用します。

データに UNIX の TIMESTAMP 数値 (1579059880000 など) がある場合は、OpenCSVSerDe を使用します。データが java.sql.Timestamp 形式を使用する場合は、LazySimpleSerDe を使用します。

対応策

今回の事象が発生しないようにするためには以下の対応をする必要があるかと思います。

日付データを全てUNIXのTIMESTAMP数値に変更する
データの"を全て外し、LazySimpleSerDeを使用したテーブルを作成する

しかし、どちらもデータをいじる必要があるため、本番環境で行うことは現実的ではないかもしれません。。。
今回はAthenaからデータの参照ができればよかったので、timestamp型だった列をvarchar型に変更しjava.sql.Timestampのまま文字列として参照することでエラーは出なくなりました。